MySQL performance optimization tips

MySQL performance optimization tips

Author: ye Jinrong (email :), source: http://imysql.cn, reproduced please indicate the translator and the source, and cannot be used for commercial purposes, offenders must investigate.

I. startup Parameter Optimization
Modify my. CNF (or my. INI) and add/modify the following lines:

# Set the number of cached connections to save connection overhead
Back_log = 64

# Disable the file system external lock
External-locking = 0

# Disable bdb. If you do not need it, so does InnoDB.
Skip-bdb

# Index buffer. If it is a dedicated database server, you can set up to half of the server's memory. If it is not dedicated,
# Set it a little lower
Key_buffer = 512 m

# Number of cached data tables. If the memory size is large, you can set a slightly higher value. Otherwise, you can set a lower value.
# To set this parameter, see open_tables in the system status (indicating the total number of opened data tables)
# And opened_tables (indicating the total number of open data tables)
Table_cache = 128

# Disable DNS resolution. If you use DNS authorization in your authorization information, this option cannot be enabled.
Skip-name-resolve

# Records slow queries and queries without indexes, which helps you analyze the problem
Long_query_time = 1
Log-Slow-queries =/usr/local/MySQL/data/slow. Log
Log-queries-not-using-Indexes

Other parameters suchsort_buffer_size,net_buffer_length,read_buffer_size,read_rnd_buffer_size,myisam_sort_buffer_size,
thread_cache_size,query_cache_size,max_binlog_cache_sizePlease query the MySQL manual and make appropriate adjustments.

Ii. Other small tips

• Try not to execute InnoDB tablesSELECT COUNT(*)Statement, because the InnoDB table does not have an internal counter like MyISAM to record the total amount of table records, this operation will scan the entire table, the speed is very slow.
• Use MyISAM tables whenever possible, unless other types are required, because the overall read and write efficiency of MyISAM type is quite high, the disadvantage is that table-level locks, rather than Row-level/page-level locks.
• Make good use Explain to help you analyze Query Optimization
• If you need to do a large data table with a large number of concurrent reads and writesGROUP BYWe recommend that you use the summary table to store statistics and regularly update the statistical table, which may greatly improve the performance.
• IfORDER BYMake sure that the field order matches the index field order to accelerate the sorting speed.
• If there is a multi-field index, the query must be performed in the order of the index, otherwise the index will not be used. For example:
  Index`idx_`(col1, col2, col3), Then querySELECT .... FROM ... WHERE col1=1 AND col2=2;Query by using Indexes... WHERE col2=2 AND col3=3;Or... WHERE col1=1 AND col3=3;No index is used.
• WHEREIf a constant type (For example, 'field' = 1), So that you can perform filtering faster.
• When two tables are connected, it is recommended that the types of the connected fields be the same (including the field length), so that the indexing speed is much faster.
• In most cases, the index value of a character-type field requires a part, for exampleCREATE INDEX char_idx ON tbl1 ( name(10) );
• Use the most appropriate data type whenever possible.ENUMDo not useTINYINT, Can be usedSMALLINTDo not useMEDIUMINTThis can save storage space, increase data storage, and increase search speed. Do not worry that this will have a huge impact on the provincial level, becauseTINYINTType changedINTIt will not change the original data.
• If you know that a table is always frequently used, you can place it inhot_cache, Use the following method:

  SET GLOBAL hot_cache.key_buffer_size=128*1024;
  CACHE INDEX `xxx` IN hot_cache;
  

• The complex and slow query is divided into multiple concise and clear queries, so that although the number of queries is too large, the overall speed and efficiency may be higher, it also reduces the possibility of table locking.
• When executing a query, try not to use external functions, because in this case, possible indexes cannot be used, and the efficiency will be greatly reduced in any case. For example:... WHERE `create_time` > UNIX_TIMESTAMP(NOW());For such a query, you can obtain the current time in the program and then directly execute the constructed SQL statement.
• When you use like to query an index field, do not use the '%' Modifier on the left to use the index. Otherwise, the index cannot be used. For example:... `name` LIKE 'yejr%';.
• If possible, use more stored procedures, which may improve the performance by 22%.
• If the number of concurrent accesses is smaller than the maximum number of connections, it is best to use permanent connections, which can save a lot of system resource consumption during connection.
• Regular execution on MyISAM tablesOPTIMIZE TABLETo improve the indexing efficiency.
• If you retrieve records in the order of col1, col2,..., performALTER TABLE ... ORDER BY col1, col2, ...Statement to achieve better performance.
• For MyISAM tables that frequently change, try to avoid updating all variable-length fields (varchar, blob, and text ).
• For a single table with more than 5 million records, you should consider Table sharding. There are multiple table sharding policies, such as sharding by ID segment or by time.
• When creating a data table, try to specify a field that cannot be null and has a default value.
• UseLOAD DATAInstead of using a large numberINSERTStatement to import data.
• Make the data table name and field name as short as possible, for example, inuserUse Field Names in the tablenameInsteaduser_name.
• UseDELAY_KEY_WRITE = 1Option to allow MyISAM to update indexes faster, because they are not flushed to the hard disk before the table is closed. The disadvantage is that if the server is suddenly killed, it must be run after restart.myisamchkFix the index.
• The replication mechanism is used to distribute the load of read data. The write data is only placed on the master server, and the read data is evenly distributed to each slave server, which greatly improves the system load.