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

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

索引的数据结构：（为什么要使用B+结构或者hash结构索引）

数据结构网站：http://www.rmboot.com/

其中：数据结构最初为二叉树：

二叉树数据结构以根节点为首，向左下方和右下方延伸节点元素，其中左下方的元素小于根节点，右下方的元素大于根节点。

此图就是一个二叉树，如果去查询索引为6的话，需要进行6次磁盘IO查询，达不到索引的一个高效性。

 

红黑树：

 

下图是我们红黑树的模型，如果我们要去查询索引为6，需要进行3次磁盘IO查询查询比二叉树快一点，其跟二叉树的一个最大区别就是红黑树可以自动帮我们进行树平衡，众所周知，树高度越小，所继续的磁盘IO就越少，效率就越快。

 

进从而引出了我们的B树，B+树。

B树就是在我们红黑树的基础之上将我们的根节点整成了多个根节点，其可以索引值可以通过折半算法等去快速定位到相对于的根节点上进而去快速查询，其这些节点还遵循从左到右依次递增。

B+树：

就是在B树的基础上讲叶子节点放到了最底层，底层数据还有一个双向指针连接。

B树底层其实是单向指针，这里的双向指针是MySQL底层在B树的基础上修改的。

 

问题：

为什么MySQL底层要使用B+树而不是使用B树呢？

首先第一B树的每个节点都是为叶子节点会大大的造成磁盘用量，其我们知道MySQL底层给我们设置的根节点的容量为16kB（16384），我们B树一个节点比如为8个字节加上一个data叶子节点比如为6个字节，相当于为16个字节，其根节点容量就位16kB/14B,而我们B+树将data移动到最外的节点去了，相当于根节点容量就比B树多了，总结我们都知道MySQL是基于我们磁盘内存的数据库，每一次查询都是基于磁盘IO去查询的，使用树的高度越低相对于效率越高，对于一个表中数据达到几千万条时，B+树的高度肯定会比B树低，还有一点B+树在B树的基础上增加了一个双向指针，此时我们如果去继续范围查询，比如说查询20-50之间的数据，使用B+树是不是首先到了20-30之后直接可以通过指针去访问到50的数据，而B树就只有一个节点数据完了之后会返回到根节点又去一步一步的查询49-50的数据。

 

其中我们MySQL现在索引数据结构默认为B+树和hash

以下为hash索引：

 

对于MySQL的存储引擎：

MyISAM和InnoDB的区别：

首先MyISAM: 我们使用MyISAM存储引擎建表会在MySQL磁盘目录中生成3个文件，分别为frm，MYD，MYI，其中frm表示表定义文件，MYD存储表中数据，MYI存储表中的索引，而且MyISAM为非聚集索引，

InnoDB建表的话只会生成2个文件：一个frm文件，一个ibd文件，其中frm文件跟MyISAM一样的表示表定义文件，ibd文件即为索引和数据，而且InnoDB为聚集索引。

 

 

为什么现在都用到InnoDB呢？

首先在查找速度方面InnoDB就比MyISAM快，原因是因为MyISAM的索引文件和数据文件是分开存储的，如果我现在索引为1,是不是要去索引文件里面取到该索引在磁盘空间的一个位置之后，在回表到另外一个数据文件里面根据这个位置去找数据，而innoDB的话就不需要了，我们现在查询索引为1的数据，我们直接去ibd文件里面找就行了，我们都ibd文件里面也是B+树结构，其中最外子节点也就是叶子节点的data里面就是数据，data节点上分是对于的索引，是不是直接可以取到所以数据，不需要重复回表查询。

 

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

首先我们InnoDB使用的B+树结构，里面叶子节点是定义是如果有主键索引就取主键索引，如果没有我们会将MySQL里的数据进行一个扫描选择一行没有重复的数据进行叶子节点的选取，如果每行数据都有重复的话，我们MySQL底层会自动生成一个Row_id来充当默认主键，而我们的MyISAM没有，所以可想而知我们自己建个主键的话MySQL可以底层就可以节省很多麻烦，可以节约我们资源的一个消耗，所以MySQL官方建议我们使用InnoDB时必须建主键。

那为什么推荐我们使用整型的自增主键呢？

其实我们MySQL不是用的B+树数据结构来充当索引嘛，是不是如果我们使用随机的UUID去当主键，首先UUID占用的字节肯定比我们传统的自增主键ID要大，从而消耗我们磁盘内存，我们也知道我们MySQL在设计B+树的时候就设置了根节点的容量大小为16kb，如果我们使用UUID这种字节太大了，从而导致我们的根节点数量就会减少，会不会同样2千万条数据我用自增ID可以控制我们到树高度为3，而UUID就要树高度达到4，从而影响我们的性能，还有一点自增ID是自己就保证了顺序的有序性的，我们也知道B+树底层是帮我们实现了顺序的一致性的，但是我们如果使用自增ID是不是就可以不用MySQL底层去帮我们去排序了，UUID的话每次都要去帮你计算值而去排序也会影响MySQL的一个性能问题。

 

联合索引：

 

这就是我们联合索引的一个结构图。

联合索引底层也是通过B+树实现的，但是排序方法是根据联合索引的一个顺序去排序的，比如上图的联合索引首先是name，age，position，就会先根据name去排序，name相等的情况下再根据age，age相等的情况下就position。

 

1：SELECT * FROM user WHERE name = 'BILL' AND age = '20'，

2：SELECT * FROM user WHERE age = '20' AND position = '20'

3：SELECT * FROM user WHERE position = '11'

下方三个sql语句中谁走了索引的？（最左前缀原则）

应该我们索引是MySQL高效获取数据的排好序的数据结构，排好序的就是说第一个执行的必须是name索引，如果没有name条件索引肯定不会生效的。所以显然只有1符合。