关于mysql:MySQL-Explain详解

35次阅读

共计 5882 个字符，预计需要花费 15 分钟才能阅读完成。

概述

explain 是 MySQL 中的一个关键字，能够用于模仿优化器执行 SQL 语句，剖析你的 SQL 的性能瓶颈。

应用时只需将该关键字加在 sql 头部，如

explain select * from employees where name = 'zhangsan'

案例

explain 返回的后果有许多字段，咱们又当如何了解呢？

以下有张员工记录表，联结索引：name_age_position

CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT ''COMMENT' 姓名 ',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT ''COMMENT' 职位 ',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职工夫',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

执行 sql

explain select * from employees where name = 'zhangsan'

explain 后果

当初，能够简略解读以下其中几个列

1、select_type: 查问类型，SIMPLE：简略查问

2、table: 查问的表

3、possible_keys: 执行该 sql 可能会用的索引，留神是可能，该列意义不大。

4、key: 执行该 sql 理论应用的索引，有没有走索引次要看该列

5、key_len: 索引所用的字节数(长度)，通过该列能够判断应用了联结索引的哪几个字段。

没错，索引是有长度的，索引的长度等于该索引蕴含字段长度之和，具体计算规定如下：

  • 字符串

    • char(n)：n 字节长度
    • varchar(n)：如果是 utf-8，则长度 3n + 2 字节，加的 2 字节用来存储字符串长度

  • 数值类型

    • tinyint：1 字节
    • smallint：2 字节
    • int：4 字节
    • bigint：8 字节

  • 工夫类型

    • date：3 字节
    • timestamp：4 字节
    • datetime：8 字节
  • 如果字段容许为 NULL，须要 1 字节记录是否为 NULL

案例表中的字段 name 类型为varchar(24)，长度即为：3*24+2=74，合乎 explain 中的后果。

如果咱们应用该 sql 语句

explain select * from employees where name = 'zhangsan' and age = 18 and position = 'dev';

name类型为varchar(24)，长度：3*24+2=74

age类型为int, 长度：4

position类型为varchar(20), 长度：3*20+2=62

74+4+62=140

简单查问

接下来，咱们看一个稍显简单的例子

explain 
select * from employees t1  
join (select id from employees order by `name` limit 10000, 10) t2 
ON t1.id = t2.id;

该语句含意为：查问以 name 字段排序的第 10000~10010 行记录

至于为什么不间接 select * from employees order by name limit 10000, 10 会在后续索引优化中解释

1、id 列：该列并非单纯指返回后果的 id，因为你可能也留神到了，第一行和第二行 id 都为 1

id 列的具体含意为：id 列的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的程序是按 select 呈现的程序增长的。id 列越大执行优先级越高，id 雷同则从上往下执行，id 为 NULL 最初执行。

2、select_type:

  • primary：简单查问中最外层的 select
  • derived：蕴含在 from 子句中的子查问。MySQL 会将后果寄存在一个长期表中，也称为派生表（derived 的英文含意）

3、table 列：deriven2 示意：当 from 子句中有子查问时，table 列是 <derivenN> 格局，示意以后查问依赖 id= N 的查问，于是先执行 id= N 的查问。

意思就是第一行依赖于第三行 id= 2 的查问

4、type 列

  • ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。
  • eq_ref：primary key或 unique key 索引的所有局部被连贯应用，最多只会返回一条符合条件的记录。
  • index：扫描全索引，个别是扫描某个二级索引

5、ref 列: 这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量

6、row 列：mysql预计 要读取并检测的行数

6、Extra 列：

  • Using index：应用笼罩索引

    笼罩索引定义：mysql 执行打算 explain 后果里的 key 有应用索引，如果 select 前面查问的字段都能够从这个索引的树中获取，这种状况个别能够说是用到了笼罩索引

联合以上列的解释，该 sql 语句的执行打算大略能够这样论述：

1、先应用联结索引执行 select id from employees order by name limit 10000, 10 语句，将查问后果 id 放入长期表中。

2、全表扫描该长期表

3、应用 id 关联查问select * from employees t1

对于 explain 的用法大略就是这些，更为具体的实践内容可查看 Explain 详解和索引最佳实际和官网文档

trace 工具

为什么有时候 explain 的后果和咱们的冀望不同？

这是因为 MySQL 优化器会进行成本计算，MySQL 感觉不走索引还会快一些。

比方该 sql

explain select * from employees3 where name > 'bb';

能够看到在执行打算中不会应用任何索引

对于执行老本，咱们能够通过 trace 工具查看

-- 开启 trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
-- 执行 sql
explain select * from employees3 where name > 'bb';
-- 查看后果
SELECT * FROM information_schema.OPTIMIZER_TRACE;

大抵内容和解释如下

{
  "steps": [
    {
      "join_preparation": {  -- 第一阶段：SQL 筹备阶段，格式化 sql
        "select#": 1,
        "steps": [
          {"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` >'LiLei')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {  -- 第二阶段：SQL 优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": { -- 条件解决
              "condition": "WHERE",
              "original_condition": "(`employees`.`name` >'LiLei')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`employees`.`name` >'LiLei')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`employees`.`name` >'LiLei')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`employees`.`name` >'LiLei')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {"substitute_generated_columns": {} /* substitute_generated_columns */
          },
          {
            "table_dependencies": [  -- 表依赖详情
              {
                "table": "`employees`",
                "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": "`employees`",
                "range_analysis": {
                  "table_scan": {  -- 全副扫描状况
                    "rows": 100127, -- 扫描行数
                    "cost": 20380  -- 查问老本
                  } /* table_scan */,
                  "potential_range_indexes": [  -- 查问可能应用的索引
                    {
                      "index": "PRIMARY",  -- 主键索引
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_age_position",  -- 辅助索引
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "position",
                        "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_name_age_position",
                        "ranges": ["LiLei < name"  -- 索引应用范畴] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,  -- 应用该索引获取的记录是否依照主键排序
                        "using_mrr": false,  
                        "index_only": false,  -- 是否应用笼罩索引
                        "rows": 50063,  -- 索引扫描行数
                        "cost": 60077,  -- 索引查问老本
                        "chosen": false, -- 是否抉择
                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {"plan_prefix": [] /* plan_prefix */,
                "table": "`employees`",
                "best_access_path": {  -- 最优拜访门路
                  "considered_access_paths": [ -- 最终抉择的拜访门路
                    {
                      "rows_to_scan": 100127,
                      "access_type": "scan",  -- 全表扫描
                      "resulting_rows": 100127,
                      "cost": 20378,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100127,
                "cost_for_plan": 20378,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {"original_condition": "(`employees`.`name` >'LiLei')",
              "attached_conditions_computation": [ ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "(`employees`.`name` >'LiLei')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {"table": "`employees`"}
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [] /* steps */} /* join_execution */
    }
  ] /* steps */
}

如果我的文章对你有所帮忙，还请帮忙 点赞、珍藏、转发 一下，你的反对就是我更新的能源，非常感谢！

追更，想要理解更多精彩内容，欢送关注公众号：程序员阿紫

集体博客网站：https://zijiancode.cn

正文完
mysql
发表至： mysql
2023-01-29
 0
关于mysql:这个大表走索引字段查询的-SQL-怎么就成全扫描了我TM人傻了
关于mysql:2021年依然火热SpringBoot来实现MySQL读写分离技术-你确定不学一学吗
MySQL-FROMUNIXTIME-函数
关于mysql:又是一条慢-SQL-改写拿捏