mysql索引失效的现象有哪些

发布时间：2022-01-05 16:05:37 作者：iii
来源：亿速云阅读：172

# MySQL索引失效的现象有哪些

## 引言

在MySQL数据库优化中，索引是提升查询性能的关键手段。然而，即使创建了合适的索引，某些情况下索引可能无法生效，导致查询性能急剧下降。本文将详细分析常见的MySQL索引失效现象，帮助开发者识别和避免这些问题。

---

## 1. 索引失效的常见现象

### 1.1 使用`!=`或`<>`操作符
当查询条件中使用`!=`或`<>`时，MySQL通常无法使用索引：
```sql
-- 索引失效示例
SELECT * FROM users WHERE age != 30;

原因：非等值查询需要扫描大部分数据，优化器会放弃使用索引。

1.2 对索引列使用函数或运算

对索引列进行函数调用、数学运算或类型转换会导致索引失效：

-- 索引失效示例
SELECT * FROM orders WHERE YEAR(create_time) = 2023;
SELECT * FROM products WHERE price + 10 > 100;

解决方案：

-- 优化为范围查询
SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

1.3 使用`OR`连接非索引列

当OR连接的列中有未建立索引的字段时，索引会失效：

-- 假设name有索引而email无索引
SELECT * FROM users WHERE name = 'John' OR email = 'john@example.com';

解决方案： - 为所有OR条件列创建索引 - 改用UNION ALL：

SELECT * FROM users WHERE name = 'John'
UNION ALL
SELECT * FROM users WHERE email = 'john@example.com';

1.4 模糊查询以通配符开头

LIKE查询以%开头时索引失效：

-- 索引失效
SELECT * FROM articles WHERE title LIKE '%数据库%';

解决方案： - 使用全文索引（FULLTEXT） - 考虑搜索引擎如Elasticsearch

1.5 数据类型隐式转换

当查询条件与索引列类型不匹配时会发生隐式转换：

-- 假设mobile是varchar类型
SELECT * FROM users WHERE mobile = 13800138000; -- 数字未加引号

解决方案：确保类型一致：

SELECT * FROM users WHERE mobile = '13800138000';

1.6 复合索引未遵循最左前缀原则

对于复合索引(a,b,c)，以下情况会失效：

-- 有效：使用最左列
SELECT * FROM table WHERE a = 1 AND b = 2;

-- 失效：跳过最左列
SELECT * FROM table WHERE b = 2 AND c = 3;

1.7 使用`IS NULL`或`IS NOT NULL`

-- 可能失效（取决于数据分布）
SELECT * FROM employees WHERE department_id IS NULL;

优化建议：为可为NULL的列设置默认值

1.8 查询范围条件后的列

当复合索引中某一列使用范围查询后，后续列的索引会失效：

-- 复合索引(a,b,c)
SELECT * FROM table WHERE a > 1 AND b = 2; -- 只有a能用索引

2. 如何诊断索引失效

2.1 使用`EXPLN`分析

通过EXPLN查看执行计划：

EXPLN SELECT * FROM users WHERE name LIKE '%张%';

关注： - type列：ALL表示全表扫描 - key列：显示实际使用的索引

2.2 开启慢查询日志

# my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

3. 避免索引失效的最佳实践

合理设计索引：
- 为高频查询条件创建索引
- 控制单表索引数量（一般不超过5-6个）
编写优化SQL：
- 避免SELECT *
- 使用覆盖索引（Covering Index）

定期维护：

ANALYZE TABLE users;
OPTIMIZE TABLE orders;

监控调整：
- 使用SHOW INDEX FROM table查看索引基数
- 关注Handler_read%状态变量

结语

索引失效是MySQL性能问题的常见原因。通过理解这些典型场景，结合EXPLN工具和慢查询分析，可以显著提升查询效率。建议在开发过程中建立索引使用规范，并定期进行SQL审计和优化。

提示：不同MySQL版本（如5.7 vs 8.0）的索引优化策略可能有所差异，建议根据实际环境进行测试验证。 “`

注：本文约1300字，涵盖主要索引失效场景及解决方案，采用Markdown格式便于技术文档的传播和编辑。可根据实际需要补充具体案例或性能对比数据。