Shulou(Shulou.com)06/01 Report--

The following mainly brings you the common storage engines and features of MySQL. I hope these contents can bring you practical use, which is also the main purpose of this article that I edit the common storage engines and features of MySQL. All right, don't talk too much nonsense, let's just read the following.

1. Common storage engines and features of MySQL

1. InnoDB storage engine

Since the MySQL5.5 version, MySQL's default built-in storage engine has been InnoDB, and its main features are:

(1) disaster recovery is good.

(2) support transactions. The default transaction isolation level is repeatability, which is achieved through MVCC (concurrent versioning).

(3) the lock granularity used is row-level lock, which can support higher concurrency.

(4) Foreign keys are supported

(5) cooperate with some hot backup tools to support online hot backup.

(6) there is buffer management in InnoDB, which caches all indexes and data through the buffer pool to speed up the query.

(7) for the InnoDB type table, the physical organization of the data is the cluster table. All data is organized according to the primary key. The data and the index are put together, both on the leaf node of the B+ number.

2. MyISAM storage engine

Before version 5.5, MyISAM is the default storage engine for MySQL. The storage engine has poor concurrency and does not support transactions, so there are few usage scenarios. The main features are as follows:

(1) transactions are not supported

(2) Foreign keys are not supported. If foreign keys are forcibly added, errors will not be prompted, but foreign keys do not work.

(3) query caching of data only caches indexes, not data like InnoDB, and makes use of the cache of the operating system itself.

(4) the default lock granularity is table-level lock, so the concurrency is poor, the lock is added quickly, and the lock conflict is less, so deadlock is not easy to occur.

(5) support full-text indexing (after MySQL5.6, InnoDB storage engine also supports full-text indexing), but MySQL's full-text indexing is basically not used, for full-text indexing, there are other mature solutions, such as: ElasticSearch,Solr,Sphinx and so on.

(6) if the host where the database is located is down, the data file of MyISAM is easy to be damaged and difficult to recover.

3. MEMORY storage engine

Storing data in memory is similar to the idea of Redis,memcached on the market. In order to improve the speed of data access, the main features are as follows:

(1) there are restrictions on the supported data types, for example, TEXT and BLOB types are not supported. For string data, only fixed-length rows are supported, and VARCHAR will be automatically stored as CHAR type.

(2) the supported lock granularity is table-level lock. Therefore, when the traffic is relatively large, table-level locking will become the bottleneck of the MEMORY storage engine.

(3) since the data is stored in memory, all data will be lost after the CVM is restarted

(4) when querying, if it is useful to a temporary table, and there are fields of type BLOB,TEXT in the temporary table, then the temporary table will be converted to a table of type MyISAM, and the performance will degrade sharply.

4. ARCHIVE storage engine

ARCHIVE storage engine is suitable for limited scenarios. Because it supports compression, it is mainly used for archiving data such as logs and pipelining. Its main features are:

(1) Zlib compression is supported. Data will be compressed before being inserted into the table.

(2) only SELECT and INSERT operations are supported, and the stored data can only be queried, not modified or deleted.

(3) only indexes on self-increasing keys are supported, but not other indexes.

5. CSV storage engine

Data transfer trial, main features:

(1) the text whose data format is .csv format can be edited and saved directly

(2) it is convenient to import and export. You can export the data in a table directly to csv and try Excel office software to open it.

Second, the comparison between InnoDB and MyISAM

1. Due to the different lock granularity, InnoDB supports higher concurrency than MyISAM.

2. InnoDB is a row-level lock and MyISAM is a table-level lock, so InnoDB is more prone to deadlocks than MyISAM, the probability of lock conflict is higher, and the cost of locking is also higher, because each row needs to be locked.

3. In terms of backup and disaster recovery, InnoDB supports online hot backup and has a very mature online hot backup solution.

4. In terms of query performance, the query efficiency of MyISAM is higher than that of InnoDB, because InnoDB needs to maintain the data cache in the query process, and the query process is to locate the data block where the row is located, and then locate the row to be found from the data block; while MyISAM can directly locate the memory address where the data is located and find the data directly

5. SELECT COUNT (*) statement, if the number of rows is more than 10 million, MyISAM can quickly find out, but InnoDB query is particularly slow, because MyISAM stores the number of rows separately, and InnoDB needs Zhu rows to count the number of rows; so if you use InnoDB, and you need to query the number of rows, you need to do special processing on the number of rows, such as offline query and cache

6. The table structure files of MyISAM include .frm (table structure definition), .MYI (index) and .MYD (data), while the table data files of InnoDB are .ibd and .frm (table structure definition).

How to choose the appropriate storage engine

1. Whether transaction support is required for usage scenarios

2. Whether it needs to support high concurrency, the concurrency of InnoDB is much higher than that of MyISAM.

3. Whether you need to support foreign keys

4. Whether you need to support online hot backup

5. Buffer data efficiently. InnoDB buffers both data and index, while MyISAM buffers only index.

6. Index. The indexes of different storage engines are different.

For more technical articles related to MySQL, please visit the MySQL tutorial column to learn!

These are the details of the MySQL storage engine, please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.