事故回看 - 如何解决深度分页问题
背景
22年8月初，合作的银行平台项目，发生了一次线上事故。
前端反馈几乎所有的接口都超时，营销平台的PC端和小程序端都处于不可用状态。这导致银行的客户经理无法执行下午的客户拜访与活动计划。
大约十几分钟后，银行反馈服务又正常了，但不久又出现了超时情况。
排查
- 银行的运维提供了服务的日志，在脱敏后将报错部分远程发送给我们。排查后发现是MYSQL无法响应后端服务。
- 同时，运维反馈数据库服务器CPU负载极高，之后我让他们打印出慢查询日志，发现大量慢查询。最后，我们根据慢查询日志定位到出问题的SQL和业务接口。
- 该业务接口是一个报表分页接口，虽然返回的数据很少，但是偏移量高达30w多，显然是有人频繁地进行深度分页。查询时间多达几分钟，CPU直接打满。最后其他业务接口无法及时得到响应，接口超时。
在这部分负责开发的同学跟我解释了这部分的业务情况后，我让前端暂时修改样式，只支持上一页下一页的跳转，大概能满足80%的客户经理需求（因为后端提交包还要找第三方安全机构重新进行安全评估，来不及）。之后，我便和这位同学一起在代码层面解决了该问题。
解决
深度分页效率低
-- 分页基本原理：LIMIT 10000 , 20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行。如果是LIMIT 1000000 , 100，需要扫描1000100 行，在一个高并发的应用里，每次查询需要扫描超过100W行，不炸才怪。
SELECT * FROM test ORDER BY id DESC LIMIT 10000, 20;
-- emp_no是主键聚集索引，birth_date是一个普通非聚集索引
mysql> select * from employees where birth_date > '1955-01-01' limit 100000,3;
-- 检索满足二级索引的所有数据，然后选择其中100003条主键，将这些主键暂存后排序（Using MRR），并按照顺序扫描主键索引树获取对应的数据100000条，然后返回后10条
覆盖索引+延迟关联
MYSQL官方推荐我们使用覆盖索引+延迟关联的方式解决。主要解决二级索引下的文件排序和频繁回表问题。可以跨页。不用回表。
-- 比上面性能好10倍，延迟关联的子查询不用回表，直接进行内连接
mysql> select e1.* from employees e1
inner join
(select emp_no from employees
where birth_date > '1955-01-01'
limit 100000,3
) as e2
on e2.emp_no=e1.emp_no;
使用滚动查询
每次接⼝都会返回查询出来的数据的最⼤的id（游标），下⼀次查询传⼊这个游标，服务端只需要根据这个游标，取出id⼤于这个游标的n个数据即可。n为每⻚展示条数。
假设上一次记录到100000，则SQL可以修改为：
select id,name,balance FROM account where id > 100000 order by id limit 10;
这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。只能连续页查询，不能跨页查询。
限制分页
很多时候，业务上没必要让用户深度分页，用户感兴趣的永远在前几十页而已。
允许小规模跳页 + 下一页的设计
禁止深度随机跳页
结论
- 深度分页慢的原因：大量扫描 + 文件排序 + 回表
- 大量扫描：limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据
- 文件排序 ，需要MySQL进行外部文件排序（order by 时Using FileSort），耗时很大。
- 二级索引大量回表查询，未使用覆盖索引
Mysql的分页limit经常执行的sql为： where xx = ？order by yy limit 100000,10 。此时如果执行计划中包含了 Using filesort 那么基本都发生了文件排序，其优化点就是尽量不要执行真正的排序，就是建立一个 xx，yy的复合索引。
复现会出现Using where；Using FileSort
- 解决方案：覆盖索引 + 延迟关联
| extra列 | 含义 |
|---|---|
| Using where | 表示优化器需要通过索引回表查询数据。 |
| Using index | 即覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表，通常是通过将待查询字段建立联合索引实现。 |
| Using index condition | 在5.6版本后加入的新特性，即大名鼎鼎的索引下推，是MySQL关于减少回表次数的重大优化。 |
| Using filesort | 文件排序，这个一般在ORDER BY时候，数据量过大，MySQL会将所有数据召回内存中排序，比较消耗资源。 |
