MySql带order by与limit的sql优化

MySql带order by与limit的sql优化

(root@localhost:mysql.sock)[mm]explain select ID,ModeType,BusinessID,BusinessList,FlowStatus,CreateTime,Name,LastStepStatus  FROM Rec_FS  where FlowStatus <>2 and LastStepStatus = 1 ORDER BY CreateTime ASC LIMIT 0,100;
+----+-------------+-----------------+------+------------------------------------------+----------------+---------+-------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra                       |
+----+-------------+-----------------+------+------------------------------------------+----------------+---------+-------+---------+-----------------------------+
|  1 | SIMPLE      | Rec_FS | ref | FlowStatus,LastStepStatus,Idx_CreateTime | LastStepStatus | 5       | const | 1183769 | Using where; Using filesort |
+----+-------------+-----------------+------+------------------------------------------+----------------+---------+-------+---------+-----------------------------+
1 row in set (0.00 sec)

走索引LastStepStatus ，消耗1183769行，并做了filesort 排序,执行完8秒.

(root@localhost:mysql.sock)[mm]show create table Rec_FS \G ;
*************************** 1. row ***************************
       Table: Rec_FS
Create Table: CREATE TABLE `Rec_FS` (
....
KEY `ModeType` (`ModeType`) USING BTREE,
KEY `FlowStatus` (`FlowStatus`) USING BTREE,
KEY `LastStepStatus` (`LastStepStatus`) USING BTREE,
KEY `Idx_CreateTime` (`FlowStatus`,`LastStepStatus`,`CreateTime`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

查看表结构，有索引Idx_CreateTime 不并满足当前SQL最优执行计划，写法不合理，系统选择LastStepStatus 索引

根据ORDER BY CreateTime ASC LIMIT 0,100，这条SQL只需要抓取含CreateTime 升序、LastStepStatus分类为1 、FlowStatus <>2 的前100行就行了。
没有必要通过索引LastStepStatus 抓取满足条件，再排序，再取100行。

将索引Idx_CreateTime改写
create inex idx_LastStepStatus2 on Rec_FS(CreateTime,LastStepStatus,FlowStatus) ;

(root@localhost:mysql.sock)[mm]explain select ID,ModeType,BusinessID,BusinessList,FlowStatus,CreateTime,Name,LastStepStatus  FROM Rec_FS use index(idx_LastStepStatus2) where FlowStatus <>2 and LastStepStatus = 1 ORDER BY CreateTime ASC LIMIT 0,100;
+----+-------------+-----------------+-------+---------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+---------------------+---------+------+------+-------------+
| 1 | SIMPLE | Rec_FS | index | NULL | idx_LastStepStatus2 | 109 | NULL | 100 | Using where |
+----+-------------+-----------------+-------+---------------+---------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

idx_LastStepStatus2 只扫描100行，消除order by 带来的排序。整个SQL跑完0.00秒