MySQL 行格式：固定和动态之间的区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/147315/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Row Format: Difference between fixed and dynamic?
提问by moo
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.
MySQL 将表的行格式指定为固定或动态，具体取决于列数据类型。如果表具有变长列数据类型，例如 TEXT 或 VARCHAR，则行格式是动态的；否则，它是固定的。
My question is, what's the difference between the two row formats? Is one more efficient than the other?
我的问题是，这两种行格式有什么区别？一种比另一种更有效吗？
回答by Harrison Fisk
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.
差异实际上只对 MyISAM 重要，其他存储引擎不关心差异。 编辑：许多用户评论说 InnoDB 确实关心：链接 1 由 steampowered，链接 2 由 Kaan。
With MyISAM with fixed width rows, there are a few advantages:
使用固定宽度行的 MyISAM，有一些优点：
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.
无行碎片：可变宽度的行可以将单行拆分为数据文件中的多个部分。这会增加磁盘搜索并减慢操作速度。可以使用 OPTIMIZE TABLE 对其进行碎片整理，但这并不总是实用的。
数据文件指针大小：在MyISAM中，有一个数据文件指针的概念，当需要引用数据文件时使用。例如，当索引引用行实际存在的位置时，它会在索引中使用。对于固定宽度大小，此指针基于文件中的行偏移量（即行是 1、2、3，无论它们的大小如何）。对于可变宽度，指针基于字节偏移量（即行可能是 1、57、163）。结果是对于大表，指针需要更大，这可能会给表增加更多的开销。
在损坏的情况下更容易修复。由于每一行的大小相同，如果您的 MyISAM 表损坏，修复起来会容易得多，因此您只会丢失实际损坏的数据。对于可变宽度，理论上可变宽度指针可能会被弄乱，这可能导致以糟糕的方式存储数据。
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.
现在固定宽度的主要缺点是它浪费了更多的空间。例如，您需要使用 CHAR 字段而不是 VARCHAR 字段，因此最终会占用额外的空间。
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.
通常，您在格式上不会有太多选择，因为它是基于架构指定的。但是，如果您只有几个 varchar 或单个 blob/文本来尝试对此进行优化，则可能是值得的。例如，考虑将唯一的 varchar 切换为 char，或将 blob 拆分为它自己的表。
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/static-format.html
回答by Ben Hoffstein
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.
这会使表格变得碎片化，并且通常会减慢速度。有一个碎片整理命令（OPTIMIZE TABLE），这在一定程度上缓解了这个问题。
回答by B T
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:
MySQL 文档中的这个页面似乎与此处的最佳答案相矛盾，因为 DYNAMIC 行格式也对 InnoDB 表有意义：
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
回答by Jonathan Rupp
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.
固定意味着每一行的大小完全相同。这意味着如果需要加载数据页上的第 3 行，它将正好是 PageHeader+2*RowSize，从而节省了一些访问时间。
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.
简而言之，是的，动态行的性能会受到轻微影响。不，这不是一个很大的。如果您认为这将是一个问题，请进行测试。
回答by jmissao
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
固定应该比动态更快，更安全，缺点是具有固定的字符长度。您可以在此处找到此信息：http: //dev.mysql.com/doc/refman/5.0/en/static-format.html
