mysql索引及执行计划

什么是索引

类似于一本书的目录，加速查询，会影响到锁的应用

种类

BTREE :在mysql应用99.9%  innodb 做范围查询
rtree ：     mongodb
FULLTEXT ：对于大字段text类型  
hash  ： mem引擎
按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。
按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。
按字段特性分类可分为：主键索引、普通索引、前缀索引。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

BTREE查找算法演变

B-TREE 普通BTREE
B+TREE 叶子节点双向指针
B++TREE 枝节点的双向指针

mysql中如何使用BTREE

b树分三层

聚簇索引构建过程

leaf 叶子 存储数据行时就是有序的 直接将数据行的page作为叶子节点（相邻的叶子节点，有双向指针）

no-leaf 枝节点 提取叶子节点的id的范围+指针 构建枝节点（相邻的枝节点 有双向指针）

root 根 提取枝节点的id的范围+指针 构建枝节点

可以优化以id列作为条件作为查询

group by ， order by 都可以覆盖到

一个b树最少要有两层结构，根和叶子构成

b树 以数据页加载数据 每个数据页都是一个范围

mysql btree 两类结构
clustered 聚簇（exent 区）索引
extent区 ： 连续的64的数据页 默认1m
IOT组织表 ： 会按照聚簇索引组织方式，存储表中的数据行
聚簇索引是建表时的 主键列 如果没有主键是第一个非空的唯一键 如果这两个都没有 会生成一个隐藏的聚簇索引（row id）占用6字节

secondary  辅助索引
普通单列索引
普通联合索引
唯一索引
前缀索引

辅助索引

非聚簇索引之外的都称之为‘’辅助索引‘’

每一列辅助索引包含两列（主键值和索引值）如果主键过长会导致辅助索引会占用更多的空间(在ibd文件存储)

alter table ti add index idx(辅助索引的列)；

1 会把辅助索引列的值和主键的值拿出来

2 安装辅助索引列的值进行排序有序的存储到各个数据页上

3 数据页生成叶子节点 枝节点是（name列的左闭右开）根节点是 按照枝节点的范围 (左闭右开)

查询语句 select * from ti where name=‘c’；

1 如果要查询数据走索引，会根据统计信息 记录的root节点的数据页的号码

2查询如果不满足单列的条件，查询一整行数据 会拿主键值在次 (回表查询) 做聚簇索引的查询

联合索引

select * from ti where name=‘s’ and gender=‘m’

如果只有name列的索引，name列重复值过高即使有and条件也会先进行name列的索引查询后，

在server层在进行gender条件过滤

alter table ti add index idx(联合索引的列)；

叶子节点会联合索引的列和主键的值拿来做 会按照联合索引从左至右的排序（生成联合索引的叶子节点）最好是重复值最少的在左列最好

枝节点的范围会选取叶子节点最（左列值的范围）联合索引的最左列

根节点范围是选取枝节点的范围(左闭右开)

#如果过滤条件缺失了最左列，就不能走联合索引了

回表是什么 回表带来什么问题 怎么减少回表

1,按照辅助索引列,作为条件查询时,先查找付索引树得到id,在回到聚簇索引树查找数据行信息

2 io量多,io次数多,sql层和engine交互多次 io偏高 cup偏高

3 辅助索引能够完全覆盖查询结果 最左列选择重复值少的

尽量让查询条件精细化 尽量使用唯一值多的列做为查询条件

优化器算法

hints

单独指定/*+ /

select @@optimizer

mrr

拿name做查询的时候 匹配对应name值对回表之前做个排序走聚簇索引拿整行数据

5.6之后

icp 有联合索引 abc 有大于小于like 只能走到这里剩下的会回表

icp 将c列的条件下推到engine层 过滤 排除无用的数据在去磁盘拿数据页

多表链接

snlj 普通的嵌套循环链接

a join b on a.Id=b.id

如何选择驱动表

人工干预left join

优化器默认自己选结果集小的

a表先拿a表第每一行判断是不是和b表的行等值

b表有索引

inlj 基于索引的扫描 普通索引

a表先拿a表第每一行扫描b表的索引找b表的行

唯一索引或者主键 点查

bnlj

a表先拿a表数据块扫描b表的数据找b表的行

bka

a表数据块排序 去扫描b表数据行

索引树高度的影响

a数据行越多,高度就越高

1分区表

2按照数据特点进行归档表 pt-archiver

3分布式架构

4在涉及方面 满足三大范式

b主键规划 长度过长

主键,尽量使用自增数字列

c列值长度越长,数据量大的话会影响高度

1使用前缀索引

100字符只取前10个字符,构建索引树

d数据类型选择合适的

如何计算一个索引的高度

1确认找到表的id号

select * from information_schema.innodb_tables;

2找到索引号

select * from information_schema.innodb_indexes;

3找到page号

select * from information_schema.innodb_indexes;

4解析idb文件

需要跳过多少个page x 16384字节 +38字节 +26字节

一个page都有什么组成

索引管理

创建索引

alter table 表名 add index 索引名(字段);

alter table 表名 add index 索引名(字段1,字段2)

联合索引要保证最左原则

前缀索引 alter table 表名 add index 索引名(字段(前缀))

查询索引

desc 表名;

show index from 表名;

删除索引

alter table 表名 drop 索引名;

执行计划获取和分析

工具

desc sql语句

explain sql语句

语句不会执行 ,只会把将来的执行计划打印出来

可以加 format=tree看每一步执行过程或者=json

explain sql语句 说明

id 代表执行的顺序 值越大优先执行 从上至下 从大到小

type 代表 使用索引类型

ALL 代表没有使用索引 
index 全索引扫描需要把索引全部扫描一遍才能拿到数据   会有以下情况会走这种类型 查询整个列会走index
range  索引的范围扫描 需要扫描索引一部分的数据来拿到我们的数据
ref  辅助索引等值查询
eq_ref  
const_(system) 点查 得到的数据一定是一行数据

possibe_keys 此次查询可能会使用那些索引

key 此次查询用的哪个索引

#有可能我们看语句可能会使用a索引 结果使用了b索引有可能是统计信息过旧导致

key,_len 表示索引用了几部分

rows 此次查询预估要扫描多少行

ftltered 索引可以过滤出来多少数据

extra  如果此列显示  using where 会在server层再次过滤  using filesort 需要内存排序非常消耗cpu 如果where条件走了一个索引
  那么order by走的条件就会在server层进行排序 最好的方法是创建一个联合索引 让where和order by 查询和排序条件都覆盖到
  using tmp 会使用到临时表 会导致内存压力比较大 
  还会显示优化器算法
   ICP SNLJ BNL BKA HJ
   一般会自动选择,大部分情况下不用干预 

那些情况不走索引

查询条件带 不等于(主键或者唯一建再用不等于时会转换成大于小于范围查询) 或者隐式转换 在5.7之前会有查询结果集大于25%就会走全表数据 统计信息不准确

联合索引应用细节

如何查询联合索引应用那部分索引

explain format=json select 查询的列 from 表名 where a=1 and b=1 and c='a';

可以通过执行计划key_len判断或者,加入format去判断使用联合索引多少部分

在5.6之后版本 explain select 查询的列 from 表名 where b=1 and c='a' and a=1;

根据5.6之后逻辑优化的调整是可以符合最左原则

也可以使用部分联合索引但是要包含最左列

select 查询的列 from 表名 where a=1 and b!=1 and c='a';

在联合索引中如果中间出现了不等值条件 或者 大于或小于 like %% 就会走到部分列的索引,

会在查询条件 不等值条件 或者 大于或小于 like %%, 就不会在下一个查询条件走联合索引的下一列

一定要遵循最左原则

查询条件中一定要包含最左列条件

查看消耗代价

可以通过format=json进行查看

explain format=tree select * from world.city where id>111;

应用场景

1新业务上线 可以在开发环境和测试环境测试使用

2 数据库比较慢确认到是语句问题

应急性比较慢

show full processlist; 想要看的更全 可以通过 info找到慢语句 如果是select 应急办法先杀掉 kill id explain sql 优化索引, 改写语句

间歇性的慢