MySQL table Engine Optimization

MyISAM:

First, optimization parameters

This table engine only stores the index cache, not the data cache. You can set the cache size by setting key_buffer_size and key_bufer_block_size.

Key_cache_division_limit is the demarcation value between hot area and warm area in the LRU linked list, which is 1. The default value is 100, that is, only warm chain. Key_cache_division_limit tells MySQL how to divide the entire cachechain into hot area and warm area.

Key_cache_age_demoshold: controls the restriction that cacheblock is downgraded from hotarea to warmarea. The default value is 300. The minimum value is 100. The smaller the value, the more likely it is to be downgraded.

Use of Multi-key Cache

Starting from msyql4.1.1, MyISAM supports multiple key caches. You can set multiple keycaches according to different requirements. For example, put data that is frequently used but rarely updated into a keycaceh to prevent the data from being cleared in the public keycache. Put data that is not frequently used and often updated in another keycache.

MySQL officially recommends using three keycaches on a busy system [Key cache is the index cache ]:

One hotcache: uses a size of 20% to store frequently-used and infrequently updated table indexes.

A coldcache: uses the size of 20% to store frequently updated table indexes.

One warmcache: uses the remaining 60% space as the default keycache for the entire system.

Second, pre-load

When a new system is launched, the system will experience a high load for a short period of time because there is no data in the cache. You can use the pre-loading mechanism to load all indexes of a specified table to the memory.

Third, the impact of null on Statistics

The MyISAM index records the column information with a null value, but the index key with a null value occupies a very small amount of space. Therefore, null affects the Execution Plan Selection of the MySQL query optimizer. Therefore, MySQL provides the myisam_stats_method parameter, allowing us to determine the processing method for null values in the index. If myisam_stats_method = nulls_unequal, MyISAM considers every null value to be different when collecting statistics, the index for this field will be larger, that is, myISAM will think that the value of distinct will be more. Myisam_stats_method = nulls_equal is the opposite.

Fourth, concurrency Optimization

1. Enable concurrent_insert. If concurrent_insert is set to 1, parallel insertion can be performed at the end of the table when no free space is left for deleting records. When concurrent_insert is set to 2, parallel inserts are performed at the end regardless of whether there is any space left by the deletion.

2. Control the size of write operations to prevent excessive congestion.

3. Improve Write Performance by sacrificing read performance. Increases the write priority.

Fifth, other Optimizations

The optimize command is used to sort out the files in the MyISAM table, which is the continuous space occupied by the files. Generally, this command is executed after a large data deletion operation.

InnoDB:

The maximum differences between InnoDB and MyISAM are as follows:

1. Cache Mechanism

2. transaction support

3. Lock implementation

4. Data Storage Methods

Differences in integrity performance: InnoDB and MyISAM are significantly different in different scenarios because of these four points.

1. InnoDB Cache Optimization

The biggest difference between InnoDB and MyISAM is that InnoDB not only caches indexes, but also actual data. Therefore, InnoDB can use more memory to cache database information for identical databases. The premise is that there is enough memory.

Innodb_buffer_pool_size sets the size of a memory area with the largest requirements of the InnoDB Storage engine, which is directly related to the performance of the InnoDB Storage engine. Therefore, if we have enough memory, we can set this parameter to a large enough value, store as many InnoDB indexes and data as possible in the cache until all of them exist.

We can calculate the cache hit rate by using (bytes-innodb_buffer_pool_read_readests)/innodb_buffer_pool_read_requests * 100%, and adjust the innodb_buffer_pool_size parameter based on the hit rate to optimize it.

Innodb_log_buffer_size (global) is the buffer used by the transaction logs of the InnoDB Storage engine. When the MySQL write load is high, you can increase this parameter to improve Io performance.

2. Transaction Optimization

InnoDB supports the following transaction isolation levels:

1. Read uncommited: often called dirty read

2. Read commited: At this isolation level, no dirty read occurs. However, non-repeatable read or phantom read may occur.

3. Repeatable read: The default transaction isolation level of InnoDB. No dirty read occurs. There may be no non-repeatable read, but there may be phantom read.

4. serializable: serializable is the highest level in the standard transaction isolation level. The data seen at any time in the transaction is the state at the startup of the transaction. Whether or not there are other transactions that have modified some data and committed during this period. Therefore, Phantom reads does not appear in serializable.

When InnoDB modifies data, it only modifies the data in the buffer pool. Data in the buffepool is not synchronized to the disk after a transaction is committed. Continuous read/write and random read/write must be understood here. The process of writing data to a disk requires head addressing. Continuous read/write refers to writing the data to a continuous address space. InnoDB does not synchronize data to the disk every time, but performs continuous read/write to reduce disk Io after saving more data.

After a system crash, how does InnoDB use transaction logs for data recovery?

Assume that MySQL crash is used at a certain time, data in all bufferpools will be lost. Including the data that has been modified but cannot be refreshed to the disk. After MySQL starts from crash, InnoDB compares the checkpoint information of all records in the transaction log and the checkpoint information in each data file to find the log sequence number corresponding to the last checkpoint. Then, all the changes from the last checkpoint before the crash are re-applied through the change records in the transaction log. Synchronize all data files to the consistent state. Of course, changes to log files that have not been synchronized to the logbuffer can no longer be retrieved. In general, the larger the transaction log file settings, the better the IO performance of the system. However, when a crash occurs, the longer the recovery time.

Innodb_flush_log_at_trx_commit is set to 1 by default, indicating that every error is completed, the log thread will trigger the log thread to write data in the logbuffer to the file and notify the file system to synchronize files. This setting is secure and ensures that no submitted data is lost, whether it is MySQL crash, OS crash, or host power failure.

Third, data storage Optimization

The primary key of the cluster in InnoDB has been learned. However, clustering primary keys are also poor, Otherwise other database vendors will vigorously promote them. The biggest problem with clustering is the cost caused by index key update, not only the index data may be moved, but all the relevant record data will be moved. Therefore, do not update the primary key value of InnoDB for performance consideration.

Data in InnoDB, including tables, indexes, and various data structures of the storage engine, are stored in pages as the minimum physical unit. The default size of each page is 16 kb. Extent is a physical storage unit consisting of multiple consecutive pages. In general, each extent64 page. Each segment consists of one or more extents. Each segment stores the same data. Generally, each table is stored in a separate segment.

Page> extent> segment> tablespace, each of which is composed of one or more of the preceding ones. Tablespace is the maximum structure unit of InnoDB.

Optimization suggestions on primary keys,

1. To reduce the size of the secondary index, the smaller the storage space occupied by the primary key field, the better. Preferably integer. Of course, this is not absolute.

2. Do not update the primary key as much as possible.

3. Perform query operations based on the primary key as much as possible.

Fourth, other Optimizations

Autocommit is expected to be known. Set autocommit based on your actual situation.