浅谈MySQL索引以及执行计划

🐪索引的作用

类似一本书的目录，起优化查询作用

🐫索引的分类（算法）

1. B树 默认使用的索引类型
2. R树
3. Hash
4. FullText
5. GIS索引（MongoDB 谷歌 百度地图等）

🦙BTREE索引算法演变

B-Tree 只有根节点、枝节点和叶子节点
B+Tree 在B-Tree基础上增加了data（粉色部分）双向指针
B*Tree 在B+Tree基础上增加了各枝节点间的双向指针

🦒Btree索引功能上的分类

4.1 辅助索引

(1) 提取索引列的所有值，进行排序
(2) 将排好序的值，均匀的存放在叶子节点，进一步生成枝节点和根节点
(3) 在叶子节点中的值，都会对应存储主键ID

4.2 聚集索引

(1) MySQL会自动选择主键作为聚集索引列，没有主键会选择唯一键，如果都没有会生成隐藏的键
(2) MySQL进行存储数据时，会按照聚集索引列值得顺序，有序的存储数据行
(3) 聚集索引直接将原表数据页，作为叶子节点，然后提取聚集索引列向上生成枝和根

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

(1) 表中任何一个列都可以创建辅助索引列（索引名字不能重复）
(2) 在一张表中，聚集索引只能有一个，一般是主键（没有主键则选择唯一键，没有唯一键则会在底层默认生成一个）
(3) 辅助索引，叶子节点只存储索引列的有序值+聚集索引列值
(4) 聚集索引，叶子节点存储的是有序的整行数据
(5) MySQL 的表数据存储时聚集索引组织表

🐘辅助索引分类

单列辅助索引
联合索引（覆盖索引）
唯一索引

🦏索引树高度

索引树高度越低越好，一般维持在3-4行
数据行数较多优化：
分表
分片（也叫分库分表或分布式架构）
字段长度较长优化：
业务允许，尽量选择字符长度短的列作为索引列
业务不允许，采用前缀索引
数据类型影响优化:
char 和 varchar
enum

🐭索引的命令操作

查询索引情况

1.方法一

desc addess; 

key 表示的是索引键
PRI ===> 主键索引
MUL ===> 辅助索引
UNI ===> 唯一索引

2.方法二

show index from addess; 

-- 创建单列辅助索引
alter table addess add index idx_name_cs(address);
-- 创建多列辅助索引
alter table addess add index idx_i_a(id,address);
-- 创建唯一索引(假如address列是唯一的)
alter table addess add unique index uidx_add(address);
-- 注意：若该列不是唯一的则会报错
-- 创建前缀索引（前缀索引只能在 字符串 列创建）
alter table addess add index idx_add(address(6));  -- 取address字段前6个字段作为索引

删除索引

-- 1.先查看表的所有索引名
show index from addess;  -- 找到 Key_name列
-- 2.删除
alter table addess drop index idx_name_cs;

🐿️执行计划分析

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

desc select * from faagent where AgentCode = 'E100000999999';

(1) table 表名

(2) type 查询的类型

①全表扫描 ：ALL
②索引扫描 ：INDEX,RANGE,REF,EQ_REF,CONST(或SYSTEM),NULL 性能从左到右依次变好

INDEX：全索引扫描
例子：

desc select AgentCode from faagent;

RANGE: 索引范围扫描 (> < >= <= between and or in like 等)
例子： mysql> desc select * from faagent where AgentCode > ‘1’;

REF：辅助索引等值查询
例子： 用 union all 代替 or
EQ_REF： 多表连接时，子表（除了from后的所有表）使用主键列或唯一键列作为链接条件时
例子：left join b b.xx xx为主键或唯一键
CONST(或SYSTEM)：主键或唯一键的等值查询
例子：

 desc select * from faagent where AgentCode = 'E101010106999';

NULL：表示没有查询到所需要的数据，返回的都是null
注意：
对于辅助索引来讲， != 或<> ，not in ，like ‘%xx’ 等语句是不走索引的
对于主键列（主键索引可以，唯一键索引不行）来讲， != 或<> ，not in 等语句是走 RANGE 的
意外情况：将表中所有列建立联合索引，没有个列做查询条件都会走索引

(3) possible_keys :可能会用到的索引

(4) key ：真正用到的索引

(5) key_len ： 索引的覆盖长度

– 前提 utf8md4 (md4表示一个字符占四个字节长度，utf8表示一个字符占三个字节长度，表想从utf8md4变成utf8 执行以下命令 ： alter table表名 charset utf8; 建完之后需要重建索引)
int(10…):最大是4个长度
char(2):最大是24+1=9个长度 其中1是存了一个是否是空的判断字节（若该字段是唯一键则长度为8）
varchar(2):最大是24+1+2=11个长度 其中1是存了一个是否是空的判断字节,2是开始和结束都会有一个空的字节
①varchar(20):能存20个任意字节
②varchar(20):不管存储的是字符，数字，还是中文，都是1个字符最大预留长度是4个字节
③varchar(20):对于中文，1个占4个字节，对于数字，1个实际占用1个字节
单列索引越小越好
联合索引覆盖的越大越好

联合索引 add index idx(a,b,c,d)
规范：唯一值多的放到最左侧
1.只要我们将来的查询，所有索引列都是<等值>查询条件下，无关排列顺序（跟mysql版本有关，之前版本要按照建索引的顺序查询才能走索引。最新版本可以无序，mysql会自动排序）
例如： acdb 、badc、cdab、dacb等
原因：优化器，会自动做查询条件的排列
2.不连续部分条件
cad —> 自动排序后： acd ----> 走 a :可以优化：单独建索引 add index idx_cad(c,a,d)
dba —> 自动排序后： abd ----> 走 ab :可以优化：单独建索引 add index idx_dba(d,b,a)
3.在where查询中如果出现 > < >= <= like 则索引只会走到该符号处
例如：select * from table where a=1 and b>2 and c=3 and d = 4;
这里索引只会到 ab
这里若想优化 分两步 ①新建索引（原来的索引需要删除） 顺序为 add index idx(a,c,d,b) ②修改语句：select * from table where a=1 and c=3 and d = 4 and b>2 ;
4.多子句查询，应用联合索引
例如 ：select * from test where a = 1 order by b;
应建索引 ：alter table test add index idx2(a,b);

(6) Extra

出现 Using filesotr 说明在查询中有关排序的条件没有合理的引用索引
相关的排序语句有 ： distinct 、 order by 、group by 、union
关注 key_len 应用的长度

🐨关于索引应用的规范

建立索引的原则

• 必须要有主键，如果没有可以作为主键条件的列，创建无关列
• 经常作为where条件列 order by 、group by 、join on 、distinct的条件
• 最好使用唯一键值多的列作为联合索引的前导列
• 列值长度较长的索引列，我们建议使用前缀索引
• 降低索引条目，一方面不要创建没有用的索引，不常使用的索引清理，percona toolkit(xxxxx) pt-duplicate-key-checker
  表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要了，数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
• 索引维护要避开业务繁忙期 （mysql_8.0版本之后可以把索引制成隐藏或可用状态，无须再删除）
• 小表不建索引

不走索引的情况

• 没有查询条件，或者查询条件没有建立索引
• 查询结果集是原表中的大部分数据，应该是25％以上
• 索引本身失效，统计数据不真实
  例子：同一个语句突然变慢（统计信息过旧导致索引失效）
• 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括（+ - * / ! 等）
• 隐式转换导致索引失效
• <> 、not in 不走索引（辅助索引）
• like ‘%aa’ 百分号在前边不走索引
• 联合索引
• 建立索引时，将等值查询条件往前放，不等值的往后放。
  a = xxx and b > xxx and c = xxx ===> idx(a,c,b)
• 多子句时：
  where c xxx order by a,b; ====>idx(c,a,b)