1. 一条INSERT语句提交后InnoDB到底干了哪些活我经常被刚学MySQL的朋友问到一个问题我把一条数据insert进去屏幕上显示Query OK那这条数据到底被放到哪儿了是直接写进磁盘文件了吗如果这时候突然断电数据会不会丢问的人多了我发现很多人对数据存储这件事的理解其实停留在表→文件→行→列这个粗糙的层面上。真要把这个问题回答清楚得从MySQL的架构分层开始捋。一条INSERT语句被你敲进客户端到数据真正落盘中间经过了四层客户端/连接层负责认证、权限校验、建立连接。Server层包括查询缓存8.0已移除、解析器、优化器、执行器。INSERT语句在这里被解析成语法树再由优化器决定执行方式。存储引擎层真正碰数据的地方。InnoDB拿到执行器传来的行数据把它组织成记录放进内存里的数据页再通过后台线程刷到磁盘。文件系统层数据最终落在磁盘上的表空间文件中。很多人在这块有个误区以为Server层直接把数据交给了磁盘。实际上Server层根本不关心数据怎么存它只负责告诉存储引擎我要往这张表插入一条记录内容如下。至于这条记录是写在内存里还是写在文件里用什么样的格式全部由存储引擎自己决定。也就是说一条数据如何存储这个问题的答案基本等于InnoDB引擎如何存储一行记录。这一章我们就把这条链路拆开看先去理解InnoDB存储的最小物理单元——数据页再钻进数据页内部看一行记录长什么样最后说清楚一条数据是怎么从内存慢慢稳住到磁盘上的。2. 数据页InnoDB一切存储逻辑的最小地基先说一个关键概念**InnoDB读写数据的最小单位是页Page默认大小16KB。**行记录本身不是直接写到磁盘上的最小单位哪怕你只插入一行InnoDB也是先把这个记录塞进一个16KB的页里再以页为单位和磁盘打交道。这里有个很直觉的问题为什么最小单位不是一行而是一整个16KB的页答案藏在磁盘IO的特性里。机械硬盘一次顺序读写的耗时大约在几毫秒到十几毫秒而内存的随机访问耗时是纳秒级中间差了四五个数量级。如果每次读写一行就触发一次磁盘IO那数据库性能会惨不忍睹。而一次读16KB和读100字节磁盘寻道时间几乎是一样的——既然成本差不多那当然是尽量多捞点数据进内存。这就是页式存储管理的基本出发点。在InnoDB的存储层次里从上到下大概是这样一个结构表空间Tablespace逻辑上的存储容器一个表空间对应磁盘上的.ibd文件。表空间内部由段Segment、区Extent、页Page构成。段SegmentInnoDB把B树的叶子节点和非叶子节点分成两个段来管理目的是为了让索引的不同部分可以有不同的物理存储特性。区Extent由连续64个页组成大小是1MB。InnoDB在分配空间时是一次分配一个区而不是零散地一个页一个页分这样能保证数据在磁盘上的物理连续性。页Page物理存储的基本单位16KB。所以你在MySQL里看到一张表文件大小是1MB其实意味着这个表至少分配了一个区。这也是为什么你刚建表还没插入多少数据表文件就有1MB多——初始轮次分配就是按区走的文件被预分配了空间。数据页内部又是什么结构一个完整的16KB数据页由这几个部分组成区块作用File Header38字节记录页的校验和、页号、上一页和下一页的指针等数据页的身份证Page Header56字节记录页的状态信息比如页中记录数、槽位数量、最后插入位置等Infimum Supremum26字节虚拟的最大记录和最小记录用来限定页内记录的范围User Records真正存放用户数据的区域从页底往上增长Free Space尚未被使用的空闲空间位于页中偏后区域Page Directory记录槽Slot用于加速页内记录查找File Trailer8字节页尾校验用于检测页在刷盘过程中是否发生损坏注意User Records和Free Space是你来我往的关系插入新记录时记录从页的某个位置占用Free Space空间用完就得开辟新页。而Page Directory里存的不是记录本身而是记录在页内的偏移量相当于给页内记录做了一套目录索引。看到这里你应该理解一个真相即使在数据页内部行记录也不是物理连续的。记录的物理顺序和记录的逻辑顺序是两回事。逻辑上InnoDB通过每条记录头里的next_record字段把页内的记录串成一条单向链表页与页之间又通过File Header里的上一页/下一页指针串成双向链表。数据真正的有序性靠的是这些指针而不是物理位置。3. Compact行格式一行记录在数据页里到底长什么样页讲完了现在进入这一章的重头戏单条记录在页里到底怎么存放。InnoDB支持4种行格式Redundant、Compact、Dynamic、Compressed。5.7开始默认是Dynamic8.0沿用了Dynamic。但不管哪种底层的基本骨架都是从Compact演化来的。理解了Compact其他格式都是变种。一条完整的记录在数据页里由两部分组成记录的额外信息和记录的真实数据。额外信息又拆成三块变长字段长度列表、NULL值列表、记录头信息。3.1 变长字段长度列表为什么VARCHAR的长度信息要单独存如果你建了一张表里面有VARCHAR(100)的字段那么这一行数据里这个字段实际占用多少字节是可变的。比如存abc占用3字节存张三在utf8mb4字符集下占用6字节。既然长度不确定InnoDB就必须在记录里额外记录这些字段的实际长度否则读取时不知道从哪里截断。这个长度列表的存放规则是逆序存放。也就是说靠后的变长字段它的长度信息反而排在前面。为什么逆序这是为了配合记录头中的next_record指针做方向判断。往简单了理解就是InnoDB在解析记录时从记录的末尾往前读更高效逆序存放让最近插入的位置刚好就是需要解析的位置。如果字段允许为NULL长度信息本身也可能占用1~2字节。具体的规则是字段实际字节数不超过255字节就用1字节表示超过255字节用2字节表示。3.2 NULL值列表一条记录里的NULL不是没存储而是花了一个bit标记这是新手最容易误解的地方。很多人以为NULL就是什么都不存能省空间。**实际上NULL值本身也需要被记录只不过不是按字段完整占用空间而是用位图bitmap来标记。**表里允许为NULL的字段每个字段对应NULL值列表里的一个bit1表示当前记录的这个字段是NULL0表示不是NULL。注意几点NULL值列表只统计允许为NULL的字段如果字段是NOT NULL它就不会出现在这个bitmap里。可空字段数量不超过8个时NULL值列表占用1字节。超过8个就往上加字节。逆序排列即后面的可空字段对应更低的bit位。所以你会发现一个有意思的现象如果你把表里所有字段都设为NOT NULL那么每条记录都能省下这1字节的NULL标记空间。单独看不多但千万条记录累积下来节省的空间很可观。在表设计阶段尽量设置NOT NULL约束不仅是约束问题也是一个存储优化手段。3.3 记录头信息记录在页内如何行走记录头信息固定5字节一共40个bit位。其中和存储关系最密切的有这么几个delete_mask1bit记录是否被删除。注意被delete的记录并不会立刻从页中物理消失只是被打上删除标记。这也是为什么你删除表中大量数据后表文件大小可能没变——记录还在页里占着位只是对查询不可见。后面我们细说。n_owned4bit一个记录拥有多少个记录。这个字段用于页内记录槽的分组管理。heap_no13bit记录在页堆中的相对位置编号。record_type3bit记录类型。0是普通记录1是B树非叶子节点记录目录项2是Infimum3是Supremum。next_record16bit下一条记录相对于当前记录的地址偏移量。页内的记录就是靠这个字段串成链表的。这套设计最精妙的地方是记录之间用相对偏移量而不是绝对地址。如果页被整体搬移到别的位置页内记录之间的相对关系不变不需要修改任何指针。这正是页作为搬家最小单位的底气。3.4 隐藏列每条记录里你没看到但一定存在的三个字段除了你建表时定义的列InnoDB还会给每条记录额外加上几个隐藏字段如果你没有显式定义主键的话隐藏列作用DB_ROW_ID6字节当表没有显式主键时InnoDB会生成一个隐藏的递增行ID作为聚簇索引键DB_TRX_ID6字节最近一次插入或更新这条记录的事务ID。事务隔离级别的实现依赖它也就是MVCC里的版本链DB_ROLL_PTR7字节回滚指针指向undo log中的一条记录用于事务回滚和快照读这三个字段的存在意味着你实际上看到的每一行数据在页里都比你以为的宽。举个例子一张表有5个字段你在客户端看到一行6字节的数据InnoDB在页里存储时这条记录的真实占用可能比你想的多出十几二十字节。表里行数越多这部分额外开销越值得注意。3.5 手把手拆一条记录从建表到落页耳听为虚我们真的来拆一条记录。假设你建了这么一张表CREATE TABLE user_info ( id INT NOT NULL, name VARCHAR(50) NOT NULL, age TINYINT NULL, email VARCHAR(255) NULL, PRIMARY KEY (id) ) ENGINEInnoDB ROW_FORMATDYNAMIC;然后插入一行数据INSERT INTO user_info (id, name, age, email) VALUES (1, 张三, 25, NULL);在字符集utf8mb4下张三占6字节。这条记录在页内的布局大致是变长字段长度列表逆序存放。email是NULL不需要记长度会被置为0age是TINYINT定长字段不进长度列表name是变长字段且长度为6进列表。所以长度列表只有1个字节存一个0x06。NULL值列表表里允许NULL的字段有age和email两个顺序按字段序号age是第3列email是第4列。逆序存放bit0对应emailbit1对应age。email是NULL对应位填1age是25非NULL对应位填0。最终NULL值列表的二进制是00000010bit1位置为1表示age字段实际不是NULL这里要严格按InnoDB规则算age的bit位是1email的bit位是0结果字节是0x02。记录头信息5字节内部记录类型是普通记录0next_record指向下一条。真实数据部分依次是DB_ROW_ID如果没有主键才有这里表有主键所以不存、DB_TRX_ID6字节、DB_ROLL_PTR7字节、id14字节、name张三6字节、age251字节emailNULL不再额外占用空间。所以这条记录在数据页里的真实占用大约是额外信息长度列表NULL列表记录头≈ 1 1 5 7字节加上真实数据约 67461 24字节合计约31字节再算上记录头里其他对齐填充可能在40字节上下。一趟拆下来你就明白了存储一行数据的成本远比你想的复杂。多几十字节的开销在大表上是实打实的容量压力。3.6 行溢出VARCHAR真的能存65535字节吗很多人看文档说VARCHAR最大长度是65535字节于是以为可以放心地塞大文本。但实际建表时你会遇到报错或者警告。这里有一个存储上的硬约束一行记录存储在数据页里时不能超过一个页大小的一半约8KB。为什么是一半因为InnoDB的B树要求至少能容纳两条记录才能形成合理的分支结构如果一条记录就撑满了一个页那索引树就退化成了链表查询性能会灾难性下跌。所以InnoDB约定单条记录大小不能超过页大小的一半。如果一条记录中的某个字段数据量极大比如一个VARCHAR(5000)存了4800个字符会发生什么在Compact格式下InnoDB会把该字段的前768字节存储在记录的真实数据部分剩下的数据存放在溢出页中并在真实数据部分存一个指向溢出页的指针。下一章会讲B树你就明白为什么这768字节的截留很重要。到了Dynamic格式8.0默认InnoDB做了优化溢出字段的所有数据全部放到溢出页记录真实数据部分只保留20字节的指针。这样页内能容纳更多记录空间利用率更高但也意味着大字段的查询需要额外的一次甚至多次页读取。所以经验之谈不要把大文本或者长JSON直接丢在业务主表里。能拆出去放附属表就尽量拆。否则一张表几十个大字段每个字段都触发行溢出查询时每一行都要额外访问溢出页性能损耗非常大。4. 主键索引与B树一条数据在逻辑上如何被编织成网单个页内记录怎么放只是存储的第一层。真正决定数据找得到的关键是InnoDB用B树把这些页组织成了索引结构。4.1 聚簇索引数据行本身就是索引的叶子InnoDB的表底层是按照主键构建的一棵B树这棵树叫做聚簇索引clustered index。它的两个特点必须搞清楚叶子节点存的是整行记录也就是数据即索引索引即数据。你查主键最终落到的叶子节点里就是完整的行内容。非叶子节点存的是主键值 指向子节点的页指针它们的作用就是指引查找路径。因为数据行本身就按主键顺序排列在聚簇索引的叶子节点上所以一张表只能有一个聚簇索引——你的行记录只能按一种顺序物理摆放不可能既按id排又按name排。4.2 二级索引先查到主键再回表除了聚簇索引你建的普通索引比如在name字段上建索引叫二级索引secondary index。二级索引的B树其叶子节点存储的内容不是完整行记录而是索引列的值 主键值。这里有个面试高频考点为什么二级索引叶子节点存的是主键而不是直接存行地址因为如果存行地址一旦数据页发生分裂、合并、搬迁所有二级索引里的地址全都要更新代价巨大。而存主键值无论行在物理上怎么挪二级索引都不需要动查询时通过主键去聚簇索引里回表找行即可。这就是逻辑指针优于物理指针的设计思想。4.3 为什么主键推荐自增整数明白了B树和页分裂就能解释一个老生常谈的建议主键最好用自增整数不要用随机分布的UUID或者业务随机字符串。B树的叶子节点是按主键顺序串联的。当你插入一条主键更大的记录时记录落在当前最右的页后面一般只需要给那个页追加空间就行。如果主键是随机分布插入位置在整棵树的任意位置很有可能插到一个已经写满的页中间。页满了怎么办发生页分裂InnoDB把页里一半的记录挪到新页再调整前后页指针。页分裂会带来写放大、页空间碎片化还会让索引出现物理不连续。页分裂这件事在性能上最直观的表现是同样的INSERT量自增主键的表和UUID主键的表前者写入速度可能快一倍以上。如果你正在设计一张写入热点极高的流水表主键选择几乎可以说是第一优先级的事。5. 从内存到磁盘Buffer Pool、redo log和一条数据的持久化之旅理解了页和B树还剩最后一个关键问题这条记录写进内存页之后什么时候真正落到磁盘。如果每次INSERT都马上写磁盘性能会非常差如果一直不落盘断电就全没了。InnoDB的解决方案是内存缓冲 顺序日志 后台刷盘。5.1 Buffer Pool所有读写都在内存页里发生InnoDB启动时会申请一块内存区域叫Buffer Pool用来缓存数据页。你INSERT一条数据实际上是把记录写到了Buffer Pool里对应的页中并把页标记为脏页dirty page并不是直接写磁盘文件。之后后台线程会根据淘汰策略把脏页异步刷到磁盘。Buffer Pool的意义是让读和写尽量发生在内存里把随机磁盘IO变换成后台批量顺序刷盘。Buffer Pool的大小由innodb_buffer_pool_size控制。经验上如果业务是纯内存热点型这个值可以设置到物理内存的60%~75%如果服务器上还跑着其他服务要谨慎一点否则内存吃紧会引发系统swap反而拖垮数据库。5.2 redo log先把账本记好再慢慢算这里有个问题脏页在内存里如果你突然断电内存里的修改全没了怎么办InnoDB的思路是**把对页做了什么修改这件事以物理日志的形式先写入磁盘上的redo log文件。**redo log是顺序写速度远快于随机写。当一个事务提交时InnoDB只要保证redo log已经落盘就可以返回客户端提交成功——即使此时数据页还在内存里没刷到磁盘。等到数据库下次启动时如果发现Buffer Pool里的页面和磁盘不一致就会用redo log里的记录把页面重新演算到最新状态。这就是为什么说redo log是崩溃恢复的基石。这里有个经典的类比你开了一家小卖部账本数据页在脑子里记着内存但脑子可能断电丢记忆。所以你每次收钱先在门口小黑板上redo log写下今天收了多少钱、谁买的黑板是写在外面的不怕断电。等有空了再慢慢把黑板上的记录誊到正式账本里刷盘。5.3 刷盘节奏一个事务提交后最真实的磁盘IO序列把上面的内容串起来一条INSERT最终落盘的完整序列是这样的客户端发送INSERT语句。Server层解析、优化后调用InnoDB接口。InnoDB把记录写入Buffer Pool中的对应数据页标记为脏页。事务提交时InnoDB把本次修改生成redo log记录顺序写入redo log buffer然后flush到磁盘上的redo log文件。此时返回客户端提交成功默认innodb_flush_log_at_trx_commit1。后台刷盘线程会在合适的时机把Buffer Pool中的脏页批量写入表空间文件.ibd中。刷盘完成后对应的redo log空间可以被覆盖重用。注意从第4步之后系统的正确性就由redo log保证了。数据页可能几分钟后才真正落地磁盘但这不影响事务的持久性承诺。还有一个Doublewrite Buffer双写缓冲是为了解决写入16KB页时写到一半断电导致页损坏的问题。InnoDB在刷脏页之前会先把整个页写入双写缓冲区域如果发生半页写错误恢复阶段可以直接用双写缓冲里的完整页覆盖损坏的页。不过这个机制在8.0.20之后推荐放在了独立表空间里原理仍然是一样的宁可多一次顺序写也要保证页的原子性写入。5.4 再解释一个常识DELETE之后表文件为什么不变小有了页的概念这个现象就很好解释了。前面讲过删除一条记录时InnoDB只是把记录的delete_mask标记为1并不会真正从页中抹掉数据。而且delete操作同样会在undo log里写下原始记录以防将来事务回滚或者MVCC需要读旧版本。真正的空间回收要等两种情况页内所有记录都被标记删除这个页变成空闲页可能被后续插入复用。你手动执行OPTIMIZE TABLE或ALTER TABLE让InnoDB重建表把标记删除的记录物理清理掉。所以别指望DELETE大表后磁盘文件马上瘦身。想回收空间用OPTIMIZE TABLE重建表才是正路。6. 针对存储机制的实操建议几个值得记住的调优方向原理讲完说几个我实际项目里验证过的落地经验。**第一行格式不要轻易改。**现在是Dynamic默认最合理足够覆盖绝大多数场景。除非你有极特殊的兼容性需求否则不要手动改成Compressed或者Redundant。尤其是Redundant那是5.0之前的古董格式很多新功能都不支持生产环境碰都别碰。**第二字段类型和长度设计直接决定单页能装多少行。**一页16KB一条记录40字节和80字节单页记录数是400条和200条的差别而B树每多一层查询就需要多一次页访问。对百万行级别的大表行长度每缩小一点整个索引树的层级和IO次数都能受益。所以VARCHAR给够用就好不要习惯性写成255。**第三关心脏页刷盘的高峰。**如果监控里看到磁盘IO有规律的尖峰多半是脏页达到了阈值后台批量刷盘。可以通过innodb_io_capacity和innodb_max_dirty_pages_pct来调节刷盘节奏。简单原则用SSDio_capacity可以调到2000以上用机械盘调低一些避免刷盘占满磁盘导致正常查询变慢。**第四检验存储状态的最直接SQL。**别猜用工具看-- 查看当前表使用的行格式 SHOW TABLE STATUS LIKE user_info\G -- 查看表空间文件大小 SELECT TABLE_NAME, ROUND(SUM(DATA_LENGTH INDEX_LENGTH)/1024/1024, 2) AS Size(MB) FROM information_schema.TABLES WHERE TABLE_SCHEMA 你的库名 GROUP BY TABLE_NAME;**第五理解页分裂对写入性能的杀伤力。**如果你的表主键是UUID可以考虑在业务层做有序化改造比如将UUID按时间前缀排序或者干脆换用雪花ID。改动虽然影响面不小但对写入热表的收益非常直接。说实话处理过几万行和几千万行的表之后你会发现存储这件事真的不是SQL层面的增删改查能覆盖的。数据页、行格式、B树、Buffer Pool、redo log这五件事串起来才构成了一条数据从内存到磁盘的完整旅程。下次再有人问你MySQL的一条数据是怎么存的你至少能说出它先在一个16KB的数据页里按Compact规则摆好再由B树按主键串成索引最终通过redo log的兜底和后台刷盘稳稳落在磁盘上。