[MYSQL]索引相关

目录

 

概要

索引常用结构的是B+数据（当然也有hash和别的）

B+特点

主键的规范

最佳左匹配

 

索引下推

mysam和inno在索引上面的差异

UK和普通索引的选择

查询差异

更新差异

mysql优化器对索引的干扰

规避方法：

 

---

概要

本文简单介绍下mysql的索引，主要围绕索引的结构，最佳左匹配，索引下推，索引选择

 

索引常用结构的是B+数据（当然也有hash和别的）

B+特点

1.多叉树，平衡性很好，深度不用很深 就能支持海量数据

2.叶子节点是有序的 链表结构

PS：数据库数据一般是用数据页为单位操作的，所以查询的数据不在同一个数据页 需要到磁盘上面去load。

机械磁盘随机找数据块一般寻址一次耗时10ms左右。如果是用二叉树，树的深度很深 比如有个30层，有40个数据库在磁盘上 数据就会很分散。

 

主键的规范

这里值得一提的是主键一般使用递增的数字类型，如果是随机的（eg：uuid），在维护树的平衡上代价很大。

还会导致空间的裂变。

所以删除数据，数据库大小不会变小，这个时候就需要重建索引。 （还有另外一点 经常更新 删除 页分裂 会导致数据页有很多有空洞 造成存储的浪费，重建索引按顺序排列 空间紧凑 利用率高）

重建索引
alter table T drop index k;
alter table T add index(k);

重建主键索引（错误写法）
alter table T drop primary key;
alter table T add primary key(id);

正确的
alter table T engine=InnoDB

 

最佳左匹配

1.字段AB AC，如果有A都能走上索引，如果是第一个字段没有命中 就凉了

2.如果是String类型的字段 索引值是 ABCSD ， like ‘AB%’也会命中索引

 

索引下推

一般查询如果不是走的主键查询，会先查二级索引，再查主键索引。如果数据都在二级索引上 就不需要回表了

 

还有一种情况： select id from T where c1 = x and c2 = y and c3 = z;

执行顺序先去 索引查到c1=x对应的主键，拿着主键去一级索引去查记录，用记录中的值去对比c2和c3的值是否满足（都回表了）

mysql5.6之后就直接在二级索引先匹配索引中已有的字段，有效减少了回表次数

 

mysam和inno在索引上面的差异

inno的叶子节点直接存了数据

mysam的叶子节点存的是行数据的指针 

 

网上随便搞了点图 B+树类似这样

 

UK和普通索引的选择

查询差异

以select * from t where c1 = 1;为例，假设c1是普通索引，引擎会返回第一个索引树（indexTree1）c1=1对应的主键ID（X），再用X去主键索引去捞数据

然后再查indexTree1的下一行，看是否满足，这样循环直到不满足c1=1的条件。如果是c1字段上是UK的话，只要查一次，无需看他的下一行是否满足。

但是：mysql操作数据都是按数据页（16KB）为单位处理的，所以一般都会加载到内存中，所以查询下一个也是很快的，差异可以忽略不计

 

更新差异

涉及uk字段写入，mysql一般找到对应的索引位置，判断冲突后直接插入。普通索引操作类似 不过少了检查冲突的动作。

当所要操作的数据页不在内存中，UK是把磁盘数据页加载到内存中再处理，而普通索引直接把插入语句写入changeBuffer，所以普通会快点。

PS：当触发该数据页读，后台定时作业，数据库关闭，会changeBuffer会更新内存数据；

changeBuffer为啥不适用于UK，应该要判断是否冲突 需要把数据页load到内存，既然到内存中了 就必要再画蛇添足加一个缓存了 。

changeBuffer适用于 写入多 读取少的场景。相反 线上如果是一个核心服务，发布核心事件消息，payload携带信息比较少，下游系统需要大量反查。

这个场景不建议开启changeBuffer，会增加changeBuffer的维护成本

changeBuffer的操作也会记录在redo文件中

 

mysql优化器对索引的干扰

mysql优化器会主动选择“最优索引”去执行SQL，真实结果可能会让你的sql变慢。

规避方法：

1.使用hint预发 force index，指定强制走某个索引

2.如果是索引搜集信息不准（优化器是选样 抽取某些数据页），可以使用analyze table来解决