mysql分页语句怎么写(javascript基本数据类型)
优化项目代码过程中发现一个千万级数据深分页问题,缘由是这样的
库里有一张耗材 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 语句如下
因为功能需要满足 增量拉取的方式,所以会有数据更新时间的条件查询,以及相关 查询排序(此处有坑)
select
mcs_prod_id,
mcs_code,
mcs_name,
updt_time
from
mcs_prod
where
updt_time >= '1970-01-01 00:00:00.0' order by updt_time
limit xx, xx
二、重新认识 mysql 分页
limit 子句可以被用于强制 select 语句返回指定的记录数。limit 接收一个或两个数字参数,参数必须是一个整数常量
如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数
举个简单的例子,分析下 sql 查询过程,掌握深分页性能为什么差
mysql> select mcs_prod_id,mcs_code,mcs_name from mcs_prod where (updt_time >= '1970-01-01 00:00:00.0') order by updt_time limit 100000, 1;
+-------------+-------------------------+------------------+---------------------+
| mcs_prod_id | mcs_code | mcs_name | updt_time |
+-------------+-------------------------+------------------+---------------------+
| 181789 | xa601709733186213015031 | 尺、桡骨lc-dcp骨板 | 2020-10-19 16:22:19 |
+-------------+-------------------------+------------------+---------------------+
1 row in set (3.66 sec)
mysql> explain select mcs_prod_id,mcs_code,mcs_name from mcs_prod where (updt_time >= '1970-01-01 00:00:00.0') order by updt_time limit 100000, 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 | using index condition |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
简单说明下上面 sql 执行过程:
- 首先查询了表 mcs_prod,进行过滤 updt_time 条件,查询出展示列(涉及回表操作)进行排序以及 limit
- limit 100000, 1 的意思是扫描满足条件的 100001 行,然后扔掉前 100000 行
mysql 耗费了 大量随机 i/o 在回表查询聚簇索引的数据上,而这 100000 次随机 i/o 查询数据不会出现在结果集中
如果系统并发量稍微高一点,每次查询扫描超过 100000 行,性能肯定堪忧,另外 limit 分页 offset 越深,性能越差(多次强调)
图1 数据仅供参考
三、深分页优化
关于 mysql 深分页优化常见的大概有以下三种策略:
- 子查询优化
- 延迟关联
- 书签记录
上面三点都能大大地提升查询效率,核心思想就是让 mysql 尽可能扫描更少的页面,获取需要访问的记录后再根据关联列回原表查询所需要的列
3.1 子查询优化
子查询深分页优化语句如下:
mysql> select mcs_prod_id,mcs_code,mcs_name from mcs_prod where mcs_prod_id >= ( select m1.mcs_prod_id from mcs_prod m1 where m1.updt_time >= '1970-01-01 00:00:00.0' order by m1.updt_time limit 3000000, 1) limit 1;
+-------------+-------------------------+------------------------+
| mcs_prod_id | mcs_code | mcs_name |
+-------------+-------------------------+------------------------+
| 3021401 | xa892010009391491861476 | 金属解剖型接骨板t型接骨板a |
+-------------+-------------------------+------------------------+
1 row in set (0.76 sec)
mysql> explain select mcs_prod_id,mcs_code,mcs_name from mcs_prod where mcs_prod_id >= ( select m1.mcs_prod_id from mcs_prod m1 where m1.updt_time >= '1970-01-01 00:00:00.0' order by m1.updt_time limit 3000000, 1) limit 1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
| 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 | using where |
| 2 | subquery | m1 | null | range | mcs_prod_1 | mcs_prod_1 | 5 | null | 2296653 | 100.00 | using where; using index |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
2 rows in set, 1 warning (0.77 sec)
根据执行计划得知,子查询 table m1 查询是用到了索引。首先在 索引上拿到了聚集索引的主键 id 省去了回表操作,然后第二查询直接根据第一个查询的 id 往后再去查 10 个就可以了
图2 数据仅供参考
3.2 延迟关联
“延迟关联” 深分页优化语句如下:
mysql> select mcs_prod_id,mcs_code,mcs_name from mcs_prod inner join (select m1.mcs_prod_id from mcs_prod m1 where m1.updt_time >= '1970-01-01 00:00:00.0' order by m1.updt_time limit 3000000, 1) as mcs_prod2 using(mcs_prod_id);
+-------------+-------------------------+------------------------+
| mcs_prod_id | mcs_code | mcs_name |
+-------------+-------------------------+------------------------+
| 3021401 | xa892010009391491861476 | 金属解剖型接骨板t型接骨板a |
+-------------+-------------------------+------------------------+
1 row in set (0.75 sec)
mysql> explain select mcs_prod_id,mcs_code,mcs_name from mcs_prod inner join (select m1.mcs_prod_id from mcs_prod m1 where m1.updt_time >= '1970-01-01 00:00:00.0' order by m1.updt_time limit 3000000, 1) as mcs_prod2 using(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 | using where; using index |
+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
思路以及性能与子查询优化一致,只不过采用了 join 的形式执行
3.3 书签记录
关于 limit 深分页问题,核心在于 offset 值,它会 导致 mysql 扫描大量不需要的记录行然后抛弃掉
我们可以先使用书签 记录获取上次取数据的位置,下次就可以直接从该位置开始扫描,这样可以 避免使用 offest
假设需要查询 3000000 行数据后的第 1 条记录,查询可以这么写
mysql> select mcs_prod_id,mcs_code,mcs_name from mcs_prod where mcs_prod_id < 3000000 order by updt_time limit 1;
+-------------+-------------------------+---------------------------------+
| mcs_prod_id | mcs_code | mcs_name |
+-------------+-------------------------+---------------------------------+
| 127 | xa683240878449276581799 | 股骨近端-1螺纹孔锁定板(纯钛)yjbl01 |
+-------------+-------------------------+---------------------------------+
1 row in set (0.00 sec)
mysql> explain select mcs_prod_id,mcs_code,mcs_name from mcs_prod where mcs_prod_id < 3000000 order by updt_time limit 1;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| 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 | using where |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
好处是很明显的,查询速度超级快,性能都会稳定在毫秒级,从性能上考虑碾压其它方式
不过这种方式局限性也比较大,需要一种类似连续自增的字段,以及业务所能包容的连续概念,视情况而定
上图是阿里云 oss bucket 桶内文件列表,大胆猜测是不是可以采用书签记录的形式完成
四、order by 巨坑, 慎踩
以下言论可能会打破你对 order by 所有 美好 yy
先说结论吧,当 limit offset 过深时,会使 order by 普通索引失效(联合、唯一这些索引没有测试)
mysql> explain select mcs_prod_id,mcs_code,mcs_name,updt_time from mcs_prod where (updt_time >= '1970-01-01 00:00:00.0') order by updt_time limit 100000, 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 | using index condition |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
先来说一下这个 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 索引失效举例
offset 100000 时,通过 key extra 得知,没有使用磁盘临时文件排序,这个时候把 offset 调整到 500000
凉凉夜色为你思念成河,化作春泥呵护着你… 一首凉凉送给写这个 sql 的同学,发现了 using filesort
mysql> explain select mcs_prod_id,mcs_code,mcs_name,updt_time from mcs_prod where (updt_time >= '1970-01-01 00:00:00.0') order by updt_time limit 500000, 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 | using where; using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
using filesort 表示在索引之外,需要额外进行外部的排序动作,性能必将受到严重影响
所以我们应该 结合相对应的业务逻辑避免常规 limit offset,采用 # 深分页优化 章节进行修改对应业务
结言
最后有一点需要声明下,mysql 本身并不适合单表大数据量业务
因为 mysql 应用在企业级项目时,针对库表查询并非简单的条件,可能会有更复杂的联合查询,亦或者是大数据量时存在频繁新增或更新操作,维护索引或者数据 acid 特性上必然存在性能牺牲
如果设计初期能够预料到库表的数据增长,理应构思合理的重构优化方式,比如 es 配合查询、分库分表、tidb 等解决方式
上一篇: 使用DOM创建XML
下一篇: c语言快速入门