附錄I:特性限制

目錄

I.1. 對儲存子程式和觸發程式的限制
I.2. 對伺服器端光標的限制
I.3. 對子查詢的限制
I.4. 對視圖的限制
I.5. 對XA事務的限制

在本附錄中,介紹了使用諸如子查詢或視圖等MySQL特性時存在的限制。

I.1. 對儲存子程式和觸發程式的限制

這裡介紹的某些限制適用於所有的儲存子程式,即儲存程式和儲存函數。某些限制僅適用於儲存函數而不是儲存程式。

對儲存函數的的所有限制也適用於觸發程式。

註釋:如果SQL語句,如SELECT ... INTO語句包含具有相同名稱的對列的引用以及聲明的局部變數,MySQL會將引用解釋為變數的名稱。這是一種非標準的行為,優先順序通常是列名、然後是SQL變數和參數。請參見20.2.9.3節,「SELECT ... INTO語句」

儲存子程式不能包含任意SQL語句。在儲存子程式中,禁止使用下述語句:

·         CHECK TABLES

·         LOCK TABLES, UNLOCK TABLES

·         LOAD DATA, LOAD TABLE

·         SQL預處理語句(PREPAREEXECUTEDEALLOCATE PREPARE)。隱含意義:不能在儲存子程式中使用動態SQL語句(其中,能夠以字串形式構造動態語句,然後執行它們)。從MySQL 5.0.13開始,對於儲存程式放寬了該限制,但該限制仍適用於儲存函數和觸發程式。

·         OPTIMIZE TABLE

對於儲存函數(而不是儲存程式),禁止下述額外語句:

·         執行顯式或隱式提交或回滾操作的語句。

·         返回結果集的語句。包括沒有INFO子句的SELECT語句,以及SHOW語句。能夠用SELECT … INTO,或使用光標和FETCH語句處理結果集的函數。

·         FLUSH語句。注意,儘管能夠在儲存程式中使用FLUSH,但不能從儲存函數或觸發程式使用這類儲存程式。

注意,儘管某些限制在正常情況下適用於儲存函數和觸發程式,不適用於儲存程式,如果它們是從儲存函數或觸發程式中使用的,這些限制也適用於儲存程式。

使用儲存子程式會導致複製問題。關於這方面的進一步討論,請參見20.4節,「儲存子程式和觸發程式的二進制日誌功能」

INFORMATION_SCHEMA尚不包含PARAMETERS資料表,因此,對於需要在運行時獲取子程式參數訊息的應用程式來說,必須採用相應的規避錯誤,如解析SHOW CREATE語句的輸出。

沒有儲存子程式調試工具。

儲存子程式使用了具體化的光標,而不是固有光標(在伺服器端生成結果集並對結果集進行高速緩衝處理,然後在客戶端獲取結果集時按行返回)。

不能提前處理CALL語句。無論是對伺服器端預處理語句還是SQL預處理語句,均成立。

為了防止伺服器線程間的交互問題,當客戶端發出語句時,伺服器將使用可用的、用於語句執行的子程式和觸發程式快照。也就是說,伺服器將計算出可在語句執行期間使用的儲存程式、函數和觸發程式的列資料表,加載它們,然後進入語句執行。這意味著,在語句執行的同時,它不會看到其他線程對子程式所作的變更。

I.2. 對伺服器端光標的限制

MySQL 5.0.2開始,通過mysql_stmt_attr_set() C API函數實現了伺服器端光標。伺服器端光標允許在伺服器端生成結果集,但不會將其傳輸到客戶端,除非客戶端請求這些行。例如,如果客戶端執行了查詢,但僅對第1行感興趣,那麼不會傳輸剩餘的行。

光標是只讀的,不能使用光標來更新行。

未實施UPDATE WHERE CURRENT OFDELETE WHERE CURRENT OF,這是因為不支援可更新的光標。

光標是不可保持的(提交後不再保持打開)。

光標是不敏感的。

光標是不可滾動的。

光標是未命名的。語句處理程式起著光標ID的作用。

對於每條預處理語句,僅能打開1個光標。如果需要多個光標,必須處理多條語句。

如果在預處理模式下不支援語句,不能在生成結果集的語句上使用光標。包括CHECK TABLESHANDLER READSHOW BINLOG EVENTS語句。

I.3. 對子查詢的限制

隨後將更正的一致問題:如果將NULL值與使用ALLANYSOME的子查詢進行比較,而且子查詢返回空結果,比較操作將對NULL的非標準結果進行評估,而不是TRUEFALSE

子查詢的外部語句可以是SELECTINSERTUPDATEDELETESETDO中的任何一個。

僅部分支援行比較操作:

·         對於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操作的序列完成的。該方法不能用於ALLANYSOME

未良好最佳化行構造程式。下面的兩個資料表達式是等效的,但只有第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.    最佳化程式在選擇不同的執行計劃方面具有更大的自由。例如,將查詢改寫為聯合,那麼就允許最佳化程式首先使用t1t2

可能的未來最佳化:對於沒有關聯子查詢的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」中進行鍵搜尋。

I.4. 對視圖的限制

視圖處理功能概念未最佳化:

·         不能在視圖上建立索引。

·         對於使用MERGE算法處理的視圖,可以使用索引。但是,對於使用臨時資料表算法處理的視圖,不能在其基資料表上利用索引提供的優點(儘管能夠在臨時資料表的生成過程中使用索引)。

在視圖的FROM子句中不能使用子查詢。未來該限制將被放寬。

存在一個一般原則,不能更改某一資料表並在子查詢的相同資料表內進行選擇。請參見I.3節,「對子查詢的限制」

如果從資料表選擇了視圖並接著從視圖進行了選擇,同樣的原理也適用,如果在子查詢中從資料表選擇了視圖並使用MERGE算法評估了視圖,也同樣。例如:

CREATE VIEW v1 AS
SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a);
 
UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;

如果視圖是使用臨時資料表評估的,可從視圖子查詢中的資料表進行選擇,並仍能更改外部查詢中的資料表。在該情況下,視圖將被具體化,因此,您實際上不能從子查詢的資料表中進行選擇並「同時」更改它(這是您打算強制MySQL使用臨時資料表算法的另一原因,其方法是在視圖定義中指定ALGORITHM = TEMPTABLE關鍵字)。

可以使用DROP TABLEALTER TABLE來捨棄或更改視圖定義中使用的資料表(它會是視圖失效),而且捨棄或更改操作不會導致告警。但在以後使用視圖時會出錯。

視圖定義是通過特定語句「凍結」的:

·         如果PREPARE預處理的語句引用了視圖,以後每次執行語句時看到的視圖內容與預處理視圖時的內容相同。即使在語句預處理完成之後、在執行語句之前更改了視圖定義,情況也同樣。例如:

·                CREATE VIEW v AS SELECT 1;
·                PREPARE s FROM 'SELECT * FROM v';
·                ALTER VIEW v AS SELECT 2;
·                EXECUTE s;

EXECUTE語句返回的結果是1,而不是2

·         如果儲存子程式中的語句引用了視圖,語句所見到的視圖內容與首次執行語句時的相同。這意味著,如果語句是以循環方式執行的,進一步的語句迭代見到的視圖內容是相同的,即使在循環過程中更改了視圖定義也同樣。例如:

·                CREATE VIEW v AS SELECT 1;
·                delimiter //
·                CREATE PROCEDURE p ()
·                BEGIN
·                  DECLARE i INT DEFAULT 0;
·                  WHILE i < 5 DO
·                    SELECT * FROM v;
·                    SET i = i + 1;
·                    ALTER VIEW v AS SELECT 2;
·                  END WHILE;
·                END;
·                //
·                delimiter ;
·                CALL p();

如果使用了程式p(),每次通過循環時SELECT返回1,即使在循環內更改了視圖定義也同樣。

關於視圖的可更新性,對於視圖,其總體目標是,如果任何視圖從理論上講是可更新的,在實際上也應是可更新的。這包括在其定義中有UNION的視圖。目前,並非所有理論上可更新的視圖均能被更新。最初的視圖實施有意採用該方式,為的是盡快地在MySQL中獲得有用的可更新視圖。很多理論上可更新的視圖現已能更新,但限制依然存在:

·         其子查詢位於WHERE子句之外任何位置的可更新視圖。對於某些其子查詢位於SELECT列資料表中的視圖,也是可更新的。

·         不能使用UPDATE來更新定義為Join的視圖的1個以上的基資料表。

·         不能使用DELETE來更新定義為Join的視圖。

I.5. 對XA事務的限制

XA事務支援限於InnoDB儲存引擎。

MySQL XA實施是針對外部XA的,其中,MySQL伺服器作為資源管理器,而客戶端程式作為事務管理器。未實施「內部XA」。這樣,就允許MySQL伺服器內的單獨儲存引擎作為RM(資源管理器),而伺服器本身作為TM(事務管理器)。處理包含1個以上儲存引擎的XA事務時,需要內部XA。內部XA的實施是不完整的,這是因為,它要求儲存引擎在資料表處理程式層面上支援兩階段提交,目前僅對InnoDB實現了該特性。

對於XA START,不支援JOINRESUME子句。

對於XA END,不支援SUSPEND [FOR MIGRATE]子句。

在全局事務內,對於每個XA事務,xid值的bqual部分應是不同的,該要求是對當前MySQL XA實施的限制。它不是XA規範的組成部分。

如果XA事務達到PREPARED狀態而且MySQL伺服器宕機,當伺服器重啟後,能夠繼續處理事務。就像原本應當的那樣。但是,如果客戶端連接中止而伺服器繼續運行,伺服器將回滾任何未完成的XA事務,即使該事務已達到PREPARED狀態也同樣。它應能提交或回滾PREPARED XA事務,但在不更改二進制日誌機制的情況下不能這樣。


這是MySQL參考手冊的翻譯版本,關於MySQL參考手冊,請訪問dev.mysql.com。原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。