MySQL执行计划

Posted on Edited on In , Views: Word count in article: 7.7k Reading time ≈ 7 mins.

1、概述

  • 执行计划，就是一条SQL语句，在数据库中实际执行的时候，一步步的分别都做了什么。也就是我们用EXPLAIN分析一条SQL语句时展示出来的那些信息。

  • 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，进而分析查询语句或是表结构的性能瓶颈。

  • EXPLAIN的结果总是有相同的列，每一列代表着不同的含义，可变的只是行数和内容。相关列的含义如下：

    列名 含义
    id id列，表示查询中执行select子句或操作表的顺序。
    select_type 查询类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询。
    table 表明对应行正在访问的是哪个表。
    partitions 查询涉及到的分区。
    type 访问类型，决定如何查找表中的行。
    possible_keys 查询可能使用哪些索引。
    key 实际使用的索引，如果为NULL，则没有使用索引。
    key_len 索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。
    ref 显示索引的那一列被使用。
    rows 估算出找到所需行而要读取的行数。
    filtered 返回结果的行数占读取行数的百分比，值越大越好。
    Extra 额外信息，但又十分重要。

2、具体分析

  • 建表脚本如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
    CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
    CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
    CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));


    INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));

    INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
     
    INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
       
    INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

2.1 id

  • select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

  • id列分为三种情况：

    • ①id相同，执行顺序由上至下。

      1
      explain select * from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id;

    • ②id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

      1
      explain select t2.id from t2 where id = (select t1.id from t1 where id = (select t3.id from t3 where id = 1 ));

    • ③id相同不同，同时存在。此时id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

2.2 select_type

  • 表示对应行的查询类型，是简单查询还是复杂查询，主要用于区分普通查询、联合查询、子查询等复杂的查询。列有如下值：

    • ①SIMPLE：简单查询，意味着不包括子查询或UNION。

      1
      explain select * from t1;

    • ②PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

      1
      explain select t2.id from t2 where id = (select t1.id from t1 where id = (select t3.id from t3 where id = 1 ));

    • ③SUBQUERY：在select 或where列表中包含了子查询。

      1
      explain select t2.id from t2 where id = (select t1.id from t1 where id = (select t3.id from t3 where id = 1 ));

    • ④DERIVED：表示包含在from子句的子查询中的select，MySQL会递归执行并将结果放到一个临时表中，称其为“派生表”，因为该临时表是从子查询中派生而来的。

    • ⑤UNION：若第二个select出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，则外层SELECT将被标记为：DERIVED。

      1
      explain select t2.* from t2 union select t3.* from t3;

    • ⑥UNION RESULT：从UNION表获取结果的select。

      1
      explain select t2.* from t2 union select t3.* from t3;

2.3 table

  • 表示对应行正在执行的哪张表，指代对应表名，或者该表的别名(如果SQL中定义了别名)。

2.4 type

  • 显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。

    类型 说明
    system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
    const 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量。
    eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
    ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
    fulltext 使用全文索引。
    ref_or_null 对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
    index_merge 在查询过程中需要多个索引组合使用，通常出现在有or的关键字的sql中。
    unique_subquery 该联接类型类似于index_subquery。 子查询中的唯一索引。
    index_subquery 利用索引来关联子查询，不再全表扫描。
    range 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
    index 全索引扫描，和全表扫描ALL类似，扫描表时按索引次序进行，而不是按行扫描，即：只遍历索引树。index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取。显然，index性能上优于ALL，合理的添加索引将有助于性能的提升。
    all 将遍历全表以找到匹配的行。

    • ①system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

    • ②const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量。

      1
      explain select * from t1 where t1.id = 1;

    • ③eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

      1
      explain select * from t1,t2 where t1.id = t2.id;

    • ④ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，
      它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。此类型只有当使用非唯一索引或者唯一索引的非唯一性前缀时，才会发生。

      1
      2
      3
      alter table t1 add index content_index(content);
      explain select * from t1 where content = 'abc';
      explain select * from t1,t2 where t1.content = t2.content;

    • ⑤range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

    • ⑥index：全索引扫描，和全表扫描ALL类似，扫描表时按索引次序进行，而不是按行扫描，即：只遍历索引树。index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取。显然，index性能上优于ALL，合理的添加索引将有助于性能的提升。

      1
      explain select id from t1;

    • ⑦all：将遍历全表以找到匹配的行。

      1
      explain select * from t1 where t1.content = 'abc'; # content字段上无任何索引

2.5 possible_keys

  • 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

2.6 key

  • 实际使用的索引，如果为NULL，则没有使用索引。

  • 查询中若使用了覆盖索引（select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖），则该索引和查询的select字段重叠。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;

    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
      `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `username_password_index`(`username`, `password`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES (1, 'abc', '123');
    INSERT INTO `user` VALUES (2, 'def', '456');

    SET FOREIGN_KEY_CHECKS = 1;
    1
    explain select username from user;

2.7 key_len

  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

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

2.8 ref

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

    • 测试1：

      1
      2
      explain select * from t1,t2,t3 where t1.id=t2.id and t2.id = t3.id;
      explain select id from t1 where id = 1;

    • 测试2：

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      DROP TABLE IF EXISTS `t5`;
      CREATE TABLE `t5`  (
        `col2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
        `col1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
        `id` int(10) NOT NULL,
        PRIMARY KEY (`id`) USING BTREE
      ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

      DROP TABLE IF EXISTS `t6`;
      CREATE TABLE `t6`  (
        `col2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
        `col1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
        `id` int(10) NOT NULL,
        PRIMARY KEY (`id`) USING BTREE
      ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
      1
      2
      create index col1_col2_index on t5(col1,col2);
      explain select * from t5,t6 where t5.col1 = t6.col1 and t5.col2 = 'abc';

2.9 rows

  • rows列显示MySQL认为它执行查询时必须检查的行数，越小越好。

2.10 filtered

  • 返回结果的行数占读取行数的百分比，值越大越好。

2.11 extra

  • 表示包含不适合在其他列中显示但十分重要的额外信息。

    • ①Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为”文件排序”，不满足最左前缀法则的查询会触发Using filesort。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      DROP TABLE IF EXISTS `t7`;
      CREATE TABLE `t7`  (
        `col3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
        `col2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
        `col1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
        `id` int(10) NOT NULL,
        PRIMARY KEY (`id`) USING BTREE
      ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

      ALTER TABLE t7 ADD INDEX col1_col2_col3_index(col1,col2,col3);
      1
      2
      3
      explain select col1 from t7 where col1 = 'abc' order by col3;
      explain select col1 from t7 where col1 = 'abc' order by col2, col3;
      explain select col1 from t7 order by col3;

    • ②Using temporary：使了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

      1
      2
      explain select col2 from t7 where col1 in ('a','b','c') group by col2;
      explain select col2 from t7 where col1 in ('a','b','c') group by col1,col2,col3;

    • ③USING index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错。

      • 如果同时出现using where，意味着无法直接通过索引查找来查询到符合条件的数据，而是通过全索引扫描（或者表扫描）来实现sql语句执行。

        1
        explain select col1,col2,col3 from t7 where col2 = 'abc';

      • 如果没有同时出现using where，意味着通过索引查找就能直接找到符合条件的数据，并且无须回表。

        1
        explain select col1,col2,col3 from t7 where col1 = 'abc';

    • ④Using where：意味着通过全表扫描的方式进程where条件的过滤，即也就是没有可用的索引查找。有两种情况：

      • 查询的列未被索引覆盖且where筛选条件不满足联合索引的最左前缀匹配原则。

      • 查询的列未被索引覆盖且where筛选条件为非索引列。

    • ⑤impossible where：where子句的值总是false，不能用来获取任何元组。

    • ⑥Using index condition：查询的列不全在索引中但where条件中是一个前导列的范围。