Explain详解(优化查询好帮手)-Part1(id、select_type、table、partitions、type)

mysql最佳化工具针对一笔叙述根据成本与规则制定一个具体计画,包含了执行叙述的方式、连接表的顺序、使用的存取方式等,explain就是可以让我们看具体计画细节的好工具,也是我们优化查询的好帮手。

mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.05 sec)

可以看到有这些栏位,接下来分别用更清楚的查询来一一说明唷!

方便说明把之前的表再贴一次

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_field varchar(100),
    -> primary key (id),
    -> key idx_key1 (key1),
    -> unique key uk_key2 (key2),
    -> key idx_key3 (key3),
    -> key idx_key_part(key_part1, key_part2, key_part3)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的uk_key2二级索引,且是唯一
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3列建立的idx_key_part二级索引,这也是个联合索引

而single_table2跟single_table一样。

id

一个select就会是一个id,除非查询内包含子查询或是Union子句。
单一select

mysql> explain select * from single_table;
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10146 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)

包含子查询

mysql> explain select * from single_table where key1 in (select key1 from single_table2) or key3 = 'a';
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | single_table  | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 10146 |   100.00 | Using where |
|  2 | SUBQUERY    | single_table2 | NULL       | index | idx_key1      | idx_key1 | 33      | NULL |     1 |   100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)

一个select一个id,所以这有两个id。
但要注意的是当子查询被转为半连接的时候会多一个materiakized

mysql> explain select * from single_table where key1 in (select key3 from single_table2 where common_field = 'a');
+----+--------------+---------------+------------+------+---------------+----------+---------+------------------+------+----------+-----------------------+
| id | select_type  | table         | partitions | type | possible_keys | key      | key_len | ref              | rows | filtered | Extra                 |
+----+--------------+---------------+------------+------+---------------+----------+---------+------------------+------+----------+-----------------------+
|  1 | SIMPLE       | <subquery2>   | NULL       | ALL  | NULL          | NULL     | NULL    | NULL             | NULL |   100.00 | Using where           |
|  1 | SIMPLE       | single_table  | NULL       | ref  | idx_key1      | idx_key1 | 33      | <subquery2>.key3 |    1 |   100.00 | Using index condition |
|  2 | MATERIALIZED | single_table2 | NULL       | ALL  | idx_key3      | NULL     | NULL    | NULL             |    1 |   100.00 | Using where           |
+----+--------------+---------------+------------+------+---------------+----------+---------+------------------+------+----------+-----------------------+
3 rows in set, 1 warning (0.00 sec)

Union子句

mysql> explain select * from single_table union select * from single_table2;
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type  | table         | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra           |
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|  1 | PRIMARY      | single_table  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10146 |   100.00 | NULL            |
|  2 | UNION        | single_table2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |     1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2>    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  NULL |     NULL | Using temporary |
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)

这边有一笔奇怪的纪录(id:NULL),大家还记得Union叙述是为了把两表的结果去重,所以这只是个临时表存放s1与s2合并结果的。

mysql> explain select * from single_table union all select * from single_table2;
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | PRIMARY     | single_table  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10146 |   100.00 | NULL  |
|  2 | UNION       | single_table2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |     1 |   100.00 | NULL  |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

而要注意的是,union all不需要对最终结果去重,没有临时表,因此像以上那样没有NULL纪录。

select_type

simple
查询叙述不包含子查询或union都算作simple。

mysql> explain select * from single_table;
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10146 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)

primary
对包含union or union all or 子查询的的大查询,由几个小查询组成的,最左边的就会是primary,
而右边就依照小查询的类型显示。

**子查询**
mysql> explain select * from single_table where key1 in (select key1 from single_table2) or key3 = 'a';
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | single_table  | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 10146 |   100.00 | Using where |
|  2 | SUBQUERY    | single_table2 | NULL       | index | idx_key1      | idx_key1 | 33      | NULL |     1 |   100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)


**Union**
mysql> explain select * from single_table union select * from single_table2;
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type  | table         | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra           |
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|  1 | PRIMARY      | single_table  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10146 |   100.00 | NULL            |
|  2 | UNION        | single_table2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |     1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2>    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  NULL |     NULL | Using temporary |
+----+--------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)


**dependent subquery**:子查询不能够转为半连接的
mysql> explain select * from single_table s1 where key1 in (select key1 from single_table2 s2 where s1.key2 = s2.key2) or key3 = 'a';
+----+--------------------+-------+------------+--------+------------------+---------+---------+----------------------+-------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys    | key     | key_len | ref                  | rows  | filtered | Extra       |
+----+--------------------+-------+------------+--------+------------------+---------+---------+----------------------+-------+----------+-------------+
|  1 | PRIMARY            | s1    | NULL       | ALL    | idx_key3         | NULL    | NULL    | NULL                 | 10146 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | eq_ref | uk_key2,idx_key1 | uk_key2 | 5       | ryan_demo_db.s1.key2 |     1 |   100.00 | Using where |
+----+--------------------+-------+------------+--------+------------------+---------+---------+----------------------+-------+----------+-------------+
2 rows in set, 2 warnings (0.01 sec)


**derived**:衍生表
mysql> explain select * from (select key1, count(*) as c from single_table group by key1) as derived_s1 where c >1;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2>   | NULL       | ALL   | NULL          | NULL     | NULL    | NULL | 10146 |   100.00 | NULL        |
|  2 | DERIVED     | single_table | NULL       | index | idx_key1      | idx_key1 | 33      | NULL | 10146 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.04 sec)

table

顾名思义就是查询的表

mysql> explain select * from single_table inner join single_table2;
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                         |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------------------------------+
|  1 | SIMPLE      | single_table2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |     1 |   100.00 | NULL                          |
|  1 | SIMPLE      | single_table  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10146 |   100.00 | Using join buffer (hash join) |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+-------------------------------+
2 rows in set, 1 warning (0.01 sec)

partitions

先略过,只要知道到目前为止看到的叙述内的partiotion都是NULL。

type

就是我们之前讲到的存取方法类型。
但之前提到的不够完整,今天在完整的说明其他的类型。
总共有system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。

system
当表中只有一笔纪录并且该表使用的储存引擎统计资料是精确的(MyISAM、Memory)。

mysql> create table t(i int) engine=MyISAM;
Query OK, 0 rows affected (0.47 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.07 sec)

mysql> explain select * from t;
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

const
根据主键或为唯一二级索引来比对的话就是const。

mysql> explain select * from single_table where id=10000;
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

eq_ref
表连接查询时,被驱动表是透过主键或不允许NULL的唯一二级索引来相等匹配的话就eq_ref。

mysql> explain select * from single_table s1 inner join single_table2 s2 on s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | s2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL               |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | s1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ryan_demo_db.s2.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

ref
当透过普通的二级索引来判断就会是ref,或是执行连接查询用普通的二级索引去相等匹配

当透过普通的二级索引来判断就会是ref
mysql> explain select * from single_table where key1='key110000';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ref  | idx_key1      | idx_key1 | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from single_table where key3='key_part39999';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ref  | idx_key3      | idx_key3 | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

或是执行连接查询用普通的二级索引去相等匹配
mysql> explain select * from single_table s1 inner join single_table2 s2 on s1.key1 = s2.key1;
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | s2    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL                 |    1 |   100.00 | Using where |
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 33      | ryan_demo_db.s2.key1 |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

fulltext
全文索引,略过。

ref_or_null
当执行连接查询用普通的二级索引去相等匹配且可以是NULL

mysql> explain select * from single_table where key1 = 'key19905' or key1 is null;
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table        | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | single_table | NULL       | ref_or_null | idx_key1      | idx_key1 | 33      | const |    2 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)

index_merge
多个索引合并(interseciotn、union、sort-union),觉得陌生请复习

mysql> explain select * from single_table where key1='key19999' or key3='key39999';
+----+-------------+--------------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table        | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+--------------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | single_table | NULL       | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 33,303  | NULL |    2 |   100.00 | Using union(idx_key1,idx_key3); Using where |
+----+-------------+--------------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.01 sec)

unique_subquery
这是针对包含in语法的查询,当查询最佳化工具决定要将in子查询转为exist查询,且使用主键或唯一二级索引相等匹配。

mysql> explain select * from single_table s1 where id in (select id from single_table2 s2 where s1.key2 = s2.key2) or key3 = 'key310000';
+----+--------------------+-------+------------+-----------------+-----------------+---------+---------+------+-------+----------+-------------+
| id | select_type        | table | partitions | type            | possible_keys   | key     | key_len | ref  | rows  | filtered | Extra       |
+----+--------------------+-------+------------+-----------------+-----------------+---------+---------+------+-------+----------+-------------+
|  1 | PRIMARY            | s1    | NULL       | ALL             | idx_key3        | NULL    | NULL    | NULL | 10146 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | unique_subquery | PRIMARY,uk_key2 | PRIMARY | 4       | func |     1 |   100.00 | Using where |
+----+--------------------+-------+------------+-----------------+-----------------+---------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

index_subquery
与unique_subquery类似,差别在於使用普通二级索引相等匹配。

mysql> explain select * from single_table s1 where key3  in (select key3 from single_table2 s2 where s1.common_field = s2.common_field) or key1 = 'key110000';
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type        | table | partitions | type           | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | PRIMARY            | s1    | NULL       | ALL            | idx_key1      | NULL     | NULL    | NULL | 10146 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | index_subquery | idx_key3      | idx_key3 | 303     | func |     1 |   100.00 | Using where |
+----+--------------------+-------+------------+----------------+---------------+----------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

range
不赘述。

mysql> explain select * from single_table where key1 in ('key11','key12','key13');
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key1      | idx_key1 | 33      | NULL |    3 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)

index

当使用索引,但要扫遍所有索引资料
mysql> explain select key_part2 from single_table where key_part3 = 'key_part31';
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+-------+----------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | single_table | NULL       | index | idx_key_part  | idx_key_part | 909     | NULL | 10146 |    10.00 | Using where; Using index |
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

像这个不需要扫遍所有索引就不会是index
mysql> explain select key_part2 from single_table where key_part1 = 'key_part11';
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | ref  | idx_key_part  | idx_key_part | 303     | const |    1 |   100.00 | Using index |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

<<:  [Day25] Angular 的 Module 与 Component

>>:  终极密码DC版

Day 22 关键字跟 SEO

在现今使用 Google 搜寻的人数占了将近九成以上,所以更可以知道搜寻引擎对於消费者来说,是一个很...

[Day0] 参赛初衷

我的背景 目前自己前端的程序能力只学过基础 html+css+js,没有独立开发过大型专案的经验,算...

Day 28 Flask-RESTX

看到标题上的 REST 四个英文字母,不知道有没有令你想起了什麽? 对,就是 RESTful API...

Day5 Redis组态档设定-GENERAL 2

Redis.config GENERAL daemonize 是否要用daemon方式启动Redis...

[DAY 09] 光头古早味手工蛋饼

光头古早味手工蛋饼 地点:台南市新营区东兴路203号 时间:6:00~11:30 如果仔细看可以发现...