程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 細化解析:MySQL 數據庫中對子查詢的限制

細化解析:MySQL 數據庫中對子查詢的限制

編輯:關於MYSQL數據庫

更正的一致缺陷:如果將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”中進行鍵查找。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved