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

MyISAM is the default storage engine in MySQL, and generally speaking, not many people care about it. Deciding what kind of storage engine to use is a tricky thing, but it's worth studying. The article here only considers MyISAM and InnoDB, because they are the most common.

Let's answer some questions first:

Is there a foreign key in your database?

Do you need transaction support?

Do you need a full-text index?

What query mode do you often use?

How big is your data?

Thinking about the above questions can help you find the right direction, but it's not absolute. If you need transactions or foreign keys, then InnoDB may be the better way. If you need a full-text index, then MyISAM is usually a good choice because it is built into the system, however, we don't test 2 million rows very often. So, even if it's a little slower, we can get the full-text index from InnoDB by using Sphinx.

The size of data is an important factor that affects what kind of storage engine you choose. Large datasets tend to choose InnoDB because it supports transaction processing and fault recovery. The small size of the database determines the length of fault recovery time, InnoDB can use transaction logs for data recovery, which will be faster. While MyISAM may take hours or even days to do these things, InnoDB only takes a few minutes.

Your habit of manipulating database tables can also have a big impact on performance. For example, COUNT () can be very fast in the MyISAM table, while it can be painful under the InnoDB table. Primary key queries can be quite fast under InnoDB, but we need to be careful that if our primary key is too long, it can also cause performance problems. A large number of inserts statements are faster under MyISAM, but updates is faster under InnoDB-especially when concurrency is high.

So, which one do you use? From experience, if it is a small application or project, then MyISAM may be more suitable. Of course, there are times when using MyISAM in large environments can be very successful, but this is not always the case. If you are planning to use a project with a large amount of data and require transaction processing or foreign key support, you should really use InnoDB directly. But keep in mind that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables can be a very bad experience.

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.