数据库课堂笔记10（SQL语句分析）_数据库sort cost-CSDN博客

本文链接：https://blog.csdn.net/yuuisei/article/details/120187886

explain分析SQL语句

explain

explain是什么？

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。

explain能干嘛？

表的读取顺序
数据读取操作的操作类型
那些索引可以使用
那些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

explain怎么玩？

explain + SQL语句
在这里插入图片描述

explain字段解释

对上图的表进行解释

id表的读取顺序

select查询的顺序号,包含一组数字,表示查询中执行select子句或操作表的顺序

两种情况:
1.id相同,执行顺序由上至下
2.id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

select_type

数据读取操作的操作类型

值	描述
SIMPLE	简单的SELECT语句（不包括UNION操作或子查询操作）
PRIMARY	查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）
UNION	UNION操作中，查询中处于内层的SELECT，即被union的SELECT
SUBQUERY	子查询中的SELECT
DERIVED	表示包含在 From 子句中的 Select 查询
UNION RESULT	union的结果,此时id为NULL

table

显示这一行的数据时关于那张表的

partitions

查询访问的分区

type

在这里插入图片描述
从最好到最差依次是
system > const > eq_ref > ref > range > index > ALL

system,表只有一行记录(等于系统表),这是const类型的特例
const表示通过索引一次就找到了,const用于比较primary key
eq_ref,唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配
ref,非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行
range,只检索给定范围的行,使用一个索引来选择行。
index,Full Index Scan,index与ALL区别为index类型只遍历索引树。
ALL,将遍历全表找到匹配的行

possible_keys

显示可能应用在这张表中的索引,一个或多个。

key

实际使用的索引。如果为null,则没有使用索引
查询中若使用了覆盖索引,则该索引仅出现在key列表中

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

索引长度计算

varchr(24)变长字段且允许NULL
24*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchr(10)变长字段且不允许NULL  
10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL      
10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL    
10*(Character Set：utf8=3,gbk=2,latin1=1)

ref

显示索引那一列被使用到了

rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
在这里插入图片描述

Extra

包含不适合在其他列中显示但十分重要的额外信息

Using filesort,说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为"文件排序"
Using temporary,使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。
Using index,使用了索引,避免了全表扫描
Using where,使用了where过滤
Using join buffer,使用了连接缓存
impossible where,不可能的条件,where子句的值总是false

Show Profile进行SQL分析

是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量

默认情况下,参数处于关闭状态,并保持最近15次的运行结果。

Show Profile分析步骤

1.是否支持,看看当前MySQL版本是否支持
2.开启功能,默认是关闭,使用前需要开启

type

all 显示所有的开销信息
block io 显示块IO相关开销
cpu 显示CPU相关开销信息
ipc 显示发送和接收相关开销信息
memory 显示内存相关开销信息
page faults 显示页面错误相关开销信息

参数注意

converting HEAP to MyISAM查询结果太大,内存都不够用了往磁盘上搬

Creating tmp table 创建临时表

Copying to tmp table on disk 把内存中临时表复制到磁盘,危险

locked 出现锁

全局查询日志

开启命令
set global general_log = 1;
将SQL语句写到表中
set global log_output = ‘TABLE’;
你所编写的SQL语句,会记录到MySQL库里的genral_log表
select * from mysql.general_log;

trace分析SQL优化器

建表

CREATE TABLE `test_trace` (             
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `a` INT(11) DEFAULT NULL,
  `b` INT(11) DEFAULT NULL,
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

trace分析SQL优化器介绍

从前面学到了 explain 可以查看 SQL 执行计划，但是无法知道它为什么做这个决策，如果想确定多种索引方案之间是如何选择的或者排序时选择的是哪种排序模式，有什么好的办法吗？

从 MySQL 5.6 开始，可以使用 trace 查看优化器如何选择执行计划。

通过trace，能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划，或者知道某个排序使用的排序模式，帮助我们更好地理解优化器行为。

如果需要使用，先开启 trace，设置格式为 JSON，再执行需要分析的 SQL，最后查看 trace 分析结果（在 information_schema.OPTIMIZER_TRACE 中）。

开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。

下面一起来看下 trace 的使用方法。使用讲解 explain 时创建的表test_trace做实验。
首先构造如下 SQL (表示取出表 t1 中 a 的值大于 900 并且 b 的值大于 910 的数据，然后按照 a 字段排序)：
select * from test_trace where a >900 and b > 910 order by a;

使用 trace 进行分析

set session optimizer_trace="enabled=on",end_markers_in_json=on;

optimizer_trace=“enabled=on” 表示开启trace
end_markers_in_json=on 表示 JSON 输出开启结束标记

查看trace分析结果

SELECT * FROM information_schema.OPTIMIZER_TRACE\G

QUERY: select * from t1 where a >900 and b > 910 order  by a    --SQL语句
TRACE: {
  "steps": [
    {
      "join_preparation": {				--SQL准备阶段
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {			--SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {    --条件处理
              "condition": "WHERE",
              "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",        --原始条件
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" 		--等值传递转换
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"       --常量传递转换
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"        --去除没有的条件后的结构
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */   --替换虚拟生成列
          },
          {
            "table_dependencies": [		--表依赖详情
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [	--预估表的访问成本
              {
                "table": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1000,       --扫描行数
                    "cost": 207.1       --成本
                  } /* table_scan */,
                  "potential_range_indexes": [    --分析可能使用的索引
                    {
                      "index": "PRIMARY",
                      "usable": false,       --为false，说明主键索引不可用
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_a",      --可能使用索引idx_a
                      "usable": true,
                      "key_parts": [
                        "a",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_b",      --可能使用索引idx_b
                      "usable": true,
                      "key_parts": [
                        "b",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": { --分析各索引的成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_a",	--使用索引idx_a的成本
                        "ranges": [
                          "900 < a"			--使用索引idx_a的范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true, --是否使用index dive（详细描述请看下方的知识扩展）
                        "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,  	--是否使用mrr
                        "index_only": false,    --是否使用覆盖索引
                        "rows": 100,            --使用该索引获取的记录数
                        "cost": 121.01,         --使用该索引的成本
                        "chosen": true          --可能选择该索引
                      },
                      {
                        "index": "idx_b",       --使用索引idx_b的成本
                        "ranges": [
                          "910 < b"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 90,
                        "cost": 109.01,
                        "chosen": true             --也可能选择该索引
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": { --分析使用索引合并的成本
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {  --确认最优方法
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_b",
                      "rows": 90,
                      "ranges": [
                        "910 < b"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 90,
                    "cost_for_plan": 109.01,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [  --考虑的执行计划
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {          --最优的访问路径
                  "considered_access_paths": [ --决定的访问路径
                    {
                      "rows_to_scan": 90,      --扫描的行数
                      "access_type": "range",  --访问类型：为range
                      "range_details": {
                        "used_index": "idx_b"  --使用的索引为：idx_b
                      } /* range_details */,
                      "resulting_rows": 90,    --结果行数
                      "cost": 127.01,          --成本
                      "chosen": true,		   --确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 90,
                "cost_for_plan": 127.01,
                "sort_cost": 90,
                "new_cost_for_plan": 217.01,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {  --尝试添加一些其他的查询条件
              "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`t1`.`a`",
              "items": [
                {
                  "item": "`t1`.`a`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`t1`.`a`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`t1`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "idx_b",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [          --改进的执行计划
              {
                "table": "`t1`",
                "pushed_index_condition": "(`t1`.`b` > 910)",
                "table_condition_attached": "(`t1`.`a` > 900)"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {             --SQL执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`t1`",
                "field": "a"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,             --未使用优先队列优化排序
              "cause": "not applicable (no LIMIT)"     --未使用优先队列排序的原因是没有limit
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {           --排序详情
              "rows": 90,
              "examined_rows": 90,          --参与排序的行数
              "number_of_tmp_files": 0,     --排序过程中使用的临时文件数
              "sort_buffer_size": 115056,
              "sort_mode": "<sort_key, additional_fields>"   --排序模式（详解请看下方知识扩展）
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0	--该字段表示分析过程丢弃的文本字节大小，本例为0，说明没丢弃任何文本
          INSUFFICIENT_PRIVILEGES: 0    --查看trace的权限是否不足，0表示有权限查看trace详情
1 row in set (0.00 sec)

关闭trace

set session optimizer_trace="enabled=off";

TRACE 字段中整个文本大致分为三个过程。

准备阶段：对应文本中的 join_preparation
优化阶段：对应文本中的 join_optimization
执行阶段：对应文本中的 join_execution
使用时，重点关注优化阶段和执行阶段

在 trace 结果的 analyzing_range_alternatives 这一项可以看到：使用索引 idx_a 的成本为 121.01，使用索引 idx_b 的成本为 109.01，显然使用索引 idx_b 的成本要低些，因此优化器选择了 idx_b 索引；

总结

explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。