MySQL:關於RR模式下insert..selcet sending data狀態說明

一、案例由來

最近看到有人在問如下：

其中的sending data是什麼意思。隔離級別為RR，語句為insert..select。

二、關於sending data

以前就說過這個問題，實際上sending data可能包含如下：

• Innodb 層資料的定位返回給MySQL 層
• Innodb 層資料的查詢返回給MySQL 層
• Innodb 層資料的修改(如果是insert..select)
• Innodb 層加鎖以及等待
• 等待進入Innodb層(innodb_thread_concurrency引數)
• MySQL 層傳送資料給客戶端

三、RR模式下對於insert..selcet 處於 sending data的原因總結

• RR模式下insert..select的select表會上S行鎖，如果這行處於X鎖則會出現 sending data狀態
  
  

• insert..selcet中insert記錄如果處於堵塞（唯一性檢查）狀態會處於 sending data狀態
  

• 整個過程如果需要操作的資料量較大，處於sending data狀態。

四、每行資料處理方式

    929 T@4: | | | | | | THD::enter_stage: 'Sending data' /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_executor.cc:202
    930 T@4: | | | | | | >PROFILING::status_change
    931 T@4: | | | | | | <PROFILING::status_change 391
    932 T@4: | | | | | | info: Sending data
    933 T@4: | | | | | | >do_select
    934 T@4: | | | | | | | >sub_select
    935 T@4: | | | | | | | | >init_read_record
    936 T@4: | | | | | | | | | info: using rr_sequential
    937 T@4: | | | | | | | | | >ha_rnd_init
    938 T@4: | | | | | | | | | | >change_active_index
    939 T@4: | | | | | | | | | | | >innobase_get_index
    940 T@4: | | | | | | | | | | | <innobase_get_index 10117
    941 T@4: | | | | | | | | | | <change_active_index 10241
    942 T@4: | | | | | | | | | <ha_rnd_init 3111
    943 T@4: | | | | | | | | | >innobase_trx_init
    944 T@4: | | | | | | | | | <innobase_trx_init 3109
    945 T@4: | | | | | | | | <init_read_record 349
    946 T@4: | | | | | | | | >handler::ha_rnd_next
    947 T@4: | | | | | | | | | >rnd_next
    948 T@4: | | | | | | | | | | >index_first
    949 T@4: | | | | | | | | | | | >index_read
    950 T@4: | | | | | | | | | | | | >row_search_mvcc
    951 T@4: | | | | | | | | | | | | | >row_sel_store_mysql_rec
    952 T@4: | | | | | | | | | | | | | | >row_sel_store_mysql_field_func
    953 T@4: | | | | | | | | | | | | | | <row_sel_store_mysql_field_func 3275
    954 T@4: | | | | | | | | | | | | | | >row_sel_store_mysql_field_func
    955 T@4: | | | | | | | | | | | | | | <row_sel_store_mysql_field_func 3275
    956 T@4: | | | | | | | | | | | | | <row_sel_store_mysql_rec 3465
    957 T@4: | | | | | | | | | | | | <row_search_mvcc 6574
    958 T@4: | | | | | | | | | | | <index_read 10042
    959 T@4: | | | | | | | | | | <index_first 10430
    960 T@4: | | | | | | | | | <rnd_next 10531
    961 T@4: | | | | | | | | <handler::ha_rnd_next 3172
    962 T@4: | | | | | | | | >evaluate_join_record
    963 T@4: | | | | | | | | | enter: join: 0x7ffef8019970 join_tab index: 0 table: testlock cond: 0x0
    964 T@4: | | | | | | | | | counts: evaluate_join_record join->examined_rows++: 1
    965 T@4: | | | | | | | | | >end_send
    966 T@4: | | | | | | | | | | >Query_result_insert::send_data
    967 T@4: | | | | | | | | | | | >fill_record
    968 T@4: | | | | | | | | | | | | >Item_field::save_in_field_inner
    969 T@4: | | | | | | | | | | | | <Item_field::save_in_field_inner 6720
    970 T@4: | | | | | | | | | | | | >Item_field::save_in_field_inner
    971 T@4: | | | | | | | | | | | | <Item_field::save_in_field_inner 6720
    972 T@4: | | | | | | | | | | | <fill_record 9801
    973 T@4: | | | | | | | | | | | >write_record
    974 T@4: | | | | | | | | | | | | >init_alloc_root
    975 T@4: | | | | | | | | | | | | | enter: root: 0x7fffe8e48c20
    976 T@4: | | | | | | | | | | | | <init_alloc_root 100
    977 T@4: | | | | | | | | | | | | >COPY_INFO::set_function_defaults
    978 T@4: | | | | | | | | | | | | <COPY_INFO::set_function_defaults 135
    979 T@4: | | | | | | | | | | | | >handler::ha_write_row
    980 T@4: | | | | | | | | | | | | | >ha_innobase::write_row
    981 T@4: | | | | | | | | | | | | | | >row_ins
    982 T@4: | | | | | | | | | | | | | | | row_ins: table: test/testbb
    983 T@4: | | | | | | | | | | | | | | | >row_ins_index_entry_step
    984 T@4: | | | | | | | | | | | | | | | | >row_ins_clust_index_entry
    985 T@4: | | | | | | | | | | | | | | | | | >row_ins_clust_index_entry_low
    986 T@4: | | | | | | | | | | | | | | | | | | >btr_cur_search_to_nth_level
    987 T@4: | | | | | | | | | | | | | | | | | | <btr_cur_search_to_nth_level 2092
    988 T@4: | | | | | | | | | | | | | | | | | | >thd_report_row_lock_wait
    989 T@4: | | | | | | | | | | | | | | | | | | <thd_report_row_lock_wait 4280
    990 T@4: | | | | | | | | | | | | | | | | | <row_ins_clust_index_entry_low 2692
    991 T@4: | | | | | | | | | | | | | | | | <row_ins_clust_index_entry 3337
    992 T@4: | | | | | | | | | | | | | | | <row_ins_index_entry_step 3619
    993 T@4: | | | | | | | | | | | | | | <row_ins 3763
    994 T@4: | | | | | | | | | | | | | | >thd_mark_transaction_to_rollback
    995 T@4: | | | | | | | | | | | | | | <thd_mark_transaction_to_rollback 4147
    996 T@4: | | | | | | | | | | | | | <ha_innobase::write_row 8895
    997 T@4: | | | | | | | | | | | | <handler::ha_write_row 8565

及RR模式下insert select的邏輯大概為查詢一行加鎖（RC下沒有加鎖步驟）一行插入一行，直到所有行處理完成。整個過程處於’Sending data’狀態下面。因此insert select和普通的insert操作有較大的區別。

關於sending data擴充套件閱讀，參考我的一篇文章：

• MySQL：sending data狀態包含了什麼
  http://blog.itpub.net/7728585/viewspace-2215202/