mysql索引的选择与优化策略_技术教程

WHERE条件中使用函数导致索引失效，因MySQL无法将函数结果与B+树中原始字段值对齐；应改写为范围查询、建生成列索引或避免字段运算。

MySQL 无法对表达式结果直接使用 B+ 树索引做快速查找，比如 WHERE YEAR(create_time) = 2025 或 WHERE UPPER(name) = 'ABC'。优化器看到的是函数计算后的值，而索引里存的是原始字段值，二者无法对齐。

实操建议：

改写为范围查询：用 create_time BETWEEN '2025-01-01' AND '2025-12-31 23:59:59' 替代 YEAR()

建生成列 + 索引（MySQL 5.7+）：

ALTER TABLE orders ADD COLUMN create_year INT AS (YEAR(create_time)) STORED;
CREATE INDEX idx_create_year ON orders(create_year);

MySQL 的联合索引遵循最左前缀原则，但排序和分组还额外依赖“索引有序性”。如果 ORDER BY a, b，而索引是 (a, c, b)，那 b 就无法利用索引排序——因为中间插了 c，破坏了 a,b 的物理连续顺序。

实操建议：

让 ORDER BY 字段尽量靠前且连续出现在联合索引中，例如 INDEX(a, b, c) 支持 ORDER BY a, b，但不支持 ORDER BY b, c
GROUP BY 同理，且若含聚合函数（如 COUNT()），确保分组字段能走索引，否则会触发 Using temporary; Using filesort
如果既要 WHERE a = ? 又要 ORDER BY b，优先建 INDEX(a, b)，而不是分开建两个单列索引

重复索引指功能完全被另一个索引覆盖，比如已有 INDEX(a, b)，又建了 INDEX(a)；低效索引指长期没被 SELECT 使用、却在 INSERT/UPDATE/DELETE 中持续拖慢写性能的索引。

实操建议：

查冗余索引：

SELECT * FROM sys.schema_redundant_indexes;

（需启用 sys schema）

看索引实际使用率：

LECT index_name, rows_selected FROM sys.schema_index_statistics WHERE table_name = 'orders';

以通配符开头的模糊查询（LIKE '%abc'）确实无法使用 B+ 树索引的有序结构，但不是所有模糊场景都无解。

实操建议：

索引优化最常被忽略的一点：它不是静态配置，而是随查询模式和数据分布动态变化的。上线后必须定期看 slow_log 和 sys.statement_analysis，而不是只靠建表时的预判。

mysql索引的选择与优化策略