付録 D. 制限と規制

目次

D.1. ストアド ルーチンとトリガの規制
D.2. サーバサイドカーソルの規制
D.3. サブクエリの規制
D.4. ビューの規制
D.5. XA トランザクションの規制
D.6. MySQL の制限
D.6.1. 結合の制限

以下は、サブクエリやビューを含む MySQL 機能の使用に関する制限と規制についての考察です。

D.1. ストアド ルーチンとトリガの規制

ここに記載されている規制のうちには、すべてのストアド ルーチン、つまりストアド プロシージャとストアド ファンクションの両方に適用するものがあります。またあるものは、ストアド ファンクションのみにあてはまり、ストアド プロシージャには関係しません。

ストアド ファンクションに適用されるすべての規制は、トリガにもあてはまります。

ストアド ルーチンは、任意の SQL 文を取り込むことはできません。次のステートメントは利用不可です:

  • 固定文 LOCK TABLESUNLOCK TABLES

  • LOAD DATA および LOAD TABLE

  • SQL プリペアド ステートメント (PREPAREEXECUTEDEALLOCATE PREPARE)。解説 : ストアド ルーチン内 (ステートメントをストリングとして動的に構築し、実行する場 ) で動的 SQL を使うことはできない。 この規制は MySQL 5.0.13 からストアド プロシージャに適用され、引き続きストアド ファンクションおよびトリガにも用いられる。

ストアド ファンクションでは、次の予備のステートメントまたは操作が不許可になっています ( ストアド プロシージャには適合しない ):

  • 明示的もしくは暗黙の遂行、またはロールバックを行うステートメント。

  • 結果セットを返すステートメント。これには INTOvar_list 句を持たない SELECT 文と、SHOW 文が含まれる。関数は SELECT ... INTO var_list または、カーソルと FETCH 文を使って結果セットを処理することができる。項17.2.7.3. 「SELECT ... INTO ステートメント」 参照。

  • FLUSH ステートメント

  • 再帰的ステートメント。ストアド ファンクションを再帰的に使用することはできない。

  • ストアド ファンクションまたはトリガ内では、その関数やトリガを実行したステートメントが ( 読み取り、または書込みに ) すでに使用しているテーブルを改変することはできない。

規制のあるものは通常、ストアド ファンクションとトリガに適用されるがストアド プロシージャには当てはまらないということになっていますが、ストアド ファンクション内またはトリガ内から実行されたストアド プロシージャには、それらの規制が適用されますので注意してください。例えば、FLUSH をストアド プロシージャで使用することはできますが、そうしたストアド プロシージャをストアド ファンクションやトリガから呼び出すことができません。

ルーチン パラメータ、ローカル変数、テーブル カラムに同じ識別子を使用することは可能です。また、同じローカル変数名を入れ子になったブロックで使うこともできます。例 :

CREATE PROCEDURE p (i INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SELECT i FROM t;
  BEGIN
    DECLARE i INT DEFAULT 1;
    SELECT i FROM t;
  END;
END;

その場合、識別子が曖昧になるため、次の優先順位ルールが適用されます :

  • ローカル変数は、ルーチン パラメータもしくはテーブル カラムより優先される。

  • ルーチン パラメーターをテーブル カラムより優先。

  • 内側ブロックのローカル変数を、外側ブロックのローカル変数より優先。

テーブル カラムが変数より優先されないという場合、その挙動は標準のものではありません。

ストアド ルーチンの使用は、反復の問題の原因になる場合があります。この問題については 項17.4. 「ストアドルーチンとトリガのバイナリログ」 で詳しく述べられています。

INFORMATION_SCHEMA スキーマは PARAMETERS テーブルをまだ持っていませんので、ランタイムでルーチン パラメータ情報を得る必要のあるアプリケーションは、SHOW CREATE 文のアウトプットを構文解析するなどの回避策を取らなければなりません。

ストアド ルーチンのデバッグ機能は存在しません。

CALL 文を準備することはできません。これはサーバ側のプリペアド ステートメントでも、SQL プリペアド ステートメントでも同じです。

UNDO ハンドラはサポートされていません。

FOR ループはサポートされていません。

サーバ スレッド間の作業の問題を防ぐため、クライアントがステートメントを発行する際、サーバはステートメントの実行に利用できるルーチンとトリガのスナップショットを使用します。これは、ステートメントの実行の間に使用される可能性のあるプロシージャ、関数、トリガのリストを算出してロードし、そしてステートメントの実行に進むということです。これにより、ステートメントが実行される間、他のスレッドが実行するルーチンに変化がないということになります。

RENAME DATABASE 文が、ストアド ルーチンを新たなスキーマ名に変えることはありません。 項12.1.18. 「RENAME DATABASE 構文」 参照。

トリガには、次の予備のステートメントまたは操作が利用不可になっています :

  • トリガは現在、外部キーのアクションでは起動されない。

  • RETURN 文は、値を返すことのできないトリガでは利用不可。すぐさまトリガから出るには、LEAVE 文を使用する。

  • mysql データベースのテーブルではトリガを使用できない。

MySQL Cluster のストアド ルーチンおよびトリガ.? ストアド ファンクション、ストアド プロシージャ、およびトリガはすべて、NDB 保存エンジンを使用したテープルにサポートされていますが、それらは MySQL サーバ間に Cluster SQL ノードとして自動的に伝播しないということを忘れないでください。その原因は次になります :

  • ストアド ルーチンの定義は、MyISAM 保存エンジンを使用して、mysql システム データベース内のテーブルに保管されます。

  • トリガの定義を含む .TRN および .TRG ファイルを、NDB 保存エンジンが読み取ることはなく、また Cluster ノード間でのコピーもされません。

MySQL Cluster テーブルに作用するすべてのストアド ルーチンまたはトリガは、そのストアド ルーチンまたはトリガを使用したいクラスタに関与する各 MySQL サーバの、適切な CREATE PROCEDURECREATE FUNCTION 、または CREATE TRIGGER 文を実行することによって再作成されなければなりません。同様に、既存のストアド ルーチンまたはトリガのいかなる変更も、クラスタにアクセスする各 MySQL Server の適切な ALTER または DROP 文を使用して、すべての Cluster SQL ノードで明示的に実行される必要があります。

警告

NDB 保存エンジンを使用するために、mysql データベース テーブルを変換して、上の最初の項で説明のあった問題を回避しようと試みるのはやめてくださいmysql データベースのシステム テーブルを変更すると希望しない結果を引き起こす可能性が高く、MySQL AB では支持していません

D.2. サーバサイドカーソルの規制

サーバサイドカーソルは、mysql_stmt_attr_set() 関数を介して C API に実装されます。ストアド ルーチンのカーソルにも同じ実装が使用されます。サーバサイドカーソルによって、結果セットをサーバ側で生成することが可能になりますが、クライアントが請求した行以外をクライアントに転送することはできません。例えば、もしクライアントがクエリを実行し、しかし最初の行しか必要としない場合は、残りの行は転送されません。

MySQL では、サーバサイドカーソルは一時テーブルへと出力されます。最初、これは MEMORY テーブルになりますが、そのサイズが max_heap_table_size システム変数の値に達すると、MyISAM テーブルに変換されます。この実装の制限のひとつとして、大きな結果セットでは、カーソルでの行の呼び出しに時間がかかる場合があります。

カーソルは読み取り専用で、カーソルを行のアップデートに使用することはできません。

UPDATE WHERE CURRENT OF および DELETE WHERE CURRENT OF は、アップデート可能なカーソルはサポートされていないため実装されていません。

カーソルは保持不可能 ( コミットの後で開いたままにしておくことができない )。

カーソルはセンシティブです。

カーソルはスクロール不可能。

カーソルに名称は付いていません。ステートメント ハンドラがカーソル ID として作用します。

プリペアド ステートメントごとに、カーソルをひとつだけ開いておくことができます。複数のカーソルが必要な場合は、複数のステートメントを準備しなければなりません。

結果セットを生成するステートメントで、準備モードでサポートされていないものにはカーソルを使うことはできません。そのようなステートメントには、CHECK TABLESHANDLER READ 、そして SHOW BINLOG EVENTS があります。

D.3. サブクエリの規制

  • 後に修正される既知のバグ :ALLANY 、または SOME を使用して NULL 値をサブクエリと比較し、サブクエリが空の結果を戻す場合、その比較は TRUE もしくは FALSE よりも、NULL の非標準結果を評価した可能性があります。

  • サブクエリの外側のステートメントが次のどれかである可能性があります :SELECTINSERTUPDATEDELETESET または DO

  • IN のサブクエリの最適化は、= オペレータ、または IN(value_list) 構文に対する最適化ほど効果的ではありません。

    貧弱な IN サブクエリの動作の一般的なケースで、サブクエリが少数の行を戻すのに対し、外側のクエリは多数の行をサブクエリの結果と比較するために戻します。

    その問題は、IN サブクエリを使用するステートメントでは、最適化機能がそれを相関サブクエリとして書き換えるということにあります。非相関サブクエリを使用する次のステートメントを検討してください :

    SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
    

    最適化機能がステートメントを相関サブクエリに書き換えます :

    SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
    

    内側と外側のクエリが M および N 行を戻す場合、それぞれ、実行時間は非相関サブクエリに対してと同様に、O(M+N) ではなく、O(M×N) の順になります。

    それはつまり、IN サブクエリが、サブクエリが戻す同じ値をリストアップする IN(value_list) 構文を使って書かれたクエリよりも、格段に実行速度が遅い場合があることを示しています。

  • 基本的に、テーブルを改変したり、サブクエリの同じテーブルから選択することはできません。例えば、この制限は次のフォームのステートメントに適用されます :

    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 へのアップデートまでには選択されているということになります。

  • 行の比較のオペレーションは一部のみサポートされています :

    • expr IN (subquery) では、exprn タプル ( 行コンストラクタ シンタックスを介して指定 ) であることがあり、サブクエリが n タプルの行を戻すことができます。

    • expr op {ALL|ANY|SOME} (subquery) では、expr がスカラ値である必要があり、またサブクエリはカラム サブクエリでなくてはならず、複数段の行を戻すことができません。

    つまり、n タプルの行を戻すサブクエリには、次がサポートされています :

    (val_1, ..., val_n) IN (subquery)
    

    しかし、次はサポートされていません :

    (val_1, ..., val_n) op {ALL|ANY|SOME} (subquery)
    

    IN の行の比較がサポートされているのに、他はされていない理由は、IN= 比較および AND オペレーションへ書き換えることによって実装されているためです。この方法は、ALLANY 、または SOME には使うことができません。

  • 行コンストラクタの最適化は的確に行われていません。次のふたつの式は等価ですが、ふたつ目のみが最適化されます :

    (col1, col2, ...) = (val1, val2, ...)
    col1 = val1 AND col2 = val2 AND ...
    
  • FROM 句のサブクエリは相関サブクエリにはなりえません。それらは外側のクエリを評価する前に出力 ( 結果セットを生成するために実行される ) されているので、外側のクエリの行ごとに評価を受けることはできません。

  • 最適化機能は、サブクエリに対してより結合に対するほうが完成度が高く、そのため多くの場合は、サブクエリを使用するステートメントを結合として書き換えるほうが、より効率的に実行されます。

    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 句のサブクエリが、組合せアルゴリズムの適用が可能なビューに類似している場合、クエリを書き換えて組合せアルゴリズムを適用すると、インデックスが使用できるようになります。次のステートメントにはその種のサブクエリが含まれています :

    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;
    

    このタイプの書き換えには、ふたつの利点があります :

    • インデックスが使えない一時テーブルの使用を避ける。書き換えられたクエリでは、最適化機能は t1 でインデックスを使うことができる。

    • 最適化機能に、複数の実行計画から選択する自由を与える。例えば、クエリを結合として書き換えることで、最適化機能が 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 でキー検査を行ってください。

D.4. ビューの規制

ビューの処理は最適化されていません :

  • ビューにインデックスを作成することはできません。

  • 組合せアルゴリズムを使用して処理されたビューに、インデックスを使うことは可能です。しかし、誘導可能なアルゴリズムで処理されたビューは、その背後にあるテーブルのインデックスを活用することができません ( 一時テーブルの作成中にインデックスを使用することはできます ) 。

ビューの FROM 句でサブクエリを使用することはできません。この制限はいずれ取り除かれる予定です。

一般原則として、テーブルを改変したり、サブクエリの同じテーブルから選択することはできません。項D.3. 「サブクエリの規制」 参照。

また、テーブルから選択するビューを選ぶ場合、ビューがサブクエリのテーブルから選択する場合、そして、ビューが組合せアルゴリズムを使って評価される場合、同じ原則が適用されます。例 :

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;

一時テーブルを使ってビューが評価される場合、ビュー サブクエリのテーブルから選択し、さらに外側のクエリのテーブルで改変することが 可能 です。その場合、ビューは一時テーブルに格納されることになり、したがってサブクエリのテーブルから選択し、「同時に」 改変するということにはなりません。(これもまた、ビュー定義で ALGORITHM = TEMPTABLE を指定して、誘導可能なアルゴリズムをMySQL が使用するよう強制できると便利だと考える理由です)。

DROP TABLE または ALTER TABLE を使用して、ビュー定義 ( ビューを無効化するもの ) や削除または変更オペレーションからの無警告結果に使われるテーブルを、削除または変更することができます。エラーは、後でビューが使用された時に発生します。

ビュー定義は特定のステートメントによって「凍結」されています :

  • PREPARE によって準備されたステートメントがビューを参照する場合、後でステートメントが実行される度に参照されるビューの内容が、ステートメントが準備できた時のビューの内容になります。これは、ステートメントが準備された後、実行される前にビュー定義が変更されても同じことです。例 :

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

    EXECUTE ステートメントによって返される結果は 2 ではなく、1 です。

  • ストアド ルーチンのステートメントがビューを参照する場合、ステートメントによって参照されたビューの内容は、最初にステートメントが実行された時のその内容です。これは例えば、ステートメントがループで実行された場合、さらなるステートメントの繰り返しが、後でビュー定義がループで変更されたとしても、同じビューの内容を参照するという意味になります。例 :

    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 を使用して、結合として定義されたビューの背後のテーブルをひとつ以上更新することはできない。

  • DELETE を使用して、結合として定義されたビューを更新することはできない。

ビューの現在の実装には欠点があります。もしユーザがビューの作成に必要な基本権限 ( CREATE VIEWSELECT 権限 ) を取得した場合、SHOW VIEW 権限も取得しない限り、そのユーザはオブジェクトの SHOW CREATE VIEW を呼び出すことはできないでしょう。

この欠点は、mysqldump を持つデータベースのバックアップの問題につながり、権限の不足のため失敗する原因になる恐れがあります。この問題は バグ #22062 で説明されています。

問題に対する迂回策としては、ビューが作成された時に MySQL が暗黙的にSHOW VIEW 権限を与えないので、管理権限者が手動でその権限を、CREATE VIEW を与えられたユーザに提供することです。

D.5. XA トランザクションの規制

XA トランザクションのサポートは、InnoDB 保存エンジンに限られています。

MySQL XA の実装は、MySQL サーバが Resource manager の役割をしたり、クライアント プログラムが Transaction Manager の役割をする 「外部 XA,」 のものです。「内部 XA」 は実装されていません。これによって、MySQL サーバ内の個々の保存エンジンが RM の役割をしたり、サーバそのものが TM の役割をしたりすることができます。内部 XA は、ひとつ以上の保存エンジンが関与する XA トランザクションを処理するために必要になります。テーブル ハンドラのレベルで 2 相コミットをサポートする保存エンジンを必要とするため、内部 XA の実装は不完全なもので、現時点では InnoDB だけです。

XA START では、JOIN および RESUME 句はサポートされていません。

XA END では、SUSPEND [FOR MIGRATE] 句はサポートされていません。

xid 値の bqual 部分が、大規模トランザクション内の各 XA トランザクションによって異なる必要があるという条件は、現在の MySQL XA の実装の制限です。これは XA の仕様によるものではありません。

XA トランザクションが PREPARED 状態に至ると、MySQL サーバが切断される ( 例えば、Unix の kill -9 で ) 、または不自然に停止する場合、サーバが再起動した後にトランザクションを続けることができるはずです。しかし、クライアントが再接続をし、トランザクションをコミットした場合、そのトランザクションは、たとえコミットされていても、バイナリ ログには記録されないでしょう。これは、データとバイナリ ログの同調が途切れたということです。XA を複製と一緒に安全に使用することはできないと思われます。

サーバが保留になっている XA トランザクションを、たとえ PREPARED 状態に至っていても、ロールバックするということはありえます。これは、クライアントの接続が切断されて、サーバは起動を続ける、またはクライアントが接続されているのに、サーバが自然に停止する場合に起こります 。( 後者のケースでは、サーバが切断される各接続をマークし、それに関連する PREPARED XA トランザクションをロールバックする ) 。PREPARED XA トランザクションをコミットする、またはロールバックすることは可能であるはずですが、これはバイナリのロギング メカニズムを変更なしには実行できません。

D.6. MySQL の制限

このセクションでは、現在の MySQL の制限 5.1 をリストアップします。

D.6.1. 結合の制限

ひとつの結合で参照できるテーブルの最大数は 61 です。これはビューの定義で参照できるテーブルの数と同じです。

アダルトレンタルサーバー