Mysql 系列 | explain 详解 + profile

EXPLAIN 用来模拟优化器执行 SQL 语句的执行计划。从而找到 SQL 优化方案。

EXPLAIN 适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。

EXPLAIN 不会真正执行 SQL 语句，只返回执行计划。

本篇使用 Mysql 8.0.12

EXPLAIN 输出结果分析

mysql> explain select * from t1 where CONCAT(a1,a2) in (select distinct a from t2 where (t2.d = '01' OR t2.d = '03' )) AND  end_flg = 0 and delete_flag = 0 and cd is not null\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: t1_delete_flag_index
          key: t1_delete_flag_index
      key_len: 2
          ref: const
         rows: 207561
     filtered: 9.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: <subquery2>
   partitions: NULL
         type: eq_ref
possible_keys: <auto_key>
          key: <auto_key>
      key_len: 768
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: MATERIALIZED
        table: t2
   partitions: NULL
         type: range
possible_keys: t2_a_index,t2_d_index
          key: t2_d_index
      key_len: 768
          ref: NULL
         rows: 198
     filtered: 100.00
        Extra: Using index condition
3 rows in set, 1 warning (0.00 sec)

id

是 SELECT 的查询系列号

ID 相同时，从上往下顺序执行
ID 不同时，值越大越先执行

select_type

是 SELECT 的类型

非 SELECT 语句的 select_type 值显示受影响表的语句类型。例如，对于 DELETE 语句，select_type 是 DELETE

SIMPLE，简单查询，不使用联合查询或子查询
PRIMARY，子查询中的最外层查询
UNION，UNION 中第二个或后面的查询
DEPENDENT UNION，UNION 中的第二个或后面的查询，依赖于外层查询
UNION RESULT，UNION 的结果
SUBQUERY，子查询中的第一个 SELECT
DEPENDENT SUBQUERY，子查询中的第一个 SELECT，依赖于外层查询
DERIVED，派生表的 SELECT
DEPENDENT DERIVED，派生表的 SELECT，依赖于另一个表
MATERIALIZED，物化子查询（从子查询查到具体结果集）
UNCACHEABLE SUBQUERY，一个子查询，其结果无法缓存，必须为外部查询的每一行重新计算
UNCACHEABLE UNION，UNION 中的第二个或后面的 SELECT，联合查询属于一个不可缓存的子查询

table

表名

查询的表名，也可能是简写
<subqueryN>，对 id 值为 N 的行的具体化子查询的结果
<derivedN>，id 值为 N 的派生表结果
<unionM,N>，id 值为 M 和 N 的并集

partitions

被查询匹配的记录分区
非分区表的值为 NULL

type

在表中找到所需行的方式

system，查询的表只有一行，是 const 类型的特例
const，查询表最多哦有一个匹配行，比如以主键或唯一索引为条件
eg_ref，对于先前表中的每个行组合，从该表中读取一行。当连接使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时使用它。

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref，对于先前表中的每个行组合，从该表中读取具有匹配索引值的所有行。如果连接仅使用键的最左侧前缀，或者如果键不是 PRIMARY KEY 或 UNIQUE 索引，则使用 ref。如果使用的键只匹配几行，这是一个很好的连接类型。

/* ref 可用于使用 = 或 <=> 运算符比较的索引列 */
SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

fulltext，使用 FULLTEXT 索引执行连接
ref_or_null，类似于 ref，但是会额外搜索包含 NULL 值的行。这种连接类型优化最常用于解析子查询。

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

index merge，索引合并优化，输出行中的键列包含使用的索引列表
range，只检索给定范围内的数据，使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 运算符中的任何一个将键列与常量进行比较时可用

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index，只扫描索引树
ALL，全表扫描

possible_keys

查询时可能使用到的索引，查询涉及到的字段如果存在索引就列出来
NULL 则没有相关索引

key

查询时实际使用的索引，包含在 possible_keys 中
如果没有选择索引则为 NULL
要强制 Mysql 使用或忽略使用索引，用 FORCE INDEX、USE INDEX 或 IGNORE INDEX
ANALYZE TABLE 有助于优化器选择更优的索引

key_len

显示索引字段的最大可能长度
NULL 的列的键长度比 NOT NULL 列的键长度大一
不损失精度的情况下，长度越短越好

ref

哪些列或常量和索引进行比较
func 表示是某一个函数的结果，详情用 EXPLAIN 后的 SHOW WARNINGS 来查看

rows

Mysql 认为它必须检查以执行查询的行数
InnoDB 中，这个数字是估计值，不准确

filtered

按条件过滤的百分比
最大为 100，表示没有过滤，rows x filtered 表示剩余与下表连接的行数

Extra

Using where，不读取表中所有数据，通过索引即可获取数据行
Using index，只用索引树来查询，当查询仅使用属于单个索引的列时，可以使用此策略
Using index condition，通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。通过这种方式，索引信息用于延迟（“下推”）读取全表行，除非有必要
。。。
。。。
。。。

profile

查看 SQL 执行情况

设置 profile

查看 profiling 是否开启

mysql> show variables like "profiling";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

开启 profile

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like "profiling";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

查看 Query 列表

开启 profile 后，所以执行的 SQL，都会记录下来

mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00286350 | show variables like "profiling" |
+----------+------------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

查看详细执行信息

最后一个 Query 信息

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000073 |
| checking permissions | 0.001133 |
| Opening tables       | 0.000026 |
| init                 | 0.000073 |
| System lock          | 0.000011 |
| optimizing           | 0.000006 |
| optimizing           | 0.000004 |
| statistics           | 0.000015 |
| preparing            | 0.000017 |
| statistics           | 0.000029 |
| preparing            | 0.000010 |
| executing            | 0.000009 |
| Sending data         | 0.000009 |
| executing            | 0.000004 |
| Sending data         | 0.001357 |
| end                  | 0.000008 |
| query end            | 0.000011 |
| removing tmp table   | 0.000010 |
| query end            | 0.000005 |
| closing tables       | 0.000008 |
| freeing items        | 0.000020 |
| cleaning up          | 0.000031 |
+----------------------+----------+
22 rows in set, 1 warning (0.00 sec)

查看指定 Query 详情

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000181 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000082 |
| init                 | 0.000007 |
| System lock          | 0.000012 |
| optimizing           | 0.000022 |
| statistics           | 0.000533 |
| preparing            | 0.000040 |
| explaining           | 0.000120 |
| end                  | 0.000006 |
| query end            | 0.000012 |
| closing tables       | 0.000009 |
| freeing items        | 0.000025 |
| cleaning up          | 0.000020 |
+----------------------+----------+
14 rows in set, 1 warning (0.00 sec)

posted @ 2022-08-04 15:16 菜乌阅读(195) 评论(0) 编辑收藏举报

刷新页面返回顶部

路漫漫其修远

Mysql 系列 | explain 详解 + profile

EXPLAIN 输出结果分析

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

profile

设置 profile

查看 Query 列表

查看详细执行信息

公告

路漫漫 其修远

Mysql 系列 | explain 详解 + profile

EXPLAIN 输出结果分析

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

profile

设置 profile

查看 Query 列表

查看详细执行信息

公告

路漫漫其修远