MYSQL索引的底层实现原理

1 索引的本质

索引（Index）是帮助MySQL高效获取数据的数据结构，为什么需要需要特定的数据结构呢？首先，顺序查找这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，更优秀的查找算法，比如二分查找（binary search）、二叉树查找（binary tree search），我们会发现每种查找算法都只能应用于特定的数据结构之上，但是数据本身的组织结构不可能完全满足各种数据结构，所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。

2 B树

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构， B 树可以看作是对2-3查找树的一种扩展， 他允许一个节点有多于2个的元素。

2.1 特征

2.1.1 树的阶（至多子树数目）

树中每个结点至多有m 棵子树 （注：m指的是树的阶）。

2.1.2 根节点（至少子树数目）

若根结点不是叶子结点，则至少有两棵子树（注：根节点至少有两个儿子）

2.1.3 非叶子节点（至少子树数目）

除根结点之外的所有非叶子结点至少有m/2(向上取整)个子节点

2.1.4 叶子节点（同层）

所有的叶子节点都在同一层，并且不带信息

2.1.5 非叶子结点结构

关键码即真实数据，所有的非叶子结点中包含以下数据：（n，A0，K1，A1，K2，…，Kn，An），其中，n 为关键码的个数，Ki（i=1,2,…,n）为关键码，且Ki<Ki+1，即从左至右升序排列，Ai 为指向儿子的指针(i=0,1,…,n)，且指针Ai-1 所指子树中所有结点的关键码均小于Ki (i=1,2,…,n)，An 所指子树中所有结点的关键码均大于Kn，即每个ki数据两旁各安放了一个指针，即Ai-1和Ai，左边的子树数据统统小于ki，右边子树的数据统统大于ki。

2.2 渐进复杂度和查找效率

例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)，即查找节点个数的渐进复杂度为O(logdN)。从这点可以看出，B-Tree是一个非常有效率的索引数据结构。由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

3 B+树（https://www.cnblogs.com/20189223cjt/p/11262450.html）

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

3.1 不同点

与B-Tree相比，B+Tree有以下不同点：

关键字+1

有n棵子树的节点含有n个关键字。

非叶子节点（关键字和指针）

它把数据都存储在叶子节点，内部只存关键字（其中叶子节点的最小值作为索引）和孩子指针， 仅具有索引作用，简化了内部节点。

叶子节点（所有关键字信息，数据和链表）

所有叶子结点包含所有关键字信息和指向关键字记录的指针，将所以叶子节点串联成链表即可从头到尾遍历。

核心不同点总结

B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。

3.2 优势

B+树的磁盘读写代价更低

由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。

B+树的查询更加稳定

所有的关键字查询都会走一条从根节点到叶子结点的路径。即s所有关键字查询的长度是一样的，查询效率稳定。

B+树便于遍历和区间查找

B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。

4 MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式

4.1 MyISAM( B+Tree，非聚集)

索引文件仅仅保存数据记录的地址， 主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复

4.2 InnoDB（B+Tree，聚集索引）

数据文件本身就是索引文件，叶节点data域保存了完整的数据记录，这个索引的key是数据表的主键。

4.2.1 InnoDB要求表必须有主键(主索引)

如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键。

隐含字段：如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

4.2.2 辅助索引

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

4.2.3 扩展

为什么不建议使用过长的字段作为主键：因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

非单调的字段作为主键在不是个好主意：因为InnoDB数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效。分布式问题，使用自增字段作为主键则是一个很好的选择。

5 Hash索引

Hash索引会将计算出的Hash值和对应的行指针信息记录在Hash表中。

5.1 检索效率非常高

不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

5.2 弊端和限制

不能使用范围查询

Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。

排序操作

Hash 索引无法被用来避免数据的排序操作。

组合索引

Hash 索引不能利用部分索引键（组合索引）查询。

不能避免表扫描哈希冲突

Hash 索引在任何时候都不能避免表扫描（哈希冲突需要和数据记录比较）。

哈希冲突

Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

5.3 支持情况（InnoDB）

InnDB存储引擎支持得哈希索引是自适应的，会根据表得#使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

然后MEMORY存储引擎是基于哈希的，数据都存放于内存，适用于临时表。

6 索引使用策略及优化

6.1 使用联合索引

建一个联合索引(a,b,c)，实际相当于建了(a)、(a,b)、(a,b,c)三个索引，所以尽量的扩展索引而不是新增，比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

6.1.1 好处

减少分别建立索引的开销；方便实现覆盖索引；避免单值索引的回表操作（一个索引查询到结果后再回表应用另一个索引），进一步提高效率

6.1.2 缺点和建议

相比单值索引占用更多的磁盘空间，降低增删改查的效率，因此单表尽可能不要超过一个联合索引，单个联合索引不超过3个字段。

6.1.3 最左前缀匹配原则

最左优先，即以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

连续使用索引

联合索引当然还是一颗B+树，只是健值数量不是一个，而是多个（a,b,c）并按顺序排列（类似order by a,b,c）

遇范围查询失效

a值相等的情况下，b值又是按顺序排列的，但a范围查询时b值是无序的，所以遇上范围查询就会停止，剩下的字段都无法使用索引

mysql查询优化器（自动调整顺序）

sql语句中字段的顺序不需要和联合索引中定义的字段顺序一致，查询优化器会自己调整顺序

非左匹配是否能够触发索引

只要是索引，或某个联合索引的一部分，mysql都可能会采用index类型的方式扫描，所以非左匹配也会触发索引，但不是平时说的那种快速取出（ref）。MySQL 8 都不需要最左匹配就能用上联合索引了，当最左索引基数不大时，会隐式的把索引前缀拼上（range），否则全索引扫描，但又如下局限性：
需要联合索引；
查询仅引用一个表；
查询不能用GROUP BY 或者DISTINCT；
查询只能用一个索引，索引需要覆盖查询的值；
查询条件必须是常量，这里包括IN()运算符。

ref类型表示mysql会根据特定的算法快速查找到某个符合条件的索引而不是会对索引中每一个数据都进行扫描判断，从而快速取出数据。

6.2 建议加索引的关键字

对 where,on,group by,order by 中出现的列使用索引

6.3 区分度问题

尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例。

6.4 数据列大小问题

对较小的数据列使用索引,这样会使索引文件更小；同时内存中也可以装载更多的索引键，较长的字符串可以考虑使用前缀索引，如；

前缀索引

索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值，范围从1/T到1之间，索引的选择性越高则查询效率越高。

计算选择性：select 1.0count(distinct left(name,3))/count() from test，分别截取name字符的前几个字母，最后选取的计算值要接近整个取整个name时得出的计算值，然后再选中占用空间小的
创建前缀索引： alter table city_demo add key (city(6))；

6.5 避免操作索引列

索引列不能参与计算，保持列“干净”，计算（!=或<>，null 值判断）、函数、类型转换(自动or手动,比如字符串与数字比较不使用索引)，会导致索引失效而转向全表扫描

6.6 注意索引数量

不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。因为我们修改表数据时，索引也需要进行调整重建，建议6个以下

6.7 like

对于like查询，”%”不要放在前面。