mysql order by limit 使用技巧及优化分析

SQL语句如下：

EXPLAIN 
SELECT id,PushData 
FROM UserCardPushlog 
WHERE status = 0 
  AND HANDleLock = 0 
ORDER BY CreateTime 
LIMIT 2000 

行数：2200W

status、CreateTime 均有索引

执行计划如下：
type key rows extra
index ix_UserCardPushlog_CreateTime 88469 Using where
运行时间：33s

当去掉order by、limit时执行计划如下：
type key rows extra
ref ix_UserCardPushlog_status 350140 Using where
运行时间：1s

Q1：status、CreateTime上都有索引为何执行时间相差这么多？

Q2：status 字段上的索引为什么没有被使用？

• 单路排序与双路排序

1) 双路排序:是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。

2) 单路排序:是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

所有字段长度总数小于max_length_for_sort_data，则使用单路排序，否则使用双路排序。

当前服务器max_length_for_sort_data配置为1024，而表UserCardPushlog所有字段长度总数大于max_length_for_sort_data，也就是说当前SQL使用的是单路排序。

可以看到type为index，说明扫描了CreateTime字段的所有数据然后进行排序。所以很慢。

Q2 是否可以理解为如果SQL查询的是单表并且包含order by且有索引，那么就将会使用order by 字段后的索引进行排序。最后才使用where条件进行过滤？

优化方案：使用status过滤数据后再进行排序。

1. 使用子查询过滤数据后进行排序，如下SQL仍然没有使用status的索引。

   EXPLAIN 
   SELECT id,PushData 
   FROM
     (SELECT 
       id,
       PushData,
       CreateTime as s
     FROM
       UserCardPushlog 
     WHERE status = 0 
       AND HANDleLock = 0) as t 
   ORDER BY t.s 
   LIMIT 2000 ;
   

2. 最终解决方案：强制使用索引FORCE INDEX

   EXPLAIN 
   SELECT id,PushData 
   FROM UserCardPushlog 
   FORCE INDEX(ix_UserCardPushlog_status)
   WHERE status = 0 
     AND HANDleLock = 0 
   ORDER BY CreateTime 
   LIMIT 2000 
   
   /* http://www.manongjc.com/article/1641.html  */
   

3. 另一种解决方案可以参考一下：where 条件后面加上CreateTime的过滤条件，这样index就会变成range，时间也只需要15s左右。SQL如下：

   EXPLAIN 
   SELECT id,PushData 
   FROM UserCardPushlog 
   WHERE status = 0 
     AND HANDleLock = 0 
    AND CreateTime >=\'2017-01-01\'
   ORDER BY CreateTime 
   LIMIT 2000