MySQL进阶之路索引失效的11种情况详析
这篇文章主要介绍了MySQL查询优化中的11种常见情况,包括索引的使用和优化策略,通过这些策略,开发者可以显著提升查询性能,需要的朋友可以参考下
目录
- 前言
- 图示
- 1. 使用不等式操作符（!=, <, >）
- 2. 使用 OR 连接多个条件
- 3. 对索引字段进行计算操作
- 4. 对索引字段进行类型转换
- 5. LIKE 头部模糊查询
- 6. NULL 值的查询
- 7. DISTINCT 或 GROUP BY 操作
- 8. JOIN 查询中没有适当的索引
- 9. 排序（ORDER BY）与索引不匹配
- 10. 表连接顺序不当
- 11. 启用 NO_INDEX 或 FORCE INDEX 提示时的索引失效
- 总结
前言
在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 WHERE 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 ASC;
10. 表连接顺序不当
- 例子：
SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.order_date > '2024-01-01';
- 原理：连接顺序不当可能导致某些表的索引无法使用，从而降低查询性能。
- 解决方案：根据数据量和索引设计优化
JOIN顺序。-- 使用子查询（筛选大表后再去连接） 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 提示时的索引失效
- 例子：
SELECT * FROM users FORCE INDEX (idx_name) WHERE age = 30;
- 原理：强制索引或禁止索引可能导致查询优化器无法选择最优的执行计划。
- 解决方案：避免使用
FORCE INDEX或NO_INDEX，让数据库自动选择最优索引。
总结
在 SQL 查询优化中，合适的索引设计和查询结构调整是提高性能的关键。通过以下措施可以避免常见的性能瓶颈：
- 使用适当的索引来加速
DISTINCT、GROUP BY、JOIN和ORDER BY操作。 - 优化连接顺序，确保合理使用索引。
- 避免强制使用或禁用索引，允许查询优化器自动选择最优执行计划。
到此这篇关于MySQL进阶之路索引失效的11种情况的文章就介绍到这了,更多相关MySQL索引失效情况内容请搜索骃骐网以前的文章或继续浏览下面的相关文章希望大家以后多多支持骃骐网！
您可能感兴趣的文章:
- Mysql索引会失效的几种情况分析
- mysql索引失效的五种情况分析
- MySQL中索引失效的常见场景与规避方法
- MySQL索引失效的几种情况汇总
- MySQL索引失效的几种情况详析
- 解决mysql模糊查询索引失效问题的几种方法
- MySQL索引失效原理
- mysql索引失效的常见九种原因图文详解
- MySQL导致索引失效的几种情况
- MySQL索引失效的几种情况图文详解