MyISAM and InnoDB engines

MyISAM

MyISAM engine is the default storage engine for MySQL relational database management system (before the 5.5.xx version), this MySQL table storage structure from the ISAM code to extend a lot of useful features, in the new version of MySQL InnoDB engine due to the integrity of the object reference, has been more high concurrency and other advantages began to gradually replace MyISAM engine

MyISAM Engine Features:

1. Do not support things (things refer to a logical set of actions that make up each unit of this set of operations, either all succeed or all fail)

2. Table level Lock (update is lock entire table). The locking mechanism is a table-level index, which can make the implementation cost of the lock very small, but also greatly reduces its concurrency performance

3, read and write each other blocking. Not only will the read be blocked at the time of writing, MyISAM will also block the write while reading, but the read itself will not block other reads.

4. Cache indexes only. MyISAM can key_buffer_size cache indexes to greatly improve access performance and reduce disk IO, but this buffer caches only indexes and does not cache data

5, the reading speed is fast, occupies a relatively small amount of resources

6. Foreign KEY constraints are not supported, but full-text indexing is supported

7, MyISAM engine is the default storage engine before mysql5.5.xx.

MyISAM Application Scenario:

1, do not need to support business services. such as bank transfer is certainly not supported

2, generally used for reading more data references. Frequently read and write the scene does not apply, reads more or writes many can.

3, read and write concurrent access to a relatively low business (read-only or write high concurrency can also) (locking mechanism problems)

4, data modification of relatively small business (blocking problems)

5, to read the main business. For example: www, blog, picture information data, user database, commodity library and other business

6, the data consistency requirements are not very high business

7, the hardware resources are relatively poor server can use MyISAM

MyISAM Engine Tuning Essentials

1, set the appropriate index (caching mechanism)

2, adjust the read and write priority, according to the actual needs to ensure that important operations more priority implementation

3. Enable deferred insertion to improve high-volume write performance (write-down is frequent and as many write-once as possible)

4, as far as possible sequential operation let insert data are written to the tail, reduce blocking

5, decomposition of large operations, reduce the blocking time of a single operation

6, reduce the number of concurrent (reduced MySQL access), some high concurrency scenarios through the application of queuing queue mechanism

7, for relatively static (change infrequently) database data, make full use of query cache or memcached cache service can greatly improve access efficiency

8, can be master-slave synchronization in the main library using InnoDB, from the library to use MyISAM

###########################################################################################

###########################################################################################

###########################################################################################

InnoDB engine

The InnoDB engine is another important engine of the MySQL database that is becoming the standard of the current MySQL database release, is included in the binary installation package, and compared to other engines, the advantages of the InnoDB engine are compatible with acid-based transactions, which are already parameter integrity (that is, support for foreign keys).

InnoDB Engine Features

1, support business. Supports 4 transaction isolation levels and supports multi-version reads.

2, row-level locking (update is generally locked the current row). By indexing, full table scans still cause table locks. Notice the effect of clearance lock

3. Read-write blocking is related to transaction isolation level

4, with very efficient cache characteristics, can cache the index, but also can cache the data

5. The entire table and primary key are stored in cluster mode, forming a balance tree

6. All Secondary index will save primary key information

7, support partition, table space, similar to Oracle database

8, support foreign KEY constraints, does not support full-text index 5.5 ago. After 5.5 support

InnoDB Engine Application Scenario

1. Business that requires transaction support (with good transactional characteristics)

2, row-level locking for high concurrency has a good adaptability, but need to ensure that the query is done by indexing

3, data read and write and update more frequent scenes, such as bbs/sns/Weibo, etc.

4, data consistency requires a higher business. such as bank transfers, etc.

5, the hardware device memory is large, can use InnoDB better cache function to provide memory utilization, minimizing disk IO

6, compared to the MyISAM engine, InnoDB engine consumes more resources, the speed is not MyISAM engine fast

InnoDB Engine Tuning Essentials

1, the primary key as small as possible, to avoid the secondary index to bring too much space burden

2, avoid the full table scan, because the full table scan will still use the table lock

3. Cache indexed data as much as possible, improve response speed and reduce disk IO consumption

4, in large batches of small inserts, try to control their own transactions and do not use autocommit automatic submission. There is a switch to control the way of submission

5, reasonable set innodb_flush_log_at_trx_commit parameter value, do not excessive pursuit of security

6, avoid the primary key update, because this will bring a lot of data movement

This article is from the "Struggle Bar" blog, please be sure to keep this source http://lvnian.blog.51cto.com/7155281/1699850

MyISAM and InnoDB engines