MySQL之查詢性能優化(八)

查詢性能優化

MySQL查詢優化器的局限性

MySQL的萬能"嵌套循環"并不是對每種查詢都是最優的。不過還好，MySQL查詢優化器只對少部分查詢不適用，而且我們往往可以通過改寫查詢讓MySQL高效地完成工作。還有一個好消息，MySQL5.6版本正式發布后，會消除很多MySQL原本的限制，讓更多的查詢能夠以盡可能高的效率完成。

關聯子查詢

MySQL的子查詢實現得非常糟糕。最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句。例如，我們希望找到Sakila數據庫中，演員Penelope Guinness(他的actor_id為1)參演過的所有影片信息。很自然的，我們會按照下面的方式用子查詢實現:

mysql> SELECT * FROM sakila.film WHERE film_id IN(SELECT film_id FROM sakila.film_actor WHERE actor_id =1);

因為MySQL對IN()列表的選項有專門的優化策略，一般會認為MySQL會先執行子查詢返回所有包含actor_id為1的film_id。一般來說，IN()列表查詢速度很快，所以我們會認為上面的查詢會這樣執行:

-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id=1;
-- Result :1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film WHERE film_id IN(1,23.....................,980);

很不幸，MySQL不是這樣做的。MySQL會講相關的外層表壓到子查詢中，它認為這樣可以更高效率地查找到數據行。也就是說，MySQL會將查詢改寫成下面的樣子:

SELECT * FROM sakila.film WHERE EXISTS (SELECT * FROM sakila.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id)

這時，子查詢需要根據film_id來關聯外部表film,因為需要film_id字段，所以MySQL認為無法先執行這個查詢。通過EXPLAIN可以看到子查詢是一個相關子查詢(DEPENDENT SUBQUERY)(可以使用EXPLAIN EXTENDED來查看這個查詢被改寫成了什么樣子)


mysql> EXPLAIN SELECT * FROM sakila.film WHERE EXISTS (SELECT * FROM sakila.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id)-> ;
+----+--------------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
| id | select_type        | table      | partitions | type   | possible_keys          | key     | key_len | ref                       | rows | filtered | Extra       |
+----+--------------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
|  1 | PRIMARY            | film       | NULL       | ALL    | NULL                   | NULL    | NULL    | NULL                      | 1000 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | film_actor | NULL       | eq_ref | PRIMARY,idx_fk_film_id | PRIMARY | 4       | const,sakila.film.film_id |    1 |   100.00 | Using index |
+----+--------------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
2 rows in set (0.10 sec)

根據EXPLAIN的輸出我們可以看到，MySQL先選擇對flim表進行全表掃描，然后根據返回的film_id逐個進行子查詢。如果是一個很小的表，這個查詢的糟糕的性能可能還不會引起注意，但是如果外層的表是一個非常大的表，那么這個查詢的性能會非常糟糕。當然我們很容易用下面的辦法來重寫這個查詢:

mysql>SELECT  film.* FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE actor_id = 1;

另一個優化的辦法是使用函數GROUP_CONCAT()在IN()中構造一個由逗號分割的列表，有時這比上面的使用關聯改寫更快。因為使用IN()加子查詢，性能經常會非常糟，所以通常建議使用EXISTS()等效的改寫查詢來獲取更好的效率。下面是另一種改寫IN()加子查詢的辦法:

mysql>SELECT * FROM sakila.film WHERE EXISTS (SELECT * FROM sakila.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id)

如何用好關聯子查詢

并不是所有關聯子查詢的性能都回很差。如果有人跟你說:“別用關聯子查詢”，那么不要理他。先測試，然后做出自己的判斷。很多時候關聯子查詢是一種非常合理、自然，甚至是性能最好的寫法，看看下面的例子:

mysql> EXPLAIN SELECT film_id,language_id FROM sakila.film-> WHERE NOT EXISTS(SELECT * FROM sakila.film_actor WHERE film_actor.film_id=film.film_id)\G
*************************** 1. row ***************************id: 1select_type: PRIMARYtable: filmpartitions: NULLtype: index
possible_keys: NULLkey: idx_fk_language_idkey_len: 1ref: NULLrows: 1000filtered: 100.00Extra: Using where; Using index
*************************** 2. row ***************************id: 2select_type: DEPENDENT SUBQUERYtable: film_actorpartitions: NULLtype: ref
possible_keys: idx_fk_film_idkey: idx_fk_film_idkey_len: 2ref: sakila.film.film_idrows: 5filtered: 100.00Extra: Using index
2 rows in set, 2 warnings (0.00 sec)

一般回建議使用左外連接(LEFT OUTER JOIN)重寫該查詢，以代替子查詢。理論上，改寫后MySQL的執行計劃完全不會改變。我們來看這個例子

mysql> EXPLAIN SELECT film.film_id,film.language_id-> FROM sakila.film-> LEFT OUTER JOIN sakila.film_actor USING(film_id)-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: filmpartitions: NULLtype: index
possible_keys: NULLkey: idx_fk_language_idkey_len: 1ref: NULLrows: 1000filtered: 100.00Extra: Using index
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: film_actorpartitions: NULLtype: ref
possible_keys: idx_fk_film_idkey: idx_fk_film_idkey_len: 2ref: sakila.film.film_idrows: 5filtered: 100.00Extra: Using where; Not exists; Using index
2 rows in set, 1 warning (0.00 sec)

可以看到，這里的執行計劃基本上是一樣，下面是一些微小的區別:

1.表film_actor的訪問類型是一個DEPENDENT SUBQUERY,而另一個是SIMPLE.這個不同是由于語句的寫法不同導致的，一個是普通查詢，一個是子查詢。這對底層存儲引擎接口來說，沒有任何不同
2.對film表，第二個查詢的Extra中沒有"Using where"，但這并不重要，第二個查詢的USING子句和第一個查詢的WHERE子句實際上是完全一樣的。
3.在第二個表film_actor的執行計劃的Extra列有"Not exists"。這是前面提到的提前終止算法(early-termination algorithm)，MySQL通過使用"Not exists"優化來避免在表film_actor的索引中讀取任何額外的行。這完全等效于直接編寫NOT EXISTS子查詢，這個執行計劃中也是一樣，一旦匹配到一行數據，就立刻停止掃描

所以，從理論上來講，MySQL將使用完全相同的執行計劃來完成這個查詢。現實世界中，建議通過一些測試來判斷使用哪種寫法速度會更快。針對上面的案例，測試結果也是不同的，如表所示在這里插入圖片描述
.測試結果顯示，使用子查詢的寫法要略微慢些！不過每個具體的案例會各有不同，有時候子查詢寫法也會快些。例如，當返回結果中只有一個表中的某些列的時候。聽起來，這種情況對于關聯查詢效率也會更好。具體情況具體分析，例如下面的關聯，我們希望返回所有演員參演的電影，因為一個電影會有很多演員參演，所以可能會返回一些重復的記錄：

mysql> SELECT film.film_id FROM sakila.film INNER JOIN sakila.film_actor USING(film_id);

我們需要使用DISTINCT和GROUP BY來移除重復的記錄:

mysql> SELECT DISTINCT film.film_id FROM sakila.film INNER JOIN sakila.film_actor USING(film_id);

但是，回頭看看這個查詢，到底這個查詢返回的結果集意義是什么？至少這樣的寫法回訪SQL的意義很不明顯。如果使用EXISTS則很容易表達"有演員參演"的邏輯，而且不需要使用DISTINCT和GROUP BY，也不會產生重復的結果集，我們知道一旦使用了DISTINCT和GROUP BY，那么在查詢的執行過程中，通常需要產生臨時中間表。下面我們用子查詢的寫法替換上面的關聯:

mysql> SELECT film_id FROM sakila.film WHERE EXISTS(SELECT * FROM sakila.film_actor WHERE film.film_id = film_actor.film_id);

再一次，我們需要通過測試來比對這兩種寫法，哪個更快一些，測試結果如表所示.在這個案例中，我們看到子查詢速度要比關聯查詢更快些。通過上面這個案例，主要想說明兩點:一時不需要聽取那些關于子查詢的"絕對真理"，二十應該用測試來驗證對子查詢的執行計劃和相應時間的假設。我們應該通過測試來驗證猜想在這里插入圖片描述