MySQL之SQL分析工具使用

概述：MySQL有三种SQL分析工具，分别为explain、profiling、optimizer_trace，下面分别介绍一下怎么使用的

  1. 环境

    1. MySQL 5.7

    2. 测试表,随便找个MySQL的表都可以，例：

      CREATE TABLE `config` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL DEFAULT '' COMMENT '配置key,唯一',
  `value` text NOT NULL COMMENT '反馈内容',
  `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 线下，1 线上',
  `prefix` varchar(50) NOT NULL DEFAULT '' COMMENT '配置前缀，建议和项目名称有关',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `type_prefix_key` (`type`,`prefix`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='反馈表'

  2. 分析

    1. explain 一般用来分析 查询语句

      1. 使用：

        explain SELECT * FROM  config where id = 1;

      2. 结果：
        MySQL explain

      3. 各字段含义：参考 MYSQL explain详解

        1. id 是select 查询序列号，，查询序列号即为sql语句执行的顺序

        2. select_type 是指 select 类型，有以下几种取值

          1. simple 简单的select 没有union和子查询

          2. primary 在有子查询的语句中，最外面的select 查询就是primary

          3. union语句的第二个或者说是后面那一个

          4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，依赖于外面的查询)

          5. UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

          6. SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

          7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

          8. DERIVED(派生表的SELECT, FROM子句的子查询)

          9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

        3. table输出的行所用的表

        4. type 连接类型，下面从最佳类型到最差类型介绍

          1. system表示仅有一行，这是const类型的特例，一般不会出现

          2. const 表示 只比较primary key或者unique索引且表最多有一行匹配

          3. eq_ref 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

          4. ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

          5. range 只检索给定范围的行，使用一个索引来选择行

          6. index Full Index Scan，index与ALL区别为index类型只遍历索引树

          7. ALL Full Table Scan， MySQL将遍历全表以找到匹配的行

        5. possible_keys

          指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

        6. key

          key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

          如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

        7. key_len

          key_len显示的值为索引字段的最大可能长度，即key_len是根据表定义计算而得，不是通过表内检索出的

        8. ref

          列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

        9. rows

          估算出结果集行数

        10. Extra

          1. Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据
          2. Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
          3. Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
          4. Using join buffer：该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
          5. Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
          6. Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
          7. No tables used：Query语句中使用from dual 或不含任何from子句，关于 dual可以参考：mysql中dual表

      4. 注：

        1. EXPLAIN没有关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
        2. EXPLAIN不考虑各种Cache
        3. EXPLAIN不能显示MySQL在执行查询时所作的优化工作
        4. 部分统计信息是估算的，并非精确值

    2. profiling 一般用来分析 查询语句

      1. 使用

        // 开启profiling
set profiling=1;
// 执行要分析的查询语句
SELECT * FROM  config where id = 1;

// 获取系统中保存的所有 Query 的 profile 概要信息
show profiles;

// 查询资源消耗情况  
Duration：SQL语句执行时长
Block_ops_in和Block_ops_out表示块存储设备输入和输出的次数，即从硬盘读取和写入数据的次数
show profile for query queryid;
show profile cpu, block, io for query queryid;

// 关闭profiling
SET profiling = 0;

    3. optimizer_trace

      1. 支持操作

        SELECT、INSERT、REPLACE、UPDATE、DELETE、EXPLAIN、SET、DECLARE、CASE、IF、RETURN、CALL

      2. 使用

        // enabled：optimizer_trace、one_line：是否开启单行存储。
set optimizer_trace="enabled=on,one_line=off";

// 查看optimizer_trace配置
show variables like '%optimizer_trace%';

//默认情况下，由于optimizer_trace_offset=-1，optimizer_trace_limit=1，记录最近的一条SQL语句，展示时，每次展示1条数据；
// 如果改成 SET optimizer_trace_offset=-2, optimizer_trace_limit=1 ，则会记录倒数第二条SQL语句；

//Maximum allowed cumulated size of stored optimizer traces 单位字节
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000

// 查找结果
select * from information_schema.optimizer_trace;
//OPTIMIZER_TRACE结果分析
//QUERY：查询语句
//TRACE：QUERY字段对应语句的跟踪信息
//MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪信息过长时，被截断的跟踪信息的字节数。
//INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。
// 主要对trace的内容进行分析

      3. 结果分析：

        {
    "steps": [
        {
            "join_preparation": {
                "select#": 1,
                "steps": [
                    {
                      // 格式化SQL
                        "expanded_query": "/* select#1 */ select `config`.`id` AS `id`,`config`.`key` AS `key`,`config`.`value` AS `value`,`config`.`type` AS `type`,`config`.`prefix` AS `prefix`,`config`.`create_time` AS `create_time`,`config`.`update_time` AS `update_time` from `config` where (`config`.`id` = 1) limit 0,5000"
                    }
                ]
            }
        },
        {
          // 优化阶段的执行过程
            "join_optimization": {
                "select#": 1,
                "steps": [
                    {
                      // 对条件处理，主要对WHERE条件进行优化处理
                        "condition_processing": {
                          // 优化对象类型。WHERE条件句或者是HAVING条件句
                            "condition": "WHERE",
                          	// 优化前的原始语句
                            "original_condition": "(`config`.`id` = 1)",
                            "steps": [
                                {
                                  // 转换类型句
                                    "transformation": "equality_propagation",
                                  //转换之后的结果输出
                                    "resulting_condition": "multiple equal(1, `config`.`id`)"
                                },
                                {
                                    "transformation": "constant_propagation",
                                    "resulting_condition": "multiple equal(1, `config`.`id`)"
                                },
                                {
                                    "transformation": "trivial_condition_removal",
                                    "resulting_condition": "multiple equal(1, `config`.`id`)"
                                }
                            ]
                        }
                    },
                    {
                      // 用于替换虚拟生成列
                        "substitute_generated_columns": {}
                    },
                    {
                      //分析表之间的依赖关系
                        "table_dependencies": [
                            {
                              // 涉及的表名
                                "table": "`config`",
                              //行是否可能为NULL，这里是指JOIN操作之后，这张表里的数据是不是可能为NULL。如果语句中使用了LEFT JOIN，则后一张表的row_may_be_null会显示为true
                                "row_may_be_null": false,
                              //表的映射编号，从0开始递增
                                "map_bit": 0,
                              //依赖的映射表。主要是当使用STRAIGHT_JOIN强行控制连接顺序或者LEFT JOIN/RIGHT JOIN有顺序差别时，会在depends_on_map_bits中展示前置表的map_bit值
                                "depends_on_map_bits": []
                            }
                        ]
                    },
                    {
                      //列出所有可用的ref类型的索引。如果使用了组合索引的多个部分,则会在ref_optimizer_key_uses下列出多个元素，每个元素中会列出ref使用的索引及对应值。
                        "ref_optimizer_key_uses": [
                            {
                                "table": "`config`",
                                "field": "id",
                                "equals": "1",
                                "null_rejecting": false
                            }
                        ]
                    },
                    {
                      //用于估算需要扫描的记录数。
                        "rows_estimation": [
                            {
                                "table": "`config`",
                                "rows": 1,
                                "cost": 1,
                                "table_type": "const",
                                "empty": false
                            }
                        ]
                    },
                    {
                        "condition_on_constant_tables": "1",
                        "condition_value": true
                    },
                    {
                        "attaching_conditions_to_tables": {
                            "original_condition": "1",
                            "attached_conditions_computation": [],
                            "attached_conditions_summary": []
                        }
                    },
                    {
                      //改善执行计划
                        "refine_plan": []
                    }
                ]
            }
        },
        {
          // 执行阶段的执行过程
            "join_execution": {
                "select#": 1,
                "steps": []
            }
        }
    ]
}

        注：本文所举例子过于简单，有些情况没有覆盖到，更详细的对trace的内容分析，可参考用MySQL的optimizer_trace进行sql调优、mysql optimizertrace_MySQL 调优 | OPTIMIZER_TRACE详解

注：

  1. 本文参考：MYSQL explain详解、mysql optimizertrace_MySQL 调优 | OPTIMIZER_TRACE详解
  2. 如需转载请注明出处：https://www.cnblogs.com/zhuchenglin/p/15099036.html
posted @ 2021-08-04 15:19  lin_zone  阅读(965)  评论(0)  编辑  收藏  举报