How to master the characteristics of Dynamic tables in MySQL Databases

The following article describes how to correctly master the characteristics of Dynamic tables in MySQL databases. It can be said that dynamic tables are frequently used in MySQL databases, therefore, it is also important to master the dynamic tables in the MySQL database. The following is the specific content of the article. If a MyISAM table contains any variable-length columns (VARCHAR, BLOB, or TEXTD

The following article describes how to correctly master the characteristics of Dynamic tables in MySQL databases. It can be said that dynamic tables are frequently used in MySQL databases, therefore, it is also important to master the dynamic tables in the MySQL database. The following is the specific content of the article. If a MyISAM table contains any variable-length columns (VARCHAR, BLOB, or TEXTD

The following article describes how to correctly master the characteristics of Dynamic tables in MySQL databases. It can be said that dynamic tables are frequently used in MySQL databases, therefore, it is also important to master the dynamic tables in the MySQL database. The following is the specific content of the article.

If a MyISAM table contains any variable-length columns (VARCHAR, BLOB, or TEXTDynamic), or if a table is created using the ROW_FORMAT = DYNAMIC option, the DYNAMIC storage format is used.

This format is more complex because each line has a header indicating the length of the row. When a record becomes longer because of the update result, the record can end at more than one position.

You can use optimize table or myisamchk to fragment a TABLE. If you frequently access or change fixed-length columns in a table, there are also some variable-length columns in the table. It may be a good idea to move these variable-length columns to other tables to avoid fragmentation.

General features of a MySQL database dynamic format table:

Except for columns with a length less than 4, all string columns are dynamic.

Before each record, a bitmap indicates which column contains an empty string (for string columns) or 0 (For numeric columns ). Note that this does not include columns that contain NULL values. If the length of a character column is zero after the drag space is removed, or a numeric column is zero, this is marked in the in-place diagram and the column is not saved to the disk. A non-null string is stored as a Length Byte plus a string.

It usually requires less disk space than a fixed-length table.

Each record only uses space of the required size. Even so, if a record becomes larger, it is split into multiple slices as needed, resulting in the consequences of record fragmentation. For example, if you update a row with the extended row length information, the row will become fragmented. In this case, you can run optimize table or myisamchk-r from time to improve performance. You can use myisamchk-ei to obtain table statistics.

After a dynamic table crashes, it is more difficult to recreate it than a static table because one record may be divided into multiple fragments and the link (fragment) may be lost.

The expected row length of a dynamic dimension record is calculated using the following expression:

 
 

  
  
·3 
  
  
·+ (number of columns + 7) / 8 
  
  
·+ (number of char columns) 
  
  
·+ (packed size of numeric columns) 
  
  
·+ (length of strings) 
  
  
·+ (number of NULL columns + 7) / 8 
 
 

Each link requires an additional 6 bytes. When an update leads to the expansion of a record, a dynamic record is linked. Each new link must be at least 20 bytes, so the next extension may be carried out in the same link. If not, another link will be created. You can use myisamchk-ed to find the number of links. All links can be removed using myisamchk-r.

The above content is an introduction to how to master the dynamic table features in the MySQL database.