mysql索引哪些情况下会失效

     MySQL索引失效通常由查询语句编写不当、索引设计缺陷或优化器决策导致。以下是主要失效场景及解决方案，附 EXPLAIN 诊断关键点：

一、查询语句问题（高频失效场景）

1. 违反最左前缀原则（复合索引）

1. 复合索引 (A,B,C)，但查询未使用 A。

1. CREATE INDEX idx_user ON users(name, age, city);
   SELECT * FROM users WHERE age = 30;           -- 失效（未用name）
   SELECT * FROM users WHERE name = 'John';      -- 生效（使用最左列）

2. 对索引列运算或函数处理

1. 场景：索引列参与计算、函数调用或类型转换。

2. 示例：

1. SELECT * FROM orders WHERE YEAR(create_time) = 2023;     -- 失效
   SELECT * FROM users WHERE CAST(phone AS CHAR) = '123';    -- 失效（隐式转换）

3. 解决方案：
   

1. SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

3. 模糊查询以 % 开头

1. 场景：LIKE '%abc' 无法利用索引定位。

2. 示例：

1. SELECT * FROM products WHERE name LIKE '%Pro';   -- 全表扫描
   SELECT * FROM products WHERE name LIKE 'Pro%';   -- 索引生效（最左匹配）

4. 使用 OR 连接非索引字段

1. 场景：OR 连接的字段中有任意一个无索引。

2. 示例：

1. SELECT * FROM users WHERE age = 25 OR address = 'Beijing';
    --address无索引则全表扫描

3. 解决方案：改用 UNION：

1. SELECT * FROM users WHERE age = 25 UNION 
   SELECT * FROM users WHERE address = 'Beijing'; -- 确保address有索引

二、索引设计缺陷

1. 索引选择性过低

• 场景：索引列重复值过高（如性别、状态）。

• 诊断：SHOW INDEX FROM users 查看 Cardinality（基数），值越低选择性越差。

• 优化：避免为低选择性列单独建索引，可结合高频查询列建复合索引。

2. NULL 值过多

• 场景：索引列包含大量 NULL 时，IS NULL 可能无法使用索引。

• 解决方案：建表时设置 NOT NULL DEFAULT 减少 NULL 值。

三、优化器决策导致失效

1. 全表扫描成本更低

1. 场景：查询覆盖超过 15%~30% 数据时，优化器可能放弃索引。

2. 诊断：EXPLAIN 中 type=ALL 且 rows 值很大。

3. 优化：强制索引（谨慎使用）：

1. SELECT * FROM users FORCE INDEX(idx_age) WHERE age > 10;

2. 统计信息不准确

1. 场景：表数据量剧烈变化后，优化器误判执行计划。

2. 解决：定期更新统计信息：

1. ANALYZE TABLE users;  -- 更新InnoDB统计信息

四、MySQL 特有陷阱

1. 字符集/校对规则不匹配

1. 场景：JOIN 或 WHERE 比较时字符集不一致。

2. 示例：

1. SELECT * FROM users u 
   JOIN orders o ON u.name = o.customer_name  -- 若字符集不同，索引失效

3. 解决：统一字符集或使用 CONVERT() 转换。

2. 索引条件下推（ICP）失效

1. 场景：MySQL 5.6+ 支持 ICP，但某些条件（如子查询）会使其失效。

2. 诊断：EXPLAIN 出现 Using index condition 表示 ICP 生效，否则失效。

验证索引是否生效：EXPLAIN 关键字段

高效索引使用原则

1. 写查询：

• 避免索引列运算，LIKE 右侧模糊。

• 用 UNION 替代 OR，用 IN 替代范围查询。

2. 建索引：

• 复合索引按 高频查询列 > 高选择性列 > 范围查询列 顺序排列。

• 避免对低选择性列单独建索引。

3. 调优：

• 定期 ANALYZE TABLE 更新统计信息。

• 监控慢查询日志：slow_query_log = ON。

通过 EXPLAIN SELECT ... 分析执行计划是定位索引问题的核心手段！