MySQL索引失效的12种场景及解决方案

MySQL索引是提升数据库性能的关键因素，正确使用索引可以将查询效率提高几十倍甚至上百倍。然而，在实际开发中，即使创建了索引，却经常出现索引不生效的情况，

本文将分享MySQL索引失效的12种典型场景，通过示例帮助开发者理解索引失效的原理，并掌握相应的优化方法。

一、在索引列上使用函数或表达式

这是最常见的索引失效场景之一，当我们在WHERE子句中对索引列使用函数时，MySQL无法利用索引进行查询优化。

问题示例

-- 创建索引
CREATE INDEX idx_create_time ON orders(create_time);-- 以下查询无法使用索引
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

原理解释

当对索引列应用函数时，MySQL必须对表中的每一行都应用该函数，然后再与条件比较，这就导致了全表扫描。索引的B+树结构是基于列的原始值构建的，而不是函数计算后的值。

解决方案

将函数应用于条件值而不是列：

-- 优化后的查询，可以使用索引
SELECT * FROM orders 
WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2024-01-01 00:00:00';

二、使用类型隐式转换

当条件中的值与索引列的类型不匹配时，MySQL会进行隐式类型转换，导致索引失效。

问题示例

-- 创建表和索引
CREATE TABLE users (id INT PRIMARY KEY,phone VARCHAR(20),INDEX idx_phone (phone)
);-- 以下查询可能无法使用索引
SELECT * FROM users WHERE phone = 13800138000;

原理解释

在上面的例子中，phone是VARCHAR类型，而条件值13800138000是整数。MySQL会将字符串类型的phone隐式转换为数字类型进行比较，导致无法使用索引。

解决方案

确保条件值与索引列类型一致：

-- 正确的查询方式，可以使用索引
SELECT * FROM users WHERE phone = '13800138000';

三、使用不等于或不包含操作符

使用!=、<>、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的索引是为了快速查找满足条件的记录，而否定条件通常意味着要查找的范围太大。MySQL优化器可能判断使用索引的代价大于全表扫描，因此选择不使用索引。

解决方案

尽量使用肯定条件替代否定条件：

-- 优化后的查询
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'cancelled');

如果必须使用否定条件，可以考虑重新设计索引或添加适当的统计信息帮助优化器做出更好的决策。

四、使用OR操作符连接不同的索引列

当使用OR连接多个条件，且这些条件分别在不同的索引上时，可能导致索引失效。

问题示例

-- 创建单列索引
CREATE INDEX idx_name ON customers(name);
CREATE INDEX idx_email ON customers(email);-- 以下查询可能无法充分利用索引
SELECT * FROM customers 
WHERE name = 'John' OR email = 'john@example.com';

原理解释

MySQL在处理OR条件时，需要分别获取满足每个条件的记录，然后合并结果。在某些情况下，优化器会认为这种操作的成本高于全表扫描，从而选择不使用索引。

解决方案

1. 使用UNION替代OR：

-- 使用UNION优化
SELECT * FROM customers WHERE name = 'John'
UNION
SELECT * FROM customers WHERE email = 'john@example.com';

2. 创建复合索引或使用索引合并：

-- 在MySQL 5.6及以上版本，可能会使用索引合并
-- 也可以创建覆盖索引
CREATE INDEX idx_name_email ON customers(name, email);

五、使用LIKE操作符且以通配符开头

当使用LIKE操作符进行模糊查询，且模式以通配符（%）开头时，索引通常会失效。

问题示例

-- 创建索引
CREATE INDEX idx_product_name ON products(product_name);-- 以下查询无法使用索引
SELECT * FROM products WHERE product_name LIKE '%phone%';

原理解释

B+树索引是按照索引列的值排序的，当使用前缀通配符（如’%phone’）时，MySQL无法利用索引的有序性来定位数据，只能进行全表扫描。

解决方案

1. 避免使用前缀通配符，改用后缀通配符：

-- 可以使用索引的查询
SELECT * FROM products WHERE product_name LIKE 'phone%';

2. 对于必须使用前缀通配符的场景，考虑使用全文索引：

-- 创建全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_product_name(product_name);-- 使用全文索引查询
SELECT * FROM products 
WHERE MATCH(product_name) AGAINST('phone' IN BOOLEAN MODE);

3. 考虑使用专门的搜索引擎，如Elasticsearch。

六、对索引列进行运算

在WHERE子句中对索引列进行算术运算同样会导致索引失效。

问题示例

-- 创建索引
CREATE INDEX idx_price ON products(price);-- 以下查询无法使用索引
SELECT * FROM products WHERE price + 100 > 500;

原理解释

与函数使用类似，当对索引列进行运算时，MySQL需要对表中的每一行数据都进行计算，然后再与条件值比较，导致无法利用索引。

解决方案

将运算应用于条件值，而不是列：

-- 优化后的查询，可以使用索引
SELECT * FROM products WHERE price > 500 - 100;

七、查询条件中的字段顺序与复合索引的顺序不一致

在使用复合索引（多列索引）时，如果查询条件中的字段顺序与索引创建时的顺序不一致，可能导致索引无法充分利用。

问题示例

-- 创建复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);-- 以下查询无法充分利用索引
SELECT * FROM users WHERE age = 25 AND city = 'New York' AND name = 'John';

原理解释

MySQL复合索引遵循"最左前缀"原则，即先按第一个索引列排序，值相同时再按第二个索引列排序，以此类推。当查询条件的顺序与索引列顺序不一致时，MySQL的查询优化器通常能够重新排序这些条件，但在某些复杂场景下可能无法最优化。

解决方案

在编写查询时，尽量保持条件顺序与索引列顺序一致：

-- 优化后的查询，更容易使用索引
SELECT * FROM users WHERE name = 'John' AND age = 25 AND city = 'New York';

另外，在设计复合索引时，应将选择性高（不重复值多）的列放在前面。

八、在WHERE子句中使用IS NULL或IS NOT NULL

在某些情况下，对索引列使用IS NULL或IS NOT NULL条件可能导致索引失效。

问题示例

-- 创建索引
CREATE INDEX idx_deleted_at ON users(deleted_at);-- 以下查询可能无法使用索引
SELECT * FROM users WHERE deleted_at IS NULL;

原理解释

MySQL对NULL值的处理比较特殊。在早期版本中，MySQL对含有NULL值的列索引效果不佳，尤其是在使用IS NULL或IS NOT NULL条件时。不过，在MySQL 5.6及以后的版本中，这种情况有所改善。

解决方案

1. 在设计表时，尽量避免使用NULL值，可以使用空字符串或默认值代替：

-- 创建表时使用NOT NULL约束和默认值
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(100) NOT NULL,deleted_at TIMESTAMP NULL DEFAULT NULL,status TINYINT NOT NULL DEFAULT 1
);

2. 如果必须查询NULL值，检查执行计划确保索引被正确使用：

EXPLAIN SELECT * FROM users WHERE deleted_at IS NULL;

九、查询的数据占表中数据的比例较大

当查询条件返回的结果集占表总数据量的比例较大时，MySQL优化器可能会选择不使用索引，而是直接全表扫描。

问题示例

-- 创建索引
CREATE INDEX idx_gender ON users(gender);-- 假设性别比例接近1:1，以下查询可能不使用索引
SELECT * FROM users WHERE gender = 'male';

原理解释

使用索引查询涉及两个步骤：先通过索引找到满足条件的记录ID，再通过这些ID获取完整记录（回表操作）。当结果集较大时，这种"随机IO"的成本可能高于顺序读取全表的成本，因此优化器会选择全表扫描。

解决方案

1. 增加更多的过滤条件，减小结果集：

-- 添加更多条件缩小结果集
SELECT * FROM users 
WHERE gender = 'male' AND age BETWEEN 25 AND 35;

2. 使用覆盖索引避免回表：

-- 创建覆盖索引
CREATE INDEX idx_gender_age_name ON users(gender, age, name);-- 查询仅需要索引中包含的列
SELECT gender, age, name FROM users WHERE gender = 'male';

十、索引字段的数据重复度过高

当索引列的基数（不同值的数量）很低时，例如状态字段只有几个不同的值，MySQL可能会认为使用索引效率低而选择全表扫描。

问题示例

-- 创建索引
CREATE INDEX idx_status ON orders(status);-- 假设status只有3个值：'pending', 'processing', 'completed'
-- 以下查询可能不使用索引
SELECT * FROM orders WHERE status = 'completed';

原理解释

索引的选择性是指不同索引值与表中记录总数的比值，选择性越高（接近1），索引效率越高。对于低选择性的列，使用索引可能需要访问大量的索引页和数据页，效率不如全表扫描。

解决方案

1. 将低选择性的列放在复合索引的后面：

-- 创建复合索引，将高选择性的user_id放在前面
CREATE INDEX idx_user_status ON orders(user_id, status);-- 查询同时使用user_id和status
SELECT * FROM orders 
WHERE user_id = 10001 AND status = 'completed';

2. 考虑使用索引下推（Index Condition Pushdown，ICP）特性（MySQL 5.6及以上版本支持）。

十一、使用不等值范围查询

当对索引列进行范围查询（如>、<、BETWEEN）时，会部分影响索引的使用效率，尤其是在复合索引中。

问题示例

-- 创建复合索引
CREATE INDEX idx_age_salary ON employees(age, salary);-- 以下查询只能使用索引的age部分，salary部分无法使用
SELECT * FROM employees WHERE age > 30 AND salary > 50000;

原理解释

在复合索引中，如果对前面的列使用了范围条件，那么后面的列就无法使用索引了。这是因为B+树索引在范围查询后，无法再保证后续列的有序性。

解决方案

1. 调整索引列顺序，将范围查询的列放在最后：

-- 调整索引顺序
CREATE INDEX idx_salary_age ON employees(salary, age);-- 如果条件中salary是等值查询，age是范围查询
SELECT * FROM employees WHERE salary = 50000 AND age > 30;

2. 对于复杂条件，考虑创建多个索引：

-- 为不同的查询模式创建不同的索引
CREATE INDEX idx_age ON employees(age);
CREATE INDEX idx_salary ON employees(salary);

十二、ORDER BY或GROUP BY子句的使用不当

当ORDER BY或GROUP BY的列与WHERE条件中使用的索引列不一致时，可能导致额外的排序操作，影响性能。

问题示例

-- 创建索引
CREATE INDEX idx_name ON users(name);-- 以下查询无法使用索引排序，会产生filesort
SELECT * FROM users WHERE name = 'John' ORDER BY create_time;

原理解释

B+树索引本身是有序的，如果排序或分组的列与索引列一致，MySQL可以直接利用索引的有序性。但如果不一致，MySQL需要在检索出结果后再进行排序（filesort），这是一个成本较高的操作。

解决方案

1. 创建包含排序/分组列的复合索引：

-- 创建包含排序列的复合索引
CREATE INDEX idx_name_create_time ON users(name, create_time);-- 现在可以使用索引排序
SELECT * FROM users WHERE name = 'John' ORDER BY create_time;

2. 如果排序方向不一致，考虑使用降序索引（MySQL 8.0+支持）：

-- 创建包含混合排序方向的索引
CREATE INDEX idx_name_time_score ON users(name ASC, create_time DESC, score ASC);-- 可以高效使用索引
SELECT * FROM users 
WHERE name = 'John' 
ORDER BY create_time DESC, score ASC;

如何诊断索引失效问题

发现并解决索引失效问题，需要掌握一些实用的诊断工具和方法：

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

配置并启用MySQL慢查询日志：

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

3. 使用MySQL性能模式(Performance Schema)

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

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

4. 使用SHOW PROFILE分析查询执行情况

SET profiling = 1;
SELECT * FROM users WHERE email LIKE '%@example.com';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

总结

索引优化是一个持续的过程，需要结合具体的业务场景和数据特点。通过了解这些索引失效的场景和原理，你可以更有针对性地设计索引策略，显著提升数据库性能。

没有一劳永逸的索引方案，随着数据量的增长和业务的变化，索引策略也需要不断调整和优化。

持续监控、分析和优化是保持高性能数据库的关键。