MySql 深度分页问题优化思路

im-xia

已于 2024-03-04 09:54:14 修改

阅读量271

点赞数 3

文章标签： mysql 数据库

于 2024-03-04 09:54:01 首次发布

本文链接：https://blog.csdn.net/x_023/article/details/136436022

版权

user 表描述：id 为主键，在 name 上建立了索引，也有其他字段但都不重要

想要分页，首先想到的就是这样的语句

select * from user limit offset, size;

问题来了，这种方式同样拿十条数据，第一页和第一百页的查询速度是不一样的
当我们执行上面的 sql 取第一页的 10 条数据时，server 层会在存储引擎的主键索引中依次获取到第 0 到 10 条完整行数据，并放到 server 层的结果集中，返回给客户端
当我们把页码调高，比如第 10w 页，server 会在存储引擎的主键索引中依次获取第 0 到 10w*10+10=1000010 条完整行数据，然后抛弃不需要的数据行，只留下最后的 10 条，放到 server 层的结果集中，返回给客户端

优化方式

当页数非 0 时，server 层会获取到很多无用的数据，而当 select 后面是 * 时，需要获取完整的行信息，我们可以从这里入手，只获取一两个字段减少获取时间，所以我们可以这样优化 sql

-- 优化前
select * from user limit 1000000, 10

-- 优化后
select * from user where id >= (select id from user limit 1000000, 1) limit 10

非主键索引的 limit 优化过程

select * from user order by name limit 0, 10;

上面 sql 的执行过程中，server 层首先会在存储引擎的 name 列索引获取到第 0 条数据，然后回表到主键索引中找到对应的完整行数据，反复依次找到 10 条数据后，放到 server 层的结果集中，返回给客户端
当页数很大时，比如 10w ，就意味着 1000010 次的回表，并且全部获取到 server 层中再抛弃不需要的数据行
当我们真这样获取第 10w 页的数据时，很大可能该 sql 的执行计划中 type 会变为 all ，也就是全表扫描。很明显，优化器在看到 100w+ 次的回表之后，摇了摇头，还不如一条条数据去判断算了，于是选择了全表扫描
全表扫描显然是不行的，性能太差了，可通过下面 sql 进行优化

select * from user where id in (select id from user order by name limit 1000000, 10);

总结

关注