MYSQL MyISAM storage engine

Mind Map

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. Features> transaction proofs are not supported:> 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 your table engine supports transactions.> The following describes the transaction operations in innodb:

> Storage structure: data files (. MYD), index files (. MYI), and structure files (. frm)> features: You can copy data files and index files on different servers.

> Locking and concurrent locking: the entire table is locked instead of rows. Concurrency: Shared locks (read locks) can be obtained on all tables during data reading. Each connection does not interfere with each other. When data is written, the entire table is locked when the exclusive lock is obtained. Other connection requests (Read and Write requests) are waiting.> Repair table> View 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

To sum up the outstanding contributions of myisam in the index and compression layers, 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