Shulou(Shulou.com)05/31 Report--

Editor to share with you an example of pre-processing prepared statement performance testing in the MySQL database, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

1. What did the pretreatment do?

When we submit a database statement, the statement reaches the database service, and the database service needs to parse the sql statement, such as syntax checking, query conditions are optimized before execution. For preprocessing, it simply divides the original interaction between the client and the database service into two times. First, submit the database statement and let the database service parse the statement first. Second, submit the parameters, invoke the statement and execute it. In this way, for statements that have been executed repeatedly, you can submit and parse the database statement once, and then constantly call the parsed statement and execute it. 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 holder) and submit parameters by binding placeholders. A very important point is that it is only values that can be bound to placeholders, not keywords in sql statements. For example, the statement: "select * from student where student.id =?" If you put "1 or 1" in the placeholder, "1 or 1" will be treated as a value, that is, it will be included with the ``symbol, and eventually this illegal statement will go wrong. So as to achieve the vulnerability of releasing 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 table `performance_ schema`.`prepared _ statements_ instances`

Run the sql script: show global variable like'% prepare%'. You can see a system variable called 'performance_schema_max_prepared_statement_instances'. A value of 0 indicates that the preprocessing statement performance data record table `performance_ schema`.`prepared _ statements_ instances` is not enabled;-1 indicates the dynamic processing of the number of records; and other positive integer values represent the maximum number of performance_schema_max_prepared_statement_instances records.

What is the table `performance_ schema`.`prepared _ statements_ instances`? It is used to record some basic information and performance data of preprocessing statements. For example, the ID of the preprocessing statement, the name of the preprocessing statement, the specific statement content of the preprocessing statement, the number of times the preprocessing statement is executed, the time consumed for each execution, the thread id to which each preprocessing statement belongs, and so on. When we create a preprocessing statement, a piece of data is inserted into the table. The preprocessing statement is based on the connection, and if the connection is broken, the preprocessing statement is deleted automatically. But the `performance_ schema`.`prepared _ statements_ instances` table is global and has nothing to do with the database connection. With these data, we can know: 1, whether the statements executed in the code are really preprocessed, and 2, by understanding the execution of preprocessed statements to determine whether a statement needs to be preprocessed in the business.

3. Qt prepare function description

According to my own project requirements, the client code for this test uses Qt. A key function is recorded here: the prepare function of the QSqlQuery class. To call the prepare function is to submit a command to the database to create a preprocessing statement. It means that there is an interaction with the database service during the call. It is important to note that when the same QSqlQuery class object calls prepare for the second time, the preprocessing statement created by the first call to prepare is deleted, and then another preprocessing statement is created, even if the two preprocessing statements are identical. When you call the exec function of QSqlQuery, the preprocessing statements previously created by QSqlQuery are also deleted. Therefore, at the end of the query, the connection is closed, or the query executes other statements, resulting in no record of the relevant preprocessing statements in the `performance_ schema`.`prepared _ statements_ instances` table, which will be mistaken for the creation of preprocessing statements failed. In fact, this approach of Qt also eliminates the need for us to artificially delete preprocessing statements.

4. Experimental conjecture

The difference between the statements executed normally and those executed after preprocessing is that in the case of multiple execution, the preprocessing statement only needs to parse the sql statement once, and then spend more time on the transmission parameters and binding parameters. Preprocessing statements use a binary transport protocol when returning results, while ordinary statements use a text format transport protocol. So we make the following conjecture and verify it.

1. If a simple statement is executed, there is little difference in performance between normal execution and preprocessing execution. Preprocessing statements show advantages only when complex statements are executed repeatedly.

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

5. Record the experimental data

Does the sequence number preprocess the statement whether the remote database returns the amount of data each time the experiment statement is executed three times the average total time per millisecond 1 is select * from task where task.taskId in (?) Is 10001000698222 No select * from task where task.taskId in (arr) 10001000667783 is select * from task where task.taskId =? Is 1100012604 No select * from task where task.taskId = id is 110009515 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 = ？";是2100021306否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";是2100014807是select * from task where task.taskId in (?)否10001000570518否select * from task where task.taskId in (arr)否10001000562359是select * from task where task.taskId = ?否1100021710否select * from task where task.taskId = id否1100020411是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 = ？";否2100036612否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 "; No 21000380

6. Conclusion

The results of the experiment are a little different from what I expected, but after reviewing the test code and the test process over and over again, it is confirmed that the test itself should be fine. Respecting the experimental data, we come to the following conclusions:

1. Through the comparison between experiment 5 and experiment 6, experiment 11 and experiment 12, we can get that conjecture 1 is wrong. The conclusion should be that there is no significant difference in performance between MySQL preprocessing and conventional queries under simple statements and complex statements.

2. Through the comparison between experiment 1 and experiment 2, experiment 7 and experiment 8, it can be concluded that conjecture 2 is wrong. The conclusion should be that there is no significant performance difference between MySQL preprocessing and conventional query in data transmission.

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

These are all the contents of the article "examples of preprocessing prepared statement performance testing in MySQL databases". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.