开发者社区> 重庆八怪> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

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

  1. 会话状态转换为update
  2. 激活事物状态由 not_active 变为 active
  3. 查找定位数据
  4. 进行乐观插入

    • 记录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

作者微信:
微信.jpg

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【0基础学习mysql】之日期函数和流程函数
【0基础学习mysql】之日期函数和流程函数
0 0
MySQL半同步(四)流程和相关参数
MySQL半同步(四)流程和相关参数
0 0
Mysql加锁流程详解
Mysql加锁流程详解
0 0
MySQL基础(SQL 字符串函数 数值函数 日期函数 流程函数)
1.Mysql下载安装启动 2.图形化界面工具 3.SQL 1.DDL 2.DML 3.DQL 4.DCL 1.管理用户 2.权限控制 4.函数 字符串函数 数值函数 日期函数 流程函数
0 0
Mysql-一条更新语句要经历那些流程
Mysql-一条更新语句要经历那些流程
0 0
深入理解MySQL主从复制原理以及集群部署流程
主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。 MySQL支持一台主库同时向多台从库进行复制, 从库同时也可以作为其他从服务器的主库,实现链状复制。
0 0
MYSQL查询和插入数据的流程是怎样的
MYSQL查询和插入数据的流程是怎样的
0 0
分析MySQL执行的流程(连接、缓存、分析、优化、执行、Undo Log、Binlog、Redo Log)
熟悉MySQL的都知道MySQL服务端实现主要分为Server层和存储引擎层。Server层负责接收和管理客户端连接、管理缓存、解析SQL、优化SQL、调用存储引擎执行SQL;存储引擎层主要负责存储、查询数据。
0 0
+关注
重庆八怪
10年ORACLE/MYSQL DBA,有一定C/C++基础
文章
问答
文章排行榜
最热
最新
相关电子书
更多
让 MySQL 原生分布式触手可及
立即下载
好的 MySQL 兼容可以做到什么程度
立即下载
云数据库RDS MySQL从入门到高阶
立即下载