mysql索引的创建与维护最佳实践

该加索引时应依据查询条件和执行计划，优先为WHERE、JOIN、ORDER BY、GROUP BY中实际参与过滤或排序的列创建索引，结合EXPLAIN分析type、key、rows，避免盲目建索引。

什么时候该加索引：看查询条件和执行计划

不是所有字段都适合建索引，核心判断依据是 WHERE、JOIN、ORDER BY 和 GROUP BY 中实际参与过滤或排序的列。盲目加索引反而拖慢写入性能，还浪费磁盘空间。

先用 EXPLAIN 看查询是否走了索引：

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

重点关注 type（最好是 ref 或 range）、key（是否命中索引）、rows（扫描行数）。如果 type 是 ALL 且 rows 接近表总行数，大概率需要优化。

• 单列高频等值查询（如 user_id）适合单列索引
• 多个字段联合过滤（如 WHERE a=1 AND b=2）优先考虑复合索引，顺序按选择性高 → 过滤性强 → 排序需求排列
• LIKE 'abc%' 可走索引，LIKE '%abc' 不行；全文检索用 FULLTEXT 索引

复合索引的列顺序怎么定：最左前缀原则不是玄学

MySQL 的 B+ 树索引依赖「最左前缀匹配」，意味着只有从索引最左边开始连续的列才能被用于查找。比如建了 (a, b, c) 索引：

• ✅ WHERE a = 1、WHERE a = 1 AND b = 2、WHERE a = 1 AND b = 2 AND c = 3 都能用上
• ✅ WHERE a = 1 AND c = 3 也能用（a 匹配后，c 在索引中是跳过 b 的，但仅限于 IN 或等值，且优化器可能放弃）
• ❌ WHERE b = 2 或 WHERE c = 3 完全无法使用该索引

所以列顺序要按「区分度（cardinality）高 → 经常出现在 WHERE 条件左侧 → 用于排序/分组的列靠后」来排。例如用户表中 city（低区分度）和 created_at（高区分度），应建为 (created_at, city) 而非反过来。

哪些操作会悄悄让索引失效

即使建了索引，以下写法会让优化器弃用它：

• 对索引列做函数操作：WHERE YEAR(created_at) = 2025 → 改成 WHERE created_at >= '2025-01-01' AND created

  

  _at
• 隐式类型转换：WHERE mobile = 13812345678（mobile 是 VARCHAR）→ 改成字符串引号：'13812345678'
• 使用 != 或 NOT IN（尤其右边是子查询时）→ 尝试改写为 LEFT JOIN ... IS NULL 或覆盖索引 + 条件过滤
• 在 OR 条件中混用无索引列：WHERE a = 1 OR b = 2，若 b 无索引，整个条件可能全表扫 → 拆成 UNION 或补索引

特别注意：JSON 字段上的虚拟列索引必须显式定义并建索引，否则 -> 或 ->> 表达式默认不走索引。

索引维护不是一劳永逸：定期检查和清理

索引不是建完就完事。随着数据增长和业务变化，有些索引可能长期未被使用，甚至成为写入瓶颈。

• 查未使用索引（MySQL 8.0+）：SELECT * FROM sys.schema_unused_indexes;
• 查冗余索引（如已有 (a,b)，又建了 (a)）：SELECT * FROM sys.schema_redundant_indexes;
• 监控索引长度：单字段索引建议不超过 767 字节（utf8mb4 下约 191 个字符），超长文本用前缀索引（如 content(100)），但需确认前 100 字足以区分大部分值
• 大批量写入前可临时禁用非必要索引（ALTER TABLE t DROP INDEX idx_x; ... INSERT ...; ALTER TABLE t ADD INDEX idx_x(...);），但要注意主键和唯一约束不能删

真正容易被忽略的是：索引统计信息过期会导致执行计划劣化。手动更新用 ANALYZE TABLE t;，生产环境建议开启 innodb_stats_auto_recalc = ON（默认已开），但大表仍建议每周夜间低峰触发一次 ANALYZE。