mysql中使用索引优化WHERE子句的查询性能

WHERE字段无索引会导致全表扫描；EXPLAIN中key为NULL且rows接近总行数即证实；需注意联合索引最左前缀、函数/隐式转换致失效、LIKE前缀匹配才走索引、ORDER BY需纳入索引避免filesort。

WHERE条件字段没加索引，查询会全表扫描

MySQL在执行 WHERE 查询时，如果过滤字段上没有索引，优化器通常会选择全表扫描（type: ALL），数据量稍大就明显变慢。用 EXPLAIN 查看执行计划是最直接的判断方式：

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

若结果中 key 列为 NULL，说明没走索引；rows 值接近表总行数，基本就是全扫了。

常见疏忽点：

• 只给单字段建索引，但查询是多条件组合（如 WHERE status = 'paid' AND created_at > '2025-01-01'），需考虑联合索引顺序
• 对索引字段用了函数或表达式（如 WHERE YEAR(created_at) = 2025），会导致索引失效
• 字段类型不一致引发隐式转换，比如 user_id 是 BIGINT，但查询写成 WHERE user_id = '123'（字符串）

联合索引的最左前缀原则必须严格遵守

MySQL联合索引（如 INDEX idx_status_time (status, created_at)）只支持从左到右匹配。以下查询能命中索引：

WHERE status = 'shipped'
WHERE status = 'shipped' AND created_at > '2025-01-01'

但这些不行：

WHERE created_at > '2025-01-01'  -- 缺少 status，跳过最左列
WHERE status IN ('paid','shipped') AND created_at > '2025-01-01'  -- IN 后范围查询，created_at 可能无法继续使用索引

注意点：

• = 和 IN 条件可以中断最左前缀，但之后的字段是否还能用索引，取决于是否还有等值条件支撑
• 范围查询（>, , BETWEEN, LIKE 'abc%'）之后的字段，一般无法用于索引查找（仅可用于索引覆盖或排序）
• 把高频、高区分度字段放在联合索引左侧，比如 user_id 比 status 更适合作第一列

LIKE 模糊查询只有前缀匹配才走索引

LIKE 是否能用索引，关键看通配符位置：

• WHERE name LIKE 'John%' → 走索引（B+树可定位到 'John' 开头的所有叶子节点）
• WHERE name LIKE '%ohn' 或 WHERE name LIKE '%oh%' → 不走索引（无法从树根开始定位）
• WHERE name LIKE 'J%h%n' → 仍走索引（只要开头固定，中间有通配符不影响前缀匹配）

如果业务必须做前后模糊搜索，不要硬扛索引，考虑：

• 用全文索引（FULLTEXT）配合 MATCH ... AGAINST
• 引入 Elasticsearch 或 MySQL 8.0+ 的 Ngram Parser
• 冗余字段：比如提前存小写 + 去空格的 name_normalized，再建索引做精确匹配

ORDER BY 和 LIMIT 配合索引能避免 filesort

当 WHERE 已用索引，但 ORDER BY 字段不在该索引中，MySQL 会额外触发 Using filesort，性能损耗显著。例如：

SELECT * FROM products WHERE category_id = 5 ORDER BY price DESC LIMIT 10;

若只有 INDEX(category_id)，就会 filesort；加上联合索引 INDE

X(category_id, price) 就可直接按索引顺序取前 10 条。

注意事项：

• ORDER BY 方向要和索引列方向一致：索引是 (a ASC, b DESC)，那么 ORDER BY a ASC, b DESC 才能完全利用
• 混合方向（如 ORDER BY a ASC, b ASC 对应 (a ASC, b DESC) 索引）会导致无法使用索引排序
• LIMIT 越小，索引覆盖带来的收益越明显；但若 LIMIT 很大（如 LIMIT 10000, 20），即使有索引，偏移成本也高，应改用游标分页（WHERE id > ? ORDER BY id LIMIT 20）

索引不是越多越好，每增一个索引都会拖慢写入，并占用磁盘和内存。真正关键的是理解查询模式、看清 EXPLAIN 输出、验证索引是否真的被用上——很多时候加了索引却因类型不匹配或写法不当而白费。