3. 索引失效场景分析：索引类型与优化策略

索引是数据库查询加速的核心手段，但索引并非总是有效。理解索引的底层结构（B+Tree 与 Hash）以及索引失效的典型场景，是慢查询根因定位的关键能力。本节将系统梳理索引类型、失效原因及优化策略。

3.1 索引类型：B+Tree 与 Hash

MySQL 等关系型数据库主要支持 B+Tree 索引和 Hash 索引。两者在存储结构、查询特性和适用场景上存在本质差异。

3.2 索引失效的典型原因

即使表上建立了索引，查询优化器也可能选择全表扫描而非索引扫描。以下是生产环境中最常见的索引失效场景。

3.2.1 函数操作

对索引列使用函数或表达式计算，会导致优化器无法使用索引。

-- ❌ 索引失效：对 create_time 列使用 DATE() 函数
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';

-- ✅ 正确写法：使用范围查询，索引生效
SELECT * FROM orders WHERE create_time >= '2024-01-01 00:00:00'
  AND create_time < '2024-01-02 00:00:00';

-- ❌ 索引失效：对 name 列使用字符串函数
SELECT * FROM users WHERE LEFT(name, 3) = '张';

-- ✅ 正确写法：使用前缀匹配
SELECT * FROM users WHERE name LIKE '张%';

3.2.2 隐式类型转换

当索引列类型与查询条件类型不一致时，数据库会进行隐式转换，导致索引失效。

-- 假设 user_id 为 VARCHAR 类型，但传入整数
-- ❌ 索引失效：发生隐式转换 CAST(user_id AS SIGNED)
SELECT * FROM users WHERE user_id = 12345;

-- ✅ 正确写法：保持类型一致
SELECT * FROM users WHERE user_id = '12345';

3.2.3 不等值查询与否定条件

不等值操作符（<>、!=、NOT IN、NOT LIKE）通常无法使用索引，因为 B+Tree 的有序性在排除性查询中难以发挥优势。

-- ❌ 索引失效：不等值查询
SELECT * FROM products WHERE category_id <> 5;

-- ❌ 索引失效：NOT IN
SELECT * FROM employees WHERE dept_id NOT IN (1, 2, 3);

-- ✅ 优化思路：改写为范围查询（如果可以）
SELECT * FROM products WHERE category_id < 5 OR category_id > 5;

3.2.4 最左前缀法则失效

对于联合索引 (a, b, c)，查询必须从最左列开始，且不能跳过中间列。

3.2.5 LIKE 模糊查询

只有前缀匹配才能使用索引，后缀匹配或中间匹配会导致索引失效。

-- ✅ 前缀匹配：索引生效
SELECT * FROM articles WHERE title LIKE 'MySQL%';

-- ❌ 后缀匹配：索引失效
SELECT * FROM articles WHERE title LIKE '%优化';

-- ❌ 中间匹配：索引失效
SELECT * FROM articles WHERE title LIKE '%数据库%';

3.2.6 OR 条件

当 OR 连接的多个条件中，只要有一个列没有索引，整个查询就会退化为全表扫描。

-- 假设 id 有索引，email 无索引
-- ❌ 索引失效：email 无索引导致全表扫描
SELECT * FROM users WHERE id = 100 OR email = 'test@example.com';

-- ✅ 优化：使用 UNION 拆分
SELECT * FROM users WHERE id = 100
UNION
SELECT * FROM users WHERE email = 'test@example.com';

3.3 索引优化策略

基于上述失效场景，以下是经过生产验证的索引优化策略。

1. 选择合适的索引列：优先选择高选择性（区分度大）的列作为索引，如用户 ID、订单号。避免在性别、状态等低基数列上建索引。
2. 合理设计联合索引：将等值查询的列放在前面，范围查询的列放在后面。例如 INDEX( status, create_time )。
3. 覆盖索引：查询的列全部包含在索引中，避免回表。使用 EXPLAIN 查看 Extra 字段是否为 Using index。
4. 避免冗余索引：删除重复或包含关系的索引。例如 INDEX(a) 和 INDEX(a,b) 中，前者是冗余的。
5. 监控索引使用率：通过 sys.schema_unused_indexes 或 performance_schema 查找从未使用的索引并清理。
6. 使用索引提示：在必要时使用 FORCE INDEX 或 USE INDEX 引导优化器，但需谨慎验证。