数据库｜数据存储结构深入

存储蓝图

database > tablespaces > pages > rows > columns

物理存储

逻辑存储结构

The storage structure of InnoDB page Page type

• Data page (B-tree node)
• Undo page (undo log page)
• System page
• Transaction data page (transaction system page)
• Insert buffer bitmap page (insert buffer page)
• Uncompressd BLOB page
• Compressed BLOB page

页结构

why innodb index length limit to 3072 when page_size is 16k?

An InnoDB page must store >= 2 keys; a secondary key record must also contain the primary key value. Therefore, if both the primary key and the secondary key are at this maximum length, it must be less than 1/4th of the free space on a page including record overhead.

MySQL imposes its own limit to this number; MAX_KEY_LENGTH = 3072.

For page sizes = 16k, InnoDB historically reported 3500 bytes here, But the MySQL limit of 3072 was always used through the handler interface.

一个 InnoDB 页面必须存储 >= 2 个键；辅助键记录还必须包含主键值。因此，如果主键和辅助键都处于此最大长度，则它必须小于页面上可用空间的 1/4，包括记录开销。MySQL 对此数字施加了自己的限制；最大键长度 = 3072。对于页面大小 = 16k，InnoDB 历史上在这里报告 3500 字节，但是 3072 的 MySQL 限制始终通过处理程序接口使用。

https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/handler/ha_innodb.cc#L6380-L6401

/** Returns the maximum number of keys.
 @return MAX_KEY */

uint ha_innobase::max_supported_keys() const { return (MAX_KEY); }

/** Returns the maximum key length.
 @return maximum supported key length, in bytes */

uint ha_innobase::max_supported_key_length() const {
  /* An InnoDB page must store >= 2 keys; a secondary key record
  must also contain the primary key value.  Therefore, if both
  the primary key and the secondary key are at this maximum length,
  it must be less than 1/4th of the free space on a page including
  record overhead.
  MySQL imposes its own limit to this number; MAX_KEY_LENGTH = 3072.
  For page sizes = 16k, InnoDB historically reported 3500 bytes here,
  But the MySQL limit of 3072 was always used through the handler
  interface. */

https://github.com/mysql/mysql-server/blob/8.0/sql/sql_const.h#L47

constexpr const unsigned int MAX_KEY{MAX_INDEXES}; /* Max used keys */
constexpr const unsigned int MAX_REF_PARTS{16};    /* Max parts used as ref */
constexpr const unsigned int MAX_KEY_LENGTH{3072}; /* max possible key */

InnoDB is full of compromises between speed and space.

InnoDB was designed before SSDs, so reading multiple adjacent disk sectors was a good idea, especially if you are likely to need more than just one piece of the 16KB block.

If you look at the disk usage for the entire database, you may find a "waste" of only a few percent when measuring space.

Also, today, most applications are small enough to fit entirely in RAM. So, the block size makes little difference. Actually, 16KB blocks is slightly faster on the initial load due to fewer I/Os, even for SSDs.

If you have a huge dataset and are I/O-bound you may get into performance difficulties if you mostly do random reads of one small row. Example: key-value dataset with UUIDs for ids. In this one use case, a smaller block size on SSDs might be noticeably faster.

Back to tradeoffs -- That UUID (etc) example is not very common.

InnoDB 在速度和空间之间充满了妥协。InnoDB 是在 SSD 之前设计的，因此读取多个相邻磁盘扇区是一个好主意，尤其是当您可能需要 16KB 块的多个部分时。如果您查看整个数据库的磁盘使用情况，您可能会发现在测量空间时只有百分之几的“浪费”。此外，今天，大多数应用程序都足够小，可以完全放入 RAM 中。因此，块大小几乎没有区别。实际上，由于 I/O 较少，16KB 块在初始加载时稍微快一些，即使对于 SSD 也是如此。如果你有一个巨大的数据集并且受 I/O 限制，如果你主要对一小行进行随机读取，你可能会遇到性能困难。示例：具有用于 ID 的 UUID 的键值数据集。在这个用例中，SSD 上较小的块大小可能会明显更快。回到权衡——UUID（等）示例不是很常见。

InnoDB requires that at least two rows fit in a given page. This rule is bent a bit because the variable-length data types VARCHAR, VARBINARY, TEXT, and BLOB may overflow to additional pages. Both otherwise, a given row must fit within a little less than 8KB.

If the InnoDB page size were smaller than the current default of 16KB, the size limit of a row would be lower than it currently is. In fact, you can reconfigure InnoDB to use different page sizes, and this does change the maximum row size.

The default of 16KB is considered appropriate for most cases. It's true that there's a chance it does not align with the disk sector size exactly, but hopefully when performance is crucial your queries read from the buffer pool, not from disk.

InnoDB 要求至少两行适合给定页面。这条规则有点弯曲，因为可变长度数据类型 VARCHAR、VARBINARY、TEXT 和 BLOB 可能会溢出到其他页面。否则，给定行的大小必须小于 8KB。如果 InnoDB 页面大小小于当前默认值 16KB，则行的大小限制将低于当前值。事实上，您可以重新配置 InnoDB 以使用不同的页面大小，这确实会改变最大行大小。16KB 的默认值被认为适用于大多数情况。的确，它有可能与磁盘扇区大小不完全一致，但希望当性能至关重要时，您的查询是从缓冲池中读取的，而不是从磁盘中读取的。

内存

内存数据库系统在磁盘上维护备份，以提供持久性并防止易失性。有些数据库只在内存中存储数据，没有任何持久性保证。

数据库在认定操作完成之前，必须先将其结果写入一个顺序日志文件。为了避免在启动过程中或奔溃后重放完整的日志内容，内存数据库维护了一个备份副本。该备份副本使用一个基于磁盘且已排序的数据结构。并且对该结构的修改通常是异步（与客户端请求解耦）且分批处理的。这样可以减少I/O操作数量。在恢复过程中，数据库可以从备份和日志还原数据库内容

日志数据通常用于批量备份，在处理该批日志数据之后，备份将持有截止到这一特定时间点的数据库快照。因此可以丢弃之前的日志内容。这个过程称为生成检查点（checkpointing）. 它会更新磁盘上点数据库快照使其更接近最新的日志条目，从而缩短恢复时间，且备份过程不会阻塞客户端请求。

使用页缓存在内存里，序列化格式和数据布局也会使磁盘数据库产生额外的开销，而不会达到与内存数据库相同的优化程序。

pagecache

Page Cache的机制如上图所示，具体来说，当应用程序读文件时，系统先检查读取的文件页是否在缓存中；如果在，直接读出即可；如果不在，就将其从磁盘中读入缓存，再读出。此时如果内存有足够的内存空间，该页可以在page cache中驻留，其他进程再访问该部分数据时，就不需要访问磁盘了。

同样，在写文件之前，系统先检查对应的页是否已经在缓存中；如果在，就直接将数据写入page cache，使其成为脏页（drity page）等待刷盘；如果不在，就在缓存中新增一个页面并写入数据（这一页面也是脏页）。真正的磁盘I/O会由操作系统调用fsync等方法来实现，这一调用可以是异步的，保证磁盘I/O不影响文件读写的效率。

dirty_background_ratio
// 触发文件系统异步刷脏的脏页占总可用内存的最高百分比，当脏页占总可用内存的比例超过该值，后台回写进程被触发进行异步刷脏。

dirty_ratio
// 触发文件系统同步刷脏的脏页占总可用内存的最高百分比，当脏页占总可用内存的比例超过该值，生成新的写文件操作的进程会先执行刷脏。

dirty_background_bytes & dirty_bytes
// 上述两种刷脏条件还可通过设置最高字节数而非比例触发。如果设置bytes版本，则ratio版本将变为0，反之亦然。

dirty_expire_centisecs
// 这个参数指定了脏页多长时间后会被周期性刷脏。下次周期性刷脏时，脏页存活时间超过该值的页面都将被刷入磁盘。

dirty_writeback_centisecs 
// 这个参数指定了多长时间唤醒一次刷脏进程，检查缓存并刷下所有可以刷脏的页面。该参数设为零内核会暂停周期性刷脏。

• http://mysql.taobao.org/monthly/2020/09/01/
• https://lwn.net/ml/linux-fsdevel/20191210162454.8608-1-axboe@kernel.dk/
• https://web.cs.iastate.edu/~prabhu/Tutorial/title.html
• http://www.howardhuang.us/teaching/cs232/24-Cache-writes-and-examples.pdf
• https://man7.org/tlpi/code/online/dist/filebuff/direct_read.c.html
• https://linux.die.net/man/2/open
• https://www.alibabacloud.com/forum/read-446
• https://stackoverflow.com/questions/73382682/why-innodb-index-length-limit-to-3072-when-page-size-is-16k
• https://dev.mysql.com/blog-archive/innodb-tablespace-space-management/