MySQL string format: the difference between fixed and dynamic?

Question

MySQL sets the table row format to both fixed and dynamic, depending on the data type of the column. If the table is of a variable-length column data type, such as TEXT or VARCHAR, the row format is dynamic; otherwise it is fixed.

My question is, what is the difference between the two line formats? Is more effective than the other?

Answer 1

The difference really matters only to MyISAM, other storage engines don't care about the difference. UPDATE: Many users have noted that InnoDB really cares: link 1 - , link 2 - Kaan .

With MyISAM with fixed line widths, there are several advantages:

1. No line fragmentation: using variable-width lines, you can split individual lines into several sections in the data file. This can increase disk search and slow down performance. It can be defragmented with OPTIMIZE TABLE, but this is not always practical.

2. Size of a pointer to a data file: MyISAM has the concept of a pointer to a data file, which is used when it needs to access a data file. For example, this is used in indexes when they refer to where the string is actually present. For fixed widths, this pointer is based on the offset of lines in the file (i.e. lines are 1, 2, 3 regardless of their size). With a variable width, the pointer is based on a byte offset (i.e. strings can be 1, 57, 163). As a result, the pointer should be larger for large tables, which potentially adds a lot more overhead to the table.

3. Easier to fix in case of corruption. Since each row is the same size, if your MyISAM table is corrupted, it is much easier to repair, so you only lose the data that is really corrupted. With variable widths, it is theoretically possible that variable-width pointers will get corrupted, which can lead to incorrect data transfers.

Now the main disadvantage of fixed widths is that it spends more space. For example, you need to use CHAR fields instead of VARCHAR fields so that additional space is eventually taken up.

As a rule, you will not have much choice in the format, as it is dictated by the scheme. However, this can be useful if you only have a few varchar or one blob / text to try to optimize this. For example, consider switching a single varchar to a character, or split a blob into its own table.

You can read 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

Answer 2

One key difference arises when updating a record. If the format of the string is fixed, the change in the record length does not change. Conversely, if the format of the string is dynamic and the new data causes the record to increase in length, the link is used to indicate "overflow" data (i.e., called the overflow pointer).

This fragmentes the table and, as a rule, slows down the work. There is a command for defragmentation (OPTIMIZE TABLE), which somewhat mitigates the problem.

Answer 3

This page in the MySQL documentation seems to contradict the main answer here, in the sense that the DYNAMIC row format also means something for InnoDB tables:

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html

Answer 4

Fixed means that each line has exactly the same size. This means that if the third row on the data page is to be loaded, it will be exactly PageHeader + 2 * RowSize, saving some access time.

To find the beginning of a dynamic recording, you need to consult a list of recording offsets, which is associated with an additional indication.

In short, yes, there is a slight performance improvement for dynamic strings. No, it's not very big. If you think this will be a problem, test it.

Answer 5

Fixed should be faster and more reliable than dynamic, with a lack of fixed char length. You can find this information here: http://dev.mysql.com/doc/refman/5.0/en/static-format.html