想必大家都跟我一样，碰到来自面试官的灵魂拷问。

今天我们就来捋一捋这个问题

一、常见失效原因及解决方案

1. 违反最左前缀原则

  • 场景：联合索引 (a,b,c)，但查询条件为 b=? AND c=?（缺少 a）。

  • 解决：

    • 重写查询条件：确保从索引最左侧列开始使用（如 a=? AND b=?）。

    • 调整索引顺序：按查询频率重建索引为 (b,c,a)。

2. 对索引列进行计算或函数操作

  • 失效示例：

    SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 索引失效

  • 解决：

    SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';--改为范围查询：

3. 使用 OR 连接非索引列

  • 失效示例：

    SELECT * FROM users WHERE id = 100 OR name = 'John'; -- `name` 无索引

  • 解决：

    #为 name 添加独立索引。
#改用 UNION：
SELECT * FROM users WHERE id = 100
UNION
SELECT * FROM users WHERE name = 'John';

4. 隐式类型转换

  • 场景：索引列 varchar，但查询用数字：

    SELECT * FROM users WHERE phone = 13800138000; -- phone 是 varchar

  • 解决：确保类型一致：

    SELECT * FROM users WHERE phone = '13800138000';

5. 索引列使用 != 或 NOT IN

  • 失效示例：

    SELECT * FROM users WHERE status != 1; -- 全表扫描

  • 解决：

    • 考虑用 IN 或范围查询替代。

    • 数据量小时可忽略，数据量大需评估必要性。

6. LIKE 以通配符开头

  • 失效示例：

    SELECT * FROM users WHERE name LIKE '%abc%'; -- 无法走索引

  • 解决：

    • 改为前缀匹配：LIKE 'abc%'。

    • 使用全文索引（如 MATCH AGAINST）。

7. 优化器选择全表扫描

  • 场景：数据量小时，优化器认为全表扫描更快。

  • 解决：

SELECT * FROM users FORCE INDEX(index_name) WHERE ...; --强制使用索引（谨慎）：

二、诊断工具

  1. EXPLAIN 分析执行计划：

    EXPLAIN SELECT * FROM users WHERE name = 'John';

    • 关键字段：

      • type：ALL 表示全表扫描。

      • key：NULL 表示未用索引。

      • Extra：Using where 表示在内存中过滤。

  2. 开启慢查询日志：

    -- 配置文件 my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  -- 超过2秒的查询

三、优化策略

1. 更新统计信息

  • 手动更新索引统计信息：

    ANALYZE TABLE users;

2. 索引重建

  • 修复索引碎片：

    ALTER TABLE users REBUILD INDEX index_name;

3. 覆盖索引优化

  • 只查询索引列，避免回表：

    SELECT id, name FROM users WHERE name = 'John'; -- (id,name) 建立联合索引

4. 调整优化器参数

  • 强制优化器优先使用索引：

    SET optimizer_switch = 'index_merge=off'; -- 关闭索引合并

四、预防措施

  1. 设计阶段：

    • 避免在低区分度列（如性别）建索引。

    • 联合索引按查询频率从左到右排序。

  2. 开发规范：

    • 禁止对索引列使用函数。

    • 统一字段类型，避免隐式转换。

  3. 监控：

    • 定期检查慢查询日志。

    • 使用 pt-index-usage（Percona工具）分析索引使用情况。

案例演示

问题SQL：

SELECT * FROM orders WHERE MONTH(create_time) = 12; -- 索引失效

优化后：

SELECT * FROM orders 
WHERE create_time BETWEEN '2023-12-01' AND '2023-12-31'; -- 有效利用索引