MySQL-索引及执行计划

索引及执行计划

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指针生成root.
(5) B*TREE中,为了进一步优化范围查询,加入了leaf双向指针,non-leaf双向指针.

1.3.4 B*TREE的作用
减少索引IO次数,有效的较少IOPS
减少了随机IO的数量
减少IO量级
1.4 MySQL的 索引组织表(InnoDB) ******

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

前提:
MySQL默认选择主键(PK)列构建聚簇索引BTREE.
如果没有主键,自动选择第一个唯一键的列构建聚簇索引BTREE.
如果以上都没有,会自动按照rowid生成聚簇索引.
说明:
聚簇索引,叶子节点,就是原始的数据页,保存的是表整行数据.
为了保证我们的索引是"矮胖"结构,枝节点和根节点都是只保存ID列值范围+下层指针.
(2) Secondary Index: 辅助(二级)索引

构建过程: alter table t1 add index idx(name)
1.提取name+id列的所有值
2.按照name自动排序,有序的存储到连续的数据页中,生成叶子节点
只提取叶子节点name范围+指针,生成枝节点和根节点
(3) 针对 name列的查询,是如何优化?

select * from t1 where name='bgx';
按照查询条件bgx,来带基于Name列构建的辅助索引进行遍历
理论上读取page为3次,找到主键值
根据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)分析
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) connst(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) 54 Bytes
varchar(5) 54 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);
-- 例子:
-- 哪些情况可以完美应用以上索引.

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 子句顺序创建联合索引.