MySQL之查詢效能最佳化四

標籤：

MySQL的萬能"嵌套迴圈"並不是對每種查詢都是最優的。不過還好，mysql查詢最佳化工具只對
少部分查詢不適用，而且我們往往可以通過改寫查詢讓mysql高效的完成工作。
在這我們先來看看mysql最佳化器有哪些局限性：

1.關聯子查詢

mysql的子查詢實現得非常糟糕。最糟糕得一類查詢是where條件中包含in()的子查詢語句。
例如，我們希望找到sakila資料庫中，演員Penlope Guiness參演的所有影片資訊。
很自然的，我們會按照下面的方式用子查詢實現：

   select * from sakila.film　　where film_id in (　　　　select film_id from sakila.film_actor where actor_id = 1　　)

 

你很容易認為mysql應該由內而外的去執行這個查詢，通過子查詢中的條件先找出所匹配的
film_id。所以你看你會認為這個查詢可能會是這樣：

-- 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,980SELECT * FROM sakila.filmWHERE film_idIN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

 

不幸的是，事實恰恰相反。MYSQL想通過外部的關聯條件用來快速的篩選子查詢，它可能認為
這會讓子查詢更效率。mysql會這樣重寫查詢：

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

 

這樣的話，子查詢將會依賴外部表格的資料，而不會被優先執行。
mysql將會全表掃描film表，然後迴圈執行子查詢。在外表很小的情況下，
不會有什麼問題，但在外表很大的情況下，效能將會非常差。幸運的是，
很容易用關聯查詢來重寫。

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

 

其他的好的最佳化方法是用group_concat手工產生in()的列表。有時甚至會比JOIN查詢
更快。總之，雖然in()子查詢在很多情況下工作不佳，但exist()或者其他等價的子查詢
有時也工作的不錯。

 

關聯子查詢效能並不是一直都很差的。

子查詢 VS 關聯查詢

 

--關聯子查詢

mysql> explain select film_id, language_id from sakila.film　　　　where not exsits (　　　　　　select * from sakila.film_actor　　　　　　where film_actor.film_id = film.film_id　　　　)

********************* 1. row ***********************************
id : 1
select_type: PRIMARY
table: film
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 951
Extra: Using where

********************* 2. row ***********************************
id : 2
select_type: Dependent subquery
table: film_actor
type: ref
possible_keys: idx_fx_film_id
key: idx_fx_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where;Using index

--關聯查詢
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

********************* 1. row ***********************************
id : 1
select_type: simple
table: film
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 951
Extra:

********************* 2. row ***********************************
id : 1
select_type: simple
table: film_actor
type: ref
possible_keys: idx_fx_film_id
key: idx_fx_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where;Using index;not exists;


可以看到，這裡的執行計畫幾乎一樣，下面是一些細微的差別：
1. 表 film_actor的訪問類型一個是Dependent subquery 另一是simple，這對底層儲存引擎介面來說，沒有任何不同；

2. 對 film表 第二個查詢沒有using where，但這不重要。using子句和where子句實際上是完全一樣的。

3. 第二個表film_actor的執行計畫的Extra 有 "Not exists" 這是我們先前提到的提前終止演算法，mysql通過not exits最佳化
來避免在表film_actor的索引中讀取任何額外的行。這完全等效於直接使用 not exist ，這個在執行計畫中也一樣，一旦匹配到一行
資料，就立刻停止掃描


測試結果為：
查詢 每秒查詢數結果(QRS)
NOT EXISTS 子查詢 360
LEFT OUTER JOIN 425
這裡顯示使用子查詢會略慢些。

另一個例子：
不過每個具體地案例會各有不同，有時候子查詢寫法也會快些。例如，當返回結果只有一個表的某些列的時候。
聽起來，這種情況對於關聯查詢效率也會很好。具體情況具體分析，例如下面的關聯，我們希望返回所有包含同一個演員參演的電影
因為電影會有很多演員參演，所以可能返回一些重複的記錄。

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_actor 　　　　where exists(select * from sakila.film_actor 　　　　where film.film_id = film_actor.film_id)

 

測試結果為：
查詢 每秒查詢數結果(QRS)
INNER JOIN 185
EXISTS 子查詢 325
這裡顯示使用子查詢會略快些。

通過上面這個詳細的案例，主要想說明兩點：
一是不需要聽取哪些關於子查詢的 "絕對真理"，(即別用使用子查詢)
二是應該用測試來驗證子查詢的執行疾患和回應時間的假設。

MySQL之查詢效能最佳化四