深入理解MySQL索引底层数据结构和算法

索引的定义

索引是一种方便MySQL查找数据的排好序的数据结构。索引所用到的数据结构大多是：

  1. 普通二叉树
  2. 红黑树
  3. Hash表
  4. B树
  5. B+树

MySQL底层采用B+树的原因

  1. 相比于普通二叉树和红黑树，B+树的一个节点可以存储更多的数据，因此B+树的高度更短，因此查找速度更快。
  2. 相比于hash表，虽然hash表对单个数据查找更快，却因为hash表的无序性导致范围查找上B+树更胜一筹。（当然也有用hash表作为索引的数据表，但大多数仍然使用B+树作为索引）
  3. 相对于B树，B+树只在叶子节点存储数据，非叶子节点只存储索引，而且相邻叶子节点之间有一个双向指针。这样在节点大小相同的情况下，B+树能存储更多的索引。假设一个索引大小16B，一个节点大小16K，则一个节点可存储1024个索引。而如果非叶子节点带有存储内容data且假设data大小为1K，则一个节点只能存储16个索引，变相增加了树的高度。

MyISAM索引和InnoDB索引的区别

MyISAM索引的索引文件和数据文件是非聚集（分离）的，分别用MYI（MyISAM Index）后缀和MYD（MyISAM Data）后缀文件保存。而InnoDB索引则是聚集的，只有一个IDB后缀文件。

因此，MyISAM索引需要跨文件访问，因此访问时间更长。

为什么IBD最好使用整形自增主键

原因如下：

主键

如果不自己设主键，MySQL需要自己找一条没有重复元素的列作为索引。如果没有这样的列，MySQL就会设一条隐藏的列作为索引，所以设立主键就能节省MySQL的空间。

整形

字符串数据对比需要对比ASCII码，整形数据对比速度更快。

自增

因为B+树的节点存储不止一个元素，如果从中间插入的话，会破坏B+树的整体结构，导致整棵树都要跟着一起变动，使用自增可以节省重新调整树结构的时间。

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

首先是为了节省存储空间，因为如果保留所有数据会占用更多的存储空间。其次是为了保持一致性，如果某个数据需要更新，只保留主键值的索引只需要更改主键索引对应数据库内容即可。
非主键索引查询得到主键值后，再从主键索引中找到对应信息，整个过程相当于一次非聚集性索引。

联合索引

联合索引是用多个字段组合成的索引。

为什么联合索引要有最左前缀原理

因为一旦忽略左前缀进行查找，则字段会变得无序，无序的查找只能全部扫描查找，则索引失去意义。