MySQL의 준비 원리에 대한 자세한 설명

이 기사에서는 준비 생성, 서버 측 실행 프로세스, JDBC의 준비 처리 및 관련 테스트를 포함하여 MySQL 준비 관련 내용을 주로 소개합니다. 그것이 모두에게 도움이 되기를 바랍니다.

Prepare의 장점

Prepare SQL이 생성되는 이유. mysql 서버에서 sql을 실행하는 과정부터 살펴보자. SQL 실행 과정은 어휘분석->구문분석->의미분석->실행계획최적화->실행의 단계로 이루어진다. 어휘 분석 -> 문법 분석 이 두 단계를 하드 파싱이라고 합니다. 어휘 분석은 SQL의 각 단어를 식별하고, 구문 분석은 SQL 문이 SQL 문법을 준수하는지 분석하여 구문 트리(Lex)를 얻습니다. 매개 변수는 다르지만 다른 항목은 동일한 SQL의 경우 실행 시간은 다르지만 하드 구문 분석 시간은 동일합니다. 동일한 SQL에 대한 쿼리 데이터가 변경됨에 따라 여러 쿼리의 실행 시간은 다를 수 있지만 하드 파싱 시간은 변경되지 않습니다. SQL 실행 시간이 짧을수록 전체 실행 시간에 대한 SQL 하드 구문 분석 시간의 비율이 높아집니다. Taobao의 대부분의 트랜잭션 SQL 애플리케이션의 경우 쿼리는 인덱스를 통과하며 실행 시간은 상대적으로 짧습니다. 따라서 Taobao는 db sql 하드 구문 분석을 사용하여 많은 부분을 차지합니다.

prepare의 등장은 하드 파싱 문제를 최적화하기 위한 것입니다. 서버 측에서 prepare 실행 과정은 다음과 같습니다

1) prepare는 클라이언트로부터 "?"가 포함된 SQL을 수신하고 하드 파싱을 수행하여 구문 트리(stmt->Lex)를 얻은 후 캐시에 저장합니다. 스레드가 있는 preparestatement 캐시입니다. 이 캐시는 HASH MAP입니다. 키는 stmt->id입니다. 그런 다음 stmt->id와 같은 정보가 클라이언트에 반환됩니다.

2) Execute는 클라이언트 stmt->id, 매개변수 등의 정보를 받습니다. 클라이언트는 여기에서 SQL을 보낼 필요가 없습니다. 서버는 stmt->id를 기반으로 하드 구문 분석된 stmt에 대한 preparestatement 캐시를 검색하고 매개변수를 설정한 다음 후속 최적화 및 실행을 계속할 수 있습니다.

준비는 실행 단계에서 어려운 구문 분석 시간을 절약할 수 있습니다. SQL이 한 번만 실행되고 준비 모드에서 실행되는 경우 SQL 실행에는 서버와의 두 가지 상호 작용(준비 및 실행)이 필요한 반면 일반(비준비) 모드에서는 한 번의 상호 작용만 필요합니다. 이러한 방식으로 준비를 사용하면 추가적인 네트워크 오버헤드가 발생하므로 얻을 가치가 없을 수도 있습니다. 동일한 SQL이 여러 번 실행되는 상황을 살펴보겠습니다. 예를 들어 준비 모드에서 10번 실행되면 하드 구문 분석은 한 번만 필요합니다. 이때 추가 네트워크 오버헤드는 무시할 수 있습니다. 따라서 prepare는 자주 실행되는 SQL에 적합합니다.

prepare의 또 다른 기능은 SQL 주입을 방지하는 것인데 이는 클라이언트 측 jdbc에서 이스케이프를 통해 달성되며 서버와는 아무런 관련이 없습니다.
하드 파싱 비율

스트레스 테스트 중 perf를 통해 얻은 결과에 따르면 하드 파싱과 관련된 기능의 비율이 상대적으로 높아(MYSQLparse 4.93%, lex_one_token 1.79%, lex_start 1.12%) 총 8에 가깝습니다. %. 따라서 서버에서 prepare를 사용하면 성능이 더 향상될 수 있습니다.

jdbc 및 준비

jdbc 서버측 매개변수:

useServerPrepStmts: 기본값은 서버 준비 스위치 사용 여부

jdbc 클라이언트측 매개변수:

캐시PrepStmts: 기본값은 false입니다. prepareStatement 객체를 캐시할지 여부입니다. 각 연결에는 SQL로 고유하게 식별되는 LRU 캐시인 캐시가 있습니다. 동일한 연결에서 다른 stmt는 prepareStatement 개체를 다시 만들 필요가 없습니다.

prepStmtCacheSize: LRU 캐시에 있는 prepareStatement 개체의 수입니다. 일반적으로 가장 많이 사용되는 SQL의 번호로 설정됩니다.

prepStmtCacheSqlLimit: prepareStatement 개체의 크기입니다. 초과된 크기는 캐시되지 않습니다.

Jdbc의 prepare 처리:

useServerPrepStmts=true Jdbc의 prepare 처리

1) preparedStatement 객체를 생성하고 COM_PREPARE 명령을 서버로 보내고, 물음표와 함께 sql을 전송하면 서버가 jdbc를 반환합니다. st mt->ID 및 기타 정보

2) COM_EXECUTE 명령을 서버로 보내고 매개변수 정보를 전송합니다.

useServerPrepStmts=false일 때 Jdbc의 준비 처리

1) ReadyStatement 객체를 생성하고 현재는 서버와 상호 작용하지 않습니다.

2) 매개변수와 preparedStatement 객체를 기반으로 전체 SQL을 연결하고 QUERY 명령을 서버로 보냅니다.

매개변수 캐시PrepStmts가 켜졌을 때와 useServerPrepStmts가 true 또는 false일 때 preparedStatement를 살펴보겠습니다. 개체가 캐시됩니다. 그러나 useServerPrepStmts가 true인 경우 캐시된 preparedStatement 개체에는 서버의 stmt->id 및 기타 정보가 포함됩니다. 즉, ReadyStatement 개체를 재사용하면 서버와 통신(COM_PREPARE 명령)하는 오버헤드가 제거됩니다. 그리고 useServerPrepStmts=false는 캐시PrepStmts를 켜서 preparedStatement 객체를 캐시하는 것은 단지 단순한 SQL 구문 분석 정보일 뿐이라는 뜻이므로, 지금은 캐시PrepStmts를 켜는 것이 별 의미가 없습니다.

Java 코드를 살펴보겠습니다

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

이 코드는 동일한 세션에서 동일한 명령문을 두 번 준비하고 실행하며, 그 사이에 ps.close();

UseServerPrepStmts=false가 있으면 서버는 동일한 SQL을 두 번 하드 구문 분석합니다.

useServerPrepStmts=true 및 캐시PrepStmts=false인 경우에도 서버는 동일한 SQL을 두 번 하드 구문 분석합니다.

useServerPrepStmts=true, 캐시PrepStmts=true인 경우 서버는 SQL을 한 번만 하드 구문 분석합니다.

두 개의 준비 사이에 ps.close();가 없는 경우 캐시PrepStmts=true 및 캐시PrepStmts=false는 하나의 하드 구문 분석만 필요합니다.

따라서 클라이언트는 동일한 SQL에 대해 ReadyStatement 개체를 자주 할당하고 해제합니다. 캐시PrepStmts 매개변수를 활성화해야 합니다.

Test

1) 주로 준비 효과와 useServerPrepStmts 매개변수의 영향을 테스트하는 간단한 테스트를 만들었습니다.

준비 시간과 준비 시간 비율

useServerPrepStmts=true0.931.01 컨택 : 문이 단순화된 경우 parent_id =?인 tc_biz_order_0030에서 *를 선택합니다. 그런 다음 테스트 결론은 useServerPrepStmts=true일 때 준비가 2%만 향상된다는 것입니다. SQL이 단순할수록 하드 구문 분석에 걸리는 시간이 줄어들고 준비가 덜 향상됩니다. 2) 준비 전과 후의 성능 비교

huseserverprepStmts = false
useserververprepstmts = true, 7%증가합니다.
참고: 이 테스트는 단일 연결 및 단일 sql이라는 이상적인 조건에서 수행되었으며, sql 실행 빈도, sql 복잡성 등도 다중 연결 및 다중 sql이 있을 것이므로 개선 효과가 있습니다. 구체적인 상황에 따라 준비가 달라집니다.

다음은 준비되지 않은 것

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

다음은 perpare

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

비교는 MYSQLparse lex_ one_token은 사전에 있습니다 파레 중에 최적화되었습니다. 1 , 응용 프로그램 서버가 메모리 문제를 일으킬 수 있습니다. 여기서 전제는 ibatis가 기본적으로 prepare를 사용한다는 것입니다. mybatis에서 labelmentType은 특정 SQL이 prepare.

statementType을 사용하는지 여부를 지정할 수 있습니다. 이는 STATEMENT, PREPARED 또는 CALLABLE 중 하나입니다. 이로 인해 MyBatis는 각각 Statement, PREPARED 또는 CallableStatement를 사용하게 됩니다.
이것은 정확할 수 있습니다. 빈도가 높은 SQL에 대해서만 준비 사용을 제어하여 준비된 SQL 수를 제어하고 메모리 소비를 줄입니다. 아쉽게도 현재 대부분의 그룹이 ibatis 버전 2.0을 사용하고 있는 것 같습니다. 이 버전은statementType

태그를 지원하지 않습니다.

2 서버 측 준비 캐시는 HASH MAP이며 키는 stmt->id이며 연결마다 하나씩 유지됩니다. 따라서 실제 테스트를 기다리는 동안 메모리 문제가 발생할 수도 있습니다. 필요한 경우 sql 키를 사용하여 글로벌 캐시로 변환해야 서로 다른 연결의 동일한 준비 SQL을 공유할 수 있습니다.

3 oracle prepare와 mysql prepare의 차이점:

mysql과 oracle의 주요 차이점은 mysql에는 oracle과 같은 실행 계획 캐시가 없다는 것입니다. 앞서 SQL 실행 프로세스에는 어휘 분석->구문 분석->의미 분석->실행 계획 최적화->실행의 단계가 포함된다고 언급했습니다. Oracle의 준비에는 실제로 다음 단계가 포함됩니다. 어휘 분석 -> 구문 분석 -> 의미 분석 -> 실행 계획 최적화. 이는 Oracle의 준비가 더 많은 작업을 수행하고 실행만 실행하면 된다는 의미입니다. 따라서 Oracle의 준비는 MySQL보다 더 효율적입니다.

요약

위 내용은 MySQL의 준비 원리에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!