mysql中某一列添加了索引，但是sql查询中未使用该索引，可能的原因有哪些？

Posted 2024-10-10 Updated 2024-10- 10

By 随风

12~15 min read

在 MySQL 中，为某一列添加了索引，但 SQL 查询没有使用该索引，可能有以下几个原因：

1. 查询优化器选择了全表扫描

MySQL 查询优化器有时可能会决定不使用索引，尤其是在认为全表扫描比使用索引更高效的情况下。优化器会根据数据的分布和查询的复杂性做出选择，可能的原因包括：

小表：当表中的数据量较少时，优化器可能认为全表扫描比使用索引更快，因为读取整个表的开销很小。
返回的数据量较大：如果查询的结果集占总数据的很大一部分，使用索引进行查询可能会导致多次磁盘I/O，而全表扫描反而更加高效。
索引选择性差：索引的选择性指的是查询条件能筛选出多少独立的值。对于低选择性的列（如性别、状态等具有少量不同值的列），索引并不能很好地提高性能，优化器可能会忽略该索引。

2. 查询条件不匹配索引

索引列必须出现在查询的 WHERE 子句中，并且应该使用与索引匹配的操作。常见的错误包括：

函数操作：如果在查询中对索引列进行了函数操作，索引无法被使用。例如：
```
SELECT * FROM users WHERE UPPER(username) = 'JOHN';
```
这里，username 列的索引不会被用到，因为对该列进行了 UPPER() 函数的转换。
隐式类型转换：如果查询中的条件值的类型与索引列的类型不匹配，MySQL 可能会进行隐式转换，从而导致索引失效。例如：
```
SELECT * FROM users WHERE id = '123'; -- id 是整数类型，但 '123' 是字符串
```
MySQL 会将 id 列从整数转换为字符串，从而导致索引无法被使用。

3. 查询未覆盖索引

索引的作用不仅仅是筛选，还能帮助快速返回查询所需的列。覆盖索引是指查询所需的所有列都可以从索引中直接获取，而不需要访问表中的实际数据行。如果查询请求的列不在索引中，MySQL 仍然需要访问实际的数据行，这可能会使得 MySQL 选择不使用该索引。

例如，假设为 email 列创建了索引，但查询请求了除 email 外的其他列：

SELECT id, name FROM users WHERE email = 'john@example.com';

虽然 email 上有索引，但查询还需要访问 id 和 name，因此索引可能不会被使用。

4. 查询中使用了 `OR` 条件

如果查询的 WHERE 子句中有多个条件，并且这些条件之间使用了 OR，而每个条件并不都包含索引列，MySQL 可能会选择忽略索引。例如：

SELECT * FROM users WHERE email = 'john@example.com' OR age = 30;

如果 email 列上有索引，但 age 列上没有索引，MySQL 可能不会使用索引进行查询。

5. 索引未按最左前缀使用

对于多列组合索引，索引的使用需要遵循“最左前缀”规则，即查询条件必须按索引中定义的列的顺序从左到右使用。例如，假设有一个组合索引 (first_name, last_name)，以下查询会使用索引：

SELECT * FROM users WHERE first_name = 'John';

但如果你仅在 last_name 上进行过滤，则不会使用索引：

SELECT * FROM users WHERE last_name = 'Doe';

6. 排序与索引列不匹配

如果查询中涉及到 ORDER BY 子句，并且排序的字段没有与索引匹配，索引可能不会被使用。例如：

SELECT * FROM users ORDER BY age DESC;

如果 age 列上没有索引，而查询要求对 age 进行排序，索引不会被用到。或者，索引的顺序和 ORDER BY 不一致，也会导致索引失效。

7. 使用了不支持索引的操作

某些 SQL 操作不支持索引，常见的情况有：

LIKE 模糊匹配：如果使用 LIKE 且通配符 % 在字符串的开头，索引将无法使用。例如：
```
SELECT * FROM users WHERE name LIKE '%John';
```
这种情况下，索引不会被使用，因为 MySQL 无法通过索引找到以“John”结尾的字符串。
范围查询：在使用范围查询（如 >、<、BETWEEN 等）时，索引可能只能用在第一个范围条件上，后续的条件将无法使用索引。例如：
```
SELECT * FROM users WHERE age > 30 AND name = 'John';
```
这里，age 列上的索引可能会被使用，但 name 列上的索引可能会被忽略。

8. 统计信息过时或不准确

MySQL 优化器依赖统计信息来决定是否使用索引。如果表的统计信息过时或不准确，优化器可能会做出错误的决定，不使用本应使用的索引。可以通过 ANALYZE TABLE 命令来更新表的统计信息：

ANALYZE TABLE users;

9. 查询缓存或索引损坏

如果索引损坏或查询缓存中有错误，MySQL 可能会忽略索引。可以通过 REPAIR TABLE 修复表结构或重新创建索引：

REPAIR TABLE users;

10. 表或索引过大

当表或索引过大时，MySQL 可能会选择不使用该索引，因为从磁盘中读取索引可能比简单地扫描整个表花费更多的时间。

如何检查索引是否被使用？

使用 EXPLAIN 语句可以检查 MySQL 查询是否使用了索引。执行 EXPLAIN 后，会显示查询的执行计划，包括哪些索引被使用、查询是否使用了全表扫描等信息：

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

输出结果中的 key 列显示了使用的索引。如果 key 列为空，表示没有使用索引。通过分析 EXPLAIN 的结果，可以更好地理解查询如何执行，以及如何优化它。

Mysql

License: CC BY 4.0