一、什么是索引？

索引（Index） 是数据库管理系统中一种特殊的数据结构，存储在磁盘上。它包含对数据表中一列或多列的值进行排序，并存储了指向表中实际数据行物理位置或主键值的引用指针。可以把它类比为书籍的目录，能够帮助数据库系统快速定位到符合查询条件的数据，而无需扫描整个数据表。

二、索引的作用

1. 快速定位与检索数据： 这是索引最核心的作用。通过索引，数据库可以避免全表扫描，直接跳转到目标数据所在的位置，极大地缩短数据查询的时间。
2. 提高数据库性能： 通过加速查询操作，索引能够有效提升数据库的整体性能，尤其是对于读密集型的应用。
3. （间接作用）保证数据的唯一性： 唯一索引可以确保索引列中的所有值都是唯一的。
4. （间接作用）加速表连接： 对用于连接的列（如外键）创建索引，可以显著提高表连接的效率。
5. （间接作用）加速排序和分组： 如果 ORDER BY 或 GROUP BY 子句中的列有索引，MySQL 可以利用索引的有序性来避免额外的排序操作。

三、索引的缺点

虽然索引能带来巨大的性能提升，但它并非没有代价：

1. 空间消耗： 索引本身也需要占用磁盘空间。数据量越大，索引占用的空间也越大。这是一种典型的“以空间换时间”的策略。对于大多数后端应用而言，磁盘成本相对较低，通常可以接受。
2. 影响写操作效率（增、删、改）：
   • 当对表中的数据进行 INSERT、DELETE、UPDATE 操作时，数据库不仅要修改数据本身，还需要同步维护索引结构（例如，B+树的节点分裂、合并、键值调整等）。
   • 这通常会导致写操作的效率降低。虽然在极少数特定更新场景下影响可能不明显，但总体趋势是写操作的开销会增加。

权衡利弊： 尽管存在上述缺点，但在实际开发中，索引的使用仍然是必不可少的。主要原因有：

1. 磁盘空间通常不是主要矛盾，其成本远低于因查询缓慢导致的用户体验下降或系统资源浪费。
2. 在大多数应用场景中，读操作（查询）的频率远高于写操作。因此，通过索引提升查询性能带来的整体效益往往远大于其对写操作的轻微影响。

四、何时使用索引？（使用场景）

在决定是否对数据表的某一列或多列创建索引时，需要综合考虑以下几点：

适合创建索引的场景：

• 数据量较大的表： 对于小表，全表扫描可能比走索引更快。索引的优势在大表上才能充分体现。
• 经常作为查询条件的列： 即经常出现在 WHERE 子句中的列。
• 经常用于表连接的列： 通常是外键列。
• 经常需要排序的列： 即经常出现在 ORDER BY 子句中的列。如果排序方向与索引一致，效果更佳。
• 经常需要分组统计的列： 即经常出现在 GROUP BY 子句中的列。
• 高基数性（高区分度）的列： 列中不同值的数量越多，索引的选择性就越好，查询效率提升越明显。

不适合或需谨慎创建索引的场景：

• 数据量非常小的表： 全表扫描效率可能更高，索引反而增加开销。
• 写操作远多于读操作的表： 维护索引的代价可能会超过查询带来的收益。
• 低基数性的列： 列中只有很少的唯一值（例如：性别列，只有男、女、未知）。索引选择性差，优化效果不明显，还浪费空间。
• 很少被查询或引用的列： 创建索引没有意义。
• 经常被修改的列： 如果某列的值频繁更新，那么维护该列索引的代价会比较大。
• 过长的文本字段： 对非常长的文本字段直接创建完整索引会占用大量空间且效率不高，可以考虑使用前缀索引或全文索引。

五、使用

1. 查看索引

SHOW INDEX FROM 表名;

或者查看建表语句，其中也包含了索引信息：

SHOW CREATE TABLE 表名;

2. 创建索引

MySQL 支持多种类型的索引，创建语法略有不同。

普通索引（INDEX）： 最基本的索引，没有任何限制。

CREATE INDEX 索引名 ON 表名(字段名1, 字段名2, ...);
-- 或者
ALTER TABLE 表名 ADD INDEX 索引名 (字段名1, 字段名2, ...);

字段名1, 字段名2, … 表示可以创建复合索引（联合索引）。

唯一索引（UNIQUE INDEX）： 索引列的值必须唯一，但允许有空值（NULL）。如果是复合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
-- 或者
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (字段名);
-- 或者在定义表时指定
-- CREATE TABLE 表名 (
--     id INT PRIMARY KEY,
--     email VARCHAR(100) UNIQUE,
--     ...
-- );

主键索引（PRIMARY KEY）： 一种特殊的唯一索引，不允许有空值。一张表只能有一个主键索引。

-- 通常在创建表时定义
CREATE TABLE 表名 (id INT PRIMARY KEY,...
);
-- 或者通过 ALTER TABLE 添加
ALTER TABLE 表名 ADD PRIMARY KEY (字段名);

创建主键约束时，会自动创建对应列的主键索引。

外键约束（FOREIGN KEY）：
当创建外键约束时，MySQL 也会自动在引用列上创建索引（如果尚不存在），以提高连接和约束检查的效率。

全文索引（FULLTEXT INDEX）： 用于对文本内容进行全文搜索，通常用于 CHAR, VARCHAR, TEXT 类型的列。

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名);
-- 或者
ALTER TABLE 表名 ADD FULLTEXT (字段名);

删除索引

DROP INDEX 索引名 ON 表名;

或者：

ALTER TABLE 表名 DROP INDEX 索引名;

对于主键索引，删除方式特殊：

ALTER TABLE 表名 DROP PRIMARY KEY;

六、索引背后的数据结构

为什么数据库索引普遍选择 B+ 树而不是其他数据结构（如哈希表、二叉搜索树）呢？

1. 为什么不选择哈希表？
哈希表通过哈希函数将键映射到存储位置，理论上等值查询（= 或 IN）的时间复杂度可以达到 O(1)，非常快。但是：

• 不支持范围查询或顺序访问： 哈希表存储的数据是无序的，无法高效处理 >、<、BETWEEN 等范围查询，也无法支持 ORDER BY 操作。
• 不支持模糊查询： 无法直接支持 LIKE ‘keyword%’ 这样的模糊查询。
• 哈希冲突问题： 当发生哈希冲突时，需要额外的机制（如链地址法）来解决，这会降低查询效率。
• 内存数据库更适用： MySQL 的 Memory 存储引擎支持哈希索引，因为它主要在内存中操作，但对于磁盘存储引擎（如 InnoDB, MyISAM），磁盘 I/O 是主要瓶颈，哈希表的优势不明显。

2. 为什么不选择二叉搜索树（BST）或平衡二叉搜索树（AVL树、红黑树）？
二叉搜索树（及其平衡变种）能够保持数据有序，支持范围查询。但是：

• 树高问题导致磁盘 I/O 过多： 当数据量非常大时，即使是平衡二叉树，其高度也会相对较高。数据库索引
• 通常存储在磁盘上，每次访问树的一个节点都可能对应一次磁盘 I/O 操作。树越高，I/O 次数就越多，查询效率越低。
• 节点存储信息量少： 每个节点只存储一个键值和两个子节点指针，对于磁盘存储来说，这没有充分利用磁盘预读（通常一次 I/O 会读取一页或一块数据）的特性。

3. B+ 树：为数据库索引量身定做
MySQL（尤其是 InnoDB 存储引擎）普遍采用 B+ 树 作为索引的数据结构。

B 树（B-Tree）简介： B 树是一种自平衡的 N 叉搜索树（N 通常远大于 2）。它的特点是每个节点可以存储多个键值和多个指向子节点的指针，从而降低树的高度。

B+ 树的改进： B+ 树是在 B 树的基础上进行优化的，更适合数据库索引。

B+树示例

图示：一个简化的 B+ 树示例。非叶子节点存储索引 key，作为导航；叶子节点存储所有 key 并形成有序双向链表，可能直接存储数据（聚簇索引）或指向数据的指针（非聚簇索引）。

B+ 树的特点：

1. N 叉搜索树： 每个节点可以包含多个关键字（通常远大于2），这使得树的阶数（fan-out）很高，从而树的高度非常低。

2. 非叶子节点冗余关键字： 非叶子节点只存储关键字（索引）和指向下一级子节点的指针，不存储实际数据。这些关键字会作为其对应子树中叶子节点所管理区间的最大（或最小）值。

3. 所有数据（或指向数据的指针）都在叶子节点： 所有的查询最终都会落到叶子节点。叶子节点包含了所有索引键值。

   • 对于聚簇索引（Clustered Index，如 InnoDB 的主键索引），叶子节点直接存储完整的数据行。
   • 对于非聚簇索引（Secondary/Non-Clustered Index，如 InnoDB 的辅助索引或 MyISAM 的索引），叶子节点存储的是索引键值和指向实际数据行的指针（InnoDB 辅助索引存的是主键值，MyISAM 存的是数据文件的物理地址）。

4. 叶子节点通过双向链表连接： 所有叶子节点按照键值顺序被串联起来，形成一个有序的双向链表。
   B+ 树的优势：

5. 极低的树高，减少磁盘 I/O： 由于 N 叉的特性，即使存储大量数据，B+ 树的高度也非常低（通常 3-4 层就能支持千万级别的数据）。这意味着从根节点到叶子节点的路径很短，大大减少了磁盘 I/O 次数。

6. 高效的范围查询和排序： 叶子节点通过双向链表连接，使得范围查询（如 BETWEEN, >, <）和排序（ORDER BY）操作非常高效，只需在叶子节点层进行顺序扫描。

7. 稳定的查询性能： 任何一次查询（无论是单点查询还是范围查询的起点定位）都需要从根节点走到某个叶子节点，查询路径长度基本一致，IO 次数稳定。

8. 非叶子节点更小，利于缓存： 非叶子节点不存储实际数据行，只存储索引键和指针，因此它们占用的空间较小。这使得更多的非叶子节点可以被加载到内存中进行缓存，进一步减少了磁盘 I/O。

七、MySQL 常见索引类型详解

1. 聚簇索引 (Clustered Index)

• 定义： 数据行的物理存储顺序与索引顺序一致的索引。一张表只能有一个聚簇索引。
• InnoDB：
  • InnoDB 表是索引组织表，数据本身就是按照聚簇索引的顺序存储的。
  • 如果你定义了 PRIMARY KEY，那么主键就是聚簇索引。
  • 如果你没有定义 PRIMARY KEY，InnoDB 会选择第一个 UNIQUE NOT NULL 的索引作为聚簇索引。
  • 如果两者都没有，InnoDB 会自动生成一个隐藏的 6 字节的 ROWID 作为聚簇索引。
• 优点： 基于聚簇索引的查询速度非常快，因为可以直接定位到数据行。
• 缺点：
  • 维护成本较高，插入新行或主键更新（不推荐）可能导致数据页分裂。
  • 辅助索引查找需要两次索引查找（先查到主键值，再通过主键值查数据行），也称为“回表”。

2. 非聚簇索引 (Secondary / Non-Clustered Index)

• 定义： 数据行的物理存储顺序与索引顺序不一致。一张表可以有多个非聚簇索引。
• InnoDB： 辅助索引（除主键索引外的其他索引）都是非聚簇索引。其叶子节点存储的是索引列的值和对应行的数据的主键值。
• MyISAM： MyISAM 表的所有索引（包括主键索引）都是非聚簇索引。其叶子节点存储的是索引列的值和对应行的数据在磁盘上的物理地址（指针）。
• 优点： 维护成本相对较低。
• 缺点： 查询时可能需要“回表”操作（除非是覆盖索引）。

3. 覆盖索引 (Covering Index)

• 定义： 如果一个索引包含了查询语句中 SELECT、WHERE、ORDER BY、GROUP BY 所需的所有列，那么数据库引擎就可以直接从该索引中获取所有数据，而无需再访问数据表本身（即无需“回表”）。这种索引就称为覆盖索引。
• 优点： 极大地提高了查询性能，避免了回表操作的额外 I/O。
• 示例： 表 users 有 (name, age) 的联合索引。查询 SELECT name, age FROM users WHERE name = 'Alice'; 就可以使用覆盖索引。

4. 联合索引 (Composite / Compound Index) 与最左前缀匹配原则

• 定义： 对表上的多个列组合起来创建的索引。
• 最左前缀匹配原则： 这是使用联合索引时非常重要的一个原则。当查询条件使用了联合索引中的最左边连续的若干个列时，索引才会被有效利用。
  • 例如，有一个联合索引 idx_abc (a, b, c)：
    • WHERE a = 1：可以用到索引。
    • WHERE a = 1 AND b = 2：可以用到索引。
    • WHERE a = 1 AND b = 2 AND c = 3：可以用到索引。
    • WHERE a = 1 AND c = 3：只能用到索引 a 部分，c 部分用不到。
    • WHERE b = 2 AND c = 3：无法用到该索引（除非有其他以b开头的索引）。
• 顺序很重要： 创建联合索引时，列的顺序非常关键。通常将选择性高（区分度大）且最常用的列放在最左边。

八、索引失效的常见场景

即使创建了索引，某些查询也可能无法有效利用索引，导致性能下降。以下是一些常见的索引失效场景：

1. 在索引列上使用函数或进行计算：
   WHERE YEAR(create_time) = 2024; (应改为 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';)
   WHERE score / 10 = 9; (应改为 WHERE score >= 90 AND score < 100;)
2. WHERE 子句中使用 OR 连接条件：
   如果 OR 前后的条件列没有都建立索引，或者优化器认为全表扫描更快，索引可能失效。某些情况下优化器可能会尝试索引合并（Index Merge）。
3. LIKE 查询以通配符 % 开头：
   WHERE name LIKE '%keyword'; (索引失效)
   WHERE name LIKE 'keyword%'; (索引有效，会走范围扫描)
4. 字符串类型字段查询时不加引号（隐式类型转换）：
   如果 phone 列是 VARCHAR 类型：WHERE phone = 12345678901; (可能发生隐式类型转换，导致索引失效，应为 WHERE phone = '12345678901';)
5. 索引列数据类型不匹配或隐式转换。
6. IS NULL 和 IS NOT NULL：
   • IS NULL 通常可以用到索引。
   • IS NOT NULL 在某些情况下可能用不到索引，取决于数据的NULL值分布和优化器判断。
7. 范围查询右边的列索引失效：
   对于联合索引 (a, b, c)，如果 WHERE a = 1 AND b > 10 AND c = 3;，那么 c 列的索引将无法使用，因为 b 列是范围查询。
8. 优化器选择：
   有时即使查询符合索引使用规则，MySQL 优化器也可能基于成本估算（如判断回表代价过高或扫描数据量小）而选择全表扫描。可以使用 EXPLAIN 查看执行计划。
9. 索引列区分度过低： 例如在性别列上建索引，优化器可能认为走索引意义不大。

九、索引设计原则

良好的索引设计是数据库性能优化的关键。

1. 选择合适的列创建索引：
   • 经常用于 WHERE 子句的列。
   • 经常用于 ORDER BY、GROUP BY 子句的列。
   • 经常用于表连接的列（外键）。
2. 选择高基数性（高区分度）的列： 列中不同值的数量越多，索引的选择性越好。
3. 使用短索引（前缀索引）： 对于 VARCHAR、TEXT 等长字符串列，如果只需要索引前面一部分字符就能保证足够的区分度，可以创建前缀索引，以节省空间和提高效率。CREATE INDEX idx_name_prefix ON users(name(10));
4. 利用最左前缀匹配原则： 合理设计联合索引的列顺序。
5. 尽量使用覆盖索引： 减少回表操作。
6. 避免冗余和重复索引： 例如，有了主键索引 (id)，再创建普通索引 (id) 就是冗余的。有了联合索引 (a, b)，再创建索引 (a) 也是冗余的（除非有特殊场景需要单独的 (a) 索引）。
7. 索引并非越多越好： 每个额外的索引都会增加写操作的开销和存储空间。需要权衡利弊。
8. 定期维护和审查索引： 随着业务发展和数据变化，一些索引可能不再适用或效率低下，需要定期检查并优化。可以使用 EXPLAIN 分析查询计划。
9. 删除不再使用的索引： 避免不必要的开销。