Detailed explanation of prepare principle in MySQL

This article mainly introduces the related content of MySQL prepare, including the generation of prepare, the execution process on the server side, as well as jdbc's processing of prepare and related tests. Friends who need it can learn more. I hope to be helpful.

The benefits of Prepare

The reason why Prepare SQL is generated. 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. Lexical analysis -> Grammatical analysis These two stages are called hard parsing. Lexical analysis identifies each word in SQL, and syntactic analysis analyzes whether the SQL statement conforms to SQL grammar and obtains a syntax tree (Lex). For sql with different parameters but the same others, their execution time is different but the hard parsing time is the same. As the query data changes for the same SQL, the execution time of multiple queries may be different, but the time of hard parsing remains unchanged. For the shorter SQL execution time, the higher the ratio of SQL hard parsing time to the total execution time. For most transactional SQL applications on Taobao, queries will go through the index, and the execution time is relatively short. Therefore, Taobao uses db sql hard parsing to account 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, hard parses it to get 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 information such as stmt->id is returned to the client.

2) Execute receives information such as client stmt->id and parameters. Note that the client does not need to send sql here. The server searches the preparestatement cache for the hard-parsed stmt based on the stmt->id, and sets the parameters, then it can continue with subsequent optimization and execution.

Prepare can save hard parsing time during the execute phase. If sql is only executed once and is executed in prepare mode, then sql execution requires two interactions with the server (Prepare and execute), while in normal (non-prepare) mode, only one interaction is required. Using prepare in this way brings additional network overhead, which may outweigh the benefits. Let's look at the situation where the same SQL is executed multiple times. For example, if it is executed 10 times in prepare mode, then only one hard parse is required. At this time, the additional network overhead becomes negligible. Therefore prepare is suitable for frequently executed SQL.

Another function of Prepare is to prevent sql injection, but this is achieved through escaping in jdbc on the client side and has nothing to do with the server.
The proportion of hard parsing

According to the results obtained through perf during the stress test, the proportions of functions related to hard parsing are relatively high (MYSQLparse 4.93%, lex_one_token 1.79%, lex_start 1.12%), which is close to the total. 8%. Therefore, using prepare on the server can bring more performance improvements.

jdbc and prepare

jdbc server-side parameters:

useServerPrepStmts: The default is false. Whether to use the server prepare switch

jdbc client parameters:

cachePrepStmts: Default 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 the prepareStatement object.

prepStmtCacheSize: The number of prepareStatement objects in the LRU cache. Generally set to the number of the most commonly used sql.

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

Jdbc’s processing of prepare:

Jdbc’s processing of prepare when useServerPrepStmts=true

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

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

Jdbc’s handling of prepare when useServerPrepStmts=false

1) Create a PreparedStatement object and will not interact with the server at this time.

2) Splice the complete SQL according to the parameters and PreparedStatement object, and send the QUERY command to the server

Let’s look at the parameter cachePrepStmts when it is turned on and useServerPrepStmts is true or false. PreparedStatement objects are cached. It's just that useServerPrepStmts is true and the cached PreparedStatement object contains the server's stmt->id and other information. That is to say, if the PreparedStatement object is reused, the overhead of communicating with the server (COM_PREPARE command) is eliminated. 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 does not make much sense.

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, and there is ps.close();

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, cachePrepStmts=true, the server will only hard parse SQL once.

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

Therefore, the client For the same SQL, when frequently allocating and releasing PreparedStatement objects, it is necessary to enable the cachePrepStmts parameter.

Test

1) Made a simple test, mainly testing the effect of prepare and the influence of useServerPrepStmts parameters.​

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 perpare 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);

The results of multiple sampling tests are as follows

##Non-prepare and prepare time ratiouseServerPrepStmts=true0.93useServerPrepStmts=false1.01Conclusion:

When useServerPrepStmts=true, prepare increases by 7%;

When useServerPrepStmts=false, prepare and non-prepare performance are equivalent.

If the statement is simplified to select * from tc_biz_order_0030 where parent_id =?. Then the test conclusion is that when useServerPrepStmts=true, prepare only improves by 2%; the simpler the SQL, the less time it takes to hard parse, and the less the prepare improves.

Note: This test is conducted under the ideal conditions of a single connection and a single SQL. There will be multiple connections and multiple SQLs online, as well as the frequency of SQL execution and the complexity of the SQL. Therefore, The improvement effect of prepare will vary depending on 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

Comparison shows that MYSQLparse lex_one_token has been optimized during prepare.

Thinking

1 The problem of turning on cachePrepStmts. As mentioned earlier, each connection has a cache, which is an LRU with sql as the unique identifier. cache. When there are many sub-tables and large connections, it may cause memory problems for the application server. The premise here is that ibatis uses prepare by default. In mybatis, the label statementType can specify whether a certain sql uses prepare.

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

This can accurately control the use of prepare only for higher frequency sql, thereby controlling the number of prepared sql and reducing memory consumption. Unfortunately, it seems that most of the groups currently use ibatis version 2.0, which does not support the statementType

tag.

2 The server-side prepare cache is a HASH MAP. The key is stmt->id, and one is maintained for each connection. Therefore, memory problems may also occur, pending actual testing. If necessary, it needs to be transformed into a global cache with the key 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 things, and execute only needs to be executed. Therefore, Oracle's prepare is more efficient than MySQL.

Summarize

The above is the detailed content of Detailed explanation of prepare principle in MySQL. For more information, please follow other related articles on the PHP Chinese website!