What Are The Differences Between InnoDB and Myisam in MySQL?

Innodb engine overview

Simple Application Server
USD1.00 New User Coupon
* Only 3,000 coupons available.
* Each new user can only get one coupon(except users from distributors).
* The coupon is valid for 30 days from the date of receipt.

The Innodb engine provides support for database ACID transactions and implements the four isolation levels of the SQL standard. The engine also provides row-level locks and foreign key constraints. Its design goal is to handle large-capacity database systems. It itself is actually a complete database system based on the MySQL background. When MySQL is running, Innodb will create a buffer pool in memory for Buffer data and indexes. However, the engine does not support FULLTEXT type indexes, and it does not save the number of rows in the table. When SELECT COUNT(*) FROM TABLE, scan the entire table. This engine is of course the first choice when you need to use database transactions. Because the lock granularity is smaller, the write operation will not lock the entire table, so when the concurrency is high, using the Innodb engine will improve efficiency. However, the use of row-level locks is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table.

Overview of MyISAM engine

MyISAM is the default engine of MySQL, but it does not provide support for database transactions, nor does it support row-level locks and foreign keys. Therefore, when INSERT (insert) or UPDATE (update) data, write operations need to lock the entire table, which is efficient It will be lower. However, unlike Innodb, MyISAM stores the number of rows in the table, so when SELECT COUNT(*) FROM TABLE, you only need to directly read the saved values without performing a full table scan. If the table has more read operations than write operations and does not require database transaction support, then MyISAM is also a good choice.

Briefly introduce the difference

1. MyISAM is non-transaction-safe, while InnoDB is transaction-safe
2. The granularity of MyISAM locks is table-level, while InnoDB supports row-level locks
3. MyISAM supports full-text indexing, while InnoDB does not support full-text indexing
4. MyISAM is relatively simple, and its efficiency is better than InnoDB. Small applications can consider using MyISAM
5. The MyISAM table is saved as a file, which is more convenient to use across platforms
Application scenarios
1. MyISAM manages non-transactional tables, provides 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 MyISAM
2. InnoDB is used for transaction processing and has features such as ACID transaction support. If you perform a large number of insert and update operations in your application, you should choose InnoDB

1. Storage structure MyISAM: Each MyISAM is stored as three files on the disk. They are: table definition file, data file, and index file. The name of the first file starts with the name of the table, and the extension indicates the file type. The .frm file stores table definitions. The extension of the data file is .MYD (MYData). The extension of the index file is .MYI (MYIndex). InnoDB: All tables are stored in the same data file (may be multiple files, or independent table space files), the size of InnoDB tables is only limited by the size of the operating system file, generally 2GB.

2. Storage space MyISAM: MyISAM supports three different storage formats: static tables (default, but note that there can be no spaces at the end of the data, which will be removed), dynamic tables, and compressed tables. After the table is created and the data is imported, no more modification operations will be performed. Compressed tables can be used to greatly reduce the disk space occupation. InnoDB: Need more memory and storage, it will establish its dedicated buffer pool in the main memory for high-speed buffering of data and indexes.

3. Portability, backup and recovery MyISAM: Data is stored in the form of files, so it will be very convenient in cross-platform data transfer. During backup and restore, you can operate on a table separately. InnoDB: The free solution can be to copy data files, back up binlog, or use mysqldump, which is relatively painful when the data volume reaches dozens of gigabytes.

4. Transaction support MyISAM: The emphasis is on performance, each query is atomic, and its execution is faster than InnoDB type, but transaction support is not provided. InnoDB: Provides advanced database functions such as transaction support transactions and external keys. Transaction-safe (ACID compliant) type table with transaction (commit), rollback (rollback) and crash recovery capabilities (crash recovery capabilities).

5. AUTO_INCREMENT MyISAM: It can build a joint index with other fields. The auto-growth column of the engine must be an index. If it is a composite index, the auto-growth may not be the first column. It can be sorted according to the previous columns and then incremented. InnoDB: InnoDB must contain only the index of this field. The auto-growth column of the engine must be an index. If it is a composite index, it must also be the first column of the composite index.

6. Table lock difference MyISAM: Only table-level locks are supported. When the user operates the myisam table, select, update, delete, and insert statements will automatically lock the table. If the table after locking meets the insert concurrency, you can Insert new data at the end of the table. InnoDB: Support for transactions and row-level locks is the biggest feature of InnoDB. Row locks greatly improve the new capabilities of multi-user concurrent operations. But InnoDB's row lock is only valid in the WHERE primary key, and the WHERE of the non-primary key will lock the entire table.

7. Full-text index MySql full-text index MyISAM: supports FULLTEXT type full-text index InnoDB: does not support FULLTEXT type full-text index, but InnoDB can use sphinx plug-in to support full-text index, and the effect is better.

8. Table primary key MyISAM: Allows the existence of tables without any indexes and primary keys. The indexes are the addresses where rows are stored. InnoDB: If the primary key is not set or a non-empty unique index is not set, a 6-byte primary key (not visible to the user) will be automatically generated. The data is part of the main index, and the additional index stores the value of the main index.

9. The specific number of rows in the table MyISAM: Store the total number of rows in the table, if you select count() from table; the value will be taken out directly. InnoDB: The total number of rows of the table is not saved. If you use select count(*) from table; it will traverse the entire table, which consumes a lot of money. However, after adding the wehre condition, myisam and innodb handle the same way.

10. CRUD operation MyISAM: If you perform a large number of SELECTs, MyISAM is a better choice. InnoDB: If your data performs a large number of INSERT or UPDATE, for performance reasons, you should use InnoDB tables.
11. Foreign key MyISAM: not supported