MySQL のカーソルとバインド変数とは何ですか

1. MySQL カーソルの概要

MySQL はサーバー側で読み取り専用の一方向カーソルを提供し、ストアド プロシージャまたは下位レベルのクライアント API でのみ使用できます。

MySQL カーソルは、それが指すオブジェクトが実際にクエリされたデータではなく一時テーブルに格納されているため、読み取り専用です。クエリ結果を 1 行ずつ指定し、プログラムにさらなる処理を実行させることができます。ストアド プロシージャ内では、カーソルを複数回使用でき、ループ構造内で「入れ子」にすることができます。

MySQL のカーソル設計は、不注意な人に対する罠を「準備」します。一時テーブルを使用して実装されるため、開発者は効率が良いという幻想を与えます。カーソルを開くときにクエリ全体を実行する必要があることに注意することが最も重要です。

次のストアド プロシージャを考えてみましょう:

CREATE PROCEDURE bad_cursor()
BEGIN
DECLARE film_id INT;
DECLARE f CURSOR FOR SELECT film_id FROM sakila.film;
OPEN f;
FETCH f INTO film_id;
CLOSE f;
END

この例は、未処理のデータの処理中にカーソルをすぐに閉じることができることを示しています。 Oracle や SQL Server を使用しているユーザーは、このストアド プロシージャに何の問題もないと考えるでしょうが、MySQL では、これにより多くの不必要な追加操作が発生します。 SHOW STATUS を使用してこのストアド プロシージャを診断すると、1000 インデックス ページの読み取りと 1000 回の書き込みが必要であることがわかります。オープン カーソル アクションの 5 行目では、テーブル sakila.film に 1000 件のレコードがあったため、1000 件の読み取りおよび書き込み操作が発生しました。

このケースは、カーソルを閉じるときに大きな結果セットのごく一部だけをスキャンすると、ストアド プロシージャはオーバーヘッドを削減できないだけでなく、多くの追加のオーバーヘッドをもたらす可能性があることを示しています。現時点では、LIMIT を使用して返される結果セットを制限することを検討する必要があります。

カーソルを使用すると、MySQL がさらに非効率的な I/O 操作を実行する可能性があります。一時メモリ テーブルは BLOB および TEXT タイプをサポートしていないため、カーソルによって返された結果にそのようなカラムが含まれる場合、MySQL はそれらを格納するために一時ディスク テーブルを作成する必要があり、パフォーマンスが低下する可能性があります。このカラムがなくても、一時テーブルが tmp_table_size を超えると、MySQL はディスク上に一時テーブルを作成します。

MySQL はクライアント側のカーソルをサポートしていませんが、クライアント API を通じてすべてのクエリ結果をキャッシュすることでカーソルをシミュレートできます。これは、結果をメモリ配列に直接保持することと何ら変わりません。

2. バインド変数

MySQL バージョン 4.1 以降、サーバー側のバインド変数 (準備されたステートメント) がサポートされ、クライアント側とサーバー側のデータ送信の効率が大幅に向上します。 MySQL CAPI などの新しいプロトコルをサポートするクライアントを使用する場合は、バインド変数機能を使用できます。さらに、Java と .NET は両方とも、それぞれのクライアント Connector/J および Connector/NET を使用してバインド変数を使用することもできます。

最後に、バインド変数をサポートする SQL インターフェイスがあります。これについては後で説明します (ここで混乱を招きやすいためです)。

クライアントは、SQL ステートメントのテンプレートをサーバーに送信して、SQL バインディング変数を作成します。 SQL ステートメント フレームを受信した後、サーバーは SQL ステートメントの部分的な実行プランを解析して保存し、SQL ステートメント処理ハンドルをクライアントに返します。今後このタイプのクエリが実行されるたびに、クライアントはこのハンドルの使用を指定します。

変数をバインドする SQL では、パラメータを受け取る場所をマークするために疑問符が使用されます。特定のクエリを実際に実行する必要がある場合、これらの疑問符を特定の値で置き換えます。たとえば、次は変数をバインドする SQL ステートメントです。

INSERT INTO tbl(col1, col2, col3) VALUES (?, ?, ?);

SQL ハンドルと各疑問符パラメータ値をサーバーに送信して、特定のクエリを実行します。この方法で特定のクエリを繰り返し実行できるのが、バインド変数の利点です。値パラメータと SQL ハンドルを送信する具体的な方法は、各クライアントのプログラミング言語によって異なります。 Java および .NET 用の MySQL コネクタを使用するのは 1 つの方法です。 MySQL C 言語リンク ライブラリを使用する多くのクライアントは、同様のインターフェイスを提供できますが、使用するプログラミング言語のドキュメントに従ってバインド変数の使用方法を理解する必要があります。

次の理由により、MySQL はバインド変数を使用すると、多数の繰り返しステートメントをより効率的に実行できます:

1. 解析する必要があるのは、サーバー側の SQL ステートメント。

2. サーバー側の一部のオプティマイザー作業は、実行計画の一部をキャッシュするため、一度実行するだけで済みます。

• パラメータとハンドルのみをバイナリで送信する方が、ASCII テキストを毎回送信するよりも効率的です。バイナリの日付フィールドには 3 バイトしか必要ありませんが、ASCII コードの場合は 10 バイトが必要です。バインド変数の形式を使用すると、BLOB および TEXT フィールドをチャンクで送信できるため、最大限の節約が実現します。これにより、1 回限りの転送が不要になります。バイナリ プロトコルは、クライアント側で大量のメモリを節約し、ネットワーク オーバーヘッドを削減し、データを元のストレージ形式からテキスト形式に変換するオーバーヘッドも節約します。

4. クエリ ステートメント全体ではなくパラメータのみをサーバーに送信する必要があるため、ネットワークのオーバーヘッドが小さくなります。

5. MySQL がパラメータを保存する場合、パラメータをキャッシュに直接保存するため、メモリ内でパラメータを何度もコピーする必要がなくなります。

バインド変数は比較的安全です。アプリケーション内でエスケープを処理する必要がないため、作業が大幅に簡素化されると同時に、SQL インジェクションや攻撃のリスクも大幅に軽減されます。 (バインド変数を使用している場合でも、決してユーザー入力を信頼しないでください。)

可以只在使用绑定变量的时候才使用二进制传输协议。如果使用常规的mysql_query()接口，则无法使用二进制传输协议。还有一些客户端让你使用绑定变量，先发送带参数的绑定SQL，然后发送变量值，但是实际上，这些客户端只是模拟了绑定变量的接口，最后还是会直接用具体值代替参数后，再使用mysql_query()发送整个查询语句。

2.1 绑定变量的优化

对使用绑定变量的SQL，MySQL能够缓存其部分执行计划，如果某些执行计划需要根据传入的参数来计算时，MySQL就无法缓存这部分的执行计划。根据优化器什么时候工作，可以将优化分为三类。

在本书编写的时候，下面的三点是适用的。

1.在准备阶段

• 服务器解析SQL语句，移除不可能的条件，并且重写子查询。

2.在第一次执行的时候

• 如果可能的话，服务器先简化嵌套循环的关联，并将外关联转化成内关联。

3.在每次SQL语句执行时

• 服务器做如下事情：

1)过滤分区。

2)如果可能的话，尽量移除COUNT()、MIN()和MAX()。

3)移除常数表达式。

4)检测常量表。

5)做必要的等值传播。

6)分析和优化ref、range和索引优化等访问数据的方法。

7)优化关联顺序。

2.2 SQL接口的绑定变量

MySQL支持了SQL接口的绑定变量。不使用二进制传输协议也可以直接以SQL的方式使用绑定变量。下面案例展示了如何使用SQL接口的绑定变量： 

当服务器收到这些SQL语句后，先会像一般客户端的链接库一样将其翻译成对应的操作。

这意味着你无须使用二进制协议也可以使用绑定变量。

正如你看到的，比起直接编写的SQL语句，这里的语法看起来有一些怪怪的。

那么，这种写法实现的绑定变量到底有什么优势呢？

最主要的用途就是在存储过程中使用。在MySQL 5.0版本中，就可以在存储过程中使用绑定变量，其语法和前面介绍的SQL接口的绑定变量类似。意思是在存储过程中可以创建和运行基于动态SQL语句的代码

“动态”是指可以通过灵活地拼接字符串等参数构建SQL语句。举个例子，下面这个存储过程可以在特定的数据库中执行OPTIMIZE TABLE操作：

DROP PROCEDURE IF EXISTS optimize_tables;
DELIMITER //
CREATE PROCEDURE optimize_tables(db_name VARCHAR(64))
BEGIN
DECLARE t VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = db_name AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
tables_loop: LOOP
FETCH c INTO t;
IF done THEN
LEAVE tables_loop;
END IF;
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE c;
END//
DELIMITER ;

可以这样调用这个存储过程：

mysql> CALL optimize_tables('sakila')

另一种实现存储过程中循环的办法是：

REPEAT
FETCH c INTO t;
IF NOT done THEN
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;

REPEAT和其他循环结构最大的不同是，它在每次循环中都会检查两次循环条件。在这个例子中，因为循环条件检查的是一个整数判断，并不会有什么性能问题，如果循环的判断条件非常复杂的话，则需要注意这两者的区别。

像这样使用SQL接口的绑定变量拼接表名和库名是很常见的，这样的好处是无须使用任何参数就能完成SQL语句。由于库名和表名都是关键字，因此在绑定变量的二进制协议中无法将这两个参数化。LIMIT子句是另一个经常需要动态设置的，因为在二进制协议中无法将其参数化。

另外，编写存储过程时，SQL接口的绑定变量通常可以很大程度地帮助我们调试绑定变量，如果不是在存储过程中，SQL接口的绑定变量就不是那么有用了。因为SQL接口的绑定变量，它既没有使用二进制传输协议，也没有能够节省带宽，相反还总是需要增加至少一次额外网络传输才能完成一次查询。所有只有在某些特殊的场景下SQL接口的绑定变量才有用，比如当SQL语句非常非常长，并且需要多次执行的时候。

2.3 绑定变量的限制

关于绑定变量的一些限制和注意事项如下：

1.绑定变量是会话级别的，所以连接之间不能共用绑定变量句柄。同样地，一旦连接断开，则原来的句柄也不能再使用了。（连接池和持久化连接可以在一定程度上缓解这个问题。）

2.在MySQL 5.1版本之前，绑定变量的SQL是不能使用查询缓存的。

3.并不是所有的时候使用绑定变量都能获得更好的性能。如果只是执行一次SQL，那么使用绑定变量方式无疑比直接执行多了一次额外的准备阶段消耗，而且还需要一次额外的网络开销。（要正确地使用绑定变量，还需要在使用完成后，释放相关的资源。）

4. 現在のバージョンではバインド変数をストアドファンクションで使用することはできません(ストアドプロシージャでは使用できます)。

変数にバインドされているリソースを解放しないと、サーバー側でリソース漏洩が発生しやすくなります。バインド変数 SQL の合計数の制限はグローバル制限であるため、1 か所でのエラーが他のすべてのスレッドに影響を与える可能性があります。

6. BEGIN などの一部の操作は、バインド変数では完了できません。

しかし、バインド変数を使用する際の最大の障害は次のとおりです。

バインド変数の実装方法と原理は何か、これら 2 つの点は混乱しやすいです。次の 3 種類のバインド変数の違いを説明するのが難しい場合があります:

1. クライアント側でシミュレートされたバインド変数

• クライアント側ドライバーパラメーターを含む SQL を受け取り、指定された値をそれに取り込み、最後に完全なクエリをサーバーに送信します。

2. サーバー側バインド変数

• #クライアントは特別なバイナリ プロトコルを使用して、パラメーターを含む文字列をサーバーに送信します。次に、バイナリプロトコルを使用して特定のパラメータ値をサーバーに送信し、実行します。

3. SQL インターフェイスのバインド変数

• クライアントは最初にパラメーターを含む文字列をサーバーに送信します。これは PREPARE SQL を使用するのと似ています。ステートメントを実行し、次に SQL を送信してパラメータを設定し、最後に EXECUTE を使用して SQL を実行します。これらはすべて、通常のテキスト転送プロトコルを使用します。

以上がMySQL のカーソルとバインド変数とは何ですかの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。