Was sind Cursor und Bindevariablen in MySQL?

1. Einführung in MySQL-Cursor

MySQL bietet schreibgeschützte, unidirektionale Cursor auf der Serverseite und kann nur in gespeicherten Prozeduren oder Client-APIs auf niedrigerer Ebene verwendet werden.

Der MySQL-Cursor ist schreibgeschützt, da das Objekt, auf das er zeigt, in einer temporären Tabelle und nicht in den tatsächlich abgefragten Daten gespeichert wird. Es kann Zeile für Zeile auf die Abfrageergebnisse verweisen und das Programm dann die weitere Verarbeitung durchführen lassen. Innerhalb einer gespeicherten Prozedur können Cursor mehrfach verwendet und in Schleifenstrukturen „verschachtelt“ werden.

Das Cursor-Design von MySQL „bereitet“ auch Fallen für Unvorsichtige vor. Da es mithilfe temporärer Tabellen implementiert wird, vermittelt es Entwicklern den Eindruck von Effizienz. Das Wichtigste, was es zu beachten gilt, ist die Notwendigkeit, beim Öffnen eines Cursors die gesamte Abfrage auszuführen.

Betrachten Sie die folgende gespeicherte Prozedur:

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

Dieses Beispiel zeigt, dass ein Cursor sofort geschlossen werden kann, während unvollendete Daten verarbeitet werden. Benutzer, die Oracle oder SQL Server verwenden, werden nicht denken, dass es bei dieser gespeicherten Prozedur ein Problem gibt, aber in MySQL bringt dies viele unnötige zusätzliche Vorgänge mit sich. Verwenden Sie SHOW STATUS, um diese gespeicherte Prozedur zu diagnostizieren. Sie können sehen, dass sie 1000 Indexseiten lesen und 1000 Schreibvorgänge ausführen muss. In der fünften Zeile der Aktion „Cursor öffnen“ fanden 1000 Lese- und Schreibvorgänge statt, da 1000 Datensätze in der Tabelle sakila.film vorhanden waren.

Dieser Fall zeigt uns, dass, wenn Sie beim Schließen des Cursors nur einen kleinen Teil einer großen Ergebnismenge scannen, die gespeicherte Prozedur möglicherweise nicht nur den Overhead nicht reduziert, sondern auch viel mehr mit sich bringt Overhead. Zu diesem Zeitpunkt müssen Sie die Verwendung von LIMIT in Betracht ziehen, um die zurückgegebene Ergebnismenge einzuschränken.

Die Verwendung von Cursorn kann dazu führen, dass MySQL einige zusätzliche ineffiziente E/A-Vorgänge ausführt. Da temporäre Speichertabellen die Typen BLOB und TEXT nicht unterstützen, muss MySQL eine temporäre Festplattentabelle erstellen, um sie zu speichern, wenn die vom Cursor zurückgegebenen Ergebnisse solche Spalten enthalten, was zu einer schlechten Leistung führen kann. Auch ohne diese Spalte erstellt MySQL eine temporäre Tabelle auf der Festplatte, wenn die temporäre Tabelle tmp_table_size überschreitet.

Obwohl MySQL keine clientseitigen Cursor unterstützt, können Cursor simuliert werden, indem alle Abfrageergebnisse über die Client-API zwischengespeichert werden. Dies unterscheidet sich nicht von der direkten Verwaltung der Ergebnisse in einem Speicherarray.

2. Bind-Variablen

Ab MySQL Version 4.1 werden serverseitige Bind-Variablen (vorbereitete Anweisung) unterstützt, was die clientseitige und serverseitige Datenübertragung erheblich verbessert . Effizienz. Wenn Sie einen Client verwenden, der das neue Protokoll unterstützt, z. B. MySQL CAPI, können Sie die Funktion zum Binden von Variablen verwenden. Darüber hinaus können sowohl Java als auch .NET auch ihre jeweiligen Clients Connector/J und Connector/NET verwenden, um Bind-Variablen zu verwenden.

Schließlich gibt es eine SQL-Schnittstelle zur Unterstützung von Bind-Variablen, auf die wir später noch eingehen werden (es kann hier leicht zu Verwirrung führen).

Der Client sendet eine Vorlage der SQL-Anweisung an den Server, um eine SQL zu erstellen, die Variablen bindet. Nach dem Empfang des SQL-Anweisungsrahmens analysiert und speichert der Server den Teilausführungsplan der SQL-Anweisung und gibt ein SQL-Anweisungsverarbeitungshandle an den Client zurück. Jedes Mal, wenn diese Art von Abfrage in der Zukunft ausgeführt wird, gibt der Client die Verwendung dieses Handles an.

Bei SQL-Bindungsvariablen werden Fragezeichen verwendet, um die Orte zu markieren, an denen Parameter empfangen werden können. Wenn tatsächlich bestimmte Abfragen erforderlich sind, werden diese Fragezeichen durch bestimmte Werte ersetzt. Das Folgende ist beispielsweise eine SQL-Anweisung, die Variablen bindet:

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

Senden Sie das SQL-Handle und jeden Fragezeichen-Parameterwert an den Server, um eine bestimmte Abfrage auszuführen. Das wiederholte Ausführen spezifischer Abfragen auf diese Weise ist der Vorteil von Bind-Variablen. Die spezifische Methode zum Senden von Wertparametern und SQL-Handles hängt von der Programmiersprache jedes Clients ab. Eine Möglichkeit ist die Verwendung des MySQL-Connectors für Java und .NET. Viele Clients, die die MySQL-C-Sprachlinkbibliothek verwenden, können ähnliche Schnittstellen bereitstellen. Sie müssen verstehen, wie Bind-Variablen gemäß der Dokumentation der verwendeten Programmiersprache verwendet werden.

Aus folgenden Gründen kann MySQL eine große Anzahl wiederholter Anweisungen effizienter ausführen, wenn Bind-Variablen verwendet werden:

1 muss die SQL-Anweisung nur einmal analysieren.

2. Einige Optimierungsarbeiten auf der Serverseite müssen nur einmal ausgeführt werden, da sie einen Teil des Ausführungsplans zwischenspeichern.

• Nur das Senden von Parametern und Handles im Binärformat ist effizienter als jedes Mal das Senden von ASCII-Text. Ein binäres Datumsfeld erfordert nur drei Bytes, wenn es sich jedoch um ASCII-Code handelt, sind es zehn Bytes werden benötigt. Durch die Verwendung der Form von Bind-Variablen können BLOB- und TEXT-Felder in Blöcken übertragen werden, wodurch maximale Einsparungen erzielt werden. Dadurch entfällt die Notwendigkeit einer einmaligen Überweisung. Binäre Protokolle können außerdem viel Speicher auf der Clientseite einsparen, den Netzwerk-Overhead reduzieren und auch den Overhead für die Konvertierung von Daten aus dem ursprünglichen Speicherformat in ein Textformat einsparen.

4 Es müssen nur die Parameter – nicht die gesamte Abfrageanweisung – an den Server gesendet werden, sodass der Netzwerkaufwand geringer ist.

5. Wenn MySQL Parameter speichert, speichert es diese direkt im Cache, sodass sie nicht mehrmals in den Speicher kopiert werden müssen.

Bind-Variablen sind relativ sicherer. Die Tatsache, dass man sich nicht um Escape-Maßnahmen in der Anwendung kümmern muss, macht es viel einfacher und reduziert gleichzeitig das Risiko von SQL-Injection und -Angriffen erheblich. (Vertrauen Sie niemals Benutzereingaben, auch nicht bei Verwendung von Bind-Variablen.)

可以只在使用绑定变量的时候才使用二进制传输协议。如果使用常规的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. In der aktuellen Version können Bind-Variablen nicht in gespeicherten Funktionen verwendet werden (sie können jedoch in gespeicherten Prozeduren verwendet werden).

Wenn die an die Variablen gebundenen Ressourcen nicht freigegeben werden, kann es leicht zu Ressourcenlecks auf der Serverseite kommen. Da es sich bei der Beschränkung der Gesamtzahl der SQL-Bindungsvariablen um eine globale Beschränkung handelt, kann sich ein Fehler an einer Stelle auf alle anderen Threads auswirken.

6. Einige Operationen, wie z. B. BEGIN, können in Bind-Variablen nicht abgeschlossen werden.

Aber das größte Hindernis bei der Verwendung von Bind-Variablen könnte sein:

Wie es implementiert ist und was das Prinzip ist, diese beiden Punkte können leicht verwirrend sein. Manchmal ist es schwierig zu erklären, was der Unterschied zwischen den folgenden drei Arten von Bind-Variablen ist:

1 Clientseitig simulierte Bind-Variablen

• Der Client-Treiber empfängt eine SQL mit Parametern und konvertiert dann den angegebenen Wert in es und senden Sie schließlich die vollständige Abfrage an die Serverseite.

2. Variablen auf der Serverseite binden

• Der Client verwendet ein spezielles Binärprotokoll, um die Zeichenfolge mit Parametern an den Server zu senden, und verwendet dann das Binärprotokoll, um den spezifischen Parameterwert zur Ausführung an den Server zu senden .

3. Binden Sie Variablen der SQL-Schnittstelle

• Der Client sendet zunächst eine Zeichenfolge mit Parametern an den Server, die der SQL-Anweisung mit PREPARE ähnelt, sendet dann die SQL zum Festlegen der Parameter und verwendet sie schließlich EXECUTE SQL ausführen. All dies nutzt gewöhnliche Textübertragungsprotokolle.

Das obige ist der detaillierte Inhalt vonWas sind Cursor und Bindevariablen in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!