iT邦幫忙

2021 iThome 鐵人賽

DAY 24
0
自我挑戰組

那些Mysql我不知道的事系列 第 24

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)

上一篇
子查詢在MySQL中是怎麼執行的
下一篇
Explain詳解(優化查詢好幫手)-Part2(possible_keys、key、key_len、ref、rows、filtered、Extra、Json格式的執行計畫)
系列文
那些Mysql我不知道的事30

尚未有邦友留言

立即登入留言