MyISAM-compromise between performance and features (2) _ MySQL

MyISAM-compromise between performance and features (2) 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.