mysql store insert_MySQL:关于RR模式下insert..selcet sending data状态说明

一、案例由来

最近看到有人在问如下：

image.png

其中的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状态

image.png

image.png

insert..selcet中insert记录如果处于堵塞(唯一性检查)状态会处于 sending data状态

image.png

image.png

整个过程如果需要操作的数据量较大，处于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: | | | | | | <:status_change>

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: | | | | | | | | | | |

941 T@4: | | | | | | | | | |

942 T@4: | | | | | | | | |

943 T@4: | | | | | | | | | >innobase_trx_init

944 T@4: | | | | | | | | |

945 T@4: | | | | | | | |

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: | | | | | | | | | | | | | |

954 T@4: | | | | | | | | | | | | | | >row_sel_store_mysql_field_func

955 T@4: | | | | | | | | | | | | | |

956 T@4: | | | | | | | | | | | | |

957 T@4: | | | | | | | | | | | |

958 T@4: | | | | | | | | | | |

959 T@4: | | | | | | | | | |

960 T@4: | | | | | | | | |

961 T@4: | | | | | | | | <:ha_rnd_next>

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: | | | | | | | | | | | | <:save_in_field_inner>

970 T@4: | | | | | | | | | | | | >Item_field::save_in_field_inner

971 T@4: | | | | | | | | | | | | <:save_in_field_inner>

972 T@4: | | | | | | | | | | |

973 T@4: | | | | | | | | | | | >write_record

974 T@4: | | | | | | | | | | | | >init_alloc_root

975 T@4: | | | | | | | | | | | | | enter: root: 0x7fffe8e48c20

976 T@4: | | | | | | | | | | | |

977 T@4: | | | | | | | | | | | | >COPY_INFO::set_function_defaults

978 T@4: | | | | | | | | | | | | <:set_function_defaults>

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: | | | | | | | | | | | | | | | | | |

988 T@4: | | | | | | | | | | | | | | | | | | >thd_report_row_lock_wait

989 T@4: | | | | | | | | | | | | | | | | | |

990 T@4: | | | | | | | | | | | | | | | | |

991 T@4: | | | | | | | | | | | | | | | |

992 T@4: | | | | | | | | | | | | | | |

993 T@4: | | | | | | | | | | | | | |

994 T@4: | | | | | | | | | | | | | | >thd_mark_transaction_to_rollback

995 T@4: | | | | | | | | | | | | | |

996 T@4: | | | | | | | | | | | | | <:write_row>

997 T@4: | | | | | | | | | | | | <:ha_write_row>

及RR模式下insert select的逻辑大概为查询一行加锁(RC下没有加锁步骤)一行插入一行，直到所有行处理完成。整个过程处于'Sending data'状态下面。因此insert select和普通的insert操作有较大的区别。

关于sending data扩展阅读，参考我的一篇文章：