Apakah kursor dan pembolehubah mengikat dalam MySQL

1. Pengenalan kepada kursor MySQL

MySQL menyediakan kursor sehala baca sahaja pada bahagian pelayan, dan hanya boleh digunakan dalam prosedur tersimpan atau API pelanggan peringkat rendah.

Kursor MySQL adalah baca sahaja kerana objek yang ditunjukkannya disimpan dalam jadual sementara dan bukannya data pertanyaan sebenar. Ia boleh menunjuk kepada hasil pertanyaan baris demi baris, dan kemudian biarkan program melakukan pemprosesan selanjutnya. Dalam prosedur tersimpan, kursor boleh digunakan beberapa kali dan boleh "bersarang" dalam struktur gelung.

Reka bentuk kursor MySQL juga "menyediakan" perangkap untuk mereka yang cuai. Kerana ia dilaksanakan menggunakan jadual sementara, ia memberikan pembangun ilusi kecekapan. Keperluan untuk melaksanakan keseluruhan pertanyaan apabila membuka kursor adalah perkara yang paling penting untuk diperhatikan.

Pertimbangkan prosedur tersimpan berikut:

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

Contoh ini menunjukkan kursor boleh ditutup serta-merta semasa memproses data tertunggak. Pengguna yang menggunakan Oracle atau SQL Server tidak akan fikir terdapat sebarang masalah dengan prosedur tersimpan ini, tetapi dalam MySQL, ini akan membawa banyak operasi tambahan yang tidak perlu. Gunakan SHOW STATUS untuk mendiagnosis prosedur tersimpan ini Anda boleh melihat bahawa ia perlu membaca 1000 halaman indeks dan melakukan 1000 penulisan. Dalam baris kelima tindakan kursor terbuka, 1000 operasi baca dan tulis berlaku kerana terdapat 1000 rekod dalam jadual sakila.filem.

Kes ini memberitahu kami bahawa jika anda hanya mengimbas sebahagian kecil daripada set hasil yang besar semasa menutup kursor, maka prosedur yang disimpan mungkin bukan sahaja gagal untuk mengurangkan overhed, malah membawa banyak overhed tambahan. Pada masa ini, anda perlu mempertimbangkan untuk menggunakan LIMIT untuk mengehadkan set hasil yang dikembalikan.

Menggunakan kursor boleh menyebabkan MySQL melakukan beberapa operasi I/O tambahan yang tidak cekap. Oleh kerana jadual memori sementara tidak menyokong jenis BLOB dan TEXT, jika hasil yang dikembalikan oleh kursor mengandungi lajur sedemikian, MySQL mesti mencipta jadual cakera sementara untuk menyimpannya, yang boleh menyebabkan prestasi yang lemah. Walaupun tanpa lajur ini, MySQL masih akan mencipta jadual sementara pada cakera apabila jadual sementara melebihi tmp_table_size.

Walaupun MySQL tidak menyokong kursor sisi klien, kursor boleh disimulasikan dengan menyimpan semua hasil pertanyaan melalui API klien. Ini tidak berbeza dengan mengekalkan hasil secara langsung dalam tatasusunan memori.

2. Ikat pembolehubah

Bermula dari MySQL versi 4.1, pembolehubah ikatan sisi pelayan (pernyataan yang disediakan) disokong, yang meningkatkan kecekapan penghantaran data sisi klien dan pelayan. Jika anda menggunakan klien yang menyokong protokol baharu, seperti MySQL CAPI, anda boleh menggunakan ciri pembolehubah bind. Selain itu, kedua-dua Java dan .NET juga boleh menggunakan pelanggan masing-masing Connector/J dan Connector/NET untuk menggunakan pembolehubah bind.

Akhir sekali, terdapat antara muka SQL untuk menyokong pembolehubah mengikat, yang akan kita bincangkan kemudian (ia boleh menyebabkan kekeliruan dengan mudah di sini).

Pelanggan menghantar templat pernyataan SQL kepada pelayan untuk mencipta SQL yang mengikat pembolehubah. Selepas menerima bingkai pernyataan SQL, pelayan menghuraikan dan menyimpan pelan pelaksanaan separa bagi pernyataan SQL, dan mengembalikan pemegang pemprosesan pernyataan SQL kepada klien. Setiap kali jenis pertanyaan ini dilaksanakan pada masa hadapan, pelanggan menentukan penggunaan pemegang ini.

Untuk pembolehubah mengikat SQL, tanda soal digunakan untuk menandakan lokasi di mana parameter boleh diterima Apabila pertanyaan khusus sebenarnya diperlukan, nilai khusus digunakan untuk menggantikan tanda soal ini. Sebagai contoh, berikut ialah pernyataan SQL yang mengikat pembolehubah:

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

Hantar pemegang SQL dan setiap nilai parameter tanda soal ke pelayan untuk melaksanakan pertanyaan tertentu. Melaksanakan pertanyaan khusus dengan cara ini berulang kali ialah kelebihan pembolehubah mengikat. Kaedah khusus untuk menghantar parameter nilai dan pengendalian SQL bergantung pada bahasa pengaturcaraan setiap pelanggan. Menggunakan penyambung MySQL untuk Java dan .NET adalah satu cara. Ramai pelanggan yang menggunakan perpustakaan pautan bahasa MySQL C boleh menyediakan antara muka yang serupa Anda perlu memahami cara menggunakan pembolehubah bind mengikut dokumentasi bahasa pengaturcaraan yang digunakan.

Atas sebab-sebab berikut, MySQL boleh melaksanakan sejumlah besar pernyataan berulang dengan lebih cekap apabila menggunakan pembolehubah bind:

1. Ia hanya perlu dihuraikan sekali pada pernyataan SQL sisi pelayan.

2. Sesetengah pengoptimuman berfungsi pada bahagian pelayan hanya perlu dilaksanakan sekali kerana ia menyimpan sebahagian daripada rancangan pelaksanaan.

• Hanya menghantar parameter dan pemegang dalam binari adalah lebih cekap daripada menghantar teks ASCII setiap kali Medan tarikh binari hanya memerlukan tiga bait, tetapi jika kod ASCII memerlukan sepuluh bait. Dengan menggunakan bentuk pembolehubah bind, medan BLOB dan TEKS boleh dihantar dalam ketulan, dengan itu mencapai penjimatan maksimum. Ini menghapuskan keperluan untuk pemindahan sekali sahaja. Protokol binari juga boleh menjimatkan banyak memori pada bahagian klien, mengurangkan overhed rangkaian, dan juga menyimpan overhed menukar data daripada format storan asal kepada format teks.

4 Hanya parameter - bukan keseluruhan pernyataan pertanyaan - perlu dihantar ke pelayan, jadi overhed rangkaian akan menjadi lebih kecil.

5. Apabila MySQL menyimpan parameter, ia secara langsung menyimpannya dalam cache, menghapuskan keperluan untuk menyalinnya beberapa kali dalam ingatan.

Pembolehubah ikatan secara relatifnya lebih selamat. Tidak perlu mengendalikan melarikan diri dalam aplikasi menjadikannya lebih mudah sementara juga mengurangkan risiko suntikan dan serangan SQL dengan ketara. (Jangan sekali-kali mempercayai input pengguna pada bila-bila masa, walaupun semasa menggunakan pembolehubah bind.)

可以只在使用绑定变量的时候才使用二进制传输协议。如果使用常规的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. Di bawah versi semasa, pembolehubah mengikat tidak boleh digunakan dalam fungsi tersimpan (tetapi ia boleh digunakan dalam prosedur tersimpan).

Jika sumber yang terikat kepada pembolehubah tidak dikeluarkan, kebocoran sumber cenderung berlaku di bahagian pelayan. Oleh kerana had pada jumlah bilangan SQL pembolehubah ikatan ialah had global, ralat di satu tempat boleh menjejaskan semua urutan lain.

6. Sesetengah operasi, seperti BEGIN, tidak dapat diselesaikan dalam pembolehubah bind.

Walau bagaimanapun, halangan terbesar untuk menggunakan pembolehubah bind mungkin:

Bagaimana ia dilaksanakan dan apakah prinsipnya, kedua-dua perkara ini mudah mengelirukan. Kadangkala, sukar untuk menerangkan perbezaan antara tiga jenis pembolehubah ikatan berikut:

1. Terima SQL dengan parameter, kemudian bawa nilai yang ditentukan ke dalamnya, dan akhirnya hantar pertanyaan lengkap ke pelayan.

• 2. Pembolehubah ikatan sebelah pelayan

Pelanggan menggunakan protokol binari khas untuk menghantar rentetan dengan parameter ke pelayan, dan kemudian Gunakan protokol binari untuk menghantar nilai parameter tertentu ke pelayan dan melaksanakannya.

• 3 Ikat pembolehubah antara muka SQL

Pelanggan mula-mula menghantar rentetan dengan parameter ke pelayan, yang serupa dengan menggunakan PREPARE SQL. kenyataan, kemudian hantar SQL untuk menetapkan parameter, dan akhirnya gunakan EXECUTE untuk melaksanakan SQL. Semua ini menggunakan protokol pemindahan teks biasa.

Atas ialah kandungan terperinci Apakah kursor dan pembolehubah mengikat dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!