mysql数据库分页查询优化

所谓分页就是当我们在打开一个页面在页脚的地方,显示的【上一篇】1 2 3 4 5 …… 【下一篇】类似这个东西。其实最好的方法是,把分页算法交给Sphinx、Lucence等第三方解决方案,没必要让MySQL来做。但是由于,"有时候开发说第三方太麻烦了","开发人数有限"等等原因。我们就只能用MySQL来做这个分页。所以就有了当数据量大的时候,分页变得缓慢的问题。
比如某日在看数据库查看慢查询的时候,看到如下语句:

诸如上面的情况,是我们的最常见MYSQL最基本的分页方式:
形如:select * from content order by id desc limit 0, 10
在中小数据量的情况下,这样的SQL足够用了,唯一需要注意的问题就是确保使用了索引。随着数据量的增加,页数会越来越多,查看后几页的SQL就可能类似:
select * from content order by id desc limit 100000, 50
因此,就是越往后分页,LIMIT语句的偏移量就会越大,速度也会明显变慢。
MYSQL分页性能优化
limit 10000,20的意思扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行,问题就在这里,如果是limit 100000,100,需要扫描100100行,在一个高并发的应用里,每次查询需要扫描超过10W行,性能肯定大打折扣。文中还提到limit n性能是没问题的,因为只扫描n行。
其实如果是细心的朋友,在mysql的官方文档,有一位网友给出了给出了一些参考方案:Speed of SELECT Statements
内容如下大致:

当然,这只是单纯的语句上的优化方案。具体如何使用,还是要看产品经理的需求和开发这边的操作。如果是我们只提供上一页和下一页,如下图:
分页优化图一
比如还是SELECT * FROM message ORDER BY id DESC,按id降序分页,每页20条,当前是第N页,当前页条目id最大的是10040,最小的是10021,如果我们只提供”上一页”、”下一页”这样的跳转(不提供到第N页的跳转),那么在处理”上一页”的时候SQL语句可以是:

处理”下一页”的时候SQL语句可以是:

不管翻多少页,每次查询只扫描20行。
但是,有时候我们还是需要提供,文章开头说的那种分页方式:【上一篇】2 3 4 5 …… 【下一篇】,那么我们就需要进行其他的方式了:
使用子查询来操作:

为什么会这样呢?因为子查询是在索引上完成的,而普通的查询时在数据文件上完成的,通常来说,索引文件要比数据文件小得多,所以操作起来也会更有效率。(via)通过explain SQL语句发现:子查询使用了索引!

有测试,使用子查询的分页方式的效率比纯LIMIT提高了14-20倍!
JOIN分页方式

经过我的测试,join分页和子查询分页的效率基本在一个等级上,消耗的时间也基本一致。explain SQL语句:

我们还可以使用分页存储过程代码

调用存储过程,我们使用call procedure_name()的方式:
例如:

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: