MySQL 索引优化实战：EXPLAIN 分析与慢查询

一、索引为什么快

MySQL InnoDB 的索引是 B+ 树结构。没有索引时查询要全表扫描（逐行比对），有了索引就像查字典——通过树形结构二分定位，查询次数从 O(n) 降到 O(log n)。

但索引不是越多越好：每次写操作都要同步维护索引树，索引太多会拖慢插入和更新。

二、怎么建索引

-- 单列索引
CREATE INDEX idx_username ON users(username);

-- 联合索引（最常用，顺序很重要）
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

-- 唯一索引（业务上不允许重复的字段）
CREATE UNIQUE INDEX uk_email ON users(email);

-- 删除索引
DROP INDEX idx_username ON users;

建索引的优先级：

WHERE 条件字段（过滤最狠的先放）
JOIN 关联字段
ORDER BY / GROUP BY 字段
区分度高的字段（性别这种只有两种值的，索引意义不大）

三、EXPLAIN：看执行计划

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

返回的关键列：

列	含义	重点
`type`	访问类型	从好到差：`system > const > eq_ref > ref > range > index > ALL`，出现 `ALL`（全表扫描）要警惕
`key`	实际用到的索引	`NULL` 说明没走索引
`rows`	预估扫描行数	越小越好，和实际行数差距大说明统计信息过期
`Extra`	额外信息	`Using index`（覆盖索引，好）；`Using filesort`（文件排序，要优化）；`Using temporary`（临时表，要优化）

优化信号：

-- 慢的：type=ALL，全表扫描
EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- 建索引后：type=ref

-- 慢的：Extra 有 Using filesort
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at;
-- 建联合索引 (user_id, created_at) 后 filesort 消失

四、最左前缀原则（联合索引核心）

联合索引 (user_id, status, created_at) 相当于建了三个索引：(user_id)、(user_id, status)、(user_id, status, created_at)。

能用上的情况：

1
2
3

WHERE user_id = 100                     -- ✅ 用到第一列
WHERE user_id = 100 AND status = 'paid' -- ✅ 用到前两列
WHERE user_id = 100 AND status = 'paid' AND created_at > '2026-01-01'  -- ✅ 全用

用不上的情况：

1 2	WHERE status = 'paid' -- ❌ 跳过了第一列，索引失效 WHERE user_id = 100 AND created_at > '2026-01-01' -- ⚠️ 只用到 user_id，中间跳了 status

经验：查询条件里最常用、区分度最高的字段放联合索引最前面。

五、覆盖索引：查询只读索引

如果查询的所有字段都在索引里，MySQL 不用回表查数据行，这就是 Using index：

1
2
3

-- 建了联合索引 (user_id, status)，下面的查询只读索引即可
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 100;
-- Extra: Using index ✅

覆盖索引是超高并发场景的核心优化手段，比回表快一个数量级。

六、常见索引失效场景

-- 1. 对索引列做运算或函数
WHERE DATE(created_at) = '2026-08-01'   -- ❌ 改写成范围查询
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'  -- ✅

-- 2. 隐式类型转换（字段是字符串，传了数字）
WHERE phone = 13800138000   -- ❌ phone 是 varchar
WHERE phone = '13800138000' -- ✅

-- 3. 前缀模糊
WHERE name LIKE '%张%'  -- ❌ 以 % 开头不走索引
WHERE name LIKE '张%'   -- ✅

-- 4. OR 连接非索引列
WHERE user_id = 100 OR status = 'paid'  -- ❌ 两边都要有索引

-- 5. 联合索引中间列用了范围查询，后面的列失效
WHERE user_id = 100 AND status > 'a' AND created_at > '2026-01-01'
-- 只用到 user_id + status，created_at 失效

七、慢查询日志

-- 查看慢查询设置
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';   -- 默认 10 秒

-- 临时开启（生产要写到配置文件持久化）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- 超过 1 秒记录

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