MySQL：一个简单insert语句的大概流程

简单记录，可能有误，主要记录重要的接口以备后用。

一、操作说明

我建了一个简单的表，插入一个简单的数据。

mysql> create table testin(id int);Query OK, 0 rows affected (2.38 sec) mysql> insert into testin values(10);Query OK, 1 row affected (0.02 sec) 

主要跟踪这个简单的插入语句在插入过程的经历。主要集中在插入流程和提交流程，不包含前期的其他阶段。
下面是这个语句经历的所有的阶段：

 126 T@2: | THD::enter_stage: 'starting' /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/socket_connection.cc:100   349  T@2: | | | | | | THD::enter_stage: 'checking permissions' /root/mysql5.7.14/percona-server-5.7.14-7/sql/auth/sql_authorization.cc:843   359  T@2: | | | | | | | THD::enter_stage: 'Opening tables' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5719 1078 T@2: | | | | | THD::enter_stage: 'init' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:470  1155  T@2: | | | | | | | THD::enter_stage: 'System lock' /root/mysql5.7.14/percona-server-5.7.14-7/sql/lock.cc:321 1253 T@2: | | | | | THD::enter_stage: 'update' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:663  1535  T@2: | | | | | THD::enter_stage: 'end' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:881 1544 T@2: | | | | THD::enter_stage: 'query end' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5174 1603 T@2: | | | | THD::enter_stage: 'closing tables' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5252 1730 T@2: | | | THD::enter_stage: 'freeing items' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5855  1793  T@2: | | THD::enter_stage: 'cleaning up' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1884  1824  T@2: | THD::enter_stage: 'starting' /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/socket_connection.cc:100 

主要集中在：

• update
• query end

两个阶段

二、大概流程

Sql_cmd_insert::mysql_insert >Sql_cmd_insert::mysql_insert    >切换session状态为 update    >进入插入逻辑    >handler::ha_write_row     >ha_innobase::write_row      >row_insert_for_mysql            >row_insert_for_mysql_using_ins_graph              >trx_start_if_not_started_xa_low                >trx_start_low                                       激活事物，事物状态由 not_active 变为 active             >row_ins_step               >row_ins                >row_ins_index_entry_step                 >row_ins_index_entry                  >row_ins_clust_index_entry                            >row_ins_clust_index_entry_low                               >btr_cur_search_to_nth_level                   查找定位数据                               >btr_cur_optimistic_insert                    进行乐观插入                                 >btr_cur_ins_lock_and_undo                                   >trx_undo_report_row_operation                                     >trx_undo_page_report_insert               记录insert的undo记录                                     >trx_undo_page_set_next_prev_and_add                                      >trx_undof_page_add_undo_rec_log         记录undo的redo log 入redo buffer                                 >page_cur_tuple_insert                      进行insert 元组插入，及实际的插入操作                                  >page_cur_insert_rec_write_log             记录插入的redo log 入redo buffer                         >binlog_log_row            >write_locked_table_maps          >THD::binlog_write_table_map          >binlog_start_trans_and_stmt           >binlog_cache_data::write_event                        binlog event 写入到 binlog cache 

进入提交逻辑  mysql_execute_command >切换session状态为 query end >trans_commit_stmt  >ha_commit_trans   >MYSQL_BIN_LOG::prepare    >ha_prepare_low       >binlog_prepare                                         生成last_commit     >innobase_xa_prepare      >trx_prepare_for_mysql       >trx_prepare                                            转换事物状态为，事物状态由 active 变为 prepare   >MYSQL_BIN_LOG::commit    >MYSQL_BIN_LOG::ordered_commit     >MYSQL_BIN_LOG::process_flush_stage_queue      >ha_flush_logs       >plugin_foreach_with_mask        >flush_handlerton         >innobase_flush_logs          >log_buffer_flush_to_disk           >log_write_up_to            >log_group_write_buf                               innodb 组提交，确保redo落盘      >MYSQL_BIN_LOG::flush_thread_caches       >binlog_cache_mngr::flush        >binlog_cache_data::flush                              binlog cache 进行flush到binlog文件      >MYSQL_BIN_LOG::sync_binlog_file                         fsync binlog文件进行os缓存落盘      >MYSQL_BIN_LOG::process_commit_stage_queue       >ha_commit_low        >innobase_commit         >innobase_commit_low                                             >trx_commit_in_memory                                innodb 进行提交,事物状态由 prepare 变为 not_active 

可以看到整个语句的流程大概为

1. 会话状态转换为update

2. 激活事物状态由 not_active 变为 active

3. 查找定位数据

4. 进行乐观插入

   • 记录insert的undo记录
   • 记录undo的redo log 入redo buffer
   • 进行insert 元组插入，及实际的插入操作
   • 记录插入的redo log 入redo buffer

5. binlog event 写入到 binlog cache

6. 会话状态转换为query end

7. 进入提交准备

   • binlog准备
   • innodb层事物准备，状态由 active变为 prepare

8. 进入提交阶段

• innodb进行组提交，确保redo落盘
• binlog cache 进行flush到binlog文件
• fsync binlog文件进行os缓存落盘
• innodb 进行提交,事物状态由 prepare 变为 not_active

这只是大概流程其中很多很多的细节，不过有了入口函数也许好分析一些。

三、备用栈帧

Num     Type           Disp Enb Address            What 1 breakpoint     keep y 0x0000000000ebd5f3 in main(int, char**) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/main.cc:25 breakpoint already hit 1 time 5 breakpoint     keep y 0x0000000001a90776 in page_cur_insert_rec_write_log(rec_t*, ulint, rec_t*, dict_index_t*, mtr_t*)                                              at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/page/page0cur.cc:964 breakpoint already hit 7 times 8 breakpoint     keep y 0x0000000001bc8f96 in trx_undo_page_report_insert(ib_page_t*, trx_t*, dict_index_t*, dtuple_t const*, mtr_t*)                                              at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0rec.cc:478 breakpoint already hit 5 times 9 breakpoint     keep y 0x0000000001bc84c4 in trx_undof_page_add_undo_rec_log(ib_page_t*, ulint, ulint, mtr_t*)                                              at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0rec.cc:67 breakpoint already hit 20 times 10 breakpoint     keep y 0x00000000019a932d in innobase_start_trx_and_assign_read_view(handlerton*, THD*)                                              at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4499 11 breakpoint     keep y 0x0000000001bddbfc in trx_start_low(trx_t*, bool) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:1380 breakpoint already hit 3 times 12 breakpoint     keep y 0x0000000001c1e9eb in btr_cur_search_to_nth_level(dict_index_t*, ulint, dtuple_t const*, page_cur_mode_t, ulint, btr_cur_t*, ulint, char const*, ulint, mtr_t*) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/btr/btr0cur.cc:770 breakpoint already hit 13 times 13 breakpoint     keep y 0x0000000001859c85 in binlog_start_trans_and_stmt(THD*, Log_event*) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9737 breakpoint already hit 1 time 14 breakpoint     keep y 0x0000000001845822 in binlog_cache_data::write_event(THD*, Log_event*) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:1114 breakpoint already hit 3 times 15 breakpoint     keep y 0x000000000153a2a3 in THD::enter_stage(PSI_stage_info const*, PSI_stage_info*, char const*, char const*, unsigned int)                                              at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_class.cc:732 breakpoint already hit 8 times 17 breakpoint     keep y 0x0000000001be195a in trx_prepare(trx_t*) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:2947 breakpoint already hit 3 times 19 breakpoint     keep y 0x0000000000f63801 in ha_commit_trans(THD*, bool, bool) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1684 breakpoint already hit 21 times 21 breakpoint     keep y 0x0000000001846901 in binlog_prepare(handlerton*, THD*, bool) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:1578 breakpoint already hit 3 times 24 breakpoint     keep y 0x00000000019c2c64 in innobase_xa_prepare(handlerton*, THD*, bool)                                              at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:17458 breakpoint already hit 2 times 25 breakpoint     keep y 0x00000000019a9788 in innobase_commit(handlerton*, THD*, bool)                                              at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4652 breakpoint already hit 2 times 26 breakpoint     keep y 0x0000000001846442 in binlog_cache_data::flush(THD*, my_off_t*, bool*) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:1408 breakpoint already hit 2 times 27 breakpoint     keep y 0x0000000001857c19 in MYSQL_BIN_LOG::sync_binlog_file(bool) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8802 breakpoint already hit 1 time 28 breakpoint     keep y 0x0000000001bdf2f5 in trx_commit_in_memory(trx_t*, mtr_t const*, bool)                                              at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:1973 

作者微信：