Shulou(Shulou.com)06/01 Report--

MyISAM Table Storage Formats

This article focuses on Static (Fixed-Length) TABLE and Dynamic Table as well as the handling of spaces.

Http://dev.mysql.com/doc/refman/5.7/en/myisam-table-formats.html

1. Create and change MyISAM Table Storage Formats syntax

# # table has no BLOB or TEXT # #

Create Table Engine

Create Table table_name ROW_FORMAT=FIXED

Change Table Engine

Alter Table table_name ROW_FORMAT=DYNAMIC

two。 Case

Create a FIXED TABLE OF MyISAM Engine and insert characters with spaces to see how FIXED TABLE OF MyISAM Engine handles spaces.

Mysql > create table myisam_char (name char (10)) engine=myisam ROW_FORMAT=FIXED

Query OK, 0 rows affected (0.04 sec)

Mysql > insert into myisam_char values ('abcde'), (' abcde')

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > select name,length (name) from myisam_char

+-+ +

| | name | length (name) |

+-+ +

| | abcde | 5 | |

| | abcde | 5 | |

| | abcde | 7 | |

| | abcde | 7 | |

+-+ +

4 rows in set (0.00 sec)

The fields in # # FIXED TABLE OF MyISAM Engine are all non-variable length fields, and the data will fill in the blanks according to the width of the column, but these spaces will not be obtained when the application accesses them. These spaces have been removed before they are returned to the application.

# # so, the development requirement is supposed to save the space after the character, so you need to pay attention to it.

Mysql > show table status from test1 like 'myisam_char'

+- -+- -+

| | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | |

+- -+- -+

| | myisam_char | MyISAM | 10 | Fixed | 4 | 11 | 44 | 3096224743817215 | 1024 | 0 | NULL | 2016-03-22 16:09:26 | 2016-03-22 16:09:35 | NULL | latin1_swedish_ci | NULL | row_format=FIXED |

+- -+- -+

1 row in set (0.00 sec)

# # viewing MyISAM Table Storage Formats directly with SHOW TABLE STATUS Syntax

2.2 change MyISAM Table Storage Formats

Mysql > alter table myisam_char engine=myisam ROW_FORMAT=DYNAMIC

Query OK, 4 rows affected (0.01sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > show table status from test1 like 'myisam_char'

+-- -+- -+

| | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | |

+-- -+- -+

| | myisam_char | MyISAM | 10 | Dynamic | 4 | 20 | 80 | 281474976710655 | 1024 | 0 | NULL | 2016-03-22 16:21:44 | 2016-03-22 16:21:44 | NULL | latin1_swedish_ci | NULL | row_format=DYNAMIC |

+-- -+- -+

1 row in set (0.00 sec)

Mysql > insert into myisam_char values ('ABCDE'), (' ABCDE')

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > select name,length (name) from myisam_char

+-+ +

| | name | length (name) |

+-+ +

| | abcde | 5 | |

| | abcde | 5 | |

| | abcde | 7 | |

| | abcde | 7 | |

| | ABCDE | 5 | |

| | ABCDE | 5 | |

| | ABCDE | 7 | |

| | ABCDE | 7 | |

+-+ +

8 rows in set (0.00 sec)

Features of 3.Static (Fixed-Length) and Dynamic Table

3.1Features of Static (Fixed-Length)

Static-format tables have these characteristics:

CHAR and VARCHAR columns are space-padded to the specified column width, although the column type is not altered. BINARY and VARBINARY columns are padded with 0x00 bytes to the column width.

Very quick.

Easy to cache.

Easy to reconstruct after a crash, because rows are located in fixed positions.

Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk-r.

Usually require more disk space than dynamic-format tables.

# # this storage method has the advantages of fast storage, easy caching, and easy recovery in the event of failure because the row is located in a fixed location; the disadvantage is that it takes up more space than dynamic tables.

3.2 Dynamic Table Featur

Dynamic-format tables have these characteristics:

All string columns are dynamic except those with a length less than four.

Each row is preceded by a bitmap that indicates which columns contain the empty string (for string columns) or zero (for numeric columns). This does not include columns that contain NULL values. If a string column has a length of zero after trailing space removal, or a numeric column has a value of zero, it is marked in the bitmap and not saved to disk. Nonempty strings are saved as a length byte plus the string contents.

Much less disk space usually is required than for fixed-length tables.

Each row uses only as much space as is required. However, if a row becomes larger, it is split into as many pieces as are required, resulting in row fragmentation. For example, if you update a row with information that extends the row length, the row becomes fragmented. In this case, you may have to run OPTIMIZE TABLE or myisamchk-r from time to time to improve performance. Use myisamchk-ei to obtain table statistics.

More difficult than static-format tables to reconstruct after a crash, because rows may be fragmented into many pieces and links (fragments) may be missing.

The expected row length for dynamic-sized rows is calculated using the following expression:

three

+ (number of columns + 7) / 8

+ (number of char columns)

+ (packed size of numeric columns)

+ (length of strings)

+ (number of NULL columns + 7) / 8

There is a penalty of 6 bytes for each link. A dynamic row is linked whenever an update causes an enlargement of the row. Each new link is at least 20 bytes, so the next enlargement probably goes in the same link. If not, another link is created. You can find the number of links using myisamchk-ed. All links may be removed with OPTIMIZE TABLE or myisamchk-r.

# # this storage method takes up less space than static tables; however, frequent updates and deletions of records will produce lock sheets. Therefore, OPTIMIZE TABLE or myisamchk-r commands need to be executed periodically to improve performance; recovery is difficult in the event of a failure.

#

All rights reserved, the article is allowed to be reprinted, but the source address must be indicated by link, otherwise legal liability will be investigated! [QQ Exchange Group: 53993419]

QQ:14040928 E-mail:dbadoudou@163.com

Link to this article: http://blog.itpub.net/26442936/viewspace-2062237/

#

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.