线上故障"/>
MySQL 千万数据量深分页优化,拒绝线上故障
优化项目代码过程中发现一个千万级数据深分页问题,缘由是这样的
库里有一张耗材 MCS_PROD 表,通过同步外部数据中台多维度数据,在系统内部组装为单一耗材产品,最终同步到 ES 搜索引擎
MySQL 同步 ES 流程如下:
- 通过定时任务的形式触发同步,比如间隔半天或一天的时间频率
- 同步的形式为增量同步,根据更新时间的机制,比如第一次同步查询 >= 1970-01-01 00:00:00.0
- 记录最大的更新时间进行存储,下次更新同步以此为条件
- 以分页的形式获取数据,当前页数量加一,循环到最后一页
在这里问题也就出现了,MySQL 查询分页 OFFSET 越深入,性能越差,初步估计线上 MCS_PROD 表中记录在 1000w 左右
如果按照每页 10 条,OFFSET 值会拖垮查询性能,进而形成一个 "性能深渊"
同步类代码针对此问题有两种优化方式:
- 采用游标、流式方案进行优化
- 优化深分页性能,文章围绕这个题目展开
一、软硬件说明
MySQL VERSION
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.30 |
+-----------+
1 row in set (0.01 sec)
表结构说明
借鉴公司表结构,字段、长度以及名称均已删减
mysql> DESC MCS_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 | |
+-----------------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
通过测试同学帮忙造了 500w 左右数据量
mysql> SELECT COUNT(*) FROM MCS_PROD;
+----------+
| count(*) |
+----------+
| 5100000 |
+----------+
1 row in set (1.43 sec)
SQL 语句如下
因为功能需要满足 增量拉取的方式,所以会有数据更新时间的条件查询,以及相关 查询排序(此处有坑)
SELECTMCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIME
FROMMCS_PROD
WHEREUPDT_TIME >= '1970-01-01 00:00:00.0' ORDER BY UPDT_TIME
LIMIT xx, xx
个人整理了一些资料,有需要的朋友可以直接点击领取。
25大Java面试专题(附解析)
从0到1Java学习路线和资料
Java核心知识集
MySQL王者晋级之路
二、重新认识 MySQL 分页
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接收一个或两个数字参数,参数必须是一个整数常量
如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个
更多推荐
MySQL 千万数据量深分页优化,拒绝线上故障
发布评论