MySQL索引类型与特性详解_简述mysql中索引类型有哪些,以及对数据库的性能的影响-优快云博客

本文链接：https://blog.youkuaiyun.com/jsiwhhsisisjs/article/details/149540669

1. 索引的基础概念

1.1 索引的本质与数据结构视角

索引，从本质上讲，是一种特殊的数据结构，由数据库管理系统（DBMS）精心维护。它不直接存储原始数据，而是存储表中一列或多列数据的有序副本，并附带指向实际数据行物理位置的“指针”或“地址”信息。这种机制使得数据库能够快速定位到所需的数据行，而无需遍历整个数据表。可以将其形象地比喻为一本书的目录或字典的部首检字表，通过目录或检字表可以迅速找到特定内容，极大地提升了查找效率。

从数据结构的角度来看，MySQL 中最常用的索引结构是 B+ 树。此外，根据不同的应用场景，还存在哈希索引、全文索引等多种数据结构。每种数据结构都有其独特的查询特性和适用范围，它们的设计决定了索引在不同操作下的性能表现。

索引的存在形式通常表现为独立的文件，与表数据文件分离（如 MyISAM 存储引擎），或者作为表数据文件的一部分（如 InnoDB 存储引擎的聚簇索引）。这种存储方式使得数据库在执行查询时，可以优先访问索引文件，从而避免了对庞大数据文件的全盘扫描。

1.2 索引的核心作用：提升查询效率

索引的核心价值在于显著提升数据查询（SELECT）的效率。当没有索引时，数据库在查找符合特定条件的数据时，可能需要执行全表扫描，即逐行检查表中的每一条记录。对于包含海量数据的大型表而言，全表扫描的开销是巨大的，会导致查询响应时间过长。索引通过提供一条预先构建的、有序的查找路径，有效地避免了这种低效的全表扫描操作。

索引能够快速定位到满足条件的少量数据行，从而大大缩小了数据库需要处理的数据范围。此外，索引结构（特别是 B+ 树）的设计将数据查找从随机的磁盘 I/O 操作转化为更高效的顺序 I/O 操作，显著减少了磁盘寻道时间，因为磁盘 I/O 是数据库性能的常见瓶颈。这种有序性不仅对精确查找至关重要，也为范围查询（如 BETWEEN、>、<）、排序（ORDER BY）和分组（GROUP BY）操作提供了高效的支持。

1.3 索引在数据库中的存在形式

索引在数据库中可以从多个维度进行分类，以反映其不同的特性和用途：

按数据结构分类： 主要包括 B+ 树索引、哈希索引、全文索引。
按物理存储分类： 分为聚簇索引（Clustered Index）和二级索引（Secondary Index 或 Auxiliary Index）。
按字段特性分类： 涵盖主键索引、唯一索引、普通索引、联合索引和前缀索引。

物理存储形式（以 InnoDB 存储引擎为例）：
在 InnoDB 存储引擎中，主键索引具有特殊的地位，它就是聚簇索引。这意味着表中的数据行是按照主键的逻辑顺序物理存储的，数据与索引是紧密绑定在一起的。聚簇索引的叶子节点直接包含了完整的行数据。这种设计对于性能有着深远的影响：选择一个合适的主键（例如，自增整数）可以确保数据写入是顺序的，这比随机写入快得多，从而优化了写入性能。

与聚簇索引相对的是二级索引。二级索引的叶子节点不存储完整的行数据，而是存储了对应数据行在聚簇索引中的主键值。这意味着当通过二级索引查询时，如果需要获取除索引列以外的其他数据，就需要进行一次额外的查找，即“回表”操作。回表操作需要使用二级索引查到的主键值，在聚簇索引中再执行一次 B+ 树查找，以检索完整的行数据，这会增加一次 B+ 树遍历和潜在的磁盘 I/O 开销。因此，理解“回表”成本对于优化查询性能至关重要，它促使了“覆盖索引”这一优化技术的出现，即当查询所需的所有列都包含在索引中时，可以避免“回表”操作，显著提升性能。

物理存储形式（以 MyISAM 存储引擎为例）：
与 InnoDB 不同，MyISAM 存储引擎的索引文件和数据文件是分离的。在 MyISAM 中，无论是主键索引还是其他类型的索引，它们都是非聚簇索引。这意味着索引的叶子节点存储的都是数据行的物理地址（指针），通过这些地址才能定位到实际的数据。MyISAM 表可以不定义主键，因为其数据存储不依赖于主键的物理顺序。

下表总结了索引类型的分类概览：

分类维度	具体类型	简要说明/特点
数据结构	B+树索引	多路平衡查找树，非叶子节点只存索引，叶子节点存数据并链表连接，适合范围查询和磁盘I/O优化。
	哈希索引	基于哈希表，通过哈希值快速定位数据，适合等值查询，不支持范围查询。
	全文索引	针对文本内容的分词检索，支持模糊查询。
物理存储	聚簇索引	数据行物理上按索引键值排序存储，叶子节点即数据行。一个表只能有一个。
	二级索引	叶子节点存储主键值（InnoDB）或数据行物理地址（MyISAM），需“回表”获取完整数据。
字段特性	主键索引	建立在主键列上，唯一且非空，InnoDB 中即聚簇索引。
	唯一索引	保证列值唯一，允许 NULL，一个表可有多个。
	普通索引	最基础类型，允许重复值。
	联合索引	在多列上创建，遵循最左匹配原则。
	前缀索引	对字符串类型字段的前 N 个字符建立索引，节省空间。

2. 常见索引类型及特性

MySQL 提供了多种索引类型，每种类型都有其独特的特性和适用场景。

2.1 主键索引 (Primary Key Index)

主键索引是建立在表的主键列上的索引。一张表只能拥有一个主键索引。它强制保证了索引列的唯一性和非空性，即主键列的值必须是唯一的，且不能包含任何 NULL 值。

在 InnoDB 存储引擎中，主键索引具有特殊的地位，它就是聚簇索引。这意味着表中的数据行是按照主键的顺序物理存储的。如果用户在创建表时没有显式定义主键，InnoDB 会按照特定的规则自动选择或生成一个聚簇索引：首先会选择表中第一个非空的唯一索引作为聚簇索引；如果依然没有，InnoDB 将自动生成一个隐式的、自增的 ID 列，并在此列上建立聚簇索引。这种聚簇特性使得主键索引在按照主键进行等值查询和范围查询时表现出极高的效率。

与 InnoDB 不同，MyISAM 存储引擎中的主键索引与普通索引在物理结构上并无本质区别，它们都属于非聚簇索引。MyISAM 索引的叶子节点存储的是数据行的物理地址，数据文件和索引文件是分离的。因此，MyISAM 表可以不定义主键。

2.2 唯一索引 (Unique Index)

唯一索引是建立在带有 UNIQUE 约束的列上的索引。它确保了索引列的所有值都是唯一的，但与主键索引不同的是，唯一索引允许存在 NULL 值，并且多个 NULL 值不会被视为重复。一张表可以拥有多个唯一索引。

与主键索引的异同：

相同点： 两者都强制保证了索引列值的唯一性。
不同点： 主键索引在一个表中只能有一个，而唯一索引可以有多个；主键索引不允许 NULL 值，而唯一索引允许 NULL 值；在 InnoDB 中，主键索引是聚簇索引，而唯一索引是二级索引。

唯一索引适用于需要保证特定列的唯一性，但该列并非表的逻辑主键，或者允许为空的场景，例如存储用户邮箱、身份证号或产品编号等。

2.3 普通索引 (Normal Index / Non-Unique Index)

普通索引是最基础的索引类型，建立在普通字段上，允许索引列的值重复。它不强制任何唯一性约束，其主要目的是为了加速查询操作。普通索引适用于任何需要加速查询的非唯一性字段，例如用户姓名、商品分类等，只要这些字段经常出现在查询条件中，就可以考虑为其创建普通索引。

2.4 联合索引 (Composite Index / Compound Index)

联合索引是在表的多个列上创建的索引，例如 INDEX (col1, col2, col3)。它将多个列组合成一个索引，可以有效地支持涉及这些列的复合查询。

最左匹配原则 (Leftmost Prefix Rule)： 这是联合索引的核心特性，也是其设计和使用的关键所在。MySQL 在利用联合索引进行查询时，会从索引的最左侧列开始匹配。只有当查询条件与索引的左侧前缀完全匹配时，索引才能被有效利用。一旦查询条件中出现了范围查询（如 >、<、BETWEEN、LIKE 'pattern%'）或者跳过了联合索引中的某个左侧字段，索引的后续部分就无法被利用。

示例： 对于一个联合索引 INDEX (a, b, c)：

WHERE a = 1：可以利用索引的 a 部分。
WHERE a = 1 AND b = 2：可以利用索引的 a, b 部分。
WHERE a = 1 AND b = 2 AND c = 3：可以利用索引的 a, b, c 部分。
WHERE a = 1 AND c = 3：只能利用索引的 a 部分，因为 b 被跳过，c 无法利用索引。
WHERE b = 2 AND c = 3：无法利用该联合索引，因为最左侧的 a 字段未被使用。
WHERE a = 1 AND b > 2 AND c = 3：可以利用索引的 a, b 部分，但由于 b 是范围查询，c 无法利用索引。

理解最左匹配原则对于查询优化至关重要。开发人员必须分析查询模式，以确定复合索引的最佳列顺序，将频繁用于等值查询的列放在索引的最前面，而将用于范围查询的列通常放在等值查询列之后。这直接影响了索引的利用率，避免了常见的“索引失效”问题。

联合索引适用于频繁出现多条件联合查询的场景。通过合理设计索引列的顺序，可以最大化索引的利用率，例如在查询订单时经常根据用户 ID 和订单状态进行筛选，可以考虑创建(user_id, order_status)的联合索引。

2.5 全文索引 (Full-Text Index)

全文索引是专门用于对文本内容进行高效检索的索引类型。它通过分词技术，将文本内容拆分成独立的关键词，然后对这些关键词建立索引，从而支持复杂的文本搜索。

全文索引主要适用于大型文本字段（如 TEXT、VARCHAR、CHAR 列）的模糊搜索，例如文章内容、商品描述或评论等。

局限性：

传统上，全文索引主要支持 MyISAM 存储引擎，但从 MySQL 5.6 版本开始，InnoDB 存储引擎也开始支持全文索引。
在 MySQL 5.7 版本之前，全文索引对中文分词的支持有限，但 MySQL 5.7 及更高版本内置了 ngram 解析器，能够支持中文、日文和韩文（CJK）的分词。
对于短文本或精确匹配查询，B+ 树索引通常比全文索引更高效。
全文索引需要使用特定的 MATCH AGAINST 语法进行查询，而不是普通的 WHERE 子句。

下表总结了常见索引类型的特性对比：

索引类型	是否唯一	是否允许 NULL	适用场景	特殊说明
主键索引	是	否	唯一标识行，等值/范围查询	InnoDB 中即聚簇索引，物理存储有序
唯一索引	是	是（多个 NULL 不冲突）	保证列唯一性，允许空值	InnoDB 中为二级索引
普通索引	否	是	加速非唯一性字段查询	最基础的索引类型
联合索引	否	是	多条件联合查询	遵循最左匹配原则，列顺序重要
全文索引	否	是	大文本内容模糊搜索	需分词，`MATCH AGAINST` 语法，MySQL 5.7+支持中文

3. 索引底层数据结构

3.1 B+ 树索引的结构特点与优势

B+ 树是 MySQL 索引最常用的底层数据结构，其设计巧妙地解决了数据库在磁盘上存储和检索数据时面临的性能挑战。

结构特点：

多路平衡查找树： B+ 树是一种多叉树，每个节点可以拥有多个子节点。这种“矮胖”的结构使得树的整体高度相对较低，从而在查找数据时能够显著减少磁盘 I/O 的次数。数据库索引通常存储在磁盘上，而磁盘 I/O 操作（读取一个磁盘页）远比内存操作耗时，因此减少 I/O 是提升数据库性能的关键。B+ 树的节点大小通常设计为与磁盘页大小一致（例如 16KB），以最大化每次 I/O 的效率。
非叶子节点只存储索引，不存储数据： B+ 树的中间节点（非叶子节点）仅存储键值（索引），而不存储实际的数据行记录。这一特性使得每个非叶子节点能够存储更多的索引条目，进一步降低了树的高度，从而减少了查找过程中所需的磁盘 I/O 次数。
所有数据都存储在叶子节点： 实际的数据记录（或指向数据记录的指针，取决于存储引擎）都集中存储在 B+ 树的最底层叶子节点中。这意味着任何查询都必须遍历到叶子节点才能获取完整数据。
叶子节点通过链表连接： B+ 树的所有叶子节点之间都通过一个有序的双向链表连接起来。

为何适合作为 MySQL 索引的主流结构：

减少磁盘 I/O： 如前所述，B+ 树“矮胖”的结构（低树高）是其最大的优势，它保证了查找一个数据所需的磁盘 I/O 次数极少，通常在常数级别，这对于磁盘驻留数据至关重要。
查询效率稳定： 由于所有数据都位于叶子节点，并且任何关键字的查找都必须从根节点遍历到叶子节点，因此所有查询路径的长度都相同，这使得查询效率更加稳定和可预测。
高效支持范围查询： 叶子节点之间通过链表连接的特性，使得 B+ 树在处理范围查询（如 WHERE column BETWEEN value1 AND value2）时表现卓越。数据库只需找到范围的起始点，然后沿着链表顺序遍历即可获取所有符合条件的数据，避免了 B 树需要中序遍历多个非叶子节点的复杂性，从而大大提高了范围查询的效率。
利于全表扫描（逻辑上）： 尽管全表扫描通常应避免，但在某些场景下（例如需要获取整个数据集并按主键排序），由于所有数据都存储在叶子节点并形成有序链表，对整个数据集进行逻辑上的顺序扫描时，只需遍历叶子节点链表即可，效率相对较高。
插入与删除效率高： B+ 树的插入和删除操作最多只涉及树的一条路径上的节点分裂或合并，并且具有自动平衡机制，确保了树的平衡性，因此插入和删除效率较高。

3.2 哈希索引原理、适用场景及与 B+ 树的对比

哈希索引是另一种重要的索引数据结构，其原理基于哈希表实现。它通过对索引列的值进行哈希计算，将哈希值直接映射到存储数据行的物理地址。

原理： 当进行查询时，数据库对查询条件中的值执行相同的哈希函数，然后根据计算出的哈希值直接定位到数据所在的存储位置。

适用场景： 哈希索引主要适用于等值查询，即 WHERE column = 'value' 这样的精确匹配查询。在 MEMORY 存储引擎中，哈希是默认的索引类型，它能够为精确查找提供极快的速度。

与 B+ 树的差异：

特性	B+树索引	哈希索引
数据结构	多路平衡查找树	哈希表
查询类型	等值查询、范围查询、模糊查询（前缀匹配）	仅支持等值查询
范围查询	高效支持，通过叶子节点链表遍历	不支持，哈希值无序
排序	支持，叶子节点有序	不支持，哈希值无序
模糊查询	支持前缀匹配（LIKE ‘value%’）	不支持（LIKE ‘%value%’ 或 LIKE ‘value%’）
I/O次数	对数级别，通常较少	理论上一次定位（平均 O(1)），但可能存在哈希冲突
内存占用	相对较大，需存储键值和指针	相对较小，仅存储哈希值和指针
适用场景	绝大多数通用数据库查询场景，特别是涉及范围、排序的查询	仅用于等值查询，如内存表或特定缓存场景
冲突处理	无哈希冲突问题	存在哈希冲突，需额外机制解决，可能影响性能
列计算	对索引列使用函数或表达式会导致索引失效	对索引列使用函数或表达式会导致索引失效

虽然哈希索引在等值查询上通常比 B+ 树更快，因为它能实现一次定位，而 B+ 树需要从根到叶子节点进行多次 I/O 访问，但这种速度是以牺牲有序性为代价的。哈希索引无法支持范围查询、排序以及部分模糊查询，这使得它在大多数实际数据库工作负载中不如 B+ 树通用。因此，在 B+ 树和哈希索引之间做出选择时，应根据具体的查询模式进行权衡。如果只执行精确的等值查找，哈希索引可能更优；但对于绝大多数涉及范围查询、排序或前缀匹配的数据库应用，B+ 树是不可或缺且更适合的选择，这进一步巩固了 B+ 树作为 MySQL 主流索引结构的地位。

4. 索引的创建与删除操作

4.1 创建各类索引的标准 SQL 语句

在 MySQL 中，创建索引可以通过 CREATE INDEX 语句或 ALTER TABLE 语句实现。

通用语法格式：

-- 使用 CREATE INDEX 语句
CREATE INDEX index_name ON table_name (column1 [(length)], column2 [(length)],...);

-- 或通过 ALTER TABLE 语句
ALTER TABLE table_name ADD INDEX [index_name] (column1 [(length)], column2 [(length)],...);

示例：

创建普通索引： 在 users 表的 name 列上创建一个名为 idx_name 的普通索引。

CREATE INDEX idx_name ON users (name);
-- 或
ALTER TABLE users ADD INDEX idx_name (name);

创建唯一索引： 在 users 表的 email 列上创建一个名为 uq_email 的唯一索引，确保邮箱地址的唯一性。

CREATE UNIQUE INDEX uq_email ON users (email);
-- 或
ALTER TABLE users ADD UNIQUE INDEX uq_email (email);

创建主键索引： 主键索引通常在创建表时定义，或通过 ALTER TABLE ADD PRIMARY KEY 添加。

-- 创建表时定义主键索引
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255)
);
-- 或为已有表添加主键
ALTER TABLE products ADD PRIMARY KEY (product_id);

创建联合索引： 在 orders 表的 customer_id 和 order_date 列上创建一个名为 idx_customer_order_date 的联合索引。

CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);
-- 或
ALTER TABLE orders ADD INDEX idx_customer_order_date (customer_id, order_date);

创建全文索引： 在 articles 表的 content 列上创建一个名为 ft_content 的全文索引。注意，全文索引通常只能用于 CHAR、VARCHAR 或 TEXT 类型的列，且需要特定的存储引擎支持（InnoDB 或 MyISAM）。
```
CREATE FULLTEXT INDEX ft_content ON articles (content);
-- 或
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
```
创建前缀索引： 如果对长字符串列进行索引，可以指定一个前缀长度，以节省存储空间并提升索引效率。例如，对 users 表的 address 列的前 20 个字符创建索引。
```
CREATE INDEX idx_address_prefix ON users (address(20));
```

4.2 删除索引的 SQL 命令及注意事项

删除索引同样可以通过 DROP INDEX 语句或 ALTER TABLE 语句实现。

SQL 命令：

-- 使用 DROP INDEX 语句
DROP INDEX index_name ON table_name;
-- 或通过 ALTER TABLE 语句
ALTER TABLE table_name DROP INDEX index_name;

-- 删除主键索引（注意：主键索引通常不能直接通过 DROP INDEX 删除，需删除约束）
ALTER TABLE table_name DROP PRIMARY KEY;

示例：

删除名为 idx_name 的普通索引：

DROP INDEX idx_name ON users;
-- 或
ALTER TABLE users DROP INDEX idx_name;

删除主键索引：
```
ALTER TABLE products DROP PRIMARY KEY;
```

需要注意的是，对于通过 PRIMARY KEY 或 UNIQUE 约束创建的索引，通常不能直接使用 DROP INDEX 语句删除。而是需要删除其对应的约束，例如使用 ALTER TABLE... DROP CONSTRAINT 或 ALTER TABLE... DROP PRIMARY KEY。

注意事项：

避免删除正在被频繁使用的索引： 删除索引会立即影响依赖该索引的查询性能，可能导致查询变慢甚至服务不可用。在生产环境中删除索引前，务必进行充分的评估和测试，确保不会对业务造成负面影响。
确认索引名称： 在执行删除操作前，务必确认索引的准确名称，避免误删。可以通过 SHOW INDEX FROM table_name; 命令查看表的现有索引信息。

4.3 创建索引时的关键考量

创建索引并非越多越好，不当的索引设计反而可能降低数据库性能。以下是创建索引时需要考虑的关键因素：

索引选择性 (Cardinality)： 索引的选择性 = 不重复的索引值数量（基数，Cardinality） / 数据表总记录数（#T），范围从 $1/#T1/\#T$ 到 $1$ 之间。选择性越高，索引的过滤能力越强，查询效率越高，因为 MySQL 在查找时可以过滤掉更多的行。唯一索引的选择性是 $1$ ，是最好的选择性。例如，性别字段只有“男”、“女”等少数几个值，选择性很低，不适合创建索引。而用户 ID、身份证号等字段，其值几乎不重复，选择性高，非常适合创建索引。
对于长字符串列，可以考虑使用前缀索引，即只对字符串的前几个字符建立索引。这可以大大节省索引占用的存储空间，并减少 B+ 树的层级，从而减少磁盘 I/O 次数。选择前缀长度时，应选择足够长的前缀以保证较高的选择性，同时又不能太长以节约空间。
避免过度索引 (Over-indexing)： 过多的索引会带来多方面的负面影响：
- 增加存储空间： 每个索引都需要占用额外的磁盘空间。
- 降低写操作性能： 每次对表进行插入（INSERT）、更新（UPDATE）或删除（DELETE）操作时，数据库不仅要修改数据行，还需要维护所有相关的索引结构，这会增加额外的 I/O 和 CPU 开销，从而降低写操作的效率。
- 优化器选择困难： 过多的索引会使查询优化器在选择最佳执行计划时面临更多选择，可能导致优化器做出错误的判断，反而选择了一个低效的索引或执行计划。
- 维护成本： 索引的创建、重建和维护都需要消耗系统资源。
- 建议： 单表的索引数量不宜过多，例如建议不超过 5 个，单个索引中的字段数也不应超过 5 个。同时，应避免冗余索引，例如如果已经有 (a, b) 的联合索引，就不需要再单独建立 (a) 的索引，因为 (a, b) 索引已经包含了 (a) 的功能。

5. 索引使用的核心原则

合理地利用索引是数据库性能优化的关键。以下是关于索引使用的核心原则。

5.1 适合建立索引的场景

频繁作为查询条件的字段： 任何经常出现在 WHERE 子句中的字段都应考虑建立索引，因为索引的主要目的就是加速数据检索。
关联查询中的外键： 在 JOIN 操作中，作为连接条件的字段（通常是外键）建立索引可以显著提高连接效率。
需要排序或分组的字段： ORDER BY 或 GROUP BY 子句中频繁使用的字段，如果建立索引，可以利用索引的有序性避免额外的排序操作，从而提升性能。
覆盖索引场景： 当查询语句中 SELECT 的所有字段都包含在某个索引中时，MySQL 可以直接从索引中获取所需数据，无需“回表”查询实际数据行，这被称为覆盖索引。这种情况下，可以显著减少 I/O 开销，大幅提升查询性能。

5.2 适合作为索引列的字段特征

区分度高： 字段的唯一值越多，即选择性越高，索引的效果越好。例如，用户 ID、身份证号等具有高区分度的字段是理想的索引列。
长度适中： 索引字段的长度不宜过长，特别是对于字符串类型。过长的索引字段会占用更多的磁盘空间，增加索引文件的大小，导致 B+ 树的层级变高，从而增加磁盘 I/O 次数，影响索引性能。对于长字符串，可以考虑使用前缀索引。
数据类型合适： 选择合适的数据类型有助于索引的效率。例如，使用整数类型通常比字符串类型更高效。
字段值“干净”： 索引列不应在查询条件中参与函数运算或表达式计算，否则会导致索引失效。

5.3 不适合建立索引的情况

频繁更新的字段： 对频繁更新的字段建立索引会增加索引维护的成本，每次更新都需要重新调整索引结构，从而降低写操作的性能。
表数据量极少的情况： 对于数据量非常小的表，全表扫描的开销可能远小于维护索引的开销，此时建立索引反而可能带来负面影响。
区分度极低的字段： 例如“性别”、“状态”等只有少数几个固定值的字段，其选择性非常低。即使建立了索引，MySQL 也可能认为全表扫描更高效，从而导致索引形同虚设。
WHERE 子句中不使用的字段： 如果一个字段从不出现在 WHERE、ORDER BY、GROUP BY 或 JOIN 子句中，那么为其创建索引是浪费资源。
冗余索引： 避免创建重复或冗余的索引，例如同时存在(a,b)和(a)两个索引，后者是冗余的。

6. 索引失效的典型场景及原因

尽管索引旨在提升查询性能，但在某些特定情况下，MySQL 的查询优化器可能无法利用索引，导致索引失效，进而退化为全表扫描。理解这些场景对于编写高效 SQL 至关重要。

查询条件中对索引列使用函数或表达式： 当在 WHERE 子句中对索引列进行函数操作（如 SUBSTR()、DATE_FORMAT()、CONCAT() 等）或表达式计算时，索引会失效。例如：
```
WHERE SUBSTR(name, 1, 3) = 'abc' -- 对name列使用了函数
WHERE age + 10 = 30             -- 对age列使用了表达式
```
原因是索引中存储的是原始列值，经过函数或表达式计算后，其值发生了变化，无法直接通过索引树进行快速匹配。MySQL 需要对所有行应用函数或表达式后再进行比较，这等同于全表扫描。
模糊查询中以通配符开头： 当 LIKE 查询的模式字符串以通配符（% 或 _）开头时，索引会失效。例如：
```
WHERE name LIKE '%abc'
WHERE name LIKE '%abc%'
```
这是因为 B+ 树索引是按照从左到右的顺序进行排序的。当前导通配符存在时，无法确定起始匹配点，索引的有序性被破坏，导致无法利用索引进行快速查找。然而，LIKE 'abc%'（前缀匹配）是可以使用索引的。
联合索引不满足最左匹配原则： 对于联合索引，如果查询条件没有从索引的最左侧列开始匹配，或者跳过了中间的列，索引的后续部分将无法生效。例如，对于联合索引 (col1, col2, col3)：
```
WHERE col2 = 'value'          -- 跳过col1
WHERE col2 = 'value' AND col3 = 'value' -- 跳过col1
WHERE col1 = 'value' AND col3 = 'value' -- 跳过col2, 只用到了col1部分的索引
```
这些查询都无法完全利用联合索引，因为它们不符合最左匹配原则。
其他情况：
- 使用 NOT IN、!=（或<>）操作符： 这些操作符表示不确定范围，可能导致索引失效，因为数据库难以确定不包含的值的范围，往往会退化为全表扫描。
- 使用 OR 操作符： 如果 OR 连接的条件中，其中一个字段没有索引，或者两个字段都有索引但优化器认为全表扫描更优，则索引可能失效。例如，WHERE col1 = 'a' OR col2 = 'b'，如果 col2 没有索引，col1 的索引也可能失效。
- 隐式类型转换： 当查询条件中的数据类型与索引列的数据类型不一致时，MySQL 可能会进行隐式类型转换，这会导致索引失效。例如，name 是字符串类型的索引列（如 varchar），查询条件为 WHERE name = 123（用数字 123 与字符串列比较）。MySQL 会将字符串列（索引列）转换为数字，而非将数字转为字符串，导致查询中索引列被施加了转换函数（CAST）。此时，MySQL 无法利用索引的有序性快速定位，必须逐行转换 name 的值后再比较，最终导致索引失效。MySQL 在处理不同类型的比较时（如数字 vs 字符串），会遵循一套类型转换优先级：数字类型（int、float 等）被视为 “更通用” 的类型；字符串类型（char、varchar 等）被视为 “更特殊” 的类型。当两者比较时，MySQL 会将字符串转换为数字（而非数字转换为字符串），以实现类型兼容。这一规则源于 SQL 标准对 “混合类型比较” 的处理逻辑，目的是尽可能避免因格式差异导致的比较歧义（例如数字 123 和字符串 ‘123’ 应被视为相等）。
- 索引列使用 IS NULL 或 IS NOT NULL： 某些情况下，对允许 NULL 值的索引列使用 IS NULL 或 IS NOT NULL 可能导致索引失效，这取决于优化器的判断和数据分布。
- 优化器判断全表扫描更优： 即使存在可用索引，如果表数据量非常小，或者查询返回的结果集占总数据量的比例非常大（例如超过 20%~30%），查询优化器可能会认为全表扫描的成本低于使用索引的成本（包括回表等开销），从而选择全表扫描。

7. 索引对数据库性能的影响

索引对数据库性能的影响是双向的，既有显著的正面作用，也存在不容忽视的负面影响。

7.1 正面影响

加速查询： 这是索引最主要的作用。索引通过提供快速查找路径，显著减少了数据库需要扫描的数据范围，从而大大缩短了查询的响应时间。例如，对于一个百万级的数据表，没有索引可能需要百万次的磁盘 I/O 进行全表扫描，而有了 B+ 树索引，可能只需 3-4 次 I/O 就能定位到所需数据。
提升排序效率： 当查询语句中包含 ORDER BY 子句时，如果排序的字段上有索引，MySQL 可以利用索引的有序性直接获取排序后的结果，避免了在内存中进行文件排序（Using filesort）的额外开销，从而大幅提升排序效率。
加速分组和聚合： 类似地，GROUP BY 操作也可以利用索引的有序性，加速分组过程，减少临时表的创建和排序操作。
减少数据扫描范围： 索引能够将随机的数据查找转化为顺序查找，通过不断缩小想要获得数据的范围来筛选出最终结果，从而减少了磁盘 I/O 次数。
支持覆盖索引： 当查询所需的所有列都包含在索引中时，MySQL 可以直接从索引中获取数据，无需访问实际的数据行，这被称为覆盖索引。它避免了“回表”操作，显著减少了 I/O 开销，进一步提升查询性能。
优化连接操作： 在多表 JOIN 操作中，如果连接字段上存在索引，可以大大加速连接过程，减少匹配行的查找时间。

7.2 负面影响

对插入、更新、删除操作的效率影响： 每次数据发生变动（INSERT、UPDATE、DELETE）时，数据库不仅需要修改数据本身，还需要同步维护所有相关的索引结构。这意味着额外的 I/O 和 CPU 开销，尤其是在表数据量大、索引数量多且更新频繁的场景下，这种维护成本会显著降低写入操作的效率。
占用存储空间： 索引本身是一种数据结构，需要占用额外的磁盘空间来存储。索引越多，占用的空间越大，这对于存储资源有限的系统来说是一个考量。
可能影响其他查询语句： 不恰当的索引（例如对区分度非常低的列添加索引）不仅会影响写入性能，也可能干扰查询优化器的判断，导致优化器选择错误的执行计划，反而影响其他查询语句的性能。
索引设计与维护复杂性： 索引并非简单地为每个查询字段添加。需要深入理解“最左前缀匹配”、“覆盖索引”等概念，并结合所有相关查询和业务场景进行综合评估。盲目或过度索引可能导致比原来更严重的性能问题。

8. 索引性能分析工具

在 MySQL 中，EXPLAIN 命令是分析 SQL 查询语句执行计划的强大工具，它能够帮助开发人员和 DBA 判断索引是否被有效使用，以及查询的性能瓶颈所在。

8.1 EXPLAIN 命令的作用

EXPLAIN 命令用于模拟 MySQL 优化器执行 SQL 查询的过程，并显示关于查询执行计划的信息。通过分析 EXPLAIN 的输出，可以了解 MySQL 如何处理查询，包括：

哪些表被访问。
表的访问顺序。
使用了哪些索引。
是否进行了全表扫描。
是否需要进行文件排序或创建临时表等耗时操作。

8.2 关键输出参数的含义

EXPLAIN 命令的输出包含多个列，其中几个关键参数对于判断索引使用情况尤为重要：

id： SELECT 查询的序列号，表示查询中每个 SELECT 子句的执行顺序。
select_type： SELECT 查询的类型，如 SIMPLE (简单查询)、PRIMARY (最外层 SELECT)、SUBQUERY (子查询)、UNION (UNION 中的第二个或后续 SELECT) 等。
table： 输出行所引用的表名。
type： 这是最重要的指标之一，表示 MySQL 访问表的方式（访问类型），从好到坏依次是：
- system：表只有一行数据（等于系统表），这是最好的。
- const：通过索引一次就找到了，通常用于主键或唯一索引的等值查询。
- eq_ref：对于每个来自前面表的行组合，从该表中读取一行。常用于连接查询中，连接列是主键或唯一索引。
- ref：对于每个来自前面表的行组合，所有匹配索引值的行都被读取。常用于非唯一索引的等值查询。
- range：只检索给定范围的行，使用一个索引来选择行。常用于 BETWEEN、>、<、IN 等范围查询。
- index：全索引扫描，MySQL 遍历整个索引树来查找数据。虽然比 ALL 好，但仍然是全表扫描（索引层面）。
- ALL：全表扫描，MySQL 遍历整个表来查找匹配的行，这是最差的访问类型，通常意味着没有有效利用索引。
- 目标： 一般来说，查询至少应达到 range 级别，最好能达到 ref 或更优级别。
possible_keys： 指出 MySQL 可能使用哪些索引在该表中找到行。如果为空，则表示没有相关的索引可供选择。
key： 显示 MySQL 实际决定使用的索引。如果为 NULL，则表示没有使用索引。
key_len： 显示 MySQL 决定使用的索引的长度（字节数）。这对于联合索引尤其有用，可以判断联合索引的哪些部分被实际使用。
ref： 显示哪个字段或常数与 key 一起被使用。
rows： MySQL 估计为了找到所需的行而需要读取的行数。这个值越小越好，但在 InnoDB 存储引擎中，这个值可能不总是非常准确。
Extra： 包含不适合在其他列中显示但非常重要的额外信息，例如：
- Using index：表示查询所需的所有数据都可以从索引中获取，无需回表，即触发了覆盖索引。这是非常高效的。
- Using where：表示 MySQL 将根据 WHERE 子句的条件对结果进行过滤。
- Using filesort：表示 MySQL 需要对结果进行外部排序（在内存或磁盘上），这通常是一个性能瓶颈，应尽量避免。
- Using temporary：表示 MySQL 需要创建临时表来处理查询，这通常也是一个性能瓶颈，应尽量避免。
- Impossible WHERE：表示 WHERE 子句的条件总是为假，没有行能满足条件。