MySQL查詢(xún)優(yōu)化的方式

本篇內容介紹了“查詢(xún)優(yōu)化的方式”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！

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

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

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

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

復制代碼 代碼如下:

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

需求:
查詢(xún) 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,也是最壞的情況。優(yōu)化是必須的。

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

復制代碼 代碼如下:

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

復制代碼 代碼如下:

 DROP INDEX x ON article;

然后建立新索引:

復制代碼 代碼如下:

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

接著(zhù)再運行查詢(xún):

復制代碼 代碼如下:

*************************** 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 也消失了,結果非常理想。
再來(lái)看一個(gè)多表查詢(xún)的例子。
首先定義 3個(gè)表 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<br /--&gt $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è)左連接查詢(xún):

復制代碼 代碼如下:

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)

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

復制代碼 代碼如下:

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

復制代碼 代碼如下:

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)

基本無(wú)變化。
       然后來(lái)看一個(gè)右連接查詢(xún):

復制代碼 代碼如下:

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)

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

復制代碼 代碼如下:

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)

基本無(wú)變化。

最后來(lái)看看 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)

此處有疑問(wèn)
刪除舊索引:

復制代碼 代碼如下:

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

我們再來(lái)看看三表查詢(xún)的例子

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

復制代碼 代碼如下:

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

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