[MySQL note.] 聯合查詢筆記

其實我不知道聯合查詢大家常不常用。不過當時我年輕不懂事的時候好像很愛用的樣子(喂)。使用這種交集式的聯合查詢,其實有一個稍微要注意的地方,就是,針對你在撈出來的資料表中的資料,重複性最好不要太高,這樣使用 UNION ALL 的效率就會非常的好。

詳細的效率可以參考這篇文章(雖然舊了點)。

http://www.mysqlperformanceblog.com/2007/10/05/union-vs-union-all-performance/

什麼時候會用上?多資料表聯集的時候。但是真的一定要用嗎?上面的文章中,也有指出使用原始查詢條件也可以達到同樣的結果。那,使用聯合查詢有什麼好處(上述文章也有提及)?

if you do not need to trick with order by and limit using index merge is faster than UNION as it indeed should be.

當你不需要使用 ORDER BY 或是使用 LIMIT 查詢時,使用一般的查詢語法是比較妥當的。為什麼?因為使用非索引的 ORDER BY 或是 LIMIT 其實效能也沒那麼好,而在 UNION 中使用 ORDER_BY 則可能更糟(可以使用 EXPLAIN 來查詢 SQL 語法若使用 ORDER BY 中是否有使用索引鍵)。

關於 ORDER BY 與 LIMIT 可以參考這一篇:

http://www.mysqlperformanceblog.com/2007/09/18/possible-optimization-for-sort_merge-and-union-order-by-limit/

多表聯集時,欄位數目必須一致!

由於 UNION 會建立一個暫存的表來儲存結果,所以對於直接操作多表的速度上會快上一些。比起複雜的子查詢或是 JOIN 查詢效率要來的好。假設我們要從 T1 跟 T2 取出共同欄位 user_id 為 1, 2, 3, 4 的時候,也許我們可以這樣查詢:

SELECT user_id FROM T1 WHERE user_id = 1 OR user_id = 2 OR user_id = 3 OR user_id = 4;
SELECT user_id FROM T2 WHERE user_id = 1 OR user_id = 2 OR user_id = 3 OR user_id = 4;

或者是:

( SELECT user_id FROM T1 WHERE user_id = 1 OR user_id = 2 OR user_id = 3 OR user_id = 4 )
UNION ALL( SELECT user_id FROM T2 WHERE user_id = 1 OR user_id = 2 OR user_id = 3 OR user_id = 4);

差別在?沒差! 但是倘若使用 UNION(或是 UNION DISTINCT),那麼他 只會取出不重複值。如果使用一般查詢,必須得再一次將不重複值給過濾掉,但是使用 UNION DISTINCT 就可以免掉這一個步驟。

但是,畢竟這不一定是高效能的作法,所以該什麼時候用請自行取決使用時機。我們對於重複性不高,需要特殊條件將多表作相關查詢時,使用 UNION 會比多次查詢要來得方便。當然,我們可以作弊來區別我們取出來的數值是哪一個表來的:

( SELECT `user_id`, 'table_1' AS `table` FROM T1 WHERE `user_id` = 1 OR `user_id`=2 )UNION ALL( SELECT `user_id`, 'table_2' AS `table` FROM T2 WHERE `user_id` = 1 OR `user_id`=2 );

這樣我們就有一個輸出欄位叫做 table,然後寫入你要給那個表的名稱。這樣,我們就能在多表數值輸出中,對於不同的表所輸出的資料,來作更多的事情。那,為什麼不用單次查詢,每次單次處理?當然,使用 UNION(或是 UNION DISTINCT),就能取出聯集(濾過不重複)。

另外一篇關於 UNION 效能討論:

http://www.mysqlperformanceblog.com/2006/08/14/mysql-followup-on-union-for-query-optimization-query-profiling/

為什麼建議在重複性不高的表來作 UNION,因為 UNION 的效率比起 UNION ALL 差得多。所以,倘若我們要比較快速取出聯集資料,若資料重複性很高,就不一定得這樣作,把效能花在 PHP 這一端,應該要遠比花在 MySQL 上面要來得好一點。

如果是重複性非常低的資料(例如 UUID 資料表等等),那麼使用 UNION ALL 來提取資料或許會比複雜子查詢會來得快一些。當然,如果想加速 UNION 查詢,把 MySQL 的表格丟到記憶體裡面去也是可以的。只不過小心別讓記憶體爆炸就好了。

MySQL Memory(HEAP) Storage Engine:
http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

當然 Query Cache 也是可以的:
http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

好 Query,不 UNION 嗎(超大誤!

Hina Chen
偏執與強迫症的患者,算不上是無可救藥,只是我已經遇上我的良醫了。
Taipei