假设我编写了一个类似于"INSERT INTO my_table (a,b) VALUES (1,2)"的查询。
在MySQL中，从客户端传递查询到将其保存到磁盘上的过程。
比如：
-> What all innodb objects(filesystem buffers/logs) affected?
-> What're the step the data has to pass through till it reaches on table space?
换言之，数据库的剖析就是这样写的。
例如：
-> query being parsed by the parser
-> correct data page be loaded to innodb_buffer_pool
-> data being changed(dirty pages), and changes are logged to redo log buffer
-> entry on undo logs(rollback segment)
-> on commit, redo log buffer flushed to redo logfile
-> binary logging happens(if enabled)
-> dirty pages write to double write buffer
-> Finally it flushed to disk.
我相信人们有更好的方法/答案来解释这个序列。
最佳答案
这假设是InnoDB，而不是其他引擎。。。
你有一些基本知识。这里还有一些。
-> Query received by Server
-> query being parsed by the parser
-> "open" table (if not already open)
-> check for "strict mode" errors
-> Hand off the query from "Handler" to "Engine". (The rest assumes `ENGINE=InnoDB`)
-> If part of a transaction, ...
-> If autocommitting, ...
-> If `AUTO_INCREMENT, ...
-> If `INSERT` included `PRIMARY KEY` columns, ...
-> If neither of above, create hidden AI value, ...
-> Execute `BEFORE TRIGGERs`, if any
-> Given the PK, drill down the BTree to find the location.
-> Check for Duplicate Key -- PK and any relevant UNIQUE keys.
-> obtain eXclusive lock on the row -- (maybe gap lock?)
-> correct data page be loaded to innodb_buffer_pool (if not already cached)
-> If adding this row would overflow the block, "split" the block
-> Mark block(s) "dirty" (so it will _eventually_ be flushed to disk)
-> changes are logged to redo log
-> entry on undo logs(rollback segment)
-> Secondary index changes (if any) go into Change Buffer -- possibly triggering flush to make room
-> Execute `AFTER TRIGGERs`, if any
-> (if Galera involved) Write to gcache; coordinate with other nodes
-> if autocommit, redo log buffer flushed to redo logfile
-> deal with "group commit"?
-> binary logging happens(if enabled) -- or is this before COMMIT?
-> dirty pages write to double write buffer
-> If the Query Cache is enabled (and this is a write), invalidate all QC entries for the table(s) modified.
-> release eXclusive lock
-> success or failure returned to client
-> The block containing the row will be flushed to disk _later_
-> Index block updates will be done _later_ (cached read, apply Change Buffer, cached write, plus remove from CB)
我想还有很多细节。我也不清楚具体的顺序。
迟早要接触的文件包括
.frm获取架构（缓存）（只读）ibdata1或.ibd--数据和任何修改过的索引的“表空间”。（读-修改-写）（cfinnodb_file_per_table）双写缓冲区（写）
iblog*（写入）这不包括在一些复杂的
SELECTs中创建的“temp”表；用于ALTER TABLE等内容的“metadata”锁。