mysql - 第6课 - 分页查询优化

注：当前测试mysql版本：mysql5.7，编码utf8mb4

表结构和添加100万条测试数据的脚本：

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `std_name` varchar(30) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `class_id` int(11) unsigned NOT NULL,
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_std_name_age_class` (`std_name`,`age`,`class_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

--添加测试数据的存储过程
drop procedure if exists proc_insert_student; 
create procedure proc_insert_student()        
begin
  declare i int;                    
  set i=1;                     
  while(i<=1000000)do                 
    insert into t_student(std_name,age,class_id) values(CONCAT((SUBSTRING_INDEX(SUBSTRING_INDEX('Li Lei,Han Mei,Poli',',',(i%3+1)),',',-1)),i), (i%120)+1 ,(i%3)+1);   
    set i=i+1;                       
  end while;
end;

-- 执行存储过程
call proc_insert_student();

分页sql优化

常见分页sql：

EXPLAIN SELECT * from t_student LIMIT 900000,5 order by std_name;

这种分页查询在数据量比较少的时候可以使用，在查询一张大表比较靠后面的数据的时候效率会非常低。上面sql的这种分页查询会先查询出900005条记录，然后抛弃前面的900000条取后面的5条，效率低。

在没有加排序字段的时候，mysql会根据查询字段选择相应的索引，查询字段不同可能排序方式就不同。一般我们可能按id排序、按名称或多个组合字段排序、按创建时间倒序进行分页查询。下面将对这3种情况进行优化。

id主键索引排序分页优化

1.使用id范围查询优化

-- 原sql：
select * from t_student order by id limit 600000,5;
-- 优化后：
select * from t_student t where t.id > 600000 order by id limit 5;

可以看到返回结果一致，原sql有0.2秒的执行时间，优化后的执行时间几乎为0。再来看下执行计划。

因为原sql的type是index，说明需要走整个索引树扫描，而优化后的type是range，范围扫描的性能高于index。

我们删除一行记录：delete from t_student where id = 600003; 再来看效果就跟原sql的结果不一致了。

可见这种优化的效果非常显著，但是也有非常大的局限性，如果数据库的id有被删除，id号不是连续的，就与原sql的返回结果不一致，实际分页中可能会显示重复的数据。这种优化一般很少在生产环境使用。除非你能保证id号是连续的。

2.使用子查询加inner join优化

-- 原sql：
select * from t_student order by id limit 700000,5;
-- 优化后：
select * from t_student t1 inner join (select t2.id from t_student t2 order by t2.id limit 700000,5) t3 on t1.id=t3.id;

看到优化后的查询时长比之前的稍好。再来看一下explain分析为啥会更好。

从explain的table列看到：

1. 第一步执行的t2查询，这个子查询相比原sql只查询了id，舍弃了原sql查询整行数据，这也是提高效率的最大原因，如果行数据越多效果越明显。此处返回5行数据，explain中的那个rows并不准确。
2. 第二步执行<derived2>，也就是t2子查询的衍生表。只有5行数据，row列显示的不准确。5行数据 All 全表扫描效率很高。
3. 第三步和t1的关联查询，拿5行数据的衍生表<derived2>小表驱动大表（后面会讲解）跟 t1 进行关联查询，效率很高。

综上分析耗性能的在第一步，同样查询了700005条数据舍弃前面700000，但是因为只查了id一个字段，从而提高了效率。

注：执行顺序是根据explain的id列，id越大越先执行，id相等时执行前面的。第二节课有讲解。

非主键索引分页优化

组合字段分页查询注意两点：

1.建立组合索引。

2.子查询加 inner join 优化。

 -- 原sql：
 select * from t_student t order by t.std_name asc,age asc limit 700000,5;
 -- 优化后的sql：
 select * from t_student t1 inner join (select id from t_student t2 order by t2.std_name asc,t2.age asc limit 700000,5) t3 on t1.id=t3.id;

组合字段排序分页，一定要给组合字段建立索引，按照最左匹配法则注意索引顺序。此例的索引顺序是std_name,age。

优化后的查询时间效果显著。看看explain：

从上面的explain看到，在优化前排序使用的是filesort，说明没有走索引排序，因为扫描范围比较大，优化器分析后觉得 走索引+回表 的扫描方式不如 全表扫描。

优化后的原理跟上面的一样，t2的子查询只查询了id一个字段，id值包含在索引idx_std_nane_age_class里，使用到了覆盖索引，并且只查询了id一个字段，效率高。

 

按添加时间排序的分页查询优化也和上面的一样，要在添加时间的字段建立索引，使用子查询和inner join查询优化。

总结：

1.对于有序自增id的分页查询，id连续时可以使用id作为分页查询条件，id不连续时使用子查询加inner join主表查询

例：select * from t_student t where t.id > 600000 order by id limit 5;

select * from t_student t1 inner join (select t2.id from t_student t2 order by t2.id limit 700000,5) t3 on t1.id=t3.id;

2.对于非id分页查询，使用子查询找到id，然后inner join主表查询。

例：select * from t_student t1 inner join (select id from t_student t2 order by t2.std_name asc,t2.age asc limit 700000,5) t3 on t1.id=t3.id;