MySQL索引

写在前面

索引的本质是一种数据顺序关系，所以索引的建立，生效与否，推荐使用的索引建设方式都与这个本质息息相关。例如联合索引的本质就是从左向右的字段比较顺序，因此要符合最左前缀匹配原则，以a_b_c的联合索引为例，索引本身的建立是按照先比较a，再比较b再比较c的方式来有规律的将数据放到索引树的节点上的，如果跳过a，b，直接使用c作为条件，是无法使用索引的，因为无法判断a，b的大小关系，进而无法判断数据在索引树的位置。

一索引分类

1.按数据结构分类：B树索引，B+树索引，哈希索引，全文索引

B树索引 B树是一种多路平衡搜索树，具有如下特点：

1. 每个节点可以存储多个键值，并且按升序排序，节点中的键值用于分割子节点的范围。

2. 所有叶子节点的深度相同，保证了查询的时间复杂度始终为 (O(\log n))。

3. 节点的键值个数范围：假设 B 树的阶（order）为 (m)，则：

4. 一个节点最多有 (m) 个子节点（即最多 (m-1) 个键）。

5. 一个节点至少有 (m/2) 个子节点（根节点除外）。

6. 插入与删除操作会自动调整树的结构，保证树的平衡性。

B树的叶子节点可以存储数据，也可以只存储键值，B树一个节点的大小通常是一个数据页大小，这样每次从磁盘加载数据时可以恰好加载一个数据页的信息

B+树索引

B+树只在叶子节点存储数据，非叶子节点存储的是索引的值，即键值，叶子结点存储键值对应的数据，同时叶子结点之间通过链表相互连接，构成一个有序链表，B树中任何一个key只出现在一个结点中，而B+树可以出现多次。 查找过程中，B树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束

B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低；

一个B+树索引可以索引多少行数据？ 以InnDB为例，最小存储单元页大小为16k，假设索引主键为bigint型，长度为8字节，InnDB中的指针长度为6字节，这样一个索引项大小为14字节，一个16k大小的节点可以存下的索引项是16k/14=1170，即一个节点能够索引1170个子节点。由于InnDB主键索引叶节点存储了记录全部信息，如果一行记录大小为1k，一个叶节点的记录数就为16k/1k=16，对于一个高度为3的B+树，能够索引记录数是1170117016=21902400条，所以高度为3的B+树索引就足够索引千万级的数据了。 一次页查找就代表一次IO，因此通过主键索引只需要3次IO即可找到对应数据，如果是通过辅助索引查询则需要6次IO访问得到最终的数据页。

哈希索引

InnoDB存储引擎有一个被称为自适应哈希索引的特性。当InnoDB发现某些索引值被非常频繁地被访问时，它会在原有的B-tree索引之上，在内存中再构建一个哈希索引。这就让B-tree索引也具备了一些哈希索引的优势，例如，可以实现非常快速的哈希查找。这个过程是完全自动化的，用户无法进行控制或者配置。不过，可以通过参数彻底关闭自适应哈希索引这个特性，哈希索引根据索引值计算一个hash值，通过hash值找到数据。

全文索引

FULLTEXT是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干、复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。 在相同的列上同时创建全文索引和基于值的B-tree索引并不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。全文索引的基本原理通过将文本进行分割为多个片段token，而后查询时，根据token片段进行匹配。更详细的介绍，参见：

juejin.cn/post/732471…

2. 按照物理存储结构：聚簇索引，非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行。 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

有些数据库服务器允许你选择用于聚簇的索引，但是MySQL内置的存储引擎都不支持这个特性。InnoDB根据主键聚簇数据。 所以InnodB的主键索引和聚集索引是等价的。

非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的，存储的是指向行数的地址， 跟聚簇索引一样，非聚簇索引也依赖于有序的数据。这应该是非聚簇索引最大的缺点了。当查到索引对应的指针或主键后，由于字段不全【select * 的时候】，可能还需要根据指针或主键再到数据文件或表中查询。【回表查询通常是慢查询的一个常见场景，在使用过程中需要尽量避免回表，还有一个常见场景是深度分页】

3.按照应用维度划分【这个划分是最乱的】

• 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
• 普通索引【辅助索引/二级索引】：仅加速查询。
• 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
• 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
• 前缀索引：对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符

索引按照物理结构不同可以分为主键索引和辅助索引。

主键索引

主键:表中标识一行数据的唯一键，不可以为空，不可以重复，原则上可以由多个字段组成，但实际应用中通常以自增ID作为主键，主键索引就是在主键的基础上创建的索引，同时由于InnodB的机制，聚合索引只能制定主键作为索引，所以主键索引在mysql innodB场景下与聚合索引等价。主键索引的组织方式实际上就是数据库表存储的结构，因此一个表只有一个主键索引。

普通索引【辅助索引/二级索引】 使用非主键之外的字段建立的索引，都是辅助索引，辅助索引的叶子结点存放的值有两部分，一部分是索引组成字段对应的键值，以及一个指向具体行的指针，如果查询时查询的字段被包含在索引字段范围内，直接返回，如果超过了索引字段的范围，需要按照指针指向的位置找到具体的主键索引，然后再找到具体的行数据，这个过程即回表操作。

唯一索引

唯一索引的分类是指，索引字段具有唯一性，即索引不允许重复，但是允许有空之【主键索引不允许有空值】

覆盖索引

覆盖索引实际上不属于一种索引的分类，覆盖索引指的是，数据查询过程中，查询需要的字段可以在索引组成范围内找到，即不需要回表操作。

联合索引

联合索引是相对于单个字段构成的索引而言的，使用多个字段构成的索引，比较时按照顺序进行比较 a_b_c 三列中，a相等，比较b，b相等比较c 所以应用联合索引时需要满足最左前缀匹配原则。

索引合并：索引合并指的是，当查询条件中，包含多个索引时，查询会先按照各自索引找到索引条件下的数据范围，然后根据条件进行并集，交集，排序合并【范围查询时 a<1 or b>2 的结果集不能直接取a<1 和 b>2 的并集】，这个结果再统一进行回表，可以提高查询的效率，但是这个效率通常低于联合索引。

前缀索引

字段存储为字符串时，通常字符串很长，但是制定字符串的前n位即可有区分度的将数据区分开，使用字段 = “具体值”使用"xxx%"模糊搜索，都可以踩到索引，但是“%xxxx”这种会有问题，因为前缀索引建立的基础是对字段的前n位比较建立的索引。

二 索引的有效与失效

在了解了索引的原理的基础上，索引有效性的判断是比较好理解的，即，查询条件能不能在查询过程中，正常的比较和排序，以最左匹配前缀原则为例，为什么不满足最左匹配前缀的条件会失效，索引就是从根节点一级一级比较，假设一个节点只有两个子节点，当小于当前节点时，向左侧搜索，大于时，向右侧搜索，，当建立联合索引 a_b_c时，索引的构建是以先比较a，再比较b，再比较c的顺序建立的，此时若只指定a，c按索引查询时，a相等条件下，我们无法根据c的值去判断子节点在哪个子节点上，所以索引自然而然就失效了

联合索引不满足最左匹配原则。

模糊查询最前面的为不确定匹配字符。【%xxx】失效，【xxx%】有效的区别就在于，后者是可比较的，但前者由于通配符的存在，是无法比较的，

下面几种原理类似，但是问题就是下面几种情况不一定严格失效，如果操作之后还能保证有效比较，那一般不会失效，但是破坏了比较的有效性，通常就会失效，WHERE FUNCTION(column) = value 这种使用函数，执行器实际上无法根据value去反向推测column的原始值【不能保证一一对应】，所以索引会失效，即索引结构与函数计算结果不匹配，优化器无法有效地将函数条件转换为索引可用的形式，函数操作改变了数据的原始特征和分布 破坏了利用索引时的有效性，

索引列参与了运算。

索引列使用了函数。【不严格失效，使用函数索引【即利用函数操作建立的索引，其实相当于将函数带来的不确定性确定了】，覆盖索引时还是会生效的】

索引列存在类型转换。【隐式类型转换，会失效，因为无法知道原始值的类型，比较也就谈不上了】

索引列使用 is not null 查询。【不严格失效，执行器会根据表数据分布进行优化，当null值占比超过30%时，有可能会选择索引，但当非空值多时，走索引的效果可能不如直接回表】