MySQL索引是提升数据库查询性能的关键，但实际开发中常常出现明明创建了索引却未被使用的情况。索引失效会导致查询性能大幅下降，尤其在数据量大的情况下，全表扫描可能造成严重性能问题。本文将分析MySQL索引失效的常见场景，并提供实用的排查方法，适用于数据库优化、性能调优和SQL编写等场景。

MySQL索引工作机制

MySQL索引是一种用于快速查询数据的数据库结构，常见类型包括B+树索引、哈希索引、全文索引等。B+树索引是最常用的类型，它通过构建多路平衡树结构，使得数据查询的时间复杂度从O(n)降低到O(log n)。

MySQL查询优化器会基于统计信息、表大小、索引选择性等因素决定是否使用索引。当优化器认为全表扫描比使用索引更高效时，就会选择不使用索引。

索引失效的常见场景

1. 使用函数或表达式

在查询条件中对列使用函数或表达式会导致索引失效：

-- 索引失效的情况
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

2. 隐式类型转换

当查询条件中列的类型与常量类型不匹配时，可能导致类型转换和索引失效：

-- 假设id列是整数类型
SELECT * FROM users WHERE id = '123'; -- 字符串与整数比较

3. 使用LIKE以通配符开头

使用LIKE查询时，如果通配符（%）出现在字符串开头，索引将失效：

-- 索引失效
SELECT * FROM products WHERE name LIKE '%apple';
-- 索引可用
SELECT * FROM products WHERE name LIKE 'apple%';

4. OR连接条件

当OR条件两边的列都有索引时，可能使用索引；但如果只有部分列有索引，MySQL可能选择全表扫描：

-- 假设name有索引，age没有
SELECT * FROM users WHERE name = 'john' OR age > 30;

5. 使用!=或<>操作符

对于某些存储引擎，使用!=或<>操作符可能导致索引失效：

-- 可能导致索引失效
SELECT * FROM users WHERE status != 'active';

6. 使用NOT IN或NOT EXISTS

这些操作通常会导致索引失效：

-- 索引可能失效
SELECT * FROM orders WHERE customer_id NOT IN (1, 2, 3);

7. 使用IS NULL或IS NOT NULL

对于允许NULL的列，使用IS NULL或IS NOT NULL可能影响索引使用：

-- 可能导致索引失效
SELECT * FROM users WHERE last_login IS NULL;

8. 使用LEADING通配符

在使用全文索引时，LEADING通配符会导致索引失效：

-- 全文索引失效
SELECT * FROM articles WHERE MATCH(content) AGAINST('%database%' IN BOOLEAN MODE);

排查方法与工具

1. 使用EXPLAIN分析查询计划

EXPLAIN SELECT * FROM users WHERE name = 'john';

关注以下关键列：

  • type: 访问类型，如ALL(全表扫描)、index(索引扫描)、range(范围扫描)等
  • key: 实际使用的索引
  • rows: 预估扫描的行数
  • Extra: 额外信息，如Using where、Using filesort等

2. 检查索引使用统计

SHOW INDEX FROM users;

查看索引是否被正确创建，以及索引的使用情况。

3. 使用慢查询日志

启用慢查询日志，分析哪些查询没有使用索引：

-- 临时开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 设置阈值(秒)

4. 使用Performance Schema

MySQL 5.7+可以使用Performance Schema监控索引使用情况：

-- 查看索引使用统计
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database';

示例：索引失效排查与优化

假设有一个用户表，包含id(主键)、name(索引)、email(索引)和age字段：

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    INDEX idx_name (name),
    INDEX idx_email (email)
);

-- 插入测试数据
INSERT INTO users VALUES
(1, 'John', 'john@example.com', 25),
(2, 'Alice', 'alice@example.com', 30),
(3, 'Bob', 'bob@example.com', 35),
(4, 'John', 'john.doe@example.com', 28);

-- 索引失效的查询
EXPLAIN SELECT * FROM users WHERE UPPER(name) = 'JOHN';

-- 优化方案1：避免函数
EXPLAIN SELECT * FROM users WHERE name = 'john';

-- 优化方案2：使用覆盖索引
EXPLAIN SELECT name, email FROM users WHERE name = 'john';

常见问题与排错思路

  1. 问题：明明创建了索引，但查询仍然很慢

排查：使用EXPLAIN分析查询计划，确认是否使用了索引；检查是否有多个索引导致优化器选择困难

  1. 问题：LIKE查询使用索引失效

排查：确认通配符位置，避免在开头使用%；考虑使用全文索引替代

  1. 问题：OR条件导致索引失效

排查：确保OR两边的列都有索引；考虑使用UNION ALL替代OR

  1. 问题：多列索引部分失效

排查：检查查询条件是否遵循最左前缀原则；考虑调整索引顺序

  1. 问题：数据量小，索引反而导致性能下降

排查：对于小表，全表扫描可能更快；考虑在数据量增长后添加索引

总结

MySQL索引失效是常见但容易被忽视的性能问题。通过理解索引工作机制，掌握常见失效场景，并熟练使用EXPLAIN等分析工具，可以有效识别和解决索引失效问题。在实际开发中，应遵循最佳实践，如避免在列上使用函数、确保查询条件与索引匹配、合理设计索引等，以充分发挥索引的性能优势。定期检查索引使用情况，优化查询语句，是保持数据库高性能的重要手段。

主楼