MySQL索引优化-执行计划（EXPLAIN）-基础使用

喵喵帕斯
196 阅读6分钟

本文基于 MySQL8.0.41，给出Explain的基础语法：

{EXPLAIN | DESCRIBE | DESC} 
    tbl_name [col_name | wild]
    
{EXPLAIN | DESCRIBE | DESC}  
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}  
    
{EXPLAIN | DESCRIBE | DESC} ANALYZE [explain_type] select_stmt

explain_type: { 
    FORMAT = format_name 
} 

format_name: { 
    TRADITIONAL | JSON | TREE 
} 

explainable_stmt: { 
    select_stmt 
    | TABLE ... 
    | DELETE ... 
    | INSERT ... 
    | REPLACE ... 
    | UPDATE ... 
} 

select_stmt: 
    SELECT ...

DESCRIBE、DESC、 EXPLAIN 对于MYSQL解析器来说是同义词。

使用上，DESC和DESCRIBE 常用于获取表结构信息，MYSQL提供 DESCRIBE 语句也是为了与 Oracle 语法兼容，而 EXPLAIN 则用于获取查询执行计划，下面给出三者在不同使用场景上的一些栗子：

获取表结构

对于获取表结构，以下几条SQL，执行结果都是相同的：

desc t_user_order;
describe t_user_order;
explain t_user_order;

-- 实际上describe是show columns的快捷方式
show columns from t_user_order;
FieldTypeNullKeyDefaultExtra
idint unsignedNOPRIauto_increment
codevarchar(64)NOUNI
uidvarchar(64)NOMUL
pay_amountintNO
pay_statustinyintNO
order_statustinyintNOMUL

再解释一下语法表中的 [col_name | wild] ，如果只想要查看表中某个字段的信息，可以这样用：

desc t_user_order code;
describe t_user_order code;
explain t_user_order code;

上面三个SQL的输出结果是完全相同的：

FieldTypeNullKeyDefaultExtra
codevarchar(64)NOUNI

那么另外的wild，它表示一种匹配模式，举个栗子就懂了：

desc t_user_order '%pay%' ;
describe t_user_order '%pay%';
explain t_user_order '%pay%';

以上的SQL输出结果也是相同的：

FieldTypeNullKeyDefaultExtra
pay_amountintNO
pay_statustinyintNO

看到结果，应该能理解了吧，相当于SQL中的like模糊匹配。

获取执行计划信息

EXPLAIN 语句提供有关 MySQL 如何执行语句的信息：

  • EXPLAIN 适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。
  • 当执行 EXPLAIN 时，MySQL 会显示来自优化器的有关语句执行计划的信息。也就是说，MySQL 会解释它将如何处理语句，包括有关表如何连接以及连接顺序的信息等。
  • 当 EXPLAIN 与 FOR CONNECTION connection_id 一起使用时，它会显示在指定连接中执行的语句的执行计划。
  • 对于可解释的语句，EXPLAIN 会生成额外的执行计划信息，这些信息可以使用 SHOW WARNINGS 显示。
  • EXPLAIN 需要与执行解释语句相同的权限。此外，EXPLAIN 还要求任何被解释的视图具有 SHOW VIEW 权限。EXPLAIN ... 如果指定的连接属于不同的用户，FOR CONNECTION 还需要 PROCESS 权限。

关于基础语法中的 explain_type，可以设置执行计划结果的输出格式，默认是传统表格形式（TRADITIONAL），除此之外还有另外两种配置：

  • JSON：以 JSON 格式显示信息。
  • TREE：从 8.0.16 开始，MYSQL提供树形结果输出，比 TRADITIONAL 格式更精确地描述查询处理；它是唯一显示哈希连接使用情况的格式，并且始终用于 EXPLAIN ANALYZE。

从 MySQL 8.0.32 起，EXPLAIN 使用的默认输出格式（即没有 FORMAT 选项时）由 explain_format 系统变量的值决定。

如果想要查看当前执行计划的格式配置，可以通过以下SQL查看：

select @@explain_format;

结果如下：

@@explain_format
TRADITIONAL

这里我们在默认格式（TRADITIONAL）下，执行一个SQL：

select * from t_user_order where uid = '9a4aebe012384c109106504b7cff14b3' and pay_amount > 1000;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEt_user_orderrefidx_uid,idx_pay_amountidx_uid2583019.79Using where

如果我们将 explain_format 的值设置为 TREE，然后重新运行相同的 EXPLAIN 语句，为了方便展示树形结构，这里我贴一张图比较方便理解：

image.png

它的树形结构是由特殊符号 -> 加上 缩进来表示，接下来再比较一下格式为JSON的情况，不过这次我们可以直接在语句上拼接参数来生效：

explain format = JSON select * from t_user_order where uid like '9a4aebe012384c109106504b7cff14b3' and pay_amount > 1000

输出结果：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10.50"
    },
    "table": {
      "table_name": "t_user_order",
      "access_type": "ref",
      "possible_keys": [
        "idx_uid",
        "idx_pay_amount"
      ],
      "key": "idx_uid",
      "used_key_parts": [
        "uid"
      ],
      "key_length": "258",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 30,
      "rows_produced_per_join": 5,
      "filtered": "19.79",
      "cost_info": {
        "read_cost": "7.50",
        "eval_cost": "0.59",
        "prefix_cost": "10.50",
        "data_read_per_join": "3K"
      },
      "used_columns": [
        "id",
        "code",
        "uid",
        "pay_amount",
        "pay_status",
        "order_status"
      ],
      "attached_condition": "(`test`.`t_user_order`.`pay_amount` > 1000)"
    }
  }
}

要将 EXPLAIN 的默认输出返回为表格格式，可以把 explain_format 设置为 TRADITIONAL。或者，也可以设置为 DEFAULT，效果是一样的。

在 EXPLAIN 的帮助下，我们可以看到应该在哪些表中添加索引，以便通过使用索引查找行来加快语句的执行速度。还可以使用 EXPLAIN 检查优化器是否以最佳顺序连接表。要提示优化程序使用与 SELECT 语句中表命名顺序相对应的连接顺序，可在语句开头使用 SELECT ... STRAIGHT_JOIN。

如果遇到应该使用索引而没有使用的问题，可以尝试运行 ANALYZE TABLE 更新表统计信息，比如索引的cardinality值，可能会影响优化器做出的选择。

使用EXPLAIN ANALYZE获取信息

MySQL 8.0.18 引入了 EXPLAIN ANALYZE，它在生成执行计划的同时还给出了时间和基于迭代器的附加信息，说明优化器的预期与实际执行的匹配情况。对于每个迭代器，将提供以下信息：

  • 预估执行时间
  • 预估返回行数
  • 返回第一行的时间
  • 执行此迭代器（包括子迭代器，但不包括父迭代器）所用的时间，毫秒。(当有多个循环时，显示的是每个循环的平均时间）。
  • 迭代器返回的行数
  • 循环次数

输出结果使用 TREE 格式显示，其中节点代表迭代器。EXPLAIN ANALYZE 始终使用 TREE 输出格式。在 MySQL 8.0.21 及更高版本中，可以选择使用 FORMAT=TREE 来显式指定输出格式；不支持 TREE 以外的格式。

从 MySQL 8.0.20 开始，可以使用 KILL QUERY 或 CTRL-C 终止语句。

EXPLAIN ANALYZE 不能与 FOR CONNECTION 一起使用。

整个栗子，来看下输出结果：

explain analyze select * from t_user_order where uid = '9a4aebe012384c109106504b7cff14b3' and pay_amount > 1000

输出结果：

-> Filter: (t_user_order.pay_amount > 1000)  (cost=8.09 rows=5.94) (actual time=0.056..0.0579 rows=3 loops=1)
    -> Index lookup on t_user_order using idx_uid (uid='9a4aebe012384c109106504b7cff14b3')  (cost=8.09 rows=30) (actual time=0.0243..0.0509 rows=30 loops=1)

注意：actual time 单位是毫秒

从 MySQL 8.0.32 起，explain_format 系统变量对 EXPLAIN ANALYZE 有以下影响：

  • 如果变量的值是 TRADITIONAL 或 TREE（或同义词 DEFAULT），那么 EXPLAIN ANALYZE 将使用 TREE 格式。
  • 如果变量的值是 JSON，除非在语句中指定 FORMAT=TREE，否则 EXPLAIN ANALYZE 将返回错误。因为 EXPLAIN ANALYZE 只支持 TREE 输出格式。
  • 无论 explain_format 的值是多少，在 XPLAIN ANALYZE 中使用 FORMAT=TRADITIONAL 或 FORMAT=JSON 总是会引发错误。

从 MySQL 8.0.33 开始，EXPLAIN ANALYZE 和 EXPLAIN FORMAT=TREE 输出中的数字按以下规则格式化：

  • 0.001-999999.5 范围内的数字打印为十进制数。小于 1000 的小数有三位有效数字，其余的有四位、五位或六位有效数字。
  • 超出 0.001-999999.5 范围的数字将以科学计数法格式打印。例如 1.23e+9 和 934e-6。
  • 不打印尾数零。例如，打印 2.3 而不是 2.30，打印 1.2e+6 而不是 1.20e+6。
  • 小于 1e-12 的数字打印为 0。

参考文档：dev.mysql.com/doc/refman/…

avatar
文章
阅读
粉丝