mysql EXPLAIN命令的输出列简介
2023-12-13 11:20:25
MySQL :: MySQL 8.2 Reference Manual :: 8.8.2 EXPLAIN Output Format
explain命令提供了mysql数据库如何执行SQL语句的信息，可以跟?SELECT,?DELETE,?INSERT,?REPLACE,?UPDATE,?和?TABLE一起使用。?
explain命令可能输出多行，每行涉及一个表 。
先来看一个实际的执行，假设执行explain select * from test_table where type='register';输出如下：
explain命令输出的列如下：
Table?8.1?EXPLAIN Output Columns
| Column | JSON Name | Meaning |
|---|---|---|
| id | select_id | The?SELECT?identifier |
| select_type | None | The?SELECT?type |
| table | table_name | The table for the output row |
| partitions | partitions | The matching partitions |
| type | access_type | The join type |
| possible_keys | possible_keys | The possible indexes to choose |
| key | key | The index actually chosen |
| key_len | key_length | The length of the chosen key |
| ref | ref | The columns compared to the index |
| rows | rows | Estimate of rows to be examined |
| filtered | filtered | Percentage of rows filtered by table condition |
| Extra | None | Additional information |
id?：select的标识符。- select_type：select的类型，取值如下：
elect_type?Value | JSON Name | Meaning |
|---|---|---|
SIMPLE | None | Simple?SELECT?(not using?UNION?or subqueries) |
PRIMARY | None | Outermost?SELECT |
| UNION | None | Second or later?SELECT?statement in a?UNION |
DEPENDENT UNION | dependent?(true) | Second or later?SELECT?statement in a?UNION, dependent on outer query |
UNION RESULT | union_result | Result of a?UNION. |
| SUBQUERY | None | First?SELECT?in subquery |
DEPENDENT SUBQUERY | dependent?(true) | First?SELECT?in subquery, dependent on outer query |
DERIVED | None | Derived table |
DEPENDENT DERIVED | dependent?(true) | Derived table dependent on another table |
MATERIALIZED | materialized_from_subquery | Materialized subquery |
UNCACHEABLE SUBQUERY | cacheable?(false) | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
UNCACHEABLE UNION | cacheable?(false) | The second or later select in a?UNION?that belongs to an uncacheable subquery (see?UNCACHEABLE SUBQUERY) |
table?：这一行的输出涉及的表名 。partitions?：查询从哪个分区匹配到的记录。如果不是分区表，那么该返回的值为NULL。type?：join?类型。各种类型的解释请参考https://dev.mysql.com/doc/refman/8.2/en/explain-output.html#explain-join-types- possible_keys：一个索引列表，mysql可以从其中选择索引，来找到要查询的行。
key?：mysql最终决定使用的索引。key_len?：mysql最终决定使用的索引的长度。ref?：使用key?列指定的索引，找到要查询的记录，用哪些列或者常数跟索引进行比较。rows?：mysql认为它找到要查找的记录所要遍历的行数。filtered?：用表的条件过滤表的行数据的百分比，最大值是100。Extra?：包含了mysql如何解析查询的额外信息。各种取值请参考：MySQL :: MySQL 8.2 Reference Manual :: 8.8.2 EXPLAIN Output Format
文章来源:https://blog.csdn.net/panghuangang/article/details/134963300
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！