MySQL 亿级数据分页
随着公司业务增大，数据量也是随之剧增。MySQL作为一款社区免费开源数据库。想要用它做几百万的数据分页。光靠limit是不靠谱的。当然不是诋毁mysql，mysql作为开源插拔式存储引擎数据库，已经是可以满足绝大部分的应用场景需求。使用mysql管理100tb也不是问题。但是使用方式却是一个问题。
分页几乎是所有后台列表页的标配功能，数据量小的时候怎么写都行，问题往往在表涨到几百万行之后才暴露出来：前几页毫秒级返回，越往后翻越慢，翻到最后几页甚至直接超时。这篇文章记录的就是我在公司账务表上遇到的深分页问题，以及一步步优化的过程。
limit 为什么会慢
limit接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
关键在于偏移量的实现方式：MySQL 并不能"跳到"第 10 万行，它必须把前面的 offset 行全部读出来再丢弃，只返回最后那几条。也就是说 limit 100000, 20 实际读取了 100020 行数据。如果查询的字段不在索引里，每一行还要根据主键回表去聚簇索引取完整记录，读的行数越多，这个代价被放大得越厉害。
limit在偏移量小于10w时性能还勉强可以接受，但随着偏移量越来越大，性能急剧下降。
公司单表账务数据已经到达230w ，做分页limt 来查询最后一页的数据，怕是没有个20秒是查询不出来的。当然具体的时间也要根据是否有索引，字段数量，数据内容而定，查询条件而定。
子查询先定位主键
为了解决分页效率问题，我采用方案是：先用子查询只查出主键 id，再拿 id 去取整行数据。
-- 子查询只取主键 id，带上 where 条件
-- 只扫描索引即可完成，不需要回表取整行
select id from table limit 100000,20
（带上where条件作为子查询）
由于主键id原本就是主键索引，所以limit的速度效率很高。并把条件字段加入复合索引，效率才会有质量的提升 。这一步快的原因在于：只查 id 时整个查询可以在索引上完成，索引每条记录很小，同样的 offset 扫描量，IO 成本比扫整行低得多。
但如果有排序，性能也会极具下降，目测30w数据排序id或时间需要1-2秒时间。
最好的方式是直接放弃 limit 的偏移量 使用where id > xxx 来更快速的定位目标数据id位置 之后排序 再 limit 20 条数据即可 这样的效率百万级基本可以支撑。这种写法通常叫游标分页（或 keyset 分页）：每次翻页时把上一页最后一条的 id 带过来，where id > xxx 直接在索引上定位起点，不管翻到第几页，扫描的行数都是固定的 20 条左右。代价是只能顺序翻页，不支持随意跳页，适合信息流、导出这类场景。
用 id 列表取整行数据
先查询出需要查询的id，之后执行不带条件的in idList查询：
-- 第二步：用上一步拿到的 id 列表取完整字段
-- 不带 where 条件，排序照常保留
select 字段 from table where id in (idList)
（不带条件，有排序还是排序，但排序没关系，因为数据已经很少，就算是外部排序也很快）
查询列表时只需要 where in id 即可，其他的条件在 select id 的时候已经加上，列表数据查询时就不需要进行任何条件，但是排序还是要加的 。
这样就可以极快的进行分页查询，并且如果列表字段不多，可以做覆盖索引，查询效率更上一层楼。但一般列表字段都比较多10几个，20几个都很正常，这些字段都加索引是不可取的，那么索引体积太大，新增数据，修改数据时效率将会因为去维护索引而降低，可能会反而降低表的性能。所以做查询也需要考虑字段个数，排序规则，条件个数，索引类型，哪些字段做索引来做权衡。
表拼接这样的操作在大数据量下基本不考虑使用。最好做冗余字段。join 在大表上意味着驱动表的每一行都要去被驱动表做一次查找，数据量上去之后放大效应很明显；把常用的关联字段冗余到主表里，用写入时多存一份换查询时少一次关联，在读多写少的列表场景里通常是划算的。
用 explain 验证执行计划
优化不能靠感觉，改完 SQL 要看执行计划确认。
查询sql 通过 explain 查看执行过程，是否走了索引，索引类型是什么。是否进行了回表，扫描数据行数等等重要信息，只要掌握好了这些，你的数据库性能才会有质量上的提升。
-- 在查询语句前加 explain 即可查看执行计划
-- 重点看 type（索引类型）、key（实际用到的索引）、
-- rows（预估扫描行数）、Extra（是否 Using index 覆盖索引）
explain select id from table where 条件 limit 100000,20;
经过测试，这样的分页效率 100w 每页20条数据 基本可以在1s内请求下来数据，基本为600ms左右的请求时间。
单表之外的路
分页优化解决的是单表查询效率，但表还在持续增长，架构上也要提前留好后路。
如果单表数据量已经过500w，已经可以考虑进行水平分表。
对于业务逻辑来讲，为了增加单库性能，可以考虑 读写分离，主写，从读，多从等方式。
根据业务对库进行垂直拆分， 分离热数据，冷数据进行分配服务器资源。
踩坑与注意
1）子查询方案的前提是 where 条件字段有合适的复合索引，否则第一步 select id 本身就会退化成全表扫描，优化等于白做。
2）where id > xxx 的游标分页要求排序键单调且唯一，如果按时间排序而时间有重复值，翻页时可能漏数据或重复，通常要用「时间 + id」联合排序兜底。
3）in (idList) 的列表长度就是每页条数，一般 20 条没有问题；但不要把这个写法推广到一次传几千个 id 的场景。
4）explain 给出的 rows 是估算值，统计信息不准时会有偏差，拿不准时结合慢查询日志和实际执行时间一起判断。
线上改分页 SQL 之前，先在从库或测试库用真实数据量验证一遍执行计划，深分页问题在小数据量下是复现不出来的。
小结
深分页慢的根源是 limit 偏移量必须逐行扫描丢弃，数据量越大代价越高。解决思路是让扫描发生在最小的数据集上：先在索引里定位主键，再回表取整行；能用游标分页就放弃偏移量。改完用 explain 验证索引是否真的生效。单表撑不住时，再考虑分表、读写分离、冷热拆分这些架构手段。
评论 / COMMENTS