后端开发

MySQL 索引原理与慢查询优化实战

2025-06-25 wangjun 17 min read
{}

为什么查询会慢？

数据量小时全表扫描无所谓，到千万级就是灾难。MySQL 快的原因，是 索引：一种让"查找"从 O(n) 变成 O(log n) 的数据结构。理解索引原理，才能解释"为什么加了索引还是慢"。

InnoDB 与 B+ 树

InnoDB 的主索引（聚簇索引）是一棵 B+ 树：叶子节点存整行数据，非叶子节点只存索引键。B+ 树的优势：

  • 矮胖：三层就能支撑千万级数据（扇出大、层数少）
  • 叶子节点有序且串成链表：范围查询（BETWEEN、ORDER BY）极快
-- 建索引示例
CREATE INDEX idx_user_id ON orders(user_id);
-- 组合索引（注意列顺序！）
CREATE INDEX idx_user_status ON orders(user_id, status);

聚簇索引 vs 二级索引：回表

聚簇索引:  主键 → 整行数据（数据就在叶子）
二级索引:  普通列 → 主键 →（再查一次聚簇索引拿整行）
                                 ↑ 这一步叫"回表"

二级索引命中后通常还要回表取整行。如果查询的列都包含在索引里（覆盖索引），就可以避免回表：

-- 覆盖索引：只需 user_id 和 status
SELECT user_id, status FROM orders WHERE user_id = 1;
-- 上面语句只用二级索引即可返回，无需回表

最左前缀原则

组合索引 (user_id, status, created_at) 等价于三个索引：(user_id)、(user_id, status)、(user_id, status, created_at)。查询必须从最左列开始匹配才有效：

WHERE user_id = 1                    -- 用到索引 ✓
WHERE user_id = 1 AND status = 2     -- 用到索引 ✓
WHERE status = 2                     -- 用不到 ✗（跳过了第一列）
WHERE user_id = 1 AND created_at > '2025-01-01'  -- 部分用到（范围后失效）

所以组合索引的列顺序要按"等值条件优先、范围条件靠后"设计。

用 EXPLAIN 诊断慢查询

EXPLAIN SELECT * FROM orders
WHERE user_id = 10086 AND status = 1
ORDER BY created_at DESC LIMIT 20;
| type | key                | rows | Extra                          |
| ref  | idx_user_status    | 1200 | Using index condition; Using filesort |

重点看四列：

  • type：访问类型。ALL（全表）→ index → range → ref → eq_ref → const，从左到右越来越快
  • key：实际使用的索引。为 NULL 说明没走索引
  • rows：预估扫描行数，越小越好
  • Extra：出现 Using filesort 要警惕，ORDER BY 与索引顺序不一致导致

索引设计五问

  1. 查询的 WHERE 等值列放组合索引最前面
  2. ORDER BY / GROUP BY 的列尽量并入索引，消除 filesort
  3. 区分度低的列（性别、状态）别单独建索引，选择性差
  4. 字符串前缀索引（INDEX(phone(11))）省空间，但别太短导致选择性下降
  5. 索引不是越多越好：每条索引都拖慢 INSERT/UPDATE，且占磁盘
索引是"用空间换时间"的典型 -- 建对了是性能利器，建多了是写入负担。