MySQL数据库文件到底是怎么存储的，顺带聊聊那些优化的小技巧和注意点

MySQL数据库文件到底是怎么存储的？咱们可以把它想象成一个超级有条理的仓库管理员管理一个大仓库，这个仓库就是你的硬盘，里面存着所有的数据，MySQL这个管理员，为了高效，把数据分门别类地用不同的方式和文件柜来存放。

最核心的是表，你建的每一张表，比如users用户表或者orders订单表，在硬盘上至少对应着两个文件（如果你用的是默认的InnoDB引擎的话），一个是.frm文件，这个文件很小，它就是个“表格结构说明书”，里面只记录这张表有哪些列，每列是什么数据类型（比如整数、字符串等），它不存实际的数据，另一个是.ibd文件，这个就厉害了，它是真正的“数据仓库”，这张表里所有的行数据、索引（相当于书的目录）都塞在这个文件里，这就是为什么你删掉一张大表会腾出很多空间，因为那个大的.ibd文件被删掉了。（来源：MySQL官方文档关于InnoDB文件存储的说明）

那数据在.ibd文件里又是怎么摆放的呢？InnoDB引擎用了“页”的概念，你可以把每个“页”想象成仓库里的一个标准尺寸的储物箱，大小默认是16KB，无论你存一条数据还是几百条，读写都是以这个“页”为单位来操作的，一条记录会被塞进一个页里，当页满了，就得申请新的页，这些页之间用指针连接起来，形成一种叫B+树的结构，这个B+树就像一本书的目录，目录项（索引）放在非叶子页上，真正的数据行都放在最底层的叶子页上，这样当你根据主键查一条数据时，MySQL就能顺着目录飞快地找到对应的那个储物箱（页），然后从里面拿出你要的数据。（来源：《高性能MySQL》一书中对InnoDB数据页和B+树结构的详解）

除了存数据的表空间文件,MySQL还有几个重要的“管理日志本”，一个是重做日志（redo log），通常是ib_logfile0和ib_logfile1两个文件，这个日志本特别重要，想象一下，当有顾客要下单（插入数据），仓库管理员不会立刻跑到遥远的仓库深处去摆放新商品，那样太慢了，他会先把这个操作记录在手边的redo log这个便签本上，告诉顾客“搞定！”，然后找个空闲时间再根据便签本上的记录去仓库里实际补货，这样速度极快，即使突然断电，管理员也能根据便签本恢复还没入库的操作，保证数据不丢，这就是所谓的“Write-Ahead Logging”机制。（来源：MySQL官方文档对InnoDB重做日志的阐释）

另一个是二进制日志（binlog），这个日志本记录的是所有对数据有改动的操作语句（比如INSERT、UPDATE）或者改动本身，它的主要目的是为了主从复制，也就是让另一个MySQL仓库（从库）能同步你这个主库的变化，也用来做数据恢复，redo log是InnoDB引擎级别的，而binlog是MySQL服务器层面的。

了解了存储方式,那些优化小技巧和注意点就很好理解了：

1. 主键的选择很重要：既然数据是按B+树组织的，主键就是默认的排序依据，用一个毫无意义的、自增的整数（AUTO_INCREMENT）做主键是最好的选择之一，因为新数据直接追加到最后一个页就行了，效率高，如果你用一个很长的字符串或者不是单调递增的列做主键，新数据插入可能导致页分裂（一个页装不下，要分裂成两个），造成性能开销和空间浪费。（来源：常见的数据库设计最佳实践）

2. 小心文本大对象：像TEXT, BLOB, VARCHAR这类可能存储很大内容的字段，InnoDB会尽量把他们都存在一个数据页里，但如果一条记录实在太大了，超出了一个页的容量，它就会把大部分数据存到其他的“溢出页”里，只在原位置留个指针，这意味着你查一条记录，可能需要进行多次磁盘I/O（先找主页，再找溢出页），速度会变慢，除非必要，否则别动不动就用TEXT，或者考虑把大字段分到另一张扩展表里。（来源：InnoDB官方文档对行格式和溢出页的说明）

3. 索引是双刃剑：索引就像书的目录，能极大加快查询速度，你应该为经常用在WHERE条件、JOIN连接和ORDER BY排序的列创建索引，目录本身也要占篇幅（磁盘空间），更麻烦的是，每当你增删改数据（写操作），MySQL不仅要动数据，还得去更新所有相关的索引（目录），这会拖慢写速度，所以索引不是越多越好，要保持平衡。

4. 定期进行表优化：当你频繁地更新、删除数据后，数据页里会产生很多“碎片”，就像仓库里有些货被搬走了，留下空位，新货又放不进去（因为尺寸不合），导致仓库空间利用率下降，这时可以使用OPTIMIZE TABLE 表名命令（或者用ALTER TABLE 表名 ENGINE=InnoDB）来重新整理一下数据页，回收空间，提高后续查询效率，不过这个操作会锁表，要在业务低峰期做。

5. 关注日志文件的大小和清理：重做日志和二进制日志如果一直不清理，会占用大量磁盘空间，你需要设置合适的日志文件大小和过期策略，可以配置二进制日志保留7天，超过7天的自动删除。

理解了MySQL是如何在硬盘上“摆摊”的，你就能更好地去规划你的“摊位”（数据库设计），知道在哪里容易“堵车”（性能瓶颈），并采取针对性的优化措施，让你的数据库跑得更顺畅。