MySQL storage engine InnoDB and MyISAM

Mysql5.5+ 's version of the default engine is InnoDB, the earlier MySQL version of the default storage engine is MyISAM

InnoDB

1. Transactional operations
2. Foreign key operation
3. Row-level lock, which is when there is a where condition. When there is no where, it is also a table lock when searching in that range. Provides the same consistent, non-lock reads as Oracle, which increases the number of concurrent read users and improves performance without increasing the number of locks.
4. Write fast, this is based on the update operation of the index

The 5.InnoDB is designed to maximize performance when dealing with large volumes of data, and its CPU utilization is the most efficient of all other disk-based relational database engines, and technically, InnoDB is a complete database system placed in the MySQL backend, InnoDB Establish its dedicated buffer pool in main memory for caching data and indexes. InnoDB the data and index in the table space, may contain multiple files, which is different from other, for example, in MyISAM, the table is stored in a separate file. The size of the InnoDB table is limited only by the size of the operating system file, typically 2 GB.
InnoDB All tables are stored in the same data file Ibdata1 (or possibly multiple files, or stand-alone tablespace files), relatively poorly backed up, the free scheme can be copy data files, backup Binlog, or mysqldump.

MyISAM

1. Table-level lock
2. Fast reading Speed
3. Better index compression
4. Better and faster auto_increment processing

One drawback of MyISAM is that it cannot be recovered safely after a crash.

MyISAM will store the table in two files: Data files and index files, respectively. MyD and. Myi as extensions

COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. While the primary key query will be quite fast under InnoDB, it is important to be careful that if our primary key is too long it can cause performance problems. A large number of inserts statements will be faster under MyISAM, but updates will be faster under innodb-especially when concurrency is large.

The two engines, MyISAM and InnoDB, also have different ways of implementing indexes.

Myiasm is the use of B-tree structure to implement the main code, non-main code, a unique index.

InnoDB is the use of B + tree structure to implement the main code, with B-tree to achieve non-master code.

The following are some of the details and the specific implementation differences:

1. InnoDB does not support indexes of fulltext types.

2. InnoDB does not save the exact number of rows in the table, that is, when executing select COUNT (*) from table, InnoDB will scan through the entire table to calculate how many rows, but MyISAM simply reads out the number of rows saved. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.

3. 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.

4. When you delete from table, InnoDB does not reestablish the table, but deletes one row at a time.

5, LOAD table from the master operation on InnoDB is not working, the solution is to change the InnoDB table to MyISAM table, import data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign keys) of the table does not apply.

6, MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding improved 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.

7, InnoDB table row lock is not absolute, if MySQL cannot determine the scope to scan when executing an SQL statement, the InnoDB table also locks the entire table, such as the Update table set num=1 where name like "%aaa%"

Use two options: If your data performs a large number of inserts or update, for performance reasons,
You should use the InnoDB table. If executing a lot of select,myisam is a better choice. If you need to use transaction processing,
But the original data table using MyISAM, you need to change to BDB or InnoDB, so based on the MyISAM program,
After changing the type to InnoDB, the program does not have to be changed ... In summary, any kind of table is not omnipotent, only appropriate for the business type to select the appropriate table type, to
Maximize the performance advantage of MySQL.

Reference: http://www.cnblogs.com/myblog1314/archive/2013/06/05/3118370.html;

http://blog.csdn.net/xifeijian/article/details/20316775

MySQL storage engine InnoDB and MyISAM (RPM)