关于MySQL的深度学习之索引

1.什么是索引？

简而言之，索引就是帮助MySQL高效获取数据的排好序的数据结构

2.索引的结构探索

1> 二叉树：

Binary Search Tree Visualization  

我们通过上面的链接去进入这个学习网站选择二叉树这个数据结构，模拟数据的插入与查询，

如下图所示当我们插入递增的数据集时，由于二叉树的特性是右边大于左边，所以这种情况下它就

会变成一个类似链表的结构，当我们查询时效率会很低，所以我们不采用二叉树作为我们的索引结

构。

2>红黑树

 Red/Black Tree Visualizationy

如上进行类似操作，会发现红黑树虽然会做平衡处理，但是它的高度随着数据量的增加而增加，这

样导致在有大量数据的情况下，效率很低，故不适合作为索引结构

 3>B - Tree

B树的特点是叶子结点具有相同深度，叶子节点的指针为空，所有的索引元素不重复，节点中的数

据从左到右递增排列，如下图所示：

我们知道MySQL的每一页的大小大约为16kb ，上图箭头所指的一个节点相当于是我们的一页，我

们看到BTree的索引元素不仅仅只存其本身还会存对应的数据，那么这样我们一页能存的索引元素

就太少了，如果数据量偏大，BTree的高度会很高，查询的效率会很低，所以BTree 也不适合

4>Hash 

我们知道数据库中的索引结构可以选择Hash 但是却很少有人用，这是为什么呢？我们看下图

我们发现虽然我们通过一次hash 就能定位出key的位置，效率很高，但是会存在hash冲突，并且

我们注意到当hash之后的值相同时，我们无法判断数据的大小，不支持范围查询，所以hash这种

结构也不合适

那什么样的数据结构才适合做索引呢？答案是BTree的变种，B+Tree：

 我们可以发现B+Tree， 非叶子结点不存data数据，只存索引，即便这个索引是冗余的，叶子结点

（一棵树中，没有子节点的节点叫叶子节点，通俗理解最下面一行）会存所有的索引字段，并且会

存对应的data数据，且叶子节点用指针链接，可以提高区间访问的性能这样

我们会发现非叶子节点能存很多很多的索引数据，在查询时由于是递增的我们也能更快的查到具体

索引位置，这样的树高度不会太高，查询效率也很快，比较适合做我们的索引结构！

3.数据库存储引擎是什么？

简而言之，就是存数据的方式，常见的有两种如下图所示：

 MyISAM  和 InnoDB，需要注意的是所谓的存储引擎不是针对一个数据库的而是针对表级别的，

一个数据库中可以有不同的存储引擎的表。接下来我们来研究下这两种引擎所建表的不同之处

 如上图所示我们利用不同的存储引擎建立了两张表，我们可以看见，mysiam 引擎建立的表会包含

三个文件，frm文件（表结构文件），myd(mysiam data)存数据的文件，myi（mysiam index）存

索引的文件；而innodb 只有两个文件，frm文件，ibd文件，通过mysiam文件的介绍我们可以知道

这个ibd文件就是存储我们索引和对应数据的文件；我们发现mysiam 的索引和数据是分开的而

innodb则是存在一起的，所以我们的mysiam中的索引又称为非聚集索引，innodb中的索引我们又

称为聚集索引。显而易见，mysiam引擎在查数据时至少做两次磁盘io，因为他先去索引文件找到

索引再去数据文件找到对应的数据，效率表较低且消耗资源，所以我们一般都用innodb引擎

4.为什么建议innodb表 必须建立主键，且推荐使用整型自增主键？

因为innodb主键索引是从左往右一次递增排列，而这个排序是由主键索引进行排序的，所以我们最

好建立好主键，不然mysql会自动帮我们选择一列作为主键索引，或者自己维护一个rowID来排

序，这样比较消耗数据库的资源，对性能有影响。那为什么用自增整型呢？因为整型所占空间比较

少，且自增情况下B+Tree 的扩展比较容易只需要不断新增节点即可，如果不是自增的，那么

B+Tree 扩展时，树的变动比较大，性能不高。

5.非主键索引的结构是什么样的？

非主键索引叶子结点存的不是data数据而是主键索引，因为这样保持了一致性而且这样节省存储空

间，如下图所示，当我们利用name 字段去建立一个非主键索引，那么它会去先找到主键索引的位

置，然后再通过主键索引去找到对应数据。

6.联合索引 和 最左前缀原理 是什么？

所谓联合索引就是由多个字段组成的共同索引，我们以一条数据中的name， age， position 来组

成一个联合索引，那么它的结构如下，联合索引也是需要遵循排好序这个准则的，它是

按照联合索引从左往右的顺序排列的，如下图，它会先根据name排好序，然后如果name相同，那

么在相同的name下它则再会用age排好序，依次往右，那么最左前缀原理就显而易见了，当我们

查询时，如果走联合索引，那么这个where条件的顺序必须满足和联合索引建立的顺序一样，不然

的话，就不会走索引，假设我们直接where age = 30，那它都无法通过第一层的name索引 ，会直

接不走索引，所以这也是我们sql优化的一个小技巧!