MyISAM and InnoDB Index implementation comparison

InnoDB Features:

support for ACID transactions (with Commit, rollback, and crash resiliency), support for stored procedures, views, support for row-level locking , support for foreign keys , maximum performance when handling huge amounts of data (it can be said that its CPU efficiency is unmatched by other disk-relational database engines), The table can be any size, does not support full-text type index , insert buffer, two write, Adaptive Hash Index (three features in detail see: InnoDB three key Features (http://blog.csdn.net/z702143700/article/ DETAILS/46049101).

Application Scenarios:

1. An INSERT, update-based application.
2. In applications that require large, high-performance databases.
3. Updates a dense table. The InnoDB storage engine is ideal for handling multiple concurrent update requests.
4. Transaction. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.
5. Automated Disaster recovery. Unlike other storage engines, the InnoDB table can automatically recover from a disaster.
6. FOREIGN KEY constraints. MySQL supports the foreign key storage engine only InnoDB.
7. Supports automatic increment of column Auto_increment property.

Note: The row lock of the InnoDB table is not absolute, and if MySQL cannot determine the scope to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "%aaa%", select The table is also locked by COUNT (*), order BY, and so on. A row-level lock is usually used only when the index is involved .
For example: When the Where condition contains an indexed field, it is a row lock. All other cases are table locks.

MyISAM Features:

High query speed, table-level lock, blob and text columns can be indexed, not supported transactions, foreign keys are supported, full-text indexing, support 3 different storage formats: static (fixed-length) tables, dynamic tables, compressed tables;

A static table is the default storage format. The fields in the static table are non-variable length fields, so each record is fixed-length, the advantage of this storage method is that the storage is very fast, easy to cache, the fault is easy to recover, the disadvantage is that the space occupied is usually more than the dynamic table. A static table complements the spaces defined by the column definition when the data is stored, but does not receive these spaces when accessed, and these spaces are removed before they are returned to the app. It is also important to note that, in some cases, you may need to return a space after the field, and the following space will be automatically processed when using this format.
Dynamic tables contain variable-length fields, where records are not fixed lengths, so storage has the advantage of less space, but frequent updates to delete records can be fragmented, requiring periodic execution of optimize table statements or MYISAMCHK-R commands to improve performance, and recovery is relatively difficult in the event of a failure.
Compressed tables are created by the Myisamchk tool and occupy very little space because each record is individually compressed, so there is very little access expense.

Application Scenarios:

1. There is no requirement for transactional integrity or a select-based application.
2. No transaction requirements, but high efficiency requirements of the application.

NOTE: The null value is allowed in the indexed column, which accounts for 0-1 bytes per key.

InnoDB and MyISAM comparison:

2. InnoDB can use transaction logs for data recovery, which is faster. While MyISAM may take hours or even days to do these things, InnoDB only takes a few minutes. But in some scenarios MyISAM seems more convenient and efficient because it can copy the frm directly. The myd,myi file is then ready to be used when it is started in the data for the version. For example, data tables are corrupted, migrated, and restored as long as the files are copied. and InnoDB to consider the effect of dictionary data, the way to copy files is invalid, if you export. sql file, it will take a lot of time.
4. The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT () from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the count () statement contains a where condition, the operation of the two tables is the same.
6. For fields of type auto_increment, InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with other fields.
8. Delete from table, InnoDB does not reestablish the table, but deletes one row at a time.
10. The LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.
12. MyISAM indexes and data are separate, and the indexes are compressed, and memory usage increases a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.
14. The InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory.
16. The MyISAM table uses the B-tree index, the MyISAM index file and the data file are detached, and the index file only holds the address of the data record, also called the nonclustered index.
18. InnoDB also uses B+tree as the index structure, and the tree's leaf node data field holds the full data record. Its index key is the primary key of the data table, so the InnoDB table data file itself is the primary index, also called the "Clustered index". MyISAM and InnoDB Index for detailed comparison, see blog http://blog.csdn.net/z702143700/article/details/46049101

MyISAM and InnoDB Index implementation comparison