What are InnoDB and MyISAM in MySQL?

Question

What are InnoDB and MyISAM in MySQL ?

Answer 1

InnoDB and MYISAM are storage engines for MySQL .

These two options differ from the lock implementation: InnoDB locks a specific row in the table, and MYISAM locks the entire MySQL table.

You can specify the type by specifying MYISAM OR InnoDB when creating the table in the database.

Answer 2

Take a look

InnoDB and MyISAM

InnoDB is a storage engine for MySQL, included in the standard form in all current binaries distributed by MySQL AB. this is a major improvement over other storage available for use with MySQL support for ACID-compatible transactions

MyISAM is the default storage engine for the MySQL relational database management system version up to 5.5 1 . It is based on earlier ISAM code, but has many useful extensions. The main disadvantage of MyISAM is the lack of transaction support. MySQL versions 5.5 and higher have switched to the InnoDB engine to provide referential integrity constraints and higher concurrency.

Answer 3

These are storage mechanisms.

http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

MyISAM: The default MySQL storage engine and the one most used on the Web, data warehouses, and other application environments. MyISAM is supported in all MySQL configurations and is the default storage engine unless you have configured MySQL to use differently.

InnoDB: A transaction-compatible security mechanism (ACID) for MySQL that has the ability to commit, roll back, and recover from a failure to protect user data. InnoDB row-level locking (without escalating to larger locks) and consecutive non-blocking readings in an Oracle style increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I / O for common primary key queries. To maintain data integrity, InnoDB also supports FOREIGN KEY referential integrity restrictions.

Answer 4

I would like to add that the ability to specify a specific storage mechanism for each table is one of the key advantages of MySQL (in addition to ease of use and good performance without tuning). For all transactions where transactions are needed, just stick with InnoDB. However, MyISAM can really speed up the process when transactions are not needed in certain situations - and requires less disk space and RAM compared to InnoDB.

However, InnoDB is getting better:

Performance and Scalability Enhancements for InnoDB 1.1

Answer 5

MyISAM does not follow the ACID, not InnoDB, which follows transactions to maintain data integrity.

MyISAM supports parallel inserts: if the table does not have free blocks in the middle of the data file, you can INSERT new lines at the same time that other threads are reading from the table. MySqlDoc

This is why MyISAM is faster and takes up less space. For example, MySQL MyISAM Storage Engine does not support tranactions. MySQL MYISAM restrictions There is a bit called parallel insertion By default, the variable is set to 1, and matching inserts are processed as just described. If it is set to 0, simultaneous inserts are disabled. If set to 2, simultaneous inserts at the end of the table are allowed even for tables that have deleted rows. The INSERT statement can be performed to add rows to the end of the table with simultaneous selection if there are no holes / deleted rows in the middle of the table (during parallel insertion).

The default isolation level of og mysql InnoDB is "Read Repeatable". There are no transactions for MyISAM. InnoDB uses row-level locking, while MyISAM can only use table-level locking, so InnoDB has better crash recovery than MyISAM. In order to avoid concurrency effects, you need to manually obtain a table level lock manually in MyISAM.

Answer 6

InnoDB by default is NOT myISAM https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html "InnoDB is the default MySQL storage engine. If you have not configured another default storage engine, issue an instruction CREATE TABLE without an ENGINE = clause creates an InnoDB table "

Answer 7

When your MySQL server crashes, data can be recovered much more easily from the MyISAM table set than from this large InnoDB transaction file. Each MyISAM table has a separate file, and if no write operations were performed to this table during a crash, it will not be fully affected. In the case of InnoDB, the entire transaction file of the entire MySQL server must be reindexed or whatever it does after the failure. It can get pretty dirty.

Answer 8

InnoDB is the transactional storage engine of MySQL, while MyISAM is the non - transactional storage engine. In other words, InnoDB follows ACID properties to maintain data integrity, but MyISAM does not follow ACID properties, thus not maintaining data integrity.

In an InnoDB (transactional) table, transaction changes can be easily rolled back if rollback is required. But changes made to the MyISAM table (not transactional) cannot be undone when a transaction rollback is required.

For example, you want to transfer money from your current account to a savings account. This is done using a transaction that includes 5 queries.

 1 START TRANSACTION; 2 SELECT balance FROM checking WHERE customer_id = 10233276; 3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276; 4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276; 5 COMMIT; 

Suppose the process crashes in step 4. If the InnoDB table were used here, the rollback would cancel the changes and you would be free from the risk of losing money. Literally, the table does not know about any failure, since the changes will not be made to the table if step 5 is not successfully completed.

But in the case of the MyISAM table, transactional changes cannot be undone when a rollback is called or in the event of a failure leading to a transaction failure. This means that if the transaction crashed in step 3, the money will be deducted from your current account. But the money would not be added to your savings account.

Courtesy example: "High Performance MySQL: Optimization, Backup, and Replication" - book by Arjen Lenz, Derek J. Balling, Jeremy Zavodny, Peter Zaitsev and Vadim Tkachenko