文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL千万数据量深分页优化流程(拒绝线上故障)

2023-05-16 11:03

关注

引言

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

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

MySQL 同步 ES 流程如下:

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

如果按照每页 10 条,OFFSET 值会拖垮查询性能,进而形成一个 "性能深渊"

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

软硬件说明

MySQL VERSION

mysql>selectversion();
+-----------+
|version()|
+-----------+
|5.7.30|
+-----------+
1rowinset(0.01sec)

表结构说明

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

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||
+-----------------------+--------------+------+-----+---------+----------------+
4rowsinset(0.01sec)

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

mysql>SELECTCOUNT(*)FROMMCS_PROD;
+----------+
|count(*)|
+----------+
|5100000|
+----------+
1rowinset(1.43sec)

SQL 语句如下

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

SELECT
MCS_PROD_ID,
MCS_CODE,
MCS_NAME,
UPDT_TIME
FROM
MCS_PROD
WHERE
UPDT_TIME>='1970-01-0100:00:00.0'ORDERBYUPDT_TIME
LIMITxx,xx

重新认识 MySQL 分页

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

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

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

mysql>SELECTMCS_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|
+-------------+-------------------------+------------------+---------------------+
1rowinset(3.66sec)
mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHERE(UPDT_TIME>='1970-01-0100:00:00.0')ORDERBYUPDT_TIMELIMIT100000,1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
|id|select_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|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
1rowinset,1warning(0.01sec)

简单说明下上面 SQL 执行过程:

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

如果系统并发量稍微高一点,每次查询扫描超过 100000 行,性能肯定堪忧,另外 LIMIT 分页 OFFSET 越深,性能越差(多次强调)

MySQL千万数据量深分页优化流程(拒绝线上故障)

图1 数据仅供参考

深分页优化

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

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

子查询优化

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

mysql>SELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHEREMCS_PROD_ID>=(SELECTm1.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|
+-------------+-------------------------+------------------------+
1rowinset(0.76sec)
mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHEREMCS_PROD_ID>=(SELECTm1.MCS_PROD_IDFROMMCS_PRODm1WHEREm1.UPDT_TIME>='1970-01-0100:00:00.0'ORDERBYm1.UPDT_TIMELIMIT3000000,1)LIMIT1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
|id|select_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|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
2rowsinset,1warning(0.77sec)

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

MySQL千万数据量深分页优化流程(拒绝线上故障)

图2 数据仅供参考

延迟关联

"延迟关联" 深分页优化语句如下:

mysql>SELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODINNERJOIN(SELECTm1.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|
+-------------+-------------------------+------------------------+
1rowinset(0.75sec)
mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODINNERJOIN(SELECTm1.MCS_PROD_IDFROMMCS_PRODm1WHEREm1.UPDT_TIME>='1970-01-0100:00:00.0'ORDERBYm1.UPDT_TIMELIMIT3000000,1)ASMCS_PROD2USING(MCS_PROD_ID);
+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+
|id|select_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|
+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+
3rowsinset,1warning(0.00sec)

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

书签记录

关于 LIMIT 深分页问题,核心在于 OFFSET 值,它会 导致 MySQL 扫描大量不需要的记录行然后抛弃掉

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

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

mysql>SELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHEREMCS_PROD_ID<3000000ORDERBYUPDT_TIMELIMIT1;
+-------------+-------------------------+---------------------------------+
|MCS_PROD_ID|MCS_CODE|MCS_NAME|
+-------------+-------------------------+---------------------------------+
|127|XA683240878449276581799|股骨近端-1螺纹孔锁定板(纯钛)YJBL01|
+-------------+-------------------------+---------------------------------+
1rowinset(0.00sec)
mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHEREMCS_PROD_ID<3000000ORDERBYUPDT_TIMELIMIT1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|id|select_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|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1rowinset,1warning(0.00sec)

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

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

MySQL千万数据量深分页优化流程(拒绝线上故障)

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

ORDER BY 巨坑, 慎踩

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

先说结论吧,当 LIMIT OFFSET 过深时,会使 ORDER BY 普通索引失效(联合、唯一这些索引没有测试)

mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIMEFROMMCS_PRODWHERE(UPDT_TIME>='1970-01-0100:00:00.0')ORDERBYUPDT_TIMELIMIT100000,1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
|id|select_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|
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
1rowinset,1warning(0.00sec)

先来说一下这个 ORDER BY 执行过程:

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

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

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

ORDER BY 索引失效举例

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

凉凉夜色为你思念成河,化作春泥呵护着你... 一首凉凉送给写这个 SQL 的同学,发现了 Using filesort

mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIMEFROMMCS_PRODWHERE(UPDT_TIME>='1970-01-0100:00:00.0')ORDERBYUPDT_TIMELIMIT500000,1;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
|id|select_type|table|partitions|type|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|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1rowinset,1warning(0.00sec)

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

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

结言

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

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

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

以上就是MySQL千万数据量深分页优化拒绝线上故障的详细内容,更多关于MySQL千万数据深分页优化的资料请关注我们其它相关文章!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯