mysql insert执行过程_MySQL · 源码分析 · 一条insert语句的执行过程-阿里云开发者社区...

绝地灬酷狼 2023-01-12 04:10 47阅读 0赞

本文只分析了insert语句执行的主路径，和路径上部分关键函数，很多细节没有深入，留给读者继续分析

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 \*/ /\* ...... \*/ /\* 执行语句 \*/

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;

/\* 执行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 &fields, /\* insert 的字段 \*/ List &values\_list, /\* insert 的值 \*/ List &update\_fields,

List &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()写binlog

my\_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 MySQL

handle \*/

/\*记录格式从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();

/\* 先尝试乐观插入，修改叶子节点 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 or

pessimistic 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 decide

whether to call btr\_cur\_optimistic\_insert() during

pessimistic descent down the index tree.

in: If this is optimistic descent, then \*page\_no

must be ULINT\_UNDEFINED. If it is pessimistic

descent, \*page\_no must be the page\_no to which an

optimistic insert was attempted last time

row\_ins\_index\_entry\_low() was called.

out: If this is the optimistic descent, \*page\_no is set

to the page\_no to which an optimistic insert was

attempted. If it is pessimistic descent, this value is

not changed. \*/

ullint\* modify\_clock) /\*!< in/out: \*modify\_clock == ULLINT\_UNDEFINED

during optimistic descent, and the modify\_clock

value for the page that was used for optimistic

insert 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操作

索引项已存在，且没有主键冲突，是因为之前的索引项对应的数据被标记为已删除

本次插入的数据和上次删除的一样，而索引项并未删除，所以变为update操作

\*/ if (row\_ins\_must\_modify\_rec(&cursor)) \{

/\* There is already an index entry with a long enough common

prefix, we must convert the insert into a modify of an

existing 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+树的操作，内部细节很多，以后再做分析

mysql insert执行过程_MySQL · 源码分析 · 一条insert语句的执行过程-阿里云开发者社区...

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关数据导入错误：MySQL拒绝执行INSERT语句

相关 java八股文面试[数据库]——MySQL 执行一条查询语句的内部执行过程

相关 MySQL 执行一条查询语句的内部执行过程？

相关 MySQL一条SQL语句的执行过程？

相关 MySQL 查询语句执行过程

相关 MySQL（二）：一条SQL查询语句的执行过程

相关 MySQL 语句执行过程详解

相关 mysql insert执行过程_MySQL · 源码分析 · 一条insert语句的执行过程-阿里云开发者社区...

相关 mysql 存储过程 rowcount_mysql 存储过程 row_count()的搜索结果-阿里云开发者社区

相关 PL/SQL 多条insert语句一起执行