程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL查詢優化之explain的深刻解析

MySQL查詢優化之explain的深刻解析

編輯：MySQL綜合教程

MySQL查詢優化之explain的深刻解析。本站提示廣大學習愛好者：（MySQL查詢優化之explain的深刻解析）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL查詢優化之explain的深刻解析正文

在剖析查詢機能時，斟酌EXPLAIN症結字異樣很管用。EXPLAIN症結字普通放在SELECT查詢語句的後面，用於描寫MySQL若何履行查詢操作、和MySQL勝利前往成果集須要履行的行數。explain 可以贊助我們剖析 select 語句,讓我們曉得查詢效力低下的緣由,從而改良我們查詢,讓查詢優化器可以或許更好的任務。

1、MySQL 查詢優化器是若何任務的
MySQL 查詢優化器有幾個目的,然則個中最重要的目的是盡量地應用索引,而且應用最嚴厲的索引來清除盡量多的數據行。終究目的是提交 SELECT 語句查找數據行,而不是消除數據行。優化器試圖消除數據行的緣由在於它消除數據行的速度越快,那末找到與前提婚配的數據行也就越快。假如可以或許起首停止最嚴厲的測試,查詢便可以履行地更快。
EXPLAIN 的每一個輸入行供給一個表的相干信息,而且每一個行包含上面的列:

項解釋 id MySQL Query Optimizer 選定的履行籌劃中查詢的序列號。表現查詢中履行 select 子句或操作表的次序,id 值越年夜優先級越高,越先被履行。id 雷同,履行次序由上至下。

select_type 查詢類型解釋 SIMPLE 簡略的 select 查詢,不應用 union 及子查詢 PRIMARY 最外層的 select 查詢 UNION UNION 中的第二個或隨後的 select 查詢,不依附於內部查詢的成果集 DEPENDENT UNION UNION 中的第二個或隨後的 select 查詢,依賴於內部查詢的成果集 SUBQUERY 子查詢中的第一個 select 查詢,不依附於外部查詢的成果集 DEPENDENT SUBQUERY 子查詢中的第一個 select 查詢,依附於內部查詢的成果集 DERIVED 用於 from 子句裡有子查詢的情形。 MySQL 會遞歸履行這些子查詢, 把成果放在暫時內外。 UNCACHEABLE SUBQUERY 成果集不克不及被緩存的子查詢,必需從新為外層查詢的每行停止評價。 UNCACHEABLE UNION UNION 中的第二個或隨後的 select 查詢,屬於弗成緩存的子查詢

項解釋 table 輸入行所援用的表

type 主要的項,顯示銜接應用的類型,按最優到最差的類型排序解釋 system 表唯一一行(=體系表)。這是 const 銜接類型的一個特例。 const const 用於用常數值比擬 PRIMARY KEY 時。當查詢的表唯一一行時,應用 System。 eq_ref const 用於用常數值比擬 PRIMARY KEY 時。當查詢的表唯一一行時,應用 System。 ref 銜接不克不及基於症結字選擇單個行,能夠查找到多個相符前提的行。叫做 ref 是由於索引要跟某個參考值比擬較。這個參考值或許是一個常數,或許是來自一個內外的多表查詢的成果值。 ref_or_null 好像 ref, 然則 MySQL 必需在首次查找的成果裡找出 null 條目,然落後行二次查找。 index_merge 解釋索引歸並優化被應用了。 unique_subquery 在某些 IN 查詢中應用此品種型,而不是慣例的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) index_subquery 在某些 IN 查詢中使用此種類型 , 與 unique_subquery 相似,然則查詢的長短獨一性索引: value IN (SELECT key_column FROM single_table WHERE some_expr) range 只檢索給定規模的行,應用一個索引來選擇行。key 列顯示應用了哪一個索引。當應用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或許 IN 操作符,用常量比擬症結字列時,可以應用 range。 index 全表掃描,只是掃描表的時刻依照索引順序停止而不是行。重要長處就是防止了排序, 然則開支依然異常年夜。 all 最壞的情形,從頭至尾全表掃描。

項解釋 possible_keys 指出 MySQL 能在該表中應用哪些索引有助於查詢。假如為空,解釋沒有可用的索引。

項解釋 key MySQL 現實從 possible_key 選擇應用的索引。假如為 NULL,則沒有應用索引。很少的情形下,MYSQL 會選擇優化缺乏的索引。這類情況下,可以在 SELECT 語句中應用 USE INDEX (indexname)來強迫應用一個索引或許用 IGNORE INDEX(indexname)來強迫 MYSQL 疏忽索引

項解釋 key_len 應用的索引的長度。在不喪失准確性的情形下,長度越短越好。

項解釋 ref 顯示索引的哪一列被應用了

項解釋 rows MYSQL 以為必需檢討的用來前往要求數據的行數

extra 中湧現以下 2 項意味著 MYSQL 基本不克不及應用索引,效力會遭到嚴重影響。應盡量對此停止優化。

extra 項解釋 Using filesort 表現 MySQL 會對成果應用一個內部索引排序,而不是從內外按索引順序讀到相干內容。能夠在內存或許磁盤長進行排序。MySQL 中沒法應用索引完成的排序操作稱為“文件排序” Using temporary 表現 MySQL 在對查詢成果排序時應用暫時表。罕見於排序 order by 和分組查詢 group by。

上面來舉一個例子來講明下 explain 的用法。
先來一張表:

CREATE TABLE IF NOT EXISTS `article` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);

再插幾條數據:

INSERT INTO `article`
(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

需求:
查詢 category_id 為 1 且 comments 年夜於 1 的情形下,views 最多的 article_id。
先查查嘗嘗看:

EXPLAIN
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1\G

看看部門輸入成果:

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: article
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

很明顯,type 是 ALL,即最壞的情形。Extra 裡還湧現了 Using filesort,也是最壞的情形。優化是必需的。

嗯,那末最簡略的處理計劃就是加索引了。好,我們來試一試。查詢的前提裡即 where 以後共應用了 category_id,comments,views 三個字段。那末來一個結合索引是最簡略的了。

ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` );

成果有了必定惡化,但依然很蹩腳:

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: article
         type: range
possible_keys: x
          key: x
      key_len: 8
          ref: NULL
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

type 釀成了 range,這是可以忍耐的。然則 extra 裡應用 Using filesort 還是沒法接收的。然則我們曾經樹立了索引,為啥沒用呢?這是由於依照 BTree 索引的任務道理,先排序 category_id,假如碰到雷同的 category_id 則再排序 comments,假如碰到雷同的 comments 則再排序 views。當 comments 字段在結合索引裡處於中央地位時,因comments > 1 前提是一個規模值(所謂 range),MySQL 沒法應用索引再對前面的 views 部門停止檢索,即 range 類型查詢字段前面的索引有效。
那末我們須要擯棄 comments,刪除舊索引:

DROP INDEX x ON article;

然後樹立新索引:

ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;

接著再運轉查詢:

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: article
         type: ref
possible_keys: y
          key: y
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

可以看到,type 變成了 ref,Extra 中的 Using filesort 也消逝了,成果異常幻想。
再來看一個多表查詢的例子。
起首界說 3個表 class 和 room。

CREATE TABLE IF NOT EXISTS `class` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`bookid`)
);
CREATE TABLE IF NOT EXISTS `phone` (
`phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card` int(10) unsigned NOT NULL,
PRIMARY KEY (`phoneid`)
) engine = innodb;

然後再分離拔出年夜量數據。拔出數據的php劇本：

<?php
$link = mysql_connect("localhost","root","870516");
mysql_select_db("test",$link);
for($i=0;$i<10000;$i++)
{
    $j   = rand(1,20);
    $sql = " insert into class(card) values({$j})";
    mysql_query($sql);
}
for($i=0;$i<10000;$i++)
{
    $j   = rand(1,20);
    $sql = " insert into book(card) values({$j})";
    mysql_query($sql);
}
for($i=0;$i<10000;$i++)
{
    $j   = rand(1,20);
    $sql = " insert into phone(card) values({$j})";
    mysql_query($sql);
}
mysql_query("COMMIT");
?>

然後來看一個左銜接查詢:

explain select * from class left join book on class.card = book.card\G

剖析成果是:

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: class
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: book
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
2 rows in set (0.00 sec)

明顯第二個 ALL 是須要我們停止優化的。
樹立個索引嘗嘗看:

ALTER TABLE `book` ADD INDEX y ( `card`);

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: class
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: book
         type: ref
possible_keys: y
          key: y
      key_len: 4
          ref: test.class.card
         rows: 1000
        Extra:
2 rows in set (0.00 sec)

可以看到第二行的 type 變成了 ref,rows 也釀成了 1741*18,優化比擬顯著。這是由左銜接特征決議的。LEFT JOIN 前提用於肯定若何從右表搜刮行,右邊必定都有,所以左邊是我們的症結點,必定須要樹立索引。
刪除舊索引:

DROP INDEX y ON book;

樹立新索引。

ALTER TABLE `class` ADD INDEX x ( `card`);

成果

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: class
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: book
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
2 rows in set (0.00 sec)

根本無變更。
       然後來看一個右銜接查詢:

explain select * from class right join book on class.card = book.card;

剖析成果是:

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: book
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: class
         type: ref
possible_keys: x
          key: x
      key_len: 4
          ref: test.book.card
         rows: 1000
        Extra:
2 rows in set (0.00 sec)

優化較顯著。這是由於 RIGHT JOIN 前提用於肯定若何從左表搜刮行,左邊必定都有,所以右邊是我們的症結點,必定須要樹立索引。
刪除舊索引:

DROP INDEX x ON class;

樹立新索引。

ALTER TABLE `book` ADD INDEX y ( `card`);

成果

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: class
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: book
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
2 rows in set (0.00 sec)

根本無變更。

最初來看看 inner join 的情形:

explain select * from class inner join book on class.card = book.card;

成果:

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: book
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: class
         type: ref
possible_keys: x
          key: x
      key_len: 4
          ref: test.book.card
         rows: 1000
        Extra:
2 rows in set (0.00 sec)

刪除舊索引:

DROP INDEX y ON book;

成果

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: class
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: book
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
2 rows in set (0.00 sec)

樹立新索引。

ALTER TABLE `class` ADD INDEX x ( `card`);

成果

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: class
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: book
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
2 rows in set (0.00 sec)

綜上所述,inner join 和 left join 差不多,都須要優化右表。而 right join 須要優化左表。

我們再來看看三表查詢的例子

添加一個新索引：

ALTER TABLE `phone` ADD INDEX z ( `card`);
ALTER TABLE `book` ADD INDEX y ( `card`);

explain select * from class left join book on class.card=book.card left join phone on book.card = phone.card;

*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: class
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20000
        Extra:
*************************** 2. row ***************************
           id: 1
select_type: SIMPLE
        table: book
         type: ref
possible_keys: y
          key: y
      key_len: 4
          ref: test.class.card
         rows: 1000
        Extra:
*************************** 3. row ***************************
           id: 1
select_type: SIMPLE
        table: phone
         type: ref
possible_keys: z
          key: z
      key_len: 4
          ref: test.book.card
         rows: 260
        Extra: Using index
3 rows in set (0.00 sec)

後 2 行的 type 都是 ref 且總 rows 優化很好,後果不錯。

MySql 中的 explain 語法可以贊助我們改寫查詢,優化表的構造和索引的設置,從而最年夜地進步查詢效力。固然,在年夜范圍數據量時,索引的樹立和保護的價值也是很高的,常常須要較長的時光和較年夜的空間,假如在分歧的列組合上樹立索引,空間的開支會更年夜。是以索引最好設置在須要常常查詢的字段中。