MySQL MyISAM storage engine

Mind Map

Jieshao

The most storage engines used in mysql are innodb and myisam. As the default storage engine of Mysql, myisam is worth learning. The following is my understanding of myisam mentioned in the High-Performance MYSQL. Please give me some advice.

Special Points

> Transactions are not supported.

The proof is as follows:

> Table record: the engine of table t2 is myisam.

> Operation

Note: If you perform transaction operations in the database but the transaction fails, you need to check whether the table engine supports transactions.

> The following describes the transaction operations in innodb:

> Storage structure: data file (. MYD), index file (. MYI), and structure file (. frm)

> Features: You can copy data files and index files on different servers.

> Lock and concurrency

Lock: Lock the entire table, not the row.

Concurrency: Shared lock read locks can be obtained for all tables when reading data. Each connection does not interfere with each other.

When data is written, the entire table is locked when the exclusive lock is obtained, while other connection requests (Read and Write requests) are waiting.

> Repair tables

> View the table status

> Check the table to see if the table is normal.

> Repair (repair. Haha, my table is normal.

> Column index. You can create indexes based on the first 500 characters of BLOB or TEXT columns.

> Add a text column to table t2.

> The table structure is as follows:

> Add a full-text index for the content field

> View table Indexes

> Delayed Index Update. MYISAM enables DELAY_KEY_WRITE by default. The entire option is unique to the MYISAM engine.

Note: After the query is complete, the index change data is not written to the disk, but the index data in the memory is changed. The index block is dumped to the disk only when the buffer is cleared or the table is closed.

> Compressed table

> View the data file location

> Compressed file

Summary

Myisam's outstanding contributions to the index and compression layers, so we often use myisam for the slave layer for clients to read. However, myisam will generate exclusive locks when writing database operations. If the write operation is always in use, other connection requests will remain in the waiting state, resulting in blocking and even removing the server dang.

Reference file: High-Performance MYSQL

Connection: http://www.cnblogs.com/baochuan/archive/2012/03/15/2398276.html