MySQL大数据量下的深度分页查询优化

大数据量下的深度分页查询优化

1、问题分析

CREATE TABLE `intent_grammar` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `intent_id` bigint(20) NOT NULL,
  `utterance` varchar(1024) NOT NULL,
  `grammar` varchar(1024) NOT NULL ,
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `modifier` varchar(128) NOT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_update_time` (`update_time`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4

表共有600W行数据

• 对于如下SQL
  select * from intent_grammar order by update_time limit 10 offset 3000000;
  耗时12.8秒，索引执行计划为全表扫描

MySQL对这条SQL的处理方式为，先当作 select * from intent_grammar order by update_time limit 3000010;
如果选择全字段排序，需要全表扫描600W行（每行全部字段） + 排序600W行（应该会用磁盘）；如果选择rowid排序，需要全表扫描600W（每行id、update_time字段）+ 排序600W行（使用内存或磁盘） + 最后100次回表查询；不论哪种方案，都不是最优方案。可以看到，尽管只返回10列，但需要读的行特别多，另外还需要对这些数据排序。

• 对于如下SQL
  select * from intent_grammar order by update_time limit 10 offset 50000;
  耗时为0.14秒，索引执行计划为索引扫描

MySQL对这条SQL的处理方式为，当作 select * from intent_grammar order by update_time limit 50010;
如果选择全字段排序，需要辅助索引扫描5W行 + 5W次回表查询（每行全部字段）；如果选择rowid排序，需要辅助索引扫描5W（每行id、update_time字段） + 最后100次回表查询；全字段排序有太多随机IO，rowid排序与后面介绍的最优方案是一样的复杂度。

2、优化方式-延迟关联

select * from intent_grammar order by update_time limit 10 offset 3000000; 
## 改写为
select t1.* from intent_grammar t1 inner join (  
    select id from intent_grammar order by update_time limit 10 offset 3000000
) t2 where t2.id = t1.id

MySQL执行分两步，这里MySQL会对inner join两张表的顺序进行优化，由于子查询只有10行，会选择子查询的临时表为驱动表，按照从上到下的顺序，取出临时表中的每一行，对第二张表进行嵌套循环查询（这里会利用聚簇索引），因此不用对最终join出来的结果再按照update_time排序。（参考高性能 MySQL 第三版 214页 MySQL如何做关联查询）

1. 执行 select id from intent_grammar order by update_time limit 10 offset 3000000
   耗时0.74秒（原来是12.8秒）。对辅助索引进行扫描，读取前3000010行数据，每行有id和update_time字段。由于索引已经排序，因此不用额外的排序，然后返回10个id。
2. 对第一步返回的每一个id，去聚簇索引中查询整行数据，每个id有一次随机磁盘IO，共10次随机IO。这条SQL执行时间0.74秒，执行计划：

select t1.* from intent_grammar t1 inner join (  
  select id from intent_grammar order by update_time limit 10 offset 50000  
) t2 where t2.id = t1.id
## 执行计划与上面类似，耗时0.018秒（原来是0.14秒）
## 这条SQL相比优化前的SQL快原因应该是，优化前的SQL用的是全字段扫描，因为max_length_for_sort_data = 1024，intent_grammar表所有字段长度和没有这么大。如果优化前的SQL用的是rowid排序，理论上应该和优化后的耗时是一样的。

参考
https://blog.97it.net/archives/103.html
https://segmentfault.com/a/1190000038704015
https://segmentfault.com/a/1190000038856674