Introducing the preprocessing (prepared statement) performance test of MySQL database

##Free learning recommendation: mysql video tutorial

1. What does preprocessing do

When we submit a database statement, the statement reaches the database service, and the database service needs to parse the sql statement, for example Syntax check, query conditions are optimized first and then executed. For preprocessing, simply speaking, the original interaction between the client and the database service is divided into two times. First, submit the database statement and let the database service parse the statement first. Second, submit the parameters, call the statement and execute it. In this way, for statements that are repeatedly executed multiple times, you can submit and parse the database statement once, and then continuously call and execute the statement that has just been parsed. This saves the time of parsing the same statement multiple times. In order to achieve the purpose of improving efficiency.

Preprocessing statements support placeholders (place holders), and parameters are submitted by binding placeholders. A very important point is that only values ​​can be bound to placeholders, not some keywords of the SQL statement. For example, statement: "select * from student where student.id = ?". If the placeholder (?) is "1 or 1=1", then "1 or 1=1" will be regarded as a value, that is, enclosed with `` symbols. Finally, this illegal statement will be error. Thereby achieving the vulnerability of sql injection (sql injestion).

The three main steps of the preprocessing mechanism:

1. Preprocess the statement

2. Execute the statement

3. Destruct the preprocessing statement.

2. Introduction to the `performance_schema`.`prepared_statements_instances` table

Run the sql script: show global variable like ‘%prepare%’. You can see a system variable called ‘

performance_schema_max_prepared_statement_instances’. Its value of 0 means that the prepared statement performance data record table is not enabled `performance_schema`.`prepared_statements_instances`; -1 means that the number of records is dynamically processed; other positive integer values ​​represent performance_schema_max_prepared_statement_instancesThe maximum number of records Number of items.

What is the table `performance_schema`.`prepared_statements_instances`? It is used to record some basic information and performance data of prepared statements. For example, the ID of the prepared statement, the name of the prepared statement, the specific statement content of the prepared statement, the number of times the prepared statement is executed, the time taken for each execution, the thread ID to which each prepared statement belongs, etc. When we create a prepared statement, a piece of data will be inserted into this table. Prepared statements are based on connections. If the connection is disconnected, the prepared statements are automatically deleted. But the `performance_schema`.`prepared_statements_instances` table is global and has nothing to do with the database connection. With this data, we can know, 1. Whether the statement executed in the code is really preprocessed, 2. By understanding the execution of the preprocessed statement, we can determine whether a statement needs to be preprocessed in the business.

3. Description of qt prepare function

Based on my own project needs, the client code for this test uses Qt. A key function is recorded here: the prepare function of the QSqlQuery class. Calling the prepare function is to submit a command to the database to create a prepared statement. This means that during the call, there will be an interaction with the database service. It should be noted that when the same QSqlQuery class object calls prepare for the second time, the prepared statement created by the first call to prepare will be deleted, and then a prepared statement will be created, even if the two prepared statements are Exactly the same. When calling the exec function of QSqlQuery, the prepared statements previously created by QSqlQuery will also be deleted. Therefore, at the end of the query, the connection is closed, or the query executes other statements, resulting in the `performance_schema`.`prepared_statements_instances` table having no records of related prepared statements, and it will be mistakenly believed that the creation of the prepared statement failed. In fact, Qt's approach also saves us from manually deleting prepared statements.

4. Experimental conjecture

The difference between a regularly executed statement and a statement executed after preprocessing is that in the case of multiple executions, the preprocessed statement only needs Parse the SQL statement once, and then spend more time transmitting parameters and binding parameters. Prepared statements use the binary transfer protocol when returning results, while ordinary statements use the text format transfer protocol. Therefore we make the following conjecture and verify it.

1. If a simple statement is executed, there is not much difference in performance between ordinary execution and preprocessing execution. Prepared statements only show their advantages when complex statements are repeatedly executed.

2. When the query result set is a large amount of data, prepared statements will show performance advantages.

5. Experimental data record

##9isselect * from task where task.taskId = ?No1100021710Noselect * from task where task.taskId = idNo1100020411 isselect * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id < 200000 and a.taskId = ? ";No2100036612No select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id < 200000 and a. taskId = 32327";No21000380

6. Conclusion

The experimental data results are a bit different from what I expected, but after repeated inspection of the test code and test process, it was confirmed that there should be no problem with the test itself. Respecting the experimental data, we draw the following conclusions:

1. By comparing experiments 5 and 6, and comparing experiments 11 and 12, we can conclude that conjecture 1 is wrong. The conclusion should be: There is no significant performance difference between MySQL preprocessing and regular queries under simple statements and complex statements.

2. By comparing experiment 1 and experiment 2, and comparing experiment 7 and experiment 8, it can be concluded that conjecture 2 is wrong. The conclusion should be: There is no significant performance gap in data transmission between the results of MySQL preprocessing and conventional queries.

        3. In addition, compare the experimental data of the remote database and the local database. It can be concluded that MySQL database will bring significant performance improvement to data operations locally.

Related free learning recommendations: mysql database(Video)

Serial number	Whether to preprocess	Statement	Whether it is a remote database	Amount of data returned	Total number of executions of each experimental statement	Average total time consumption of three experiments/unit millisecond
1	is	select * from task where task.taskId in (?)	is	1000	1000	69822
2	No	select * from task where task.taskId in (arr)	is	1000	1000	66778
3	is	select * from task where task.taskId = ?	是	1	1000	1260
4	No	select * from task where task.taskId = id	Yes	1	1000	951
5	is	select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a .taskName like '%s%' and b.file_id > 100000 and b.file_id < 200000 and a.taskId = ? ";	Yes	2	1000	2130
6	No	select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id < 200000 and a. taskId = 32327";	is	2	1000	1480
7	Yes	select * from task where task.taskId in (?)	No	1000	1000	57051
8	No	select * from task where task.taskId in (arr)	No	1000	1000	56235

The above is the detailed content of Introducing the preprocessing (prepared statement) performance test of MySQL database. For more information, please follow other related articles on the PHP Chinese website!