Summary and comparison of InnoDB and MyISAM

Summary and comparison of InnoDB and MyISAM

Mysql database engine has been stated in my blog, but it is only a simple description of all engines. InnoDB and MyISAM database engines are the most commonly used, it is necessary to provide a detailed and comprehensive introduction and comparison so that we can better choose the database engine as needed.

Features of InnoDB:

ACID transactions supported(With the ability to submit, roll back, and crash recovery), supports stored procedures, views,Supports row-level locking,Supports Foreign keysIt has the maximum performance when processing a large amount of data (it can be said that its CPU efficiency is unmatched by other disk-based relational database engines), the table can be any size,Full-text indexes are not supported.Insert buffer, secondary write, adaptive hash index (three features are described in detail: InnoDB three key features (http://blog.csdn.net/z702143700/article/details/46049101 ).

Application scenarios: INSERT and UPDATE-based applications. In applications that require high-performance large databases. Update intensive tables. The InnoDB Storage engine is particularly suitable for processing multiple concurrent update requests. Transaction. The InnoDB Storage engine is a standard MySQL storage engine that supports transactions. Automatic disaster recovery. Unlike other storage engines, InnoDB tables can be automatically recovered from disasters. Foreign key constraint. MySQL only supports the InnoDB Storage engine. The AUTO_INCREMENT attribute can be automatically added.

Note: The row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope of the scan when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %", select count (*), order by, and so on will also lock the table. WhileRow-level locks are generally used only when indexes are involved..
For example, when the where condition contains an index field, it is a row lock. In other cases, table locks are used.

MyISAM features:

High query speed, table lock; BLOB and TEXT columns can be indexed; transactions are not supported, foreign keys are not supported; full-TEXT indexing is supported, and three different storage formats are supported: static (fixed length) tables, dynamic tables, and compressed tables;

Static tables are stored in the default format. Fields in a static table are non-variable-length fields, so that each record has a fixed length. The advantage of this storage method is that it is very fast to store, easy to cache, and easy to recover when a fault occurs; the disadvantage is that it usually occupies more space than dynamic tables. When data is stored in a static table, spaces are supplemented according to the width definition of the column, but these spaces are not obtained during access. These spaces are removed before they are returned to the application. At the same time, note: In some cases, the space after the field may be returned. When this format is used, the Space following it will be automatically processed.
A dynamic table contains variable-length fields and records do not have a fixed length. In this way, the storage space is relatively small, but fragments are generated when records are updated or deleted frequently, you need to regularly execute the optimize table statement or myisamchk-r command to improve performance, and it is relatively difficult to recover when a fault occurs.
The compressed table is created by the myisamchk tool, which occupies a very small space, because each record is compressed separately, so there is only a small access cost.

Application Scenario: an application that does not require transaction integrity or is dominated by SELECT. No transaction requirements, but applications with high efficiency requirements.

Note: The NULL value is allowed in the indexed column, which occupies 0-1 bytes of each key.

Comparison between InnoDB and MyISAM: InnoDB can use transaction logs for data recovery, which is faster. However, MyISAM may take several hours or even a few days to do these tasks. InnoDB only needs a few minutes. However, in some scenarios, MyISAM seems to be more convenient and efficient, because it can directly copy the frm. MYD and MYI files, and then start the version data to use it. For example, if the data table is corrupted, you only need to copy the file to migrate and restore the data. InnoDB needs to consider the impact of dictionary data, and the method of copying files is invalid. If you export A. SQL file, it will take a lot of time. InnoDB does not save the specific number of rows of the table, that is, execute select count ( ) From table, InnoDB needs to scan the entire table to calculate the number of rows, but MyISAM only needs to simply read the number of rows saved. Note that when count () When the statement contains the where condition, the operations on the two tables are the same. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields. When deleting FROM table, InnoDB does not create a new table, but deletes a row. The load table from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys. The indexes and data of MyISAM are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is closely bound with indexes and data. Without compression, Innodb is much larger than MyISAM. The InnoDB Storage engine maintains its own buffer pool to cache data and indexes in the primary memory. MyISAM tables use B-type tree indexes. MyISAM index files are separated from data files. index files only store the addresses of data records, which are also called "non-clustered indexes ". InnoDB also uses B + Tree as the index structure. The leaf node data field of this Tree stores complete data records. The index key is the primary key of the data table. Therefore, the InnoDB table data file itself is the primary index, also known as "clustered Index ".