如何判断一个MySQL查询是否使用了索引？-mysql教程-PHP中文网

答案：使用EXPLAIN命令分析执行计划，重点观察type、key、rows和Extra列，结合Handler状态变量和optimizer_trace等工具综合判断索引使用效率。

如何判断一个mysql查询是否使用了索引？

要判断一个MySQL查询是否使用了索引，最直接、最权威的手段就是使用

EXPLAIN

登录后复制

命令。它能清晰地揭示MySQL优化器如何执行你的SQL语句，包括是否利用了索引、使用了哪个索引、扫描了多少行数据等关键信息。此外，通过观察MySQL的状态变量和更深层次的优化器跟踪，我们也能间接或详细地洞察索引的使用情况。

解决方案

EXPLAIN

登录后复制

命令是我们的核心工具。你只需在任何

SELECT

登录后复制

、

INSERT

登录后复制

、

UPDATE

登录后复制

、

DELETE

登录后复制

语句前加上

EXPLAIN

登录后复制

关键字，MySQL就会返回一个执行计划，而不是实际执行查询。

EXPLAIN SELECT * FROM products WHERE category_id = 10 AND price > 100 ORDER BY created_at DESC;

登录后复制

观察

EXPLAIN

登录后复制

的输出，有几个关键列需要我们重点关注：

type
登录后复制
：这是最重要的列之一，它表示了连接类型。
- ```
const
```
  登录后复制
  ,
```
eq_ref
```
  登录后复制
  ,
```
ref
```
  登录后复制
  ,
```
range
```
  登录后复制
  ：这些都是非常好的类型，表明查询有效地使用了索引。
```
const
```
  登录后复制
  是最佳的，通常用于主键或唯一索引的等值查询；
```
range
```
  登录后复制
  表示索引范围扫描。
- ```
index
```
  登录后复制
  ：表示全索引扫描。虽然比
```
ALL
```
  登录后复制
  （全表扫描）好，因为它避免了访问数据行，但如果索引很大，性能依然可能不理想。
- ```
ALL
```
  登录后复制
  ：最差的类型，表示全表扫描，意味着没有使用索引，或者说索引没有发挥作用。
possible_keys
登录后复制
：这一列列出了MySQL认为可能用于查找的索引。这只是一个候选列表，不代表实际使用了。
key
登录后复制
：这一列显示了MySQL实际决定使用的索引。如果这里是
```
NULL
```
登录后复制
，那就说明没有使用索引。
key_len
登录后复制
：显示了MySQL实际使用的索引的长度。这个值越小，通常表示索引的匹配度越高，查询效率也可能越好。
rows
登录后复制
：MySQL估计为了找到所需行而需要读取的行数。这个值越小越好，它直接反映了索引的过滤能力。
Extra
登录后复制
：这一列提供了额外的信息，对判断索引使用情况至关重要。
- ```
Using index
```
  登录后复制
  ：表示查询所需的所有数据都可以在索引中找到，无需回表（覆盖索引）。这是非常高效的。
- ```
Using where
```
  登录后复制
  ：表示MySQL需要通过
```
WHERE
```
  登录后复制
  子句来过滤结果。如果同时出现
```
Using index
```
  登录后复制
  ，通常意味着索引用于查找，
```
WHERE
```
  登录后复制
  用于进一步过滤。如果只有
```
Using where
```
  登录后复制
  且
```
type
```
  登录后复制
  是
```
ALL
```
  登录后复制
  ，那说明
```
WHERE
```
  登录后复制
  是在全表扫描后进行的。
- ```
Using filesort
```
  登录后复制
  ：表示MySQL需要对结果进行额外的排序操作，这通常发生在内存或磁盘上，开销较大。如果能通过索引避免，性能会显著提升。
- ```
Using temporary
```
  登录后复制
  ：表示MySQL需要创建临时表来处理查询，通常发生在复杂的
```
GROUP BY
```
  登录后复制
  或
```
DISTINCT
```
  登录后复制
  操作中，开销也很大。

在我看来，如果你看到

type

登录后复制

是

ALL

登录后复制

，或者

key

登录后复制

是

NULL

登录后复制

，那基本可以确定索引没用上。如果

Extra

登录后复制

里有

Using filesort

登录后复制

或

Using temporary

登录后复制

，那也说明查询还有优化空间，可能可以通过创建合适的索引来避免这些额外操作。

如何解读

EXPLAIN

登录后复制

结果中的关键指标来判断索引效率？

解读

EXPLAIN

登录后复制

结果，不只是看有没有用索引，更要看索引用得“好不好”。毕竟，用了索引但效率低下，和没用索引也差不了多少，甚至有时还不如全表扫描。我通常会这么看：

首先，

type

登录后复制

列是第一道防线。

const

登录后复制

、

eq_ref

登录后复制

、

ref

登录后复制

、

range

登录后复制

这四种类型，基本可以认为是索引发挥了有效作用，效率从高到低排列。特别是

const

登录后复制

和

eq_ref

登录后复制

，那简直是完美。

range

登录后复制

表示索引范围扫描，虽然不错，但如果范围过大，

rows

登录后复制

值也会相应增大，效率会打折扣。

index

登录后复制

类型则要警惕一下，它确实扫描了索引，但如果是全索引扫描，且索引本身很大，那性能可能不如预期。最糟糕的是

ALL

登录后复制

，这通常意味着索引失效，或者压根就没有合适的索引。

其次，

rows

登录后复制

列是我判断索引“瘦身”能力的关键。这个值代表了MySQL预估需要检查的行数。一个高效的索引，应该能让

rows

登录后复制

值尽可能小。如果

rows

登录后复制

值接近表中的总行数，即使

type

登录后复制

不是

ALL

登录后复制

，也说明索引的过滤性很差，或者查询条件没有充分利用索引。

再来，

Extra

登录后复制

列是藏着很多“秘密”的地方。

```
Using index
```
登录后复制
：这是我们梦寐以求的，意味着查询是一个“覆盖索引”查询。所有需要的数据都在索引树中，MySQL不需要回表去查找实际的数据行，大大减少了I/O操作。如果你能把大部分查询都优化成覆盖索引，那性能提升会非常明显。
```
Using filesort
```
登录后复制
：看到这个，我通常会眉头一皱。这意味着MySQL无法利用索引的排序特性，需要自己对结果进行额外的排序。这在数据量大时，会非常耗时。考虑是否能为
```
ORDER BY
```
登录后复制
子句创建复合索引来消除它。
```
Using temporary
```
登录后复制
：这个也让人头疼。它表示MySQL需要创建临时表来处理查询，通常是
```
GROUP BY
```
登录后复制
或
```
DISTINCT
```
登录后复制
操作无法直接通过索引完成时。同样，合适的复合索引有时能解决这个问题。

最后，

key_len

登录后复制

列也值得一看。它表示MySQL在索引中使用的字节长度。如果一个复合索引包含多个列，

key_len

登录后复制

会告诉你实际使用了多少列来匹配。比如，

INDEX(col1, col2, col3)

登录后复制

，如果

key_len

登录后复制

只显示了

col1

登录后复制

的长度，说明

col2

登录后复制

和

col3

登录后复制

没有被用于索引匹配，这可能是查询条件不匹配索引的最左前缀原则，或者查询条件中存在函数导致索引失效。

综合来看，一个高效的索引使用，通常会表现为：

type

登录后复制

是

const

登录后复制

、

eq_ref

登录后复制

、

ref

登录后复制

或

range

登录后复制

，

key

登录后复制

列不为空，

rows

登录后复制

值很小，

Extra

登录后复制

列中最好有

Using index

登录后复制

，且没有

Using filesort

登录后复制

或

Using temporary

登录后复制

。

为什么有时候MySQL不使用索引，即使它存在？

这是一个非常常见的困惑，也是优化工作中经常遇到的挑战。我见过太多开发者，明明为某个列创建了索引，但查询性能依然不佳，一查

EXPLAIN

登录后复制

，发现索引根本没用上。这背后其实是MySQL优化器的一个复杂决策过程。

一个主要原因是索引选择性（Cardinality）太低。如果一个列的唯一值很少，比如一个性别字段（男/女），即使你为它创建了索引，MySQL也可能认为扫描整个表（

ALL

登录后复制

）的成本，比先扫描索引再回表查找数据的成本更低。因为索引扫描也需要I/O，如果索引能过滤掉的行数不多，那么索引的优势就不明显了。优化器会根据统计信息来判断。

其次，数据分布不均也会影响索引使用。比如，一个字段大部分值都是

NULL

登录后复制

，只有少数非

NULL

登录后复制

值。如果你查询非

NULL

登录后复制

值，索引可能有效；但如果你查询

NULL

登录后复制

值，MySQL可能觉得直接全表扫描更快。

查询条件不符合“最左前缀原则”是另一个大坑。对于复合索引

INDEX(col1, col2, col3)

登录后复制

，如果你只查询

col2

登录后复制

或

col3

登录后复制

，或者查询条件跳过了

col1

登录后复制

，那么这个复合索引可能就无法被完全利用，甚至完全失效。比如

WHERE col2 = 'abc'

登录后复制

就用不上

col1

登录后复制

开头的索引。

蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

查看详情

隐式类型转换也是个隐形杀手。如果你对一个字符串类型的列（比如

VARCHAR

登录后复制

）使用数字进行查询，例如

WHERE string_col = 123

登录后复制

，MySQL可能会将

string_col

登录后复制

隐式转换为数字再进行比较。这种转换会导致索引失效，因为索引是基于原始数据类型构建的。始终确保查询条件的数据类型与列的实际数据类型匹配。

OR

登录后复制

条件有时也会让索引失效。如果

OR

登录后复制

连接的两个条件分别可以使用不同的索引，MySQL可能无法有效地合并这两个索引的扫描结果，最终选择全表扫描。不过，在某些版本和特定情况下，MySQL的优化器已经能够处理

OR

登录后复制

条件下的索引合并（

Using union

登录后复制

），但这不是绝对的。

负向查询条件，如

NOT IN

登录后复制

、

!=

登录后复制

、

<>

登录后复制

、

NOT LIKE

登录后复制

（如果不是前缀匹配），通常也难以利用索引。因为这些操作需要扫描大部分数据才能确定哪些是不符合条件的。

函数操作在

WHERE

登录后复制

子句中也是索引的克星。例如

WHERE YEAR(date_col) = 2023

登录后复制

，MySQL无法直接利用

date_col

登录后复制

上的索引，因为它必须先计算

YEAR(date_col)

登录后复制

，然后才能比较。解决方案通常是重写查询，避免在索引列上使用函数，或者创建函数索引（MySQL 8.0+）。

最后，有时是优化器本身的决策。MySQL优化器是一个复杂的成本模型，它会综合考虑索引大小、数据量、数据分布、内存情况等多种因素来估算不同执行路径的成本。即使存在索引，如果优化器评估全表扫描的成本更低（例如，要返回的行数占总行数的比例很高），它也可能选择不使用索引。这不一定是优化器“错了”，而是它基于当前统计信息做出的“最佳”选择。

除了

EXPLAIN

登录后复制

，还有哪些方法可以监控MySQL的索引使用情况？

虽然

EXPLAIN

登录后复制

是分析单个查询的利器，但要全面监控数据库层面索引的整体使用情况，我们需要一些更宏观的视角和工具。

我经常会用到

SHOW STATUS

登录后复制

命令族来查看MySQL服务器的各种状态变量。其中，与索引使用相关的主要是那些以

Handler_

登录后复制

开头的变量。

Handler_read_key

登录后复制

表示从索引读取行的请求数，

Handler_read_rnd_next

登录后复制

表示在数据文件中进行下一次读取的请求数（通常是全表扫描或未按索引顺序读取）。如果

Handler_read_key

登录后复制

很高，而

Handler_read_rnd_next

登录后复制

相对较低，那通常意味着索引使用得不错。反之，如果

Handler_read_rnd_next

登录后复制

异常高，可能就暗示着存在大量全表扫描或者索引效率低下。当然，这些是全局计数器，需要结合时间段和业务负载来分析。

对于更深入地理解优化器如何做出决策，尤其是当

EXPLAIN

登录后复制

的输出不够清晰时，

optimizer_trace

登录后复制

是一个非常强大的工具。它能让你看到MySQL优化器在选择执行计划时的每一步思考过程，包括它考虑了哪些索引、为什么选择了某个索引或放弃了某个索引、成本估算等。

使用

optimizer_trace

登录后复制

的步骤大致是：

```
SET optimizer_trace='enabled=on';
```
登录后复制
执行你想要分析的SQL查询。

SELECT * FROM information_schema.optimizer_trace;

登录后复制

```
SET optimizer_trace='enabled=off';
```
登录后复制

optimizer_trace

登录后复制

的输出是JSON格式的，内容非常详细，需要一些耐心去解读。它会告诉你优化器是如何计算每个可能执行计划的成本，以及最终选择哪个计划的原因。这对于理解为什么某个索引没有被使用，或者为什么优化器选择了看似次优的计划，非常有帮助。

此外，MySQL 5.7及更高版本提供的

sys

登录后复制

schema也包含了一些非常有用的视图，可以帮助我们监控索引使用情况。例如：

```
sys.schema_table_statistics_with_buffer
```
登录后复制
：这个视图提供了每个表的I/O统计信息，包括读取次数、写入次数等。通过观察不同表的I/O模式，可以间接判断哪些表可能存在索引问题。
```
sys.schema_index_statistics
```
登录后复制
：这个视图提供了每个索引的统计信息，包括索引的扫描次数、读取行数等。这能让你知道哪些索引被频繁使用，哪些索引可能长期处于闲置状态，从而为索引的优化或删除提供依据。

这些工具结合起来，能让我们从不同粒度、不同维度去监控和分析MySQL的索引使用情况，从单个查询的微观优化到整个数据库的宏观性能调优，都能提供有价值的洞察。

以上就是如何判断一个MySQL查询是否使用了索引？的详细内容，更多请关注php中文网其它相关文章！