Introduction to enabling InnoDB data engine in MySQL and differences and connections between InnoDB and MyISAM

InnoDB engine

The InnoDB engine provides support for database acid transactions and implements four isolation levels for the SQL standard. 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.

Main differences:

1, Myiasm is non-transactional security, and InnoDB is a transaction security

2, Myiasm lock granularity is table-level, and InnoDB support row-level lock

3. Myiasm supports full-text type indexing, while InnoDB does not support full-text indexing

4, myiasm relatively simple, more efficient than InnoDB, small applications can consider the use of myiasm

5, Myiasm table saved as a file form, cross-platform use more convenient

Application scenario: 1, myiasm management of non-transactional tables, providing high-speed storage and retrieval and full-text search capabilities, if you perform a large number of select operations in the application, you should choose MyIASM2, InnoDB for transaction processing, ACID transaction support and other features, If you perform a large number of insert and update operations in your app, you should select InnoDB

These differences can affect the performance and functionality of your application, so you have to choose the right engine for your business type to maximize the performance benefits of MySQL.

Introduction to enabling InnoDB data engine in MySQL and differences and connections between InnoDB and MyISAM