MySQL 行格式：固定和动态之间的区别？
MySQL 根据列数据类型将表的行格式指定为固定或动态。 如果表具有可变长度列数据类型，例如 TEXT 或 VARCHAR，则行格式是动态的； 否则，它是固定的。
我的问题是，两种行格式有什么区别？ 其中一个比另一个更有效率吗？
MySQL specifies the row format of a table as either fixed or dynamic, depending on the column data types. If a table has a variable-length column data type, such as TEXT or VARCHAR, the row format is dynamic; otherwise, it's fixed.
My question is, what's the difference between the two row formats? Is one more efficient than the other?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这种差异实际上只对 MyISAM 重要，其他存储引擎并不关心这种差异。
编辑：许多用户评论说 InnoDB 确实关心：steampowered 的链接 1，链接2 由卡恩。
使用具有固定宽度行的 MyISAM，有一些优点：
无行碎片：使用可变宽度行可以将单个行拆分为数据文件中的多个部分。 这会增加磁盘寻道并减慢操作速度。 可以使用 OPTIMIZE TABLE 对其进行碎片整理，但这并不总是实用。
数据文件指针大小：在MyISAM中，有一个数据文件指针的概念，当需要引用数据文件时使用该指针。 例如，当索引引用行实际存在的位置时，这会在索引中使用。 对于固定宽度大小，该指针基于文件中的行偏移量（即，行为 1、2、3，无论其大小如何）。 对于可变宽度，指针基于字节偏移量（即行可能是 1、57、163）。 结果是，对于大型表，指针需要更大，这可能会给表增加更多的开销。
在损坏的情况下更容易修复。 由于每一行的大小相同，因此如果您的 MyISAM 表损坏，修复起来会容易得多，因此您只会丢失实际损坏的数据。 对于可变宽度，理论上可变宽度指针可能会变得混乱，这可能会导致以错误的方式存储数据。
现在固定宽度的主要缺点是浪费更多空间。 例如，您需要使用 CHAR 字段而不是 VARCHAR 字段，因此最终会占用额外的空间。
通常，您在格式上没有太多选择，因为它是根据架构决定的。 但是，如果您只有几个 varchar 或单个 blob/文本来尝试对此进行优化，那么可能是值得的。 例如，考虑将唯一的 varchar 转换为 char，或者将 blob 拆分到它自己的表中。
您可以在以下位置阅读更多相关信息：
http://dev. mysql.com/doc/refman/5.0/en/static-format.html
http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html
The difference really only matters for MyISAM, other storage engines do not care about the difference.
EDIT : Many users commented that InnoDB does care: link 1 by steampowered, link 2 by Kaan.
With MyISAM with fixed width rows, there are a few advantages:
No row fragmentation: It is possible with variable width rows to get single rows split into multiple sections across the data file. This can increase disk seeks and slow down operations. It is possible to defrag it with OPTIMIZE TABLE, but this isn't always practical.
Data file pointer size: In MyISAM, there is a concept of a data file pointer which is used when it needs to reference the data file. For example, this is used in indexes when they refer to where the row actually is present. With fixed width sizes, this pointer is based on the row offset in the file (ie. rows are 1, 2, 3 regardless of their size). With variable width, the pointer is based on the byte offset (ie. rows might be 1, 57, 163). The result is that with large tables, the pointer needs to be larger which then adds potentially a lot more overhead to the table.
Easier to fix in the case of corruption. Since every row is the same size, if your MyISAM table gets corrupted it is much easier to repair, so you will only lose data that is actually corrupted. With variable width, in theory it is possible that the variable width pointers get messed up, which can result in hosing data in a bad way.
Now the primary drawback of fixed width is that it wastes more space. For example, you need to use CHAR fields instead of VARCHAR fields, so you end up with extra space taken up.
Normally, you won't have much choice in the format, since it is dictated based on the schema. However, it might be worth if you only have a few varchar's or a single blob/text to try to optimize towards this. For example, consider switching the only varchar into a char, or split the blob into it's own table.
You can read even more about this at:
http://dev.mysql.com/doc/refman/5.0/en/static-format.html
http://dev.mysql.com/doc/refman/5.0/en/dynamic-format.html
固定意味着每行的大小完全相同。 这意味着如果需要加载数据页上的第 3 行，它将恰好位于 PageHeader+2*RowSize，从而节省一些访问时间。
为了找到动态记录的开头，必须查阅记录偏移量列表，这涉及额外的间接寻址。
简而言之，是的，动态行的性能会受到轻微影响。 不，这不是一个很大的。 如果您认为这会有问题，请进行测试。
Fixed means that every row is exactly the same size. That means that if the 3rd row on a data page needs to be loaded, it will be at exactly PageHeader+2*RowSize, saving some access time.
In order to find the beginning of a dynamic record, the list of record offsets must be consulted, which involves an extra indirection.
In short, yes, there's a slight performance hit for dynamic rows. No, it's not a very big one. If you think it will be a problem, test for it.
更新记录时会出现一个关键区别。 如果行格式固定，则记录的长度不会发生变化。 相反，如果行格式是动态的并且新数据导致记录长度增加，则使用链接来指向“溢出”数据（即称为溢出指针）。
这会使表碎片化并且通常会减慢速度。 有一个碎片整理命令（OPTIMIZE TABLE），这在一定程度上缓解了这个问题。
One key difference occurs when you update a record. If the row format is fixed, there is no change in the length of the record. In contrast, if the row format is dynamic and the new data causes the record to increase in length, a link is used to point to the "overflow" data (i.e. it's called the overflow pointer).
This fragments the table and generally slows things down. There is a command to defragment (OPTIMIZE TABLE), which somewhat mitigates the issue.
MySQL 文档中的此页面似乎与此处的最佳答案相矛盾，因为动态行格式对 InnoDB 表也意味着某些内容：
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
This page in MySQL's documentation seems to contradict the top answer here, in that DYNAMIC row format means something for InnoDB tables as well:
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
固定应该比动态更快、更安全，但缺点是字符长度固定。
您可以在这里找到此信息： http://dev.mysql .com/doc/refman/5.0/en/static-format.html
Fixed should be faster and more secure than dynamic, with the drawback of having a fixed char-lenght.
You can find this information here: http://dev.mysql.com/doc/refman/5.0/en/static-format.html