MySQL全面瓦解21（番外）：一次深夜优化亿级数据分页的奇妙经历

背景

晚上10点半，下班后愉快的坐在在回家的地铁上，心里想着周末的生活怎么安排。

突然电话响了起来，一看是我们的一个开发同学，顿时紧张了起来，本周的版本已经发布过了，这时候打电话一般来说是线上出问题了。

果然，沟通的情况是线上的一个查询数据的接口被疯狂的失去理智般的调用，这个操作直接导致线上的MySql集群被拖慢了。

好吧，这问题算是严重了，下了地铁匆匆赶到家，开电脑，跟同事把Pinpoint上的慢查询日志捞出来。看到一个很奇怪的查询，如下

    1 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是怎样的：

    1 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、创建两个表：员工表和部门表

     1 /*部门表,存在则进行删除 */
     2 drop table if EXI