MySQL management MyISAM and InnoDB storage engine simple comparison

Copyright QQ87006009 All

First, MyISAM engine:

1. The Separation service sector:

Because the isolation transaction level of the MyISAM is serial. Table-level locks are used, and things and full-text indexes are not supported. It is therefore not suitable for large concurrent, heavy-duty production systems.

2. Instance Crash recovery:

When the system goes down or the MySQL process crashes, the MyISAM engine table is vulnerable to corruption. Had to be repaired with external command myisamchk.

3. Allocating memory and using:

MyISAM engine memory primarily stores index information and does not hold data information, so the index information is accessed first when the table is retrieved, and then the data information is read on the go disk.

4, the use of CPU efficiency:

The MyISAM engine does not support multi-core CPUs, because it is a table locking mechanism that reduces the throughput of read and write.

5. How to store files R:

Each MyISAM will store data in three files on disk: Table structure (. frm), data file (. MYD), index file (. MYI).

--------------------------------------------------------------------------------------------------------------- ------------------

Second, InnoDB engine:

Copyright QQ87006009 All

1. Isolation transaction Level:

The REPEATABLE read isolation level is used by default. The features of table-level locks, row-level locks, and page-level locks are adopted, thus supporting large concurrent and high-load production systems.

2. Instance Crash recovery:

Commits with transactions, rollback, crash recovery mechanism. An acid storage engine that supports the security of things.

3. Memory allocation and use:

InnoDB has its own piece of memory area, which is mainly stored in transaction information, index information, insert buffer, Adaptive Hash Index, lock information, and data dictionary information.

4, the use of CPU efficiency:

The InnoDB engine supports multi-core cpu,5.5 support for 32-core CPUs, and currently 5.6 supports 64-core CPUs.

5, the file storage method:

The InnoDB engine typically places data and index files in a table space where a tablespace file can contain multiple files or multiple disk partitions.

MySQL management MyISAM and InnoDB storage engine simple comparison