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

梦里梦外; 2022-04-10 11:45 251阅读 0赞

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

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  
![576303-20180205161139591-1402384305.png][]

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

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

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

![576303-20180205161155841-784048986.png][]

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 分页可能会有重复，无法满足业务需求。  
![576303-20180205161212091-125195146.png][]

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

![576303-20180205161228998-1212113289.png][]

[576303-20180205161139591-1402384305.png]: /images/20220330/c2e656466d324b1b9c9799ee348e1f5e.png
[576303-20180205161155841-784048986.png]: /images/20220330/bd3839e9d7d3421ebad24f1dcf686dd2.png
[576303-20180205161212091-125195146.png]: /images/20220330/9ad7ef2cc9334cfab4bf00d97b13156a.png
[576303-20180205161228998-1212113289.png]: /images/20220330/b8fd363833694ca4b1256259bcae37ff.png