mysql中避免全表扫描的索引优化

WHERE条件字段无索引必致全表扫描；单列查询需建普通索引，多条件组合应建联合索引且等值字段在前、范围字段在后，避免对索引字段使用函数。

WHERE 条件字段没加索引，全表扫描就躲不掉

MySQL 在执行 SELECT 时，如果 WHERE 子句中的字段没有索引，优化器大概率会选 ALL 类型的全表扫描。用 EXPLAIN 看执行计划，type 列显示 ALL 就是明确信号。

• 单列查询（如 WHERE status = 'active'）→ 给 status 加普通索引
• 多条件组合（如 WHERE user_id = 123 AND created_at > '2025-01-01'）→ 优先建联合索引，顺序按「等值查询字段在前、范围查询字段在后」，比如 (user_id, created_at)
• 避免对索引字段做函数操作，比如 WHERE YEAR(created_at) = 2025 会让索引失效；改用 created_at >= '2025-01-01' AND created_at

LIKE 查询以通配符开头，索引基本作废

LIKE 的写法直接影响索引是否生效：只有前缀匹配（LIKE 'abc%'）能走索引；LIKE '%abc' 或 LIKE '%abc%' 无法使用 B+ 树索引的有序结构，只能全表扫。

• 模糊搜索需求强，又必须支持任意位置匹配 → 考虑 FULLTEXT 索引（仅 MyISAM 和 InnoDB 支持），配合 MATCH ... AGAINST
• 若只是后缀匹配（如查邮箱域名），可冗余一个倒序字段（如 email_reversed），建索引后查 WHERE email_reversed LIKE 'moc.liamg.%'
• 别在索引列上用 LOWER()、TRIM()

  

  等函数，同样会导致索引失效

ORDER BY 和 GROUP BY 字段不在索引覆盖范围内

即使 WHERE 条件走了索引，如果后续的 ORDER BY 或 GROUP BY 字段没包含在同一个索引里，MySQL 可能触发 Using filesort 或 Using temporary，实际还是隐式遍历大量数据。

• 例如 SELECT * FROM orders WHERE shop_id = 100 ORDER BY created_at DESC → 建联合索引 (shop_id, created_at)，既能过滤又能排序
• 如果语句含 SELECT a, b, c，而索引只含 (a, b)，但 c 不在索引中，就会回表；想避免回表，可建覆盖索引：(a, b, c)
• 注意 ORDER BY 方向一致性：联合索引 (a ASC, b DESC) 在 MySQL 8.0+ 才支持混合排序；旧版本统一用 ASC 更稳妥

索引太多或太宽，反而拖慢写入和维护

每个索引都是 B+ 树，INSERT/UPDATE/DELETE 都要同步更新所有相关索引。字段过长（如 VARCHAR(1000)）、前缀过长、或为低区分度字段（如 gender）建索引，性价比极低。

• 字符串字段建索引优先考虑前缀长度，用 SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM users; 评估区分度，通常 6–12 字符足够
• 删除长期不用的索引：查 information_schema.STATISTICS 或用 sys.schema_unused_indexes（MySQL 5.7+）
• 复合索引不是字段越多越好，一般不超过 3 列；超过要考虑是否真需要，或拆成更聚焦的索引

SHOW INDEX FROM orders;
-- 关注 Key_name、Seq_in_index、Column_name、Cardinality
-- Cardinality 值越接近表总行数，该列区分度越高，越适合作为索引首列

索引优化最常被忽略的一点：没有定期看 slow_log 和 EXPLAIN 结果，而是凭经验“感觉加了就行”。线上表数据量一变，原来的索引可能就失效了。真正有效的优化，永远从慢查询出发，而不是从“应该加什么索引”出发。