优化项目代码过程中发现一个千万级数据深分页问题，缘由是这样的

库里有一张耗材 mcs_prod 表，通过同步外部数据中台多维度数据，在系统内部组装为单一耗材产品，最终同步到 es 搜索引擎

mysql 同步 es 流程如下：

通过定时任务的形式触发同步，比如间隔半天或一天的时间频率同步的形式为增量同步，根据更新时间的机制，比如第一次同步查询 >= 1970-01-01 00:00:00.0记录最大的更新时间进行存储，下次更新同步以此为条件以分页的形式获取数据，当前页数量加一，循环到最后一页

在这里问题也就出现了，mysql 查询分页 offt 越深入，性能越差，初步估计线上 mcs_prod 表中记录在 1000w 左右

如果按照每页 10 条，offt 值会拖垮查询性能，进而形成一个 “性能深渊”

同步类代码针对此问题有两种优化方式：

采用游标、流式方案进行优化优化深分页性能，文章围绕这个题目展开

一、软硬件说明

mysql version

mysql>lectversion();+-----------+|version()|+-----------+|5.7.30|+-----------+1rowint(0.01c)

表结构说明

借鉴公司表结构，字段、长度以及名称均已删减

mysql>descmcs_prod;+-----------------------+--------------+------+-----+---------+----------------+|field|type|null|key|default|extra|+-----------------------+--------------+------+-----+---------+----------------+|mcs_prod_id|int(11)|no|pri|null|auto_increment||mcs_code|varchar(100)|yes|||||mcs_name|varchar(500)|yes|||||updt_time|datetime|no|mul|null||+-----------------------+--------------+------+-----+---------+----------------+4rowsint(0.01c)

通过测试同学帮忙造了 500w 左右数据量

mysql>lectcount(*)frommcs_prod;+----------+|count(*)|+----------+|5100000|+----------+1rowint(1.43c)

sql 语句如下

因为功能需要满足 增量拉取的方式，所以会有数据更新时间的条件查询，以及相关 查询排序（此处有坑）

lectmcs_prod_id,mcs_code,mcs_name,updt_timefrommcs_prodwhereupdt_time>='1970-01-0100:00:00.0'orderbyupdt_timelimitxx,xx

二、重新认识 mysql 分页

limit 子句可以被用于强制 lect 语句返回指定的记录数。limit 接收一个或两个数字参数，参数必须是一个整数常量

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数

举个简单的例子，分析下 sql 查询过程，掌握深分页性能为什么差

mysql>lectmcs_prod_id,mcs_code,mcs_namefrommcs_prodwhere(updt_time>='1970-01-0100:00:00.0')orderbyupdt_timelimit100000,1;+-------------+-------------------------+------------------+---------------------+|mcs_prod_id|mcs_code|mcs_name|updt_time|+-------------+-------------------------+------------------+---------------------+|181789|xa601709733186213015031|尺、桡骨lc-dcp骨板|2020-10-1916:22:19|+-------------+-------------------------+------------------+---------------------+1rowint(3.66c)mysql>explainlectmcs_prod_id,mcs_code,mcs_namefrommcs_prodwhere(updt_time>='1970-01-0100:00:00.0')orderbyupdt_timelimit100000,1;+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+|id|lect_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+|1|simple|mcs_prod|null|range|mcs_prod_1|mcs_prod_1|5|null|2296653|100.00|usingindexcondition|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+1rowint,1warning(0.01c)

简单说明下上面 sql 执行过程：

首先查询了表 mcs_prod，进行过滤 updt_time 条件，查询出展示列（涉及回表操作）进行排序以及 limitlimit 100000, 1 的意思是扫描满足条件的 100001 行，然后扔掉前 100000 行

mysql 耗费了 大量随机 i/o 在回表查询聚簇索引的数据上，而这 100000 次随机 i/o 查询数据不会出现在结果集中

如果系统并发量稍微高一点，每次查询扫描超过 100000 行，性能肯定堪忧，另外 limit 分页 offt 越深，性能越差（多次强调）

图1 数据仅供参考

三、深分页优化

关于 mysql 深分页优化常见的大概有以下三种策略：

子查询优化延迟关联书签记录

上面三点都能大大地提升查询效率，核心思想就是让 mysql 尽可能扫描更少的页面，获取需要访问的记录后再根据关联列回原表查询所需要的列

3.1 子查询优化

子查询深分页优化语句如下：

mysql>lectmcs_prod_id,mcs_code,mcs_namefrommcs_prodwheremcs_prod_id>=(lectm1.mcs_prod_idfrommcs_prodm1wherem1.updt_time>='1970-01-0100:00:00.0'orderbym1.updt_timelimit3000000,1)limit1;+-------------+-------------------------+------------------------+|mcs_prod_id|mcs_code|mcs_name|+-------------+-------------------------+------------------------+|3021401|xa892010009391491861476|金属解剖型接骨板t型接骨板a|+-------------+-------------------------+------------------------+1rowint(0.76c)mysql>explainlectmcs_prod_id,mcs_code,mcs_namefrommcs_prodwheremcs_prod_id>=(lectm1.mcs_prod_idfrommcs_prodm1wherem1.updt_time>='1970-01-0100:00:00.0'orderbym1.updt_timelimit3000000,1)limit1;+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+|id|lect_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+|1|primary|mcs_prod|null|range|primary|primary|4|null|2296653|100.00|usingwhere||2|subquery|m1|null|range|mcs_prod_1|mcs_prod_1|5|null|2296653|100.00|usingwhere;usingindex|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+2rowsint,1warning(0.77c)

根据执行计划得知，子查询 table m1 查询是用到了索引。首先在 索引上拿到了聚集索引的主键 id 省去了回表操作，然后第二查询直接根据第一个查询的 id 往后再去查 10 个就可以了

图2 数据仅供参考

3.2 延迟关联

“延迟关联” 深分页优化语句如下：

mysql>lectmcs_prod_id,mcs_code,mcs_namefrommcs_prodinnerjoin(lectm1.mcs_prod_idfrommcs_prodm1wherem1.updt_time>='1970-01-0100:00:00.0'orderbym1.updt_timelimit3000000,1)asmcs_prod2using(mcs_prod_id);+-------------+-------------------------+------------------------+|mcs_prod_id|mcs_code|mcs_name|+-------------+-------------------------+------------------------+|3021401|xa892010009391491861476|金属解剖型接骨板t型接骨板a|+-------------+-------------------------+------------------------+1rowint(0.75c)mysql>explainlectmcs_prod_id,mcs_code,mcs_namefrommcs_prodinnerjoin(lectm1.mcs_prod_idfrommcs_prodm1wherem1.updt_time>='1970-01-0100:00:00.0'orderbym1.updt_timelimit3000000,1)asmcs_prod2using(mcs_prod_id);+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+|id|lect_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|extra|+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+|1|primary|<derived2>|null|all|null|null|null|null|2296653|100.00|null||1|primary|mcs_prod|null|eq_ref|primary|primary|4|mcs_prod2.mcs_prod_id|1|100.00|null||2|derived|m1|null|range|mcs_prod_1|mcs_prod_1|5|null|2296653|100.00|usingwhere;usingindex|+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+3rowsint,1warning(0.00c)

思路以及性能与子查询优化一致，只不过采用了 join 的形式执行

3.3 书签记录

关于 limit 深分页问题，核心在于 offt 值，它会 导致 mysql 扫描大量不需要的记录行然后抛弃掉

我们可以先使用书签 记录获取上次取数据的位置，下次就可以直接从该位置开始扫描，这样可以 避免使用 offest

假设需要查询 3000000 行数据后的第 1 条记录，查询可以这么写

mysql>lectmcs_prod_id,mcs_code,mcs_namefrommcs_prodwheremcs_prod_id<3000000orderbyupdt_timelimit1;+-------------+-------------------------+---------------------------------+|mcs_prod_id|mcs_code|mcs_name|+-------------+-------------------------+---------------------------------+|127|xa683240878449276581799|股骨近端-1螺纹孔锁定板（纯钛）yjbl01|+-------------+-------------------------+---------------------------------+1rowint(0.00c)mysql>explainlectmcs_prod_id,mcs_code,mcs_namefrommcs_prodwheremcs_prod_id<3000000orderbyupdt_timelimit1;+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+|id|lect_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+|1|simple|mcs_prod|null|index|primary|mcs_prod_1|5|null|2|50.00|usingwhere|+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+1rowint,1warning(0.00c)

好处是很明显的，查询速度超级快，性能都会稳定在毫秒级，从性能上考虑碾压其它方式

不过这种方式局限性也比较大，需要一种类似连续自增的字段，以及业务所能包容的连续概念，视情况而定

上图是阿里云 oss bucket 桶内文件列表，大胆猜测是不是可以采用书签记录的形式完成

四、order by 巨坑, 慎踩

以下言论可能会打破你对 order by 所有 美好 yy

先说结论吧，当 limit offt 过深时，会使 order by 普通索引失效（联合、唯一这些索引没有测试）

mysql>explainlectmcs_prod_id,mcs_code,mcs_name,updt_timefrommcs_prodwhere(updt_time>='荷月1970-01-0100:00:00.0')orderbyupdt_timelimit100000,1;+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+|id|lect_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+|1|simple|mcs_prod|null|range|mcs_prod_1|mcs_prod_1|5|null|229665中秋节日记400字左右3|100.00|usingindexcondition|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+1rowint,1warning(0.00c)

先来说一下这个 order by 执行过程：

初始化 sort_buffer，放入 mcs_prod_id,mcs_code,mcs_name,updt_time 四个字段从索引 updt_time 找到满足条件的主键 id，回表查询出四个字段值存入 sort_buffer从索引处继续查询满足 updt_time 条件记录，继续执行步骤 2对 sort_buffer 中的数据按照 updt_time 排序排序成功后取出符合 limit 条件的记录返回客户端

按照 updt_time 排序可能在内存中完成，也可能需要使用外部排序，取决于排序所需的内存和参数 sort_buffer_size

sort_buffer_size 是 mysql 为排序开辟的内存。如果排序数据量小于 sort_buffer_size，排序会在内存中完成。如果数据量过大，内存放不下，则会利用磁盘临时文件排序

针对 sort_buffer_size 这个参数在网上查询到有用资料比较少，大家如果测试过程中存在问题，可以加微信一起沟通

4.1 order by 索引失效举例

offt 100000 时，通过 key extra 得知，没有使用磁盘临时文件排序，这个时候把 offt 调整到 500000

凉凉夜色为你思念成河，化作春泥呵护着你… 一首凉凉送给写这个 sql 的同学，发现了 using filesort

mysql>explainlectmcs_prod_id,mcs_code,mcs_name,updt_timefrommcs_prodwhere(updt_time>='1970-01-0100:00:00.0')orderbyupdt_timelimit500000,1;+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+|id|lect_type|table|partitions|t个人经历ype|possible_keys|key|key_len|ref|rows|filtered|extra|+----+-------------+----------+------------+-----糖蛋白作用-+---------------+------+---------+------+---------+----------+-----------------------------+|1|simple|mcs_prod|null|all|mcs_prod_1|null|null|null|4593306|50.00|usingwhere;usingfilesort|+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+1rowint,1warning(0.00c)

using filesort 表示在索引之外，需要额外进行外部的排序动作，性能必将受到严重影响

所以我们应该 结合相对应的业务逻辑避免常规 limit offt，采用 # 深分页优化 章节进行修改对应业务

结言

最后有一点需要声明下，mysql 本身并不适合单表大数据量业务

因为 mysql 应用在企业级项目时，针对库表查询并非简单的条件，可能会有更复杂的联合查询，亦或弹力公式者是大数据量时存在频繁新增或更新操作，维护索引或者数据 acid 特性上必然存在性能牺牲

如果设计初期能够预料到库表的数据增长，理应构思合理的重构优化方式，比如 es 配合查询、分库分表、tidb 等解决方式