jlzzjlzz亚洲乱熟在线播放

系統城裝機大師 - 唯一官網:www.farandoo.com!

當前位置:首頁 > 數據庫 > DB2 > 詳細頁面

explain都不會用,你還好意思說精通MySQL查詢優化?

時間:2020-08-31來源:www.farandoo.com作者:電腦系統城

Explain簡介

Explain關鍵字是Mysql中sql優化的常用「關鍵字」,通常都會使用Explain來「查看sql的執行計劃,而不用執行sql」,從而快速的找出sql的問題所在。

在講解Explain之前首先創建需要的「用戶表user、角色表role、以及用戶角色關系表role_user」作為測試用的表:


 
  1. // 用戶表  
  2. DROP TABLE IF EXISTS `user`;  
  3. CREATE TABLE `user` (  
  4.   `id` int(11) NOT NULL,  
  5.   `name` varchar(25) DEFAULT NULL,  
  6.   `age` int(11)  NOT NULL DEFAULT 0,  
  7.   `update_time` datetime DEFAULT NULL,  
  8.   PRIMARY KEY (`id`)  
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
  10. INSERT INTO `user` (`id`, `name`, `age`,`update_time`) VALUES (1,'張三',23,'2020-12-22 15:27:18'), (2,'李四',24,'2020-06-21 15:27:18'), (3,'王五',25,'2020-07-20 15:27:18'); 
  11.  DROP TABLE IF EXISTS `role`;  
  12. CREATE TABLE `role` (  
  13.   `id` int(11) NOT NULL AUTO_INCREMENT,  
  14.   `name` varchar(10) DEFAULT NULL,  
  15.   PRIMARY KEY (`id`),  
  16.   KEY `index_name` (`name`)  
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
  18. INSERT INTO `role` (`id`, `name`) VALUES (1,'產品經理'),(2,'技術經理'),(3,'項目總監');  
  19. DROP TABLE IF EXISTS `role_user`;  
  20. CREATE TABLE `role_user` (  
  21.   `id` int(11) NOT NULL,  
  22.   `role_id` int(11) NOT NULL,  
  23.   `user_id` int(11) NOT NULL,  
  24.   PRIMARY KEY (`id`),  
  25.   KEY `index_role_user_id` (`role_id`,`user_id`)  
  26. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
  27. INSERT INTO `role_user` (`id`, `role_id`, `user_id`) VALUES (1,2,1),(2,1,2),(3,3,3);  

我們首先執行一條sql:explain select * from user where id =2;,執行后可以看到執行的結果如下:

可以看到這里有12個字段那個且都有對應的值,這就是explain的執行計劃,能看懂這個執行計劃,你離精通sql優化就不遠了,下面就來詳細的介紹這12個字段分別表示什么意思。

id字段

id表示執行select查詢語句的序號,它是sql執行的順序的標識,sql按照id從大到小執行,id相同的為一組,從上到下執行。

什么意思呢?例如執行這條sql:explain select * from user where id in (select user_id from role_user);


 
  1. +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 
  2. | id | select_type | table     | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                                                                             | 
  3. +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 
  4. |  1 | SIMPLE      | user      | NULL       | ALL   | PRIMARY       | NULL               | NULL    | NULL |    3 |   100.00 | NULL                                                                              | 
  5. |  1 | SIMPLE      | role_user | NULL       | index | NULL          | index_role_user_id | 8       | NULL |    3 |    33.33 | Using where; Using index; FirstMatch(user); Using join buffer (Block Nested Loop) | 
  6. +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 

顯示出的兩者的id都相同,便表示sql的執行從上往下執行,第一條記錄對應的是user表,然后第二條記錄對應的是role_user表,這種是id相同的情況。

若是id不同,例如執行下面的sql:explain select (select 1 from user limit 1) from role;:


 
  1. +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+  
  2. | id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |  
  3. +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+  
  4. |  1 | PRIMARY     | role  | NULL       | index | NULL          | index_name | 33      | NULL |    3 |   100.00 | Using index |  
  5. |  2 | SUBQUERY    | user  | NULL       | index | NULL          | PRIMARY    | 4       | NULL |    3 |   100.00 | Using index |  
  6. +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 

就會看到有兩條記錄,并且兩條記錄的id會不一樣,id越大的就越先執行,可以看到id=2的執行的是user表,也就是子查詢部分,最后執行最外層的部分。

「結論:」 這個就是id標識sql的執行順序,一般在復雜查詢中會有多條記錄,簡單查詢只有一條記錄,復雜查詢中id相同的為一組,執行的順序是從上往下,而id越大的越先執行;Mysql 8中會存在對子查詢進行優化,所以有時候即使是復雜查詢,也只有一條記錄。

select_type字段

select_type表示查詢的類型,也就是對應的是簡單查詢還是復雜查詢,若是復雜查詢又包含:「簡單的子查詢、from子句的子查詢、union查詢」。下面就分別來看看select_type中的所有查詢類型。

simple

simple表示簡單查詢,不含有任何的復雜查詢。

PRIMARY

復雜查詢中「最外層的select語句的查詢類型就是PRIMARY」,例如執行下面的sql:explain select * from role where id = (select id from role_user where role_id = (select id from user where id = 2));

最外層的select,也就是select * from role where id =?會被標記為PRIMARY類型。

SUBQUERY

在「select或者where中包含的子查詢」會被表示為SUBQUERY類型,例如上一句執行的sql中就有兩次的子查詢為SUBQUERY。

DERIVED

「DERIVED表示的是派生表或者衍生表的意思,在from包含的子查詢中會被表示為DERIVED類型」,Mysql會遞歸執行這些子查詢,并且把結果放在臨時表中。執行sql:explain select * from (select name from user union select name from role) a where a.name = '張三';

在Mysql 5.7以上的版本中對其做了優化,新增了derived_merge(派生合并),可以加快查詢效率。

UNION

在出現「UNION查詢語句中,第二個select的查詢語句就會被表示為UNION」:

UNION RESULT

「UNION查詢語句的結果被標記為UNION RESULT」,如上面執行的sql:explain select * from (select name from user union select name from role) a where a.name = '張三';

第四行記錄中從table字段中可以看出,第四行的記錄來源于第二行和第三行<union2,3>,因此一個UNION查詢語句的結果就會被標記為UNION RESULT

其它

上面的七個select_type都是比較常見的,還有一些不常見的,作為了解就好:

  1.  DEPENDENT UNION:也表示UNION查詢語句中第二個或者后面的語句,但是取決于外面的查詢。
  2.  DEPENDENT SUBQUERY:子查詢中的第一個select語句,也是依賴于外部的查詢。
  3.  UNCACHEABLE SUBQUERY:子查詢的結果不能被緩存,必須重新評估外連接的第一行。

table字段

這個很容易看出「table字段表示的是查詢的是哪個表」,一個是已經存在的表,比如上面的user、role都是我們自己創建的表,也可以表示衍生表。

比如:UNION RESULT的table字段表示為<union2,3>,也就是查詢的是第二行和第三行的結果記錄。

type字段

「type字段表示的sql關聯的類型或者說是訪問的類型」。從這個字段中我們可以確定這條sql查找數據庫表的時候,查找記錄的大概范圍是怎么樣的,直接就能體現sql的效率問題。

type字段的類型也是有比較多,主要常見掌握的有以下幾個:system、const 、eq_ref 、ref 、range 、index 、ALL。它的性能體現是從高到低,即system > const > eq_ref > ref > range > index > ALL,下面就來詳細的說一說這屬性。

system

system是const的特例,「表示表中只有一行記錄」,這個幾乎不會出現,也作為了解。

const

const表示通過索引一次就查找到了數據,一般const出現在「唯一索引或者主鍵索引中使用等值查詢」,因為表中只有一條數據匹配,所以查找的速度很快。例子:explain select * from user where id =2;

eq_ref

eq_ref表示使用唯一索引或者主鍵索引掃描作為表鏈接匹配條件,對于每一個索引鍵,表中只有一條記錄與之匹配。例如:explain select * from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id;

ref

ref性能比eq_ref差,也表示表的鏈接匹配條件,也就是使用哪些表字段作為查詢索引列上的值,ref與eq_ref的區別就是eq_ref使用的是唯一索引或者主鍵索引。

ref掃描后的結果可能會找到多條符合條件的行數據,本質上是一種索引訪問,返回匹配的行。例如:explain select * from user where name = '張三';

range

「range使用索引來檢索給定范圍的行數據,一般是在where后面使用between、<>、in等查詢語句就會出現range」:explain select * from user where id > 2;

index

index表示會遍歷索引樹,index回避ALL速度快一些,但是出現index說明需要檢查自己的索引是否使用正確:explain select id from user;

ALL

「ALL與index的區別就是ALL是從硬盤中讀取,而index是從索引文件中讀取」,ALL全表掃描意味著Mysql會從表的頭到尾進行掃描,這時候表示通常需要增加索引來進行優化了,或者說是查詢中并沒有使用索引作為條件進行查詢:explain select * from user;

possible_keys字段

possible_keys表示這一列查詢語句可能使用到的索引,僅僅只是可能,列出來的索引并不一定真正的使用到。

當沒有使用索引為NULL時,說明需要增加索引來優化查詢了,若是表的數據比較少的話,數據庫覺得全表掃描更快,也可能為NULL。

key字段

key字段與possible_keys的區別就是,表示的真正使用到的索引,即possible_keys中包含key的值。

若是想Mysql使用或者忽視possible_keys中的索引,可以使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len字段

表示sql查詢語句中索引使用到的字節數,這個字節數并不是實際的長度,而是通過計算查詢中使用到的索引中的長度得出來的,顯示的是索引字段最大的可能長度。

一般來說在不損失精度的前提下,key_len是越小越好,比如上面的測試表的id為int類型,int類型由4個字節組成:explain select * from user where id =2;

key_len對于不同的類型有自己的計算規則,具體的計算規則如下所示:

數據類型 所占字節數
字符串 char(n):n字節長度
varchar(n):2字節存儲字符串長度,如果是utf-8,則長度 3n + 2
數值類型 tinyint:1字節
smallint:2字節
int:4字節
bigint:8字節
時間類型 date:3字節
timestamp:4字節
datetime:8字節

若是索引為字符串類型的時候,實際存儲的字符串非常長,已經超出了字符串類型的存儲最大長度(768字節),mysql,就會使用類似左前綴索引來處理。

ref字段

ref表示列與索引的比較,表連接的匹配條件,表示哪些列或者常量被用于查詢索引列上的值。

rows字段

rows表示估算的要掃描的行數,一般Mysql會根據統計表信息和索引的選用情況,估算出查找記錄所要掃描的行數,注意這個并不是實際結果集的行數。

partitions、filtered字段

partitions表示所匹配的分區;filtered表示的是查詢表行所占表的百分比。

Extra字段

該字段顯示的是sql查詢的額外信息,主要有以下幾種情況:

Using index

表示查詢的列被索引覆蓋,這個是查詢性能比較高的體現,即所要查詢的信息搜在索引里面可以得到,不用回表,索引被正確的使用:explain select id from user where id =2;

假如同時出現了using where,表示索引用于執行索引鍵值的查找;若是沒有出現using where,則表示索引用于讀取數據,而非執行查詢的動作。

Using where

該屬性與Using index相反,查詢的列并沒有被索引覆蓋,where條件后面使用的是非索引的前導列,它僅僅是使用了where條件而已:explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;

Using temporary

「Using temporary表示使用了臨時表存儲中間的結果,一般在對結果排序的時候會使用臨時表」,例如:排序order by 和分組查詢group by。例子:explain select * from (select name from user union select name from role) a where a.name = '張三';

Using filesort

Using filesort表示文件排序,說明Mysql對數據使用了外部的索引進行排序,并沒有使用表中的索引進行排序:explain select * from user order by name;

Using join buffer

Using join buffer表示使用連接緩存:explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;

它強調在獲取連接條件時,并沒有使用索引,而是使用連接緩沖區來存儲中間結果,若是出現該值,一般說明需要添加索引來進行優化了。

Impossible where

Impossible where會出現在where后的條件一直為false的情況下,這種可以忽視,比較少出現:explain select * from user where name = 'hah' and name = 'sfsd';

Select tables optimized away

表示select語句沒有遍歷表或者索引就返回數據了,比如:explain select min(id) from user;

在Extra字段中還有其它的屬性,但是幾乎都沒見過的,不出現,所以哪些就講解,有興趣的可以自己去了解,這里只列出這些常見的。

說了那么多理論總是要實踐一下的,下面以user測試表為例進行測試實踐。

實踐

(1)通過查詢 explain select * from user where name ='張三';name字段并沒有創建索引。

我們可以通過創建一個聯合索引index_name_age_time,來解決:


 
  1. alter table user add index index_name_age_time (name,age,update_time) ; 

當再次查詢的時候,就會使用上了索引:

(2)使用聯合索引要遵循「最左前綴法則」,關于最左前綴法則原則的使用,之前我寫過一篇詳細介紹的文章,可以參考[]。

(3)在使用索引進行查詢的時候,不要做任何的函數操作,不然會導致索引失效:例子:EXPLAIN SELECT * FROM user WHERE name = '張三';

但是你在使用的時候,使用了left()函數,如:EXPLAIN SELECT * FROM employees WHERE left(name,2) = '張三';,會導致索引失效。

(4)在數據庫的查詢中不要使用(!=或者<>)等判條件和is null,is not null、like關鍵詞中以%開頭來判斷,不然也會使索引失效:

分享到:

相關信息

系統教程欄目

欄目熱門教程

人氣教程排行

站長推薦

熱門系統下載