（Mysql优化学习01）深入理解Mysql索引底层数据结构与算法

一.索引的本质

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

索引的数据结构

数据结构与算法模拟演示网站：
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树

为什么不用二叉树作为索引：
随着树的高度增加，查找数据的磁盘IO次数会变得很高，导致效率降低。如果索引数据是1,2,3,4,5这种顺序的数据，则跟链表没什么区别。

红黑树

为什么不用红黑树作为索引：
相对于二叉树虽然高度降低了，不会出现链表的情况，但同样随着数据量增加，树的高度会增加，查找数据的磁盘IO次数也会变得很高，导致效率降低。

B-Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
  为什么不用B-Tree作为索引:
  B-Tree虽然大幅度降低了树的高度，但是大数据量时，相比于B+Tree的高度会高不少，磁盘IO次数也会变高。叶子节点间没有指针连接，范围查找性能低

B+Tree

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引，空白处储存下页的地址指针
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能
  
  查看mysql文件页大小（16K）：SHOW GLOBAL STATUS like 'Innodb_page_size’;

Hash表

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

二.存储引擎

MyISAM

MyISAM索引文件和数据文件是分离的(非聚集)

frm：表结构相关信息
MYD：数据信息
MYI：索引信息

InnoDB

InnoDB索引文件和数据文件是在一起的(聚集)

frm：表结构相关信息
ibd：索引与数据信息

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶节点包含了完整的数据记录（主键所在文件的叶子结点存储索引+数据）
  
• 非主键索引的叶子结点存的是该数据所在行的主键
  

什么是回表

由于非主键索引的叶子结点储存的数据是主键，当查询定位到该数据时，需要到主键聚集索引中查找到具体的数据，这种从非主键索引树到主键索引树的查找就叫回表

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

InnoDB因为索引和数据存在一起，要维护一个B+Tree,如果没有主键，会自动寻找数据不相同的列的数据绘制树，找不到数据不相同的则虚拟出一列不相同的整形列来绘制树，这些额外操作会耗费资源。
使用整形比较大小更快，提高查询速度，并且更节省空间。
使用自增主键因为是顺序的，绘制树会更快，如果不是顺序的，还需要重新排序。

为什么非主键索引结构叶子节点存储的是主键值？

1 一致性问题：插入数据时要保证每个索引的数据同时插入成功；数据只在一个地方维护能保证一致性
2 节省存储空间

三.联合索引

索引最左前缀原理

联合索引排序时先按最左的字段进行排序，第一个字段相同，则按第二个字段排序，以此类推；当查询数据时先比较第一个字段，如果相同再比较第二个，如果最终都相同则比较主键（非联合主键索引）；
不遵守最左前缀的情况：假如查询时没有第一个字段，就直接从第二个字段比较，而第二个字段是整体没有顺序的，就要全表扫描比较。