后端开发数据库

MySQL 索引设计与优化：踩坑记录和排查技巧

, ,

索引是 MySQL 性能优化的核心。设计得好的索引能提升几百倍查询速度，设计得不好不仅没用，还会拖慢写入、占磁盘空间。这篇记录一下索引设计的经验，包括常见类型、创建方法、容易踩的坑。

索引基础概念

为什么需要索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
┌─────────────────────────────────────────────────────────────┐
│              索引的工作原理                                  │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  无索引查询（全表扫描）：                                     │
│  ┌──────────────────────────────────────────────────────┐  │
│  │ id │ name  │ email              │ age │ status      │  │
│  ├────┼───────┼────────────────────┼─────┼─────────────┤  │
│  │ 1  │ Alice │ alice@example.com  │ 25  │ active      │  │
│  │ 2  │ Bob   │ bob@example.com    │ 30  │ inactive    │  │
│  │ 3  │ Carol │ carol@example.com  │ 28  │ active      │  │
│  │ ...│ ...   │ ...                │ ... │ ...         │  │
│  │ 100│ Zack  │ zack@example.com   │ 35  │ active      │  │
│  └────┴───────┴────────────────────┴─────┴─────────────┘  │
│                                                              │
│  SELECT * FROM users WHERE email = 'bob@example.com';       │
│                                                              │
│  无索引：需要扫描 100 行数据                                  │
│  时间复杂度：O(n)                                            │
│                                                              │
│  ────────────────────────────────────────────────────────   │
│                                                              │
│  有索引查询（B+Tree 索引）：                                  │
│                                                              │
│         ┌─────────┐                                          │
│         │  B+Tree │                                          │
│         │  索引   │                                          │
│         └────┬────┘                                          │
│              │                                                │
│    ┌─────────┼─────────┐                                    │
│    │         │         │                                    │
│    ▼         ▼         ▼                                    │
│  alice@   bob@    carol@                                    │
│            │                                                │
│            ▼                                                │
│         直接定位到第 2 行                                     │
│                                                              │
│  时间复杂度：O(log n)                                        │
│                                                              │
└─────────────────────────────────────────────────────────────┘

MySQL 索引类型

索引类型 关键字 特点 适用场景
主键索引 PRIMARY KEY 唯一、非空、每个表只有一个 主键字段
唯一索引 UNIQUE 唯一、允许 NULL 唯一性约束字段（如邮箱、手机号）
普通索引 INDEX 允许重复 频繁查询的字段
全文索引 FULLTEXT 针对文本内容的关键词搜索 大文本字段搜索
组合索引 多字段组合 多列联合索引 多条件联合查询

索引的创建与管理

查看现有索引

1
2
3
4
5
-- 查看表的所有索引
SHOW INDEX FROM table_name;

-- 或
SHOW KEYS FROM table_name;

使用 ALTER TABLE 创建索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 添加普通索引
ALTER TABLE users ADD INDEX idx_email (email);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email_unique (email);

-- 添加主键索引
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加组合索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 添加前缀索引（针对长字符串）
ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

使用 CREATE INDEX 创建索引

1
2
3
4
5
6
7
8
9
-- 创建普通索引
CREATE INDEX index_name ON table_name (column_list);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_list);

-- 示例
CREATE INDEX idx_username ON users (username);
CREATE UNIQUE INDEX idx_phone ON users (phone);

删除索引

1
2
3
4
5
6
7
8
-- 使用 DROP INDEX
DROP INDEX index_name ON table_name;

-- 使用 ALTER TABLE
ALTER TABLE table_name DROP INDEX index_name;

-- 删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY;

索引创建最佳实践

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 示例表结构
CREATE TABLE `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `order_no` varchar(32) NOT NULL,
  `status` tinyint unsigned NOT NULL DEFAULT '0',
  `amount` decimal(10,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

组合索引设计原则

最左前缀法则

组合索引的查询条件必须从索引的最左边字段开始匹配，否则索引不会被使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 组合索引：(a, b, c)
ALTER TABLE test ADD INDEX idx_abc (a, b, c);

-- 能使用索引的查询
SELECT * FROM test WHERE a = 1;                           -- 使用 a
SELECT * FROM test WHERE a = 1 AND b = 2;                 -- 使用 a, b
SELECT * FROM test WHERE a = 1 AND b = 2 AND c = 3;      -- 使用 a, b, c
SELECT * FROM test WHERE a = 1 AND c = 3;                 -- 只使用 a（c 跳跃了 b）

-- 不能使用索引的查询
SELECT * FROM test WHERE b = 2;                           -- 缺少 a
SELECT * FROM test WHERE b = 2 AND c = 3;                 -- 缺少 a
SELECT * FROM test WHERE c = 3;                           -- 缺少 a, b

索引字段顺序设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
┌─────────────────────────────────────────────────────────────┐
│              组合索引字段顺序设计原则                        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  优先级排序：                                                │
│                                                              │
│  1. 等值查询字段（=）                                        │
│     放在最前面，过滤性最强                                   │
│     例：WHERE user_id = 100                                  │
│                                                              │
│  2. 排序字段（ORDER BY）                                     │
│     放在第二位，避免文件排序                                 │
│     例：ORDER BY created_at                                  │
│                                                              │
│  3. 范围查询字段（>, <, BETWEEN）                          │
│     放在后面，前面的字段仍能使用索引                         │
│     例：WHERE created_at > '2024-01-01'                     │
│                                                              │
│  4. 低选择性字段（如 status）                                │
│     一般放在最后或不单独建索引                               │
│                                                              │
└─────────────────────────────────────────────────────────────┘

实际案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 假设有以下查询场景：
-- 1. 查询某用户的订单列表，按时间倒序
-- 2. 查询某用户特定状态的订单

-- 差的设计：
ALTER TABLE orders ADD INDEX idx_status (status);
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE orders ADD INDEX idx_created_at (created_at);
-- 问题：idx_status 选择性低（只有几种状态），idx_user_id 和 idx_created_at 无法联合使用

-- 好的设计：
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);
-- 优势：
-- • WHERE user_id = ? 可以使用索引
-- • WHERE user_id = ? AND status = ? 可以使用索引
-- • WHERE user_id = ? ORDER BY created_at 可以使用索引（避免排序）
-- • WHERE user_id = ? AND status = ? AND created_at > ? 可以使用索引

索引失效的常见场景

场景一：隐式类型转换

当查询条件中的字段类型与索引字段类型不一致时，MySQL 会进行隐式类型转换，导致索引失效。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 表结构：pid VARCHAR(20)，建有索引
CREATE TABLE products (
  `pid` VARCHAR(20) NOT NULL,
  `name` VARCHAR(100),
  INDEX `idx_pid` (`pid`)
);

-- 索引失效：pid 是字符串类型，但查询条件传入数字
SELECT * FROM products WHERE pid = 1211111111;
-- MySQL 会先将 pid 列转换为数字再比较
-- 相当于：CAST(pid AS UNSIGNED) = 1211111111
-- 导致全表扫描

-- 正确使用：传入字符串类型
SELECT * FROM products WHERE pid = '1211111111';
-- 索引有效

场景二：在索引列上使用函数或运算

1
2
3
4
5
6
7
8
9
10
11
12
-- 索引失效：在索引列上使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 索引列 created_at 被 YEAR() 函数包裹

-- 正确使用：避免对索引列使用函数
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 索引失效：在索引列上进行运算
SELECT * FROM users WHERE id + 1 = 100;

-- 正确使用：将运算移到常量端
SELECT * FROM users WHERE id = 99;

场景三：使用不等于或 NOT IN

1
2
3
4
5
6
-- 索引可能失效（取决于数据分布和 MySQL 版本）
SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- 优化方案：使用覆盖索引或改写查询
SELECT * FROM users WHERE status = 0 OR status = 2;

场景四：LIKE 通配符在开头

1
2
3
4
5
6
7
8
9
-- 索引失效：通配符在开头
SELECT * FROM users WHERE email LIKE '%@example.com';

-- 索引有效：通配符不在开头
SELECT * FROM users WHERE email LIKE 'alice@%';

-- 如果有全文搜索需求，考虑使用 FULLTEXT 索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL');

场景五：OR 条件使用不当

1
2
3
4
5
6
7
8
9
-- 如果 OR 两边的条件不是都使用索引，可能导致全表扫描
SELECT * FROM users WHERE id = 1 OR email = 'test@example.com';
-- 如果 id 和 email 都有索引，可能使用索引合并
-- 如果 email 没有索引，则全表扫描

-- 优化方案：拆分为 UNION
SELECT * FROM users WHERE id = 1
UNION ALL
SELECT * FROM users WHERE email = 'test@example.com';

索引失效场景汇总表

失效场景 示例 解决方案
隐式类型转换 VARCHAR 字段用数字查询 确保类型一致，加引号
索引列使用函数 WHERE YEAR(col) = 2024 改为范围查询
索引列参与运算 WHERE id + 1 = 100 运算移到右侧
LIKE 通配符在开头 WHERE name LIKE '%abc' 使用全文索引或改写查询
OR 条件部分无索引 WHERE a = 1 OR b = 2 改用 UNION
组合索引未使用最左前缀 缺少最左字段 调整查询条件或索引顺序
数据量小，全表扫描更快 小表 无需优化

索引优化分析工具

EXPLAIN 分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 使用 EXPLAIN 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 输出示例
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

-- 关键字段说明：
-- type: 访问类型（system > const > eq_ref > ref > range > index > ALL）
--       ALL 表示全表扫描，需要优化
-- key: 实际使用的索引
-- rows: 扫描的行数（越小越好）
-- Extra: 额外信息，Using index 表示覆盖索引

慢查询日志

1
2
3
4
5
6
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询记录

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

SHOW PROFILE

1
2
3
4
5
6
7
8
9
-- 开启 profiling
SET profiling = 1;

-- 执行查询
SELECT * FROM users WHERE email = 'test@example.com';

-- 查看详细耗时
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

索引设计原则总结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
┌─────────────────────────────────────────────────────────────┐
│              MySQL 索引设计黄金法则                          │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  1. 选择性原则                                               │
│     索引列的区分度要高，避免在低选择性字段建索引              │
│     区分度 = COUNT(DISTINCT col) / COUNT(*)                 │
│     建议区分度 > 0.1                                         │
│                                                              │
│  2. 最左前缀原则                                             │
│     组合索引查询条件必须从最左列开始                          │
│                                                              │
│  3. 避免冗余索引                                             │
│     (a,b) 和 (a) 同时存在时，(a) 是冗余的                   │
│                                                              │
│  4. 控制索引数量                                             │
│     单表索引不超过 5 个，过多索引影响写入性能                 │
│                                                              │
│  5. 覆盖索引优先                                             │
│     SELECT 的字段尽量在索引中，避免回表查询                   │
│                                                              │
│  6. 避免对频繁更新的字段建索引                                │
│     索引维护有开销，频繁更新的字段索引代价高                  │
│                                                              │
│  7. 优先使用自增整数作为主键                                  │
│     避免使用 UUID 等随机字符串作为主键                        │
│                                                              │
└─────────────────────────────────────────────────────────────┘

总结

MySQL 索引优化的一些经验：

  1. 理解索引类型：根据业务需求选择 PRIMARY KEY、UNIQUE、INDEX 或 FULLTEXT
  2. 掌握创建方法：ALTER TABLE 和 CREATE INDEX 都要会
  3. 遵循最左前缀：组合索引查询条件要从最左字段开始
  4. 警惕类型转换：字符串字段查询记得加引号
  5. 避免函数运算：索引列上不要用函数或参与运算
  6. 善用分析工具：EXPLAIN 分析执行计划，慢查询日志定位问题
  7. 权衡读写性能：索引加速查询但拖慢写入，要控制数量

索引这东西，建少了查询慢，建多了写入慢，需要多测试多调整。