BH4FFUBH4FFU

程序员
业余无线电爱好者

史上最强 | Mysql EXPLAIN详解 - 聊聊Type

本文内容较长,大约需要15分钟。建议点支烟,泡壶茶慢慢品。

0x00 EXPLAIN

平时开发中,我们经常使用到EXPLAIN这个命令去查看一些sql的执行计划。看一下sql的执行是否符合我们的预期,比如,有没有按照我们的设计走索引,有没有做全表扫描等等。EXPLAIN提供的信息是我们优化sql的基础和重要参考。
EXPLAIN命令执行后的效果如下:

  • id 数字越大越先执行;相同的话,从上往下依次执行。
  • select_type 查询类型,比如SIMPLE(简单查询)、PRIMARY(最外层查询)等。
  • table 表示该行访问的表的名字/别名。
  • partitions 当前记录所在的分区,对于未分区的表,显示为null。
  • type 连接类型,比如const、ref、eq_ref等。我更倾向于理解为访问类型。本文重点介绍的对象
  • possible_keys 展示当前查询可以使用哪些索引。
  • key 表示实际使用的索引。
  • key_len 索引使用的字节数。
  • ref 表示将哪个字段或常量和key列所使用的字段进行比较。
  • rows mysql估算会扫面的行数,数值越小越好。
  • filtered 表示符合查询条件的数据百分比,最大100。
  • Extra 本次查询的附加信息,比如常见的using index、using where及using filesort等。

我们今天重点介绍一下Type这一列,主要介绍一下type共有哪些取值,每种取值的含义及例子。

基于mysql 8.0.17

0x01 准备工作

  1. 新建student表,并插入基础数据

    -- 新建student表
    CREATE TABLE `student`  (
      `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(100)  NOT NULL DEFAULT '' COMMENT '名字',
      `age` smallint(3) NOT NULL COMMENT '年龄',
      `comments` text  NOT NULL COMMENT '备注',
      `gmt_create` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
      `gmt_modified` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `idx_student_name`(`name`) USING BTREE COMMENT 'name索引',
      INDEX `idx_student_age`(`age`) USING BTREE COMMENT 'age索引',
      FULLTEXT INDEX `ft_student_comments`(`comments`) COMMENT 'comments 索引'
    );
    
    -- 插入基础数据
    INSERT INTO `student` VALUES (1, 'byron', 10, '1', '2021-05-06 23:18:50.085164', '2021-05-06 23:18:50.085164');
    INSERT INTO `student` VALUES (2, 'charles', 11, '2', '2021-05-06 23:19:03.932634', '2021-05-06 23:19:03.932634');
    INSERT INTO `student` VALUES (3, 'johnny', 12, '3', '2021-05-06 23:19:14.395957', '2021-05-06 23:19:14.395957');
    
  2. 新建student_score表,并插入基础数据

    -- 新建student_score表
    CREATE TABLE `student_score`  (
      `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
      `student_id` bigint(20) UNSIGNED NOT NULL,
      `score` int(10) NOT NULL,
      `gmt_create` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
      `gmt_modified` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE INDEX `uk_student_score_student_id`(`student_id`) USING BTREE COMMENT 'student_id唯一索引'
    ) ENGINE = InnoDB;
    
    -- 插入基础数据
    INSERT INTO `student_score` VALUES (1, 1, 10, '2021-05-06 23:19:50', '2021-05-06 23:19:50');
    INSERT INTO `student_score` VALUES (2, 2, 11, '2021-05-06 23:19:59', '2021-05-06 23:19:59');
    INSERT INTO `student_score` VALUES (3, 3, 12, '2021-05-06 23:20:05', '2021-05-06 23:20:05');
    
  3. 新建student_interest表,并插入基础数据

    -- 新建student_interest表
    CREATE TABLE `student_interest`  (
      `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
      `student_id` bigint(20) NOT NULL,
      `student_name` varchar(100) NOT NULL,
      `interest` varchar(50) NOT NULL,
      `gmt_create` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
      `gmt_modified` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE     CURRENT_TIMESTAMP(0),
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `idx_student_interest_student_id`(`student_id`) USING BTREE,
      INDEX `idx_student_interest_student_name`(`student_name`) USING BTREE
    ) ENGINE = InnoDB;
    
    -- 插入基础数据
    INSERT INTO `student_interest` VALUES (1, 1, 'byron', '足球', '2021-05-07 15:53:12', '2021-05-07 15:53:12');
    INSERT INTO `student_interest` VALUES (2, 1, 'byron', '篮球', '2021-05-07 15:53:17', '2021-05-07 15:53:17');
    INSERT INTO `student_interest` VALUES (3, 2, 'byron', '乒乓球', '2021-05-07 15:53:37', '2021-05-07 15:53:37');
    

0x03 type详解

system

The table has only one row (= system table). This is a special case of the const join type.

解读
表中只有一条记录,这是const的特殊场景。需要注意的是,当存储引擎为MyISAM时可以复现,为InnoDB时看到的并不是system

举例

-- 表中仅保留id,并将student表的存储引擎更换为MyISAM
explain select * from student

const

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.
const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values.

解读
在开始查询读取时,表里面最多只有一条匹配的记录。这条记录各个列的值可以被优化器的其余部分视为常量,因为仅有一条记录。const表非常快,因为它们只被读取一次。
当您将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时,将使用const。

举例

-- id为主键
EXPLAIN select * from student where id = 1;

-- student_id为唯一索引
EXPLAIN select * from student_score where student_id = 1;

eq_ref

One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.
eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table.

解读
在连表查询的场景下,不妨假设t1 inner join t2(或者官方文档中的from t1, t2,但不是left join)。eq_ref讲的是,从t2中查询出来的每条记录,在与之关联的t1表中仅有一条记录, 那么t1的type就是eq_ref。这是除了sytem和const外最好的连接类型。当连接使用索引的所有部分且索引为PRIMARY KEY或UNIQUE NOT NULL索引时使用。
eq_ref可用于使用=运算符进行比较的索引列。比较值可以是常量,也可以是使用在此 table 之前读取的 table 中列的 table 达式。

举例

-- 官方文档中的查询
EXPLAIN select * from student, student_score where student.id = student_score.student_id;

-- 与之等价的查询
EXPLAIN select * from student s inner join student_score ss on ss.student_id = s.id;

关于left join

The way LEFT JOIN is executed in MySQL is by executing two nested loops: in the outer loop it traverses the left table, and in the inner loop it traverses the inner table. If inner table has many rows, and there's an index on the columns from the ON condition, it will use that index to speed up the inner loop. Note that index on the outer table cannot help speed up the outer loop because it needs to traverse all the rows of the left table no matter what.

In the explain you provided the right table has only a single row. MySQL smartly concludes that it does not need any index, because full table scan on that table will be just as fast (or even faster due to avoiding overhead associated with using the index) as using the index, so it chooses to perform a full scan of the table (that is, a full scan of a single row) on each iteration.

If your right table had more rows, then MySQL would have chosen to use that index.

Finally note, that if your join is an INNER JOIN, not a LEFT JOIN, MySQL might be smart enough to reverse the order of the tables, and use your feature_translations as the outer table, in which case it will be able to leverage the index on the inner Feature table.

参考:https://stackoverflow.com/questions/33763883/mysql-left-join-not-using-foreign-key

简单讲,left join在mysql中使用两层嵌套循环查询。left join前(左表)的table作为外层循环的表,left join后面(右表)的table作为内层循环的表。内层循环的表可以使用索引加速,外层循环的表的索引对加速查询没有任何意义,因为,无论如何外层循环的表都需要把所有的数据都查询出来。(注:需要注意的是,这里讲的是没有where条件的场景。如果有where条件,外层循环还是可以使用索引加速查询的)。

另外,处于内层循环的表一定会使用索引么?非也!这和表中的数据量有关,如果仅有一条或者几条记录,mysql是认为扫描全表更快。

ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

ref can be used for indexed columns that are compared using the = or <=> operator.

解读
两种场景会使用到ref这个type

  • 联表查询。不妨假设t1 inner join t2, 从t2中查询出来的每条记录,在与之关联的t1表中不只有一条记录, 那么t1的type就是ref。
  • 使用普通索引/前缀索引等非主键/非唯一索引的情况下的单表查询。
    如果只有少量匹配的行数,这也是也给好的联接类型。

举例

-- 关联查询
EXPLAIN select * from student, student_interest where student.name = student_interest.student_name;

-- 简单查询
EXPLAIN select * from student where name = 'byron';

fulltext

The join is performed using a FULLTEXT index.

解读
使用全文索引的联接类型

举例

-- fulltext的正确食用方法
EXPLAIN select * from student where MATCH(comments)against('1' IN BOOLEAN MODE)

-- fulltext的错误示范
EXPLAIN select * from student where comments = '1'

ref_or_null

This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries.

解读
比ref多了一个null的场景。

举例

-- 需要将表中name字段临时改为允许为null
EXPLAIN select * from student where name = 'byron' or name is null;

index_merge

This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used.

解读
这种联接类型意味着MySQL使用了索引合并。在此情况下,查询计划中,key这一列会列出所有使用到的索引,key_len这一类,会列出每个索引用到的最长的key。

举例
往student表中新插入记录

INSERT INTO `student` VALUES (4, 'oscar', 10, '4', '2021-05-06 23:19:14.395957', '2021-05-06 23:19:14.395957');
INSERT INTO `student` VALUES (5, 'byron', 18, '5', '2021-05-06 23:19:14.395957', '2021-05-06 23:19:14.395957');

可以构造如下查询

EXPLAIN select * from student where age = 10 and name = 'byron'

需要注意的是,如果通过索引age或者索引name直接可以找到符合条件的记录,那么index_merge将不会被应用。比如student表中刚好有一条记录满足,age=10和name='byron',那么type的值将是ref,这一点请牢记!

这里只演示了index_merge的一种类型:Using intersect(idx_student_age,idx_student_name),更多关于index_merge的资料请参阅:
https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html

unique_subquery

This type replaces eq_ref for some IN subqueries of the following form:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

解读
与eq_ref不同的地方在于,当至多只会有一条结果的select出现在where中的in条件时,执行计划的type会显示为unique_subquery。在高版本MySQL(5.6或以上)由于对in子查询有了很多优化,比较难看见这种类型。了解即可。

举例
没有复现

index_subquery

This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:

value IN (SELECT key_column FROM single_table WHERE some_expr)

解读
unique_subquery保证了in列表中的值不会是重复的。而index_subquery有点类似于ref,它会利用索引对in子句中的查询结果去重。

举例
没有复现

range

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

解读
使用索引的范围查询。如果列上没有索引,type的值为all。

举例

-- 使用索引的范围查找
explain select * from student_score where id > 1;

-- 没有所有的范围查找
explain select * from student_score where score > 1;

index

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.

A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

MySQL can use this join type when the query uses only columns that are part of a single index.

解读
和ALL类似,只不过index为索引扫描。以下两种情况会出现

  • 某个索引的列覆盖了查询条件,即可以使用索引覆盖避免访问聚集索引(InnoDB),这时extra列会显示"using index"。
  • 可以利用索引的顺序来遍历记录,这种情况下extra列不会显示"using index"。

当查询仅使用属于单个索引一部分的列时,MySQL 可以使用此联接类型。

举例

explain select count(1) from student

ALL

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

解读
全表扫描。可以在列上加索引避免全表扫描的出现。

举例

-- score列上没有索引
explain select * from student_score where score = 1;

好了,到此type所有可能的取值都介绍完毕,你学会了么?

参考资料

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types
https://stackoverflow.com/questions/33763883/mysql-left-join-not-using-foreign-key

本原创文章未经允许不得转载 | 当前页面:BH4FFU » 史上最强 | Mysql EXPLAIN详解 - 聊聊Type

评论 1

文章评论已关闭!