Specific use of Mysql prepare preprocessing

The significance of MySQL PREPARE preprocessing technology is that it is a technology to reduce the pressure on the server.

That is to say, in most cases, a certain SQL statement for a certain requirement may be called and executed repeatedly, or only individual values ​​may be different each time it is executed.
for example:

• The WHERE clause value of SELECT is different;
• The SET clause values ​​of UPDATE are different;
• The VALUES values ​​of INSERT are different;
  If the above lexical semantic analysis, statement optimization, execution plan formulation, etc. are required every time, the efficiency will be significantly reduced.

1. Preprocessing

MySQL provides support for server-side prepared statements, which is called preprocessing.

This support takes advantage of an efficient client/server binary protocol. Using prepared statements with placeholders for parameter values ​​provides the following benefits:

• Reduce the overhead of parsing statements each time they are executed. Typically, database applications process a large number of nearly identical statements, changing only literal or variable values ​​in clauses such as WHERE for queries and deletes, SET for updates, and VALUES for inserts.
• Prevent SQL injection attacks. Parameter values ​​can contain unescaped SQL quotes and delimiters.

Preprocessing interface

1. Prepared statements in applications You can use server-side prepared statements through client programming interfaces, including the MySQL C API client library for C programs, MySQL Connector/J for Java programs, and MySQL for use. NET technology program MySQL Connector/NET. For example, the C API provides a set of function calls that constitute its prepared statement API.

2. Prepared statements in SQL scripts There is also an alternative SQL interface for prepared statements. But no programming is required; it is directly available at the SQL level, and you can use it in any program that can send SQL statements to the server to be executed, such as the mysql client program.

2. Pretreatment application method

The SQL syntax for prepared statements is based on three SQL statements:

• The PREPARE statement prepares for execution.
• EXECUTE executes a prepared statement.
• DEALLOCATE PREPARE releases a prepared statement.

A. Example:

Prepared statements cannot be used across sessions:

mysql>CREATE TABLE `t1` (
  `id` int NOT NULL,
   NAME varchar(20),
KEY `idx_id` (`id`)
) ENGINE=InnoDB ;
 
mysql>INSERT INTO t1(id,name) values(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F');
 
#Set the prepared statement mysql>PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE a=? ';
 
#Set the transfer variable mysql>SET @a = 8;
 
#Execute statementmysql>EXECUTE stmt1 USING @a;
 
# Release the prepared statement mysql>DEALLOCATE PREPAR stmt1;

B. Preprocessing to track changes in execution plans

By observing the changes in the status indicator Select_scan (the number of full-table search queries executed), you can determine whether the data volume is affected.

The execution plan of the preprocessed SQL statement changes as the amount of data changes.

C. The stored procedure contains preprocessing

Prepared Statements If you create a prepared statement within a stored routine, it is not released when the stored routine ends.

DELIMITER //
 
DROP PROCEDURE IF EXISTS proc_prepared;
CREATE PROCEDURE proc_prepared()
BEGIN
DECLARE a INT;
DECLARE i INT;
PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE id>? ';
SET @a = 5;
EXECUTE stmt1 USING @a;
END //
 
DELIMITER ;
 
call proc_prepared();
After the stored procedure, the prepared statement is called separately and the result set is returned: This indicates that the preprocessing did not destroy SET @a = 5;
EXECUTE stmt1 USING @a;
+----+------+
| id | NAME |
+----+------+
| 6 | F |
. . .

After the stored procedure, the prepared statement is called separately and the result set is returned: This means that the preprocessing is not destroyed

SET @a = 5; EXECUTE stmt1 USING @a; +----+------+ | id | NAME | +----+------+ | 6 | F | 。。。

D. View the cost of parsing statements through profile

By profiling the execution time of various statements, the time spent on parsing the statements is within 0.01 seconds. Can be ignored.
Therefore, no obvious advantages have been found in pretreatment at present.

3. Summary

The initial role of precompilation:

• Improve efficiency: perform analysis, checking, and compilation in advance.
• Improving security: Preventing SQL injection

Limitations and practical effects:

• Because preprocessing is limited to the session level, it cannot reflect its true value now. Because the MySQL GA version does not have the concept of thread pool, each connection is each session
• The overhead of parsing compiled statements is basically negligible for the MySQL environment.
• The execution plan also changes with the amount of data.

Judging from its limitations and actual effects, it is currently not functioning as it should. Not suitable for use in a sound field environment.

This is the end of this article about the specific use of MySQL prepare preprocessing. For more relevant MySQL prepare preprocessing content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!