MySQL 索引优化 - 编程实战

索引是数据库性能优化的核心。一个合理的索引设计可以让查询从数秒降到毫秒级别。本文将从底层数据结构出发，结合 EXPLAIN 分析工具，系统讲解 MySQL 索引优化的方方面面。

B+Tree：索引的底层基石

MySQL InnoDB 引擎默认使用 B+Tree 作为索引结构。理解 B+Tree 的特性对优化至关重要：

所有数据存储在叶子节点：非叶子节点只存键值，叶子节点存完整数据
叶子节点通过双向链表连接：支持高效的范围查询和排序
高度可控：通常 3-4 层即可索引上亿条数据（每层 fan-out 约 1000+）
查询复杂度 O(log n)：稳定且可预测

聚簇索引与二级索引

-- InnoDB 中，主键就是聚簇索引，叶子节点存完整行数据
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,   -- 聚簇索引
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    INDEX idx_email (email)              -- 二级索引
);

-- 二级索引的叶子节点存的是主键值
-- 通过二级索引查询需要"回表"：二级索引 → 主键 → 聚簇索引 → 行数据

建议：主键尽量用自增整数。UUID 等随机主键会导致页分裂，影响写入性能。

覆盖索引：避免回表

当查询的所有列都在索引中时，不需要回表查询聚簇索引，性能大幅提升：

-- 查询所需列全在索引中 → 覆盖索引，不需要回表
CREATE INDEX idx_email_name ON users (email, name);
SELECT name FROM users WHERE email = 'alice@example.com';

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

联合索引与最左前缀原则

-- 创建联合索引 (a, b, c)
CREATE INDEX idx_abc ON orders (customer_id, order_date, status);

-- 可以使用索引的查询（遵循最左前缀）
SELECT * FROM orders WHERE customer_id = 1;                          -- ✅ 用到 a
SELECT * FROM orders WHERE customer_id = 1 AND order_date = '2026-01-01';  -- ✅ 用到 a,b
SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';      -- ✅ 用到 a（跳过 b 导致 c 失效）

-- 无法使用索引的查询
SELECT * FROM orders WHERE order_date = '2026-01-01';                -- ❌ 没用到 a
SELECT * FROM orders WHERE status = 'paid';                          -- ❌ 没用到 a

EXPLAIN 分析实战

EXPLAIN SELECT u.name, COUNT(*) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
  AND o.order_date >= '2026-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

-- 关注 key（使用的索引）、rows（扫描行数）、Extra（额外信息）
-- type 列从好到坏：system > const > eq_ref > ref > range > index > ALL

type	含义	示例
const	主键或唯一索引等值查询	WHERE id = 1
eq_ref	关联查询用主键/唯一索引	JOIN ON a.id = b.id
ref	非唯一索引等值查询	WHERE email = 'x'
range	索引范围扫描	WHERE age > 18
index	全索引扫描	仅比全表扫描好
ALL	全表扫描	需优化

索引优化的常见误区

索引越多越好？索引需要维护，写入时会更新所有索引，过多索引会拖慢写入性能。
把 WHERE 条件中的列全部建索引？应该建联合索引而非多个单列索引。MySQL 通常一次查询只用一个索引。
在索引列上使用函数会导致索引失效：WHERE DATE(create_time) = '2026-01-01' 无法用索引，应改为 WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02'。
LIKE '%关键词%' 无法使用索引。前缀模糊匹配 LIKE '关键词%' 可以用索引。

索引下推（Index Condition Pushdown）

MySQL 5.6+ 支持索引下推，将部分 WHERE 条件下推到存储引擎层过滤，减少回表次数：

-- 联合索引 (name, age)
-- 查询：SELECT * FROM users WHERE name LIKE '张%' AND age = 20;

-- 无 ICP：存储引擎通过 name 过滤后全部回表，Server 层再过滤 age
-- 有 ICP：存储引擎在用 name 过滤的同时判断 age，不满足的不回表

实际调优流程

开启慢查询日志：slow_query_log = ON，设置 long_query_time = 1
使用 pt-query-digest 分析慢查日志，找到高频慢 SQL
EXPLAIN 分析执行计划，关注 type、rows、Extra
创建合适的索引，验证优化效果
持续监控，定期 Review 索引使用情况

总结

MySQL 索引优化的核心在于理解 B+Tree 的工作原理和最左前缀原则。通过覆盖索引避免回表、通过联合索引优化多条件查询、通过 EXPLAIN 验证优化效果，这是一套行之有效的调优方法论。记住：索引的目的是减少扫描的数据量——Always Think in Terms of Rows。

← 返回首页