MySQL 一個簡單insert語句的大概流程

2024-07-24 12:32:09

字體：大中小

來源：轉載

供稿：網友

簡單記錄，可能有誤，主要記錄重要的接口以備后用。

一、操作說明
我建了一個簡單的表，插入一個簡單的數據。

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
兩個階段

二、大概流程
1、樂觀插入的流程
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
2、其提交流程
進入提交邏輯
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
可以看到整個語句的流程大概為

會話狀態轉換為update
激活事物狀態由 not_active 變為 active
查找定位數據
進行樂觀插入
記錄insert的undo記錄
記錄undo的redo log 入redo buffer
進行insert 元組插入，及實際的插入操作
記錄插入的redo log 入redo buffer
binlog event 寫入到 binlog cache
會話狀態轉換為query end
進入提交準備
binlog準備
innodb層事物準備，狀態由 active變為 prepare
進入提交階段
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

（編輯：武林網）

上一篇：MySQL 數據庫和表操作

下一篇：MySQL 8.0新特性-不可見索引