亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

MySQL查詢優(yōu)化之explain的深入解析

 更新時(shí)間：2013年06月13日 09:35:53   作者：  
本篇文章是對(duì)MySQL查詢優(yōu)化中的explain進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下

在分析查詢性能時(shí)，考慮EXPLAIN關(guān)鍵字同樣很管用。EXPLAIN關(guān)鍵字一般放在SELECT查詢語(yǔ)句的前面，用于描述MySQL如何執(zhí)行查詢操作、以及MySQL成功返回結(jié)果集需要執(zhí)行的行數(shù)。explain 可以幫助我們分析 select 語(yǔ)句,讓我們知道查詢效率低下的原因,從而改進(jìn)我們查詢,讓查詢優(yōu)化器能夠更好的工作。

一、MySQL 查詢優(yōu)化器是如何工作的
MySQL 查詢優(yōu)化器有幾個(gè)目標(biāo),但是其中最主要的目標(biāo)是盡可能地使用索引,并且使用最嚴(yán)格的索引來(lái)消除盡可能多的數(shù)據(jù)行。最終目標(biāo)是提交 SELECT 語(yǔ)句查找數(shù)據(jù)行,而不是排除數(shù)據(jù)行。優(yōu)化器試圖排除數(shù)據(jù)行的原因在于它排除數(shù)據(jù)行的速度越快,那么找到與條件匹配的數(shù)據(jù)行也就越快。如果能夠首先進(jìn)行最嚴(yán)格的測(cè)試,查詢就可以執(zhí)行地更快。
EXPLAIN 的每個(gè)輸出行提供一個(gè)表的相關(guān)信息,并且每個(gè)行包括下面的列: 

項(xiàng) 說(shuō)明
id MySQL Query Optimizer 選定的執(zhí)行計(jì)劃中查詢的序列號(hào)。表示查詢中執(zhí)行 select 子句或操作表的順序,id 值越大優(yōu)先級(jí)越高,越先被執(zhí)行。id 相同,執(zhí)行順序由上至下。


select_type 查詢類(lèi)型 說(shuō)明
SIMPLE 簡(jiǎn)單的 select 查詢,不使用 union 及子查詢
PRIMARY 最外層的 select 查詢
UNION UNION 中的第二個(gè)或隨后的 select 查詢,不 依賴(lài)于外部查詢的結(jié)果集
DEPENDENT UNION UNION 中的第二個(gè)或隨后的 select 查詢,依 賴(lài)于外部查詢的結(jié)果集
SUBQUERY 子查詢中的第一個(gè) select 查詢,不依賴(lài)于外 部查詢的結(jié)果集
DEPENDENT SUBQUERY 子查詢中的第一個(gè) select 查詢,依賴(lài)于外部 查詢的結(jié)果集
DERIVED 用于 from 子句里有子查詢的情況。 MySQL 會(huì) 遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時(shí)表里。
UNCACHEABLE SUBQUERY 結(jié)果集不能被緩存的子查詢,必須重新為外 層查詢的每一行進(jìn)行評(píng)估。
UNCACHEABLE UNION UNION 中的第二個(gè)或隨后的 select 查詢,屬 于不可緩存的子查詢


項(xiàng) 說(shuō)明
table 輸出行所引用的表


type 重要的項(xiàng),顯示連接使用的類(lèi)型,按最 優(yōu)到最差的類(lèi)型排序 說(shuō)明
system 表僅有一行(=系統(tǒng)表)。這是 const 連接類(lèi)型的一個(gè)特例。
const const 用于用常數(shù)值比較 PRIMARY KEY 時(shí)。當(dāng) 查詢的表僅有一行時(shí),使用 System。
eq_ref const 用于用常數(shù)值比較 PRIMARY KEY 時(shí)。當(dāng) 查詢的表僅有一行時(shí),使用 System。
ref 連接不能基于關(guān)鍵字選擇單個(gè)行,可能查找 到多個(gè)符合條件的行。 叫做 ref 是因?yàn)樗饕?跟某個(gè)參考值相比較。這個(gè)參考值或者是一 個(gè)常數(shù),或者是來(lái)自一個(gè)表里的多表查詢的 結(jié)果值。
ref_or_null 如同 ref, 但是 MySQL 必須在初次查找的結(jié)果 里找出 null 條目,然后進(jìn)行二次查找。
index_merge 說(shuō)明索引合并優(yōu)化被使用了。
unique_subquery 在某些 IN 查詢中使用此種類(lèi)型,而不是常規(guī)的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery 在 某 些 IN 查 詢 中 使 用 此 種 類(lèi) 型 , 與 unique_subquery 類(lèi)似,但是查詢的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range 只檢索給定范圍的行,使用一個(gè)索引來(lái)選擇 行。key 列顯示使用了哪個(gè)索引。當(dāng)使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比較關(guān)鍵字列時(shí),可 以使用 range。
index 全表掃描,只是掃描表的時(shí)候按照索引次序 進(jìn)行而不是行。主要優(yōu)點(diǎn)就是避免了排序, 但是開(kāi)銷(xiāo)仍然非常大。
all 最壞的情況,從頭到尾全表掃描。



項(xiàng) 說(shuō)明
possible_keys 指出 MySQL 能在該表中使用哪些索引有助于 查詢。如果為空,說(shuō)明沒(méi)有可用的索引。


項(xiàng) 說(shuō)明
key MySQL 實(shí)際從 possible_key 選擇使用的索引。 如果為 NULL,則沒(méi)有使用索引。很少的情況 下,MYSQL 會(huì)選擇優(yōu)化不足的索引。這種情 況下,可以在 SELECT 語(yǔ)句中使用 USE INDEX (indexname)來(lái)強(qiáng)制使用一個(gè)索引或者用 IGNORE INDEX(indexname)來(lái)強(qiáng)制 MYSQL 忽略索引


項(xiàng) 說(shuō)明
key_len 使用的索引的長(zhǎng)度。在不損失精確性的情況 下,長(zhǎng)度越短越好。


項(xiàng) 說(shuō)明
ref 顯示索引的哪一列被使用了


項(xiàng) 說(shuō)明
rows MYSQL 認(rèn)為必須檢查的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)


項(xiàng) 說(shuō)明
rows MYSQL 認(rèn)為必須檢查的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)


extra 中出現(xiàn)以下 2 項(xiàng)意味著 MYSQL 根本不能使用索引,效率會(huì)受到重大影響。應(yīng)盡可能對(duì)此進(jìn)行優(yōu)化。

extra 項(xiàng) 說(shuō)明
Using filesort 表示 MySQL 會(huì)對(duì)結(jié)果使用一個(gè)外部索引排序,而不是從表里按索引次序讀到相關(guān)內(nèi)容?？赡茉趦?nèi)存或者磁盤(pán)上進(jìn)行排序。MySQL 中無(wú)法利用索引完成的排序操作稱(chēng)為“文件排序”
Using temporary 表示 MySQL 在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)于排序 order by 和分組查詢 group by。

下面來(lái)舉一個(gè)例子來(lái)說(shuō)明下 explain 的用法。
先來(lái)一張表:

復(fù)制代碼 代碼如下:

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`)
);

再插幾條數(shù)據(jù):
復(fù)制代碼 代碼如下:

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。
先查查試試看:
復(fù)制代碼 代碼如下:

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

看看部分輸出結(jié)果:
復(fù)制代碼 代碼如下:

*************************** 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 里還出現(xiàn)了 Using filesort,也是最壞的情況。優(yōu)化是必須的。

嗯,那么最簡(jiǎn)單的解決方案就是加索引了。好,我們來(lái)試一試。查詢的條件里即 where 之后共使用了 category_id,comments,views 三個(gè)字段。那么來(lái)一個(gè)聯(lián)合索引是最簡(jiǎn)單的了。

復(fù)制代碼 代碼如下:

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

結(jié)果有了一定好轉(zhuǎn),但仍然很糟糕:
復(fù)制代碼 代碼如下:

*************************** 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 仍是無(wú)法接受的。但是我們已經(jīng)建立了索引,為啥沒(méi)用呢?這是因?yàn)榘凑?BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 則再排序 comments,如果遇到相同的 comments 則再排序 views。當(dāng) comments 字段在聯(lián)合索引里處于中間位置時(shí),因comments > 1 條件是一個(gè)范圍值(所謂 range),MySQL 無(wú)法利用索引再對(duì)后面的 views 部分進(jìn)行檢索,即 range 類(lèi)型查詢字段后面的索引無(wú)效。
那么我們需要拋棄 comments,刪除舊索引:
復(fù)制代碼 代碼如下:

 DROP INDEX x ON article;

然后建立新索引:
復(fù)制代碼 代碼如下:

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

接著再運(yùn)行查詢:
復(fù)制代碼 代碼如下:

*************************** 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 變?yōu)榱?ref,Extra 中的 Using filesort 也消失了,結(jié)果非常理想。
再來(lái)看一個(gè)多表查詢的例子。
首先定義 3個(gè)表 class 和 room。
復(fù)制代碼 代碼如下:

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;

然后再分別插入大量數(shù)據(jù)。插入數(shù)據(jù)的php腳本：
復(fù)制代碼 代碼如下:

<?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");
?>

然后來(lái)看一個(gè)左連接查詢:
復(fù)制代碼 代碼如下:

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

分析結(jié)果是:
復(fù)制代碼 代碼如下:

*************************** 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)

顯然第二個(gè) ALL 是需要我們進(jìn)行優(yōu)化的。
建立個(gè)索引試試看:
復(fù)制代碼 代碼如下:

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

復(fù)制代碼 代碼如下:

*************************** 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 變?yōu)榱?ref,rows 也變成了 1741*18,優(yōu)化比較明顯。這是由左連接特性決定的。LEFT JOIN 條件用于確定如何從右表搜索行,左邊一定都有,所以右邊是我們的關(guān)鍵點(diǎn),一定需要建立索引。
刪除舊索引:
復(fù)制代碼 代碼如下:

DROP INDEX y ON book;

建立新索引。
復(fù)制代碼 代碼如下:

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

結(jié)果
復(fù)制代碼 代碼如下:

*************************** 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)

基本無(wú)變化。
       然后來(lái)看一個(gè)右連接查詢:
復(fù)制代碼 代碼如下:

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

分析結(jié)果是:
復(fù)制代碼 代碼如下:

*************************** 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)

優(yōu)化較明顯。這是因?yàn)?RIGHT JOIN 條件用于確定如何從左表搜索行,右邊一定都有,所以左邊是我們的關(guān)鍵點(diǎn),一定需要建立索引。
刪除舊索引:
復(fù)制代碼 代碼如下:

DROP INDEX x ON class;

建立新索引。
復(fù)制代碼 代碼如下:

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

結(jié)果
復(fù)制代碼 代碼如下:

*************************** 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)

基本無(wú)變化。

最后來(lái)看看 inner join 的情況:

復(fù)制代碼 代碼如下:

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

結(jié)果:
復(fù)制代碼 代碼如下:

*************************** 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)

刪除舊索引:
復(fù)制代碼 代碼如下:

DROP INDEX y ON book;

結(jié)果
復(fù)制代碼 代碼如下:

*************************** 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)

建立新索引。
復(fù)制代碼 代碼如下:

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

結(jié)果
復(fù)制代碼 代碼如下:

*************************** 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 差不多,都需要優(yōu)化右表。而 right join 需要優(yōu)化左表。

我們?cè)賮?lái)看看三表查詢的例子

添加一個(gè)新索引：

復(fù)制代碼 代碼如下:

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

復(fù)制代碼 代碼如下:

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

復(fù)制代碼 代碼如下:

*************************** 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 優(yōu)化很好,效果不錯(cuò)。

MySql 中的 explain 語(yǔ)法可以幫助我們改寫(xiě)查詢,優(yōu)化表的結(jié)構(gòu)和索引的設(shè)置,從而最大地提高查詢效率。當(dāng)然,在大規(guī)模數(shù)據(jù)量時(shí),索引的建立和維護(hù)的代價(jià)也是很高的,往往需要較長(zhǎng)的時(shí)間和較大的空間,如果在不同的列組合上建立索引,空間的開(kāi)銷(xiāo)會(huì)更大。因此索引最好設(shè)置在需要經(jīng)常查詢的字段中。

相關(guān)文章

最新評(píng)論