Explain执行计划

我不是女神ヾ 2023-01-01 10:56 163阅读 0赞

## 简介 ##

当`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");

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70][]

### 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 );

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 1][]

### 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;

![20210102114305590.png][]

*  **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;

![2021010211481621.png][]

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

EXPLAIN select o.one\_id from one o where o.one\_name = "我是第一表2" ;

![20210102131709267.png][]

*  **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;

![20210102132001374.png][]

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

EXPLAIN   
SELECT o.one\_id FROM one o WHERE o.one\_id> 1;

![20210102164930203.png][]

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

EXPLAIN SELECT three\_id from three ;

![20210102165215954.png][]

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

EXPLAIN SELECT \* from two ;

![20210102165434818.png][]

### possible\_keys ###

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

### key ###

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

### key\_len ###

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

### ref ###

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

### rows ###

最大扫描的列数

### filtered ###

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

### Extra ###

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

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

EXPLAIN SELECT one\_id from one ;

![20210102170826717.png][]

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

EXPLAIN SELECT one\_name from one where create\_time ='2021-01-02';

![2021010217223698.png][]

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

EXPLAIN SELECT create\_time from one where one\_id in (1,2) GROUP BY create\_time;

![20210102173206415.png][]

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

EXPLAIN SELECT one\_id from one  ORDER BY create\_time;

![20210102172704347.png][]

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

EXPLAIN SELECT one\_name from one o,two t where o.create\_time = t.create\_time;

![20210102173442169.png][]

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

EXPLAIN SELECT one_name from one WHERE 1=2;

![20210102173556311.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70]: /images/20221120/f0ab1c666cb74ec4b8c8e36449d28223.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0ZXZlX2ZyYW5r_size_16_color_FFFFFF_t_70 1]: /images/20221120/9deb7da6c85841caaf4b38fab35834e7.png
[20210102114305590.png]: /images/20221120/dbf796314762429d8026e1aa22388b2f.png
[2021010211481621.png]: /images/20221120/f28393e5c9f04faebb861ed143beecc6.png
[20210102131709267.png]: /images/20221120/e247b07f746246338e38a771e1c2510a.png
[20210102132001374.png]: /images/20221120/45382fd5d2a74d219590ffcb556c6f7b.png
[20210102164930203.png]: /images/20221120/616456e1cedf438a9dff42429cc62385.png
[20210102165215954.png]: /images/20221120/7e29a9b274e341288bb208a0e1139fcb.png
[20210102165434818.png]: /images/20221120/b5ec2fd70d5c4c97ac2c8b41551c19f2.png
[20210102170826717.png]: /images/20221120/a459ee6599154c9c8d0dd3d12bc12923.png
[2021010217223698.png]: /images/20221120/14990e4cb0364458bb57a30bdce6c1e0.png
[20210102173206415.png]: /images/20221120/a8838cfad02a4b87a4af2fa018fa1c38.png
[20210102172704347.png]: /images/20221120/b02ae87a64ad4aa8b760926daa0f1da7.png
[20210102173442169.png]: /images/20221120/3895df5aabab42319d3fc69d4e33ba76.png
[20210102173556311.png]: /images/20221120/fed4c29a671e40868c11b6740d00558d.png