Shulou(Shulou.com)06/01 Report--

The editor in this issue will bring you the difference between InnoDB and MyISAM in MySQL, and analyze and describe it from a professional point of view. I hope you can get something after reading this article.

InnoDB storage engine

InnoDB, one of the database engines of MySQL, is now the default storage engine of MySQL and one of the standards for publishing binary for MySQL AB. InnoDB was developed by Innobase Oy and acquired by Oracle in May 2006. Compared with traditional ISAM and MyISAM, the most important feature of InnoDB is that it supports ACID-compatible transaction (Transaction) function, similar to PostgreSQL.

InnoDB provides MySQL with transaction security (transaction-safe (ACID compliant)) tables with transaction (transaction), rollback (rollback), crash repair (crash recovery capabilities), and multi-version concurrency control (multi-versioned concurrency control).

MyISAM storage engine

MyISAM is the default storage engine (before Mysql5.1). It is based on older ISAM code, but has many useful extensions. (note that ISAM is not supported in MySQL 5.1). Each MyISAM is stored as three files on disk, and the name of each file starts with the name of the table, and the extension indicates the file type.

The difference between InnoDB and MyISAM

Business

For the atomicity of database operations, we need transactions. Ensure that a set of operations either succeed or fail, such as the ability to transfer money. We usually put multiple SQL statements between begin and commit to form a transaction.

InnoDB supports it, but MyISAM does not.

Indexes

To optimize the speed of the query, sort and match lookups, we need an index. For example, all people's names are stored sequentially from the acronym of Amurz, and when we look for zhangsan or bit 44, we can quickly locate the location we want to find.

InnoDB is a clustered index, and the data is bound to the clustered index of the primary key, which is very efficient through the primary key index. If you look through the secondary index of other columns, you need to find the clustered index first, and then query all the data, requiring two queries.

MyISAM is a nonclustered index, the data files are separated, and the index holds pointers to the data.

From InnoDB 1.2.x to MySQL5.6, both support full-text indexing.

Auto_increment self-increasing

For self-incrementing fields, InnoDB requires that the column must be an index and must be the first column of the index, otherwise an error will be reported:

Mysql > create table test (- > an int auto_increment,-> b int,-> key (bmeme a)->) engine=InnoDB;ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Just replace the order of (b) with (a).

MyISAM can combine this field with other fields in any order to form a federated index.

Number of table rows

A common requirement is to see how many pieces of data there are in the table, and we need select count (*) from table_name.

InnoDB does not save the number of table rows and requires a full table scan. MyISAM is saved as a variable and reads the value directly, which is faster. At that time, when there was a where query, it was the same.

Storage

The files of the database need to be stored on disk and read into memory when the application needs it. It generally contains data files and index files.

InnoDB is divided into:

.frm table structure file .ibdata1 shared tablespace .ibd table exclusive space.redo log file

MyISAM is divided into three files:

.frm storage table definition. MYD storage table data. MYI storage table index

Primary key

Because of InnoDB's clustered index, it automatically generates a primary key if it does not specify a primary key.

MyISAM supports the existence of tables without primary keys.

Foreign key

To solve the dependency of complex logic, we need foreign keys. For example, the entry of college entrance examination results must belong to a certain classmate, so we need the foreign key with the admission card number in the college entrance examination results database.

InnoDB supports it, but MyISAM does not.

Execution speed

If your operation is a large number of query operations, such as SELECT, using MyISAM performance will be better.

If most of the operations are delete and change, use InnoDB.

The indexes of InnoDB and MyISAM are both B+ tree indexes, and the primary key of the data can be queried through the index. Those who are not familiar with B+ tree can check the principle and algorithm of MySQL InnoDB index. The main performance difference between the two is that they are handled differently after querying the data primary key.

InnoDB caches indexes and data files, and generally uses 16KB as a minimum unit (data page size) to interact with the disk. InnoDB actually gets the ID of the primary key after querying the index data. It needs to find all the data of the row in the data page in memory, but if the data is not loaded hot data, it also needs to find and replace the data page. This may involve multiple Istroke O operations and in-memory data lookups, resulting in high time-consuming.

On the other hand, the MyISAM storage engine only caches index files, not data files, and the cache of its data files directly uses the cache of the operating system, which is very unique. At this point, the same space can load more indexes, so when the cache space is limited, there will be fewer index data page substitutions for MyISAM. According to what we know earlier, MyISAM files are divided into MYI and MYD. When we find the primary key ID through MYI, we actually get the offset offset of the MYD data file, which is much faster than the InnoDB addressing mapping.

But because MyISAM is a table lock and InnoDB supports row locks, InnoDB has much better concurrency performance than MyISAM when a large number of writes are involved. At the same time, InnoDB also uses MVVC multi-version control to improve concurrent read and write performance.

Delete delete data

When you call delete from table, MyISAM rebuilds the table directly, and InnoDB deletes one by one, but you can use truncate table instead. Reference: there are two ways and differences for mysql to empty table data.

Lock

MyISAM only supports table locks, locking the entire table for each operation.

InnoDB supports row locking, locking the minimum number of row data per operation.

Table locks consume less resources than row locks, and there is no deadlock, but the concurrency performance is poor. Row locks consume more resources, are slower, and deadlocks may occur, but because of the small granularity of locking, less data, and good concurrency performance. If a statement in InnoDB cannot determine the scope to scan, the entire table is also locked.

When a deadlock occurs in a row lock, the number of rows affected by each transaction is calculated, and the transaction with a smaller number of rows is rolled back.

Data recovery

There is no quick and safe recovery after a MyISAM crash. InnoDB has a complete recovery mechanism.

Data caching

MyISAM caches only index data and queries data through indexes. InnoDB not only caches index data, but also caches data information, reads the data page by page into the cache pool, and updates it according to the LRU (least recently used by Latest Rare Use) algorithm.

How to choose a storage engine

The statements for creating the table are all the same, and only the final type specifies the storage engine.

MyISAM

1. Query a large number of total count

2. Frequent queries and infrequent insertions

3. No transaction operation

InnoDB

1. High availability or transaction is required

2. Frequent table updates

The above is the difference between InnoDB and MyISAM in MySQL shared by Xiaobian. If you have similar doubts, it does not hinder understanding with reference to the above analysis. If you want to know more about it, please follow the industry information.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.