MySQL行格式:固定和动态之间的区别? [英] MySQL Row Format: Difference between fixed and dynamic?
问题描述
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?
推荐答案
差异实际上仅对MyISAM至关重要，其他存储引擎则不关心该差异. 许多用户评论InnoDB确实在乎:链接2，Kaan .
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，有一些优点:
With MyISAM with fixed width rows, there are a few advantages:
-
没有行碎片:可变宽度的行可以将单个行拆分为整个数据文件的多个部分.这会增加磁盘查找次数并减慢操作速度.可以使用OPTIMIZE TABLE对其进行碎片整理，但这并不总是可行的.
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.
数据文件指针大小:在MyISAM中，有一个数据文件指针的概念，它在需要引用数据文件时使用.例如，当索引引用该行实际存在的位置时，将在索引中使用此字段.对于固定的宽度大小，此指针基于文件中的行偏移量(即，行的大小为1、2、3).在宽度可变的情况下，指针基于字节偏移量(即行可能是1、57、163).结果是，对于大表，指针需要更大，这可能会增加表的开销.
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.
在损坏的情况下更易于修复.由于每一行的大小都是相同的，因此如果MyISAM表损坏，则修复起来会容易得多，因此，您只会丢失实际损坏的数据.在理论上，使用可变宽度时，可变宽度指针很可能会弄乱，从而可能以不良的方式存储数据.
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.
现在，固定宽度的主要缺点是浪费更多的空间.例如，您需要使用CHAR字段而不是VARCHAR字段，因此最终会占用额外的空间.
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.
通常，您不会在格式上有太多选择，因为它是根据架构决定的.但是，如果您只有几个varchar或单个blob/文本来尝试对此进行优化，则可能是值得的.例如，考虑将唯一的varchar转换为char，或将blob拆分为它自己的表.
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
这篇关于MySQL行格式:固定和动态之间的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
