This is the 16th day of my participation in the August Text Challenge.More challenges in August

Preface: This article is my 16th article about MySQL, the level is general, ability is limited. The article is relatively shallow, suitable for novice. This article uses MySQL8.0 as an example. This article introduces the choice of MySQL storage engine.

I. Under normal circumstances

MySQL made InnoDB the default storage engine in 5.1 and supported full-text indexing in 5.6. In general, we prefer the InnoDB storage engine as our first choice. Unless we have a requirement that InnoDB can’t implement.

Of course, if we don’t need InnoDB’s features and extra features, concurrency and so on. We can choose MyISAM storage engine, which can save more space.

Note: the benefits of a hybrid storage engine are much lower than the risks. So don’t mix storage engines.

2. Factors to consider

1. The transaction

Transactions are the first thing we consider when we have write operations, and they are the most critical to choosing a storage engine. In terms of transaction support, InnoDB and XtraDB support is relatively good and stable.

2. Crash recovery

As mentioned in the previous article, MyISAM does not support crash recovery, which is one of the main reasons to be replaced, because online environmental database crashes require a very short recovery time and are highly reliable. InnoDB is a good choice when considering such conditions.

3. Hot standby

In an online environment, databases need to be backed up periodically for data security. In this case, a storage engine that supports hot backup is a good choice.

Log storage

In view of only need to insert and a small number of queries, and insert speed, storage space has high requirements for logging requirements. InnoDB is available at this time, but MyISAM or another logging storage engine is definitely a better choice.

However, the insertion speed may be affected if there are too many queries. In this case, read/write separation can be performed. The write library writes data and asynchronously synchronizes data to the read library, which is only responsible for query.

Or a date table operation, which can also separate reads and writes.

Reference documentation

High Performance MySQL