MySQL의 커서와 바인드 변수란 무엇입니까?

1. MySQL 커서 소개

MySQL은 서버 측에서 읽기 전용 단방향 커서를 제공하며 저장 프로시저 또는 하위 수준 클라이언트 API에서만 사용할 수 있습니다.

MySQL 커서는 읽기 전용입니다. 커서가 가리키는 개체가 실제 쿼리된 데이터 대신 임시 테이블에 저장되기 때문입니다. 쿼리 결과를 한 줄씩 가리킨 다음 프로그램이 추가 처리를 수행하도록 할 수 있습니다. 저장 프로시저 내에서 커서는 여러 번 사용될 수 있으며 루프 구조 내에 "중첩"될 수 있습니다.

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개의 쓰기를 수행해야 함을 알 수 있습니다. 커서 열기 작업의 다섯 번째 행에서는 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 커넥터를 사용하는 것도 한 가지 방법입니다. MySQL C 언어 링크 라이브러리를 사용하는 많은 클라이언트는 유사한 인터페이스를 제공할 수 있습니다. 사용된 프로그래밍 언어 문서에 따라 바인드 변수를 사용하는 방법을 이해해야 합니다.

다음과 같은 이유로 MySQL은 바인드 변수를 사용할 때 많은 반복 문을 더 효율적으로 실행할 수 있습니다.

1 SQL 문은 서버 측에서 한 번만 구문 분석하면 됩니다.

2. 서버 측의 일부 최적화 작업은 실행 계획의 일부를 캐시하기 때문에 한 번만 실행하면 됩니다.

• 매번 ASCII 코드 텍스트를 보내는 것보다 바이너리로 매개변수와 핸들만 보내는 것이 더 효율적입니다. 바이너리 날짜 필드에는 3바이트만 필요하지만 ASCII 코드인 경우에는 10바이트가 필요합니다. 바인드 변수 형태를 이용하면 BLOB, TEXT 필드를 청크로 나누어 전송할 수 있어 최대의 절감 효과를 얻을 수 있습니다. 이렇게 하면 일회성 전송이 필요하지 않습니다. 또한 바이너리 프로토콜은 클라이언트 측에서 많은 메모리를 절약하고, 네트워크 오버헤드를 줄이며, 데이터를 원래 저장소 형식에서 텍스트 형식으로 변환하는 오버헤드도 절약할 수 있습니다.

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의 총 개수에 대한 제한은 전역 제한이므로 한 곳의 오류가 다른 모든 스레드에 영향을 줄 수 있습니다.

6. BEGIN과 같은 일부 작업은 바인드 변수에서 완료할 수 없습니다.

그러나 바인드 변수를 사용하는 데 있어 가장 큰 장애물은 다음과 같습니다.

구현 방법과 원칙이 무엇인지, 이 두 가지 사항은 쉽게 혼동됩니다. 때로는 다음 세 가지 바인드 변수 유형의 차이점을 설명하기 어려울 수 있습니다.

1. 클라이언트 측 시뮬레이션 바인드 변수

• 클라이언트 드라이버는 매개변수가 포함된 SQL을 수신한 후 지정된 값을 다음으로 변환합니다. 그리고 마지막으로 전체 쿼리를 서버 측으로 보냅니다.

2. 서버 측 변수 바인딩

• 클라이언트는 특수한 바이너리 프로토콜을 사용하여 매개변수가 포함된 문자열을 서버에 보낸 다음, 바이너리 프로토콜을 사용하여 특정 매개변수 값을 서버에 전송하여 실행합니다. .

3. SQL 인터페이스의 변수 바인딩

• 클라이언트는 먼저 매개 변수가 포함된 문자열을 서버에 보냅니다. 이는 PREPARE를 사용하는 SQL 문과 유사하며, 그런 다음 매개 변수를 설정하기 위해 SQL을 보내고 마지막으로 다음을 사용합니다. EXECUTE SQL을 실행합니다. 이 모든 것은 일반 텍스트 전송 프로토콜을 사용합니다.

위 내용은 MySQL의 커서와 바인드 변수란 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!