Comparison between MySQL MyISAM and InnoDB Optimization Solutions

 

MySQL configuration principles rational configuration of MySQL the most reasonable use of MySQL for MyISAM or InnoDB for different application conditions rational configuration for my. CNF
Configuration, followed by reasonable settings for servers with 2 GB memory
Common options

MyISAM options

InnoDB options

Performance of MyISAM and InnoDB Storage Engines

MyISAM and InnoDB
The storage engine performance is not very different. For InnoDB, the main impact on performance is
If innodb_flush_log_at_trx_commit is set to 1, the efficiency of setting to 0 can be significantly improved. Of course, you can also use SQL
To set the performance.
It can also be seen that InnoDB is worth replacing MyISAM engine for development. After all
InnoDB has multiple database features, better data storage performance, and query performance. MySQL tips

 

 

------------------------------------------------------------ Storage engine--My I sam, I n ODB, others
MyISAM is more suitable for applications with less frequent inserts and more Wal (write Ahead Logging) queries)
InnoDB is suitable for environments with large concurrent writes and queries: supports transactions (acid space (not limited by large files)
Merge is actually mrg_myisam, which is applicable to the advantages of Multiple indexes of the same type.
The archive storage engine is used to store large amounts of data in a very small coverage area.
Index data. Is read-only. MyISAM Optimization
Total memory allocated to the MyISAM index cache by key_buffer_size
● Query_cache_size controls the total memory allocated to the query Cache
● Long_query_time: Set the slow query time.
● External-locking prohibits the use of external locks to prevent deadlocks
● How many requests can be piled up in a short time before the back_log temporarily stops responding to new requests. For example, ● the number of data tables cached by table_cache to avoid overhead of opening tables repeatedly
● Thread_cache_size indicates the number of reusable threads in the cache.
● Sort/join/read buffer size is allocated to each thread for processing Sorting/scanning table connections ● skip-bdb and so on, disable unnecessary engine InnoDB optimization ● if the database CPU usage is less than 70%, the pressure on MySQL may be that the disk is larger, but do not set the buffer pool to be equal to or greater than 80% of the physical memory.
● Put multiple modifications (insert/update/delete) in one transaction. However, you must also set innodb_flush_log_at_trx_commit to 0 (refresh every second) and 1 (Real-Time
Refresh), 2 (only write log files, do not brush new to the disk) ● To the InnoDB table to import data, first turn off the autocommit mode, otherwise it will be real-time ● more please view: http://imysql.cn /? Q = node/116
Innodb_buffer_pool_size control is assigned to include cluster data and
The total InnoDB cache memory on the secondary index page. The default value is 16 Mb.
● Innodb_additional_mem_pool_size Control
The buffer required for sorting data dictionaries in InnoDB. The default value is 1 MB.
● Innodb_log_buffer_size control is allocated to InnoDB Storage in advance.
Buffer required for writing log records. The default value is 1 MB.
● Innodb_log_files_in_group Number of log files in the log Group
. InnoDB writes data into files cyclically. The default value is 2 (recommended)
● Innodb_log_file_size: The default value is 5 MB. Recommended values: 1 MB to n
One buffer pool size, where N is the number of log files in the group. Other optimizations --
Char vs int
● Independent index vs joint index
● Record slow queries ● Regularly execute optimize/analyze table
● For InnoDB tables, do not execute the select count (*) Statement whenever possible.
● Use explain to help you analyze Query Optimization
● If you want to perform group by and other unified table connections for a large data table with a large number of concurrent reads and writes, it is best to set the connection fields to the same type (including the field length ), in this case, index ● in most cases, the index value of a character type field requires a part. ● when performing a query, try not to use external functions. In this case, it is impossible to store indexes. ● it is complicated to drag and drop, slow queries are decomposed into multiple concise and clear queries.
● Do not use the '%' Modifier on the left side of the like query on the index field.
You can use indexes. Otherwise, indexes such as... 'name' like 'ejr % 'cannot be used'
● For MyISAM tables that frequently change, try to avoid updating all variable-length fields.
(Varchar, blob, and text) ● if order by clause exists during Query