更正的一致缺陷:如果將NULL值與使用ALL、ANY或SOME的子查詢進行比較,而且子查詢返回空結果,比較操作將對NULL的非標准結果進行評估,而不是TRUE或FALSE。
子查詢的外部語句可以是SELECT、INSERT、UPDATE、DELETE、SET或DO中的任何一個。
僅部分支持行比較操作:
·對於expr IN (subquery),expr可以是n-tuple(通過行構造程序語法指定),而且子查詢能返回n-tuples個行。
·對於expr op {ALL|ANY|SOME} (subquery),expr必須是標度值,子查詢必須是列子查詢,不能返回多列行。
換句話講,對於返回n-tuples行的子查詢,支持:
(val_1, ..., val_n) IN (subquery)
但不支持:
(val_1, ..., val_n) op {ALL|ANY|SOME} (subquery)
支持針對IN的行比較,但不支持針對其他的行比較,原因在於,IN實施是通過將其重新編寫為“=”比較和AND操作的序列完成的。該方法不能用於ALL、ANY或SOME。
未良好優化行構造程序。下面的兩個表達式是等效的,但只有第2個表達式能被優化:
(col1, col2, ...) = (val1, val2, ...) col1 = val1 AND col2 = val2 AND ...
對於IN的子查詢優化不如對“=”的優化那樣有效。
對於不良IN性能的一種典型情況是,當子查詢返回少量行,但外部查詢返回將與子查詢結果相比較的大量行。
FROM子句中的子查詢不能與子查詢有關系。在評估外部查詢之前,將對它們進行具體化處理(執行以生成結果集),因此,不能按照外部查詢的行對它們進行評估。
一般而言,不能更改表,並從子查詢內的相同表進行選擇。例如,該限制適用於具有下述形式的語句:
DELETE FROM t WHERE ... (SELECT ... FROM t ...); UPDATE t ... WHERE col = (SELECT ... FROM t ...); {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
例外:如果為FROM子句中更改的表使用子查詢,前述禁令將不再適用。例如:
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
禁令在此不適用,這是因為FROM中的子查詢已被具體化為臨時表,因此“t”中的相關行已在滿足“t”條件的情況下、在更新時被選中。
與子查詢相比,針對聯合的優化程序更成熟,因此,在很多情況下,如果將其改寫為join(聯合),使用子查詢的語句能夠更有效地執行。
但下述情形例外:IN子查詢可被改寫為SELECT DISTINCT聯合。例如:
SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);
可將該語句改寫為:
SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;
但在該情況下,聯合需要額外的DISTINCT操作,而且與子查詢相比,效率並不高。
可能的未來優化:MySQL不改寫針對子查詢評估的聯合順序。在某些情況下,如果MySQL將其改寫為聯合,能夠更有效地執行子查詢。這樣,優化程序就能在更多的執行方案間進行選擇。例如,它能決定是否首先讀取某一表或其他。
例如:
SELECT a FROM outer_table AS ot WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
對於該查詢,MySQL總會首先掃描outer_table,如然後針對每一行在inner_table上執行子查詢。如果outer_table有很多行而inner_table只有少量行,查詢的執行速度或許要慢於本應有的速度。
前述查詢可改寫為:
SELECT a FROM outer_table AS ot, inner_table AS it WHERE ot.a = it.a AND ot.b = it.b;
在該情況下,我們能掃描小的表(inner_table)並查詢outer_table中的行,如果在“ot.a,ot.b”上有索引,速度會更快。
可能的未來優化:對外部查詢的每一行評估關聯的子查詢。更好的方法是,如果外部行的值與之前的行相比沒有變化,不對子查詢進行再次評估,而是使用以前的結果。
可能的未來優化:通過將結果具體化到臨時表,而且該表不使用索引,對FROM子句中的子查詢進行評估。在查詢中與其他表進行比較時,盡管可能是有用的,但不允許使用索引。
可能的未來優化:如果FROM子句中的子查詢類似於可施加MERGE算法的視圖,改寫查詢並采用MERGE算法,以便能夠使用索引。下述語句包含這類子查詢:
SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
該語句可被改寫為聯合,如下所示:
SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
這類改寫具有兩個優點:
1.避免使用那些不能使用索引的臨時表。在改寫的查詢中,優化程序可在t1上使用索引。
2.優化程序在選擇不同的執行計劃方面具有更大的自由。例如,將查詢改寫為聯合,那麼就允許優化程序首先使用t1或t2。
可能的未來優化:對於沒有關聯子查詢的IN、= ANY、<> ANY、= ALL、以及<> ALL,為結果使用“內存中”哈希處理,或對較大的結果使用具有索引的臨時表。例如:
SELECT a FROM big_table AS bt WHERE non_key_field IN (SELECT non_key_fIEld FROM table WHERE condition)
在該情況下,可創建臨時表:
CREATE TABLE t (key (non_key_field)) (SELECT non_key_fIEld FROM table WHERE condition)
然後,對big_table中的每一行,根據bt.non_key_fIEld,在“t”中進行鍵查找。