MySQL 索引优化与慢查询分析方法

前言

MySQL 基础篇讲了增删查改。实际工作中，这些操作做到”对”不难，做到”快”才见功力。

当数据量从几千行涨到几百万行，一条 SQL 从 10ms 变成 10s，这时候就需要索引和慢查询优化了。这篇文章聚焦于日常开发中最常用的优化手段。

---

一、B+Tree 索引原理（理解这个，索引就懂了一半）

MySQL InnoDB 引擎使用 B+Tree 作为索引的底层数据结构。

和二叉搜索树不同，B+Tree 一个节点可以存很多个键值（比如几十到几百个），这使得树的高度很低——百万级别的数据，B+Tree 高度通常只有 3 到 4 层。

关键特征：

• 所有数据都存在叶子节点
• 叶子节点之间用双向链表连接，支持范围查询
• 非叶子节点只存键值（不存数据），用于路由

这意味着从百万数据中查一条记录，只需要 3-4 次磁盘 IO（每次读一个节点进内存）。

---

二、聚簇索引与二级索引

聚簇索引（Clustered Index）

InnoDB 的主键索引就是聚簇索引。它的叶子节点存的是整行数据。

主键索引（聚簇索引）:
            [1..1000]
           /         \
      [1..500]       [501..1000]
      /      \        /        \
  [1,张三] [2,李四]  [3,王五] [4,赵六] ...
   ↑ 叶子节点直接存整行数据

二级索引（Secondary Index）

你手动创建的普通索引。它的叶子节点存的是主键值，而不是整行数据。

二级索引（name 列）:
         [A..Z]
        /      \
   [A..M]      [N..Z]
   /     \      /    \
[1,张三] [3,李四] [2,王五] [4,赵六]
 ↑ 叶子节点存的是主键 ID，不是整行

回表：通过二级索引查到主键后，还要拿着主键再去聚簇索引查一次完整数据。这个过程叫”回表”，是额外的性能开销。

覆盖索引：如果查询的列都在索引里（不需要回表），性能会好很多。

-- 假设有索引 idx_name_email (name, email)

-- 需要回表（因为 age 不在索引里）
SELECT name, email, age FROM users WHERE name = 'July';

-- 覆盖索引，不需要回表（name 和 email 都在索引里）
SELECT name, email FROM users WHERE name = 'July';

---

三、联合索引与最左前缀法则

联合索引是把多个列组合成一个索引。理解它的关键是最左前缀法则。

CREATE INDEX idx_a_b_c ON orders (user_id, status, create_time);

这个索引相当于同时创建了三个索引：

• user_id（最左列，单独用也有效）
• user_id, status（前两列，有效）
• user_id, status, create_time（三列都用，有效）

以下查询能用到索引：

WHERE user_id = 1                                              -- 匹配最左列
WHERE user_id = 1 AND status = 'paid'                          -- 匹配前两列
WHERE user_id = 1 AND status = 'paid' AND create_time > '2025' -- 全部匹配
WHERE user_id = 1 AND create_time > '2025'                     -- 匹配最左列（跳过 status，只能用到 user_id）

以下查询用不到索引：

WHERE status = 'paid'                                          -- 跳过最左列，全表扫描
WHERE create_time > '2025'                                     -- 跳过最左列
WHERE user_id > 1 AND status = 'paid'                          -- 范围查询 users 之后，status 无法用上索引

联合索引的列顺序原则：

1. 区分度高的放前面（能快速缩小范围）
2. 等值查询的列放前面，范围查询的列放后面
3. 最常用的查询条件放最左边

---

四、EXPLAIN 执行计划

EXPLAIN 是分析 SQL 性能最重要的工具。它能告诉你 MySQL 是怎么执行这条 SQL 的。

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

输出解读（只看最关键的几列）：

列	说明	好	坏
type	访问类型	const, eq_ref, ref, range	ALL（全表扫描）
key	实际使用的索引	有值	NULL（没走索引）
rows	预估扫描行数	越小越好	接近表总行数
Extra	额外信息	Using index（覆盖索引）	Using filesort, Using temporary

type 从好到坏排序：

system > const > eq_ref > ref > range > index > ALL
                                              ↑      ↑
                                   索引全扫描   全表扫描
                                   （也算慢）  （最差）

常见优化判断：

-- type = ALL，全表扫描，需要建索引
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- → 给 email 加索引

-- Extra = Using filesort，排序没走索引
EXPLAIN SELECT * FROM orders WHERE user_id = 1 ORDER BY create_time DESC;
-- → 建联合索引 (user_id, create_time)

-- Extra = Using temporary，用了临时表
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- → 给 status 加索引

-- Extra = Using index，完美，覆盖索引
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1;
-- → 查询的列都在索引里，不需要回表

常见 EXPLAIN 输出及对策

type	Extra	问题	对策
ALL	NULL	全表扫描	加索引
ref	NULL	正常	—
range	NULL	范围扫描	如果 rows 很大考虑加联合索引
ref	Using filesort	排序没走索引	调整联合索引，把排序列也加进索引
ref	Using temporary	用了临时表	GROUP BY/DISTINCT 列加索引
index	Using index	索引覆盖	不错，但可能扫描了太多行

---

五、慢查询定位与优化

5.1 开启慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 超过 1 秒的查询记入日志
SET GLOBAL long_query_time = 1;

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

-- 查看有多少条慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';

5.2 定位问题 SQL

拿到慢查询日志后，按以下优先级排查：

1. 执行次数最多的 SQL——一条执行 10000 次的慢 SQL，优化收益远大于一条执行 1 次的超慢 SQL
2. 执行时间最长的 SQL——找出 TOP 10，逐个过 EXPLAIN
3. 锁等待的 SQL——事务阻塞其他操作

5.3 优化实战：一个典型例子

场景：分页查询越来越慢。

-- 第 1 页很快，第 10000 页很慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

问题在于 LIMIT offset, count 并不是直接跳过 offset 行，而是逐行计数到 offset 再取 count 行。offset 越大越慢。

优化方案（游标分页）：

-- 记住上一页最后一条的 ID，下次从这个 ID 往后查
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

这种写法不管 offset 多大，速度都是恒定的。但要保证 ID 是连续递增的，如果中间有删除，可能漏数据。具体场景具体分析。

5.4 索引失效的常见陷阱

-- 1. 对索引列做函数操作
SELECT * FROM users WHERE DATE(create_time) = '2025-01-01';    -- 全表扫描
SELECT * FROM users WHERE create_time >= '2025-01-01'
                     AND create_time < '2025-01-02';            -- 走索引

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone 是 VARCHAR，全表扫描
SELECT * FROM users WHERE phone = '13800138000'; -- 加引号，走索引

-- 3. LIKE 以 % 开头
SELECT * FROM articles WHERE title LIKE '%MySQL%';  -- 全表扫描
-- 解决方案：用全文索引（FULLTEXT）或 Elasticsearch

-- 4. OR 条件中有非索引列
SELECT * FROM users WHERE id = 1 OR nickname = 'test';
-- 改成：
SELECT * FROM users WHERE id = 1
UNION ALL
SELECT * FROM users WHERE nickname = 'test';

---

六、索引设计实操原则

1. 先分析查询，再建索引：不要提前给所有列都建索引。把项目里实际跑的 SQL 收集起来，分析哪些最频繁、最慢，针对性地建。

2. 选择性高的列优先建索引：选择性 = 不重复值的数量 / 总行数。选择性 0.9 以上的列（如 user_id）建索引效果好，选择性接近 0 的列（如 gender 只有男/女）建了也没什么用。

3. 一个查询最多走一个索引：MySQL 对一个 SELECT 通常只用一个索引。如果 WHERE 里有多个条件，建联合索引比建多个单列索引有效。

4. 不要过度建索引：每个索引都需要维护（INSERT/UPDATE/DELETE 时同时更新索引），索引太多会拖累写入性能。

5. 定期清理无效索引：用 SHOW INDEX FROM table_name 查看表中的索引，配合 performance_schema 分析哪些索引从未被使用过（sys.schema_unused_indexes）。

---

索引优化的核心就三句话：建对索引（联合索引、最左匹配）、用对索引（避免失效场景）、看懂 EXPLAIN（知道它实际走了什么）。其他更复杂的优化（分库分表、读写分离）是在这三步做完后仍有性能问题才考虑的。