MySQL-05 索引原理与优化

2026-03-17 约 4861 字预计阅读 10 分钟

MySQL 索引原理与优化

1. 为什么需要索引

假设 users 表有 1000 万行数据：

-- 无索引：全表扫描，扫描 1000 万行
SELECT * FROM users WHERE email = 'alice@example.com';

-- 有索引：B+Tree 查找，约 log₂(10000000) ≈ 23 次比较
SELECT * FROM users WHERE email = 'alice@example.com';

索引的本质：用额外的存储空间换取查询速度，是一种以空间换时间的数据结构。

索引的代价：

占用磁盘空间
写入（INSERT/UPDATE/DELETE）时需要维护索引，有额外开销
优化器需要评估是否使用索引（统计信息维护开销）

2. B+Tree 索引结构

2.1 为什么是 B+Tree 而不是其他

结构	查找	范围查询	磁盘 I/O	说明
哈希表	O(1)	❌ 不支持	少	MySQL Memory 引擎用
二叉搜索树	O(log n)	✅	多（深度大）	不适合磁盘
B-Tree	O(log n)	一般	中等	内部节点也存数据
B+Tree	O(log n)	✅ 高效	少	InnoDB 默认
跳表	O(log n)	✅	中等	Redis SortedSet 用

B+Tree 特点：

只有叶子节点存数据，内部节点只存 key，可以存更多 key，树更矮（通常 3~4 层）
叶子节点双向链表，范围查询只需找到起点，然后顺序扫描链表
所有叶子节点在同一层，查询路径长度相同，性能稳定

2.2 B+Tree 的高度

InnoDB 页大小 16KB，假设：

主键 BIGINT = 8 字节
指针 = 6 字节
每个内部节点可存 key 数 ≈ 16384 / (8 + 6) ≈ 1170
每个叶子节点存行数 ≈ 16384 / 1KB(行大小) ≈ 16

3 层 B+Tree 可存行数：
  1170 × 1170 × 16 ≈ 2190 万行

结论：3 层 B+Tree 可支撑千万级数据，只需 3 次磁盘 I/O（根页通常在 Buffer Pool）。

2.3 B+Tree 可视化

                  ┌──────────────────┐
                  │  根节点（内部节点） │
                  │  [30] [60] [90]  │
                  └──────────────────┘
                  /        |          \
       ┌──────────┐  ┌──────────┐  ┌──────────┐
       │ [10][20] │  │ [40][50] │  │ [70][80] │  内部节点
       └──────────┘  └──────────┘  └──────────┘
       /     |  \
  ┌───┐ ┌───┐ ┌───┐   ...
  │10 │←│20 │←│30 │←  叶子节点（双向链表，存完整行数据）
  └───┘ └───┘ └───┘

3. 聚簇索引与二级索引

3.1 聚簇索引（Clustered Index）

InnoDB 中，数据行与主键索引存储在一起，这就是聚簇索引。

聚簇索引 = 主键索引 = 数据本身
叶子节点存放：完整的行数据

InnoDB 选择聚簇索引的顺序：

若有显式 PRIMARY KEY，用它
若有 NOT NULL 的 UNIQUE KEY，用第一个
都没有，InnoDB 内部生成隐藏的 6 字节 row_id

为什么建议用自增主键？

非自增主键（如 UUID）的问题：插入随机位置，导致页分裂和页碎片，写性能差。

自增主键插入：总是在 B+Tree 最右边追加
UUID 插入：随机位置，可能导致中间页分裂

-- 推荐：BIGINT UNSIGNED AUTO_INCREMENT
CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  -- ...
);

-- 不推荐：UUID 作主键
-- UUID 36 字节 vs BIGINT 8 字节，索引也更大

3.2 二级索引（Secondary Index）

所有非聚簇索引都是二级索引，叶子节点存放：

二级索引叶子节点 = 索引列值 + 主键值

3.3 回表（Back to Primary Key）

二级索引查询时，若需要非索引列的数据，需要用主键值回到聚簇索引再查一次，这叫回表。

-- name 列有二级索引，但 SELECT * 需要回表
SELECT * FROM users WHERE name = 'Alice';

-- 执行过程：
-- 1. 在 name 索引树找到 name='Alice' 的叶子节点 → 得到 id=100
-- 2. 用 id=100 在聚簇索引树查找 → 得到完整行数据（回表）

回表的代价：每次回表都是一次随机 I/O，数据量大时性能差。

4. 索引类型汇总

-- 主键索引（聚簇索引）
PRIMARY KEY (id)

-- 唯一索引
UNIQUE INDEX idx_email (email)

-- 普通索引
INDEX idx_name (name)

-- 联合索引（复合索引）
INDEX idx_user_status (user_id, status)

-- 前缀索引（节省空间，适合长字符串）
INDEX idx_email_prefix (email(20))

-- 全文索引（仅支持 CHAR/VARCHAR/TEXT 列）
FULLTEXT INDEX ft_content (title, body)

-- 空间索引（GIS，不常用）
SPATIAL INDEX idx_location (location)

4.1 全文索引使用示例

-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body);

-- 自然语言模式（默认）
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('MySQL索引');

-- 布尔模式（支持 +/- 操作符）
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

注意：MySQL 全文索引对中文支持较差（按空格分词），中文场景推荐用 Elasticsearch。

5. 联合索引与最左前缀

5.1 联合索引的存储结构

INDEX idx_abc (a, b, c)
-- 排序规则：先按 a 排序，a 相同按 b，b 相同按 c

叶子节点（有序）：
(a=1, b=1, c=1) → pk=10
(a=1, b=1, c=2) → pk=20
(a=1, b=2, c=1) → pk=30
(a=2, b=1, c=1) → pk=40
...

5.2 最左前缀原则

联合索引 (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 b = 2`	❌	跳过了 a
`WHERE b = 2 AND c = 3`	❌	跳过了 a
`WHERE a = 1 AND c = 3`	⚠️ 部分	只用 a，c 无法走索引
`WHERE a > 1 AND b = 2`	⚠️ 部分	a 范围查询后，b 无法走索引
`WHERE a = 1 ORDER BY b`	✅	a 等值，b 有序可利用
`WHERE a = 1 ORDER BY b, c`	✅	a 等值，(b,c) 有序
`WHERE a = 1 ORDER BY c`	❌	跳过了 b，文件排序

5.3 范围查询阻断

-- 索引 (age, name)
-- age > 18 是范围查询，之后的 name 索引失效
SELECT * FROM users WHERE age > 18 AND name = 'Alice';
-- → 只用到 age 部分索引

-- 优化：如果 name 选择性更高，考虑换联合索引顺序 (name, age)
-- 或者：name 单独建索引，由优化器选择

5.4 索引跳跃扫描（Index Skip Scan，MySQL 8.0+）

MySQL 8.0 引入索引跳跃扫描，对于联合索引的前导列选择性低（distinct 值少）的情况，可以绕过最左前缀限制。

-- 联合索引 (gender, age)，gender 只有 'M'/'F' 两个值
-- 以前：跳过 gender 查 age 不走索引
-- MySQL 8.0+：分裂为多个子查询 (gender='M' AND age=25) UNION (gender='F' AND age=25)
SELECT * FROM users WHERE age = 25;

6. 覆盖索引

如果查询所需的所有列都在索引中，就不需要回表，称为覆盖索引（Covering Index）。

-- 表：users(id, name, email, age, ...)
-- 索引：INDEX idx_name_email (name, email)

-- 需要回表（SELECT *）
SELECT * FROM users WHERE name = 'Alice';

-- 覆盖索引，无需回表（只查 name 和 email）
SELECT name, email FROM users WHERE name = 'Alice';

-- EXPLAIN 中 Extra 列显示 "Using index" 即为覆盖索引

实践技巧：

-- 高频查询：只查 id 也算覆盖索引（id 在所有二级索引的叶子节点上）
SELECT id FROM users WHERE email = 'alice@example.com';

-- 利用联合索引实现覆盖
-- 原查询：SELECT id, status FROM orders WHERE user_id = 1
-- 建索引：INDEX idx_user_status (user_id, status)
-- 查询的列 (id, status) 都在索引中，不需回表（id 是主键，在叶子节点）

7. 索引下推（ICP）

Index Condition Pushdown（ICP），MySQL 5.6+ 特性。

没有 ICP 时：

存储引擎层根据索引找到记录（只过滤索引的前导列）
返回行给 Server 层
Server 层再过滤其余条件

有 ICP 时：

Server 层把索引上的条件下推给存储引擎层
存储引擎直接用索引过滤，不满足则跳过（不回表）
减少回表次数

-- 索引：(name, age)
-- 查询：name LIKE 'Alice%' AND age = 25

-- 无 ICP：
--   引擎：用索引找所有 name LIKE 'Alice%' 的行（可能很多）→ 逐一回表
--   Server：过滤 age = 25

-- 有 ICP：
--   引擎：用索引找 name LIKE 'Alice%' 的行，同时在索引中验证 age = 25
--   只有 age = 25 的才回表

EXPLAIN 中 Extra: Using index condition 表示使用了 ICP。

8. 索引失效场景

8.1 函数操作导致失效

-- ❌ 对索引列用函数，导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE LEFT(email, 5) = 'alice';
SELECT * FROM users WHERE id + 1 = 100;

-- ✅ 改写为范围查询
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM users WHERE email LIKE 'alice%';
SELECT * FROM users WHERE id = 99;

8.2 隐式类型转换

-- phone 是 VARCHAR 类型，索引在 phone 列
-- ❌ 传入数字，发生隐式转换（VARCHAR → INT），索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 正确写法
SELECT * FROM users WHERE phone = '13800138000';

Go 中注意：使用 ? 占位符时，Go 驱动会自动处理类型，但要确保传入的类型与列类型匹配。

8.3 模糊查询左侧有通配符

-- ❌ 前缀通配符，全表扫描
SELECT * FROM users WHERE name LIKE '%Alice';
SELECT * FROM users WHERE name LIKE '%Alice%';

-- ✅ 后缀通配符，可走索引
SELECT * FROM users WHERE name LIKE 'Alice%';

-- 需要全文模糊查询？考虑 FULLTEXT 或 Elasticsearch

8.4 OR 条件

-- ❌ OR 两侧有一个没索引，整体失效
SELECT * FROM users WHERE name = 'Alice' OR address = 'Beijing';
-- address 没有索引，上面查询走全表扫描

-- ✅ 两侧都有索引时，走 index_merge
SELECT * FROM users WHERE name = 'Alice' OR email = 'alice@example.com';
-- EXPLAIN Extra: Using union(idx_name, idx_email)

-- ✅ 改写为 UNION 效率通常更好
SELECT * FROM users WHERE name = 'Alice'
UNION
SELECT * FROM users WHERE email = 'alice@example.com';

8.5 NOT IN / NOT EXISTS

-- ❌ NOT IN 通常走全表扫描
SELECT * FROM orders WHERE status NOT IN (1, 2);

-- ✅ 改写为正向查询
SELECT * FROM orders WHERE status = 0;  -- 若已知状态值有限

-- ❌ != 或 <> 通常走全表扫描
SELECT * FROM users WHERE age != 18;

8.6 NULL 值

-- IS NULL / IS NOT NULL 是否走索引取决于选择性
-- 若 NULL 值极少，IS NULL 可能走索引
-- 若 NULL 值极多，IS NOT NULL 可能走索引（优化器基于统计信息决定）

-- ✅ 设计时避免 NULL，用默认值代替
ALTER TABLE users MODIFY age TINYINT NOT NULL DEFAULT 0;

8.7 数据量少的表

-- 表只有几百行，优化器可能选择全表扫描（I/O 比索引查找更少）
-- 不是 BUG，是正确的优化选择

9. 索引设计原则

9.1 高选择性原则

选择性（Cardinality）= 不同值数 / 总行数，越接近 1 越好。

-- 查看列的选择性
SELECT
  COUNT(DISTINCT status) / COUNT(*) AS status_sel,      -- 差（0.001）
  COUNT(DISTINCT user_id) / COUNT(*) AS user_sel,       -- 好（0.8）
  COUNT(DISTINCT email) / COUNT(*) AS email_sel         -- 极好（≈1）
FROM orders;

9.2 前缀索引节省空间

-- 对长字符串列用前缀索引
-- 先确定合适的前缀长度
SELECT
  COUNT(DISTINCT LEFT(email, 5)) / COUNT(*),
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*),
  COUNT(DISTINCT LEFT(email, 20)) / COUNT(*),
  COUNT(DISTINCT email) / COUNT(*)
FROM users;

-- 选择选择性接近完整列的最短前缀
CREATE INDEX idx_email_prefix ON users (email(20));

9.3 联合索引顺序

原则：
1. 等值查询列放前面，范围查询列放后面
2. 选择性高的列放前面（通常）
3. 覆盖查询需要的列（避免回表）
4. ORDER BY 的列排在范围查询之后

-- 查询：WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- 推荐索引：(user_id, status, created_at)
-- 不推荐：(status, user_id, created_at) -- user_id 选择性更高

CREATE INDEX idx_user_status_created (user_id, status, created_at);

9.4 避免过多索引

每个索引都需要维护，写入时有额外开销
经验：单表索引数量控制在 5 个以内（宽表除外）
定期检查未使用的索引：

-- 查看未使用的索引（MySQL 8.0+）
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_STAR = 0
  AND OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

9.5 索引维护

-- 查看索引碎片
SHOW TABLE STATUS LIKE 'orders'\G
-- Data_free 字段代表碎片大小

-- 重建索引（MySQL 5.6+ Online DDL，不锁表）
ALTER TABLE orders ENGINE=InnoDB;  -- 重建整张表
-- 或者
OPTIMIZE TABLE orders;

-- 分析表统计信息（让优化器做出更准确的决策）
ANALYZE TABLE orders;

10. EXPLAIN 解读

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 1;

10.1 关键字段

字段	含义
`id`	查询序号，子查询有多个
`select_type`	查询类型（SIMPLE/PRIMARY/SUBQUERY/UNION等）
`table`	当前行访问的表
`partitions`	分区信息
`type`	访问类型（重要！）
`possible_keys`	可能使用的索引
`key`	实际使用的索引
`key_len`	使用的索引长度（字节）
`ref`	与索引比较的列或常量
`rows`	预估扫描行数（重要！）
`filtered`	WHERE 过滤后的行数百分比
`Extra`	额外信息（重要！）

10.2 type 访问类型（性能从好到差）

system > const > eq_ref > ref > range > index > ALL

system  : 表只有一行（系统表）
const   : 主键/唯一键等值查询，最多匹配一行
          WHERE id = 1 → const
eq_ref  : JOIN 时，被驱动表用主键/唯一键等值匹配
          ON a.id = b.id（b.id 是主键）→ eq_ref
ref     : 普通索引等值查询，可能多行
          WHERE user_id = 1（普通索引）→ ref
range   : 索引范围查询
          WHERE age BETWEEN 18 AND 30 → range
index   : 扫描整个索引树（比 ALL 略好，索引比数据小）
          SELECT id FROM users（id 是主键，扫全部）
ALL     : 全表扫描（最差！通常需要优化）

优化目标：type 至少达到 range，ref 以上更好。

10.3 Extra 关键信息

Extra 值	含义	好/坏
`Using index`	覆盖索引，无需回表	✅ 好
`Using index condition`	索引下推（ICP）	✅ 好
`Using where`	Server 层过滤	中
`Using temporary`	使用临时表（GROUP BY/DISTINCT）	⚠️ 差
`Using filesort`	额外排序（无法利用索引排序）	⚠️ 差
`Using join buffer`	JOIN 使用了 Buffer（无法用索引匹配）	⚠️ 差
`Impossible WHERE`	WHERE 条件永远为 false	注意

10.4 EXPLAIN ANALYZE（MySQL 8.0.18+）

-- 实际执行并显示真实行数和时间（不只是估算）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

11. Go 中的索引实践

11.1 避免在 Go 代码中绕过索引

// ❌ 错误：对 int 类型列传字符串（虽然 Go 驱动会转换，但某些情况下可能隐式转换失效）
rows, err := db.Query("SELECT * FROM users WHERE id = ?", "123")

// ✅ 正确：类型匹配
rows, err := db.Query("SELECT * FROM users WHERE id = ?", 123)

// ❌ 错误：拼接 SQL（SQL 注入 + 无法使用预处理缓存）
rows, err := db.Query(fmt.Sprintf("SELECT * FROM users WHERE name = '%s'", name))

// ✅ 正确：占位符
rows, err := db.Query("SELECT * FROM users WHERE name = ?", name)

11.2 批量查询使用 IN 时注意索引

// IN 列表在元素多时仍然走索引（range 类型），但元素太多效率下降
// 建议单次 IN 不超过 1000 个

ids := []int64{1, 2, 3, ..., 500}

// 使用 sqlx 的 In 辅助函数
query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
if err != nil {
    return err
}
query = db.Rebind(query)
rows, err := db.Query(query, args...)

11.3 利用覆盖索引优化 Go 查询

// ❌ 查所有列，需要回表
type User struct {
    ID    int64
    Name  string
    Email string
    Age   int
    // ... 20 个字段
}
db.Query("SELECT * FROM users WHERE status = 1 LIMIT 100")

// ✅ 只查需要的列，配合覆盖索引
type UserSummary struct {
    ID   int64
    Name string
}
// 确保 INDEX idx_status_name (status, name) 存在
db.Query("SELECT id, name FROM users WHERE status = 1 LIMIT 100")

11.4 GORM 中的索引标签

type Order struct {
    ID        uint           `gorm:"primarykey"`
    UserID    uint           `gorm:"not null;index:idx_user_status,priority:1"`
    Status    int8           `gorm:"not null;index:idx_user_status,priority:2"`
    CreatedAt time.Time      `gorm:"index"`
}

// 联合唯一索引
type UserEmail struct {
    UserID  uint   `gorm:"uniqueIndex:idx_user_email"`
    Email   string `gorm:"uniqueIndex:idx_user_email"`
}

// 手动迁移（创建索引）
db.AutoMigrate(&Order{})

小结

索引选择流程：
1. WHERE 条件中的等值列 → 优先建联合索引
2. 高频 ORDER BY / GROUP BY 列 → 纳入联合索引
3. 覆盖查询列 → 考虑联合索引包含 SELECT 列
4. EXPLAIN 验证 → type >= ref，Extra 无 filesort/temporary
5. 监控慢查询 → 发现新的索引需求

关键原则：

自增主键建聚簇索引，避免 UUID 主键
联合索引：等值在前，范围在后，高选择性在前
覆盖索引避免回表是最有效的优化手段
索引失效场景：函数操作、隐式类型转换、左侧通配符

目录