The difference between the MYSQL MyISAM engine and the InnoDB primer engine

MyISAM and InnoDB are the two main engines of the MySQL database, MyISAM is the default engine prior to the MYSQL5.5 version, and InnoDB is the MySQL 5.5 version of the default engine.

The differences between the two engines are as follows:

1. Transaction processing
   

   InnoDB supports transaction processing features, A transaction has the following 4 properties, often referred to simply as the acid property of a transaction.

   Atomicity (atomicity): A transaction is an atomic manipulation unit whose modifications to the data are either all executed or not executed.

   Conformance (Consisten): Data must be in a consistent state at the beginning and completion of a transaction. This means that all relevant data rules must be applied to transaction modifications to maintain the integrity of the data; At the end of a transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must also be correct. The

   Isolation (Isolation) database system provides a certain isolation mechanism to ensure that transactions are performed in a "stand-alone" environment that is unaffected by external concurrency operations. This means that the intermediate state in the transaction process is not visible to the outside, and vice versa.

   Persistence: After a transaction is complete, its modification to the data is permanent, even if a system failure persists. The

   MyISAM does not support transaction processing.

2. Select, update,insert,delete action

   MYISAM: If a large number of select,myisam is a better choice

   INNODB: If your data performs a large number of inserts or update, you should use InnoDB for performance reasons.

3. Lock mechanism different

   INNODB is a row-level lock, MyISAM is a table-level lock. The

4. Query table has a different number of rows

   MYISAM records the Total row count of the table, so when COUNT (*) is relatively fast.

   INNODB: Do not save the exact number of rows in the table, that is, COUNT (*) to perform a full table scan of the table.

5. MYISAM: Each MYISAM table is stored as three files on disk. The first file name begins with the name of the table, and the extension indicates the file type. The

   . frm file stores the table definition. The

   Data file has an extension of. MYD (MYData). The extension of the

   Index file is. MYI (myindex)

INNODB: In two ways, one for shared tablespace, this is all tables and undo, and the index is placed in one or more tablespace files with a maximum file size of 64TB. The second is a separate table space for each table.

6.MYISAM Table storage method is hot table, heap table.

The InnoDB table is stored as an IoT table, (Index organization table)

7.MyISAM uses B+tree as the index structure, the data of the leaf node holds the pointer, that is the address of the record. The primary key index and the secondary index are the same.

InnoDB also uses B+tree as the index structure, and it's important to note that for primary key indexes, InnoDB uses a clustered index, and the InnoDB data file itself is the index file. MyISAM, the primary key index and the data file are separated.

InnoDB data files, to aggregate the index by primary key, this requires that the InnoDB table must have a primary key (MyISAM can not). If you do not explicitly specify a primary key, InnoDB automatically selects a field that uniquely identifies the record as the primary key, such as the Auto_increment field, and if no such column exists, InnoDB automatically generates an implied field as the primary key, which is a 6-byte implicit field, which is a long shape.

For the secondary index of InnoDB, the data of the leaf node holds the value of the primary key. This means that using a secondary index to locate a record requires two indexes: first the value of the primary key is found using the secondary index, and a record is found using the primary key index, based on the value of the primary key.

Why should the secondary index of INNODB be designed like this?
If the secondary index data holds the row pointer, when the row moves or the data page splits, the value of the data field row pointer needs to be updated, which increases maintenance costs. There is no problem with the value of the primary key in data. Row movement and Data page splitting, the primary key index is automatically updated. The value of the Data Association primary key, which does not need to be updated, is equivalent to adding an indirection layer. This indirect layer has a small impact on performance because it is very fast to locate records through the primary key.

Understanding the index implementation of InnoDB, there are several places to note:
Do not use a long field as the primary key, because the secondary index uses the primary key index to locate the record, which is too long, uses more memory, and affects performance.
Using a monotone field as the primary key, especially insert, b+tree maintenance is expensive if it is non-monotonic.

This is a good explanation, isolation level repeatable-read, not using an index to lock the entire table, using an index (primary key index or secondary index) will only lock the corresponding row.

In the query execution plan, there is a field type, Eq_ref, that uses the primary key index to navigate directly to the record. Ref indicates that the secondary index is used first, the value of the primary key is found, and the primary key index is used to navigate to the record.

8.MYISAM does not support foreign keys, INNODB supports foreign keys

6mmy, My1, MyISAM : Each MyISAM is stored on disk as three files. The first file name begins with the name of the table, and the extension indicates the file type.

The difference between the MYSQL MyISAM engine and the InnoDB primer engine