The detailed differences between MySQL storage engine-innodb and MyISAM are described

Current network, using MySQL database, access to a single machine can reach 1w/s, used for a long time, intends to summarize some of the knowledge of MySQL. Start with the most basic storage engine.

We currently use the storage engine is: InnoDB.

I. Differences in data storage:

MyISAM data, on disk, stored in three files, respectively, data files:. myd, index file:. MYI, structure file:. frm;

InnoDB data, is limited by the size of the operating system files, generally 2G.

Specific impact:

MyISAM database, the index and data are stored separately, the index is compressed, so the memory utilization ratio is high. Because the file exists disk, so, if the table data error operation, such as full table update, you can extract the corresponding data files from the previous backup, dump into SQL and back to the database, supplemented binlog, can be, more efficient than InnoDB.

InnoDB uses clustered indexes, data, and indexes, all in memory, while MyISAM simply puts the index file in memory. When querying data based on an index, InnoDB can get the corresponding data as soon as it is queried. And MyISAM, because the index and data are stored in different places, so need two times query;

MyISAM The index is saved in different data segments compared to InnoDB, so disk fragmentation is easier to produce.

2. Transaction processing aspects

InnoDB supports transaction processing, MyISAM does not support transactions.

If the data has transactional operations, be sure to look at the table's storage engine first.

3.select,update,insert,delete

MyISAM is suitable for performing a large number of select operations.

The InnoDB is more appropriate for update and insert operations, and the delete operation is the deletion of a row of rows without re-establishing the table.

Operation of the 4.auto_increment

MyISAM: Can be combined with other indexes to carry out the joint index;

InnoDB: The calculator that executes auto_increment is loaded into memory for calculation, must contain the index of this column;

5. The exact number of rows in the table

InnoDB does not save a specific number of rows, MyISAM has a specific number of rows to save, but if there is a where condition that restricts select count (*), the two tables behave the same way.

If usually in the operation, often need to check the entire table row number, that use MyISAM, performance will be better. If you need to use the condition to judge, then use InnoDB will be relatively better, after all, record the number of rows, but also need extra action.

6. Lock

MyISAM only provides table locks, InnoDB also provides row locks, but InnoDB also provides table locks if there is no way to determine the range to scan when executing SQL.

Table Lock: Lock the entire table at the same time, can read, but cannot write;

Table lock Characteristics: small cost, fast operation, large lock size, the probability of lock conflict, the concurrency is low;

Row Lock: Locks a row, the line can read, but not write;

Row lock Characteristics: large overhead, slow operation, small lock size, the probability of a lock collision is small, high degree of concurrency;

The InnoDB lock is implemented by adding locks to the index items, and the row-level locks only take effect if the data is retrieved through the index, otherwise the table lock is used;

Summarized as follows:

Scenarios for MyISAM:

1. The system reads more and writes less;

2. No transaction support is required;

3. For memory usage requirements, INNODB clustered index, memory consumption is very large, memory usage is high, the disk IO will generate higher frequency than MyISAM;

4. It is often necessary to export data to other machines;

For InnoDB scenarios:

1. System Update operation is more, the company's current game department, the vast majority of the use of InnoDB;

2. Need for business support;

3. Higher stability requirements, because MyISAM is a multi-file storage, prone to problems;

4. High backup consistency requirements;

Individuals tend to use INNODB, because Oracle acquired InnoDB early and later acquired MySQL, so InnoDB's features are sure to get better.

This article is from the "Think Precipitation" blog, please be sure to keep this source http://joezhengjinhong.blog.51cto.com/7791846/1582603

The detailed differences between MySQL storage engine-innodb and MyISAM are described