MySQL:关于RR模式下insert..select sending data状态说明

爱被打了一巴掌 2023-02-16 12:20 54阅读 0赞

作者：高鹏（网名八怪），《深入理解MySQL主从原理32讲》系列的作者。

系列链接：https://www.jianshu.com/nb/43148932

一、案例由来

最近看到有人在问如下：

其中的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/

全文完。

--------------------

*  由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。
    
    ![format_png][]
    
    --------------------
    
    另外，叶老师在腾讯课堂《**MySQL性能优化**》精编版第一期已完结，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。
    
    下面是自动拼团的二维码直接享受组团价
    
    ![format_png 1][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeEx5ajlRdWJEY0hQeUlwemgwaWNXSVpnTm96aFo3TmxDbW53MkU1ekpqUzZTTjE0TENaR0JvUW04WGliVHkzZDFtaWFqYWlhQVhTcTFTVVEvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9udHM1Mm5IaGVUek5xdmliY3IwZmJDQkU3MXBoMzF5aWJ5TzNFOVJONnlLd0NYd1R6cVZmc1ZxM2RxUUFpYWRoUDlGSkVXOUluNlk0Mnl4ZTVUUWVuMm9BUS82NDA?x-oss-process=image/format,png