MySQL：一個(gè)簡(jiǎn)單insert語(yǔ)句的大概流程

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

10年積累的成都做網(wǎng)站、成都網(wǎng)站建設(shè)經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問(wèn)題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先網(wǎng)站制作后付款的網(wǎng)站建設(shè)流程，更有樂(lè)都免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

一、操作說(shuō)明

我建了一個(gè)簡(jiǎn)單的表，插入一個(gè)簡(jiǎn)單的數(shù)據(jù)。

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)

主要跟蹤這個(gè)簡(jiǎn)單的插入語(yǔ)句在插入過(guò)程的經(jīng)歷。主要集中在插入流程和提交流程，不包含前期的其他階段。
下面是這個(gè)語(yǔ)句經(jīng)歷的所有的階段：

 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

兩個(gè)階段

二、大概流程

1、樂(lè)觀插入的流程 
Sql_cmd_insert::mysql_insert
 >Sql_cmd_insert::mysql_insert
    >切換session狀態(tài)為 update
    >進(jìn)入插入邏輯
    >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                                       激活事物，事物狀態(tài)由 not_active 變?yōu)?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                   查找定位數(shù)據(jù)
                               >btr_cur_optimistic_insert                    進(jìn)行樂(lè)觀插入
                                 >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                      進(jìn)行insert 元組插入，及實(shí)際的插入操作
                                  >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、其提交流程 
進(jìn)入提交邏輯  
mysql_execute_command
 >切換session狀態(tài)為 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                                            轉(zhuǎn)換事物狀態(tài)為，事物狀態(tài)由 active 變?yōu)?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 進(jìn)行flush到binlog文件
      >MYSQL_BIN_LOG::sync_binlog_file                         fsync binlog文件進(jìn)行os緩存落盤
      >MYSQL_BIN_LOG::process_commit_stage_queue
       >ha_commit_low
        >innobase_commit
         >innobase_commit_low                                   
          >trx_commit_in_memory                                innodb 進(jìn)行提交,事物狀態(tài)由 prepare 變?yōu)?not_active

可以看到整個(gè)語(yǔ)句的流程大概為

  1. 會(huì)話狀態(tài)轉(zhuǎn)換為update

  2. 激活事物狀態(tài)由 not_active 變?yōu)?active

  3. 查找定位數(shù)據(jù)

  4. 進(jìn)行樂(lè)觀插入

    • 記錄insert的undo記錄
    • 記錄undo的redo log 入redo buffer
    • 進(jìn)行insert 元組插入，及實(shí)際的插入操作
    • 記錄插入的redo log 入redo buffer

  5. binlog event 寫入到 binlog cache

  6. 會(huì)話狀態(tài)轉(zhuǎn)換為query end

  7. 進(jìn)入提交準(zhǔn)備

    • binlog準(zhǔn)備
    • innodb層事物準(zhǔn)備，狀態(tài)由 active變?yōu)?prepare

  8. 進(jìn)入提交階段

  • innodb進(jìn)行組提交，確保redo落盤
  • binlog cache 進(jìn)行flush到binlog文件
  • fsync binlog文件進(jìn)行os緩存落盤
  • innodb 進(jìn)行提交,事物狀態(tài)由 prepare 變?yōu)?not_active

這只是大概流程其中很多很多的細(xì)節(jié)，不過(guò)有了入口函數(shù)也許好分析一些。

三、備用棧幀 

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：一個(gè)簡(jiǎn)單insert語(yǔ)句的大概流程

網(wǎng)站標(biāo)題：MySQL：一個(gè)簡(jiǎn)單insert語(yǔ)句的大概流程
路徑分享：http://bm7419.com/article4/iidioe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供虛擬主機(jī)、電子商務(wù)、網(wǎng)站導(dǎo)航、標(biāo)簽優(yōu)化、品牌網(wǎng)站制作、網(wǎng)站營(yíng)銷

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都定制網(wǎng)站網(wǎng)頁(yè)設(shè)計(jì)