MySQL索引

基本概念

索引(index)：帮助数据库高效获取数据的数据结构，索引和数据就是位于存储引擎中

优点

提高数据查询的效率，降低数据库的I0成本，
通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

缺点

索引会占用存储空间。
索引大大提高了查询效率，同时却也降低了insert、 update、delete的效率。

索引操作

创建索引

1

create [unique] index index_name on table_name(字段名,...)

查看索引

1

show index from table_name

删除索引

1

drop index index_name on table_name

提示

主键字段，在建表时，会自动创建主键索引。
添加唯一约束时，数据库实际上会添加唯一索引。

索引加速查询的原理

数据库文件是存储在磁盘上的，磁盘 I/O 是数据库操作中最耗时的部分之一。没有索引时，数据库会进行全表扫描（Sequential Scan），这意味着它必须读取表中的每一行数据来查找匹配的行（时间效率为 $O(n)$）。当表的数据量非常大时，就会导致大量的磁盘 I/O 操作。

有了索引，就可以直接跳到索引指示的数据位置，而不必扫描整张表，从而大大减少了磁盘 I/O 操作的次数。

索引分类

按数据结构分类

B+Tree 索引：通过树形结构存储数据，适用于范围查询（如 BETWEEN ）和精确查询（如 = ），支持有序数据的快速查找、排序和聚合操作。是 MySQL 默认的索引类型，常用于 InnoDB 和 MyISAM 引擎。
HASH 索引：基于哈希表的结构，适用于等值查询（如 =），查询速度非常快，但不支持范围查询（如 > 、< ）。哈希索引不存储数据的顺序，常用于 Memory 引擎。
Full-Text 索引：用于全文搜索，将全文分词，通过存储词与文档的映射，支持模糊匹配和关键字搜索。特别适合用于大文本字段，如 TEXT 类型的列，用于查找包含特定词语的记录。

存储引擎对索引的支持情况

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）

创建的主键索引和二级索引默认使用的是 B+Tree 索引。

B+树索引

B+ 树是 B 树的升级版。具有以下特点

而且每个节点里的数据是 按主键顺序存放 的。每一层父节点的索引值都会出现在下层子节点的索引值中
B+ 树中的非叶子节点都不存储数据，只存储索引。
叶子节点中存储了所有的数据，并且构成了一个从小到大的有序双向链表，使得在完成一次树的遍历定位到范围查询的起点后，可以直接通过叶子节点间的指针顺序访问整个查询范围内的所有记录，而无需对树进行多次遍历。这在处理大范围的查询时特别高效。

B+树索引结构

一棵三层的 B+ 树在 MySQL 中可以存储大约 2000 万条记录 。

提示

为什么选择B+树作为索引的数据结构

高效的查找性能：B+ 树是一种自平衡树，每个叶子节点到根节点的路径长度相同，B+ 树在插入和删除节点时会进行分裂和合并操作，以保持树的平衡，但它又会有一定的冗余节点，使得删除的时候树结构的变化小，更高效。查找、插入、删除等操作的时间复杂度为 O(log n)，能够保证在大数据量情况下也能有较快的响应时间。
树的高度增长不会过快，使得查询磁盘的 I/O 次数减少：B+ 树不像红黑树，数据越多树的高度增长就越快。它是多叉树，非叶子节点仅保存主键或索引值和页面指针，使得每一页能容纳更多的记录，因此内存中就能存放更多索引，容易命中缓存，使得查询磁盘的 I/O 次数减少。
范围查询能力强：B+ 树特别适合范围查询。因为叶子节点通过链表链接，从根节点定位到叶子节点查找到范围的起点之后，只需要顺序扫描链表即可遍历后续的数据，非常高效

B树和B+树的区别以及不使用B树作为索引的原因

节点结构不同
- B 树每个节点都存储了完整的数据
- B+ 树非叶子节点仅存储 key 和指针，完整数据存储在叶子节点。这使得 B+ 树可以在内存中存放更多索引页，减少磁盘查询次数。
叶子节点的独立性
- B+ 树叶子组成了链表，便于区间查找。
- B 树的叶子节点都是独立的
查询效率的稳定性
- B+ 树查询时间更平均、稳定，都需要从根节点扫描到叶子节点。
- B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。

查询过程

数据从根节点找起，根据比较数据键值与节点中存储的索引键值，确定数据落在哪个区间，从而确定分支，从上到下最终定位到叶子节点
叶子节点存储实际的数据行记录，但是一页有 16kb 大小，存储的数据行不止一条
叶子节点中数据行以组的形式划分，利用 页目录 结构，通过二分查找可以定位到对应的组
定位组后，利用链表遍历就可以找到对应的数据行

Hash索引

基于哈希表的结构，适用于等值查询（如 =），查询速度非常快，但不支持范围查询（如 > 、< ）。哈希索引不存储数据的顺序，常用于 Memory 引擎。

按物理存储分类

从物理存储的角度来看，索引分为聚集索引和辅助索引

聚簇索引

聚簇索引就是索引结构和数据一起存放的索引。查询到了索引就可以直接访问到整个记录。

InnoDB 中的主键索引就属于聚簇索引。在聚簇索引中，它的B+树索引的叶子节点中包含整条记录，可以直接访问完整数据。在整个数据库有且只有一个

非聚簇索引

非聚簇索引就是索引结构和数据分开存放的索引，查询到了索引还需要通过索引中指向记录的指针访问记录。

MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

对于非聚簇索引，它的B+树索引的叶子节点只包含主键值和索引值。

通过非聚簇索引查找记录的过程（回表）

找到二级索引中的 B+Tree 的索引值，找到对应的叶子节点，
要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行

当非聚集索引同时是 覆盖索引 时就不需要回表。比如使用该二级索引去查找主键

提示

回表

回表是指在使用二级索引（非聚簇索引）作为条件进行查询时，由于二级索引中只存储了索引字段的值和对应的主键值，无法得到其它数据。如果要查询数据行中的其它数据，需要根据主键去聚簇索引查找实际的数据行

覆盖索引

覆盖索引：一个二级索引包含（或者说覆盖）所有需要查询的字段的值，从而使查询可以仅通过访问二级索引而不需要访问实际的表数据（主键索引）。

表现为查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到

按字段特性分类

主键索引：建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
唯一索引：建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
普通索引：普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。
前缀索引：对文本的前几个字符创建索引，相比普通索引建立的数据更小

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

建立在单列上的索引称为单列索引，比如主键索引；
建立在多列上的索引称为联合索引

联合索引

联合索引：通过将多个字段组合成一个索引

联合索引的匹配过程遵循最左前缀匹配原则

提示

最左匹配原则

最左前缀匹配原则指的是在使用 联合索引 时，查询条件必须从索引的最左侧开始匹配。MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据。最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）就会停止匹配。在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配

假设有一个联合索引 (column1, column2, column3) ，其从左到右的所有前缀为 (column1)、(column1, column2)、(column1, column2, column3)（创建 1 个联合索引相当于创建了 3 个索引），包含这些列的所有查询都会走索引而不会全表扫描。

底层原理：因为联合索引在 B+ 树中的排列方式遵循“从左到右”的顺序，例如联合索引 (first_name, last_name, age) 会按照 (first_name, last_name, age) 的顺序在 B+ 树中进行排序

根据最左匹配原则，建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中 建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

对联合索引的优化：索引下推

索引下推（Index Condition Pushdown, ICP）是一种减少回表查询，提高查询效率的技术。它允许 MySQL 在使用索引查找数据时，将部分查询条件下推到存储引擎层过滤，从而减少需要从表中读取的数据行，减少了 IO（本该由 Server 层做操作，交由存储引擎层因此叫做 “下推” ）。

使用了聚簇索引（主键）查询，索引下推也不会生效，因为其是对于非聚簇索引来进行减少回表次数。

索引的设计原则

选择合适的字段建立索引

不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段 ：创建索引的字段应该是查询操作非常频繁的字段。被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
字段有唯一性限制的：比如商品编码；

不需要创建索引的情况

字段中存在大量重复数据，不需要创建索引：比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少不需要建立索引：表数据太少的时候不需要创建索引
经常更新的字段不用创建索引：因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。
长文本字段（非常长的 varchar 或 JSON、BLOB 和 TEXT 类型，这些类型的列通常包含大量数据）
- 数据量大排序时都无法用内存排，只能利用磁盘文件，排序很慢。
- 数据量大，每个页能存放的行数就少，扫描查询可能会涉及大量的 I/O。

提示

索引并不是越多越好。因为索引不论从时间还是空间上都是有一定成本的

从时间上
- 每次对表中的数据进行增删改(INSERT、UPDATE 或 DELETE)的时候，索引也必须被更新，这会增加写入操作的开销。例如删除了一个 name 为面试鸭的记录，不仅主键索引上需要修改，如果 name 字段有索引，那么 name 索引也需要修改，所以 索引越多需要修改的地方也就越多，时间开销就大了，并且 B+ 树可能会有页分裂、合并等操作，时间开销就会更大。
- MySQL 有个查询优化器，它需要分析当前的查询，选择最优的计划，这过程就需要考虑选择哪个索引的查询成本低。如果索引过多，那么会导致优化器耗费更多的时间在选择上。
从空间上
- 每建立一个二级索引，都需要新建一个 B+ 树，默认每个数据页都是 16kb，如果数据量很大，索引又很多，占用的空间可不小。

索引失效的情况和排查

索引失效情况

创建了联合索引，但查询条件未遵守最左匹配原则
在索引列上进行表达式计算、函数、隐式类型转换等操作
- 表达式计算和函数的失效原理：因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
- MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
以 % 开头的 LIKE 查询（左模糊或者左右模糊匹配）比如 LIKE '%abc'
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到

索引失效排查方法

使用 EXPLAIN 命令，通过在查询前加上**EXPLAIN**，可以查看 MySQL 选择的执行计划，了解是否使用了索引、使用了哪个索引、估算的行数等信息。

主要观察 EXPLAIN 结果以下几点：

type（访问类型）：这个属性显示了查询使用的访问方法，例如 ALL 、index 、range 等。当查询使用索引时，这个属性通常会显示为 index 或 range ，表示查询使用了索引访问。如果这个值是 ALL ，则表示查询执行了全表扫描，没有使用索引。
key（使用的索引）：这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是 NULL ，则表示查询没有使用索引。
rows（扫描的行数）：这个属性显示了查询扫描的行数，需要评估下扫描量。

索引优化方法

前缀索引优化

前缀索引：使用某个字段中字符串的前几个字符建立索引。使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

主键尽量自增

主键索引最好是自增的：

如果使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次 插入一条新记录，都是追加操作，不需要重新移动数据 ，因此这种插入数据的方法效率非常高。
如果使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为 页分裂 。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

避免在索引列上使用函数和表达式计算

在 where 子句中直接对列使用函数会导致索引失效，因为数据库需要对每行的列应用函数后再进行比较，无法直接利用索引。不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

避免对索引使用最左模糊匹配

因为索引 B+ 树是按照 索引值 有序排列存储的，只能根据前缀进行比较。所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。