MySQL 亿级数据分页的优化
需积分: 1 169 浏览量
2023-08-04
08:57:51
上传
评论
收藏 21KB DOCX 举报
MySQL 亿级数据分页的优化
背景下班后愉快的坐在在回家的地铁上，心里想着周末的生活怎么安排。
突然电话响了起来，一看是我们的一个开发同学，顿时紧张了起来，本周的版
本已经发布过了，这时候打电话一般来说是线上出问题了。果然，沟通的情况
是线上的一个查询数据的接口被疯狂的失去理智般的调用，这个操作直接导致
线上的 MySql 集群被拖慢了。好吧，这问题算是严重了，下了地铁匆匆赶到
家，开电脑，跟同事把 Pinpoint 上的慢查询日志捞出来。看到一个很奇怪的查
询，如下。
POST
domain/v1.0/module/method?order=condition&orderType=desc&offset=1800000
&limit=500
domain、module 和 method 都是化名，代表接口的域、模块和实例方法名，后
面的 offset 和 limit 代表分页操作的偏移量和每页的数量，也就是说该同学是
在 翻第（1800000/500+1=3601）页。初步捞了一下日志，发现 有 8000 多次这
样调用。这太神奇了，而且我们页面上的分页单页数量也不是 500，而是 25 条
每页，这个绝对不是人为的在功能页面上进行一页一页的翻页操作，而是数据
被刷了（说明下，我们生产环境数据有 1 亿+）。详细对比日志发现，很多分页
的时间是重叠的，对方应该是多线程调用。通过对鉴权的 Token 的分析，基本
定位了请求是来自一个叫做 ApiAutotest 的客户端程序在做这个操作，也定位
了生成鉴权 Token 的账号来自一个 QA 的同学。立马打电话给同学，进行了沟通
和处理。分析其实对于我们的 MySQL 查询语句来说，整体效率还是可以的，该
有的联表查询优化都有，该简略的查询内容也有，关键条件字段和排序字段该
有的索引也都在，问题在于他一页一页的分页去查询，查到越后面的页数，扫
描到的数据越多，也就越慢。我们在查看前几页的时候，发现速度非常快，比
如：limit 200,25，瞬间就出来了。但是越往后，速度就越慢，特别是百万条
之后，卡到不行，那这个是什么原理呢。先看一下我们翻页翻到后面时，查询
的 sql 是怎样的：
select * from t_name where c_name1='xxx' order by c_name2 limit
2000000,25;
这种查询的慢，其实是因为 limit 后面的偏移量太大导致的。比如像上面的
limit 2000000,25 ，这个等同于数据库要扫描出 2000025 条数据，然后再丢
弃前面的 20000000 条数据，返回剩下 25 条数据给用户，这种取法明显不合
理。大家翻看《高性能 MySQL》第六章：查询性能优化，对这个问题有过说
明：分页操作通常会使用 limit 加上偏移量的办法实现，同时再加上合适的
order by 子句。但这会出现一个常见问题：当偏移量非常大的时候，它会导致
MySQL 扫描大量不需要的行然后再抛弃掉。 数据模拟那好，了解了问题的原
理，那就要试着解决它了。涉及数据敏感性，我们这边模拟一下这种情况，构
造一些数据来做测试。1、创建两个表：员工表和部门表
/*部门表,存在则进行删除 */
drop table if EXISTS dep;
create table dep(
id int unsigned primary key auto_increment,
资源评论
