What are cursors and bind variables in MySQL

1. Introduction to MySQL cursors

MySQL provides read-only, one-way cursors on the server side, and can only be used in stored procedures or lower-level client APIs.

MySQL cursor is read-only because the object it points to is stored in a temporary table instead of the actual queried data. It can point to the query results line by line, and then let the program do further processing. Within a stored procedure, cursors can be used multiple times and can be "nested" within loop structures.

MySQL's cursor design also "prepares" traps for the careless. Because it is implemented using temporary tables, it gives developers an illusion of efficiency. The need to execute the entire query when opening a cursor is the most important thing to note.

Consider the following stored procedure:

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

This example shows that the cursor can be closed immediately while processing outstanding data. Users using Oracle or SQL Server will not think there is any problem with this stored procedure, but in MySQL, this will bring a lot of unnecessary extra operations. Use SHOW STATUS to diagnose this stored procedure. You can see that it needs to read 1000 index pages and do 1000 writes. In the fifth row of the open cursor action, 1000 read and write operations occurred because there were 1000 records in the table sakila.film.

This case tells us that if you only scan a small part of a large result set when closing the cursor, then the stored procedure may not only fail to reduce the overhead, but instead bring a lot of additional overhead. At this time, you need to consider using LIMIT to limit the result set returned.

Using cursors may cause MySQL to perform some additional inefficient I/O operations. Because temporary memory tables do not support BLOB and TEXT types, if the results returned by the cursor contain such columns, MySQL must create a temporary disk table to store them, which may cause poor performance. Even without this column, MySQL will still create a temporary table on disk when the temporary table exceeds tmp_table_size.

Although MySQL does not support client-side cursors, cursors can be simulated by caching all query results through the client API. This is no different from maintaining the results directly in a memory array.

2. Bind variables

Starting from MySQL version 4.1, server-side bind variables (prepared statement) are supported, which greatly improves the efficiency of client-side and server-side data transmission. If you use a client that supports the new protocol, such as MySQL CAPI, you can use the bind variable feature. In addition, both Java and .NET can also use their respective clients Connector/J and Connector/NET to use bind variables.

Finally, there is a SQL interface to support bind variables, which we will discuss later (it can easily cause confusion here).

The client sends a template of the SQL statement to the server to create a SQL binding variable. After receiving the SQL statement frame, the server parses and stores the partial execution plan of the SQL statement, and returns a SQL statement processing handle to the client. Every time this type of query is executed in the future, the client specifies the use of this handle.

In SQL that binds variables, question marks are used to mark the locations where parameters can be received. When a specific query really needs to be executed, specific values ​​are used to replace these question marks. For example, the following is a SQL statement that binds variables:

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

Send the SQL handle and each question mark parameter value to the server to execute a specific query. Executing specific queries in this way repeatedly is the advantage of bind variables. The specific method of sending value parameters and SQL handles depends on the programming language of each client. Using the MySQL connector for Java and .NET is one way. Many clients using the MySQL C language link library can provide similar interfaces. You need to understand how to use bind variables according to the documentation of the programming language used.

For the following reasons, MySQL can execute a large number of repeated statements more efficiently when using bind variables:

1. It only needs to be parsed once on the server side SQL statement.

2. Some optimizer work on the server side only needs to be executed once because it caches part of the execution plan.

• Sending only parameters and handles in binary is more efficient than sending ASCII text every time. A binary date field only requires three bytes, but if ASCII code requires ten bytes. By using the form of bind variables, BLOB and TEXT fields can be transmitted in chunks, thereby achieving maximum savings. This eliminates the need for a one-time transfer. Binary protocols may also save a lot of memory on the client side, reduce network overhead, and also save the overhead of converting data from the original format of storage to a text format.

4. Only the parameters - not the entire query statement - need to be sent to the server, so the network overhead will be smaller.

5. When MySQL stores parameters, it stores them directly in the cache, eliminating the need to copy them multiple times in memory.

Bind variables are relatively safer. Not having to handle escaping in the application makes it much simpler while also significantly reducing the risk of SQL injection and attacks. (Never trust user input at any time, even when using bind variables.)

可以只在使用绑定变量的时候才使用二进制传输协议。如果使用常规的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. Under the current version, bind variables cannot be used in stored functions (but they can be used in stored procedures).

If the resources bound to the variables are not released, resource leakage will easily occur on the server side. Because the limit on the total number of bind variable SQLs is a global limit, an error in one place may affect all other threads.

6. Some operations, such as BEGIN, cannot be completed in bind variables.

But the biggest obstacle to using bind variables may be:

How it is implemented and what the principle is, these two points are easily confusing. Sometimes, it is difficult to explain what is the difference between the following three types of bind variables:

1. Client-side simulated bind variables

• Client-side driver Receives an SQL with parameters, brings the specified values ​​into it, and finally sends the complete query to the server.

2. Server-side bind variables

• #The client uses a special binary protocol to send the string with parameters to the server, and then Use the binary protocol to send specific parameter values ​​to the server and execute it.

3. Bind variables of SQL interface

• The client first sends a string with parameters to the server, which is similar to using PREPARE SQL statement, then send the SQL to set the parameters, and finally use EXECUTE to execute the SQL. All of this uses ordinary text transfer protocols.

The above is the detailed content of What are cursors and bind variables in MySQL. For more information, please follow other related articles on the PHP Chinese website!