Introduction to MySQL storage engine MyISAM storage engine

MyISAM storage engine tables are in the database, and each table is stored as three physical files named after the table name. First of all, there must be a .frm file that stores table structure definition information, which is indispensable for any storage engine. There are also .MYD and .MYI files, which store table data (.MYD) and index data (.MYI) respectively. Each table has and only three files used as storage for tables of the MyISAM storage type. That is to say, no matter how many indexes the table has, they are all stored in the same .MYI file.

MyISAM supports the following three types of indexes:

1. B-Tree index

B-Tree index, as the name suggests, means that all index nodes are stored according to the data structure of the balance tree, and all index data nodes are in Leaf nodes.

2. R-Tree index

The storage method of R-Tree index is somewhat different from that of b-tree index. It is mainly designed to index fields for storage space and multi-dimensional data, so for the current MySQL version, it is also Only geometry type fields are supported for indexing.

3. Full-text index

Full-text index is what we call the full-text index, and its storage structure is also b-tree. Mainly to solve the inefficiency problem when we need to use like query.

MyISAM Among the above three index types, the most commonly used is the B-Tree index. Fulltext is occasionally used, but the R-Tree index is rarely used in general systems. In addition, MyISAM's B-Tree index has a larger limitation, that is, the sum of the lengths of all fields participating in an index cannot exceed 1000 bytes.

Although each MyISAM table is stored in a .MYD file with the same suffix name, the storage format of each file may not actually be exactly the same, because the data storage format of MyISAM is divided into static (FIXED) ) fixed length, dynamic (DYNAMIC) variable length and compressed (COMPRESSED) three formats. Of course, we can choose whether to compress among the three formats. We can specify {COMPRESSED | DEFAULT} through ROW_FORMAT when creating the table, or we can use the myisampack tool to compress. The default is not compression. In the case of non-compression, whether it is static or dynamic is related to the definition of a field in our table. As long as there are variable-length fields in the table, the table must be in DYNAMIC format. If there are no variable-length fields, it is in FIXED format. Of course, you can also use the alter table command to force a table with A DYNAMIC table with VARCHAR type fields is converted to FIXED, but the result is that the original VARCHAR field type will be automatically converted to CHAR type. On the contrary, if you convert FIXED to DYNAMIC, the CHAR type field will also be converted to VARCHAR type, so you must be careful when doing manual forced conversion.

Are the tables of the MyISAM storage engine reliable enough? The MySQL User Reference Manual lists table file corruption that may occur when encountering the following situations:

1. When mysqld is killed while doing write operations or other situations cause abnormal termination;

2. Host Crash ;

3. Disk hardware failure;

4. Bug in MyISAM storage engine?

After an error occurs in a table file of the MyISAM storage engine, it will only affect that table and not other tables, let alone other databases. If we find a problem with a MyISAM table while our database is running, we can use the check table command to try to verify it online, and we can use the repair table command to try to repair it. When the database is closed, we can also use the myisamchk tool to detect or repair one (or some) tables in the database. However, it is strongly recommended not to repair the table easily unless absolutely necessary, and to do as much backup as possible before repairing to avoid unnecessary consequences.

In addition, the tables of the MyISAM storage engine can theoretically be used and operated by multiple database instances at the same time, but we do not recommend this, and it is also mentioned in the official MySQL user manual. It is recommended not to use multiple database instances at the same time. MyISAM storage files are shared between mysqld.

The above is the introduction of the MyISAM storage engine of Mysql storage engine. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!