分页查询优化
示例表:
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('lisi',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();
很多时候我们业务系统实现分页功能可能会用如下sql实现:
select * from employees limit 10000,10;
表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录,实际这条 SQL 是先读取 10010 条记录,然后抛弃前 10000 条记录,然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据,执行效率 是非常低的。
>>常见的分页场景优化技巧:
1、根据自增且连续的主键排序的分页查询
首先来看一个根据自增且连续主键排序的分页查询的例子:
mysql> select * from employees limit 90000,5;
该 SQL 表示查询从第 90001开始的五行数据,没添加单独 order by,表示通过主键排序。我们再看表 employees ,因 为主键是自增并且连续的,所以可以改写成按照主键去查询从第 90001开始的五行数据,如下:
mysql> select * from employees where id > 90000 limit 5;
查询的结果是一致的。我们再对比一下执行计划:
mysql> EXPLAIN select * from employees limit 90000,5;
mysql> EXPLAIN select * from employees where id > 90000 limit 5;
显然改写后的 SQL 走了索引,而且扫描的行数大大减少,执行效率更高。 但是,这条 改写的SQL 在很多场景并不实用,因为表中可能某些记录被删后,主键空缺,导致结果不一致,如下图试验 所示(先删除一条前面的记录,然后再测试原 SQL 和优化后的 SQL):
两条 SQL 的结果并不一样,因此,如果主键不连续,不能使用上面描述的优化方法。 另外如果原 SQL 是 order by 非主键的字段,按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满 足以下两个条件:
- 主键自增且连续
- 结果是按照主键排序的
2、根据非主键字段排序的分页查询
再看一个根据非主键字段排序的分页查询,SQL 如下:
mysql> select * from employees ORDER BY name limit 90000,5;
mysql> EXPLAIN select * from employees ORDER BY name limit 90000,5;
mysql> select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
需要的结果与原 SQL 一致,执行时间减少了一半以上,我们再对比优化前后sql的执行计划:
原 SQL 使用的是 filesort 排序,而优化后的 SQL 使用的是索引排序。
Join关联查询优化
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;
create table t2 like t1
‐‐ 插入一些示例数据
‐‐ 往t1表插入1万行记录
drop procedure if exists insert_t1
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
‐‐ 往t2表插入100行记录
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t2();
mysql的表关联常见有两种算法
1. Nested-Loop Join 算法
2. Block Nested-Loop Join 算法
1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法
mysql> EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
mysql>EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
in和exsits优化
select * from A where id in (select id from B)
#等价于:
for(select id from B){
select * from A where A.id = B.id
}
select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select * from A){
select * from B where B.id = A.id
}
#A表与B表的ID字段应建立索引
count(*)查询优化
# 临时关闭mysql查询缓存,为了查看sql多次执行的真实时间
mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;
mysql> EXPLAIN select count(1) from employees;
mysql> EXPLAIN select count(id) from employees;
mysql> EXPLAIN select count(name) from employees;
mysql> EXPLAIN select count(*) from employees;
注意:以上4条sql只有根据某个字段count不会统计字段为null值的数据行
![](https://img-blog.csdnimg.cn/b2e78eb6445746628519b0c448020bde.png)
常见优化方法
![](https://img-blog.csdnimg.cn/bcbed3638442474989cfb90b8daa8d2f.png)
对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制),查询count需要实时计算
![](https://img-blog.csdnimg.cn/fd2a20e8950c49758db19b0289970eb3.png)