MySQL的索引及执行计划

类似于一本书中的目录,起到优化查询作用

索引的分类

B树    默认使用的索引类型
R树
Hash
FullText 
GIS 索引

Btree索引功能分类

聚集索引

如果有PK,MySQL默认使用PK作为聚簇索引
将来存储数据时,能够保证所有数据行按照主键列的顺序存储到磁盘页中.
自动生成聚集索引树
如果没有PK,会自动选择唯一键(UK)
什么都没有,会自动按照时间戳的hash值作为聚簇索引.
一张表中只能有一个聚簇索引.
建议是数字的自增列最佳.

构建过程: 
(1) 在建表时,设定了主键.MySQL InnoDB 自动将主键作为聚簇索引列
(2) 在存储数据时,会按照聚簇索引组织存储数据
(3) InnoDB引擎会将数据行所在的数据页作为叶子节点
(5) 提取每个叶子节点的最小值,生成枝节点,保留叶子节点指针
(6) 提取枝节点的最小值和指针,最终生成根节点

辅助索引

按照业务的查询特点,一般经常需要where  group  order by 条件列
创建辅助索引.
构建过程:
(1) 将索引键值进行自动排序(默认从小到大排序)
(2) 将排好序的键值+PK,生成叶子节点(16KB)
(3) 提取每个叶子节点的最小值,生成枝节点,保留叶子节点指针
(4) 提取枝节点的最小值和指针,最终生成根节点

查询过程:
一旦我们拿索引键值作为条件发起查询
(1) 按照Btree查找算法,找到辅助索引叶子节点,获取到PK值
(2) 按照PK的值回表,通过聚簇索引Btree进一步找到具体数据行

聚集索引和辅助索引的区别

表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
在一张表中,聚集索引只能有一个,一般是主键.
辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
聚集索引,叶子节点存储的时有序的整行数据.
MySQL的表数据存储是聚集索引组织表

辅助索引细分

#普通的单列辅助索引
#联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
#唯一索引
索引列的值都是唯一的.

索引数高度

索引树高度应当越低越好,一般维持在3-4最佳
#数据行数较多
分表 : parttion  用的比较少了.
分片,分布式架构.
#字段长度
业务允许,尽量选择字符长度短的列作为索引列
业务不允许,采用前缀索引.
#数据类型
char 和 varchar 
enum 

索引的命令操作

#查询索引
mysql> desc city;
mysql> show index from city\G

PRI   ==> 主键索引 
MUL   ==> 辅助索引
UNI   ==> 唯一索引 

#创建索引
#单列的辅助索引
mysql> alter table city add index idx_name(name);

#多列的联合索引
mysql> alter table city add index idx_c_p(countrycode,population);

#唯一索引
mysql> alter table city add unique index uidex_dir(district);

#前缀索引
mysql> alter table city add index idx_dir(district(5));

#删除索引
mysql> alter table city drop index idx_name;

压力测试

mysql> create database test;
mysql> use test
mysql> source /root/t100w.sql

#未做优化之前测试
[root@mysql ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.

Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 1323.035 seconds
	Minimum number of seconds to run all queries: 1323.035 seconds
	Maximum number of seconds to run all queries: 1323.035 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20

[root@mysql ~]# 

#做优化之后测试
mysql> alter table t100w add index idx_k2(k2);

[root@mysql ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 2.794 seconds
	Minimum number of seconds to run all queries: 2.794 seconds
	Maximum number of seconds to run all queries: 2.794 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20

[root@mysql ~]# 

执行计划分析

#作用
将优化器 选择后的执行计划 截取出来.便于管理管判断语句得执行效率.
#获取执行
desc   SQL语句
explain SQL 语句

mysql> desc select * from test.t100w where k2='MN89';
mysql> explain select * from test.t100w where k2='MN89';

#分析执行计划
#table        	表名
#type				
查询的类型:
全表扫描	 	: ALL 
索引扫描    	: index,range,ref,eq_ref,const(system),NULL

index:全索引扫描
mysql> desc select id from city;

range:索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;

对于辅助索引来讲,!= 和not in等语句是不走索引的
对于主键索引列来讲,!= 和not in等语句是走range

ref: 辅助索引等值查询
mysql> desc  select  * from city where countrycode='CHN'  union all  select  * from city where countrycode='USA';

eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件
A join B on a.x = B.y 
mysql> desc select b.name,a.name,a.population from city as a join country as b on a.countrycode=b.code where a.population<100;

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

#possible_key:可能会用到的索引

#key: 真正选择了哪个索引

#key_len: 索引覆盖长度
varchar(20)  utf8mb4
1. 能存20个任意字符
2. 不管存储的时字符,数字,中文,都1个字符最大预留长度是4个字节
3. 对于中文,1个占4个字节 
4. 对于数字和字母,1个实际占用大小是1个字节
select length() from test;

#Extra
出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引
order by
group by
distinct 
union 
关注key_len应用的长度

联合索引

#联合索引应用细节
只要我们将来的查询,所有索引列都是<等值>查询条件下,无关排列顺序 
唯一值多的列放在最左侧
abcd 
acbd
adbc
acbd
等等
mysql> alter table test add index idx(k1,k2,k3,k4);
mysql> desc  select * from test where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';
mysql> desc  select * from test where  k2='中国' and k3='aaaa' and k4='中国你好' and k1='aa';

原因: 优化器,自动做查询条件的排列

#不连续部分条件 
cda   ----> acd   ---> a  -----> idx(c,d,a)
dba   ----> abd   ---> ab ---->  idx(d,b,a)

#在where查询中如果出现> < >= <= like 
(1)
mysql> alter table test add index idx1(k1,k3,k4,k2);
(2) 
mysql> desc  select * from test where k1='aa'  and k3='aaaa' and k4='中国你好' and  k2>'中国';

#多子句 	查询,应用联合索引
mysql> alter table test add index idx3(k1,k2);
mysql> desc select * from test where k1='aa' order by k2;

不走索引的情况

(1) 语句本身就是全表扫描
	select *  from city;
	select * from city where 1=1;
(2) 查询条件没建索引
mysql> desc select * from city where name='dalian';
(3) 查询条件不满足索引应用逻辑
mysql> desc select * from city where countrycode like '%CH%';
如果业务中有大量的需求,可以使用ES
<>,not in()
(5) 查询条件中出现计算
mysql> desc select * from city where id-1=9;

(6) 出现隐式转换
mysql> desc select * from t1 where telnum=110;
mysql> desc select * from t1 where telnum='110';

索引应用规范

1.建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 列值经常变化,没必要建索引,小表可以不用建索引.
(7) 索引维护要避开业务繁忙期(pt-toolkit)