Classification of MySQL storage engines

The previous chapters have explained how to use CRUD in MySQL. This chapter talks about some basic concepts, mainly to let everyone understand the classification of storage engines in MySQL database. What is a storage engine? It is how and how to better store the data locally so that the data can be checked and used at any time.

To learn MySQL, you can choose to install it and do it in practice.

1. In the file system, MySQL will save all the information in the database (schema) in the data directory.

Every time a database is created, it is actually equivalent to creating a directory. , and then create the corresponding file for the table corresponding to the database under the file in the directory. And the suffix is ​​.frm,

One thing to note here is that in Windows, the path is not case-sensitive , but it is case-sensitive in unix and linux.

2. You can use SHOW TABLE STATUS LIKE 'acout'; to view the corresponding table information.

Briefly describe the corresponding description.

Name: Table name

Engine: Storage type of the table. In the old version, the name of the column was Type.

Rows: The number of rows in the table. It should be noted that the value of this data in the MyISAM engine is correct, but in InnoDB, this value is an estimate.

Data_length: The size of the table data (unit: words Section)

Auth_increment: The value of the next AUTH_INCREMENT.

Update_time: The last modification time of the table data

Comment: Other information description of the table, corresponding to different storage engines The data is different. MyISAM table saves the comments of the table when it was created. If it is an InnoDB table, it saves the remaining space information of the table space. If it is a view, the information is the text of VIEW.

Others I won’t describe them one by one, just use search engines. Haha.

3.InnoDB storage engine:

InnoDB is the default for MySQL The transaction engine is also the most important and most commonly used storage engine. It is mainly used to process a large number of short-lived transactions. In most cases, short-lived transactions are submitted normally and are rarely rolled back. Based on the characteristics of InnoDB , Unless there are other reasons, the InnoDB engine is generally used by default.

InnoDB data is stored in the tablespace (tablespace).

InnoDB uses MVCC to support high concurrency and implement There are four standard isolation levels. The default is: REPEATABLE READ.

Here is just a brief introduction to the types of storage engines. If you want to continue learning in depth, it is recommended to read the official manual. "InnoDB transaction model and locks".

4.MyISAM storage engine: In MySQL5.1 and previous versions, MyISAM is the default storage engine. MyISAM provides a large number of Features, including: full-text index, compression, spatial functions, etc. However, MyISAM does not support transaction and row-level locks, and has a relatively large flaw, that is, it cannot be safely restored after a crash. But if it is read-only data, or the table is relatively small , you can still continue to use this engine, but it is best to use the InnoDB storage engine by default. There are two files stored in MyISAM: data files and index files, with .MYD and .MYI as suffixes respectively. MyISAM features: locking and concurrency, repair , index characteristics. Locking is to lock the entire table, not to rows. When reading, all tables read are added to shared locks. When writing, exclusive locks are added to the table. MyISAM compresses the table, if the table data , there will be no modification operations after importing, so it is suitable to use MyISAM to compress the table.

5. Other storage engines, in addition to these two engines, there are other built-in engines and third parties The engine is just briefly mentioned here without introducing too many details.

Since there are so many engines, how should we choose?

In most cases, InnoDB It is definitely the right choice. So starting from MySQL 5.5, InnoDB is the default storage engine. As for the choice, it is a simple sentence. Unless you need to use features that non-InnoDB does not have, and there is no other way to replace it, all The InnoDB engine should be preferred.

Notes

To choose the appropriate storage engine to avoid some common problems. It is best to operate these during testing Carry out in an environment.

Learning requires accumulation and progress bit by bit. Don’t be greedy.

The above is the detailed content of Classification of MySQL storage engines. For more information, please follow other related articles on the PHP Chinese website!