MySQL进阶之路索引失效的11种情况介绍

在MySQL的查询优化中，索引是一项至关重要的技术，它能够大大提升数据检索的效率。本文将讨论这11种常见情况，帮助开发者更好地理解索引的使用及优化。

图示

1. 使用不等式操作符（!=, <, >）

• 例子：

  1	SELECT * FROM users WHERE age != 30;

• 原理：索引通常用于等值查询（=）或范围查询（>, <），不等式操作无法有效利用索引。
• 解决方案：避免使用不等式条件，改用范围查询。

  1 2	SELECT * FROM users WHERE age NOT BETWEEN 30 AND 30; SELECT * FROM users WHERE`age > 30`AND`age < 30;

2. 使用 OR 连接多个条件

• 例子：

  1	SELECT * FROM users WHERE age = 30 OR gender = 'male';

• 原理：OR 会导致多个独立查询，尤其当每个条件涉及不同列时，索引不会完全失效，会快速定位有索引列部分，无索引列进行全部扫描。
• 解决方案：使用 UNION 替代 OR、创建联合索引。

  1 2 3 4 5 6 7

  --创建联合索引 create index idx_users_age_gender on users(age,gender); SELECT * FROM users WHERE age = 30 OR gender = 'male'; --使用UNION合并子查询 SELECT * FROM users WHERE age = 30 UNION SELECT * FROM users WHERE gender = 'male';

3. 对索引字段进行计算操作

• 例子：

  1	SELECT * FROM orders WHERE YEAR(order_date) = 2024;

• 原理：计算和函数操作会改变数据的表现形式，索引无法直接应用。这个查询中，使用了 YEAR(order_date) 函数来提取 order_date 字段的年份，与 2024 进行比较。
• 解决方案：1.改为直接存储处理后的数据。2.直接改为当前字段的范围查询。

  1 2 3 4 5 6

  --范围查询 SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'; --直接存储处理后的数据 ALTER TABLE orders add column order_year INT; -- 新增字段 order_year UPDATE orders SET order_year = YEAR(order_date); SELECT * FROM orders WHERE order_year = 2024;

4. 对索引字段进行类型转换

• 例子：

  1	SELECT * FROM users WHERE CAST(age AS CHAR) = '30';

• 原理：类型转换会导致数据类型与索引数据类型不匹配，索引失效。
• 解决方案：确保查询条件的数据类型与索引数据类型一致，避免使用类型转换。

5. LIKE 头部模糊查询

• 例子：

  1	SELECT * FROM users WHERE name LIKE '%john';

• 原理：LIKE 查询以 % 开头时，索引无法使用，因为数据库无法提前确定匹配的范围。
• 解决方案：避免在 LIKE 查询中使用前缀 %，改为 LIKE 'john%'。

  1	SELECT * FROM users WHERE name LIKE 'john%';

6. NULL 值的查询

• 例子：

  1	SELECT * FROM users WHERE age IS NULL;

• 原理：索引对 NULL 值的查询支持有限，可能无法高效利用。
• 解决方案：避免频繁查询 NULL 值，或者为包含 NULL 值的字段设计专门的索引、将 NULL值替换为其他默认值。

  1 2 3 4 5 6

  -- 使用IFNULL() 函数 SELECT * FROM users WHERE IFNULL(age, -1) = -1; -- 使用COALESCE() 函数 SELECT * FROM users WHERE COALESCE(age, -1) = -1; --使用 NOT NULL 约束，修改字段默认值为 0 ALTER TABLE users MODIFY age NOT NULL DEFAULT 0;

7. DISTINCT 或 GROUP BY 操作

• 例子：

  1 2	SELECT DISTINCT age FROM users; SELECT age, COUNT(*) FROM users GROUP BY age;

• 原理：DISTINCT 和 GROUP BY 操作需要去重或聚合数据。这些操作不能直接利用索引来返回唯一结果，通常会导致数据库扫描整个表（即全表扫描），尤其是在没有合适索引的情况下。
• 解决方案：使用合适的索引（例如 GROUP BY 列上创建索引），或者将查询分解成多个步骤。

  1 2 3 4 5 6 7 8 9 10 11 12 13

  --创建索引 CREATE INDEX idx_users_age ON users(age); SELECT age, COUNT(*) FROM users GROUP BY age;   --子查询获取去重结果集再查询 SELECT age, COUNT(*) FROM users WHERE age IN (     SELECT DISTINCT age     FROM users     WHERE age IS NOT NULL ) GROUP BY age;

8. JOIN 查询中没有适当的索引

• 例子：

  1	SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

• 原理：如果连接条件没有索引，JOIN 查询可能会导致全表扫描。
• 解决方案：为连接字段创建索引，确保连接操作高效执行。

  1 2	CREATE INDEX idx_user_id ON orders(user_id); CREATE INDEX idx_user_id_users ON users(id);

• 使用合适的连接类型：在某些情况下，使用 INNER JOIN、LEFT JOIN 或其他连接类型可以影响查询性能，选择最合适的连接方式可以帮助优化性能。

9. 排序（ORDER BY）与索引不匹配

• 例子：

  1	SELECT * FROM users ORDER BY name DESC,age ASC;

• 原理：如果索引的顺序与查询的排序要求不匹配，可能无法利用索引。
• 解决方案：确保查询的排序方式与索引的顺序一致，使用复合索引支持多种排序需求。

  1 2 3

  -- 创建复合索引 CREATE INDEX idx_name_age ON users(name DESC, age ASC); SELECT * FROM users ORDER BY name DESC, age ASC;

10. 表连接顺序不当

• 例子：

  1	SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.order_date > '2024-01-01';

• 原理：连接顺序不当可能导致某些表的索引无法使用，从而降低查询性能。
• 解决方案：根据数据量和索引设计优化 JOIN 顺序。

  1 2 3 4 5 6 7 8 9 10

  -- 使用子查询（筛选大表后再去连接） SELECT * FROM (SELECT * FROM orders WHERE order_date > '2024-01-01') o JOIN users u ON u.id = o.user_id; -- 小表驱动大表（如果users表有100条，orders有20万数据） -- 使用 STRAIGHT_JOIN 强制左表为驱动表 SELECT * FROM users u STRAIGHT_JOIN orders o ON u.id = o.user_id WHERE o.order_date > '2024-01-01';

11. 启用 NO_INDEX 或 FORCE INDEX 提示时的索引失效

• 例子：

  1	SELECT * FROM users FORCE INDEX (idx_name) WHERE age = 30;

• 原理：强制索引或禁止索引可能导致查询优化器无法选择最优的执行计划。
• 解决方案：避免使用 FORCE INDEX 或 NO_INDEX，让数据库自动选择最优索引。

总结

在 SQL 查询优化中，合适的索引设计和查询结构调整是提高性能的关键。通过以下措施可以避免常见的性能瓶颈：

• 使用适当的索引来加速 DISTINCT、GROUP BY、JOIN 和 ORDER BY 操作。
• 优化连接顺序，确保合理使用索引。
• 避免强制使用或禁用索引，允许查询优化器自动选择最优执行计划。