MySQL Performance Tuning Storage Engine

Original: http://bbs.landingbj.com/t-0-246222-1.html http://bbs.landingbj.com/t-0-245851-1.html

Optimization of the MyISAM storage engine in MySQL performance tuning.

The points to consider in optimizing the MyISAM storage engine are as follows:

As far as possible index, MyISAM only cache index does not cache data;

Adjust read-Write priority according to actual demand;

Delay insertion, use Insert delay, reduce and SELECT compete

Data order operation, let insert all to tail, reduce and select competition;

Decomposition of large operations, the large operations into multi-step small operation, to prevent long-time locking;

Reduce the number of concurrent, table lock will lead to fierce competition, through the queuing mechanism to improve efficiency;

Make the most of query cache: for static data, use query cache as much as possible.

In addition MyISAM does not require transaction support, and is read-oriented. MyISAM reading efficiency is higher, but MyISAM has a disadvantage, that is, the table is prone to bad, need regular repair.

In the online environment, InnoDB is suitable for the following scenarios:

1. Need for business support;

2. concurrency is large;

3. Data changes are more frequent;

4. Data consistency requirements are high;

5 Hardware Device memory is large, far greater than the amount of index data;

The points to consider in optimizing the InnoDB storage engine are as follows:

1. Primary key is as small as possible: all non-primary key indexes need to store the primary key;

2. Index integration, reduce redundant index, reduce the amount of data;

3. Avoid full table scan, because it will cause the table lock;

4. Try to control the affairs and close the Aotucommit;

5. Cache all data and indexes as much as possible;

6. Set Innodb_flush_log_at_trx_commit rationally;

7. Make full use of index to avoid table lock;

8. Avoid primary key updates.

InnoDB and MyISAM have their own strengths, no better than which, as long as the actual problem can be solved. What kind of storage engine to use in a production environment needs to be considered in many ways. In fact, many companies are mixed with two kinds of storage engines.

MySQL Performance Tuning Storage Engine