Analysis on the specific execution process of an insert statement in mysql 8.0

Code version: mysql 8.0.22

Programming language: C + + & & C + + 11 & & C + + 14 & & C + + 17

Previous article: Analysis on the specific execution process of an insert statement in mysql 8.0 (2)_ A ray of sunshine blog - CSDN blog

This chapter mainly introduces the storage engine. This chapter mainly introduces which interfaces need to be called when there is a secondary index in an insert statement. The order of calling interfaces is as follows:

The following interface can be understood basically by looking at the source code and will not be repeated.

Taking clustered index and secondary index as an example, the calling interface of clustered index is as follows:

| > row_ins_clust_index_entry
| | > row_ins_clust_index_entry_low
| | | > btr_cur_optimistic_insert
| | | | > btr_cur_ins_lock_and_undo
| | | | | > trx_undo_report_row_operation
| | | | | | > trx_undo_page_report_insert
| | | | | | | > trx_undo_page_set_next_prev_and_add
| | | | | | | | > trx_undof_page_add_undo_rec_log

| | | | > page_cur_tuple_insert
| | | | | > rec_convert_dtuple_to_rec
            > rec_convert_dtuple_to_rec_new
            > rec_convert_dtuple_to_rec_old
              > rec_convert_dtuple_to_rec_comp
| | | | | > page_cur_insert_rec_low
            > page_cur_insert_rec_write_log

(12),row_ins_clust_index_entry: inserting an entry into a clustered index will call the interface in 13
(13),row_ins_clust_index_entry_low: try to insert the entry into the clustered index, ignoring the foreign key constraint.
(14),btr_cur_optimistic_insert: insert a page into an index tree
(15),btr_cur_ins_lock_and_undo: check lock and write undo log
(16),page_cur_tuple_insert: append a record to the current page, which needs to be completed with the function in 17
(17),page_cur_insert_rec_low: append a record to the current page and write it in uncompressed mode

The secondary index calling interface is as follows:

| > row_ins_sec_index_entry
| | > row_ins_sec_index_entry_low
| | | > btr_cur_search_to_nth_level
        > ibuf_should_try
| | | > row_ins_scan_sec_index_for_duplicate

(18),row_ins_sec_index_entry: inserts an entry into the secondary index
(19),row_ins_sec_index_entry_low: try to insert the entry into the secondary index. If you find a record with exactly the same field, you must mark another record as deleted before inserting it into the secondary index
(20),btr_cur_search_to_nth_level: find the current location on the level given in the index tree
(21),row_ins_scan_sec_index_for_duplicate: scans the unique nonclustered index at a given index entry to determine whether there is a uniqueness conflict between the key values of the entry. Set a shared lock on potentially duplicate records

Finally, after all breakpoints are executed, use the bt command to print the stack information. Because there are too many breakpoints, some main functions are printed, as follows:

(gdb) bt
#0  btr_cur_optimistic_insert (flags=0, cursor=0x7fffe83d4ae0, offsets=0x7fffe83d4a78, 
    heap=0x7fffe83d4a70, entry=0x7fff300cb370, rec=0x7fffe83d4a80, big_rec=0x7fffe83d4a68, 
    thr=0x7fff3029c6f8, mtr=0x7fffe83d50a0)
    at /storage/innobase/btr/
#1  0x000055555a4c7571 in row_ins_clust_index_entry_low (flags=0, mode=2, index=0x7fff3028a640, 
    n_uniq=1, entry=0x7fff300cb370, thr=0x7fff3029c6f8, dup_chk_only=false)
    at storage/innobase/row/
#2  0x000055555a4c945c in row_ins_clust_index_entry (index=0x7fff3028a640, entry=0x7fff300cb370, 
    thr=0x7fff3029c6f8, dup_chk_only=false)
    at /storage/innobase/row/
#3  0x000055555a4c9c14 in row_ins_index_entry (index=0x7fff3028a640, entry=0x7fff300cb370, 
    multi_val_pos=@0x7fff3029c3f8: 0, thr=0x7fff3029c6f8)
    at /storage/innobase/row/
#4  0x000055555a4ca26f in row_ins_index_entry_step (node=0x7fff3029c338, thr=0x7fff3029c6f8)
    at /storage/innobase/row/
#5  0x000055555a4ca616 in row_ins (node=0x7fff3029c338, thr=0x7fff3029c6f8)
    at /storage/innobase/row/
#6  0x000055555a4caa92 in row_ins_step (thr=0x7fff3029c6f8)
    at /storage/innobase/row/
#7  0x000055555a4ea804 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x7fff302892c8 "\376\005", 
    prebuilt=0x7fff3029b830) at /storage/innobase/row/
#8  0x000055555a4eae9a in row_insert_for_mysql (mysql_rec=0x7fff302892c8 "\376\005", 
    prebuilt=0x7fff3029b830) at /storage/innobase/row/
#9  0x000055555a2a9ad9 in ha_innobase::write_row (this=0x7fff30287bf8, record=0x7fff302892c8 "\376\005")
    at /storage/innobase/handler/
#10 0x0000555558d4ace2 in handler::ha_write_row (this=0x7fff30287bf8, buf=0x7fff302892c8 "\376\005")
    at /sql/
#11 0x00005555590a14a7 in write_record (thd=0x7fff30001040, table=0x7fff30287270, info=0x7fffe83d64f0, 
    update=0x7fffe83d6570) at /sql/
#12 0x000055555909cb7e in Sql_cmd_insert_values::execute_inner (this=0x7fff302becc8, thd=0x7fff30001040)
    at /sql/
#13 0x00005555589ff8fc in Sql_cmd_dml::execute (this=0x7fff302becc8, thd=0x7fff30001040)
    at /sql/
#14 0x000055555897bd6e in mysql_execute_command (thd=0x7fff30001040, first_level=true)
    at /sql/
#15 0x00005555589810b9 in dispatch_sql_command (thd=0x7fff30001040, parser_state=0x7fffe83d7b90)
    at /sql/
#16 0x0000555558977226 in dispatch_command (thd=0x7fff30001040, com_data=0x7fffe83d8b80, 
    command=COM_QUERY) at /sql/
#17 0x00005555589756df in do_command (thd=0x7fff30001040)
    at /sql/
#18 0x0000555558b6ee3d in handle_connection (arg=0x55556006bbe0)
    at /sql/conn_handler/
#19 0x000055555aa337b6 in pfs_spawn_thread (arg=0x55555fee7ae0)
    at /storage/perfschema/
--Type <RET> for more, q to quit, c to continue without paging--
#20 0x00007ffff7f95609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#21 0x00007ffff76e0293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

So far, the basic process of insert ing a record in MySQL has been sorted out, and the process is still relatively clear. Those with primary database experience can sort it out.

The next few articles mainly talk about mtr in redo.log in MySQL.

Keywords: MySQL

Added by ataria on Sun, 19 Sep 2021 06:35:42 +0300