Explain执行计划

简介

当Explain 与 SQL语句一起使用时，MySQL 会显示来自优化器关于SQL执行的信息。

/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 50731
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50731
 File Encoding         : 65001

 Date: 02/01/2021 17:38:56
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for one
-- ----------------------------
DROP TABLE IF EXISTS `one`;
CREATE TABLE `one` (
  `one_id` tinyint(5) NOT NULL,
  `two_id` tinyint(5) DEFAULT NULL,
  `one_name` varchar(20) DEFAULT NULL,
  `create_time` date DEFAULT NULL,
  PRIMARY KEY (`one_id`),
  KEY `idx_name` (`one_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of one
-- ----------------------------
BEGIN;
INSERT INTO `one` VALUES (1, 1, '我是第一表1', '2021-01-02');
INSERT INTO `one` VALUES (2, 2, '我是第一表2', '2021-01-02');
COMMIT;

-- ----------------------------
-- Table structure for three
-- ----------------------------
DROP TABLE IF EXISTS `three`;
CREATE TABLE `three` (
  `three_id` tinyint(5) NOT NULL,
  `two_id` tinyint(5) DEFAULT NULL,
  `three_name` varchar(20) DEFAULT NULL,
  `create_time` date DEFAULT NULL,
  PRIMARY KEY (`three_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of three
-- ----------------------------
BEGIN;
INSERT INTO `three` VALUES (1, 1, '我是第三表1', '2021-01-02');
INSERT INTO `three` VALUES (2, 2, '我是第三表2', '2021-01-02');
COMMIT;

-- ----------------------------
-- Table structure for two
-- ----------------------------
DROP TABLE IF EXISTS `two`;
CREATE TABLE `two` (
  `two_id` tinyint(5) NOT NULL,
  `three_id` tinyint(20) DEFAULT NULL,
  `two_name` varchar(255) DEFAULT NULL,
  `create_time` date DEFAULT NULL,
  PRIMARY KEY (`two_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of two
-- ----------------------------
BEGIN;
INSERT INTO `two` VALUES (1, 1, '我是第二表1', '2021-01-02');
INSERT INTO `two` VALUES (2, 2, '我是第二表2', '2021-01-02');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

执行计划

id

id的值越大，代表优先级越高，越先执行。

EXPLAIN 
SELECT*FROM one o WHERE o.two_id=(
SELECT t.two_id FROM two t WHERE t.three_id=(
SELECT r.three_id FROM three r WHERE r.three_name='我是第三表2')) AND o.one_id IN (
SELECT one_id FROM one WHERE o.one_name="我是第一表2");

select_type

• simple: 简单的select查询,查询中不包含子查询或union查询
• primary: 查询中若包含任何复杂的子部分,最外层查询则被标记为primary
• subquery 在select 或where 列表中包含了子查询
• derived 在from列表中包含的子查询被标记为derived,mysql会递归这些子查询,把结果放在临时表里
• union 做第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为derived
• union result 从union表获取结果的select

EXPLAIN SELECT
	t.two_name,
	( SELECT one.one_id FROM one ) o 
FROM
	( SELECT two_id, two_name FROM two WHERE two_name = '' ) t UNION
	( SELECT r.three_name, r.three_id FROM three r );

table

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

partitions

查询时匹配到的分区信息，对于非分区表值为NULL

type

查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All,一般情况下,得至少保证达到range级别,最好能达到ref

• system:该表只有一行（=系统表）。这是const联接类型的特例 。
• const:该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。 const表非常快，因为它们只能读取一次。 

EXPLAIN SELECT * from three where three_id=1;

• eq_ref:当连接使用索引的所有部分且索引为PRIMARY KEY或UNIQUE NOT NULL index时使用。

EXPLAIN SELECT o.one_name FROM one o,two t WHERE o.two_id=t.two_id;

• ref:非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体

EXPLAIN select o.one_id from one o where o.one_name = "我是第一表2" ;

• ref_or_null:这种连接类型类似于 ref，但是除了MySQL还会额外搜索包含NULL值的行

EXPLAIN 
SELECT o.one_id FROM one o WHERE o.one_name="我是第一表2" OR o.one_name IS NULL;

• range:使用索引选择行，仅检索给定范围内的行。可使用 =， <>， >， >=， <， <=， IS NULL， <=>， BETWEEN， LIKE，或 IN()

EXPLAIN 
SELECT o.one_id FROM one o WHERE o.one_id> 1;

• index:index类型只遍历索引树,这通常比All快,因为索引文件通常比数据文件小,index是从索引中读取,all从硬盘中读取

EXPLAIN SELECT three_id from three ;

• all:全表扫描,是最差的一种查询类型

EXPLAIN SELECT * from two ;

possible_keys

显示可能应用在这张表中的索引,一个或多个,查询到的索引不一定是真正被用到的

key

实际使用的索引,如果为null,则没有使用索引

key_len

表示查询用到的索引长度（字节数），原则上长度越短越好

ref

显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值

rows

最大扫描的列数

filtered

表里符合条件的记录数的百分比

Extra

重要的额外信息，特别注意损耗性能的两个情况，using filesort和using temporary

• Using index:查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态

EXPLAIN SELECT one_id from one ;

• Using where:查询时未找到可用的索引，进而通过where条件过滤获取所需数据

EXPLAIN SELECT one_name from one where create_time ='2021-01-02';

• Using temporary :使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by

EXPLAIN SELECT create_time from one where one_id in (1,2) GROUP BY create_time;

• Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为"文件排序"

EXPLAIN SELECT one_id from one  ORDER BY create_time;

• Using join buffer:在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果

EXPLAIN SELECT one_name from one o,two t where o.create_time = t.create_time;

• impossible where:where子句的值false,没有符合条件的行

EXPLAIN SELECT one_name from one WHERE 1=2;