ToB企服应用市场:ToB评测及商务社交产业平台

标题: 项目劳绩总结--MySQL深度分页优化 [打印本页]
作者: 八卦阵    时间: 2024-11-9 07:26
标题: 项目劳绩总结--MySQL深度分页优化
一、深度分页先容

查询偏移量过大的场景称为深度分页，这会导致查询性能较低，例如：
  1. -- MySQL 在无法利用索引的情况下跳过1000000条记录后，再获取10条记录
  2. SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10
复制代码
二、数据准备

2.1 建表

执行如下语句建表：
  1. CREATE TABLE `test_big_data` (
  2. `id` bigint NOT NULL AUTO_INCREMENT,
  3. `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  4. `add_time` bigint NOT NULL,
  5. `update_time` bigint DEFAULT NULL,
  6. `del` tinyint NOT NULL DEFAULT '0',
  7. PRIMARY KEY (`id`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码
2.2 创建存储过程初始化数据

  1. CREATE PROCEDURE test_insert_bigdata()
  2. begin
  3. declare i int(11) default 1;
  4. while i < 10000000
  5. DO
  6. INSERT INTO test_big_data(name, add_time, update_time, del) VALUES (CONCAT("itlgitlg",SUBSTRING(MD5(RAND()),1,30)),
  7. (SELECT 1419955200- (FLOOR(1 + (RAND() * 12)) * 2678400) - (FLOOR(1 + (RAND() * 31)) * 86400) - FLOOR(1 + (RAND() * 86400))),
  8. (SELECT 1419955200- (FLOOR(1 + (RAND() * 12)) * 2678400) - (FLOOR(1 + (RAND() * 31)) * 86400) - FLOOR(1 + (RAND() * 86400))), 0);
  9. SET i = i + 1;
  10. END WHILE ;
  11. commit;
  12. end
复制代码
2.3 执行存储过程，可多窗口执行

  1. call test_insert_bigdata()
复制代码
2.4 创建联合索引

导入数据后才创建，包管初始化数据速度
  1. CREATE INDEX idx_name_update_time USING BTREE ON test.test_big_data (name,update_time);
复制代码
2.5 查询当前数据信息

执行语句
  1. // 查看数据库最大id记录
  2. select max(id) from test_big_data
复制代码

三、深度分页场景

当MyBatis执行SQL语句：
  1. select * from test_big_data where name like 'itlgitlg%' limit 800000,10
复制代码
该查询分页需要扫描800000 +10行数据，然后丢掉前面800000行记录，同时进行800000+10次回表，总计耗时7.5秒；
limit语句会先扫描offset+n行，然后再抛弃掉前offset行，返回后n行数据;
limit 800000 +10 扫描更多的行数，也意味着回表更多（800000 +10）的次数。

这个任务照旧在MySQL没有其他任务处理时的执行环境，若在生产环境，效率会更差，接口响应会更慢，因此这也是一种SQL优化场景。
四、优化方案

4.1 范围查询

当可以包管 ID 的连续性时（有索引更佳），可根据 ID 范围进行分页：
  1. # 查询指定 ID 范围的数据
  2. SELECT * FROM test_big_dataWHERE id > 800000 AND id <= 100010 ORDER BY id
  3. # 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询：
  4. SELECT * FROM test_big_dataWHERE id > 800000 LIMIT 10
复制代码
4.2 子查询

先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效率会更快一些。
  1. # 通过子查询来获取 id 的起始值，把 limit 1000000 的条件转移到子查询
  2. SELECT * FROM test_big_dataWHERE id >= (SELECT id FROM test_big_data limit 8000000, 1) LIMIT 10;
复制代码
只适用于 ID 是正序的，子查询的结果会产生一张新表，会影响性能，应该尽量制止大量使用子查询。
4.3 采用覆盖索引优化

由于name、update_time创建联合索引，若业务上，所需的字段都在索引上，可以使用覆盖索引来优化SQL，减少数据库的回表操作；如下sql：
  1. select name,update_time from test_big_data where name like 'itlgitlg%' limit 800000,10
复制代码
sql耗时470ms左右：

制止 InnoDB 表进行索引的二次查询，也就是回表操作，可以把随机 IO 变成顺序 IO 加快查询效率。
不过，当查询的结果集占表的总行数的很大一部分时，大概就不会走索引，自动转换为全表扫描。就算强制索引force index(id) 效果也不明显，需要读取大量的索引页，频繁回表等随机IO。
4.4 延迟关联

优化思绪：跟子查询的优化思绪是一样的，把条件转移到主键索引树，然后减少回表。差别点是，延迟关联使用inner join取代子查询。
先通过二级索引查询主键，再通过主键关联，减少回表提升性能，,优化后SQL为：
  1. select a.* from test_big_data a
  2. inner join
  3. (select id from test_big_data
  4. where name like 'itlgitlg%' limit 800000,10) t
  5. on a.id = t.id
复制代码
sql耗时为466ms：

ps：
  1. MySQL B+树索引：
  2. InnoDB存储引擎中，索引分主键索引（聚簇索引）和二级索引
  3. 主键索引：叶子节点存放的是整行数据
  4. 二级索引：叶子节点存放的是主键的值；
  5. 回表：在InnoDB存储引擎下，二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键B+tree的叶子节点里面，二级索引的B+树叶子节点都是存放的(索引列,主键)。
复制代码
4.5 标签记录

limit 深度分页题目本质原因：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的降落。
标签记录的思想：就是标记一下前次查询到哪一条，下次再来查的时候，从该条开始往下扫描。
则SQL可以修改为：
  1. select * from test_big_data where name like 'itlgitlg%' and id > 800000 order by id asc limit 0,10
复制代码
执行耗时10ms左右：

求按照id连续查询，或者其他连续自增字段查询（如更新时间）每次查询后将最大值返给前端；下一次查询时，前端将最大值带到后端查询，现实中大概很多场景不支持。
4.6 单路排序和双路排序

4.6.1 单路排序


4.6.2 双路排序


MySQL在4.1之前都是双路排序，之后优化改为满意条件默认单路排序，条件为：查询字段数据大小小于max_length_for_sort_data值，但改到最小值测试也没有看到变化。
因此，子查询方式快的原因是：子查询只取create_time+id到sort buffer（相称于双路排序的做法）, 相比直接查询，省去绝大部分字段，减少大量临时文件IO操作，因此提高查询效率。
另一个方法调整sort_buffer_size大小，锦上添花，不能做为深分页的优化方案
4.6 使用GTID

4.7 换库，使用TiDB

五、总结

制止深度分页，可以从以下方面考虑：
5.1 业务方面


5.2 技能方面



免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4