Two common engines for MySQL

MyISAM Engine Features
1. Transactions are not supported (transactions refer to a logical set of operations that make up each unit of this set of operations, either fully successful or fail all)
2, table-level locking (Lock the entire table when data is updated): Its locking mechanism is table-level locking, although it can make the implementation cost of locking very small but also greatly reduce its concurrency performance.
3, read and write each other blocking: not only in the time of writing blocking read, MyISAM will also block the write when reading, but read itself does not block the other read.
4. Only the index is cached: MyISAM can significantly improve access performance by Key_buffer_size cache indexes to reduce disk IO, but this buffer caches only indexes, not data.
5, the reading speed is faster, 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.5

MyISAM engine for the production environment
1, do not need business support services (such as transfer will not, recharge payment)
2, generally for reading data more applications, reading and writing are frequently not suitable for the scene, read more or write less is appropriate.
3, read and write concurrent access to a relatively low business (pure read Write high concurrency can also) (locking mechanism problems)
4, data modification of the relatively small business (blocking problems).
5, to read the main business, such as: www,blog, image information database, user database, commodity library and other business
6, the data consistency requirements are not very high business.
7, the hardware resources are relatively poor machine can be used MyISAM.
Single operation of the database can use MyISAM, the so-called single is as far as possible to read, or write pure (insert,update,delete) and so on.

MyISAM Engine Tuning
1, set the appropriate index (caching mechanism)
2, adjust the read-write priority, according to the actual needs to ensure that important operations more priority.
3. Enable deferred insertion improves bulk write performance (reduces write frequency, as many data writes at once)
4, as far as possible sequential operation let insert data are written to the tail, reduce blocking.
5, decomposition of large long-time operation, reduce the blocking time of a single operation.
6, reduce the number of concurrent (reduce to MySQL access), some high concurrency scenarios through the application of queuing queue mechanism Q queue
7, for relatively static (change infrequently) database data, make full use of query cache or memcached cache service can greatly improve access efficiency.
grep query My.cnf
Query_cache_size = 2M
Query_cache_limit = 1M
Query_cache_min_res_unit = 2k

8. Count of MyISAM is particularly efficient when full-table scan, count with other conditions requires actual data access
Select COUNT (*) from Oldboy.zizeng;

9, can be master-slave synchronization of the main library using InnoDB, from the library using the MyISAM engine (but in order to master and slave switching is still to use InnoDB, so this is not realistic.) ）

InnoDB Engine Features
1, support transaction: Support 4 transaction ISOLATION level, support multi-version read.
2, row-level locking (usually lock the current line when updating): Through the index implementation, the full table scan will still be a table lock, pay attention to the impact of Gap lock.
3. Read and write blocking is related to the transaction isolation level.
4, with very efficient caching features: Can cache the index, can also cache data.
5. The entire table and primary key are stored in cluster mode, forming a balance tree.
6. All Secondary index will save the primary key information.
7, support partition, table space, similar to Oracle database.
8, support foreign KEY constraints, 5.5 did not support full-text indexing, and later supported.
9, compared with the MyISAM engine, the INNODB hardware resource requirements are relatively high.

InnoDB engine for the production environment
1. Business supported by transaction (with good transactional characteristics)
2, row-level locking for high concurrency has a good adaptability, but need to ensure that the query is done through the index.
3, the data read and write and update are more frequent scenes, such as: Bbs,sns, Weibo, and so on.
4, data consistency requires a higher business, such as: Recharge the transfer, bank card transfer.
5, the hardware device memory is large, can take advantage of InnoDB better cache capacity to improve memory utilization, reduce disk IO as much as possible.
Shared tablespace corresponds to physical data file
Separate table spaces correspond to physical data files
6, compared to myisam,innodb more consumption of resources, speed is not MyISAM fast

Tuning of the InnoDB engine
1, the primary key to be as small as possible, to avoid the secondary index to bring too much space burden.
2, avoid full table scan, because the table lock will be used
3. Cache all indexes and data as much as possible, improve response speed and reduce disk IO consumption.
4, in large batches of small inserts, as far as possible to control their own transactions and do not use autocommit automatic submission, there are switches to control the way of submission.
5, reasonable set innodb_flush_log_at_trx_commit parameter value, do not excessive pursuit of security.
If the value of Innodb_flush_log_at_trx_commit is 0,log buffer, the log file is written to disk every second, and no action is taken when committing the transaction.
6, avoid the primary key update, because this will bring a lot of data movement.

Show engines;

Collation of two engine profiles for InnoDB and MyISAM

Change engine
ALTER TABLE Oldboy engine = INNODB;
ALTER TABLE Oldboy engine = MyISAM;

Two common engines for MySQL