MySQL關鍵字EXPLAIN的用法及其案例

文章目錄

    • 概述
    • EXPLAIN輸出的列的解釋
    • 實例說明
      • select_type的說明
        • UNION
        • DEPENDENT UNION與DEPENDENT SUBQUERY
        • SUBQUERY
        • DERIVED
      • type的說明
        • system，const
        • eq_ref
        • ref
        • ref_or_null
        • index_merge
        • unique_subquery
        • index_subquery
        • range
        • index
        • ALL
      • extra的說明
        • Distinct
        • Not exists
        • Range checked for each record
        • Using filesort
        • Using index
        • Using temporary
        • Using where
        • Using sort_union(...)/Using union(...)/Using intersect(...)
        • Using index for group-by
      • 一個SQL語句的優化例子
    • 參考資料

概述

根據表、列、索引和WHERE子句中的條件的詳細信息，MySQL優化器考慮了許多技術來高效地執行SQL查詢。

  • 可以在不讀取所有行的情況下對大型表執行查詢；
  • 可以在不比較每一行組合的情況下執行包含多個表的聯接。

優化器選擇執行最有效查詢的一組操作稱為“查詢執行計劃（query execution plan）”，也稱為解釋計劃（EXPLAIN plan）。

我們目標是認識到EXPLAIN計劃中表示查詢優化良好的方面，并學習SQL語法和索引技術，以便在看到一些低效的操作時改進該計劃。

使用方法，就是在SELECT語句前加上EXPLAIN即可：

EXPLAIN SELECT * FROM question;

輸出結果如下：

mysql> explain SELECT * FROM question;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | question | ALL  | NULL          | NULL | NULL    | NULL |  383 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set

EXPLAIN 也可以用在DELETE, INSERT, REPLACE, 和 UPDATE語句上。

EXPLAIN輸出的列的解釋

  • id：SELECT識別符。這是SELECT的查詢序列號
  • select_type：SELECT類型，可以為以下任何一種：
    • SIMPLE：簡單SELECT(不使用UNION或子查詢)
    • PRIMARY：最外面的SELECT
    • UNION：UNION中的第二個或后面的SELECT語句
    • DEPENDENT UNION：UNION中的第二個或后面的SELECT語句，取決于外面的查詢
    • UNION RESULT：UNION 的結果
    • SUBQUERY：子查詢中的第一個SELECT
    • DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢
    • DERIVED：導出表的SELECT(FROM子句的子查詢)
  • table：輸出的行所引用的表
  • type：聯接類型。下面給出各種聯接類型，按照從最佳類型到最壞類型進行排序：
    • system：表僅有一行(=系統表)。這是const聯接類型的一個特例。
    • const：表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行，在這行的列值可被優化器剩余部分認為是常數。const表很快，因為它們只讀取一次
    • eq_ref：對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。
    • ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。
    • ref_or_null：該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。
    • index_merge：該聯接類型表示使用了索引合并優化方法。
    • unique_subquery：該類型替換了下面形式的IN子查詢的ref： value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查找函數，可以完全替換子查詢，效率更高。
    • index_subquery：該聯接類型類似于unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引： value IN (SELECT key_column FROM single_table WHERE some_expr)
    • range：只檢索給定范圍的行，使用一個索引來選擇行。
    • index：該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小。
    • ALL：對于每個來自于先前的表的行組合，進行完整的表掃描。
  • possible_keys：指出MySQL能使用哪個索引在該表中找到行
  • key：顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引，鍵是NULL。
  • key_len：顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。
  • ref：顯示使用哪個列或常數與key一起從表中選擇行。
  • rows：顯示MySQL認為它執行查詢時必須檢查的行數。多行之間的數據相乘可以估算要處理的行數。
  • filtered：顯示了通過條件過濾出的行數的百分比估計值。
  • Extra：該列包含MySQL解決查詢的詳細信息
    • Distinct：MySQL發現第1個匹配行后，停止為當前的行組合搜索更多的行。
    • Not exists：MySQL能夠對查詢進行LEFT JOIN優化，發現1個匹配LEFT JOIN標準的行后，不再為前面的的行組合在該表內檢查更多的行。
    • range checked for each record (index map： #)：MySQL沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用。
    • Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。
    • Using index：從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。
    • Using temporary：為了解決查詢，MySQL需要創建一個臨時表來容納結果。
    • Using where：WHERE 子句用于限制哪一個行匹配下一個表或發送到客戶。
    • Using sort_union(…), Using union(…), Using intersect(…)：這些函數說明如何為index_merge聯接類型合并索引掃描。
    • Using index for group-by：類似于訪問表的Using index方式，Using index for group-by表示MySQL發現了一個索引，可以用來查 詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬盤訪問實際的表。

實例說明

select_type的說明

UNION

當通過union來連接多個查詢結果時，第二個之后的select其select_type為UNION。

mysql> explain select * from t_order where order_id=100 union select * from t_order where order_id=200; 
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+ 
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra | 
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+ 
|  1 | PRIMARY      | t_order    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
|  2 | UNION        | t_order    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |       | 
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+ 
3 rows in set (0.34 sec)

DEPENDENT UNION與DEPENDENT SUBQUERY

當union在子查詢中作用時，其中第二個union的select_type就是DEPENDENT UNION。
第一個子查詢的select_type則是DEPENDENT SUBQUERY。

mysql> explain select * from t_order where order_id in (select order_id from t_order where order_id=100 union select order_id from t_order where order_id=200); 
+----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+ 
| id | select_type        | table      | type  | possible_keys | key     | key_len | ref   | rows   | Extra       | 
+----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+ 
|  1 | PRIMARY            | t_order    | ALL   | NULL          | NULL    | NULL    | NULL  | 100453 | Using where | 
|  2 | DEPENDENT SUBQUERY | t_order    | const | PRIMARY       | PRIMARY | 4       | const |      1 | Using index | 
|  3 | DEPENDENT UNION    | t_order    | const | PRIMARY       | PRIMARY | 4       | const |      1 | Using index | 
| NULL | UNION RESULT       | <union2,3> | ALL   | NULL          | NULL    | NULL    | NULL  |   NULL |             | 
+----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+ 
4 rows in set (0.03 sec)

dependent
英 [d??pend?nt] 美 [d??pend?nt]
adj. 依靠的;依賴的;有癮的;受…的影響;取決于
n.被扶養人：被贍養人：非獨立生活的人;依靠者;從屬物

SUBQUERY

子查詢中的第一個select其select_type為SUBQUERY。

mysql> explain select * from t_order where order_id=(select order_id from t_order where order_id=100); 
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ 
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       | 
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ 
|  1 | PRIMARY     | t_order | const | PRIMARY       | PRIMARY | 4       | const |    1 |             | 
|  2 | SUBQUERY    | t_order | const | PRIMARY       | PRIMARY | 4       |       |    1 | Using index | 
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ 
2 rows in set (0.03 sec)

DERIVED

當子查詢是from子句時，其select_type為DERIVED。

mysql> explain select * from (select order_id from t_order where order_id=100) a; 
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+ 
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       | 
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+ 
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |             | 
|  2 | DERIVED     | t_order    | const  | PRIMARY       | PRIMARY | 4       |      |    1 | Using index | 
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+ 
2 rows in set (0.03 sec)

type的說明

system，const

DERIVED的示例中，其中第一行的type就是為system，第二行是const，這兩種聯接類型是最快的。

eq_ref

在t_order表中的order_id是主鍵，t_order_ext表中的order_id也是主鍵，該表可以認為是訂單表的補充信息表，他們的關系是1對1，在下面的例子中可以看到b表的連接類型是eq_ref，這是極快的聯接類型。

mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id; 
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+ 
| id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows | Extra       | 
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+ 
|  1 | SIMPLE      | b     | ALL    | order_id      | NULL    | NULL    | NULL            |    1 |             | 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.b.order_id |    1 | Using where | 
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+ 
2 rows in set (0.00 sec)

ref

下面的例子在上面的例子上略作了修改，加上了條件。此時b表的聯接類型變成了ref。因為所有與a表中order_id=100的匹配記錄都將會從b表獲取。這是比較常見的聯接類型。

mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id and a.order_id=100; 
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+ 
| id | select_type | table | type  | possible_keys | key      | key_len | ref   | rows | Extra | 
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+ 
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY  | 4       | const |    1 |       | 
|  1 | SIMPLE      | b     | ref   | order_id      | order_id | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+ 
2 rows in set (0.00 sec)

ref_or_null

user_id字段是一個可以為空的字段，并對該字段創建了一個索引。在下面的查詢中可以看到聯接類型為ref_or_null，這是mysql為含有null的字段專門做的處理。在我們的表設計中應當盡量避免索引字段為NULL，因為這會額外的耗費mysql的處理時間來做優化。

mysql> explain select * from t_order where user_id=100 or user_id is null; 
+----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+ 
| id | select_type | table   | type        | possible_keys | key     | key_len | ref   | rows  | Extra       | 
+----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+ 
|  1 | SIMPLE      | t_order | ref_or_null | user_id       | user_id | 5       | const | 50325 | Using where | 
+----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+ 
1 row in set (0.00 sec)

index_merge

經常出現在使用一張表中的多個索引時。mysql會將多個索引合并在一起，如下例:

mysql> explain select * from t_order where order_id=100 or user_id=10; 
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+ 
| id | select_type | table   | type        | possible_keys   | key             | key_len | ref  | rows | Extra                                     | 
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+ 
|  1 | SIMPLE      | t_order | index_merge | PRIMARY,user_id | PRIMARY,user_id | 4,5     | NULL |    2 | Using union(PRIMARY,user_id); Using where | 
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+ 
1 row in set (0.09 sec)

unique_subquery

該聯接類型用于替換value IN (SELECT primary_key FROM single_table WHERE some_expr)這樣的子查詢的ref。注意ref列，其中第二行顯示的是func，表明unique_subquery是一個函數，而不是一個普通的ref。

mysql> explain select * from t_order where order_id in (select order_id from t_order where user_id=10); 
+----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+ 
| id | select_type        | table   | type            | possible_keys   | key     | key_len | ref  | rows   | Extra       | 
+----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+ 
|  1 | PRIMARY            | t_order | ALL             | NULL            | NULL    | NULL    | NULL | 100649 | Using where | 
|  2 | DEPENDENT SUBQUERY | t_order | unique_subquery | PRIMARY,user_id | PRIMARY | 4       | func |      1 | Using where | 
+----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+ 
2 rows in set (0.00 sec)

index_subquery

該聯接類型與上面的太像了，唯一的差別就是子查詢查的不是主鍵而是非唯一索引。

mysql> explain select * from t_order where user_id in (select user_id from t_order where order_id>10); 
+----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+ 
| id | select_type        | table   | type           | possible_keys   | key     | key_len | ref  | rows   | Extra                    | 
+----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+ 
|  1 | PRIMARY            | t_order | ALL            | NULL            | NULL    | NULL    | NULL | 100649 | Using where              | 
|  2 | DEPENDENT SUBQUERY | t_order | index_subquery | PRIMARY,user_id | user_id | 5       | func |  50324 | Using index; Using where | 
+----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+ 
2 rows in set (0.00 sec)

range

按指定的范圍進行檢索，很常見。

mysql> explain select * from t_order where user_id in (100,200,300); 
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ 
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ 
|  1 | SIMPLE      | t_order | range | user_id       | user_id | 5       | NULL |    3 | Using where | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ 
1 row in set (0.00 sec)

index

在進行統計時非常常見，此聯接類型實際上會掃描索引樹，僅比ALL快些。

mysql> explain select count(*) from t_order; 
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+ 
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra       | 
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+ 
|  1 | SIMPLE      | t_order | index | NULL          | user_id | 5       | NULL | 100649 | Using index | 
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+ 
1 row in set (0.00 sec)

ALL

完整的掃描全表，最慢的聯接類型，盡可能地避免。mysql> explain select * from t_order; 
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+ 
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra | 
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+ 
|  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100649 |       | 
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+ 
1 row in set (0.00 sec)

extra的說明

Distinct

MySQL發現第1個匹配行后,停止為當前的行組合搜索更多的行。對于此項沒有找到合適的例子，求指點。

Not exists

因為b表中的order_id是主鍵，不可能為NULL，所以mysql在用a表的order_id掃描t_order表，并查找b表的行時，如果在b表發現一個匹配的行就不再繼續掃描b了，因為b表中的order_id字段不可能為NULL。這樣避免了對b表的多次掃描。

mysql> explain select count(1) from t_order a left join t_order_ext b on a.order_id=b.order_id where b.order_id is null;  
+----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+ 
| id | select_type | table | type  | possible_keys | key          | key_len | ref             | rows   | Extra                                | 
+----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+ 
|  1 | SIMPLE      | a     | index | NULL          | express_type | 1       | NULL            | 100395 | Using index                          | 
|  1 | SIMPLE      | b     | ref   | order_id      | order_id     | 4       | test.a.order_id |      1 | Using where; Using index; Not exists | 
+----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+ 
2 rows in set (0.01 sec)

Range checked for each record

這種情況是mysql沒有發現好的索引可用，速度比沒有索引要快得多。

mysql> explain select * from t_order t, t_order_ext s where s.order_id>=t.order_id and s.order_id<=t.order_id and t.express_type>5; 
+----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+ 
| id | select_type | table | type  | possible_keys        | key          | key_len | ref  | rows | Extra                                          | 
+----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+ 
|  1 | SIMPLE      | t     | range | PRIMARY,express_type | express_type | 1       | NULL |    1 | Using where                                    | 
|  1 | SIMPLE      | s     | ALL   | order_id             | NULL         | NULL    | NULL |    1 | Range checked for each record (index map: 0x1) | 
+----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+ 
2 rows in set (0.00 sec)

Using filesort

在有排序子句的情況下很常見的一種情況。此時mysql會根據聯接類型瀏覽所有符合條件的記錄，并保存排序關鍵字和行指針，然后排序關鍵字并按順序檢索行。

mysql> explain select * from t_order order by express_type; 
+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+ 
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra          | 
+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+ 
|  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100395 | Using filesort | 
+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+ 
1 row in set (0.00 sec)

Using index

這是性能很高的一種情況。當查詢所需的數據可以直接從索引樹中檢索到時，就會出現。上面的例子中有很多這樣的例子，不再多舉例了。

Using temporary

發生這種情況一般都是需要進行優化的。mysql需要創建一張臨時表用來處理此類查詢。

mysql> explain select * from t_order a left join t_order_ext b on a.order_id=b.order_id group by b.order_id; 
+----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+ 
| id | select_type | table | type | possible_keys | key      | key_len | ref             | rows   | Extra                           | 
+----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+ 
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL     | NULL    | NULL            | 100395 | Using temporary; Using filesort | 
|  1 | SIMPLE      | b     | ref  | order_id      | order_id | 4       | test.a.order_id |      1 |                                 | 
+----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+ 
2 rows in set (0.00 sec)

Using where

當有where子句時，extra都會有說明。

Using sort_union(…)/Using union(…)/Using intersect(…)

下面的例子中user_id是一個檢索范圍，此時mysql會使用sort_union函數來進行索引的合并。而當user_id是一個固定值時，請參看上面type說明index_merge的例子，此時會使用union函數進行索引合并。

mysql> explain select * from t_order where order_id=100 or user_id>10; 
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+ 
| id | select_type | table   | type        | possible_keys   | key             | key_len | ref  | rows | Extra                                          | 
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+ 
|  1 | SIMPLE      | t_order | index_merge | PRIMARY,user_id | user_id,PRIMARY | 5,4     | NULL |    2 | Using sort_union(user_id,PRIMARY); Using where | 
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+ 
1 row in set (0.00 sec)

對于Using intersect的例子可以參看下例，user_id與express_type發生了索引交叉合并。

mysql> explain select * from t_order where express_type=1 and user_id=100; 
+----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+ 
| id | select_type | table   | type        | possible_keys        | key                  | key_len | ref  | rows | Extra                                              | 
+----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+ 
|  1 | SIMPLE      | t_order | index_merge | user_id,express_type | user_id,express_type | 5,1     | NULL |    1 | Using intersect(user_id,express_type); Using where | 
+----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+ 
1 row in set (0.00 sec)

Using index for group-by

表明可以在索引中找到分組所需的所有數據，不需要查詢實際的表。

mysql> explain select user_id from t_order group by user_id; 
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ 
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ 
|  1 | SIMPLE      | t_order | range | NULL          | user_id | 5       | NULL |    3 | Using index for group-by | 
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ 
1 row in set (0.00 sec)

除了上面的三個說明，還需要注意rows的數值，多行之間的數值是乘積的關系，可以估算大概要處理的行數，如果乘積很大，那就很有優化的必要了。

一個SQL語句的優化例子

通過獲取EXPLAIN輸出的rows列中的值的乘積，可以很好地指示聯結（join）有多好。它大致告訴您MySQL必須檢查多少行才能執行查詢。這是優化SQL語句的前提依據。

下面的示例演示如何根據EXPLAIN提供的信息逐步優化多表聯接。

假設您計劃使用EXPLAIN檢查下面SELECT語句：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,tt.ProjectReference, tt.EstimatedShipDate,tt.ActualShipDate, tt.ClientID,tt.ServiceCodes, tt.RepetitiveID,tt.CurrentProcess, tt.CurrentDPPerson,tt.RecordVolume, tt.DPPrinted, et.COUNTRY,et_1.COUNTRY, do.CUSTNAMEFROM tt, et, et AS et_1, doWHERE tt.SubmitTime IS NULLAND tt.ActualPC = et.EMPLOYIDAND tt.AssignedPC = et_1.EMPLOYIDAND tt.ClientID = do.CUSTNMBR;

對于此示例，作出以下假設：

  • 相關列聲明如下：
TableColumnData Type
ttActualPCCHAR(10)
ttAssignedPCCHAR(10)
ttClientIDCHAR(10)
etEMPLOYIDCHAR(15)
doCUSTNMBRCHAR(15)
  • 相關表有如下的索引：
TableIndex
ttActualPC
ttAssignedPC
ttClientID
etEMPLOYID (primary key)
doCUSTNMBR (primary key)
  • 這個 tt.ActualPC 值不是均勻分布的。

首先，在執行任何優化之前，使用EXPLAIN后會生成以下信息：

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872ClientID,ActualPCRange checked for each record (index map: 0x23)

由于每一個表對應的type是ALL，所以這個輸出結果表明MySQL正在生成所有表的笛卡爾積，也就是說，表之間的每一行的組合。

稍微計算一下乘積，74×2135×74×3872=45268558720行。檢查這行數相當耗時。如果表再大一點，這將要等到猴年馬月啊。

針對這里的問題，如果將列聲明為相同的類型和大小，MySQL可以更有效地對列使用索引。在這個上下文中，如果VARCHAR和CHAR聲明為相同的大小，則它們被認為是相同的。tt.ActualPC 的類型聲明為CHAR（10）并且 et.EMPLOYID 的是CHAR（15），因此長度不匹配。

若要修復列長度之間的差異，使用ALTER TABLE將ActualPC從10個字符延長到15個字符（第一次修改）：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

修改后，再次執行EXPLAIN生成如下結果:

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    UsingClientID,                                         whereActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

與上次的相比，縮小了74倍，差強人意。

在第二次修改，可以為tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 消除列之長度差。

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),MODIFY ClientID   VARCHAR(15);

修改后，再次執行EXPLAIN生成如下結果:

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   UsingClientID,                                         whereActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

到這里，該查詢幾乎盡可能被優化了。接下來的問題是，MySQL默認在tt.ActualPC列的值是均勻分布的，但tt表的其他列則不這么認為。

幸運的是，讓MySQL分析關鍵分布情況是很容易的：

mysql> ANALYZE TABLE tt;

隨著額外的索引信息的幫助下，該SQL聯結接變得完美了。EXPLAIN生成如下結果：

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 UsingClientID,                                        whereActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAIN輸出中的rows列是來自MySQL連接優化器的有根據的推測。通過將rows乘積與查詢返回的實際行數進行比較，檢查這些數字是否甚至接近真實值。如果數字相差很大，參閱官方文檔，獲取更多解決方案。Link

參考資料

  1. MySQL 8.0 Reference Manual - 8.8 Understanding the Query Execution Plan
  2. mysql explain用法和結果的含義

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/445934.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/445934.shtml
英文地址，請注明出處：http://en.pswp.cn/news/445934.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

FFmpeg源代碼簡單分析-其他-日志輸出系統（av_log()等）

FFmpeg源代碼簡單分析-其他-日志輸出系統（av_log()等）

參考鏈接 FFmpeg源代碼簡單分析&#xff1a;日志輸出系統&#xff08;av_log()等&#xff09;_雷霄驊的博客-CSDN博客_ffmpeg源碼分析 日志輸出系統&#xff08;av_log()等&#xff09; 本文分析一下FFmpeg的日志&#xff08;Log&#xff09;輸出系統的源代碼。日志輸出部分的…
閱讀更多...
FFmpeg源代碼簡單分析-其他-AVClass和AVoption

FFmpeg源代碼簡單分析-其他-AVClass和AVoption

參考鏈接 FFmpeg源代碼簡單分析&#xff1a;結構體成員管理系統-AVClass_雷霄驊的博客-CSDN博客FFmpeg源代碼簡單分析&#xff1a;結構體成員管理系統-AVOption_雷霄驊的博客-CSDN博客 概述 AVOption用于在FFmpeg中描述結構體中的成員變量。它最主要的作用可以概括為兩個字&a…
閱讀更多...
oracle手工收集awr報告_oracle手工生成AWR報告方法記錄-阿里云開發者社區

oracle手工收集awr報告_oracle手工生成AWR報告方法記錄-阿里云開發者社區

AWR(Automatic Workload Repository)報告是我們進行日常數據庫性能評定、問題SQL發現的重要手段。熟練掌握AWR報告&#xff0c;是做好開發、運維DBA工作的重要基本功。AWR報告的原理是基于Oracle數據庫的定時鏡像功能。默認情況下&#xff0c;Oracle數據庫后臺進程會以一定間隔…
閱讀更多...
IntelliJ IDEA 默認快捷鍵大全

IntelliJ IDEA 默認快捷鍵大全

文章目錄Remember these ShortcutsGeneralDebuggingSearch / ReplaceEditingRefactoringNavigationCompile and RunUsage SearchVCS / Local HistoryLive Templates參考資料Remember these Shortcuts 常用功能快捷鍵備注●Smart code completionCtrl Shift Space-●Search e…
閱讀更多...
python爬蟲的數據如何解決亂碼_寫爬蟲時如何解決網頁亂碼問題

python爬蟲的數據如何解決亂碼_寫爬蟲時如何解決網頁亂碼問題

實戰講解&#xff0c;文章較長&#xff0c;對爬蟲比較熟悉的瀏覽翻看章節 2.3 獲取新聞文本內容。寫爬蟲時經常對網址發起請求&#xff0c;結果返回的html數據除了標簽能看懂&#xff0c;其他的全部是亂碼。大家如果對爬蟲感興趣&#xff0c;請耐心閱讀本文&#xff0c;我們就以…
閱讀更多...
FFmpeg源代碼簡單分析-其他-libswscale的sws_getContext()

FFmpeg源代碼簡單分析-其他-libswscale的sws_getContext()

參考鏈接 FFmpeg源代碼簡單分析&#xff1a;libswscale的sws_getContext()_雷霄驊的博客-CSDN博客 libswscale的sws_getContext() FFmpeg中類庫libswsscale用于圖像處理&#xff08;縮放&#xff0c;YUV/RGB格式轉換&#xff09;libswscale是一個主要用于處理圖片像素數據的類…
閱讀更多...
IntelliJ IDEA 學習筆記

IntelliJ IDEA 學習筆記

IDEA教學視頻 文章目錄1.IntelliJ IDEA的介紹和優勢IDEA 的主要優勢2.版本介紹與安裝前的準備3.IDEA的卸載4.IDEA的安裝5.安裝目錄和設置目錄結構的說明安裝目錄設置目錄6.啟動IDEA并執行HelloWorld7.Module的使用8.IDEA的常用設置9.快捷鍵的設置10.常用的快捷鍵的使用111.常用…
閱讀更多...
機器學習頂刊文獻_人工智能頂刊TPAMI2019最新《多模態機器學習綜述》

機器學習頂刊文獻_人工智能頂刊TPAMI2019最新《多模態機器學習綜述》

原標題&#xff1a;人工智能頂刊TPAMI2019最新《多模態機器學習綜述》來源&#xff1a;專知摘要&#xff1a;”當研究問題或數據集包括多個這樣的模態時&#xff0c;其特征在于多模態。【導讀】人工智能領域最頂級國際期刊IEEE Transactions on Pattern Analysis and Machine I…
閱讀更多...
Windows上同時運行兩個Tomcat

Windows上同時運行兩個Tomcat

步驟 1.獲得免安裝包 從Tomcat官網下載免安裝包。 2.解壓復制 解壓并創建兩個副本tomcat1和tomcat2&#xff0c;它們的路徑分別為&#xff1a; tomcat1&#xff1a;C:\tomcat\double\apache-tomcat-7.0.90-8081tomcat2&#xff1a;C:\tomcat\double\apache-tomcat-7.0.90-…
閱讀更多...
FFmpeg源代碼簡單分析-其他-libswscale的sws_scale()

FFmpeg源代碼簡單分析-其他-libswscale的sws_scale()

參考鏈接 FFmpeg源代碼簡單分析&#xff1a;libswscale的sws_scale()_雷霄驊的博客-CSDN博客_bad dst image pointers libswscale的sws_scale() FFmpeg的圖像處理&#xff08;縮放&#xff0c;YUV/RGB格式轉換&#xff09;類庫libswsscale中的sws_scale()函數。libswscale是一…
閱讀更多...
布朗橋python_MATLAB 里面有哪些加快程序運行速度的方法呢，求分享？

布朗橋python_MATLAB 里面有哪些加快程序運行速度的方法呢，求分享？

挖墳了…睡不著覺當個備忘錄記一下用過的方法吧1. 循環向量化2. 利用函數的矩陣輸入功能批量處理3. 必須用for且費時的地方改成單層parfor&#xff0c;要是循環次數比cpu核數還少反而會拖慢程序4. 非常大的矩陣的運算可以用gpuArray(這個在matlab 深度學習工具箱中深有體會)5. …
閱讀更多...
FFmpeg源代碼簡單分析-其他-libavdevice的avdevice_register_all()

FFmpeg源代碼簡單分析-其他-libavdevice的avdevice_register_all()

參考鏈接 FFmpeg源代碼簡單分析&#xff1a;libavdevice的avdevice_register_all()_雷霄驊的博客-CSDN博客 libavdevice的avdevice_register_all() FFmpeg中libavdevice注冊設備的函數avdevice_register_all()。avdevice_register_all()在編程中的使用示例可以參考文章&#…
閱讀更多...
Tomcat無需輸入項目名，直接用域名訪問項目

Tomcat無需輸入項目名，直接用域名訪問項目

問題 在Tomcat上開發Web應用&#xff0c;通常是將應用放置Tomcat主目錄下webapps&#xff0c;然后在瀏覽器地址欄輸入域名應用名&#xff08;如http://localhost:8080/app&#xff09;對應用進行訪問。 為了方便開發&#xff0c;打算直接用域名訪問項目。例如&#xff0c;在瀏…
閱讀更多...
藍牙該串口設備不存在或已被占用_電腦識別不了串口設備如何解決_電腦檢測不到串口怎么辦...

藍牙該串口設備不存在或已被占用_電腦識別不了串口設備如何解決_電腦檢測不到串口怎么辦...

2015-09-07 10:46:45win8.1系統USB轉串口不能識別設備出現錯誤代碼10的解決方法分享給大家&#xff0c;win8.1系統插入USB設備提示“指定不存在的設備”&#xff0c;左下角有小黃色感嘆號&#xff0c;導致設備無法識別不能識別...2016-12-02 10:52:57一般情況下&#xff0c;win…
閱讀更多...
FFmpeg源代碼簡單分析-其他-libavdevice的gdigrab

FFmpeg源代碼簡單分析-其他-libavdevice的gdigrab

參考鏈接 FFmpeg源代碼簡單分析&#xff1a;libavdevice的gdigrab_雷霄驊的博客-CSDN博客_gdigrab libavdevice的gdigrab GDIGrab用于在Windows下屏幕錄像&#xff08;抓屏&#xff09;gdigrab的源代碼位于libavdevice\gdigrab.c。關鍵函數的調用關系圖如下圖所示。圖中綠色背…
閱讀更多...
分區和分片的區別_PHP: 分區和分片 - Manual

分區和分片的區別_PHP: 分區和分片 - Manual

分區和分片數據庫群組是由于各種各樣的原因建立的&#xff0c;他可以提升處理能力、容忍錯誤&#xff0c;并且提升大量服務器同時工作的的性能。群組有時會組合分區和共享功能&#xff0c;來將大量復雜的任務分拆成更加簡單的任務&#xff0c;更加可控的單元。插件可以支持各種…
閱讀更多...
Ubuntu安裝GmSSL庫適用于ubuntu18和ubuntu20版本

Ubuntu安裝GmSSL庫適用于ubuntu18和ubuntu20版本

參考鏈接 編譯與安裝【GmSSL】GmSSL 與 OpenSSL 共存的安裝方法_阿卡基YUAN的博客-CSDN博客_openssl和gmssl在Linux下安裝GmSSL_百里楊的博客-CSDN博客_安裝gmssl ubuntu18操作 需要超級管理員權限本人將下載的安裝包master.zip和安裝的位置都設定在/usr/local下創建文件夾/u…
閱讀更多...
Windows7右鍵菜單欄添加打開cmd項

Windows7右鍵菜單欄添加打開cmd項

背景簡介 眾所周知&#xff0c;在Linux桌面操作系統中的工作目錄窗口中&#xff0c;單擊鼠標右鍵&#xff0c;彈出的菜單欄通常有一項“打開終端”&#xff0c;然后移動鼠標點擊該項&#xff0c;就可以打開Shell窗口&#xff0c;在當前工作目錄進行命令行操作。 但是&#xf…
閱讀更多...
python11_Python11，文件操作

python11_Python11，文件操作

整了這么多雜七雜八又“沒用”的&#xff0c;終于來點實際的操作了。Python中用open()方法來對打開文件。我們來看看它的用法&#xff1a;path "C:\\Users\Frank\Desktop\\text.txt"f open(path,r,encoding"utf-8")首先給變量path指定一個路徑&#xff0…
閱讀更多...
在ubuntu環境下執行openssl編譯和安裝

在ubuntu環境下執行openssl編譯和安裝

參考鏈接 工具系列 | Ubuntu18.04安裝Openssl-1.1.1_Tinywan的技術博客_51CTO博客密碼學專題 openssl編譯和安裝_MY CUP OF TEA的博客-CSDN博客_openssl 編譯安裝 下載 /source/index.html編譯 使用命令sudo tar -xvzf openssl-1.1.1q.tar.gz 解壓。使用cd openssl-1.1.1q/進…
閱讀更多...
最新文章

Copyright @ 2022~2023