MySQL基础优化(5)：优化分页查询

有时业务遇到分页查询的需求，如

select a,b,c from t1 limit 10000,10;

上述语句从t1表中取出从10001条记录开始的10行记录。该语句的实际执行过程如下，

1. 先读取10010条记录
2. 抛弃前10000条记录，返回后10条记录

查询一张大表比较靠后的数据时，执行效率是非常低的。

创建一张大表，

use test;                       
drop table if exists t1;        

CREATE TABLE t1 (            
  id int(11) NOT NULL auto_increment,
  a int(11) DEFAULT NULL,
  b int(11) DEFAULT NULL,
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (id),
  KEY idx_a (a),
  KEY idx_b (b)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;	

drop procedure if exists insert_t1; 

delimiter ;;
create procedure insert_t1()        
begin
  declare i int;                    /
  set i=1;                          
  while(i<=100000)do                  
    insert into t1(a,b) values(i, i); 
    set i=i+1;                      
  end while;
end;;
delimiter ;                 

call insert_t1();           

根据自增且连续的主键排序的分页查询

下面两个语句进行测试，

explain select * from t1 limit 99000,2;
explain select * from t1 where id > 99000 limit 2;

两条语句查询的结果是一致的，


分析结果如下，

key项的值显示，改写后的SQL只用了主键索引，相应的row项的值比未使用主键索引时小了很多。显然改写后，SQL的执行效率更高。

但是，这条SQL语句在很多场景下并不实用，因为表中可能某些记录被删除后，主键空缺，导致表中的主键并不连续。
假设先删除表中的一条记录，

delete from t1 where id = 10;

此时再使用之前的两个语句进行查询，

select * from t1 limit 99000,2;
select * from t1 where id > 99000 limit 2;

返回结果如下，

两条语句返回的结果不一致。如果主键不连续，不能使用这种优化方法。

此外，如果原来的SQL不是依据主键进行排序，而是依据其他非主键字段进行排序时，这种方法返回的结果也是不同的。

所以，将limit改写为范围查询的条件必须满足，

1. 主键自增且连续
2. 结果是按照主键进行排序

根据非主键字段排序的分页查询

测试语句如下，

select * from t1 order by a limit 99000, 2;

返回结果，

使用explain对该语句进行分析，

该分页查询并没有使用字段a的索引(key项对应值为NULL)，原因可以参考order by优化技巧2。

具体的做法是让排序时，返回的字段尽可能少，且返回的字段尽可能都有索引。可以让排序和分页查询操作先查出主键，在根据主键查到对应记录。

将SQL语句改写如下，

select * from t1 f join (select id from t1 order by a limit 99000, 2)g on f.id = g.id;

返回结果如下，

使用explain分析的结果如下，

分析结果显示，该语句使用了索引排序。

对于更复杂的分页查询，也基本可以按照这个思路去优化。因为第一种优化思路的约束过大，在很多实际需求中并不满足条件，以至于无法使用。