mysql limit 分页优化怎么做？有哪些高效方法

mysql limit 分页优化原理是什么

咱们先聊聊mysql limit分页到底是咋回事，平时咱们用select * from table limit offset, rows; 这种语句，就是从表中取数据，跳过offset条，取rows条，比如limit 10,20，就是跳过前10条，取20条，这在数据量小的时候没啥问题，就像从钱包里掏几张零钱，顺手就拿出来了，但数据量大了就不一样，尤其是offset特别大的时候，mysql会先扫描前面所有的行，然后再取后面的，这就像你要从一本1000页的书里找第990-1000页的内容，得先把前989页都翻一遍，能不累吗？所以mysql limit分页的核心问题，就是offset过大时的全表扫描导致效率低下。

mysql limit 分页优化常见问题有哪些

实际用的时候，大家常遇到的坑还真不少，最常见的就是offset值太大导致查询变慢，比如查第1000页，offset=9990，这时候mysql要扫描9990条数据，再取10条，简直是吃力不讨好，还有人没给查询字段建索引，本来limit可以走索引快速定位，结果变成全表扫描，速度慢得像蜗牛爬。返回数据量太大也是个问题，比如一次取1000条，网络传输和内存消耗都跟着增加，页面加载半天出不来，之前我朋友就踩过这坑，他做的用户列表页，没分页，直接查全表，结果数据量到10万条时，页面直接卡崩了,被老板骂了一顿。

mysql limit 分页优化具体方法有哪些

知道了问题，咱们就来说说怎么优化，第一个方法是用索引覆盖查询，如果你查询的字段都在索引里，mysql就不用回表查数据，直接从索引里取，速度会快很多，比如表有主键id，建了索引idx_id，查询select id from table limit 100000,10; 就比select *快，因为id在索引里,不用去查数据表。

第二个方法是子查询定位起始位置，比如要查offset=100000的10条数据，可以先查第100000条的id，然后用where id > 这个id limit 10，比如select * from table where id > (select id from table limit 100000,1) limit 10; 这样mysql会先通过子查询定位到起始id，再往后取10条，避免了扫描前面100000条数据，就像你知道要找的页码从第100001页开始，直接翻到那一页就行,不用从头翻。

第三个方法是延迟关联，有时候咱们需要查很多字段，但又不想回表，这时候可以先查id，再关联表查其他字段，比如select t.* from (select id from table limit 100000,10) as a inner join table as t on a.id = t.id; 这样先通过id索引快速拿到10个id，再关联表查详细数据，比直接select *快不少，相当于先找钥匙，再开门拿东西,效率高。

第四个方法是书签分页，也叫“基于上次查询结果的分页”，就是每次分页都以上一次的最后一条数据的id为起点，比如第一次查limit 10，得到最后id是100，下次就查where id > 100 limit 10，这种方法适合“上一页/下一页”的场景，不支持直接跳转到第N页，但效率特别高，因为where id > xxx可以直接走索引，就像看书时夹个书签，下次直接从书签处开始看,不用数页数。

mysql limit 分页优化和同类方法对比

咱们再看看mysql limit优化和其他分页方法比有啥优势，传统的limit offset分页，在offset小的时候还行，offset大了就完蛋，就像小马拉大车，越跑越慢，而咱们说的优化方法，比如子查询定位、书签分页，就像给小马换了发动机,跑起来嗖嗖的。

和存储过程分页比，存储过程虽然能封装逻辑，但维护起来麻烦，改个逻辑还得改存储过程，不像sql语句直接写在代码里方便，而且存储过程在高并发下可能有性能问题，就像你把很多工具都堆在一个抽屉里,想用的时候翻半天。

和其他数据库比如PostgreSQL的limit比，mysql的优化方法更灵活，尤其是结合索引使用，效果更好，PostgreSQL虽然也有limit，但在处理大数据量offset时，优化手段没mysql这么多，就像同样是开车，mysql优化后相当于装了导航，能抄近路,而其他数据库可能还在走大路。

mysql limit 分页优化案例分享

说个我自己的经历吧，之前在一家做在线教育的公司，负责课程表的查询功能，课程表数据大概有300万条，用户需要按时间、分类筛选后分页查看，一开始用的是select * from courses where category=1 order by create_time desc limit 10000,10; 结果用户翻到100页以后，每次查询都要等4秒多,投诉电话快被打爆了。

我当时先看了一下sql执行计划，发现type是ALL，全表扫描，rows是300万，难怪慢，然后我给category和create_time建了联合索引idx_category_create_time，这时候再查，type变成了range，rows降到了10万左右，速度快了1秒，但还是不够，接着我用了子查询定位，改成select * from courses where id > (select id from courses where category=1 order by create_time desc limit 10000,1) and category=1 order by create_time desc limit 10; 这下执行计划里rows直接变成了10，查询时间从4秒降到了0.1秒，用户再也没投诉过,老板还夸我效率高。

mysql limit 分页优化注意事项有哪些

优化归优化，有些坑还是得注意。索引不是越多越好，建太多索引会影响插入、更新的速度，就像你衣柜里衣服太多，找衣服方便，但收拾起来麻烦,所以只给常用的查询条件建索引就行。

数据一致性问题，如果用书签分页，中间有数据被删除或新增，可能导致分页结果跳页或重复，比如你上次查到最后id是100，下次查id>100，但中间有个id=99的被删了，就可能少一条数据，这时候可以结合版本号或者时间戳来处理,确保数据准确性。

不同mysql版本优化效果可能不一样，比如mysql 8.0支持更多索引类型和优化器功能，同样的sql在8.0上可能比5.7快不少，所以升级mysql版本也是个优化方向，就像手机系统升级,新功能往往更流畅。