MYSQL talking about MyISAM storage engine _mysql

Mind Mapping

Introduced

The most storage engines used in MySQL are InnoDB and MyISAM. As the default storage engine for MySQL, MyISAM is worth us to learn, the following is my "High-performance mysql" book mentioned in the MyISAM understanding, please give us a lot of advice.

Characteristics

> does not support transactions

Proved as follows:

>> records: The engine of the T2 table is MyISAM.

>> operation

Note: If you have transactions in the database, but the transaction does not succeed, you need to look at your table engine to see if the engine supports transactions.

>> below please see the transaction operation in InnoDB

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

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

> Lock and concurrency

Lock: Lock the entire table, not the line.

Concurrency: When reading data, shared locks (read locks) are available on all tables, and each connection does not interfere with each other.

When writing data, acquiring exclusive locks will lock the entire table, while other connection requests (read, write requests) are waiting.

> Repair Table

>> View table Status

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

>> Repair (fix) the table. Oh, this form of mine is normal.

> Column index. You can create a related index based on the first 500 characters of a BLOB or text type column.

>> add a text column to the T2 table.

>> table structure as follows

>> Add a Full-text index to the Content field

>> View index of table

> Delay Update index. MYISAM The Delay_key_write is turned on by default, and the entire option is unique to the MYISAM engine.

Note: Instead of writing the index changes to the disk after the query is finished, change the index data in memory. The index block is dumped to disk only when the buffer is cleaned or the table is closed.

> Compression table

>> View Data File location

>> Compressed Files

Summarize

MyISAM is an excellent contribution to the index and compression layers, so we often use MyISAM for the slave layer for the client to read. While the MyISAM in the write library operation will produce exclusive locks, if the write operation has been occupied, then other connection requests have been in the waiting, resulting in congestion, or even the server dang off.

Reference file: "High performance MySQL"