The Mylsam of the Mysql engine

MYSQL talking about MyISAM storage engine

Mind Mapping

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031517135244.png "style=" border:0px; " />

Introduced

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

Characteristics

> does not support transactions

The proof is as follows:

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

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031516463184.png "style=" border:0px; " />

>> operation

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031516484493.png "style=" border:0px; " />

Note: If you are doing transactional operations on the database, but the transaction cannot succeed, you will have to look at your table engine to see if the engine supports transactions.

>> See the transaction operations in InnoDB

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031516555969.png "style=" border:0px; " />

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

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

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031516305238.png "style=" border:0px; " />

> Locking and concurrency

Locking: Locks the entire table instead of rows.

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

When writing data, obtaining an exclusive lock, the entire table is locked, while the other connection requests (read, write requests) are waiting.

> Repair Table

>> View table Status

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031515164439.png "style=" border:0px; " />

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

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031515175881.png "style=" border:0px; " />

>> Repair (fix) the table. Oh, my watch is normal.

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031515195843.png "style=" border:0px; " />

> 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.

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031515282031.png "style=" border:0px; " />

The >> table structure is as follows

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031515290428.png "style=" border:0px; " />

>> Add a full-text index to a content field

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031515300284.png "style=" border:0px; " />

>> viewing the index of a table

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031515364270.png "style=" border:0px; " />

> Delayed update of Indexes. MYISAM Delay_key_write is turned on by default, the entire option is unique to the MYISAM engine.

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031515423473.png "style=" border:0px; " />

Note: At the end of the query, changes to the index data are not written to disk, but instead are changed in memory. The index block is dumped to disk only when the buffer is cleaned up or when the table is closed.

> Compression table

>> View Data File Locations

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031516020817.png "style=" border:0px; " />

>> Compressed Files

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/152332/2012031516214375.png "style=" border:0px; " />

Summarize

MyISAM is an excellent contributor to the index layer and the compression layer, so we often use MyISAM for the slave layer for client-side reading. While the MyISAM in the write library operation will produce an exclusive lock, if the write operation has been occupied, then the other connection request has been waiting, resulting in congestion, and even the server can be dang off.

This article from "Li Shilong" blog, declined reprint!

The Mylsam of the Mysql engine