【避坑】让 MySQL 索引 “躺平” 的 10 种情况，开发者必看的性能优化红线-CSDN博客

本文链接：https://blog.csdn.net/weixin_46669100/article/details/149600131

MySQL索引失效场景

1. 引言

索引的作用：加速数据检索，减少全表扫描。
索引失效的后果：导致查询性能下降，增加服务器负载。
文章目标：列出常见失效场景，提供优化建议。

2. 常见索引失效场景及SQL代码示例

场景1: 在索引列上使用函数或表达式

解释: 索引基于原始列值构建，如果查询中应用函数（如日期函数、数学运算），MySQL无法直接使用索引。

SQL代码示例:

-- 失效示例: 使用YEAR函数处理索引列date_column
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- 优化建议: 避免使用函数，改用范围查询
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

场景2: LIKE操作符以通配符开头

解释: 索引按前缀匹配，如果LIKE以%开头（如%Smith），MySQL无法利用索引进行高效搜索。

SQL代码示例:

-- 失效示例: 通配符开头导致索引失效
SELECT * FROM users WHERE name LIKE '%Smith';

-- 优化建议: 尽量使用后缀通配符或全文本索引
SELECT * FROM users WHERE name LIKE 'Smith%';

场景3: 数据类型不匹配

解释: 索引列与查询条件的数据类型不一致时（如索引为整数，查询用字符串比较），MySQL可能忽略索引。

SQL代码示例:

-- 失效示例: 索引列id为INT，但查询用字符串'100'
SELECT * FROM products WHERE id = '100';

-- 优化建议: 确保数据类型一致
SELECT * FROM products WHERE id = 100;

场景4: 使用OR条件连接多个列

解释: OR条件涉及多个索引列时，MySQL可能无法合并索引扫描，转而使用全表扫描。

SQL代码示例:

-- 失效示例: OR条件连接两个列
SELECT * FROM employees WHERE department = 'IT' OR salary > 5000;

-- 优化建议: 改用UNION或重构查询
SELECT * FROM employees WHERE department = 'IT'
UNION
SELECT * FROM employees WHERE salary > 5000;

场景5: 索引列参与计算

解释: 在WHERE子句中对索引列进行数学运算（如乘法），MySQL无法直接匹配索引值。

SQL代码示例:

-- 失效示例: 索引列price参与计算
SELECT * FROM items WHERE price * 1.1 > 100;

-- 优化建议: 将计算移到条件右侧
SELECT * FROM items WHERE price > 100 / 1.1;

场景6: 使用NOT操作符

解释: NOT条件（如NOT status = 'active'）通常无法高效利用索引，因为索引不适合反向查询。

SQL代码示例:

-- 失效示例: NOT操作符导致索引忽略
SELECT * FROM accounts WHERE NOT status = 'active';

-- 优化建议: 改用正向条件
SELECT * FROM accounts WHERE status = 'inactive';

场景7: 复合索引未使用前缀列

解释: 复合索引（如(col1, col2)）要求查询条件从第一列开始，否则索引可能部分或完全失效。

SQL代码示例:

-- 失效示例: 索引为(name, age)，但查询跳过name
SELECT * FROM customers WHERE age > 30;

-- 优化建议: 包含前缀列
SELECT * FROM customers WHERE name = 'John' AND age > 30;

场景8: 表数据量小或优化器决策

解释: 当表行数少时，优化器可能选择全表扫描而非索引扫描；统计信息过时也会影响决策。

SQL代码示例:

-- 失效示例: 小表查询（行数<1000）
SELECT * FROM small_table WHERE category = 'A';

-- 优化建议: 定期更新统计信息或强制使用索引
ANALYZE TABLE small_table;
SELECT * FROM small_table FORCE INDEX (idx_category) WHERE category = 'A';