mysql如何排查索引失效-mysql教程-PHP中文网

mysql如何排查索引失效

P粉602998670

发布： 2025-09-19 12:10:01

原创

288人浏览过

索引失效指MySQL未使用预期索引，导致查询效率下降甚至全表扫描。通过EXPLAIN分析执行计划，观察type=ALL或key为NULL可判断索引未命中。常见原因包括：WHERE子句中数据类型不匹配、索引列使用函数或表达式、OR连接无索引条件、NOT负向查询、范围查询过大、联合索引不满足最左前缀原则。此外，统计信息不准、索引选择性低、MySQL版本或配置问题也会导致失效。避免方式包括遵循最左前缀原则、避免在索引列上使用函数、确保数据类型一致。模糊查询以%开头、低区分度字段如性别建索引效果差。可使用FORCE INDEX强制走索引，但不推荐作为常规手段。当频繁增删改导致索引碎片、数据量大或查询变慢时，应考虑重建索引，通过ALTER TABLE或OPTIMIZE TABLE整理碎片，建议在低峰期执行。

mysql如何排查索引失效

索引失效，简单来说，就是MySQL在执行查询时，本应该用上的索引没用上，导致查询效率直线下降，甚至全表扫描。

解决方案

排查MySQL索引失效，需要从以下几个方面入手：

```
EXPLAIN
```
登录后复制
你的查询：这是最基础也是最重要的步骤。在你的SQL查询语句前加上
```
EXPLAIN
```
登录后复制
，执行后MySQL会返回查询的执行计划。关注
```
type
```
登录后复制
列，
```
type=ALL
```
登录后复制
表示全表扫描，基本可以确定索引失效了。
```
possible_keys
```
登录后复制
列会告诉你可能用到的索引，
```
key
```
登录后复制
列会告诉你实际用到的索引，如果
```
key
```
登录后复制
为
```
NULL
```
登录后复制
，那也说明索引失效了。
```
key_len
```
登录后复制
列显示了索引中被使用字节的数量，通过这个值可以计算具体使用了索引中的哪些列。
```
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 25;
```
登录后复制
检查WHERE子句：WHERE子句是索引发挥作用的关键区域。
- 数据类型不匹配：如果列类型是字符串，而你在WHERE子句中使用了数字，MySQL可能会进行隐式类型转换，导致索引失效。例如，
```
WHERE phone = 1234567890
```
  登录后复制
  ，如果
```
phone
```
  登录后复制
  是VARCHAR类型，就可能导致索引失效。
- 使用了函数或表达式：在索引列上使用函数（如
```
UPPER()
```
  登录后复制
  、
```
LOWER()
```
  登录后复制
  、
```
DATE()
```
  登录后复制
  ）或表达式，会导致索引失效。例如，
```
WHERE UPPER(name) = 'JOHN'
```
  登录后复制
  。
- OR条件：如果OR连接的多个条件中，有一个条件没有索引，那么整个查询都可能导致索引失效。尽量避免在WHERE子句中使用OR。
- NOT条件：
```
NOT IN
```
  登录后复制
  、
```
NOT EXISTS
```
  登录后复制
  、
```
!=
```
  登录后复制
  、
```
<>
```
  登录后复制
  等负向查询，通常会导致索引失效。
- 范围查询：范围查询（如
```
>
```
  登录后复制
  、
```
<
```
  登录后复制
  、
```
>=
```
  登录后复制
  、
```
<=
```
  登录后复制
  、
```
BETWEEN
```
  登录后复制
  ）在某些情况下可能导致索引失效，特别是当范围过大时。MySQL会评估使用索引的成本，如果全表扫描更快，它会选择全表扫描。
- 最左前缀原则：对于联合索引，必须从索引的最左边的列开始使用，否则索引会失效。例如，如果有一个联合索引
```
INDEX(a, b, c)
```
  登录后复制
  ，那么
```
WHERE b = xxx AND c = xxx
```
  登录后复制
  是无法使用该索引的。
索引统计信息：MySQL的查询优化器依赖于索引统计信息来做出最佳的执行计划。如果统计信息不准确，可能会导致优化器错误地选择不使用索引。可以使用
```
ANALYZE TABLE
```
登录后复制
命令来更新表的统计信息。
```
ANALYZE TABLE users;
```
登录后复制
索引选择性：索引的选择性是指索引列中不同值的数量与表总行数的比率。选择性越高，索引的效果越好。如果索引的选择性很低，MySQL可能认为使用索引的成本高于全表扫描，从而放弃使用索引。
MySQL版本和配置：不同版本的MySQL在索引优化方面可能有所不同。同时，一些配置参数（如
```
optimizer_switch
```
登录后复制
）也会影响索引的使用。

索引失效的常见场景有哪些？如何避免？

索引失效的场景很多，但归根结底都是因为MySQL的优化器认为使用索引的成本高于全表扫描。

纳米搜索

纳米搜索：360推出的新一代AI搜索引擎

查看详情

模糊查询以
%
登录后复制
开头：
```
WHERE name LIKE '%John'
```
登录后复制
，这种情况下索引无法使用。尽量避免使用前缀模糊查询，或者考虑使用全文索引。
联合索引不满足最左前缀原则：确保查询条件包含联合索引的最左边的列。
WHERE子句中使用了计算或函数：避免在索引列上使用计算或函数。可以将计算或函数应用在WHERE子句的右侧。
数据量小：当表的数据量很小时，MySQL可能认为全表扫描更快。
区分度低的字段：例如，性别字段，只有男女两种值，建立索引的意义不大。

如何强制MySQL使用索引？

虽然通常情况下MySQL的优化器会做出正确的选择，但在某些特殊情况下，我们可能需要强制MySQL使用特定的索引。可以使用

FORCE INDEX

登录后复制

提示。

SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'John';

登录后复制

但是，强制使用索引通常不是一个好的做法。它可能会掩盖更深层次的问题，例如索引设计不合理或统计信息不准确。建议先排查问题，优化SQL语句和索引设计，而不是简单地强制使用索引。

什么时候应该重建索引？

索引在长期使用过程中可能会产生碎片，影响查询性能。重建索引可以整理索引碎片，提高查询效率。以下情况可以考虑重建索引：

频繁的INSERT、UPDATE、DELETE操作：这些操作会导致索引碎片增多。
表数据量很大：大表的索引更容易产生碎片。
查询性能下降：如果发现查询性能明显下降，可以考虑重建索引。

重建索引可以使用

ALTER TABLE

登录后复制

命令：

ALTER TABLE users DROP INDEX idx_name, ADD INDEX idx_name (name);

登录后复制

或者使用

OPTIMIZE TABLE

登录后复制

命令，它会重建表并更新统计信息：

OPTIMIZE TABLE users;

登录后复制

注意：重建索引是一个耗时操作，特别是对于大表，应该在业务低峰期进行。

以上就是mysql如何排查索引失效的详细内容，更多请关注php中文网其它相关文章！

大家都在看：

mysql中如何排查客户端连接异常如何在mysql中配置服务自动启动如何在mysql中使用事务处理日志数据如何在mysql中定期审计用户权限如何在mysql中优化安装参数