MySQL索引失效的几种常见场景

MySQL 是最流行的关系型数据库之一，广泛应用于各种互联网应用中。索引作为 MySQL 性能优化的重要手段，其有效性直接影响查询效率。然而，在实际使用中，我们常常会遇到索引失效的情况。

MySQL 索引失效的背景和影响
什么是索引？
索引在数据库中的作用类似于书籍的目录，通过索引可以快速定位数据，避免全表扫描。常见的包括单列索引、复合索引和全文索引等。

索引失效的影响
索引失效是指在执行查询时，数据库未能使用预期的索引，导致查询性能下降。理解索引失效的原因，有助于我们优化查询性能，提升数据库的响应速度。

MySQL 索引失效的常见场景
场景一：查询条件中使用函数或表达式
当在查询条件中对列使用函数或表达式时，MySQL 无法使用索引。示例代码

-- 在create_date字段上创建索引
CREATE INDEX idx_create_date ON users(create_date);

-- 索引失效的查询
SELECT * FROM users WHERE YEAR(create_date) = 2023;

解释与解决方法
在上述查询中，YEAR(create_date)会导致索引失效，因为索引无法用于函数操作。我们可以改写查询，避免使用函数：

-- 改写后的查询，避免使用函数
SELECT * FROM users WHERE create_date BETWEEN '2023-01-01' AND '2023-12-31';
场景二：隐式类型转换
隐式类型转换发生在查询条件的列类型与参数类型不匹配时，导致索引失效。示例代码

-- 在phone_number字段上创建索引
CREATE INDEX idx_phone_number ON users(phone_number);

-- 索引失效的查询
SELECT * FROM users WHERE phone_number = 1234567890;

解释与解决方法
如果 phone_number 列是字符类型，而参数 1234567890 是数字类型，MySQL 会进行类型转换，从而导致索引失效。正确的做法是确保类型一致：

-- 改写后的查询，确保查询类型一致
SELECT * FROM users WHERE phone_number = '1234567890';
场景三：使用 OR 语句
当查询条件中包含 OR 语句且 OR 两边的列没有同时包含索引时，MySQL 会选择全表扫描，而不是使用索引。示例代码

-- 在username和email字段上分别创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

-- 可能导致索引失效的查询
SELECT * FROM users WHERE username = 'john' OR email = 'john@example.com';

解释与解决方法
如果 username 和 email 都有索引，MySQL 可以使用索引；否则会导致索引失效。为了解决这个问题，可以将查询拆分：

-- 改写后的查询，拆分成两个查询用UNION连接
SELECT * FROM users WHERE username = 'john'
UNION
SELECT * FROM users WHERE email = 'john@example.com';

场景四：负向条件查询
使用负向条件（如 NOT IN, NOT LIKE, != 等）会导致索引失效。示例代码

-- 在username字段上创建索引
CREATE INDEX idx_username ON users(username);

-- 索引失效的查询
SELECT * FROM users WHERE username != 'john';

解释与解决方法
这类查询会导致全表扫描，建议使用其他替代方案。例如，可以通过子查询来避免索引失效：

-- 使用子查询以避免索引失效
SELECT * FROM users WHERE username NOT IN (SELECT username FROM users WHERE username = 'john');

场景五：字符串前导通配符
在使用 LIKE 语句时，如果通配符 % 出现在字符串的开头，MySQL 无法使用索引。示例代码

-- 在username字段上创建索引
CREATE INDEX idx_username ON users(username);

-- 索引失效的查询
SELECT * FROM users WHERE username LIKE '%john';
解释与解决方法
建议避免在开头使用 %，以便索引生效：

-- 改写后的查询，避免使用开头的通配符
SELECT * FROM users WHERE username LIKE 'john%';

场景六：多列索引未使用最左前缀
对于多列复合索引，如果查询未使用最左前缀，会导致索引失效。示例代码

-- 创建联合索引
CREATE INDEX idx_name_age ON users(first_name, last_name);

-- 索引失效的查询
SELECT * FROM users WHERE last_name = 'Smith';

解释与解决方法
正确的使用方式应包括最左前缀：

-- 改写后的查询，包含最左前缀
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Smith';

结语
了解 MySQL 索引失效的场景及其原因，有助于我们在设计数据库结构和编写查询语句时，避免性能问题。通过本文的讲解，相信读者对 MySQL 索引失效的常见情况有了更清晰的认识，并能够在实际应用中加以避免。