知识库

Appearance

MySQL 索引原理 ​

  索引是数据库性能优化的核心手段。理解索引的底层原理，才能写出高效的 SQL 并合理设计表结构。MySQL InnoDB 引擎使用 B+Tree 作为主要索引结构。

1. 什么是索引 ​

1.1 索引的定义 ​

  索引是帮助 MySQL 高效获取数据的数据结构。类比书的目录，索引让数据库能够快速定位到目标数据，而不需要全表扫描。

1.2 为什么需要索引 ​

  • 减少磁盘 I/O：不用全表扫描，通过索引快速定位数据页。
  • 保证数据唯一性：唯一索引确保列值不重复。
  • 加速排序和分组：索引天然有序，ORDER BY 和 GROUP BY 可以利用索引。
  • 加速表连接：JOIN 操作使用索引加速关联查询。

1.3 索引的代价 ​

  • 空间代价：索引需要额外存储空间，通常是数据大小的 1.5 倍以上。
  • 时间代价：INSERT、UPDATE、DELETE 需要维护索引，降低写入性能。
  • 优化器成本：索引过多会增加优化器选择执行计划的时间。

2. B+Tree 结构与原理 ​

2.1 为什么是 B+Tree ​

  数据库索引需要一种能够高效支持等值查找、范围查找、排序的数据结构。B+Tree 是 B-Tree 的变体，专为磁盘 I/O 优化。

常见数据结构对比：

数据结构等值查找范围查找适用场景
哈希表O(1)不支持等值查询（Memory 引擎）
二叉搜索树O(logN)O(logN)退化后变 O(N)
红黑树/AVLO(logN)O(logN)树太高，磁盘 I/O 多
B-TreeO(logN)O(logN)节点存数据，范围查需中序遍历
B+TreeO(logN)O(logN)叶子节点链表，磁盘 I/O 最优

2.2 B+Tree 结构详解 ​

                    ┌─────────────────────┐
                    │    [30 | 60] (根节点)  │
                    │    /         \       │
                    └──────────────────────┘
                   /           |            \
         ┌──────────┐  ┌──────────┐  ┌──────────┐
         │ [10|20]  │  │ [40|50]  │  │ [70|80]  │  (内部节点)
         │  / | \   │  │  /  \    │  │  /  \    │
         └──────────┘  └──────────┘  └──────────┘
         /    |    \     /      \     /      \
    ┌────┐┌────┐┌────┐┌────┐┌────┐┌────┐┌────┐
    │ 10 ││ 20 ││ 30 ││ 40 ││ 50 ││ 70 ││ 80 │ (叶子节点)
    │  → ││  → ││  → ││  → ││  → ││  → ││  → │  ← 双向链表
    └────┘└────┘└────┘└────┘└────┘└────┘└────┘

内部节点（非叶子节点）：

  • 存储键值（Key）和子节点指针（Page Pointer）。
  • 不存储实际数据，只存储索引键。
  • 每个节点可以存储大量键值，使树的高度很低（通常 3-4 层）。

叶子节点：

  • 存储键值和实际数据（聚集索引）或主键值（二级索引）。
  • 叶子节点之间通过双向链表连接，支持高效的范围查询。
  • 所有叶子节点在同一层，保证了查询的稳定性。

B+Tree 的优势：

  1. 树高度低：每个节点存储大量键值（16KB 页可存储约 1170 个键），3 层可存储约 2000 万行数据。
  2. 磁盘 I/O 少：每次查找只需 h 次磁盘 I/O（h 为树高度）。
  3. 范围查询高效：叶子节点形成有序链表，找到起始位置后顺序扫描。
  4. 查询稳定性：所有查询都走到叶子节点，性能稳定。

2.3 B+Tree 与 B-Tree 的区别 ​

特性B-TreeB+Tree
数据存储内部节点和叶子节点都存数据只有叶子节点存数据
内部节点存储键值 + 数据 + 指针只存储键值 + 指针
叶子节点链表无双向链表
范围查询需要中序遍历顺序遍历叶子链表
内部节点容量较小（因为存数据）较大（只存键值）
树高度较高较低
查询效率可能命中内部节点必须到叶子节点

3. 聚集索引与二级索引 ​

3.1 聚集索引（Clustered Index） ​

  聚集索引决定了数据行的物理存储顺序。InnoDB 中，主键索引就是聚集索引。

特点：

  • 叶子节点存储的是完整的行数据（所有列）。
  • 一张表只能有一个聚集索引（因为数据只能按一种顺序物理存储）。
  • 主键的选取直接影响插入性能（建议使用自增 ID，避免随机插入导致的页分裂）。

主键选取规则：

  1. 如果定义了 PRIMARY KEY，使用主键。
  2. 如果没有主键但定义了非空唯一索引，使用第一个非空唯一索引。
  3. 如果都没有，InnoDB 自动生成一个 6 字节的隐藏主键 ROW_ID。

3.2 二级索引（Secondary Index） ​

  二级索引（辅助索引）的叶子节点存储的是索引键 + 主键值，而不是完整行数据。

查找过程（回表）：

  1. 在二级索引中查找索引键，获取对应的主键值。
  2. 用主键值在聚集索引中查找完整行数据。
二级索引（idx_name）              聚集索引（PRIMARY KEY）
┌──────────────┐                ┌──────────────┐
│ 索引键: name  │                │ 索引键: id    │
│ 叶子节点:     │                │ 叶子节点:     │
│ (name, id)   │                │ 完整行数据     │
└──────────────┘                └──────────────┘
       │                              ▲
       │     回表（通过 id 查找）        │
       └──────────────────────────────┘

为什么二级索引存储主键而不是数据地址：

  1. 减少维护成本：数据页分裂或移动时，不需要更新所有二级索引。
  2. 一致性：通过主键保证数据一致性。
  3. 节省空间：主键通常比数据地址更稳定。

3.3 聚集索引 vs 二级索引对比 ​

特性聚集索引二级索引
数量一个多个
叶子节点存储完整行数据索引键 + 主键值
查询速度一次查找可能需要回表
主键大小影响影响所有二级索引影响索引大小
插入速度顺序插入最快需要维护索引

4. 索引类型 ​

4.1 主键索引（PRIMARY KEY） ​

  • 唯一标识每一行，不允许 NULL。
  • InnoDB 中主键索引是聚集索引。
  • 建议使用自增 BIGINT 作为主键。
sql
CREATE TABLE t (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

4.2 唯一索引（UNIQUE） ​

  • 索引列的值必须唯一，但允许 NULL（多个 NULL 不冲突）。
  • 可用于约束数据唯一性。
sql
CREATE UNIQUE INDEX idx_email ON users(email);

4.3 普通索引（Normal Index） ​

  • 最基本的索引，唯一作用是加速查询。
  • 没有唯一性约束。
sql
CREATE INDEX idx_name ON users(name);

4.4 复合索引（Composite Index） ​

  • 多个列组合成一个索引。
  • 遵循最左前缀原则。
  • 可用于索引覆盖。
sql
CREATE INDEX idx_name_age ON users(name, age);

-- 以下查询可以使用 idx_name_age：
-- WHERE name = 'Alice'                    (使用 name 列)
-- WHERE name = 'Alice' AND age = 25       (使用全部列)
-- WHERE name = 'Alice' AND age > 20       (使用 name 和 age)

-- 以下查询不能使用 idx_name_age：
-- WHERE age = 25                          (不满足最左前缀)
-- WHERE age = 25 AND name = 'Alice'       (可以用，优化器会调整顺序)

4.5 全文索引（FULLTEXT） ​

  • 用于全文搜索，支持 MATCH ... AGAINST 语法。
  • InnoDB 在 MySQL 5.6+ 支持全文索引。
  • 支持三种搜索模式：自然语言模式、布尔模式、查询扩展模式。
sql
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

-- 搜索
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL 索引');
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

4.6 空间索引（SPATIAL） ​

  • 用于地理空间数据类型（GEOMETRY、POINT、LINESTRING、POLYGON）。
  • 使用 R-Tree 索引结构。
  • MyISAM 和 InnoDB 都支持（InnoDB 5.7+）。
sql
CREATE TABLE geo (
    id INT PRIMARY KEY,
    location POINT NOT NULL,
    SPATIAL INDEX idx_location (location)
);

4.7 前缀索引 ​

  • 对字符串列的前 N 个字符建索引，减少索引大小。
  • 需要权衡选择性和索引大小。
sql
-- 查看前缀选择性
SELECT COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) FROM users;

-- 创建前缀索引
CREATE INDEX idx_name_prefix ON users(name(10));

5. 覆盖索引与索引优化技术 ​

5.1 覆盖索引（Covering Index） ​

  当查询的所有列都在索引中时，不需要回表查询，直接从索引中获取数据，称为覆盖索引。

示例：

sql
-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 覆盖索引：查询的列都在索引中
EXPLAIN SELECT name, age FROM users WHERE name = 'Alice';
-- Extra: Using index（覆盖索引，不需要回表）

-- 非覆盖索引：需要回表
EXPLAIN SELECT name, age, email FROM users WHERE name = 'Alice';
-- Extra: NULL（需要回表获取 email）

优势：

  • 减少回表次数，减少磁盘 I/O。
  • 对于大数据量查询，性能提升显著。

5.2 索引条件下推（ICP - Index Condition Pushdown） ​

  ICP 是 MySQL 5.6 引入的优化特性，将 WHERE 条件中能使用索引的部分下推到存储引擎层过滤，减少回表次数。

工作原理：

  • 没有 ICP：存储引擎通过索引找到所有匹配的行，返回给 Server 层，Server 层用 WHERE 条件过滤。
  • 有 ICP：存储引擎在索引层面就过滤掉不满足条件的行，只返回符合所有条件的行。

示例：

sql
-- 假设有索引 idx_name_age(name, age)
-- 查询：SELECT * FROM users WHERE name LIKE 'A%' AND age = 25;

-- 没有 ICP：
-- 1. 通过索引找到所有 name LIKE 'A%' 的行
-- 2. 全部回表获取完整数据
-- 3. Server 层过滤 age = 25

-- 有 ICP（Extra: Using index condition）：
-- 1. 通过索引找到所有 name LIKE 'A%' 的行
-- 2. 在索引层面过滤 age = 25（不需要回表检查 age）
-- 3. 只回表获取满足所有条件的行

ICP 触发条件：

  • 使用二级索引（非聚集索引）。
  • WHERE 条件中有一部分可以使用索引，另一部分不能。
  • 使用 range、ref、eq_ref、ref_or_null 访问方式。

5.3 MRR 优化（Multi-Range Read） ​

  MRR 是 MySQL 5.6 引入的优化，将随机 I/O 转换为顺序 I/O，减少磁盘寻道开销。

工作原理：

  1. 在二级索引中收集所有匹配的主键值。
  2. 对主键值排序。
  3. 按主键顺序回表，将随机 I/O 变为顺序 I/O。
sql
-- 开启 MRR
SET optimizer_switch = 'mrr=on,mrr_cost_based=off';

-- Extra: Using MRR

6. 最左前缀原则 ​

  最左前缀原则是复合索引最重要的规则，决定了索引是否能被使用以及如何使用。

6.1 原理 ​

  复合索引按列的先后顺序构建 B+Tree，索引键先按第一列排序，第一列相同再按第二列排序，以此类推。

sql
CREATE INDEX idx_a_b_c ON t(a, b, c);

  索引结构相当于按 ORDER BY a, b, c 排序。

6.2 能使用索引的情况 ​

查询条件是否使用索引说明
WHERE a = 1使用 a 列最左列匹配
WHERE a = 1 AND b = 2使用 a, b 列连续匹配
WHERE a = 1 AND b = 2 AND c = 3使用全部列全值匹配
WHERE a = 1 AND c = 3使用 a 列中间断开了
WHERE b = 2 AND a = 1使用 a, b 列优化器调整顺序
WHERE a = 1 AND b > 2 AND c = 3使用 a, b 列范围查询导致 c 失效
WHERE a = 1 AND b LIKE 'abc%' AND c = 3使用 a, b 列LIKE 前缀匹配算范围
WHERE a = 1 ORDER BY b使用 a, b 列排序列有序
WHERE a = 1 ORDER BY c使用 a 列排序用到 filesort

6.3 不能使用索引的情况 ​

查询条件是否使用索引说明
WHERE b = 2全表扫描不满足最左前缀
WHERE c = 3全表扫描不满足最左前缀
WHERE a LIKE '%abc'全表扫描范围查询导致后面的列失效
WHERE a = 1 OR b = 2可能不用OR 条件

6.4 实战示例 ​

sql
-- 创建复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 完全匹配：使用全部 3 列
EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age = 25 AND city = 'Beijing';
-- key_len: 根据 3 列计算

-- 最左两列：使用 name 和 age
EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age = 25;
-- key_len: 根据 2 列计算

-- 最左一列：只使用 name
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
-- key_len: 根据 1 列计算

-- 中间断开：只使用 name，age 被跳过
EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND city = 'Beijing';
-- key_len: name 列的长度

-- 范围查询：name 和 age 能用，city 不能用
EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age > 25 AND city = 'Beijing';
-- key_len: name + age 的长度

-- 不满足最左前缀：全表扫描
EXPLAIN SELECT * FROM users WHERE age = 25;
-- type: ALL

7. 索引失效场景 ​

7.1 LIKE 以通配符开头 ​

sql
-- 索引失效
SELECT * FROM users WHERE name LIKE '%Alice';

-- 索引有效
SELECT * FROM users WHERE name LIKE 'Alice%';

原因：B+Tree 按前缀有序排列，无法利用后缀匹配。

7.2 对索引列使用函数 ​

sql
-- 索引失效
SELECT * FROM users WHERE UPPER(name) = 'ALICE';
SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';

-- 索引有效（函数在值上）
SELECT * FROM users WHERE name = UPPER('Alice');
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

7.3 隐式类型转换 ​

sql
-- 假设 phone 是 VARCHAR 类型

-- 索引失效（字符串与数字比较，MySQL 会将字符串转为数字）
SELECT * FROM users WHERE phone = 13800138000;

-- 索引有效（类型匹配）
SELECT * FROM users WHERE phone = '13800138000';

7.4 使用 OR 条件 ​

sql
-- 如果 OR 的两边有一边没有索引，则全表扫描
SELECT * FROM users WHERE name = 'Alice' OR age = 25;
-- 如果 name 有索引但 age 没有，索引失效

-- 解决方案：使用 UNION ALL
SELECT * FROM users WHERE name = 'Alice' 
UNION ALL 
SELECT * FROM users WHERE age = 25 AND name != 'Alice';

7.5 使用 != 或 <> ​

sql
-- 索引可能失效（优化器认为范围太大，全表扫描可能更快）
SELECT * FROM users WHERE name != 'Alice';
-- 通常使用覆盖索引可以避免

7.6 IS NULL 和 IS NOT NULL ​

sql
-- IS NULL 通常可以使用索引
SELECT * FROM users WHERE name IS NULL;

-- IS NOT NULL 可能不使用索引（取决于数据分布）
SELECT * FROM users WHERE name IS NOT NULL;

7.7 NOT IN 和 NOT EXISTS ​

sql
-- 通常不使用索引
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

7.8 复合索引跳列 ​

sql
-- 索引 idx_a_b_c(a, b, c)
-- 跳过了 b，只使用 a 列
SELECT * FROM t WHERE a = 1 AND c = 3;

7.9 索引列参与运算 ​

sql
-- 索引失效
SELECT * FROM users WHERE age + 1 = 26;

-- 索引有效
SELECT * FROM users WHERE age = 25;

8. 哈希索引与 B+Tree 对比 ​

8.1 哈希索引 ​

特性哈希索引
查找速度O(1)，等值查询极快
范围查询不支持
排序不支持
部分匹配不支持（必须全值匹配）
冲突处理链地址法
适用引擎Memory、NDB

8.2 自适应哈希索引 ​

  InnoDB 的自适应哈希索引是自动在内存中构建的哈希索引，用于加速热点页的等值查询。

触发条件：

  • 某个索引页被频繁访问。
  • 访问模式是等值查询（= 或 IN）。
  • 某种访问模式持续出现。

注意事项：

  • 高并发下可能造成锁竞争，可考虑关闭。
  • 只对等值查询有效。
sql
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
SHOW STATUS LIKE 'Innodb_adaptive_hash%';

9. 索引设计最佳实践 ​

9.1 设计原则 ​

  1. 选择区分度高的列：区分度 = COUNT(DISTINCT col) / COUNT(*)，越接近 1 越好。
  2. 复合索引列顺序：区分度高的列放前面，或等值查询的列放前面、范围查询的列放后面。
  3. 避免过多索引：每张表索引建议不超过 5 个，单表索引列数不超过 5 个。
  4. 使用覆盖索引：将 SELECT 中常用的列加入索引，避免回表。
  5. 主键尽量短：二级索引存储主键值，主键越长，二级索引越大。
  6. 使用自增主键：顺序插入，避免页分裂，写入性能最优。
  7. 避免冗余索引：(a, b) 和 (a) 是冗余的，(a, b) 可以覆盖 (a) 的功能。

9.2 冗余索引检测 ​

sql
-- 使用 sys 库检查冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 使用 pt-duplicate-key-checker（Percona Toolkit）
-- pt-duplicate-key-checker -u root -p

9.3 索引监控 ​

sql
-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引统计
SELECT * FROM sys.schema_index_statistics;

10. 总结 ​

  • B+Tree 是 MySQL InnoDB 的核心索引结构，优势在于树高度低、范围查询高效、查询稳定。
  • 聚集索引的叶子节点存储完整行数据，二级索引的叶子节点存储主键值，需要回表查询。
  • 复合索引遵循最左前缀原则，设计时需注意列的顺序。
  • 索引失效的常见场景包括：LIKE 前缀通配符、函数/运算、隐式类型转换、OR 条件。
  • 覆盖索引是重要的优化手段，避免回表查询。
  • 索引设计需权衡查询性能和写入性能，避免过多索引。