文章目錄
- 生猛幹貨
- 官方文檔
- Explain介紹
- 測試資料
- explain 使用
- explain重要列說明
-
- id
- select_type
-
- simple
- primary
- subquery
- derived
- union
- table
- type
-
- NULL
- const, system
- eq_ref
- ref
- range
- index
- ALL
- possible_keys
- key
- key_len
-
- key_len計算規則
- rows
- Extra
-
- Using index
- Using where
- Using index condition
- Using temporary
- Using filesort
- Select tables optimized away
- 搞定MySQL實戰
帶你搞定MySQL實戰,輕松對應海量業務處理及高并發需求,從容應對大場面試
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
使用EXPLAIN關鍵字可以模拟優化器執行SQL語句,分查詢語句或是結構的性能瓶頸
在 select 語句之前增加 explain 關鍵字,MySQL 會在查詢上設定一個标記,執行查詢會傳回執行計劃的資訊,而不是執行這條SQL。
如果 from 中包含子查詢,仍會執行該子查詢,将結果放入臨時表中 。
DB Version
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.29-log |
+------------+
1 row in set (0.00 sec)
mysql>
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');
###############################
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
###############################
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
explain 兩個擴充的使用
explain extended: 提供: 額外一些查詢優化的資訊 (‘EXTENDED’ is deprecated and will be removed in a future release.)
mysql> explain extended select * from film where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
# 2 warnings
# 可以通過 show warnings 指令檢視
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. |
| Note | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `dbtest`.`film` where 1 |
+---------+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
filtered 列: 百分比,計算公式 rows * filtered/100 可以估算出将要和 explain 中前一個表進行連接配接的行數(前一個表指 explain 中的id值比目前表id值小的表) , 供參考
第二個 ‘PARTITIONS’ is deprecated and will be removed in a future
mysql> explain partitions select * from film where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1681 | 'PARTITIONS' is deprecated and will be removed in a future release. |
| Note | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `dbtest`.`film` where 1 |
+---------+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
是以隻使用explain就足夠了 。
mysql> explain select * from film_actor a where a.actor_id = (select id from actor where name = 'a');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 2 | SUBQUERY | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
id列的編号是 select 的序列号,有幾個 select 就有幾個id,并且id的順序是按 select 出現的順序增長的。
執行順序:
id越大執行優先級越高,id相同則從上往下執行,id為NULL最後執行
表示的對應行是簡單還是複雜的查詢
簡單查詢,查詢不包含子查詢和union
mysql> explain select * from film where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
複雜查詢中最外層的 select
mysql> explain select * from film_actor a where a.actor_id = (select id from actor where name = 'a');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 2 | SUBQUERY | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
包含在 select 中的子查詢(不在 from 子句中)
mysql> explain select * from film_actor a where a.actor_id = (select id from actor where name = 'a');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 2 | SUBQUERY | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
包含在 from 子句中的子查詢。
MySQL會将結果存放在一個臨時表中,也稱為派生表
derived: 衍生的
mysql> set session optimizer_switch='derived_merge=off'; #關閉mysql5.7新特性對衍生表的合并優化
Query OK, 0 rows affected (0.00 sec)
mysql> explain select (select 1 from actor where id = 1 ) from (select * from film where id =1 ) t ;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | <derived3> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 3 | DERIVED | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | actor | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
mysql>
在 union 中的第二個和随後的 select
mysql> EXPLAIN select 1 union select 1 ;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
mysql>
表示 explain 的一行正在通路哪個表
mysql> explain select (select 1 from actor where id = 1 ) from (select * from film where id =1 ) t ;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | <derived3> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 3 | DERIVED | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | actor | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
當 from 子句中有子查詢時,table列是
<derivenN>
格式,表示目前查詢依賴 id=N 的查詢,于是先執行 id=N 的查詢。
mysql> EXPLAIN select 1 union select 1 ;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
mysql>
當有 union 時,UNION RESULT 的 table 列的值為<union1,2>,1和2表示參與 union 的 select 行id。
表示關聯類型或通路類型,即MySQL決定如何查找表中的行,查找資料行記錄的大概範圍。
依次從最優到最差分别為:system > const > eq_ref > ref > range > index > ALL
一般來說,得保證查詢達到range級别,最好達到ref
mysql能夠在優化階段分解查詢語句,在執行階段用不着再通路表或索引.
例如:在索引列中選取最小值,可以單獨查找索引來完成,不需要在執行時通路表
mysql> explain select min(id) from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql能對查詢的某部分進行優化并将其轉化成一個常量(可以看show warnings 的結果)。用于primary key 或 unique key 的所有列與常數比較時,是以表最多有一個比對行,讀取1次,速度比較快。
system是const的特例,表裡隻有一條元組比對時為system.
mysql> EXPLAIN select * from (select * from film where id=1) t ;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual |
+-------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
primary key 或 unique key 索引的所有部分被連接配接使用 ,最多隻會傳回一條符合條件的記錄。這可能是在const 之外最好的聯接類型了,簡單的 select 查詢不會出現這種 type。
mysql> EXPLAIN select * from film_actor a left join film b on a.film_id = b.id ;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbtest.a.film_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `dbtest`.`a`.`id` AS `id`,`dbtest`.`a`.`film_id` AS `film_id`,`dbtest`.`a`.`actor_id` AS `actor_id`,`dbtest`.`a`.`remark` AS `remark`,`dbtest`.`b`.`id` AS `id`,`dbtest`.`b`.`name` AS `name` from `dbtest`.`film_actor` `a` left join `dbtest`.`film` `b` on((`dbtest`.`b`.`id` = `dbtest`.`a`.`film_id`)) where 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分字首,索引要和某個值相比較,可能會找到多個符合條件的行.
【 簡單 select 查詢,name是普通索引(非唯一索引)】
mysql> show INDEX from film ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| film | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| film | 1 | idx_name | 1 | name | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql>
mysql> EXPLAIN select * from film a where a.name = 'film0';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ref | idx_name | idx_name | 33 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
【關聯表查詢,idx_film_actor_id是film_id和actor_id的聯合索引,這裡使用到了film_actor的左邊字首film_id部分。】
mysql> show index from film_actor;
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| film_actor | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| film_actor | 1 | idx_film_actor_id | 1 | film_id | A | 2 | NULL | NULL | | BTREE | | |
| film_actor | 1 | idx_film_actor_id | 2 | actor_id | A | 3 | NULL | NULL | | BTREE | | |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql>
mysql> EXPLAIN select film_id from film left join film_actor on film.id =film_actor.film_id ;
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
| 1 | SIMPLE | film_actor | NULL | ref | idx_film_actor_id | idx_film_actor_id | 4 | dbtest.film.id | 1 | 100.00 | Using index |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql>
範圍掃描通常出現在 in(), between ,> ,<, >= 等操作中。使用一個索引來檢索給定範圍的行。
mysql> explain select * from actor where id > 1 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
掃描全索引就能拿到結果,一般是掃描某個二級索引,這種掃描不會從索引樹根節點開始快速查找,而是直接對二級索引的葉子節點周遊和掃描,速度還是比較慢的,這種查詢一般為使用覆寫索引,二級索引一般比較小,是以這種通常比ALL快一些
mysql> explain select * from film;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
全表掃描,掃描你的聚簇索引的所有葉子節點。通常情況下這需要增加索引來進行優化了
mysql> explain select * from actor ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
顯示查詢可能使用哪些索引來查找
explain 時可能出現 possible_keys 有列,而 key 顯示 NULL 的情況,這種情況是因為表中資料不多,mysql認為索引對此查詢幫助不大,選擇了全表查詢。
如果該列是NULL,則沒有相關的索引。在這種情況下,可以通過檢查 where 子句看是否可以創造一個适當的索引來提高查詢性能,然後用 explain 檢視效果。
mysql實際采用哪個索引來優化對該表的通路。
如果沒有使用索引,則該列是 NULL。如果想強制mysql使用或忽視possible_keys列中的索引,在查詢中使用 force index、ignore index。
顯示了mysql在索引裡使用的位元組數,通過這個值可以算出具體使用了索引中的哪些列。
舉個例子 :
film_actor的聯合索引 idx_film_actor_id 由 film_id 和 actor_id 兩個int列組成,并且每個int是4位元組。
mysql> explain select * from film_actor where film_id=1;
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | film_actor | NULL | ref | idx_film_actor_id | idx_film_actor_id | 4 | const | 2 | 100.00 | NULL |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
通過結果中的key_len=4可推斷出查詢使用了第一個列:film_id列來執行索引查找。
【字元串】
- char(n):n位元組長度
- varchar(n):如果是utf-8,則長度 3n + 2 位元組,加的2位元組用來存儲字元串長度
【數值類型】
- tinyint:1位元組
- smallint:2位元組
- int:4位元組
- bigint:8位元組
【時間類型】
- date:3位元組
- timestamp:4位元組
- datetime:8位元組
如果字段允許為 NULL,需要1位元組記錄是否為 NULL
索引最大長度是768位元組,當字元串過長時,mysql會做一個類似左字首索引的處理,将前半部分的字元提取出來做索引
顯示了在key列記錄的索引中,表查找值所用到的列或常量,常見的有:const(常量),字段名(例:film.id)
mysql> explain select * from film_actor where film_id=1;
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | film_actor | NULL | ref | idx_film_actor_id | idx_film_actor_id | 4 | const | 2 | 100.00 | NULL |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN select film_id from film left join film_actor on film.id =film_actor.film_id ;
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
| 1 | SIMPLE | film_actor | NULL | ref | idx_film_actor_id | idx_film_actor_id | 4 | dbtest.film.id | 1 | 100.00 | Using index |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql估計要讀取并檢測的行數,注意這個不是結果集裡的行數。
展示的是額外資訊。
列舉幾個常見的值
使用覆寫索引 : 無需回表
mysql執行計劃explain結果裡的key有使用索引,如果select後面查詢的字段都可以從這個索引的樹中擷取,這種情況一般可以說是用到了覆寫索引,extra裡一般都有using index;
覆寫索引一般針對的是輔助索引,整個查詢結果隻通過輔助索引就能拿到結果,不需要通過輔助索引樹找到主鍵,再通過主鍵去主鍵索引樹裡擷取其它字段值
mysql> explain select film_id from film_actor where film_id=1;
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | film_actor | NULL | ref | idx_film_actor_id | idx_film_actor_id | 4 | const | 2 | 100.00 | Using index |
+----+-------------+------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
使用 where 語句來處理結果,并且查詢的列未被索引覆寫
mysql> show index from actor;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| actor | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> explain select * from actor where name = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
查詢的列不完全被索引覆寫,where條件中是一個前導列的範圍;
mysql> explain select * from film_actor where film_id > 1 ;
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | film_actor | NULL | range | idx_film_actor_id | idx_film_actor_id | 4 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql需要建立一張臨時表來處理查詢。出現這種情況一般是要進行優化的,首先是想到用索引來優化。
【actor.name沒有索引,此時建立了張臨時表來distinct】
mysql> explain select distinct name from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
【film.name建立了idx_name索引,此時查詢時extra是using index,沒有用臨時表】
mysql> explain select distinct name from film;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | idx_name | idx_name | 33 | NULL | 3 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
将用外部排序而不是索引排序,資料較小時從記憶體排序,否則需要在磁盤完成排序。這種情況下一般也是要考慮使用索引來優化的
【 actor.name未建立索引,會浏覽actor整個表,儲存排序關鍵字name和對應的id,然後排序name并檢索行記錄】
mysql> explain select * from actor order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql>
【film.name建立了idx_name索引,此時查詢時extra是using index】
mysql> explain select * from film order by name ;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
使用某些聚合函數(比如 max、min)來通路存在索引的某個字段