数据前提:导入sql,执行一下数据表
CREATE TABLE `t1` ( /* 创建表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; /* 如果存在存储过程insert_t1,则删除 */
delimiter ;;
create procedure insert_t1() /* 创建存储过程insert_t1 */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=100000)do /* 对满足i<=100000的值进行while循环 */
insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段,值都为i当前的值 */
set i=i+1; /* 将i加1 */
end while;
end;;
delimiter ; /* 创建批量写入100000条数据到表t1的存储过程insert_t1 */
call insert_t1(); /* 运行存储过程insert_t1 */
先看看我们平时的sql分页执行过程:
select a,b,c from t1 limit 10000,10;
表示从表 t1 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录,实际这条 SQL 是先读取 10010 条记录,然后抛弃前 10000 条记录,然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据,执行效率是非常低的。本节内容就一起研究下,是否有办法去优化分页查询。
1.根据自增且连续主键排序的分页查询(结果没有走索引)
explain select * from t1 limit 99000,2;
通过range方式查询
explain SELECT * from t1 where id>99000 limit 2
查看一下执行时间:
[SQL] select * from t1 limit 99000,2;
受影响的行: 0
时间: 0.033s
[SQL]
SELECT * from t1 where id>99000 limit 2
受影响的行: 0
时间: 0.001s
总结:
第一条limit方式的SQL 中 key 字段为 NULL,表示未走索引,rows 显示 100164,表示扫描的行数 100164行;
改写后的 SQL key 字段为 PRIMARY,表示走了主键索引,扫描了999行。
显然改写后的 SQL 执行效率更高。
但是呢,有一种情况是这样的,主键id不连续的情况下
我们删除一条数据
delete from t1 where id=8
可以发现两条 SQL 的结果并不一样,因此,如果主键不连续,不能使用上面描述的优化方法
所以这种改写得满足以下两个条件:
主键自增且连续
结果是按照主键排序的
2.查询根据非主键字段排序的分页查询
explain SELECT * from t1 ORDER BY a LIMIT 99000,2
查询结果:
发现并没有使用 a 字段的索引(key 字段对应的值为 null),具体原因:扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高,所以优化器放弃使用索引
新的写法:select * from t1 f inner join (select id from t1 order by a limit 99000,2)g on f.id = g.id;
查看一下分析结果:
原 SQL 使用的是 filesort 排序,而优化后的 SQL 使用的是索引排序。
总结:
对于其它一些复杂的分页查询,也基本可以按照这两个思路去优化,尤其是第二种优化方式。第一种优化方式需要主键连续,而主键连续对于一个正常业务表来说可能有点困难,总会有些数据行删除的,但是占用了一个主键 id