假設有一張訂單表 order扰魂，主要包含了主鍵訂單編碼 order_no闻伶、訂單狀態(tài) status、提交時間 create_time 等列，并且創(chuàng)建了 status 列索引和 create_time 列索引禁熏。此時通過創(chuàng)建時間降序獲取狀態(tài)為 1 的訂單編碼，以下是具體實現(xiàn)代碼：
select order_no from order where status =1 order by create_time desc
你知道其中的問題所在嗎邑彪？我們又該如何優(yōu)化瞧毙？
實踐
1. 造數(shù)據(jù)
創(chuàng)建表order01，主鍵索引，status宙彪，create_time 索引
CREATE TABLE `order01` (
`oder_no` bigint(0) NOT NULL AUTO_INCREMENT,
`status` bigint(0) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`create_time` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`oder_no`) USING BTREE,
INDEX `idx_status`(`status`) USING BTREE,
INDEX `idx_create_time`(`create_time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
創(chuàng)建表order02矩动，主鍵索引，status和create_time 聯(lián)合索引
CREATE TABLE `order02` (
`id` bigint(0) NOT NULL AUTO_INCREMENT,
`status` bigint(0) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`create_time` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_status_create_time`(`status`, `create_time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
隨機插入10000條數(shù)據(jù)释漆，方便測試
DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存儲過程則刪掉
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
insert into order01(STATUS,name,create_time) VALUES(RAND()*10000000,RAND()*10000000,now());
SET i = i+1;
END WHILE;
END $
CALL proc_initData();
執(zhí)行完悲没，我手動把1000條數(shù)據(jù)的status值置為1，方便測試男图。
2. 查看執(zhí)行計劃
EXPLAIN select * from `order01` WHERE STATUS = 1 ORDER BY create_time;
image.png
EXPLAIN select * from `order02` WHERE STATUS = 1 ORDER BY create_time;
image.png
執(zhí)行時間對比
image.png
3. 結論
status和create_time單獨建索引示姿，在查詢時只會遍歷status索引對數(shù)據(jù)進行過濾，不會用到create_time列索引逊笆，將符合條件的數(shù)據(jù)返回到server層栈戳，在server層對數(shù)據(jù)通過快排算法進行排序，Extra列會出現(xiàn)filesort难裆；
應該利用索引的有序性子檀，在status和creat_time列建立聯(lián)合索引，這樣根據(jù)status過濾后的數(shù)據(jù)就是按照create_time排好序的乃戈，避免在server層排序命锄。
