MySQL MyISAM InnoDB Optimization

MySQL history

MySQL configuration principles

Properly configured MySQL
Server, try to reach
The most reasonable use of MySQL is for MyISAM or InnoDB
Custom configurations for different engines
Perform proper configuration for different application scenarios. 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
Innodb_flush_log_at_trx_commit. If it is set to 1
Then, the data will be automatically submitted every time the data is inserted, resulting in a sharp decline in performance. It should be the same as refreshing the log
The link is set to 0, and the efficiency 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 and query performance

MySQL tips

------------------------------------------------------------

Storage engine-My I sam, I n ODB, others
MyISAM is more suitable for application rings with less frequent inserts and more queries
Environment: does not support transactions, can only lock the entire table, does not support foreign keys, and does not support
WAL (write Ahead Logging)
InnoDB is suitable for environments with large concurrent writes and queries: supports Acid
Compatible), row locks, foreign keys, have their own memory buffer pool, independent tables
Space (not limited by large files)
Merge is actually mrg_myisam, which is suitable
The seed table is merged into a large table, which is easy to operate but cannot be fully utilized.
Benefits of Indexing
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
If you need to allow a large number of connections in a short time, you can increase the value.
● 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 sort/scan table connections
Memory connected to and indexed
● Skip-bdb and so on to disable unnecessary Engines

InnoDB Optimization

● If the CPU usage of the database is less than 70%, the pressure on MySQL may be on the disk.
Factors, there may be too many transactions and commits, or the buffer pool is too small. Another buffer pool is available.
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. But also
Note the efficiency issues
● Innodb_flush_log_at_trx_commit is set to 0 (refresh every second) and 1 (Real-Time
Refresh), 2 (only write log files, not flushed to disk)
● Use large log files and large log Buffering
● When importing data to an InnoDB table, disable the autocommit mode. Otherwise, data is imported in real time.
Refresh data to disk
● 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
● Use stored procedures, triggers, and views
● 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 operations on a large data table with concurrent reads and writes
Summary tables are recommended to store statistics and regularly update statistical tables.
When a table is connected, it is recommended that the types of connected fields be the same (including the Field Length). In this case, the index
Faster
● In most cases, the index value of a character-type field must be partial.
● Use the most appropriate data type as far as possible, without wasting space and Efficiency
● When executing a query, try not to use external functions, because in this case, the memory may not be used.
Index
● Break down complex and slow queries 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)
● Share the pressure and use the cluster/copy
● If order by clause exists during Query