問(wèn)題描述

如下SQL語(yǔ)句在MySQL中執(zhí)行需要2秒左右的時(shí)間，如果使用UNION ALL進(jìn)行不去重合并只需要0.4秒，UNION ALL配合DISTINCT來(lái)去重速度又變成2秒了，請(qǐng)問(wèn)如何進(jìn)行優(yōu)化？

SELECT a. KEY, a. DATA, a.date_addedFROM (( SELECTCONCAT(’customer_’, ca. KEY) AS `key`,ca. DATA,ca.date_added FROM`cf_customer_activity` ca)UNION (SELECT CONCAT(’affiliate_’, aa. KEY) AS `key`, aa. DATA, aa.date_addedFROM `cf_affiliate_activity` aa ) ) aORDER BY a.date_added DESCLIMIT 0, 5;

問(wèn)題解答

看你寫(xiě)的語(yǔ)句key前綴不一樣永遠(yuǎn)不會(huì)有重復(fù)啊，直接union all

當(dāng)然先是使用QEP

mysql> explain select * from wp_options limit 1G;*************************** 1. row *************************** id: 1 select_type: SIMPLEtable: wp_options partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 136 filtered: 100.00Extra: NULL1 row in set, 1 warning (0.01 sec)mysql> show session status like ’Handler_read%’;+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 3 || Handler_read_key | 3 || Handler_read_last | 0 || Handler_read_next | 0 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 209 |+-----------------------+-------+7 rows in set (0.01 sec)

至于每個(gè)字段的具體含義，自己查資料。

@prolifes 是對(duì)的。也許題主沒(méi)表述清楚。

我補(bǔ)充另外一個(gè)思路，因?yàn)槟阕詈笾恍枰?條數(shù)據(jù)，從ca中取5條，再?gòu)腶a中取5條，總共10條排序，這個(gè)會(huì)很快。

SELECT a. KEY, a. DATA, a.date_addedFROM (( SELECTCONCAT(’customer_’, ca. KEY) AS `key`,ca. DATA,ca.date_added FROM`cf_customer_activity` ca ORDER BY a.date_added DESC /* 如果date_added有索引的話，這個(gè)會(huì)比較快 */ LIMIT 0,5)UNION ALL (SELECT CONCAT(’affiliate_’, aa. KEY) AS `key`, aa. DATA, aa.date_addedFROM `cf_affiliate_activity` aaORDER BY a.date_added DESC /* 如果date_added有索引的話，這個(gè)會(huì)比較快 */ LIMIT 0,5 ) ) aORDER BY a.date_added DESCLIMIT 0, 5;