MySQL索引及执行计划

1.索引及执行计划

1.1 介绍

相当于一本书中的目录,优化查询.

1.2 MySQL索引的类型(算法)

BTREE  (Banlance Tree)  ******
HASH
FULLTEXT
RTREE  
GIS

1.3 索引算法的演变

1.3.1 BTREE算法 由来
BTREE讲究的是查找数据的平衡,让我们的查询可以快速锁定范围

1.3.2 BTREE 的增强之路
B-TREE  ------> 叶子节点双向指针 ------> 非叶子结点双向指针 -----> B*TREE

1.3.3 BTREE 数据结构构建过程 *****
(1) 数据排序(默认是从小到大)
(2) 将数据有序的存储到16KB数据页,生成叶子(leaf node)节点.
(3) 通过叶子节点范围(最小值到下个叶子节点最小值)+每个叶子节点指针生成non-leaf.
(4) 通过non-leaf节点的范围(最小值到下个non-leaf节点最小值)+每个 non-leaf指针生成根节点
(5) B*TREE中,为了进一步优化范围查询,加入了leaf双向指针,non-leaf双向指针.

 	 1. 减少索引IO次数,有效的较少IOPS
     2. 减少了随机IO的数量
     3. 减少IO量级

1.4 MySQL的 索引组织表(InnoDB) ******

(1) Clusterd  Index: 聚簇(聚集,集群)索引
前提: 

1.MySQL默认选择主键(PK)列构建聚簇索引BTREE.
2.如果没有主键,自动选择第一个唯一键的列构建聚簇索引BTREE.
3.如果以上都没有,会自动选择隐藏的rowid生成聚簇索引.
4.聚簇索引只能有一个

说明: 
1.聚簇索引,叶子节点,就是原始的数据页,保存的是表整行数据.
2.为了保证我们的索引是"矮胖"结构,枝节点和根节点都是只保存ID列值范围+下层指针.

(2) Secondary Index: 辅助(二级)索引
构建过程: alter table t1  add index idx(name)
	1.提取name+id列的所有值
	2.按照name自动排序,有序的存储到连续的数据页中,生成叶子节点
	3. 只提取叶子节点name范围+指针,生成枝节点和根节点

(3) 针对 name列的查询,是如何优化?
select * from t1 where name='bgx';
    1. 按照查询条件bgx,来带基于Name列构建的辅助索引进行遍历
		理论上读取page为3次,找到主键值
	2. 根据ID值,回到聚簇索引树,继续遍历,进而找到所需数据行.
		理论读取的数据页为3次.

1.5 辅助索引细分

1.5.1 单列 
1.5.2 联合索引  *****
例如:
idx(a,b,c) 
理论上可以有效的避免回表的次数.
1.5.3 唯一索引 
手机号,身份证号类似的列. 
理论上通过唯一索引作为遍历条件的话,读取6个page即可获取数据行.

1.6 索引树高度问题,影响的原因?

(1) 数据行数多.
	分区表(现在用的少).
	归档表.
	分库分表
(2) 选取的索引列值过长
	前缀索引.
	test(10)
(3) varchar(64)  char(64)   enum()等数据类型的影响

1.7 索引管理操作

1.7.1 查询索引

desc city;
key:   
	PRI : 主键
	UNI : 唯一键
	MUL : 普通
mysql> show index from city\G
select 
table_schema,table_name,
column_name ,
data_type,Column_key ,
COLUMN_COMMENT from information_schema.columns 
WHERE table_schema NOT IN ('sys','informatiion_schema','performance_schema','mysql');

1.7.2 创建索引

例子: 
-- 1. 单列索引例子
select * from city where population>10000000
索引设计:
mysql> alter table city add index idx_popu(population);

说明:  
	1. 作为 where 查询条件的列.
	2. 经常作为 group by ,order by,distint,union的列创建索引.

-- 2. 联合索引例子 
select * from city where district='shandong' and name='jinan';

索引设计: 
mysql> alter table city add index idx_dis_name(district,name);

说明: 
联合索引排列顺序,从左到右.重复值少的列,优先放在最左边.

-- 3. 前缀索引应用(字符串)
mysql> alter table city add index idx_name(name(5));

-- 4. 唯一索引 
mysql> alter table student add unique index idx_tel(xtel);
mysql> desc student;

1.7.3 删除索引

mysql> alter table city drop index idx_dis_name;

2.执行计划(explain) desc分析

2.0 命令

• explain select
• desc select

2.1 使用场景

(1) 语句执行之前 :  防患未然
(2) 出现慢语句时 :  亡羊补牢

2.2 执行计划结果查看(优化器选择后的执行计划)

mysql> desc select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> 

2.3 重点关注指标说明

table:  发生在哪张表的执行计划.
type :  查询的类型
		全表扫描 : ALL
		索引扫描 : index < range < ref < eq_ref < connst(system)< NULL   *****
possible_keys : 可能用到的索引
key           : 此次查询走的索引名.
key_len  : 索引覆盖长度.为了评估联合索引应用长度的.     *****
rows     : 扫描了表中的多少行
Extra    : 额外的信息                               **** 

2.4 type

(1) ALL       :  全表扫描 
mysql> desc select * from city;
mysql> desc select * from city where 1=1 ;
mysql> desc select * from city where population=42;
mysql> desc select * from city where countrycode !='CHN';
mysql> desc select * from city where countrycode not in ('CHN','USA');
mysql> desc select * from city where countrycode like '%CH%';


(2) index     : 全索引扫描 
mysql> desc select countrycode from city;

(3) range     : 索引范围扫描(最常见)

>   <   >=  <=  like  
>   in or

mysql> desc select  *  from city where id<10;
mysql> desc select * from city where countrycode like 'CH%';

mysql> desc select * from city where countrycode  in ('CHN','USA');
改写: 
desc 
select * from city where countrycode='CHN'
union all 
select * from city where countrycode='USA'
可以看出比上一个效果好，一次升级改造

(4) ref  辅助索引等值查询
desc 
select * from city where countrycode='CHN';

(5) eq_ref 多表关联查询中,非驱动表的连接条件是主键或唯一键
desc 
select 
city.name,
country.name ,
city.population 
from city 
join country 
on city.countrycode=country.code
where city.population<100;

(6) const(system) :主键或者唯一键等值查询
mysql> desc select * from city where id=10;

(7) NULL  索引中获取不到数据 
mysql> desc select * from city where id=100000;

2.5 key_len详细说明

2.5.1 作用
判断联合索引覆盖长度

2.5.2 最大覆盖长度的计算方法
idx(a,b,c)    ====> a(10)+b(20)+c(30)

(1) 影响计算的条件 
字符集 : utf8mb4

数字类型 
tinyint    1 Bytes
int        4 Bytes 
bigint     8 Bytes 

字符串类型
char(5)    5*4 Bytes
varchar(5) 5*4 Bytes + 2 Bytes

没有 not null  :  多一个字节存储是否为空

测试表:

create table keyt (
id int not null primary key auto_increment,
num int not null, 
num1 int ,
k1 char(10) not null ,
k2 char(10) , 
k3 varchar(10) not null ,
k4 varchar(10)
)charset=utf8mb4;

num :  4 
num1:  5
k1  :  40 
k2  :  41
k3  :  42 
k4  :  43

2.5.3 联合索引应用   *****
-- 建立联合索引时,最左侧列,选择重复值最少的列.
alter table keyt add index idx(a,b,c);

-- 例子:
-- 哪些情况可以完美应用以上索引.
mysql> alter table student add index idx_test(xname,xage,xsex);

desc select *from student where xname='张三' and xage=11 and xgender='m';
desc select *from student where xage=11 and xgender='m' and xname='张三' ;
desc select *from student where xgender='m' and xname='张三' and xage=11 ;

-- 影响到联合索引应用长度的.
-- 缺失 联合索引最左列,不走任何索引
mysql> desc select *from student where xage=11 and xgender='m'  ;

-- 缺失中间部分,只能走丢失部分之前的索引部分
mysql> desc select *from student where xname ='张三'  and xgender='m'  ;

-- 查询条件中,出现不等值查询(> ,< ...like )
mysql> desc select *from student where xname ='张三' xage<18 and xgender='m'  ;
联合索引应用长度到不等值列截断了.

-- 多子句
按照 select 子句顺序创建联合索引.s
mysql> desc select * from student where xname='张三' order by xage;

2.6聚簇索引和辅助索引区别和联系

区别: 

1. 一般选择主键生成聚簇索引,一张表只能一个 ,没有主键选择唯一键,都没有选择隐藏rowid,自动生成隐藏聚簇索引.
2. 聚簇索引叶子节点,存储的是整行的表数据.枝节点和根节点,叶子节点ID值的范围.
3. 辅助索引,可以有多个.
4. 辅助索引,叶子节点,存储的是索引列值+主键.

关系:
执行查询时,select * from t1 where name='bgx';
	1.  首先根据name的索引,快速锁定bgx的主键ID
	2.  根据ID列值回表查询聚簇索引,获取整行.

2.7 管理

show  index from city;
alter table city add index idx_name(name);
alter table city add index idx_a_b_c(a,b,c);
alter table city add index idx_a(a(10));
alter table city add unique index idx_a(a(10));
alter table city drop index idx_name;	

1. 	explain  / desc 
    type :  ALL   index range  ref  eq_ref  const(system)  NULL 

key_len
联合索引应用   *****
-- 建立联合索引时,最左侧列,选择重复值最少的列.
alter table keyt add index idx(a,b,c);
-- 例子:
-- 哪些情况可以完美应用以上索引.
desc select *from student where xname='张三' and xage=11 and xgender='m';
desc select *from student where xage=11 and xgender='m' and xname='张三' ;
desc select *from student where xgender='m' and xname='张三' and xage=11 ;

-- 影响到联合索引应用长度的.
-- 缺失 联合索引最左列,不走任何索引
mysql> desc select *from student where xage=11 and xgender='m'  ;
-- 缺失中间部分,只能走丢失部分之前的索引部分
mysql> desc select *from student where xname ='张三'  and xgender='m'  ;
-- 查询条件中,出现不等值查询(> ,< ...like )
mysql> desc select *from student where xname ='张三' xage<18 and xgender='m'  ;
联合索引应用长度到不等值列截断了.
-- 多子句
按照 select 子句顺序创建联合索引.


mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

3. 索引应用规范

1.1 创建索引的条件

(1) 必须要有主键,建议是自增长的ID列
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 唯一值多的列作为联合索引最左列.
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
(7) 经常更新的列不要建索引

mysql> desc select * from world.city where countrycode='chn' order by population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

出现Using filesort时，比较影响性能；说明表没有走索引或者是单列索引
解决方案：多子句联合索引优化：
mysql> alter table city add index idx_co_po(countrycode,population);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc select * from world.city where countrycode='chn' order by population;

1.2 开发规范

(1) 没有查询条件，或者查询条件没有建立索引
mysql> desc select * from city; 
mysql> desc select * from city where true;
mysql> desc select * from city where 1=1;
mysql> desc select * from city where name='jinan';
mysql> desc select *from student where xage=11 and xgender='m'  ;

(2) 查询结果集是原表中的大部分数据，应该是20-30%以上。
1000w   200w-300w  ----> 有可能导致索引失效.
解决方案:  给范围查询增加上限和下限

(3) 索引本身失效，统计数据不真实,更新不及时
前几天运行的很快,突然有一天慢了.
desc select * from city where name='jinan';
解决方案: 
		1. 手工触发更新统计信息
		ANALYZE TABLE city;
		optimize table city;
		2. 重建索引

(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
mysql> desc select * from city where id-1=9;

(5) 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
mysql> desc  select * from tab where telnum='110';

(6) <> ，not in 不走索引（辅助索引）
(7) like "%_" 百分号在最前面不走
(8) 联合索引规范
联合索引(a,b,c) ---->   bc  ---> c  不走任何索引   
联合索引(a,b,c) ---->  ac   只能走部分 
联合索引(a,b,c)  中间出现不等值(> <  like)

1.3 AHI 自适应hash索引

mysql> select @@innodb_adaptive_hash_index;
Adaptive Hash Indexes 原理 
InnoDB存储引擎会监控对二级索引的查找，如果发现某一个二级索引被频繁访问，二级索引成为一个热数据。那么此时建立hash索引可以带来速度的提升	经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。
查看使用状况：
show engine innodb status ;
可以通过观察show engine innodb status结果中的SEMAPHORES部分来决定是否使用自适应哈希索引。如果你看到很多线程都在btr0sea.c文件上创建rw-latch上waiting，那么建议关闭掉自适应哈希索引。高并发模式下AHI引起的竞争，需要关闭AHI.



设置参数
innodb_adaptive_hash_index=on/off

1.4 MySQL Insert Buffer技术

插入缓冲技术，对于非聚集类索引的插入和更新操作，不是每一次都直接插入到索引页中，而是先插入到内存中。具体做法是：如果该索引页在缓冲池中，直接插入；否则，先将其放入插入缓冲区中，再以一定的频率和索引页合并，这时，就可以将同一个索引页中的多个插入合并到一个IO操作中，大大提高写性能。
这个设计思路和HBase中的LSM树有相似之处，都是通过先在内存中修改，到达一定量后，再和磁盘中的数据合并，目的都是为了提高写性能
那么插入缓冲如何减少随机IO的呢？每个一段时间，insert buffer会去合并在insert buffer中的二级非唯一索引。通常情况下，它会合并N个修改到同一个btree索引的索引页中，从而节约了很多IO操作。经测试，insert buffer可以提高15倍的插入速度。
在事务提交后，insert buffer可能还在合并写入。所以，假如当DB异常重启，reovery阶段，当有非常多的二级索引需要更新或插入时，insert buffer将可能花费很长时间，甚至几个小时。在这个阶段，磁盘IO将会增加，那么就会导致IO-Bound类型的查询有显著的性能下滑。

1.5Index Condition Pushdown (ICP)

mysql使用索引从表中检索行数据的一种优化方式，MySQL5.6开始支持
MySQL 5.6之前，存储引擎会通过遍历索引定位基表中的行，然后返回给Server层，再去为这些数据行进行WHERE后的条件的过滤。
mysql 5.6之后支持ICP后，如果WHERE条件可以使用索引
MySQL 会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。
ICP能减少引擎层访问基表的次数和 Server层访问存储引擎的次数。

联合索引(a,b,c) ---->  ac   只能走部分 
没有ICP 
a --->  从磁盘拿满足a条件的数据 加载到内存  ,再C过滤想要的结果   =====> SQL层 ---> 
有ICP
a ---->    a +  c  =====>     SQL  层


mysql> SET  @@optimizer_switch='index_condition_pushdown=on
mysql> show variables like 'optimizer_switch%' \G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

1.6MRR 的全称是 Multi-Range Read

Optimization，是优化器将随机 IO 转化为顺序 IO 以降低查询过程中IO开销的一种手段，这对IO-bound类型的SQL语句性能带来极大的提升，适用于range ref eq_ref类型的查询
MRR优化的几个好处
使数据访问有随机变为顺序，查询辅助索引是，首先把查询结果按照主键进行排序，按照主键的顺序进行书签查找
减少缓冲池中页被替换的次数
批量处理对键值的操作

mysql> SET  @@optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> show variables like 'optimizer_switch%' \G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

1.7针对多表连接查询

Simple Nested Loops Join（SNL），简单嵌套循环算法
Index Nested Loops  Join（INL），索引嵌套循环连接
Block Nested Loops  Join（BNL），块嵌套循环连接
Batched Key Access  join(BKA) ,  BNL+MRR

说明:  

1. batched_key_access=on
2. mrr必须开启 ,mrr=on,mrr_cost_based=off
3. 被驱动表,关联列必须有索引.

作用: 

1. 减少了 Nested Loops 次数
2. 将扫描非驱动表时,可以将大量的随机IO转变为顺序IO


A
id   name   age      
1     zs     12
2     l4     13 
3     w5     14


B 
id    addr   telnum 
1     bj      110
2     sh      120 
3     tj      119    

select name,age,telnum
from a  join b  
on A.id=b.id
where name like  '张%'


提高表join性能的算法。当被join的表能够使用索引时，就先排好顺序，然后再去检索被join的表，听起来和MRR类似，实际上MRR也可以想象成二级索引和 primary key的join
如果被Join的表上没有索引，则使用老版本的BNL策略(BLOCK Nested-loop)

SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

mysql> show variables like 'optimizer_switch%' \G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on, ,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)