MySQL进阶之路索引失效的11种情况详析

前言

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

图示

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

• 例子：

  SELECT * FROM users WHERE age != 30; 
  

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

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

2. 使用 OR 连接多个条件

• 例子：

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

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

  --创建联合索引
  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. 对索引字段进行计算操作

• 例子：

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

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

  --范围查询
  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. 对索引字段进行类型转换

• 例子：

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

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

5. LIKE 头部模糊查询

• 例子：

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

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

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

6. NULL 值的查询

• 例子：

  SELECT * FROM users WHERE age IS NULL; 
  

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

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

7. DISTINCT 或 GROUP BY 操作

• 例子：

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

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

  --创建索引
  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 查询中没有适当的索引

• 例子：

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

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

  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）与索引不匹配

• 例子：

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

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

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

10. 表连接顺序不当

• 例子：

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

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

  -- 使用子查询（筛选大表后再去连接）
  SELECT * FROM 
  (SELECT js* 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 提示时的索引失效

• 例子：

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

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

总结

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

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

到此这篇关于MySQL进阶之路索引失效的11种情况的文章就介绍到这了,更多相关MySQL索引失效情况内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)！