MySQL storage engine and selection method

0x00 MySQL database common storage engine

MySQL database is an open source database, supporting a variety of storage engine options, such as the most commonly used storage engine is: Myisam,innodb,memory.

MyISAM Storage Engine

MyISAM is the default storage engine of MySQL, it supports B-tree/fulltext/r-tree index type, and the lock level of myisam is table lock, table lock cost is small, lock is fast, lock granularity is high, the probability of lock conflict is higher, concurrency is low; table lock is suitable for querying. The MyISAM engine does not support transactional or foreign keys.

InnoDB Storage Engine

The biggest highlight of the InnoDB storage engine is transactional support and rollback support. It supports Hash/b-tree index types. InnoDB lock level is a row lock, row locks on the lock on the consumption of more than the table lock, but when the system concurrent access is high, InnoDB overall performance is much higher than MyISAM. The InnoDB index not only caches the index itself, but also caches the data, so InnoDB requires more memory.

Memory Storage Engine

The memory storage engine is a storage engine that stores all of the data in memory, so it can store a small amount of data. The memory storage engine has poor tolerance for data consistency because of the characteristics of the RAM. The lock level of memory is the same as MyISAM, which is a table lock and does not support transactional.

0x01 How to choose the right storage engine using the MyISAM engine

• r/w > 100:1 and update less (r/w: read/write Ratio)
• Concurrency not high, no support transactions required
• Small amount of table data
• Full-Text Search is required

Using the InnoDB engine

• R/w relatively small, frequently updated large print segment
• More than tens of thousands of table data, high concurrency
• High Security and availability requirements

Using the Memory engine

• have enough memory
• Low data consistency requirements, such as session/online numbers
• Data that needs to be archived regularly

0x02 Conclusion

Database selection, database storage engine selection, and database optimization, like an art, it does not have good and bad points, only suitable and not suitable. A project needs to consider a lot of factors, how to weigh the weights between several factors, how to find an optimal solution, really need a long time of accumulation of experience, the path of the database learning, persist, and then adhere to!

I am the second light in the original
Original link: http://www.2liang.me/archives/254
Reprint must be marked in the text and retain the original link, author and other information.

MySQL storage engine and selection method