【MySQL】索引简述

敞开的大门
1,354 阅读11分钟

索引是用来快速查找记录的数据结构，相当于书籍的目录。在 MySQL 中，索引是在存储引擎层实现的，因此没有统一的索引标准，每个存储引擎都可以选择适合自己的索引，甚至像 CSV 和老版 Archive 引擎会选择不支持索引。

  • 本文是笔记类型文章，只有结论没有证明，可用于复习巩固知识，不能用于新知识的学习。如有错误，恳请指正，不胜感谢。
  • 转载请于文首标明出处：【MySQL】索引简述 - 掘金 (juejin.cn)

系列文章：

一、索引的类型

MySQL 中索引的概念，可以自顶向下地按「定义」、「使用」、「存储」和「结构」这几个层级划分：

  • 按逻辑定义分，分为主键索引与普通索引、单列索引与多列索、唯一索引与非唯一索引、前缀索引与后缀索引。
  • 按使用方式分，分为覆盖索引和非覆盖索引。
  • 按存储方式分，分为聚集索引和辅助索引。
  • 按物理结构分，分为 B 树索引、B+ 树索引、T 树索引、哈希索引、全文索引和 R 树空间索引等。

二、按逻辑定义分

2.1 主键索引 vs 普通索引

主键是数据的唯一标识，以主键为 Key 的索引称为主键索引。
普通索引就是非主键索引，也就是除主键索引之外的一切索引。

2.2 单列索引 vs 多列索引（联合索引）

索引可以由多个字段组成，由单独一列作为 Key 的索引称为单列索引。
由多列组成作为 Key 的索引称为多列索引，也叫联合索引。

2.3 唯一索引 vs 非唯一索引

添加了唯一约束的索引就是唯一索引。唯一索引的列中不得出现相同的 key，即构成索引的字段不得出现重复。主键就是最典型的唯一索引。而非唯一索引就是没有这种约束的索引。

2.4 前缀索引 vs 后缀索引

前缀索引指利用某字段数据的前缀建立的索引，对于长字符串类型的字段我们不可能把整个字符串作为索引存储起来，因此可以截取其最前面 N 个字符进行索引，这就是前缀索引。前缀索引通常用于超长 Varchar、Blob 或者 Text 类型字段。它所选择的前缀长度取决于其索引选择性。

索引选择性指不重复的索引值和表中总记录数的比值，比如唯一索引不存在重复索引值，因此索引选择性为 1。如果一个索引存在的重复很多，那么不重复的索引值就很少，索引选择性就越低。因此索引选择性越高越好。

MySQL 不支持后缀索引，但是我们可以自己实现后缀索引，例如将字符串翻转过来再建立前缀索引，此时就是后缀索引了。

三、按使用方式分 - 覆盖索引 vs 非覆盖索引

覆盖索引是一个从查询方式来讲的概念，如果一个辅助索引的索引 Key 包含了查询需要的所有字段，那我们就称之为覆盖索引。

Q：按照覆盖索引的方式去使用，有什么用呢？
A：看后面「覆盖索引」就知道了。

四、按存储方式分 - 聚集索引 vs 辅助索引

聚集索引有的叫聚簇索引，也有的叫索引组织表，指的是一种数据存储方式，指数据与索引的数据结构存储在一起。
如 InnoDB 的主键索引中所有叶子节点都存储了对应行的数据。因为数据肯定只是存储在一个地方，所以一个表只能有一个聚集索引。如果 InnoDB 表没有定义主键，那么它会选择一个唯一非空索引代替，如果没有唯一非空索引，则会隐式地创建一个整型主键作为聚集索引的 Key。

辅助索引就是除了聚集索引之外的其他索引，他们不存储数据行，而是存储索引字段本身和数据行的指针（也就是主键ID）。因此通过辅助索引查询数据，实际上是先用它找到主键，再根据主键索引获取数据。

使用主键查询之所以快的原因就是，主键和数据放在一起，不需要像辅助索引一样进行二次查询。

辅助索引由于只保存主键值，如果主键和索引都是长整型，叶子节点才 16 字节，这种情况下三层的 B+ 树可以达到十亿条数据。

此时回答上面覆盖索引的作用：

比如我们的辅助索引是基于 name, age, gender 建立的，如果查询条件能命中索引并且只查询 SELECT name, age, gender、SELECT name, age 或者 SELECT name，那么在查询时，就不需要根据主键去聚集索引那里查找行记录了，因为索引的 Key 已经包含了我们要的所有数据。👍

五、按物理结构分

5.1 B 树

B 树索引就是以 BTree 为数据结构存储的索引。

B 树全称多路搜索树，B 的含义为 Balance 而非 Binary，是为磁盘等外存储设备设计的一种平衡查找树，MyISAM 就是使用 B 树作为实现索引的数据结构。

image.png

以三阶 B 树为例。每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的 key 和三个指向子节点的指针，两个 key 将数据划分成的三个范围域。由于范围域和 key 都是升序排序，所以对于一块载入的磁盘空间可以使用二分查找查找元素。如果命中关键字则直接返回数据，如果不命中则进入相应范围的子节点查询。

⚠️ B树的阶数指一个节点的子节点数量。需要注意的是，「阶数」表示子节点数的这个概念，是B树家族专属的。

  • 优点：由于 B 树每个节点内部都是有序的，因此节点内数据查询可以使用二分查找的方式查询，时间复杂度为 O(log⁡2N)O(\log_2N)，而且也非常适合范围查询。同时，一棵 M 阶树的节点会存储 M 个指针和 M-1 个 Key，因此整体查询的渐进复杂度为 O(log⁡MN)O(\log _MN)。这样的查询速度还是很快的，而且由于是多叉树，树的深度减少，更适合磁盘检索。

  • 缺点：B 树存在的问题在于，非叶子节点也会存储数据，由于每个节点大小是固定的，如果节点存储的数据越大，那么节点能够保存的指针数量就会越少。B树阶数越小，深度就越深，IO 次数就会增加。对于普通索引还好，只是将主键 ID 作为 data 存储，但对于主键索引，data 就是整条记录，在这种情况下，整个 B 树的阶数会变得非常小。同时主键索引又被极高频地使用，因此这个缺点还挺严峻的。

5.2 B+ 树

B 树索引就是以 B+Tree 为数据结构存储的索引。

B+ 树是 B 树的一种优化，InnoDB 就是使用 B+ 树作为实现索引的数据结构。

B+ 树

由于 B 树的问题是父节点也存储数据，导致父节点的指针数量减少，因此 B+ 树的优化就在于，将所有数据都存放在叶子节点，父节点只存放 key 和指针，不存放数据，这样就可以大大提高父节点的指针数了。

InnoDB 中默认页大小为 16K，假设指针长度为 64 位，主键的类型为长整型，二者都为 8 字节。那么一个 B+ 树父节点可以存储的指针数是：16 * 1024 / (8 + 8) = 1024。因此一个父节点可以拥有 1024 个子节点。深度为三的 B+ 树总共拥有 10242=1,048,5761024^2 = 1,048,576，也就是约一百万个叶子节点，也就是一百万个页。如果一行数据是 512 字节，那么三层的 B+ 数可以存储三千多万行记录了。

5.3 哈希索引

哈希索引基于哈希表实现，这就使得它的查询速度非常快，基于哈希索引的存储引擎有 Memory 和 NDB 引擎。Memory 的哈希索引有以下几个特点：

  1. Memory 采用拉链法的方式解决哈希冲突，因此哈希冲突多的时候，查询和修改都会比较慢。
  2. Memory 的哈希索引只存储索引值和行指针，因此无法实现覆盖索引。
  3. Memory 的哈希索引是无序的，只有精确匹配才能进行有效的查询。因此无法使用范围查询，只支持等值比较查询，如 =、in 和 <=>。
  4. 哈希索引是绝对不支持部分匹配的，因为计算哈希值必须索引的所有字段都参与计算。

<=> 用于和 NULL 进行比较：

(a = NULL) 结果为 NULL
(NULL = NULL) 结果为 NULL
(a <=> NULL) 结果为 0
(NULL <=> NULL)  结果为 1

5.4 全文索引

全文索引是一种特殊的索引，如它的名字所描述，它是针对大文本类型所设计的索引。对于一个超长的字符串类型字段，我们不可能将它所有的内容都作为索引，全文索引通过抽取文本关键词的方式生成索引。在使用时通过关键字的匹配进行查询过滤。

全文索引最初只对英文有效，因为可以利用空格进行分词，MySQL5.7.6 开始，引入了一个新的全文分析器来解决这个问题，并且对 MyISAM 和 InnoDB 引擎都有效。

六、索引的匹配方式

  • 全值匹配：指的是索引在匹配时使用索引中的所有列进行匹配，如 WHERE name = 'Allen' AND addr = 'Guangzhou'。
  • 匹配最左前缀：指的是在多列索引的匹配中，只需要使用到最左几列即可完成匹配，如 WHERE name = 'Allen'。
  • 匹配列前缀：指的是匹配某一列值的开头部分，如 WHERE name LIKE 'All%'。
  • 匹配范围值：指的是通过范围进行匹配，如 WHERE id > 1024 AND id < 2048。
  • 精确匹配再范围匹配：如 WHERE name = 'Allen' AND first_name LIKE 'Thrump'。
  • 覆盖索引匹配：指的是只访问多列索引所覆盖到的列的数据，如 WHERE teacher_id = 1024 AND student_id > 202000000 AND student_id < 202000103。

七、索引失效情况

只有合法地使用索引，才能够让索引生效，否则索引只会徒占空间，对于多列索引而言，存在着许多查询生效的规则，这些规则存在的原因，都是因为多列索引在建立时，先由前面的列决定顺序，再由后面的列决定顺序。如先排第一列，再排第二列，再排第三列等等。因此，后面列的顺序是基于前面列的，一旦查询条件中没有用到前面列的顺序，后面列的索引就没法生效了：

  1. 如果不按照索引的最左列开始向右进行多列查询，整个索引就会失效。
  2. 如果跳过索引的中间列，后面的索引就会失效，只能用到前面按序的列。
  3. 如果查询中某个列有范围查询，则后面的索引就会失效。
  4. 如果查询中有 like 条件，只有匹配最左前缀才能够使索引生效，同时后面的列就不生效了。

MySQL 虽然有查询优化器，但它不会重排用户查询的 Where 条件。它的优化工作是：

  • 选择索引：扫描全表 vs 使用索引 A vs 使用索引 B vs 使用索引合并？
  • 选择表的访问方式：使用索引进行快速查找、范围扫描，还是无可奈何地全表扫描？
  • 确定连接顺序：JOIN 哪个表先、用哪种 JOIN 算法（Nested Loop, Hash Join, Sort-Merge）。
  • 决定何时应用过滤条件：是边读数据边过滤（利用索引）还是先读再过滤？但这不等于改变条件在 WHERE 子句中的显式书写顺序。

八、索引提示

MySQL 支持显式地告诉优化器使用哪个索引，这称为索引提示（INDEX HINT）。使用 USE INDEX(index_name) 即可使用索引提示，但最好在以下情况才使用索引提示：

  • MySQL 优化器选择了错误的索引，我们必须主动选择最优的索引。
  • 某个查询可以使用的索引太多，以至于优化器选择索引的时间超出了利用索引节省的时间，此时我们直接指定需要使用的索引即可避免优化器的分析耗时。
avatar
文章
阅读
粉丝