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

一、什么是索引？

索引是帮助Mysql高效的排列数据的一种数据结构

二、索引的数据结构

很多数据结构都可以实现索引：二叉树、红黑树、hash表、B-Tree、B+Tree等等。Mysql使用的是B+Tree数据结构。
为了降低树的高度，引入了B-Tree；为了提高索引的效率又引入了B+Tree。

为什么Mysql使用B+Tree当作索引呢？那么我们必须要了解B+Tree和B-Tree的数据结构的特点：
B-Tree：
1、叶子节点的深度都一样
2、数据和索引是存储在一起的
3、不同的值通过索引查询效率可能不一样

B+Tree：
1、所有的非叶子节点都是索引，数据全部存储在叶子节点上
2、不同的值通过索引查询的效率是一样的
3、所有的叶子节点形成一个双向链表，遍历数据时只需要从头遍历叶子节点即可

那么为什么选择B+Tree作为Mysql的索引呢？
1、B+Tree每次查询的效率都是一样的，便于优化
2、InnoDB是按页存储的，每页存储的大小为16kb。对于B+Tree来说非叶子节点都是索引，数据和索引是分开存储的，那么一页存储的索引个数就比B-Tree的多。索引个数多，那么查询的效率就更高
3、在进行数据遍历时也只需要遍历叶子节点就可以了，而B-Tree还需要进行遍历才可以

索引为什么就这么快呢？
我们知道，InnoDB存储引擎的页大小为16kb。加入我们存储一个id使用bigInt类型–>8byte，一个索引存储下一个索引的地址大概为6byte。那么一页可以存储16kb / (8+6) = 1170个索引。对于叶子节点，数据类型+data大概为1kb，那么一页可以存储16条数据。如果树的深度为3，那么可以存储的数据条数为：1170117016 = 21902400(千万级条)。如果把根节点和索引节点加载到内存当中，只需要一次I/O操作就可以得到目标数据，这是特别快的。这就是索引这么快的原因。减少了查询的个数。

Hash
对索引的key进行一次哈希运算就能得到数据存储的位置
很多时候Hash要比B+tree更高效
仅仅能满足“IN”、“=”的查询条件，其他的条件不能满足
hash冲突问题

例如：范围查找就不能实现。

MyISAM存储引擎的存储方式和查询数据的过程：
MyISAM是采用分区存储的方式维护表和表中的数据的
.frm表：存储表的结构的
.MYD表：存储表的数据的
.MYI表：存储表的索引信息的

非聚簇索引：MyISAM采用的是非聚簇索引的方式


MyISAM中查询一条记录的过程：
根据索引快速定位到叶子节点找那个存储的物理地址，这个物理地址就是对应数据在.MYD文件中的地址，然后根据物理地址到.MYD文件中快速找到数据。

无论是主键索引还是非主键索引，叶子节点记录的都是记录对应的物理的地址。通过物理地址再到.MYD文件中查询相对应的数据。

InnoDB的存储方式和查询过程
InnoDB是Mysql默认的存储引擎，它的索引和MyISAM还是有一点区别的。

InnoDB存储引擎维护表是靠两张表来维护的
.ibd文件：用于存储表的索引和数据
.frm文件：用于存储表的结构

索引的实现(聚簇索引)：
对于InnoDB来说，数据的本身就是以B+Tree的形式来存储的。首先，InnoDB会根据设置的主键来构造B+Tree【如果没有设置主键则InnoDB会为这张表设置一个默认的主键root id用来存储数据】。所有的数据存储在叶子节点上面，这就是聚簇索引。

查询过程
当我们根据主键查询数据时，通过聚簇索引直接定位到叶子节点得到数据即可。
当我们使用辅助索引【非聚簇索引】时，根据索引定位到叶子节点，此时叶子节点中存储的是对应数据的id而不是数据信息。然后我们根据id再到聚簇索引中查询得到对应的数据。


我们思考几个问题，这也是面试中经常问的问题：
1、在InnoDB中，为什么强烈建议为每一张表设置一个主键？
InnoDB引擎，数据的存储本身就是以B+Tree的形式存储的。一开始InnoDB会根据主键生成一棵索引树。如果不设置主键的话，InnoDB会自动为我们生成一个默认的主键Root ID。但是Mysql的资源是很珍贵的，我们只希望它就做添加/查询的功能就可以了。所以我们手动设置id不要让Mysql有过多的资源消耗。

2、为什么推荐使用自增的整型主键？
首先整型的数据比较要比字符型啊什么的要快。再一个呢就是自增的主键，在维护索引的时候不会破坏原来已经达到平衡的节点。如果节点不够需要扩容的话只需要在后面追加一个节点再维护一下 节点的值就可以了，不去破坏已经达到平衡的节点。效率较高一点。

3、为什么非主键索引的叶子节点存储的是主键值？
两个原因：
1.节省空间：如果所有的索引的叶子节点都记录数据，那么会造成空间上的浪费。数据冗余
2.保持数据的一致性：对表进行写操作时，需要同时维护每张索引表的数据，可能会出现数据不一致的情况，那么如果辅助索引只存储id，那么只需要修改聚簇索引的数据信息即可。不会产生数据的不一致性。

联合索引的排序原理和底层存储结构？

对于联合主键。例如(id,age,gender)。首先会根据id进行排序，如果id相同就根据age进行排序，如果id和age都相同，最后才根据gender进行排序。

最左前缀原则：
面试题：为什么要有最左前缀原则？
对于联合索引，首先会根据最左边的索引字段进行排序，如果最左边的字段的值都相同再按照后一个索引字段进行排序，依次类推。索引在查询数据的时候也应该遵守最左前缀原则，否则无法走索引。

建立索引的原则：
1、索引不宜建太多，每个索引都对应着一颗索引树，Mysql需要维护这颗索引树。如果索引太多维护起来也相当麻烦。

2、尽量使用联合索引将多个需要建立索引的字段都创建在一个索引中

3、建立索引的字段的数据类型不宜过大。InnoDB的一页大小为16kb，如果索引字段数据类型太多，建立的索引个数就减少，那么索引的查询效率也就会降低

4、建立索引的字段的数据应该尽量的散列。例如性别字段，只有男和女两种数据，索引就无法发挥它的优势。所以自增的主键索引查询的效率就高就是这个原因

5、对于联合索引要满足最左前缀原则

6、在通过索引查询数据的时候尽量不适用一些函数或者in not in等，可能不会走索引

7、对于模糊查询：like “%xx”这种不会走索引