MySQL 索引常见误区与优化思路

“慢查询?加个索引就好啦” —— 这句话只对了一半。索引用得不对,反而可能让查询更慢、更占空间。这篇整理一下我自己常踩、也常看到别人踩的几个坑。

误区 1:对每个 WHERE 字段都建索引

索引不是免费的。每多一个索引,就多一份磁盘占用、多一次写入开销(INSERT / UPDATE 都要维护)。一个表上常驻 10+ 个单列索引,大概率有 70% 从来没被用过。

经验法则:

高频查询的字段才建索引
区分度低的字段(如 status 只有 2 种值)单独建索引价值很小
组合索引比一堆单列索引更高效

误区 2:组合索引顺序随便写

-- 建了这个索引
CREATE INDEX idx_a_b_c ON t (a, b, c);

-- 这些能用到:
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 这些用不到 / 部分用到:
WHERE b = 2 AND c = 3           -- 完全用不到
WHERE a = 1 AND c = 3           -- 只用到 a

这是经典的“最左前缀”原则。设计组合索引时,把等值过滤、区分度高的字段放前面,范围查询的字段放最后。

误区 3:在索引列上做运算 / 函数

-- 不会走索引
WHERE DATE(created_at) = '2026-05-01'
WHERE YEAR(created_at) = 2026
WHERE id + 1 = 100

-- 应改写成
WHERE created_at >= '2026-05-01' AND created_at < '2026-05-02'
WHERE id = 99

MySQL 8 之后部分场景能用上函数索引,但更稳妥的方式还是改写 SQL。

误区 4:`LIKE '%xxx'` 也想走索引

前缀模糊(LIKE 'abc%')可以用索引,通配符在最前面('%abc')的话只能全表扫描。这种需求一般两种解法:

全文索引(FULLTEXT),适合中等规模的搜索
上外部搜索引擎(Meilisearch / Elasticsearch),适合数据量大、搜索功能复杂的场景

误区 5:不看执行计划就动手优化

写优化之前,先用 EXPLAIN(或 EXPLAIN ANALYZE)看一眼:

EXPLAIN SELECT * FROM posts
WHERE category_id = 1 AND status = 'published'
ORDER BY published_at DESC
LIMIT 10;

需要关注的几列:

type:const / eq_ref / ref / range 都还行,ALL 就是全表扫描
key:实际用上的索引,NULL 表示没用上
rows:估算扫描行数,越小越好
Extra:Using filesort 和 Using temporary 通常是优化信号

一个真实的优化案例

之前一个文章列表查询:

SELECT * FROM posts
WHERE status = 'published' AND category_id = 5
ORDER BY published_at DESC
LIMIT 20;

原本只有 idx_category(category_id),执行计划 Using filesort,几万行也得几百毫秒。

加上组合索引 (status, category_id, published_at) 之后:

WHERE 部分走索引等值过滤
ORDER BY 因为索引列已经天然有序,省掉了 filesort
实际耗时降到 ~5ms

小结

索引是工具,不是答案,先 EXPLAIN 再动手
组合索引比一堆单列更值钱,顺序很关键
写多读少的表,索引要克制;读多写少的表,可以多花点心思设计
别忘了:最快的查询,是不查询。能用缓存解决的,就别让数据库扛