Two engine InnoDB and myiasm for the database

InnoDB engine

The InnoDB engine provides support for database acid transactions, and implements four isolation levels for the SQL standard, with information about database transactions and their isolation levels in the article, database transactions and their isolation levels. The engine also provides row-level and foreign-key constraints, which are designed to handle a large-capacity database system, which is itself a complete database system based on the MySQL backend, and the MySQL runtime InnoDB creates buffer pools in memory for buffering data and indexes. However, the engine does not support an index of type Fulltext, and it does not save the number of rows in the table, and the full table needs to be scanned when Select COUNT (*) from table. The engine is of course preferred when it is necessary to use a database transaction. Because the lock granularity is smaller, the write operation does not lock the full table, so using the INNODB engine increases efficiency when concurrency is high. However, using row-level locks is not absolute, and if MySQL does not determine the scope to scan when executing an SQL statement, the INNODB table will also lock the full table.

Myiasm engine

Myiasm is the default engine for MySQL, but it does not provide support for database transactions or row-level and foreign keys, so it is less efficient to write operations that require the entire table to be locked when insert (insert) or update (updated) data. Unlike InnoDB, however, the number of rows in the table is stored in myiasm, so the Select COUNT (*) from table only needs to read the saved values directly and does not require a full table scan. Myiasm is also a good choice if the table reads much more than writes and does not require support for database transactions.

Choice of two engines

Large datasets tend to select the InnoDB engine because it supports transactional processing and failback. The size of the database determines how long the recovery takes, and InnoDB can use the transaction log for data recovery, which is faster. Primary key queries can also be pretty fast under the InnoDB engine, but it's important to note that if the primary key is too long it can cause performance problems, as I'll see later in this question. A large number of INSERT statements (write multiple lines in each INSERT statement, BULK INSERT) will be faster under MyISAM, but the UPDATE statement will be faster under InnoDB, especially if the concurrency is large.

Two engine InnoDB and myiasm for the database