MySQL prepare principle detailed explanation

Benefits of Prepare

The reason why Prepare SQL is generated. First, let's start with the process of executing SQL on the MySQL server. The SQL execution process includes the following stages: lexical analysis -> syntax analysis -> semantic analysis -> execution plan optimization -> execution. The two stages of lexical analysis -> grammatical analysis are called hard parsing. Lexical analysis identifies each word in SQL, and syntax analysis parses the SQL statement to see if it conforms to SQL syntax and obtains a syntax tree (Lex). For SQL statements that are the same except for the parameters, their execution times are different but the hard parsing time is the same. As the query data changes, the execution time of multiple queries of the same SQL may be different, but the hard parsing time remains unchanged. The shorter the SQL execution time is, the higher the ratio of SQL hard parsing time to the total execution time is. For most of the transactional SQL statements of Taobao applications, queries will go through indexes and the execution time is relatively short. Therefore, Taobao's application database sql hard analysis accounts for a large proportion.

The emergence of Prepare is to optimize the problem of hard parsing. The execution process of Prepare on the server side is as follows

1) Prepare receives the SQL with "?" from the client, performs hard parsing to obtain the syntax tree (stmt->Lex), and caches it in the preparestatement cache where the thread is located. This cache is a HASH MAP. The key is stmt->id. Then it returns the client's stmt->id and other information.

2) Execute receives information such as stmt->id and parameters from the client. Note that the client does not need to send SQL here. The server searches the preparedstatement cache for the hard-parsed stmt according to stmt->id, sets the parameters, and then continues with subsequent optimization and execution.

Prepare can save hard parsing time in the execute phase. If the SQL statement is executed only once and in the prepare mode, then the SQL statement execution requires two interactions with the server (Prepare and execute), while in the normal (non-prepare) mode, only one interaction is required. Using prepare in this way will bring additional network overhead, which may not be worth the cost. Let's look at the case where the same SQL statement is executed multiple times, for example, 10 times in prepare mode, then only one hard parse is required. At this time, the additional network overhead is negligible. Therefore, prepare is suitable for frequently executed SQL.

Another function of Prepare is to prevent SQL injection, but this is achieved through escaping on the client side of JDBC and has nothing to do with the server.
Proportion of hard analysis

According to the results obtained through perf during stress testing, the proportion of hard parsing-related functions is relatively high (MYSQLparse 4.93%, lex_one_token 1.79%, lex_start 1.12%), totaling nearly 8%. Therefore, using prepare on the server can bring about significant performance improvements.

jdbc and prepare

Parameters of jdbc server:

useServerPrepStmts: Defaults to false. Whether to use the server prepare switch

jdbc client parameters:

cachePrepStmts: Default is false. Whether to cache prepareStatement objects. Each connection has a cache, which is an LRU cache uniquely identified by sql. Under the same connection, different stmts do not need to recreate prepareStatement objects.

prepStmtCacheSize: The number of prepareStatement objects in the LRU cache. It is generally set to the number of most commonly used SQL statements.

prepStmtCacheSqlLimit: The size of the prepareStatement object. Exceeding the size will not be cached.

Jdbc's processing of prepare:

Jdbc processing of prepare when useServerPrepStmts=true

1) Create a PreparedStatement object, send a COM_PREPARE command to the server, and send the SQL with a question mark. The server returns information such as jdbc stmt->id

2) Send the COM_EXECUTE command to the server and pass the parameter information.

Jdbc processing of prepare when useServerPrepStmts=false

1) Create a PreparedStatement object, which does not interact with the server.

2) Constructs the complete SQL statement based on the parameters and the PreparedStatement object, and sends a QUERY command to the server.

Let's look at the parameter cachePrepStmts. When useServerPrepStmts is true or false, PreparedStatement objects are cached. However, if useServerPrepStmts is true, the cached PreparedStatement object contains information such as the server's stmt->id. That is to say, if the PreparedStatement object is reused, the overhead of communicating with the server (COM_PREPARE command) is saved. And useServerPrepStmts=false means that turning on cachePrepStmts to cache PreparedStatement objects is just simple sql parsing information, so turning on cachePrepStmts at this time is not very meaningful.

Let's take a look at a piece of java code

Connection con = null;
      PreparedStatement ps = null;
      String sql = "select * from user where id=?";
      ps = con.prepareStatement(sql);      
      ps.setInt(1, 1);      
      ps.executeQuery();      
      ps.close();      
      ps = con.prepareStatement(sql);      
      ps.setInt(1, 3);      
      ps.executeQuery();      
      ps.close();

This code prepares and executes the same statement twice in the same session, with ps.close() in between.

When useServerPrepStmts=false, the server will hard parse the same SQL twice.

When useServerPrepStmts=true and cachePrepStmts=false, the server will still hard parse the same SQL twice.

When useServerPrepStmts=true and cachePrepStmts=true, the server will only hard parse the SQL once.

If there is no ps.close() between two prepares, then cachePrepStmts=true and cachePrepStmts=false only need one hard parse.

Therefore, if the client frequently allocates and releases PreparedStatement objects for the same SQL, it is necessary to enable the cachePrepStmts parameter.

test

1) A simple test was done to test the effect of prepare and the influence of the useServerPrepStmts parameter.

cnt = 5000;
    // no prepare
    String sql = "select biz_order_id,out_order_id,seller_nick,buyer_nick,seller_id,buyer_id,auction_id,auction_title,auction_price,buy_amount,biz_type,sub_biz_type,fail_reason,pay_status,logistics_status,out_trade_status,snap_path,gmt_create,status,ifnull(buyer_rate_status, 4) buyer_rate_status from tc_biz_order_0030 where " +
    "parent_id = 594314511722841 or parent_id =547667559932641;";
    begin = new Date();
    System.out.println("begin:" + df.format(begin));
    stmt = con.createStatement();
    for (int i = 0; i < cnt; i++)
    {      
      stmt.executeQuery(sql);
    } 
    end = new Date();
    System.out.println("end:" + df.format(end));
    long temp = end.getTime() - begin.getTime();
    System.out.println("no persist interval:" + temp);
    
    //test prepare    
    sql = "select biz_order_id,out_order_id,seller_nick,buyer_nick,seller_id,buyer_id,auction_id,auction_title,auction_price,buy_amount,biz_type,sub_biz_type,fail_reason,pay_status,logistics_status,out_trade_status,snap_path,gmt_create,status,ifnull(buyer_rate_status, 4) buyer_rate_status from tc_biz_order_0030 where " +
        "parent_id = 594314511722841 or parent_id =?;";
    ps = con.prepareStatement(sql);
    BigInteger param = new BigInteger("547667559932641");
    begin = new Date();
    System.out.println("begin:" + df.format(begin));
    for (int i = 0; i < cnt; i++)
    {
      ps.setObject(1, param);
      ps.executeQuery(); 
    } 
    end = new Date();
    System.out.println("end:" + df.format(end));
    temp = end.getTime() - begin.getTime();
    System.out.println("prepare interval:" + temp);

After multiple sampling tests, the results are as follows

in conclusion:

When useServerPrepStmts=true, prepare is increased by 7%;

When useServerPrepStmts=false, the performance of prepare is equivalent to that of non-prepare.

If the statement is simplified to select * from tc_biz_order_0030 where parent_id =?. The conclusion of the test is that when useServerPrepStmts=true, prepare is only improved by 2%; the simpler the SQL, the less time is spent on hard parsing, and the less improvement is made on prepare.

Note: This test is performed under the ideal condition of a single connection and a single SQL statement. There may be multiple connections and multiple SQL statements online, and the SQL execution frequency and SQL complexity may vary. Therefore, the improvement effect of prepare will vary with the specific environment.

2) Comparison of perf top before and after prepare

The following is non-prepare

6.46% mysqld mysqld[.]_Z10MYSQLparsePv
   3.74% mysqld libc-2.12.so[.]__memcpy_ssse3
   2.50% mysqld mysqld[.]my_hash_sort_utf8
   2.15% mysqld mysqld[.] cmp_dtuple_rec_with_match
   2.05% mysqld mysqld[.]_ZL13lex_one_tokenPvS_
   1.46% mysqld mysqld[.]buf_page_get_gen
   1.34% mysqld mysqld[.]page_cur_search_with_match
   1.31% mysqld mysqld[.]_ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj
   1.24% mysqld mysqld[.] rec_init_offsets
   1.11% mysqld libjemalloc.so.1[.]free
   1.09% mysqld mysqld[.] rec_get_offsets_func
   1.01% mysqld libjemalloc.so.1[.]malloc
   0.96% mysqld libc-2.12.so[.]__strlen_sse42
   0.93% mysqld mysqld[.]_ZN4JOIN8optimizeEv
   0.91% mysqld mysqld[.]_ZL15get_hash_symbolPKcjb
   0.88% mysqld mysqld[.] row_search_for_mysql
   0.86% mysqld [kernel.kallsyms] [k] tcp_recvmsg

The following is perpare

3.46% mysqld libc-2.12.so[.]__memcpy_ssse3
   2.32% mysqld mysqld[.] cmp_dtuple_rec_with_match
   2.14% mysqld mysqld[.]_ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj
   1.96% mysqld mysqld[.]buf_page_get_gen
   1.66% mysqld mysqld[.]page_cur_search_with_match
   1.54% mysqld mysqld[.] row_search_for_mysql
   1.44% mysqld mysqld[.]btr_cur_search_to_nth_level
   1.41% mysqld libjemalloc.so.1[.]free
   1.35% mysqld mysqld[.] rec_init_offsets
   1.32% mysqld [kernel.kallsyms] [k] kfree
   1.14% mysqld libjemalloc.so.1[.]malloc
   1.08% mysqld [kernel.kallsyms] [k] fget_light
   1.05% mysqld mysqld[.] rec_get_offsets_func
   0.99% mysqld mysqld[.]_ZN8Protocol24send_result_set_metadataEP4ListI4ItemEj
   0.90% mysqld mysqld[.]sync_array_print_long_waits
   0.87% mysqld mysqld[.]page_rec_get_n_recs_before
   0.81% mysqld mysqld[.]_ZN4JOIN8optimizeEv
   0.81% mysqld libc-2.12.so[.]__strlen_sse42
   0.78% mysqld mysqld[.]_ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
   0.72% mysqld [kernel.kallsyms] [k] tcp_recvmsg
   0.63% mysqld libpthread-2.12.so[.] __pthread_getspecific_internal
   0.63% mysqld [kernel.kallsyms] [k] sk_run_filter
   0.60% mysqld mysqld[.]_Z19find_field_in_tableP3THDP5TABLEPKcjbPj
   0.60% mysqld mysqld[.]page_check_dir
   0.57% mysqld mysqld[.]_Z16dispatch_command19enum_server_commandP3THDP

By comparison, we can find that MYSQLparse lex_one_token has been optimized during prepare.

think

1. Regarding the issue of enabling cachePrepStmts, we have mentioned before that each connection has a cache, which is an LRU cache uniquely identified by sql. In the case of many sub-tables and large connections, this may cause memory problems for the application server. The premise here is that ibatis uses prepare by default. In mybatis, the statementType tag can specify whether a certain SQL statement uses prepare.

statementType Any one of STATEMENT, PREPARED or CALLABLE. This causes MyBatis to use Statement, PreparedStatement orCallableStatement respectively. Default: PREPARED.

In this way, you can accurately control the use of prepare only for SQL with higher frequency, thereby controlling the number of prepare SQL used and reducing memory consumption. Unfortunately, most groups currently seem to use ibatis 2.0, which does not support statementType
Label.

2 The server-side prepare cache is a HASH MAP. The key is stmt->id, and each connection maintains one. Therefore, there may be memory problems, which need to be tested in practice. If necessary, it needs to be transformed into a global cache with the Key as sql, so that the same prepare sql of different connections can be shared.

3 The difference between oracle prepare and mysql prepare:

A major difference between MySQL and Oracle is that MySQL does not have an execution plan cache like Oracle. Earlier we mentioned that the SQL execution process includes the following stages: lexical analysis -> syntax analysis -> semantic analysis -> execution plan optimization -> execution. Oracle's prepare actually includes the following stages: lexical analysis -> syntax analysis -> semantic analysis -> execution plan optimization, which means that Oracle's prepare does more work, and execute only needs to execute. Therefore, Oracle's prepare is more efficient than MySQL.

Summarize

The above is all the content of this article about the detailed explanation of the MySQL prepare principle. Friends who are interested can refer to other related topics on this site. If you have any questions or need articles, books or source codes, you can leave a message at any time and the editor will be happy to answer you. Thank you for your support of this website.