Database(四) mysql的innodb的索引

一：简介

1.索引是一种提高文件检索效率的方法：
比如常用的思想：空间换时间，用hash来存储数据，方便查询；搜索引擎如ES，利用倒排索引可以快速的检索到文档信息；kafka的消息管理，为了快速的定位到offset所在的message，也是使用索引来辅助查询；
2.Innodb的索引主要是为了检索数据库，对于写多读少的场景，不适宜建立索引；下面会从三方面来说明一下：索引的分类和使用，设计索引和不走索引的情况，索引的实现原理；
3.引入索引的原因：
在数据库查询中，数据是被放置在磁盘上的，一次磁盘操作：寻道+旋转延迟+传输时间；通常寻道和旋转延迟是在5ms级别的；
试想如果没有索引，需要将整个表的数据load到内存中，寻道和旋转的开销太大；
同时根据局部性原理，可以利用索引页，将相邻的数据在磁盘地址提前加载出来；
4.不适合的场景
索引不太适合于写多读少的场景，比如归档、日志之类的，同时索引也会带来一些额外的开销，比如insert buffer(change buffer)就是为了解决写操作带来的索引更新引入的；

二：索引的分类与使用

1.索引，分类从不同的纬度有不同的分类：
1）聚簇索引，非聚簇索引
聚簇索引是指索引和数据是同一个，比如在Innodb中，主键索引就是数据页；但在MyIsam中，所有的索引都是数据页的地址；他们场景不同，但各有好处，如MyIsam是OLAP场景，非聚簇，每个索引就很小，索引页承载的指向多，缺点是需要多检索一次；
2）唯一索引，非唯一索引
这是一种约束限制，可以用来作为重复的约束，Unique key；
3）组合索引，单一索引
组合索引是指索引字段不只一个，比如(create_time,account_id);
4）前缀索引
通常用于某个字段较长，截取其一段作为索引，来节省索引空间；
5）全文索引，倒排索引
MySQL5.6.24上Innodb也引入了全文索引，全文索引的实现原理是倒排索引；
6）B+树索引，hash索引(Innodb的adaptive hash index 自适应hash索引)
Innodb的索引是用B+树来实现的，网上资料很多；hash索引是innodb的一种优化手段，引擎会监控索引的一些访问和使用情况，对访问特别频繁的数据，加上hash索引；也有一些引擎比如Memory引擎在以Hash索引为主要索引；
7）使用角度：覆盖索引
覆盖索引是一种不需要回表(即访问聚簇索引)，只需要访问当前索引，就可以满足检索需求的一种形式，比如 select a,b,c from table, 切好有(a,b,c)的组合索引，那仅需要从这个作何索引中取相应的字段即可；
2.索引的一些原则
1）尽量设置识别度高的字段为索引，即 count(distinct column)/count(*)越接近1越好，比如sex：男、女，就不适合做索引，因为对检索的作用很小，而且占空间；
2）索引列不能参与计算，要保持干净，否则有可能使用不到索引，比如 id * 3 + 1 > 100，这种就会没办法使用相应的索引；
3.组合索引
1）最左匹配原则，对于索引(a,b,c)，它的存储是先按照a，再照b，最后是c的顺序来存储，所以对于 b=x and c=y会导致全索引扫描；
2）=和in可以乱序，对于 b=x and a=y，这种可以直接定位，因为SQL优化器会处理；
3）范围查询截断；对于 b=x and a>y，如果explain查看执行计划的话，会发现，只用到索引的一部分字段，即只用到了a字段，这是因为利用定位到 a>y的数据后，就没办法用索引来快速定位到b=x的数据，只有全量扫描a>y的结果集；

三：索引的实现原理和性能改进

1.B+树索引
B+树和B+索引的文章有很多，就不细说了，主要描述下到B+树索引的演进过程；
1）主要的演进过程是从二叉树 --> 查找树 --> 平衡树 --> B树 --> B+树；
2）二叉树和查找树的缺点是容易退化成线性，平衡树的缺点的修改动作太繁琐，B树的缺点在于数据节点可以在非叶子节点上；
3）B+树，集中了树高度很低、快速查找、范围查找等优势；
4）B+树的查找次数，取决于树的高度h，h=㏒(m+1)N，N是总的数据量，m = 磁盘块的大小 / 数据项的大小，这里的磁盘块是用页组织的，一般为16KB；
2.查询的优化手段：索引驻入内存，adaptive hash index 自适应hash索引
1）为了进一步的提高查询的效率，对一些热门的索引页会驻留在内存中，同时Innodb引擎还会对一些热门的数据，直接加上自适应hash索引；
3.写操作优化：change buffer、insert buffer
1）如上面所讲，索引是为了加快检索的速度，那么增删改的写操作，有一些额外操作；
2）在mysql5.5之前是insert buffer，后来引入了change buffer，主要针对非唯一索引的change进行效率优化，非唯一索引需要离散地访问非聚集索引页，插入性能在这里变低了，唯一索引因需要校验，所以直接IO，不做优化；
3）change buffer的原理：延迟写、合并写、把随机写变为顺序写；
4）change buffer的流程：
a）查看当前要修改的页是否在内存，若不在则加载到内存；
b）修改内存中数据的页和索引页；
c）写入redo log，防止mysql崩溃，用来灾备；
d）系统调用fsync()，将内存中的页写入磁盘；
5）change buffer的一致性问题：主要还是存在于fsync，但这个问题OS中一直都有
a）数据库异常奔溃，能够从redo log中恢复数据；
b）写缓冲不只是一个内存结构，它也会被定期刷盘到写缓冲系统表空间；
c）数据读取时，有另外的流程，将数据合并到缓冲池；

四：索引的设计与不走索引的情况

1. 总体原则：MySQL内部优化器会对SQL语句进行优化，最终优化器决定走不走索引；
例如 or 、in 、not in 、is null、 is not null、!=,并不是完全不走索引，要考虑到：
a）全表扫描是否比索引更快，以至于优化器选择全表扫描；
b）mysql-server 的版本；
c）可以通过优化语法或者配置优化器，走索引。
2.在索引上进行函数计算
3.在索引上进行OR运算，有可能会ALL，但也有可能进行index merge；
4.隐式转换：比如对于字段 a 为 char类型， where `a`=1 -- 不走索引；
5. like 的前缀“%x%”不走索引
6.情况众多，但结合explain根据场景、版本号、数据量等来分析;
参见系列的(三) https://www.jianshu.com/p/1a268b41d715

参考文章：

1. https://www.cnblogs.com/chenpingzhao/p/4881010.html
2.https://tech.meituan.com/2014/06/30/mysql-index.html
3.https://blog.csdn.net/qq_42914528/article/details/90762012