深入理解MYSQL索引数据结构

索引是帮助MYSQL高效获取数据的排好序的数据结构

一、索引的数据结构

• 二叉树
• Hash
• 红黑树
• B-Tree
• B+Tree

1.1 二叉树

• 在极端情况下，若按照大小顺序插入二叉树，则会形成单边增长的二叉树，这样使用索引的时候和全表扫描是一样的了

1.2 Hash

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+ 树索引更高效
• 仅能满足 “=”，“IN”，不支持范围查询
• Hash冲突问题

1.3 红黑树

• 红黑树也叫平衡二叉树
• 当单边的节点大于3时候，就会自动调整，这样可以解决二叉树的弊端
• 数据量较大情况，树的深度会很大，I/O次数很多，效率低

1.4 B-Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
• 索引节点存储数据
• 若一个节点我们申请的空间为16KB，若data中的数据过大，则一个节点能放的数据量越小，这样就会造成树的高度比较大了（比红黑树高度小点）

1.5 B+Tree

• 非叶子节点不存储data，只存储索引和指针(冗余)，目的是可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

<1> 树的高度越小，磁盘I/O次数越少，效率越高
<2> 查看MySQL文件页大小（一个节点的大小）：

SHOW GLOBAL STATUS like 'Innodb_page_size'；

<3> MySQL页文件默认为16384Bit即16KB，树的高度为3，能够存储多少数据？

我们先看非叶子节点，假设主键ID为 bigint 类型，那么长度为8B，指针大小在Innodb源码中6B，一共14B,那么一页（即一个节点）可以存储 16KB/14B=1170 个索引元素和 1170个指针；
根节点有1170个索引和1170个指针，树高度为2的节点就有1170个，那么叶子节点的数量为 1170x1170；
每个叶子节点可以存储16KB，若每条数据比较大为1KB，那么每个叶子节点可以存储16条数据；那么，高度为3的 B+Tree 的叶子节点可以存储的数据量为 1170x1170x16=2000W；

二、MYSQL 存储引擎

同一个数据库中，不同的表可以设置不同的存储引擎；
MySQL的数据存储在 data 目录下， data 目录下的 文件夹是以 数据库为单位的，数据库文件夹下面存放的表数据； data / {数据库名} /表文件

2.1 MyISAM 存储引擎索引实现

• MyISAM存储引擎的索引文件和数据文件是分离的（非聚集）；
• MyISAM 存储引擎的一个表有3个文件：*.frm 文件存储的表的结构； *.MYD 文件存储表的数据； *.MYI文件存储表中的索引数据；
• MYISAM 存储引擎的索引的叶子节点的data中存储的是索引所在行的磁盘指针； ---- 非聚集索引
• MYISAM 存储引擎的主键索引 和 非主键索引的存储是差不多的，
• InnoDB 存储引擎的 主键索引 和 非主键索引存储是不一样的；
  

2.2 InnoDB 存储引擎索引实现

• InnoDB存储引擎索引文件和数据文件是合一的(聚集)；
• InnoDB 存储引擎的1个表有2个文件： *.frm 文件存储表的结构； *.ibd 文件存储的是索引和数据；
• InnoDB表的数据文件本身就是按 B+Tree 组织的一个索引结构文件；聚集索引叶子节点包含了完整的数据记录；

2.2.1 InnoDB的主键索引

• InnoDB 存储引擎的索引的叶子节点的data中存储的是索引对应的所有数据；----聚集
• 问题1：为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
  a. 因为 MySQL对于 InnoDB 表设计的就是按照 B+Tree 数据结构存储数据的，若没有主键就没有办法去存储数据了；但是在平常我们建表的时候没有指定主键也是可以建成功的，这是因为 MySQL 会生成一个 rowid 作为数据的唯一标识；
  b. 若使用的 UUID 作为主键，在查找的时候需要去比较大小，字符串UUID比较的效率肯定低于整数的比较；在进行比较的时候会把数据拿到内存空间中做比较，UUID为字符串占用的内存空间就会较多；
  c. 若是递增的，则插入的数据直接向后排，这个节点满了，直接新增一个节点就好了；若不是递增的，有个节点存储满了（5, 9），但是新插入了一个数据（7）在这个节数据的中间，则需要将这个节点先分裂，再平衡去满足 B+Tree 的结构；

2.2.2 InnoDB 的非主键索引

• 在使用非主键索引查找的时候，先从非主键索引的树中查询到对应的主键值，然后使用主键值去到主键索引的树中去查找；
• 对于非主键单值索引，若索引字段的值为null，则它的数据不会放到非叶子节点上，是放在叶子节点的链表的最前面的；（强烈不建议字段设置为null）
• 问题2：为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
  因为在插入数据之前先要维护一下索引，然后再将数据插入进去；若主键索引 和 非主键索引的叶子节点都存储具体的数据，则一个 insert 语句插入成功的判断就是向主键索引中插入成功且向非主键索引中也插入成功，这样就造成了事务的问题，事务是很耗性能的；当然，主键索引和非主键索引的叶子节点都存储具体数据，会造成数据的同样的数据存储了几份，就造成了空间的浪费；

3 联合索引

以上的联合索引从左到右由字段 a，b，c 组成；

• 联合索引在存数据或比较的时候，先比较联合索引最前面的字段，若最前面的字段值一样，则再比较第二个字段的值；
• 联合索引的索引字段中有一个值为null，则将其放在叶子节点的最前面；可以认为null值是最小的。
  
• like KK%相当于=常量，%KK和%KK% 相当于范围
• 问题3：解决like’%字符串%'索引不被使用的方法？
  a.使用覆盖索引，查询字段必须是建立覆盖索引字段
  b.如果不能使用覆盖索引则可能需要借助搜索引擎

4.索引下推（Index Condition Pushdown，ICP）, like KK%其实就是用到了索引下推优化

对于辅助的联合索引(a,b,c)，正常情况按照最左前缀原则
SELECT * FROM employees WHERE a like ‘LiLei%’ AND b= 22 AND c =‘manager’
这种情况只会走a字段索引，因为根据a字段过滤完，得到的索引行里的b和c是无序的，无法很好的利用索引。

4.1 什么是索引下推？

• 在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到a是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对b和c这两个字段的值是否符合。
• MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到a是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤b和c这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。
• 索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

4.2 为什么范围查找MYSQL没有用索引下推优化？

估计应该是MYSQL认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like
KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

总结

使用索引一定要符合索引排好序的规则

数据结构和算法（Data Structure Visualizations）：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
https://www.cnblogs.com/yufeng218/p/12465694.html