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
兩個階段
二、大概流程
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
作者微信:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-2155792/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL的簡單查詢語句MySql
- 一個insert插入語句很慢的優化優化
- MySQL INSERT IGNORE語句的使用MySql
- javascript流程控制語句簡單介紹JavaScript
- 一條簡單的更新語句,MySQL是如何加鎖的?MySql
- MySQL:簡單insert 一秒原因排查MySql
- 一條簡單SQL語句的構成及語句解析SQL
- mysql的sql語句執行流程MySql
- MySQL -update語句流程總結MySql
- 【Mysql】兩條insert 語句產生的死鎖MySql
- SQLite Insert 語句SQLite
- 一句簡單的SQL查詢語句的背後...SQL
- mysql insert語句錯誤問題解決MySql
- MySQL 5.5 INSERT ... ON DUPLICATE KEY UPDATE語句說明MySql
- SQLite中特殊的INSERT語句SQLite
- 簡單的SQL語句學習SQL
- 請教一個mysql查詢語句!!MySql
- 簡單的幾條Insert語句引起的邏輯Standby應用延遲的診斷
- mysql 常用sql語句 簡介MySql
- 一條insert語句導致的效能問題分析(一)
- 一條更新語句的執行流程
- 【SQL】9 SQL INSERT INTO 語句SQL
- Sql Server系列:Insert語句SQLServer
- Oracle 使用一條insert語句完成多表插入Oracle
- 【SQL】使用一條INSERT語句完成多表插入SQL
- 簡單使用oracle的審計語句Oracle
- GO語言一個簡單的工程Go
- 一條查詢語句的執行流程
- mysql handler語句之一MySql
- MySQL的語句MySql
- 教你使用SQLite-insert語句SQLite
- Java簡單迴圈語句案例Java
- JavaScript return語句簡單介紹JavaScript
- javascript with()語句用法簡單介紹JavaScript
- 簡單SQL語句小結(轉)SQL
- 快速執行大量 insert 語句的方法
- 一條insert語句導致的效能問題分析(二)
- 一個簡單mysql 備份策略 。MySql