首页 / MYSQL / Mysql系列-性能优化神器EXPLAIN使用介绍及分析

Mysql系列-性能优化神器EXPLAIN使用介绍及分析

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Mysql系列-性能优化神器EXPLAIN使用介绍及分析，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12275字，纯文字阅读大概需要18分钟。

内容图文

* from user_info WHERE id < 300;

下面是我结合我自己创建的表以及执行相关sql语句总结的相关知识点。

准备

为了接下来方便演示 EXPLAIN 的使用, 首先我们需要建立两个测试用的表, 并添加相应的数据:

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers`  (
  `customerNumber` int(11) NOT NULL,
  `customerName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `contactLastName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `contactFirstName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `addressLine1` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `addressLine2` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `state` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `postalCode` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `country` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `salesRepEmployeeNumber` int(11) NULL DEFAULT NULL,
  `creditLimit` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`customerNumber`) USING BTREE,
  INDEX `salesRepEmployeeNumber`(`salesRepEmployeeNumber`) USING BTREE,
  INDEX `customers_idx_combine_1`(`customerName`, `phone`, `customerNumber`) USING BTREE,
  CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建表成功后，插入一些测试数据。

EXPLAIN 输出格式

EXPLAIN 命令的输出内容大致如下:

mysql> EXPLAIN SELECT * FROM customers WHERE customerName=‘Herkku Gifts‘ AND phone=‘+47 2267 3215‘ AND customerNumber=167;
+----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys                   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | const | PRIMARY,customers_idx_combine_1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

各列的含义如下:

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符
select_type: SELECT 查询的类型
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引
ken_len: 表示查询优化器使用了索引的字节数
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息

接下来我们详细看一下每个字段的具体含义：

select_type

select_type 表示了查询的类型, 它的常用取值有:

SIMPLE：表示此查询不包含 UNION 查询或子查询
PRIMARY：表示此查询是最外层的查询
UNION：表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION：UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT：UNION 的结果
SUBQUERY：子查询中的第一个 SELECT
DEPENDENT SUBQUERY：子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果
DERIVED：当子查询是from子句时，其select_type为DERIVED

最常见的查询类别应该是 SIMPLE 了, 比如当我们的查询没有子查询, 也没有 UNION 查询时, 那么通常就是 SIMPLE 类型, 例如：

1.SIMPLE 情况：

mysql> EXPLAIN SELECT * FROM customers WHERE customerName=‘Herkku Gifts‘ AND phone=‘+47 2267 3215‘ AND customerNumber=167;
+----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys                   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | const | PRIMARY,customers_idx_combine_1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2.UNION情况

　　当通过union来连接多个查询结果时，第二个之后的select其select_type为UNION

mysql> EXPLAIN SELECT customerNumber FROM customers WHERE customerNumber IN (125,144) UNION SELECT customerNumber FROM customers WHERE country IN (‘USA‘,‘France‘);
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY      | customers  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using index |
|  2 | UNION        | customers  | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |  122 |    20.00 | Using where              |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary          |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

3.DEPENDENT UNION与DEPENDENT SUBQUERY

　　当union作为子查询时，其中第二个union的select_type就是DEPENDENT UNION。第一个子查询的select_type则是DEPENDENT SUBQUERY

mysql> EXPLAIN SELECT * FROM customers WHERE customerNumber IN (SELECT customerNumber FROM customers WHERE customerNumber IN (125,144) UNION SELECT customerNumber FROM customers WHERE country IN (‘USA‘,‘France‘));
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type        | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY            | customers  | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |  122 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | customers  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 |   100.00 | Using where; Using index |
|  3 | DEPENDENT UNION    | customers  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 |    20.00 | Using where              |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary          |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+--------------------------+
4 rows in set, 1 warning (0.00 sec)

4.SUBQUERY

　　子查询中的第一个select其select_type为SUBQUERY

mysql> EXPLAIN SELECT * FROM customers WHERE customerNumber=(SELECT customerNumber FROM customers WHERE customerNumber=124);
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | customers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | customers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM customers WHERE customerNumber in (SELECT customerNumber FROM customers WHERE customerNumber=124);
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | customers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

思考下为什么一个用了in一个用了=反而select_type就不一样了？？？？

5.DERIVED

mysql> EXPLAIN SELECT * FROM (SELECT COUNT(*) FROM customers WHERE customerNumber=124) a;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  2 | DERIVED     | customers  | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

table

表示查询涉及的表或衍生表

partitions

匹配的分区

type

type 字段比较重要, 显示连接使用了何种类型。从最好到最差的连接类型依次分别为const、eq_reg、ref、range、index和ALL 它提供了判断查询是否高效的重要依据依据。

通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等。

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。

type 常用的取值有:

　　system: 表中只有一条数据. 这个类型是特殊的 const 类型

　　const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可。

　　例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的

mysql> EXPLAIN SELECT * FROM customers WHERE customerName=‘Herkku Gifts‘ AND phone=‘+47 2267 3215‘ AND customerNumber=167;
+----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys                   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | const | PRIMARY,customers_idx_combine_1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Mysql系列-性能优化神器EXPLAIN使用介绍及分析

标签：使用介绍成功命令 xpl ddr reference cte state 接下来

本文系统来源：https://www.cnblogs.com/MrFiona/p/10925919.html

内容总结

以上是互联网集市为您收集整理的Mysql系列-性能优化神器EXPLAIN使用介绍及分析全部内容，希望文章能够帮你解决Mysql系列-性能优化神器EXPLAIN使用介绍及分析所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/507602.html

来源：【匿名】

【上一篇】MySQL高性能SQL探索与笔录【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【Mysql系列-性能优化神器EXPLAIN使用介绍及分析】教程文章相关的互联网学习教程文章

MySQL执行计划explain的key_len解析

作者：沃趣科技高级数据库专家邱文辉前言当用Explain查看SQL的执行计划时，里面有列显示了 key_len 的值，根据这个值可以判断索引的长度，在组合索引里面可以更清楚的了解到了哪部分字段使用到了索引。下面演示中，表结构的合理性这边暂且不说，只是证明一下索引长度的计算方法。目前大部分博文是字符类型的索引长度计算方法，下面列举几个类型的索引长度计算方法：1、整数类型(dg1)root@127.0.0.1 [mytest]> desc table_key;+-...

mysql explain（转）

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain就可以了：如：explain select surname,first_name form a,b where a.id=b.id EXPLAIN列的解释：table：显示这一行的数据是关于哪张表的type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALLpossible_keys：显示可能应用...

为什么rows这么大，在mysql explain中---写在去acumg听讲座的前一夜【代码】

这周五下班前，发现了一个奇怪问题，大概是这个背景一张表，结构为CreateTable: CREATETABLE `out_table` (`id` int(11) NOTNULL AUTO_INCREMENT,`name` varchar(20) NOTNULL,PRIMARYKEY (`id`) ) ENGINE=Innodb AUTO_INCREMENT=36865DEFAULT CHARSET=latin1总共有37K rows的数据，数据大概是这样+----+------+| id | name |+----+------+|1| a ||2| b ||3| c ||4| D ||5| c ||6| c ||7| c ||8| c ||9| c ...

基于JRebel开发的MySQL Explain插件【代码】【图】

前言我们在使用数据库时，为了使业务系统性能达到最优，往往都需要避免慢SQL查询，不能等到线上告警了再排查是否为慢SQL导致。在开发阶段，每个开发人员就应该针对自己写的SQL看是否可能为慢SQL，从而进行相应的SQL优化和索引优化等。项目中可能用的是mybatis，也可能用的jpa，针对写的SQL如何能自动执行explain sql语句来查看每条SQL的执行计划呢？于是有了本篇文章，带你实现这样一个简单好用通用的插件。探索针对这个问题，一个...

( 转 ) MySQL高级之 explain执行计划详解【图】

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。explain执行计划包含的信息其中最重要的字段为：id、type、key、rows、Extra各字段详解idselect查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序三种情况： 1、id相同：执行顺序由上至下 2、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 3、id相同...

[MySQL] explain执行计划解读【代码】【图】

Explain语法EXPLAIN SELECT …… 变体： 1. EXPLAIN EXTENDED SELECT …… 将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句 2. EXPLAIN PARTITIONS SELECT …… 用于分区表的EXPLAIN执行计划包含的信息 id包含一组数字，表示查询中执行select子句或操作表的顺序 id相同，执行顺序由上至下如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 id如果相同，可以认为是一组，...

mysql中EXPLAIN 的作用【代码】

mysql的explain关键参数

一、关键参数 typesystem最好，到ref，一般就认为达标了。ALL和index最差，必须要进行优化。联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: system: 表仅有一行(=系统表)。这是const联接类型的一个特例。const: 表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!eq_ref: 对于每个来自于前面的表的行组合,从该表中读取一行...

Mysql Explain 详解

MYSQL EXPLAIN 中的KEY_LEN的说明

对于explain extended 查看执行计划里面的一些信息作为一个DBA还是必须掌握的。参考博文：http://www.cnblogs.com/xuanzhi201111/p/4554769.html环境： MySQL5.6.36默认字符集： utf8 一、前置回顾：1、数值型的字段长度字段类型长度 UNSIGNED SIGNED有符号型适用场合tinyint: 1bytes 2^8-1 0-255 -128~127 小整数值smallint: 2bytes 2^16-1 0-65535 -327...

MySQL——通过EXPLAIN分析SQL的执行计划

在MySQL中，我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。下面分别对EXPLAIN命令结果的每一列进行说明：select_type:表示SELECT的类型，常见的取值有：类型说明SIMPLE简单表，不使用表连接或子查询PRIMARY主查询，即外层的查询UNIONUNION中的第二个或者后面的查询语句SUBQUERY子查询中的第一个table:输出结果集的表（表别名）type:表示MySQL在表中找到所需行的方...

mysql explain

语法：explain select ....变体：1.explain extended select ....将执行计划“反编译”成select语句；运行show warnings 可以得到被mysql优化器优化后的语句2.explain partitions select ...用于分区表的explain 运行结果含义：type： all ，index，range，ref，eq_ref,const,system null 从左到右，最差到最好； all: full table scan ;mysql将遍历全表以找到匹配的行；index ： index scan; index 和 all的区别在于index类型只...

MySql下的Explain 中的TYPE, Extra

TYPE: system > const > eq_ref > ref > range > index > allEXTRA: Using where(标明使用了where过滤);Using temporary(九死无生);Using filesort(九死一生)；Using index(发财了)；Using join buffer(表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些);Impossible where(where子句的值总是false，不能获取任何值)；原文：https://www.cnblogs.com/effortfu/p/12180...

MySQL基础优化之explain使用【代码】

explain 详解：作用：主要用来调取语句的执行计划，主要是判断语句是否走索引。 explain select stu_name,gender,age from stu where gender=‘F‘ and age <20; mysql> explain select name,gender,age from test where gender=‘F‘ and age <20; +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | ke...

mysql explain结果含义【图】

在SQL语句前面加上EXPLAIN即可各字段含义idSELECT识别符。这是SELECT的查询序列号select_typeSELECT类型,可以为以下任何一种:SIMPLE:简单SELECT(不使用UNION或子查询)PRIMARY:最外面的SELECTUNION:UNION中的第二个或后面的SELECT语句DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询UNION RESULT:UNION 的结果SUBQUERY:子查询中的第一个SELECTDEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询DERI...

首页 / MYSQL / Mysql系列-性能优化神器EXPLAIN使用介绍及分析

Mysql系列-性能优化神器EXPLAIN使用介绍及分析

内容导读

内容图文

准备

EXPLAIN 输出格式

select_type

table

partitions

type

内容总结

内容备注

内容手机端

【Mysql系列-性能优化神器EXPLAIN使用介绍及分析】教程文章相关的互联网学习教程文章

MySQL执行计划explain的key_len解析

mysql explain（转）

为什么rows这么大，在mysql explain中---写在去acumg听讲座的前一夜【代码】

基于JRebel开发的MySQL Explain插件【代码】【图】

( 转 ) MySQL高级之 explain执行计划详解【图】

[MySQL] explain执行计划解读【代码】【图】

mysql中EXPLAIN 的作用【代码】

mysql的explain关键参数

Mysql Explain 详解

MYSQL EXPLAIN 中的KEY_LEN的说明

MySQL——通过EXPLAIN分析SQL的执行计划

mysql explain

MySql下的Explain 中的TYPE, Extra

MySQL基础优化之explain使用【代码】

mysql explain结果含义【图】

MYSQL - 相关标签

性能 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程