MySQL architecture-mysql storage engine-MyISAM

 

MyISAM

MyISAM is the default storage engine of MySQL. MyISAM provides a good solution to the performance and features. Features include full-text indexing, compression, and GIS functions. MyISAM does not support transactions and row locks.

 

Storage

MyISAM stores the table in two files. A data file and an index file. The extension is. MYD and. myi. The format of MyISAM is platform independent. This means that you can copy these two files on any platform.

 

MyISAM can contain dynamic or static columns. MySQL determines which format to use based on the table definition. The number of rows in the MyISAM table is limited to the hard disk space and the maximum number of files allowed by the operating system.

 

MyISAM table in mysql5.0, the default is the dynamic length of rows, and can operate TB data, with a 6-byte pointer pointing to the data record. The default pointer of MySQL in earlier versions is 4 bytes, which supports 4 GB of data. The maximum pointer size supported by all MySQL versions is 8 bytes. To change the pointer size of the MyISAM table, you must specify the value of max_rows and avg_row_length. The two data points out the total amount of space you need.

 

 

Create Table mytable (

A integer not null primary key,

B char (18) not null

Max_rows = 1000000000 avg_row_length = 32;

 

In this example, we tell MySQL to allocate at least 32 GB space for this table. Check how much MySQL is allocated. Let's take a look.

Mysql> show table status like 'mytable' \ G
* *************************** 1. row ***************************
Name: mytable
Engine: MyISAM
Row_format: fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 98784247807
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2002-02-24 17:36:57
Update_time: 2002-02-24 17:36:57
Check_time: NULL
Create_options: max_rows = 1000000000 avg_row_length = 32
Comment:
1 row in SET (0.05 Sec)

 

We can see that the value of create_options is set. We can also see that the value of max_data_length is close to 91 GB. You can use the alter table statement to modify the pointer size. However, such an operation will overwrite the entire table and its indexes. It takes a lot of time.

 

MyISAM features

As an old MySQL engine. MyISAM has many good features. These features have been developed for many years. Can meet the work needs.

 

Lock and concurrency

MyISAM locks the entire table, not the row. The reader can obtain the read lock of the table to be read. The write lock is obtained by the Write Program. However, new data can still be inserted when the query statement is executed. (Concurrent inserts ). This is a very useful feature.

 

Automatic Repair

MySQL supports automatic detection and repair of MyISAM tables.

 

Manual repair

You can use the check table and repair table commands to detect and fix table errors. When the server is stopped, you can use the myisamchk command line tool to check and repair the table.

 

Index features

In the MyISAM table, you can add an index to the blob and text columns with the first 500 characters. MyISAM supports full-text indexing. For complex queries, separate words are indexed. The index will be detailed later.

 

Jian write latency

The MyISAM table is marked with the delay_key_write creation option. After a statement is completed, the changed index is not written to the hard disk. Instead, MyISAM caches this information in the memory. When it streamlines the buffer or closes the table, it writes the cached index block to the hard disk. For a frequently updated table, the performance will be greatly improved. However, when the server or system fails, the index will also be damaged and need to be repaired. You can use myisamchk to detect and fix the problem before starting the server. Or use the Automatic Repair Option. (Even if you do not use the delay_key_write feature, this is also a good security solution ). You can configure delay_key_write globally. You can also configure individual tables.

 

Compressed MyISAM table

For example, in CD-ROM-based or DVD-ROM-based applications and other embedded environments. Once these tables are created, they do not modify or fill up data. These tables are very suitable for compression.

 

You can use myisampack to compress tables. You cannot change the compressed table (though you can decompress, modify, and re-compress it), but this table can save a lot of hard disk space. It can also improve the performance, because this table is small and requires little hard disk space, it can quickly find records. Compressed tables can have indexes, but they are only read-only.

 

Reading the extracted data is negligible for most modern hardware. The real advantage of compression is that it reduces the hard disk I/O. Rows can be compressed separately. Therefore, MySQL does not need to decompress the entire table and only extract one row.

 

MyISAM merge Engine

The merge engine is a variant of MyISAM. A merge table integrates many identical MyISAM tables into a virtual table. This is suitable for MySQL applications in logs and data warehouses.

 

From: http://xiayuanfeng.iteye.com/blog/400662