【MySQL】索引失效10大场景详解：如何避免索引失效提升查询性能

CyberShen
9 阅读9分钟

深入理解MySQL索引失效的常见场景，让数据库查询性能提升一个档次

在日常开发和数据库优化中，索引是提高查询性能最有效的手段之一。然而，很多时候我们为表添加了索引，却发现查询性能并没有提升，这很可能是索引失效导致的。本文将详细分析MySQL中索引失效的各种场景，帮助大家避免常见的陷阱。

一、索引基本原理回顾

在深入了解索引失效场景前，我们先简单回顾一下MySQL索引的基本原理。MySQL最常用的索引类型是B+树索引，这种索引结构能够高效支持等值查询和范围查询。 索引类似于书籍的目录，可以帮我们快速定位到需要的数据位置。但就像目录只有在按照特定方式查找时才有效一样，索引也需要在合适的查询条件下才能发挥作用。

二、索引失效的常见场景

1. 违反最左前缀匹配原则

问题描述：在使用复合索引（多列索引）时，如果查询条件没有从索引的最左列开始，索引将无法被使用。

-- 创建复合索引
CREATE INDEX idx_country_city ON users(country, city);

-- 有效：使用了索引的最左列
SELECT * FROM users WHERE country = 'China';

-- 失效：跳过了最左列country，直接使用city
SELECT * FROM users WHERE city = 'Beijing';

原理解析：复合索引在B+树中是按照索引定义的列顺序排序的。想象一下电话簿先按姓氏排序，同姓氏下再按名字排序。如果只知道名字不知道姓氏，就无法利用电话簿的有序性快速查找。 解决方案：设计复合索引时，将查询中最频繁使用的列放在最左边。对于上面的失效查询，可以单独为city字段创建索引，或者调整查询条件包含country字段。

2. 在索引列上使用函数或表达式

问题描述：在WHERE子句中对索引列使用函数或表达式，会导致索引失效。

-- 创建索引
CREATE INDEX idx_create_time ON orders(create_time);

-- 失效：在索引列上使用了函数
SELECT * FROM orders WHERE YEAR(create_time) = 2023;
SELECT * FROM orders WHERE UPPER(username) = 'JOHN';

原理解析：索引存储的是列的原始值，而不是函数计算后的值。当对索引列应用函数时，MySQL需要对每一行数据都计算函数结果，然后再比较，导致无法使用索引。 解决方案：将函数应用于条件值而不是列：

-- 优化后：将函数应用于条件值
SELECT * FROM orders 
WHERE create_time >= '2023-01-01 00:00:00' 
  AND create_time < '2024-01-01 00:00:00';

3. 隐式类型转换

问题描述：当查询条件中的值类型与索引列定义的类型不匹配时，MySQL会进行隐式类型转换，导致索引失效。

-- 创建表和索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    phone VARCHAR(20),
    INDEX idx_phone (phone)
);

-- 失效：phone是VARCHAR类型，但条件值是数字
SELECT * FROM users WHERE phone = 13800138000;

原理解析：MySQL在执行查询时，需要将条件值转换为与索引列相同的类型，这相当于在索引列上应用了转换函数。 解决方案：确保条件值与索引列类型一致：

-- 正确：使用相同类型
SELECT * FROM users WHERE phone = '13800138000';

4. 使用LIKE操作符且以通配符开头

问题描述：使用LIKE进行模糊查询时，如果模式以通配符（%）开头，索引通常会失效。

-- 创建索引
CREATE INDEX idx_product_name ON products(product_name);

-- 失效：以通配符开头
SELECT * FROM products WHERE product_name LIKE '%phone%';
SELECT * FROM products WHERE product_name LIKE '%phone';

原理解析：B+树索引是按照索引列的完整值排序的。当使用前缀通配符时，MySQL无法利用索引的有序性来定位数据，只能进行全表扫描。 解决方案：

  1. 避免使用前缀通配符，改用后缀通配符：
-- 有效：使用后缀通配符
SELECT * FROM products WHERE product_name LIKE 'phone%';
  1. 对于必须使用前缀通配符的场景，考虑使用全文索引：
-- 创建全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_product_name(product_name);

-- 使用全文索引查询
SELECT * FROM products 
WHERE MATCH(product_name) AGAINST('phone' IN BOOLEAN MODE);

5. 使用OR操作符连接条件

问题描述：当使用OR连接多个条件，且这些条件并非都使用索引时，可能导致索引失效。

-- 创建单列索引
CREATE INDEX idx_name ON customers(name);

-- 失效：email列没有索引
SELECT * FROM customers 
WHERE name = 'John' OR email = 'john@example.com';

原理解析：MySQL在处理OR条件时，如果其中一个条件无法使用索引，优化器可能会选择全表扫描，因为这种成本可能低于分别使用索引再合并结果。 解决方案：

  1. 为OR连接的所有列创建索引
  2. 使用UNION替代OR：
-- 使用UNION优化
SELECT * FROM customers WHERE name = 'John'
UNION
SELECT * FROM customers WHERE email = 'john@example.com';

6. 对索引列进行运算

问题描述：在WHERE子句中对索引列进行算术运算会导致索引失效。

-- 创建索引
CREATE INDEX idx_price ON products(price);

-- 失效：对索引列进行运算
SELECT * FROM products WHERE price + 100 > 500;

原理解析：与函数使用类似，对索引列进行运算改变了列的原始值，使得MySQL无法直接使用索引。 解决方案：将运算应用于条件值，而不是列：

-- 优化后：将运算应用于条件值
SELECT * FROM products WHERE price > 500 - 100;

7. 使用否定操作符

问题描述：使用!=、<>、NOT IN、NOT LIKE等否定条件时，通常会导致索引失效。

-- 创建索引
CREATE INDEX idx_status ON orders(status);

-- 可能失效：使用否定条件
SELECT * FROM orders WHERE status != 'completed';
SELECT * FROM orders WHERE status NOT IN ('completed', 'shipped');

原理解析：否定条件通常需要查找大部分数据，MySQL优化器可能判断全表扫描比使用索引更高效。 解决方案：尽量使用肯定条件替代否定条件，或增加更多过滤条件缩小结果集。

8. 数据重复度过高（低选择性）

问题描述：当索引列的不同值很少（选择性低）时，MySQL可能选择不使用索引。

-- 创建索引
CREATE INDEX idx_gender ON users(gender);

-- 可能失效：gender只有少数几个不同值
SELECT * FROM users WHERE gender = 'male';

原理解析：对于低选择性的列，使用索引可能需要访问大量的索引页和数据页，效率可能不如全表扫描。 解决方案：

  1. 增加更多的过滤条件，减小结果集：
SELECT * FROM users 
WHERE gender = 'male' AND age BETWEEN 25 AND 35;
  1. 使用覆盖索引避免回表：
-- 创建覆盖索引
CREATE INDEX idx_gender_age_name ON users(gender, age, name);

-- 查询仅需要索引中包含的列
SELECT gender, age, name FROM users WHERE gender = 'male';

9. 查询返回大量数据

问题描述：当查询条件返回的结果集占表总数据量的比例较大时（通常超过20%-30%），MySQL优化器可能会选择全表扫描。 原理解析：使用索引查询涉及索引查找和回表操作。当结果集较大时，这种"随机IO"的成本可能高于顺序读取全表的成本。 解决方案：优化查询，添加更多过滤条件减小结果集，或使用分页查询。

10. ORDER BY使用不当

问题描述：当ORDER BY的列与WHERE条件中使用的索引列不一致时，可能导致额外的排序操作。

-- 创建索引
CREATE INDEX idx_name ON users(name);

-- 可能产生filesort操作
SELECT * FROM users WHERE name = 'John' ORDER BY create_time;

原理解析：B+树索引本身是有序的，但如果排序或分组的列与索引列不一致，MySQL需要在检索出结果后再进行排序（filesort）。 解决方案：创建包含排序/分组列的复合索引：

-- 创建包含排序列的复合索引
CREATE INDEX idx_name_create_time ON users(name, create_time);

-- 现在可以使用索引排序
SELECT * FROM users WHERE name = 'John' ORDER BY create_time;

三、诊断索引失效的工具和方法

1. 使用EXPLAIN分析查询计划

EXPLAIN是诊断索引使用情况的主要工具：

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 AND status = 'completed';

重点关注以下字段：

  • type：访问类型，从好到差依次是：system > const > eq_ref > ref > range > index > ALL
  • key：实际使用的索引，如果为NULL则表示未使用索引
  • rows：预计扫描的行数，数值越小越好
  • Extra：额外信息，如"Using filesort"表示需要额外排序

2. 使用慢查询日志

启用慢查询日志可以帮助发现性能差的SQL语句：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 设置慢查询阈值为1秒

3. 使用性能模式（Performance Schema）

MySQL 5.6及以上版本提供了强大的性能监控工具：

-- 查看查询性能统计
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

四、索引设计与使用的最佳实践

  1. 合理设计复合索引：遵循最左前缀原则，将高选择性的列放在前面。
  2. 避免过度索引：索引会占用空间，并在数据修改时带来维护开销。
  3. 考虑使用覆盖索引：减少回表操作，提高查询性能。
  4. 定期更新统计信息：使用ANALYZE TABLE命令更新统计信息，帮助优化器做出更好的决策。
  5. 使用连接查询优化：确保连接字段的数据类型和字符集一致。

五、总结

MySQL索引失效通常是由查询语句编写不当或索引设计不合理导致的。了解这些常见的索引失效场景，并在开发和优化过程中避免这些问题，可以显著提高数据库查询性能。 最关键的是养成使用EXPLAIN分析查询计划的习惯，确保索引被正确使用。同时，合理的索引设计和持续的SQL优化是保证数据库性能的重要手段。 希望本文能帮助大家更好地理解和使用MySQL索引，避免在生产环境中遇到性能问题。如果你有其他关于MySQL索引的问题或经验，欢迎在评论区分享讨论。

本文基于MySQL 8.0版本编写，不同版本的MySQL在优化器行为上可能有细微差异。

avatar
文章
阅读
粉丝