【转】基于主键的分页查询优化

经常有人来问为什么基于主键的分页查询性能也会很差,看下面这个例子

SELECT * FROM T_Withdraw WHERE CreateTime >= "2018-01-12" AND "2018-01-23" > CreateTime AND PayState = 1 AND AutoId >= 0 ORDER BY AutoId limit 200 \G

 

一、第一个疑问是走主键不好吗？

MySQL会自动计算 cost，判断走哪条路最快，这种情况下做了错误的选择，走主键索引，排序确实是快了。但是 where条件全部走不到索引，相当于全表扫描了一次。SQL 跑起来巨慢无比。

二、如何走到正确的索引?

有两种方法：

1.force index,这种方法类似奇技淫巧，不到万不得已,一般不推荐使用。

 SELECT * FROM T_Withdraw force index(idx_PayState_CreateTime) WHERE CreateTime >= "2018-01-12" AND "2018-01-23" > CreateTime AND PayState = 1 AND AutoId >= 0 ORDER BY AutoId limit 200 \G

看下图，确实走到正确的索引了，速度也快了很多倍。

 

2.SELECT * FROM T_Withdraw  WHERE CreateTime >= "2018-01-12" AND "2018-01-23" > CreateTime AND PayState = 1 AND AutoId >= 0 ORDER BY CreateTime limit 200 \G 

如果我们把 order by AutoId 改成 order by CreateTime，就可以走上正确的索引，这种方式的弊端是 CreateTime 分页可能会有重复，无法满足业务需求。

 

3.最后，其实我们调整一下 order by 的字段，这个问题就解了。

SELECT * FROM T_Withdraw  WHERE CreateTime >= "2018-01-12" AND "2018-01-23" > CreateTime AND PayState = 1 AND AutoId >= 0 ORDER BY CreateTime,AutoId limit 200 \G 

把 order by 调整为 CreateTime 相同的情况下再按 AutoId 排序，就可以解决这个问题了。也不需要 force index