Mysql索引优化实战

1.分页查询优化

插入demo表和测试数据

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='员工记录表';

#插入10万示例数据 
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('zhuge',i),i,'dev'); 
set i=i+1; 
end while; 
end;; 
delimiter ; 
call insert_emp();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

很多时候我们业务系统实现分页功能可能会用如下sql实现

mysql> select * from employees limit 10000,10; 

1
2

表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

>>常见的分页场景优化技巧：

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

首先看一个主键自增且按主键排序的例子：

 select * from employees limit 90000,5;
1

该 SQL 表示查询从第 90001开始的五行数据，没添加单独 order by，表示通过主键排序。我们再看表 employees ，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 90001开始的五行数据，如下：

 select * from employees where id > 90000 limit 5; 
1

结果是一样的：

我们再对比一下执行计划：

explain select * from employees limit 90000,5;
1

explain select * from employees where id>90000 limit 5;
1

可以看出优化后扫描的行数大大缩小，同时查询类型变成了range。

但是，这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图试验所示（先删除几条前面的记录，然后再测试原 SQL 和优化后的 SQL）：

可以看到，当有主键不连续时，两个SQL查询出来的结果是不一样的。

另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满

足以下两个条件：

主键自增且连续
结果是按照主键排序的

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

看一个非主键字段排序的分页查询例子：

select * from employees order by name limit 90000,5;
1

使用explain关键字分析：

explain select * from employees order by name limit 90000,5;
1

可以看到现在采用全局扫描，这种方式的效率是很低的；

如果优化呢？

我们可以考虑采用查询字段覆盖索引的方式来优化，直白地说就是分页查询先查个主键ID，得到的ID列表再进行内联查询出其他我们需要的但未能被索引覆盖的字段；

原因是扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

那么优化后的SQL如下：

select * from employees e inner join(select id from employees order by name limit 90000,5)et on  e.id=et.id; 
1

可以看到结果跟上面是一样的；

同样使用explain关键字分析：

explain select * from employees e inner join(select id from employees order by name limit 90000,5)et on  e.id=et.id;
1

我们可以看到我们的查询现在已经能够覆盖索引了，原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序，当然查询效率也大大提升。

2.Join关联查询优化

先建立Join关联查询优化示例表并插入数据

‐‐ 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 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();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

mysql的表关联常见有两种算法

Nested-Loop Join 算法 -嵌套循环连接算法

Block Nested-Loop Join -基于块的嵌套循环连接算法

2.1 嵌套循环连接Nested-Loop Join(NLJ) 算法

从驱动表中循环读取一行，根据这一行数据的关联字段去关联被驱动表，从被驱动表中或得目标数据跟驱动表的数据合并起来组成结果集合。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
1

从执行计划中分析：

1、t2全表扫描为驱动表，t1为被驱动表；

这里如何确定驱动表还是非驱动表？

在使用inner join时，mysql会自动选择数据量小的表为驱动表，数据量大的表为被驱动表；

在使用left join时，左边的表为驱动表，右边的表为被驱动表；

在使用right join时，右边的表为驱动表，左边的表为被驱动表；

在上面的例子中，t2的数据量小，故而mysql自动选择t2为驱动表；当id相同时，mysql的执行顺序从上到小，从侧面证明了t2为驱动表；

2、一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

上面的大概流程如下：

1、从t2表中读取一行数据（如果有条件，会从条件过滤后的结果读取一行）；

2、从第1步的数据中，取字段a去t1表里面查找关联的记录；

3、将第1、2步的数据合并成结果集；

4、重复上面3步。

在上面的过程中，会先扫描t2的100行数据，再根据关联字段去去扫描t1表，t1表也扫描了100行，总共扫描了200行。

如果关联字段a没有索引的话，那么走NLJ算法效率会很低，此时mysql会选择Block Nested-Loop Join算法。

2.2 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

从驱动表中将所有记录读取到内存块join_buffer中，然后读取被驱动表所有数据，一条条地跟内存块中的数据做比对，满足条件的合并到结果集中。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
1

从执行计划中可以分析：

1、Extra 中出现 Using join buffer 则表示使用的 join 算法是 BLJ；

2、驱动表和被驱动表都进行了全表扫描；

上面的大概流程如下：

1、从t2表中扫描全表数据加入到内存块中；

2、扫描t1全表数据，并选择其中一条记录，将这条记录与内存块中做比对，满足条件的加入结果集中；

在上面的过程中，t2全表扫描了100次，t1全表扫描了10000次，总共扫描了10100次；

同时每次在t1中循环选择的记录都需要去内存块中进行一一比对，所以在内存中总共运算次数为：

10000*100=100万次；

这个驱动表的数据量小，如果驱动表数据量大怎么办？

如果内存中放不下驱动表的所有数据时，会选择分段放，比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

在上面的例子中，如果选择NLJ算法，会直接在磁盘中进行运算，运算次数也是100万次，跟内存运算比较起来，肯定效率要低很多，故而mysql会根据实际情况选择最合适的算法BLJ,而非NLJ。

对于关联sql的优化

关联字段加索引，让mysql做join操作时尽量选择NLJ算法

小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。

straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）

尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

对于小表定义的明确

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

3.count(*)查询优化

总数查询有四种方式：count(*)、count(1)、count(字段)、count(主键 id)；

这四个sql执行效率差不多；

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二

级索引存储数据比主键索引少，所以count(字段)>count(主键 id)

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，

count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。

count() 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count()。

为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。