本文只分析了insert語句執(zhí)行的主路徑，和路徑上部分關鍵函數，很多細節(jié)沒有深入，留給讀者繼續(xù)分析

create table t1(id int);

insert into t1 values(1)

略過建立連接，從 mysql_parse() 開始分析

void mysql_parse(THD *thd, char *rawbuf, uint length,Parser_state *parser_state) {/* ...... *//* 檢查query_cache，如果結果存在于cache中，直接返回 */if (query_cache_send_result_to_client(thd, rawbuf, length) <= 0) {LEX *lex= thd->lex;/* 解析語句 */bool err= parse_sql(thd, parser_state, NULL);/* 整理語句格式，記錄 general log *//* ...... *//* 執(zhí)行語句 */error= mysql_execute_command(thd);/* 提交或回滾沒結束的事務（事務可能在mysql_execute_command中提交，用trx_end_by_hint標記事務是否已經提交） */if (!thd->trx_end_by_hint) {if (!error && lex->ci_on_success)trans_commit(thd);if (error && lex->rb_on_fail)trans_rollback(thd);}

進入 mysql_execute_command()

/* *//* ...... */case SQLCOM_INSERT:{ /* 檢查權限 */if ((res= insert_precheck(thd, all_tables)))break;/* 執(zhí)行insert */res= mysql_insert(thd, all_tables, lex->field_list, lex->many_values,lex->update_list, lex->value_list,lex->duplicates, lex->ignore);/* 提交或者回滾事務 */if (!res){trans_commit_stmt(thd);trans_commit(thd);thd->trx_end_by_hint= TRUE;}else if (res){trans_rollback_stmt(thd);trans_rollback(thd);thd->trx_end_by_hint= TRUE;}

進入 mysql_insert()

bool mysql_insert(THD *thd,TABLE_LIST *table_list,List<Item> &fields, /* insert 的字段 */List<List_item> &values_list, /* insert 的值 */List<Item> &update_fields,List<Item> &update_values,enum_duplicates duplic,bool ignore) { /*對每條記錄調用 write_record */while ((values= its++)){if (lock_type == TL_WRITE_DELAYED){LEX_STRING const st_query = { query, thd->query_length() };DEBUG_SYNC(thd, "before_write_delayed");/* insert delay */error= write_delayed(thd, table, st_query, log_on, &info);DEBUG_SYNC(thd, "after_write_delayed");query=0;}else /* normal insert */error= write_record(thd, table, &info, &update);}/*這里還有thd->binlog_query()寫binlogmy_ok()返回ok報文，ok報文中包含影響行數*/

進入 write_record

/*COPY_INFO *info 用來處理唯一鍵沖突，記錄影響行數COPY_INFO *update 處理 INSERT ON DUPLICATE KEY UPDATE 相關信息 */ int write_record(THD *thd, TABLE *table, COPY_INFO *info, COPY_INFO *update) {if (duplicate_handling == DUP_REPLACE || duplicate_handling == DUP_UPDATE){/* 處理 INSERT ON DUPLICATE KEY UPDATE 等復雜情況 */}/* 調用存儲引擎的接口 */else if ((error=table->file->ha_write_row(table->record[0]))){DEBUG_SYNC(thd, "write_row_noreplace");if (!ignore_errors ||table->file->is_fatal_error(error, HA_CHECK_DUP))goto err; table->file->restore_auto_increment(prev_insert_id);goto ok_or_after_trg_err;} }

進入ha_write_row、write_row

/* handler 是各個存儲引擎的基類，這里我們使用InnoDB引擎*/ int handler::ha_write_row(uchar *buf) {/* 指定log_event類型*/Log_func *log_func= Write_rows_log_event::binlog_row_logging_function;error= write_row(buf); }

進入引擎層，這里是innodb引擎，handler對應ha_innobase
插入的表信息保存在handler中

int ha_innobase::write_row( /*===================*/uchar* record) /*!< in: a row in MySQL format */ {error = row_insert_for_mysql((byte*) record, prebuilt); } UNIV_INTERN dberr_t row_insert_for_mysql( /*=================*/byte* mysql_rec, /*!< in: row in the MySQL format */row_prebuilt_t* prebuilt) /*!< in: prebuilt struct in MySQLhandle */ {/*記錄格式從MySQL轉換成InnoDB*/row_mysql_convert_row_to_innobase(node->row, prebuilt, mysql_rec);thr->run_node = node;thr->prev_node = node;/*插入記錄*/row_ins_step(thr); } UNIV_INTERN que_thr_t* row_ins_step( /*=========*/que_thr_t* thr) /*!< in: query thread */ {/*給表加IX鎖*/err = lock_table(0, node->table, LOCK_IX, thr);/*插入記錄*/err = row_ins(node, thr); }

InnoDB表是基于B+樹的索引組織表

如果InnoDB表沒有主鍵和唯一鍵，需要分配隱含的row_id組織聚集索引

row_id分配邏輯在row_ins中，這里不詳細展開

static __attribute__((nonnull, warn_unused_result)) dberr_t row_ins( /*====*/ins_node_t* node, /*!< in: row insert node */que_thr_t* thr) /*!< in: query thread */ {if (node->state == INS_NODE_ALLOC_ROW_ID) {/*若innodb表沒有主鍵和唯一鍵，用row_id組織索引*/row_ins_alloc_row_id_step(node);/*獲取row_id的索引*/node->index = dict_table_get_first_index(node->table);node->entry = UT_LIST_GET_FIRST(node->entry_list);}/*遍歷所有索引，向每個索引中插入記錄*/while (node->index != NULL) {if (node->index->type != DICT_FTS) {/* 向索引中插入記錄 */err = row_ins_index_entry_step(node, thr);if (err != DB_SUCCESS) {return(err);}} /*獲取下一個索引*/node->index = dict_table_get_next_index(node->index);node->entry = UT_LIST_GET_NEXT(tuple_list, node->entry);}} }

插入單個索引項

static __attribute__((nonnull, warn_unused_result)) dberr_t row_ins_index_entry_step( /*=====================*/ins_node_t* node, /*!< in: row insert node */que_thr_t* thr) /*!< in: query thread */ {dberr_t err;/*給索引項賦值*/row_ins_index_entry_set_vals(node->index, node->entry, node->row);/*插入索引項*/err = row_ins_index_entry(node->index, node->entry, thr);return(err); } static dberr_t row_ins_index_entry( /*================*/dict_index_t* index, /*!< in: index */dtuple_t* entry, /*!< in/out: index entry to insert */que_thr_t* thr) /*!< in: query thread */ {if (dict_index_is_clust(index)) {/* 插入聚集索引 */return(row_ins_clust_index_entry(index, entry, thr, 0));} else {/* 插入二級索引 */return(row_ins_sec_index_entry(index, entry, thr));} }

row_ins_clust_index_entry 和 row_ins_sec_index_entry 函數結構類似，只分析插入聚集索引

UNIV_INTERN dberr_t row_ins_clust_index_entry( /*======================*/dict_index_t* index, /*!< in: clustered index */dtuple_t* entry, /*!< in/out: index entry to insert */que_thr_t* thr, /*!< in: query thread */ulint n_ext) /*!< in: number of externally stored columns */ {if (UT_LIST_GET_FIRST(index->table->foreign_list)) {err = row_ins_check_foreign_constraints(index->table, index, entry, thr);if (err != DB_SUCCESS) {return(err);}}/* flush log，make checkpoint（如果需要） */log_free_check();/* 先嘗試樂觀插入，修改葉子節(jié)點 BTR_MODIFY_LEAF */err = row_ins_clust_index_entry_low(0, BTR_MODIFY_LEAF, index, n_uniq, entry, n_ext, thr, &page_no, &modify_clock);if (err != DB_FAIL) {DEBUG_SYNC_C("row_ins_clust_index_entry_leaf_after");return(err);} /* flush log，make checkpoint（如果需要） */log_free_check();/* 樂觀插入失敗，嘗試悲觀插入 BTR_MODIFY_TREE */return(row_ins_clust_index_entry_low(0, BTR_MODIFY_TREE, index, n_uniq, entry, n_ext, thr,&page_no, &modify_clock));

row_ins_clust_index_entry_low 和 row_ins_sec_index_entry_low 函數結構類似，只分析插入聚集索引

UNIV_INTERN dberr_t row_ins_clust_index_entry_low( /*==========================*/ulint flags, /*!< in: undo logging and locking flags */ulint mode, /*!< in: BTR_MODIFY_LEAF or BTR_MODIFY_TREE,depending on whether we wish optimistic orpessimistic descent down the index tree */dict_index_t* index, /*!< in: clustered index */ulint n_uniq, /*!< in: 0 or index->n_uniq */dtuple_t* entry, /*!< in/out: index entry to insert */ulint n_ext, /*!< in: number of externally stored columns */que_thr_t* thr, /*!< in: query thread */ulint* page_no,/*!< *page_no and *modify_clock are used to decidewhether to call btr_cur_optimistic_insert() duringpessimistic descent down the index tree.in: If this is optimistic descent, then *page_nomust be ULINT_UNDEFINED. If it is pessimisticdescent, *page_no must be the page_no to which anoptimistic insert was attempted last timerow_ins_index_entry_low() was called.out: If this is the optimistic descent, *page_no is setto the page_no to which an optimistic insert wasattempted. If it is pessimistic descent, this value isnot changed. */ullint* modify_clock) /*!< in/out: *modify_clock == ULLINT_UNDEFINEDduring optimistic descent, and the modify_clockvalue for the page that was used for optimisticinsert during pessimistic descent */ {/* 將cursor移動到索引上待插入的位置 */btr_cur_search_to_nth_level(index, 0, entry, PAGE_CUR_LE, mode, &cursor, 0, __FILE__, __LINE__, &mtr);/*根據不同的flag檢查主鍵沖突*/err = row_ins_duplicate_error_in_clust_online(n_uniq, entry, &cursor,&offsets, &offsets_heap);err = row_ins_duplicate_error_in_clust(flags, &cursor, entry, thr, &mtr);/*如果要插入的索引項已存在，則把insert操作改為update操作索引項已存在，且沒有主鍵沖突，是因為之前的索引項對應的數據被標記為已刪除本次插入的數據和上次刪除的一樣，而索引項并未刪除，所以變?yōu)閡pdate操作 */if (row_ins_must_modify_rec(&cursor)) {/* There is already an index entry with a long enough commonprefix, we must convert the insert into a modify of anexisting record */mem_heap_t* entry_heap = mem_heap_create(1024);/* 更新數據到存在的索引項 */err = row_ins_clust_index_entry_by_modify(flags, mode, &cursor, &offsets, &offsets_heap,entry_heap, &big_rec, entry, thr, &mtr);/*如果索引正在online_ddl，先記錄insert*/if (err == DB_SUCCESS && dict_index_is_online_ddl(index)) {row_log_table_insert(rec, index, offsets);}/*提交mini transaction*/mtr_commit(&mtr);mem_heap_free(entry_heap);} else {rec_t* insert_rec;if (mode != BTR_MODIFY_TREE) {/*進行一次樂觀插入*/err = btr_cur_optimistic_insert(flags, &cursor, &offsets, &offsets_heap,entry, &insert_rec, &big_rec,n_ext, thr, &mtr);} else {/*如果buffer pool余量不足25%，插入失敗，返回DB_LOCK_TABLE_FULL處理DB_LOCK_TABLE_FULL錯誤時，會回滾事務防止大事務的鎖占滿buffer pool(注釋里寫的)*/if (buf_LRU_buf_pool_running_out()) {err = DB_LOCK_TABLE_FULL;goto err_exit;}if (/*太長了，略*/) {/*進行一次樂觀插入*/err = btr_cur_optimistic_insert(flags, &cursor,&offsets, &offsets_heap,entry, &insert_rec, &big_rec,n_ext, thr, &mtr);} else {err = DB_FAIL;}if (err == DB_FAIL) {/*樂觀插入失敗，進行悲觀插入*/err = btr_cur_pessimistic_insert(flags, &cursor,&offsets, &offsets_heap,entry, &insert_rec, &big_rec,n_ext, thr, &mtr);}}}

btr_cur_optimistic_insert 和 btr_cur_pessimistic_insert 涉及B+樹的操作，內部細節(jié)很多，以后再做分析

總結

以上是生活随笔為你收集整理的MySQL · 源码分析 · 一条insert语句的执行过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。