MySQL Learning Note (2)-MyISAM storage Engine

Prior to version 5.5, MyISAM was the default storage engine for MySQL. MyISAM does not support transactions or foreign keys, and its advantages are fast access, no requirement for transactional integrity, or a SELECT, insert-based application that can use this engine to create tables. Each myisam is stored as 3 files on disk with the same file name and table name, but with the following extensions:

• . frm (Definition of storage table)
• . MYD (MYData, storing data)
• . MYI (myindex, storage index)

Data files and index files can be stored separately in different directories, evenly distributed IO, for faster speeds. To specify the path to the index file and the data file, you need to specify it with the statement when you create the table, which means that the DATA DIRECTORY INDEX DIRECTORY index files and data files of the different MyISAM tables can be placed under different paths. The file path is an absolute path and has access rights.

A table of type MyISAM may be corrupted for a variety of reasons, and the damaged table may not be accessible, prompting the result of an error that needs to be repaired or accessed. MyISAM types of tables provide repair tools that can be used CHECK TABLE to check the health state of MyISAM and to REPAIR TABLE fix a corrupted MyISAM table with a statement. Table corruption can cause database exceptions to restart.

The MyISAM table supports different storage formats in 3, namely:

• static table (fixed length)
• Dynamic tables
• Compression table

静态表is the default storage format. The fields in the static table are fixed-length fields, so that each record is constant-size, which has the advantage of storing very quickly, easily cached, and prone to recovery; The disadvantage is that it takes up more space than a dynamic table. Static table data will be stored in the column width defined by the definition of the space, but when the application access is not to get these spaces, these spaces are returned to the application has been removed.

However, there are some issues that require special attention, and if the content that needs to be saved is preceded by a space, it will be removed when the result is returned. The following example shows the processing when the inserted record contains a space:

Mysql> CREATE TABLE Myisam_char (name char (ten)) Engine=myisam; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into Myisam_char values (' ABCDE '), (' ABCDE '), (' ABCDE '),  (' ABCDE  '); Query OK, 4 rows Affected (0.00 sec) records:4  duplicates:0  warnings:0mysql> Select Name,length (name) from my isam_char;+---------+--------------+| Name    | length (name) |+---------+--------------+| abcde   |            5 | | ABCDE   |            5 | |   ABCDE |            7 | | ABCDE   |            5 |+---------+--------------+4 rows in Set (0.00 sec)

动态表Contains variable-length fields, records are not fixed lengths, so the advantage of storage is that they occupy relatively little space, but frequent updates or delete records are fragmented, require regular execution of optimize table statements or myisamchk -r commands to improve performance, and recovery is relatively difficult in the event of a failure.

压缩表Created by the myisampack tool, occupying very small disk space. Because each record is individually compressed, there is very little access overhead.

MySQL Learning Note (2)-MyISAM storage Engine