MySQL EXPLAIN命令詳解學習(檢視執行計劃)
MySQL的EXPLAIN命令用於SQL語句的查詢執行計劃(QEP)。這條命令的輸出結果能夠讓我們瞭解MySQL 最佳化器是如何執行
SQL 語句的。這條命令並沒有提供任何調整建議,但它能夠提供重要的資訊幫助你做出調優決策。
1 語法
MySQL 的EXPLAIN 語法可以執行在SELECT 語句或者特定表上。如果作用在表上,那麼此命令等同於DESC 表命令。UPDATE
和DELETE 命令也需要進行效能改進,當這些命令不是直接在表的主碼上執行時,為了確保最最佳化的索引使用率,需要把它們改
寫成SELECT 語句(以便對它們執行EXPLAIN 命令)。請看下面的示例:
這個UPDATE語句可以被重寫成為下面這樣的SELECT語句:
在5.6.10版本里面,是可以直接對dml語句進行explain分析操作的.
MySQL 最佳化器是基於開銷來工作的,它並不提供任何的QEP的位置。這意味著QEP 是在每條SQL 語句執行的時候動態地計
算出來的。在MySQL 儲存過程中的SQL 語句也是在每次執行時計算QEP 的。儲存過程快取僅僅解析查詢樹。
2 各列詳解
MySQL EXPLAIN命令能夠為SQL語句中的每個表生成以下資訊:
這個QEP 顯示沒有使用任何索引(也就是全表掃描)並且處理了大量的行來滿足查詢。對同樣一條SELECT 語句,一個最佳化過的QEP 如下所示:
在這個QEP 中,我們看到使用了一個索引,且估計只有一行資料將被獲取。
QEP 中每個行的所有列表如下所示:
? id
? select_type
? table
? partitions(這一列只有在EXPLAIN PARTITIONS 語法中才會出現)
? possible_keys
? key
? key_len
? ref
? rows
? filtered(這一列只有在EXPLAINED EXTENDED 語法中才會出現)
? Extra
這些列展示了SELECT 語句對每一個表的QEP。一個表可能和一個物理模式表或者在SQL 執行時生成的內部臨時表(例如從子查詢或者合併操作會產生內部臨時表)相關聯。
可以參考MySQL Reference Manual 獲得更多資訊:http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。
2.1 key
key 列指出最佳化器選擇使用的索引。一般來說SQL 查詢中的每個表都僅使用一個索引。也存在索引合併的少數例外情況,如給定表上用到了兩個或者更多索引。
下面是QEP 中key 列的示例:
key: item_id
key: NULL
key: first, last
SHOW CREATE TABLE命令是最簡單的檢視錶和索引列細節的方式。和key 列相關的列還包括possible_keys、rows 以及key_len。
2.2 ROWS
rows 列提供了試圖分析所有存在於累計結果集中的行數目的MySQL 最佳化器估計值。QEP 很容易描述這個很困難的統計量。
查詢中總的讀運算元量是基於合併之前行的每一行的rows 值的連續積累而得出的。這是一種巢狀行演算法。
以連線兩個表的QEP 為例。透過id=1 這個條件找到的第一行的rows 值為1,這等於對第一個表做了一次讀操作。第二行是
透過id=2 找到的,rows 的值為5。這等於有5 次讀運算子合當前1 的積累量。參考兩個表,讀操作的總數目是6。在另一個QEP
中,第一rows 的值是5,第二rows 的值是1。這等於第一個表有5 次讀操作,對5個積累量中每個都有一個讀操作。因此兩個表
總的讀操作的次數是10(5+5)次。
最好的估計值是1,一般來說這種情況發生在當尋找的行在表中可以透過主鍵或者唯一鍵找到的時候。
在下面的QEP 中,外面的巢狀迴圈可以透過id=1 來找到,其估計的物理行數是1。第二個迴圈處理了10行。
可以使用SHOW STATUS 命令來檢視實際的行操作。這個命令可以提供最佳的確認物理行操作的方式。請看下面的示例:
在下一個QEP 中,透過id=1 找到的外層巢狀迴圈估計有160行。第二個迴圈估計有1 行。
透過SHOW STATUS 命令可以檢視實際的行操作,該命令表明物理讀運算元量大幅增加。請看下面的示例:
2.3 possible_keys
possible_keys 列指出最佳化器為查詢選定的索引。
一個會列出大量可能的索引(例如多於3 個)的QEP 意味著備選索引數量太多了,同時也可能提示存在一個無效的單列索引。
可以用第2 章詳細介紹過的SHOW INDEXES 命令來檢查索引是否有效且是否具有合適的基數。
為查詢確定QEP 的速度也會影響到查詢的效能。如果發現有大量的可能的索引,則意味著這些索引沒有被使用到。
相關的QEP 列還包括key 列。
2.4 key_len
key_len 列定義了用於SQL 語句的連線條件的鍵的長度。此列值對於確認索引的有效性以及多列索引中用到的列的數目很重要。
常見的計算規律為:
① 1個utf8字符集的字元佔用3個位元組;1個gbk字符集的字元佔用2個位元組。
② 對於變長的型別(VARCHAR),key_len還要加2位元組;若欄位允許為空,則key_len需要加1;
③ INT型別的長度為4。
④ 對於DATATIME型別的欄位,在MySQL 5.6.4以前是8個位元組(不能儲存小數位),之後的長度為5個位元組再加上小數位位元組數。DATATIME最大小數位是6。若小數位為1或2,則總位元組數為6(5+1);若小數位為3或4,則總位元組數為7(5+2);若小數位為5或6,則總位元組數為8(5+3)。
此列的一些示例值如下所示:
此列的一些示例值如下所示:
key_len: 4 // INT NOT NULL
key_len: 5 // INT NULL
key_len: 30 // CHAR(30) NOT NULL
key_len: 32 // VARCHAR(30) NOT NULL
key_len: 92 // VARCHAR(30) NULL CHARSET=utf8
從這些示例中可以看出,是否可以為空、可變長度的列以及key_len 列的值只和用在連線和WHERE 條件中的索引的列
有關。索引中的其他列會在ORDER BY 或者GROUP BY 語句中被用到。下面這個來自於著名的開源部落格軟體WordPress 的表展示了
如何以最佳方式使用帶有定義好的表索引的SQL 語句:
這個表的索引包括post_type、post_status、post_date 以及ID列。下面是一個演示索引列用法的SQL 查詢:
EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';
這個查詢的QEP 返回的key_len 是62。這說明只有post_type列上的索引用到了(因為(20×3)+2=62)。儘管查詢在WHERE 語句
中使用了post_type 和post_date 列,但只有post_type 部分被用到了。其他索引沒有被使用的原因是MySQL 只能使用定義索引的
最左邊部分。為了更好地利用這個索引,可以修改這個查詢來調整索引的列。請看下面的示例:
在SELECT查詢的新增一個post_status 列的限制條件後,QEP顯示key_len 的值為132,這意味著post_type、post_status、post_date
三列(62+62+8,(20×3)+2,(20×3)+2,8)都被用到了。此外,這個索引的主碼列ID 的定義是使用MyISAM 儲存索
引的遺留痕跡。當使用InnoDB 儲存引擎時,在非主碼索引中包含主碼列是多餘的,這可以從key_len 的用法看出來。
相關的QEP 列還包括帶有Using index 值的Extra 列。
2.5 table
table 列是EXPLAIN 命令輸出結果中的一個單獨行的唯一識別符號。這個值可能是表名、表的別名或者一個為查詢產生臨時表
的識別符號,如派生表、子查詢或集合。下面是QEP 中table 列的一些示例:
table: item
table:
table:
表中N 和M 的值參考了另一個符合id 列值的table 行。相關的QEP 列還有select_type
2.6 select_type
select_type 列提供了各種表示table 列引用的使用方式的型別。最常見的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能
的值還有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。
1. SIMPLE
對於不包含子查詢和其他複雜語法的簡單查詢,這是一個常 見的型別。
2. PRIMARY
這是為更復雜的查詢而建立的首要表(也就是最外層的表)。這個型別通常可以在DERIVED 和UNION 型別混合使用時見到。
3. DERIVED
當一個表不是一個物理表時,那麼就被叫做DERIVED。下面的SQL 語句給出了一個QEP 中DERIVED select-type 型別的
示例:
mysql> EXPLAIN SELECT MAX(id)
-> FROM (SELECT id FROM users WHERE first = 'west') c;
4. DEPENDENT SUBQUERY
這個select-type 值是為使用子查詢而定義的。下面的SQL語句提供了這個值:
mysql> EXPLAIN SELECT p.*
-> FROM parent p
-> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);
5. UNION
這是UNION 語句其中的一個SQL 元素。
6. UNION RESULT
這是一系列定義在UNION 語句中的表的返回結果。當select_type 為這個值時,經常可以看到table 的值是,
這說明匹配的id 行是這個集合的一部分。下面的SQL產生了一個UNION和UNION RESULT select-type:
mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val
LIKE 'a%'
-> UNION
-> SELECT p.* FROM parent p WHERE p.id > 5;
2.7 partitions
partitions 列代表給定表所使用的分割槽。這一列只會在EXPLAIN
PARTITIONS 語句中出現。
2.8 Extra
Extra 列提供了有關不同種類的MySQL 最佳化器路徑的一系列
額外資訊。Extra 列可以包含多個值,可以有很多不同的取值,並
且這些值還在隨著MySQL 新版本的釋出而進一步增加。下面給
出常用值的列表。你可以從下面的地址找到更全面的值的列表:
http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。
1. Using where
這個值表示查詢使用了where 語句來處理結果——例如執行
全表掃描。如果也用到了索引,那麼行的限制條件是透過獲取必
要的資料之後處理讀緩衝區來實現的。
2. Using temporary
這個值表示使用了內部臨時(基於記憶體的)表。一個查詢可能
用到多個臨時表。有很多原因都會導致MySQL 在執行查詢期間
建立臨時表。兩個常見的原因是在來自不同表的列上使用了
DISTINCT,或者使用了不同的ORDER BY 和GROUP BY 列。
想了解更多內容可以訪問_
of_query_execution_and_use_of_temp_tables。
可以強制指定一個臨時表使用基於磁碟的MyISAM 儲存引
擎。這樣做的原因主要有兩個:
? 內部臨時表佔用的空間超過min(tmp_table_size,max_
heap_table_size)系統變數的限制
? 使用了TEXT/BLOB 列
3. Using filesort
這是ORDER BY 語句的結果。這可能是一個CPU 密集型的過程。
可以透過選擇合適的索引來改進效能,用索引來為查詢結果排序。詳細過程請參考第4 章。
4. Using index
這個值重點強調了只需要使用索引就可以滿足查詢表的要求,不需要直接訪問表資料。請參考第5 章的詳細示例來理解這
個值。
5. Using join buffer
這個值強調了在獲取連線條件時沒有使用索引,並且需要連線緩衝區來儲存中間結果。
如果出現了這個值,那應該注意,根據查詢的具體情況可能需要新增索引來改進效能。
6. Impossible where
這個值強調了where 語句會導致沒有符合條件的行。請看下面的示例:
mysql> EXPLAIN SELECT * FROM user WHERE 1=2;
7. Select tables optimized away
這個值意味著僅透過使用索引,最佳化器可能僅從聚合函式結果中返回一行。請看下面的示例:
8. Distinct
這個值意味著MySQL 在找到第一個匹配的行之後就會停止搜尋其他行。
9. Index merges
當MySQL 決定要在一個給定的表上使用超過一個索引的時候,就會出現以下格式中的一個,詳細說明使用的索引以及合併的型別。
? Using sort_union(...)
? Using union(...)
? Using intersect(...)
2.9 id
id 列是在QEP 中展示的表的連續引用。
2.10 ref
ref 列可以被用來標識那些用來進行索引比較的列或者常量。
2.11 filtered
filtered 列給出了一個百分比的值,這個百分比值和rows 列的值一起使用,可以估計出那些將要和QEP 中的前一個表進行連
接的行的數目。前一個表就是指id 列的值比當前表的id 小的表。這一列只有在EXPLAIN EXTENDED 語句中才會出現。
2.12 type
type 列代表QEP 中指定的表使用的連線方式。下面是最常用的幾種連線方式:
? const 當這個表最多隻有一行匹配的行時出現system 這是const 的特例,當表只有一個row 時會出現
? eq_ref 這個值表示有一行是為了每個之前確定的表而讀取的
? ref 這個值表示所有具有匹配的索引值的行都被用到
? range 這個值表示所有符合一個給定範圍值的索引行都被用到
? ALL 這個值表示需要一次全表掃描其他型別的值還有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。
想了解更多資訊可以訪問http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。
3 解釋EXPLAIN 輸出結果
理解你的應用程式(包括技術和實現可能性)和最佳化SQL 語句同等重要。下面給出一個從父子關係中獲取孤立的父輩記錄的商
業需求的例子。這個查詢可以用三種不同的方式構造。儘管會產生相同的結果,但QEP 會顯示三種不同的路徑。
要使用執行計劃,首先要讀懂執行計劃,然後透過改寫SQL和索引技術來改進執行計劃。
MySQL5.6.3之前只有 SELECT 可以生成執行計劃,5.6.3及之後的版本SELECT DELETE INSERT REPLACE UPDATE都可以生成執行計劃。
explain語法:
{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] explainable_stmt explain_type: { EXTENDED | PARTITIONS | FORMAT = format_name } format_name: { TRADITIONAL | JSON } explainable_stmt: { SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement }看到了吧,檢視執行計劃不只explain命令,desc也可以,結果一樣。
mysql> desc select * from p_range where id=12;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | p_range | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> desc extended select * from p_range where id=12;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |filtered | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | p_range | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)
有一個warning,可以看看
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '12' AS `id`,'員工JONES' AS `name` from `test`.`p_range` where 1 |
+-------+------+------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
警告資訊顯示最佳化器最佳化後執行的SQL。再看一個複雜點的:
mysql> desc extended select * from emp where deptno in (select deptno from dept where deptno=20);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | dept | const | PRIMARY | PRIMARY | 1 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | 100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`emp`.`empno` AS `empno`,`test`.`emp`.`ename` AS `ename`,`test`.`emp`.`job` AS `job`,`test`.`emp`.`mgr` AS `mgr`,`test`.`emp`.`hiredate` AS `hiredate`,`test`.`emp`.`sal` AS `sal`,`test`.`emp`.`comm` AS `comm`,`test`.`emp`.`deptno` AS `deptno` from `test`.`dept` join `test`.`emp` where (`test`.`emp`.`deptno` = 20) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
從警告裡可以看出最佳化器最終將*替換成所有的列名,這不但增加了sql文字的長度佔用更多記憶體,還會使返回的資料量增大,所以在select列表裡一定要寫明所選列的列名,尤其當表中列特別多時更應寫出列名,只選要檢視的列。
mysql> desc partitions select * from p_range where id=12;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | p_range | p0 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
執行計劃的解釋可以參與這裡:http://dev.mysql.com/doc/refman/5.6/en/explain-output.html
以下摘錄一部分:
EXPLAIN Output Columns
This section describes the output columns produced by EXPLAIN. Later sections provide additional information about the type and Extra columns.
Each output row from EXPLAIN provides information about one table. Each row contains the values summarized in Table 8.1, “EXPLAIN Output Columns”, and described in more detail following the table. Column names are shown in the table's first column; the second column provides the equivalent property name shown in the output when FORMAT=JSON is used.
Table 8.1 EXPLAIN Output Columns
Column | JSON Name | Meaning |
---|---|---|
id | select_id | The SELECT identifier |
select_type | None | The SELECT type |
table | table_name | The table for the output row |
partitions | partitions | The matching partitions |
type | access_type | The join type |
possible_keys | possible_keys | The possible indexes to choose |
key | key | The index actually chosen |
key_len | key_length | The length of the chosen key |
ref | ref | The columns compared to the index |
rows | rows | Estimate of rows to be examined |
filtered | filtered | Percentage of rows filtered by table condition |
Extra | None | Additional information |
JSON properties which are NULL are not displayed in JSON-formatted EXPLAIN output.
-
id (JSON name: select_id)
The SELECT identifier. This is the sequential number of the SELECT within the query. The value can be NULL if the row refers to the union result of other rows. In this case, the table column shows a value like <unionM,N> to indicate that the row refers to the union of the rows with id values of M and N.
-
select_type (JSON name: none)
The type of SELECT, which can be any of those shown in the following table. A JSON-formatted EXPLAIN exposes the SELECT type as a property of aquery_block, unless it is SIMPLE or PRIMARY. The JSON names (where applicable) are also shown in the table.
select_type Value JSON Name Meaning SIMPLE None Simple SELECT (not using UNION or subqueries) PRIMARY None Outermost SELECT UNION None Second or later SELECT statement in a UNION DEPENDENT UNION dependent (true) Second or later SELECT statement in a UNION, dependent on outer query UNION RESULT union_result Result of a UNION. SUBQUERY None First SELECT in subquery DEPENDENT SUBQUERY dependent (true) First SELECT in subquery, dependent on outer query DERIVED None Derived table SELECT (subquery in FROM clause) MATERIALIZED materialized_from_subquery Materialized subquery UNCACHEABLE SUBQUERY cacheable (false) A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query UNCACHEABLE UNION cacheable (false) The second or later select in a UNION that belongs to an uncacheable subquery (seeUNCACHEABLE SUBQUERY) DEPENDENT typically signifies the use of a correlated subquery. See Section 13.2.10.7, “Correlated Subqueries”.
DEPENDENT SUBQUERY evaluation differs from UNCACHEABLE SUBQUERY evaluation. For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. For UNCACHEABLE SUBQUERY, the subquery is re-evaluated for each row of the outer context.
Cacheability of subqueries differs from caching of query results in the query cache (which is described in Section 8.10.3.1, “How the Query Cache Operates”). Subquery caching occurs during query execution, whereas the query cache is used to store results only after query execution finishes.
When you specify FORMAT=JSON with EXPLAIN, the output has no single property directly equivalent to select_type; the query_block property corresponds to a given SELECT. Properties equivalent to most of the SELECT subquery types just shown are available (an example beingmaterialized_from_subquery for MATERIALIZED), and are displayed when appropriate. There are no JSON equivalents for SIMPLE or PRIMARY.
-
table (JSON name: table_name)
The name of the table to which the row of output refers. This can also be one of the following values:
-
<unionM,N>: The row refers to the union of the rows with id values of M and N.
-
<derivedN>: The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in theFROM clause.
-
<subqueryN>: The row refers to the result of a materialized subquery for the row with an id value of N. See Section 8.2.1.18.2, “Optimizing Subqueries with Subquery Materialization”.
-
-
partitions (JSON name: partitions)
The partitions from which records would be matched by the query. This column is displayed only if the PARTITIONS keyword is used. The value is NULL for nonpartitioned tables. See Section 19.3.5, “Obtaining Information About Partitions”.
-
type (JSON name: access_type)
The join type. For descriptions of the different types, see EXPLAIN Join Types.
-
possible_keys (JSON name: possible_keys)
The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table. Note that this column is totally independent of the order of the tables as displayed in the output from EXPLAIN. That means that some of the keys in possible_keys might not be usable in practice with the generated table order.
If this column is NULL (or undefined in JSON-formatted output), there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again. See Section 13.1.7, “ALTER TABLE Syntax”.
To see what indexes a table has, use SHOW INDEX FROM tbl_name.
-
key (JSON name: key)
The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.
It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.
For InnoDB, a secondary index might cover the selected columns even if the query also selects the primary key because InnoDB stores the primary key value with each secondary index. If key is NULL, MySQL found no index to use for executing the query more efficiently.
To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query. SeeSection 8.9.3, “Index Hints”.
For MyISAM and NDB tables, running ANALYZE TABLE helps the optimizer choose better indexes. For NDB tables, this also improves performance of distributed pushed-down joins. For MyISAM tables, myisamchk --analyze does the same as ANALYZE TABLE. See Section 7.6, “MyISAM Table Maintenance and Crash Recovery”.
-
key_len (JSON name: key_length)
The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value ofkey_len enables you to determine how many parts of a multiple-part key MySQL actually uses.
-
ref (JSON name: ref)
The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
If the value is func, the value used is the result of some function. To see which function, use EXPLAIN EXTENDED followed by SHOW WARNINGS. The function might actually be an operator such as an arithmetic operator.
-
rows (JSON name: rows)
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
For InnoDB tables, this number is an estimate, and may not always be exact.
-
filtered (JSON name: filtered)
The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. This column is displayed if you useEXPLAIN EXTENDED.
-
Extra (JSON name: none)
This column contains additional information about how MySQL resolves the query. For descriptions of the different values, see EXPLAIN Extra Information.
There is no single JSON property corresponding to the Extra column; however, values that can occur in this column are exposed as JSON properties, or as the text of the message property.
EXPLAIN Join Types
The type column of EXPLAIN output describes how tables are joined. In JSON-formatted output, these are found as values of the access_type property. The following list describes the join types, ordered from the best type to the worst:
-
The table has only one row (= system table). This is a special case of the const join type.
-
The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.
const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as aconst table:
SELECT * FROM tbl_name WHERE primary_key=1; SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
-
One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.
eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an eq_ref join to process ref_table:
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
-
All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.
ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can use a ref join to processref_table:
SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
-
The join is performed using a FULLTEXT index.
-
This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use a ref_or_null join to process ref_table:
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
-
This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_lencontains a list of the longest key parts for the indexes used. For more information, see Section 8.2.1.4, “Index Merge Optimization”.
-
This type replaces eq_ref for some IN subqueries of the following form:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.
-
This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:
value IN (SELECT key_column FROM single_table WHERE some_expr)
-
Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. Thekey_len contains the longest key part that was used. The ref column is NULL for this type.
range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN() operators:
SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
-
The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:
-
If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, theExtra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.
-
A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.
MySQL can use this join type when the query uses only columns that are part of a single index.
-
-
A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.
EXPLAIN Extra Information
The Extra column of EXPLAIN output contains additional information about how MySQL resolves the query. The following list explains the values that can appear in this column. Each item also indicates for JSON-formatted output which property displays the Extra value. For some of these, there is a specific property. The others display as the text of the message property.
If you want to make your queries as fast as possible, look out for Extra column values of Using filesort and Using temporary, or, in JSON-formattedEXPLAIN output, for using_filesort and using_temporary_table properties equal to true.
-
Child of 'table' pushed join@1 (JSON: message text)
This table is referenced as the child of table in a join that can be pushed down to the NDB kernel. Applies only in MySQL Cluster, when pushed-down joins are enabled. See the description of the ndb_join_pushdown server system variable for more information and examples.
-
const row not found (JSON property: const_row_not_found)
For a query such as SELECT ... FROM tbl_name, the table was empty.
-
Deleting all rows (JSON property: message)
For DELETE, some storage engines (such as MyISAM) support a handler method that removes all table rows in a simple and fast way. This Extra value is displayed if the engine uses this optimization.
-
Distinct (JSON property: distinct)
MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.
-
FirstMatch(tbl_name) (JSON property: first_match)
The semi-join FirstMatch join shortcutting strategy is used for tbl_name.
-
Full scan on NULL key (JSON property: message)
This occurs for subquery optimization as a fallback strategy when the optimizer cannot use an index-lookup access method.
-
Impossible HAVING (JSON property: message)
The HAVING clause is always false and cannot select any rows.
-
Impossible WHERE (JSON property: message)
The WHERE clause is always false and cannot select any rows.
-
Impossible WHERE noticed after reading const tables (JSON property: message)
MySQL has read all const (and system) tables and notice that the WHERE clause is always false.
-
LooseScan(m..n) (JSON property: message)
The semi-join LooseScan strategy is used. m and n are key part numbers.
-
Materialize, Scan (JSON: message text)
Before MySQL 5.6.7, this indicates use of a single materialized temporary table. If Scan is present, no temporary table index is used for table reads. Otherwise, an index lookup is used. See also the Start materialize entry.
As of MySQL 5.6.7, materialization is indicated by rows with a select_type value of MATERIALIZED and rows with a table value of <subqueryN>.
-
No matching min/max row (JSON property: message)
No row satisfies the condition for a query such as SELECT MIN(...) FROM ... WHERE condition.
-
no matching row in const table (JSON property: message)
For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition.
-
No matching rows after partition pruning (JSON property: message)
For DELETE or UPDATE, the optimizer found nothing to delete or update after partition pruning. It is similar in meaning to Impossible WHERE for SELECTstatements.
-
No tables used (JSON property: message)
The query has no FROM clause, or has a FROM DUAL clause.
For INSERT or REPLACE statements, EXPLAIN displays this value when there is no SELECT part. For example, it appears for EXPLAIN INSERT INTO t VALUES(10) because that is equivalent to EXPLAIN INSERT INTO t SELECT 10 FROM DUAL.
-
Not exists (JSON property: message)
MySQL was able to do a LEFT JOIN optimization on the query and does not examine more rows in this table for the previous row combination after it finds one row that matches the LEFT JOIN criteria. Here is an example of the type of query that can be optimized this way:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
Assume that t2.id is defined as NOT NULL. In this case, MySQL scans t1 and looks up the rows in t2 using the values of t1.id. If MySQL finds a matching row in t2, it knows that t2.id can never be NULL, and does not scan through the rest of the rows in t2 that have the same id value. In other words, for each row in t1, MySQL needs to do only a single lookup in t2, regardless of how many rows actually match in t2.
-
Range checked for each record (index map: N) (JSON property: message)
MySQL found no good index to use, but found that some of indexes might be used after column values from preceding tables are known. For each row combination in the preceding tables, MySQL checks whether it is possible to use a range or index_merge access method to retrieve rows. This is not very fast, but is faster than performing a join with no index at all. The applicability criteria are as described in Section 8.2.1.3, “Range Optimization”, andSection 8.2.1.4, “Index Merge Optimization”, with the exception that all column values for the preceding table are known and considered to be constants.
Indexes are numbered beginning with 1, in the same order as shown by SHOW INDEX for the table. The index map value N is a bitmask value that indicates which indexes are candidates. For example, a value of 0x19 (binary 11001) means that indexes 1, 4, and 5 will be considered.
-
Scanned N databases (JSON property: message)
This indicates how many directory scans the server performs when processing a query for INFORMATION_SCHEMA tables, as described in Section 8.2.4, “Optimizing INFORMATION_SCHEMA Queries”. The value of N can be 0, 1, or all.
-
Select tables optimized away (JSON property: message)
The optimizer determined 1) that at most one row should be returned, and 2) that to produce this row, a deterministic set of rows must be read. When the rows to be read can be read during the optimization phase (for example, by reading index rows), there is no need to read any tables during query execution.
The first condition is fulfilled when the query is implicitly grouped (contains an aggregate function but no GROUP BY clause). The second condition is fulfilled when one row lookup is performed per index used. The number of indexes read determines the number of rows to read.
Consider the following implicitly grouped query:
SELECT MIN(c1), MIN(c2) FROM t1;
Suppose that MIN(c1) can be retrieved by reading one index row and MIN(c2) can be retrieved by reading one row from a different index. That is, for each column c1 and c2, there exists an index where the column is the first column of the index. In this case, one row is returned, produced by reading two deterministic rows.
This Extra value does not occur if the rows to read are not deterministic. Consider this query:
SELECT MIN(c2) FROM t1 WHERE c1 <= 10;
Suppose that (c1, c2) is a covering index. Using this index, all rows with c1 <= 10 must be scanned to find the minimum c2 value. By contrast, consider this query:
SELECT MIN(c2) FROM t1 WHERE c1 = 10;
In this case, the first index row with c1 = 10 contains the minimum c2 value. Only one row must be read to produce the returned row.
For storage engines that maintain an exact row count per table (such as MyISAM, but not InnoDB), this Extra value can occur for COUNT(*) queries for which the WHERE clause is missing or always true and there is no GROUP BY clause. (This is an instance of an implicitly grouped query where the storage engine influences whether a deterministic number of rows can be read.)
-
Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table (JSON property: message)
These values indicate file-opening optimizations that apply to queries for INFORMATION_SCHEMA tables, as described in Section 8.2.4, “Optimizing INFORMATION_SCHEMA Queries”.
-
Skip_open_table: Table files do not need to be opened. The information has already become available within the query by scanning the database directory.
-
Open_frm_only: Only the table's .frm file need be opened.
-
Open_trigger_only: Only the table's .TRG file need be opened.
-
Open_full_table: The unoptimized information lookup. The .frm, .MYD, and .MYI files must be opened.
-
-
Start materialize, End materialize, Scan (JSON: message text)
Before MySQL 5.6.7, this indicates use of multiple materialized temporary tables. If Scan is present, no temporary table index is used for table reads. Otherwise, an index lookup is used. See also the Materialize entry.
As of MySQL 5.6.7, materialization is indicated by rows with a select_type value of MATERIALIZED and rows with a table value of <subqueryN>.
-
Start temporary, End temporary (JSON property: message)
This indicates temporary table use for the semi-join Duplicate Weedout strategy.
-
unique row not found (JSON property: message)
For a query such as SELECT ... FROM tbl_name, no rows satisfy the condition for a UNIQUE index or PRIMARY KEY on the table.
-
Using filesort (JSON property: using_filesort)
MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order. SeeSection 8.2.1.15, “ORDER BY Optimization”.
-
Using index (JSON property: using_index)
The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.
-
Using index condition (JSON property: using_index_condition)
Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary. See Section 8.2.1.6, “Index Condition Pushdown Optimization”.
-
Using index for group-by (JSON property: using_index_for_group_by)
Similar to the Using index table access method, Using index for group-by indicates that MySQL found an index that can be used to retrieve all columns of a GROUP BY or DISTINCT query without any extra disk access to the actual table. Additionally, the index is used in the most efficient way so that for each group, only a few index entries are read. For details, see Section 8.2.1.16, “GROUP BY Optimization”.
-
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access) (JSON property: using_join_buffer)
Tables from earlier joins are read in portions into the join buffer, and then their rows are used from the buffer to perform the join with the current table.(Block Nested Loop) indicates use of the Block Nested-Loop algorithm and (Batched Key Access) indicates use of the Batched Key Access algorithm. That is, the keys from the table on the preceding line of the EXPLAIN output will be buffered, and the matching rows will be fetched in batches from the table represented by the line in which Using join buffer appears.
In JSON-formatted output, the value of using_join_buffer is always either one of Block Nested Loop or Batched Key Access.
-
Using MRR (JSON property: message)
Tables are read using the Multi-Range Read optimization strategy. See Section 8.2.1.13, “Multi-Range Read Optimization”.
-
Using sort_union(...), Using union(...), Using intersect(...) (JSON property: message)
These indicate how index scans are merged for the index_merge join type. See Section 8.2.1.4, “Index Merge Optimization”.
-
Using temporary (JSON property: using_temporary_table)
To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BYclauses that list columns differently.
-
Using where (JSON property: attached_condition)
A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
Using where has no direct counterpart in JSON-formatted output; the attached_condition property contains any WHERE condition used.
-
Using where with pushed condition (JSON property: message)
This item applies to NDB tables only. It means that MySQL Cluster is using the Condition Pushdown optimization to improve the efficiency of a direct comparison between a nonindexed column and a constant. In such cases, the condition is “pushed down” to the cluster's data nodes and is evaluated on all data nodes simultaneously. This eliminates the need to send nonmatching rows over the network, and can speed up such queries by a factor of 5 to 10 times over cases where Condition Pushdown could be but is not used. For more information, see Section 8.2.1.5, “Engine Condition Pushdown Optimization”.
1)、id列數字越大越先執行,如果說數字一樣大,那麼就從上往下依次執行,id列為null的就表是這是一個結果集,不需要使用它來進行查詢。
MySQL執行計劃裡面的key_len
原創 2017-09-26 r13筆記地94天 楊建榮的學習筆記
以前看MySQL的執行計劃,感覺內容有些簡陋,平時分析主要就是看是否全表掃描,索引使用是否合理等。基本上也能分析出很多問題來,但是顯然有時候會有些疑惑,那就是對於複合索引,多列值的情況下,到底啟用了那些索引列,這個時候索引的使用情況就很值得琢磨琢磨了,我們得根據執行計劃裡面的key_len做一個重要的參考。
我們做一個簡單的測試來說明。
CREATE TABLE `department` (
`DepartmentID` int(11) DEFAULT NULL,
`DepartmentName` varchar(20) DEFAULT NULL,
KEY `IND_D` (`DepartmentID`),
KEY `IND_DN` (`DepartmentName`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
執行語句為:explain select count(*)from department\G
對於這個語句,key_len到底是多少呢?
mysql> explain select count(*)from department\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: department
type: index
possible_keys: NULL
key: IND_D
key_len: 5
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
在這個例子裡面,possible_keys,key,Extra你看了可能有些暈,我們看看key_len的值為5,這個值是怎麼算出來的呢,首先表有兩個欄位,第一個欄位的型別為數值,int的長度為4,因為欄位可為null,所以需要一個位元組來儲存,這樣下來就是4+1=5了。由此我們可以看到這個語句是啟用了索引ind_d.
那我們舉一反三,把語句修改一下,看看key_len的變化。
mysql> explain select departmentName from department b where departmentName='TEST'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: IND_DN
key: IND_DN
key_len: 43
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.09 sec)
從上面可以看到,key_len為43,這個值是怎麼算出來的呢,我們來掰扯一下,欄位2為字元型,長度20,因為是GBK字符集,所以需要乘以2,因為允許欄位為NULL,則需要一個位元組,對於變長的型別(在此就是VARCHAR),key_len還要加2位元組。這樣下來就是20*2+1+2=43
到了這裡僅僅是個開始,我們需要看看略微複雜的情況,就需要複合索引了。我們就換一個表test_keylen2
create table test_keylen2 (c1 int not null,c2 int not null,c3 int not null);
alter table test_keylen2 add key idx1(c1, c2, c3);
下面的語句就很實際了,
explain SELECT *from test_keylen2 WHERE c1=1 AND c2=1 ORDER BY c1\G
這個語句中,keylen到底是應該為4或者8還是12呢? 我們就需要驗證一下了。
mysql> explain SELECT *from test_keylen2 WHERE c1=1 AND c2=1 ORDER BY c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_keylen2
type: ref
possible_keys: idx1
key: idx1
key_len: 8
ref: const,const
rows: 1
Extra: Using index
1 row in set (0.07 sec)
顯然key_len只計算了where中涉及的列,因為是數值型別,所以就是4+4=8
那下面的這個語句呢。
explain SELECT *from test_keylen2 WHERE c1>=1 and c2=2 \G
我們新增一個範圍,看看這個該如何拆分。
mysql> explain SELECT *from test_keylen2 WHERE c1>=1 and c2=2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_keylen2
type: index
possible_keys: idx1
key: idx1
key_len: 12
ref: NULL
rows: 1
Extra: Using where; Using index
1 row in set (0.07 sec)
在這裡就不只是計算where中的列了,而是因為>1的條件直接選擇了3個列來計算。
對於date型別的處理,有一個很細小的差別。我們再換一個表,含有事件型別的欄位,
CREATE TABLE `tmp_users` (
`id` int(11) NOT NULL
AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`l_date` datetime NOT NULL,
`data` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ind_uidldate` (`uid`,`l_date`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
下面的語句key_len該如何計算呢。
explain select * from tmp_users where uid = 9527 and l_date >= '2012-12-10 10:13:17'\G
這一點出乎我的意料,按照datetime的印象是8個位元組,所以應該是8+4=12,但是這裡卻偏偏是9,這個數字怎麼計算的。
id: 1
select_type: SIMPLE
table: tmp_users
type: range
possible_keys: ind_uidldate
key: ind_uidldate
key_len: 9
ref: NULL
rows: 1
Extra: Using index condition
1 row in set (0.07 sec)
這裡就涉及到一個技術細節,是在MySQL 5.6中的datetime的儲存差別。在5.6.4以前是8個位元組,之後是5個位元組(在不帶小數位的情況下)。
所以按照這個演算法就是4+5=9
[root@LHRDB ~]# mysql -S/usr/local/mysql55/mysql5557/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.57 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (2.57 sec)
mysql>
mysql>
mysql>
mysql> use test;
Database changed
mysql>
mysql>
mysql> CREATE TABLE `tmp_users` (
-> `id` int(11) NOT NULL
-> AUTO_INCREMENT,
-> `uid` int(11) NOT NULL,
-> `l_date` datetime NOT NULL,
-> `data` varchar(32) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `ind_uidldate` (`uid`,`l_date`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.06 sec)
mysql> explain select * from tmp_users where uid = 9527 and l_date >= '2012-12-10 10:13:17'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_users
type: range
possible_keys: ind_uidldate
key: ind_uidldate
key_len: 12
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.31 sec)
About Me
.............................................................................................................................................
● 本文作者:小麥苗,部分內容整理自網路,若有侵權請聯絡小麥苗刪除
● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址:http://www.cnblogs.com/lhrbest
● 本文pdf版、個人簡介及小麥苗雲盤地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● DBA寶典今日頭條號地址:
.............................................................................................................................................
● QQ群號:230161599(滿)、618766405
● 微信群:可加我微信,我拉大家進群,非誠勿擾
● 聯絡我請加QQ好友(646634621),註明新增緣由
● 於 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解
● 版權所有,歡迎分享本文,轉載請保留出處
.............................................................................................................................................
● 小麥苗的微店:
● 小麥苗出版的資料庫類叢書:http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號(xiaomaimiaolhr)及QQ群(DBA寶典),學習最實用的資料庫技術。
小麥苗的微信公眾號 小麥苗的DBA寶典QQ群2 《DBA筆試面寶典》讀者群 小麥苗的微店
.............................................................................................................................................
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2125036/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL Explain執行計劃 - 詳解MySqlAI
- mysql explain 執行計劃詳解MySqlAI
- MySQL執行計劃EXPLAIN詳解MySqlAI
- MySQL explain執行計劃詳細解釋MySqlAI
- Explain執行計劃詳解AI
- MySQL EXPLAIN 命令詳解學習MySqlAI
- 檢視執行計劃方法總結之一:explain plan命令AI
- 使用EXPLAIN PLAN來檢視執行計劃AI
- 【Explain Plan】檢視SQL的執行計劃AISQL
- mysql執行計劃explainMySqlAI
- mysql explain 執行計劃MySqlAI
- MongoDb學習之Explain執行計劃MongoDBAI
- Hive底層原理:explain執行計劃詳解HiveAI
- 十六、Mysql之Explain執行計劃MySqlAI
- Oracle檢視執行計劃的命令Oracle
- MySQL調優篇 | EXPLAIN執行計劃解讀(4)MySqlAI
- mysql調優之——執行計劃explainMySqlAI
- (4) MySQL中EXPLAIN執行計劃分析MySqlAI
- MySQL explain命令詳解MySqlAI
- MySQL優化從執行計劃開始(explain超詳細)MySql優化AI
- MySQL 5.7 檢視理解SQL執行計劃MySql
- TOAD中檢視執行計劃(Explain Plan)AI
- mysql之 explain、optimizer_trace 執行計劃MySqlAI
- 執行計劃__獲取方法、檢視執行順序、統計資訊詳解
- 檢視執行計劃
- explain執行計劃分析AI
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- MySQL執行計劃explain輸出列結果解析MySqlAI
- MySQL執行計劃explain的key_len解析MySqlAI
- 配置oracle 解釋執行計劃--explain planOracleAI
- 檢視執行計劃(一)
- 檢視執行計劃(二)
- 檢視sql執行計劃SQL
- 檢視一個正在執行的sql的執行計劃(explain for connection processlist_id)SQLAI
- explain 查詢執行計劃AI
- SQLPLUS檢視oracle sql執行計劃命令SQLOracle
- MySQL 5.5 檢視分割槽表的執行計劃MySql
- 執行計劃詳解