在MySQL優化的環節上,我們首先需要知道的就是我們當前的這句SQL語句在實際的數據庫中究竟是怎么執行的,才能談要如何優化它。而在MySQL中,就給我們提供了模擬語句執行的一個非常好用的關鍵字:EXPLAIN。EXPLAIN可以用來查看SQL語句的執行效果,可以幫助選擇更好的索引和優化查詢語句,寫出更好的優化語句。因此今天我們就來講一講這個關鍵字的一些基礎的用法與應用。
EXPLAIN的使用方法非常簡單:
mysql> EXPLAIN SELECT * FROM user;
簡單來說,就是在原有的SQL語句前面加上EXPLAIN關鍵字,或者說是在EXPLAIN關鍵字后跟這你要檢查的SQL語句。
EXPLAIN語句的輸出結果才是我們想要的數據,也是我們分析的重點。
我們先來看看上面的語句所給到的對應的結果的形式:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
EXPLAIN語句給到我們的數據總共有10列,接下來我們看一下一些在性能優化上有比較重要作用的數據列所代表的意思。
這個是select查詢的序列號。
當我們的SQL語句是非select語句的時候(即delete,update...),這個字段的值就是對應的操作類型(delete,update...)。
mysql> EXPLAIN INSERT INTO user VAULES(2,'ahong','31');
此時的輸出select_type就是我們對應的INSERT:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | user | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
而當SQL語句時select語句的時候,他就是對應的一些詳細的select的類型,可以有如下幾種:
SIMPLE:簡單SELECT(不使用UNION或子查詢等) PRIMARY:最外面的SELECT UNION:UNION中的第二個或后面的SELECT語句 DEPENDENT UNION:UNION中的第二個或后面的SELECT語句,取決于外面的查詢 UNION RESULT:UNION的結果。 SUBQUERY:子查詢中的第一個SELECT DEPENDENT SUBQUERY:子查詢中的第一個SELECT,取決于外面的查詢 DERIVED:導出表的SELECT(FROM子句的子查詢)
下面就是一個最簡單的SIMPLE查詢的例子:
mysql> EXPLAIN SELECT * FROM user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
顯示這一步操作所訪問的數據是關于哪一張表的。
顯示表所使用的分區,如果要統計十年公司訂單的金額,可以把數據分為十個區,每一年代表一個區。這樣可以大大的提高查詢效率。
這是最重要的一列。顯示了連接使用了哪種類別,有無使用索引。是分析查詢性能的關鍵。
結果性能從優到差分別有以下的情況:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
而這幾種情況所代表的意義如下:
一般來說,得保證查詢至少達到range級別,最好能達到ref,否則就可能會出現性能問題。
顯示查詢語句有可能會使用到的索引列。取值可能為一個,多個或者null。
key列顯示的是該查詢語句實際使用的索引列。如為null,則表示沒有使用索引。
展示一下possible_key和key的實際效果:
下面是一個在age列上建立索引的數據表,我們進行以下的查詢
mysql> explain select * from user where age = 1;
會得到以下的結果:
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
顯示的是當前的查詢語句所使用的索引的長度。在不損失精確性的情況下,長度越短越好.
引用到的上一個表的列。
根據表的情況和查詢語句的情況,MySQL會估算出返回最終結果所必須檢查的行的數量。該列的值越大查詢效率越差。
一個百分比的值,和rows 列的值一起使用,可以估計出查詢執行計劃(QEP)中的前一個表的結果集,從而確定join操作的循環次數。小表驅動大表,減輕連接的次數。
關于MySQL如何解析查詢的額外信息,主要有以下幾種:
Extra中包含的值:
以上就是MySQL EXPLAIN語句的使用示例的詳細內容,更多關于MySQL EXPLAIN語句的資料請關注腳本之家其它相關文章!