如何更高效地写sql,还在单调的使用 limit offet ,size

数据前提:导入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;

如何更高效地写sql,还在单调的使用 limit offet ,size

 

通过range方式查询

explain SELECT * from t1 where  id>99000 limit 2

如何更高效地写sql,还在单调的使用 limit offet ,size

 

查看一下执行时间:

[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,还在单调的使用 limit offet ,size

 

 可以发现两条 SQL 的结果并不一样,因此,如果主键不连续,不能使用上面描述的优化方法

 

所以这种改写得满足以下两个条件:

主键自增且连续

结果是按照主键排序的

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

explain SELECT * from t1 ORDER BY a LIMIT 99000,2

查询结果:

如何更高效地写sql,还在单调的使用 limit offet ,size

 

 发现并没有使用 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,还在单调的使用 limit offet ,size

 

 查看一下分析结果:

如何更高效地写sql,还在单调的使用 limit offet ,size

 

 

如何更高效地写sql,还在单调的使用 limit offet ,size

 

 原 SQL 使用的是 filesort 排序,而优化后的 SQL 使用的是索引排序。

总结:

对于其它一些复杂的分页查询,也基本可以按照这两个思路去优化,尤其是第二种优化方式。第一种优化方式需要主键连续,而主键连续对于一个正常业务表来说可能有点困难,总会有些数据行删除的,但是占用了一个主键 id

 

如何更高效地写sql,还在单调的使用 limit offet ,size

上一篇:windows下重命名一个带有前缀"."dot字符的名字的错误问题


下一篇:Easy2Boot——可制作多包含多个原版系统(.iso)的工具