Mysql storage engine and Selection Method

Mysql storage engine and Selection Method
0x00 common storage engines for Mysql Databases

Mysql database is an open-source database that supports multiple storage engines, such as MyISAM, InnoDB, and Memory.

MyISAM storage engine

MyISAM is the default storage engine of Mysql. It supports the B-tree/FullText/R-tree index type, and the lock level of MyISAM is table lock. The table lock overhead is small and the lock is fast; the lock granularity is large, the probability of lock conflicts is high, and the concurrency is low. Table locks are suitable for queries. The MyISAM engine does not support transactions or foreign keys.

InnoDB Storage Engine

The biggest highlight of the InnoDB Storage engine is the support for transactional and rollback. It supports the Hash/B-tree Index type. The InnoDB Lock level is a row lock. The consumption of row locks is greater than that of table locks. However, when the concurrent access volume of the system is high, the overall performance of InnoDB is much higher than that of MyISAM. InnoDB indexes not only cache indexes but also data, so InnoDB needs a larger memory.

Memory storage engine

The Memory storage engine is a Memory-level storage engine that stores all data in the Memory, so it can store a small amount of data. Due to Memory features, Memory storage engine provides poor support for data consistency. The lock level of Memory is the same as that of MyISAM. It is a table lock and does not support transactions.

0x01 how to select a suitable storage engine using the MyISAM Engine

• R/W> 100: 1 and less Update (R/W: read/write ratio)
• Low concurrency, no need to support transactions
• Small table data volume
• Full-text search required

InnoDB Engine

• R/W is relatively small, and large fields are frequently updated.
• Table data volume exceeds 10 million, high concurrency
• High security and availability requirements

Use Memory Engine

• Enough memory
• Low requirements on data consistency, such as session/online user count
• Data to be archived on a regular basis

0x02 conclusion

Database selection, database storage engine selection, and database optimization are like an art. There are no good or bad points, but they are only suitable or not. There are many factors to consider for a project. How to weigh the weights between several factors and how to find an optimal solution requires a long time of experience and database learning, stick to it again!

Author: I am erliang
Link: http://www.2liang.me/archives/254
For reprinting, you must mark and retain the original link, author, and other information in the body.