Small research on improving efficiency of Stmt preprocessing in MySQL

Copy codeThe Code is as follows:
DELIMITER $
Set @ stmt = 'select userid, username from myuser where userid? And? ';
Prepare s1 from @ stmt;
Set @ s1 = 2;
Set @ S2. = 100;
Execute s1 using @ s1, @ s2;
Deallocate prepare s1;
$
DELIMITER;

For a query written in this form, you can replace the parameter at will. The person who gives the Code calls it preprocessing. I think this should be the variable binding in MySQL ...... However, I heard two voices during the data query process. One is that MySQL has a syntax similar to Oracle variable binding, but it has no practical effect, that is, it can only be easily written and cannot improve the efficiency. This statement is shown in the post for several years:
Http://www.itpub.net/thread-1210292-1-1.html
Http://cuda.itpub.net/redirect.php? Fid = 73 & tid = 1210572 & goto = nextnewset
Another saying is that variable binding in MySQL can indeed improve efficiency. This is expected. If there is a future, try it by yourself.
The test is carried out on the local machine, the data size is relatively small, the specific number does not have practical significance, but, can be used to describe some problems, the database version is a mysql-5.1.57-win32 without installation version.
In the attitude of not very familiar with the database ^_^, many detours were taken during the experiment. This article focuses on the conclusions and does not list the design process of the experiment, the article is a bit boring. I want someone to make a brick, because I come to the conclusion that preprocessing is less efficient than direct execution when there is no cache ...... I don't want to accept my experiment results .. If preprocessing only regulates queries and increases the cache hit rate, I personally think it is a little useful. I hope someone familiar with it can point out what it looks like-NewSilen
Lab preparation
First file NormalQuery. SQL
Copy codeThe Code is as follows:
Set profiling = 1;
Select * From MyTable where dictids = 100601000004;
Select DictID from MyTable limit 1,100;
Select DictID from MyTable limit 2,100;
/* Duplicate code is omitted from limit 1,100 to limit 100,100 */
......
Select DictID from MyTable limit 100,100;
SELECT query_id, seq, STATE, 10000 * duration from information_schema.profiling into outfile 'd:/NormalResults.csv 'fields TERMINATED by', 'Lines TERMINATED by' \ n ';

The second SQL file StmtQuery. SQL
Copy codeThe Code is as follows:
Set profiling = 1;
Select * From MyTable where dictids = 100601000004;
Set @ stmt = 'select DictID from MyTable limit ?,? ';
Prepare s1 from @ stmt;
Set @ s = 100;
Set @ s1 = 101;
Set @ S2. = 102;
......
Set @ s100= 200;
Execute s1 using @ s1, @ s;
Execute s1 using @ s2, @ s;
......
Execute s1 using @ NAS, @ s;
SELECT query_id, seq, STATE, 10000 * duration from information_schema.profiling into outfile 'd:/StmtResults.csv 'fields TERMINATED by', 'Lines TERMINATED by' \ n ';

Below are some notes:
1. set profiling = 1; after executing this statement, you can read the statement execution details from the information_schema.profiling table, which actually contains a lot of content, including the time information I need. This is a temporary table, you must reset the profiling attribute for each new session to read data from this table.
2. Select * From MyTable where DictID = 100601000004;
This line of code seems to have nothing to do with our experiment. I thought so too. The reason why I added this sentence is what I found in my previous exploration, there is an open table step in the execution process. If it is the first time to open a table, it takes a long time. Therefore, before executing the following statement, I first executed this line of code to open the table used for the test.
3. by default, MySQL stores 15 query records in the information_schema.profiling table. You can modify the profiling_history_size attribute to adjust the query history. I hope it will allow me to retrieve enough data at a time, but the maximum value is 100, although I changed to 150, only 100 items can be found at last, but it is enough.
4. I didn't list all the SQL code, because the query statement is similar. The ellipsis is used in the above Code. The final result is two csv files. You can save the results to the database for analysis.
Lab procedure
Restart the database, run the file NormalQuery. SQL, and run the file StmtQuery. SQL. Two result files are displayed.
Restart the database, run StmtQuery. SQL, run the file NormalQuery. SQL, and get the other two results files.
Lab results
The detailed results are provided at the end of the attachment download. If you are interested, you can check it.
Result Analysis
One hundred query statements are executed in each SQL file. No duplicate query statements exist and no query cache exists. The following results are obtained by calculating the average time of SQL Execution.

From the results, we can see that the statements in the NormalQuery are faster than those in the pre-processing statement!

Let's take a look at the specific situation of each query. The Normal and Stmt queries are executed two hundred times each time. The details of each step are as follows:

It can be seen from this that, first, normalquery is one step less than stmtquery, and second, although stmt is superior to normal in many steps, it loses too much in the executing step, the final result is also failed.

 Finally, an experiment result for querying the cache is provided. The detailed steps are not listed.

When querying the cache, Normal wins ......

Conclusion

This is probably the case. I recalled that preprocessing can improve the efficiency on the Internet. Basically, queries are executed in programming mode. I don't know whether this is related or not, and the foundation is limited, I hope the cows in the garden can see it and help me solve the problem.
Lab result attachment

MySQL preprocessing experiment results