六、mysql索引优化实战二

一、myql索引底层数据结构与算法
二、mysql——Explain工具介绍
三、mysql——索引最佳实践
四、mysql索引优化实战
五、mysql——常见sql语句优化
六、mysql索引优化实战二
七、mysql事务隔离机制和锁机制

分页查询优化

不太好的分页查询写法：

SELECT * from employees limit 10000,10;

explain执行计划结果：

分析：这条sql看似只查询了10条记录，实际上会读取10010条记录，然后抛弃前10000条，保留你需要的后10条，执行效率低。

优化方案：

1、根据自增且连续的主键排序的分页排序
注：该优化有两个前提：
（1）主键自增且连续
（2）结果是按照主键排序
否则优化后的sql执行结果与优化前不一致。
优化后的sql

SELECT * from employees where id>10000 limit 10;

执行计划结果：

分析：优化后的sql利用了主键索引，而且扫描的行数大大减少，提高了执行效率。

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

现实的业务场景中按照name排序的情况也很常见。
优化前的sql：

SELECT * from employees ORDER BY name limit 9000,5;

执行计划：

分析：通过执行计划，可以看出这条sql没有使用任何索引，而且使用了filesort这种低效率的排序方式。
虽然name字段上有联合索引，但是mysql通过内部cost计算，发现使用走name索引比全表扫描效率还低。

优化方案：

让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。
优化后的sql，真的巧妙

SELECT * from employees e INNER JOIN (SELECT id from employees ORDER BY name LIMIT 9000,5) ed on e.id=ed.id;

执行计划：

分析：这里没有使用filesort排序，而且可以看到走了索引，提高了执行效率。

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;; 
25 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表关联的Nested-Loop Join算法与Block Nested-Loop Join算法

1、Nested-Loop Join ——嵌套循环连接算法
一次一行地从第一张表（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表中（被驱动表）里取出满足条件的行，然后取出两张表的结果集合。

EXPLAIN SELECT * from t1 INNER JOIN t2 on t1.a=t2.a

分析：驱动表表是t2，被驱动表是t1；mysql优化的时候一般会选择**小表作为驱动表。**总共扫描了200行。
left join：规定了左边是驱动表；
right join：规定了右边是驱动表；
注：一般join语句，如果执行计划中没有出现“Using join buffer”则表示使用的join算法是NLJ。

2、基于快的嵌套循环连接 ——Block Nested-Loop Join算法
把驱动表的数据读取到join buffer中，然后扫描被驱动表，把被驱动表中的每一行取出来跟join buffer中的数据做对比.

EXPLAIN SELECT * from t1 INNER JOIN t2 on t1.b=t2.b

分析：扫描的总行数：10000+100；以及在buffer中100万次比对。
join buffer的大小有参数join_buffer_size设定，默认值是256k，如果放不下，则分段存放。

优化方案：

（1）关联字段加索引：让mysql做join时尽量选择NLJ算法；
（2）小表驱动达标，必要时可以使用straight join确定驱动表和被驱动表；

in和exsits优化

原则：小表驱动大表

in：当B表的数据小于A表的时候，in优于exists；

select * from A where id in (select id from B)

等价于：

for(select id from B){
	select * from A where A.id = B.id
}

exists:当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id=A.id)

将主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定主查询的数据是否保留。
等价于：
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
}

count(*)查询优化

对比以下四种查询语句:

EXPLAIN SELECT * from t1 INNER JOIN t2 on t1.b=t2.b

EXPLAIN select count(1) from employees;

EXPLAIN select count(id) from employees;

EXPLAIN select count(name) from employees;

EXPLAIN select count(*) from employees;

注意：以上4条sql只有根据某个字段count时，不会统计字段为null值的数据行。

count（1）和count（字段）执行过程类似，不过count（1）不用取出字段，所以快一些。
count（*）mysql并不会取出所有的字段，而是做了优化，效率有count（1）差不多。
count（id）本来应该走主键索引，但是mysql做了优化，如果有二级会走二级索引，因为二级索引数据量小，效率高。

优化方案：

（1）对行数只需要知道个大概

show table status

(2) 将总数维护到redis中
但是很难保证表操作和redis操作的事物一致性

（3）增加数据计数器