Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief account of where you need to pay attention to the optimization of MySQL. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring some practical help to you where you need to pay attention to optimize MySQL.

1. Control the appropriate data file size:

The larger the amount of data, the larger the size of the data file and the larger the index file, which will affect the performance and lead to a sharp decline in performance. therefore, it is necessary to limit the amount of data and the size of the data file in a single table in the database.

2. Note that the fragments are empty:

Often, when we delete table data, it is obviously deleted, but it is only marked as deleted at the bottom, but in fact, there is no free space, so we should check the fragmentation of the table regularly.

Show table status-find fragmentation of the table (check the ratio of actual data data_length to empty data data_free. If the data_free is very large, the table needs to be optimized.)

The optimization method is: optimize table table name; or alter table table name engine=innodb to rebuild the tablespace

3. Note the line storage format:

After MySQL5.7.9, the row format of the innodb table is changed from the default compact to dynamic. The difference between the two can be viewed in official documents, but the compact format saves 20% of the space. At the same time, when storing UTF8 or UTF8MB4 data, the compact format saves as much space as possible without storing the spaces in it.

4. Use the index correctly:

Although the index can improve the query performance, it will reduce the speed of writing data in MySQL. At the same time, it will also increase the size of the data file. The more indexes are added, the slower the data is written, and the larger the data file will be. So when designing the index, you will require that the field type of the primary key must be a numeric type, and be as small as possible. If you can use INT, never use bigint. When designing a secondary index, add only the required indexes, avoid adding duplicate indexes and redundant indexes, and try to add prefix indexes for long string fields.

-how to use the index correctly:

①: when using the index, MySQL uses the leftmost matching principle. If it is a multi-column index: idx_a_b_c (aformab), then the index function can be combined as follows: a, (ameme b) (ameme b).

②: MySQL cannot use indexes in computed columns

③: MySQL cannot use indexes in negative conditions

④: MySQL cannot use an index if the join field type in join is inconsistent

*

Some common system parameters in MySQL:

1 、 general_log:

It is recommended that this parameter be turned off when the database is in normal service, because it records everything submitted to MySQL, which wastes disks and affects efficiency. At the same time, it is also a sharp tool for analyzing the problem. When there is an exception in the database, you can open it, and then intercept the log for a period of time to help locate the problem.

2 、 query_cache_size:

Is the cache space used to cache the text of the sql statement and the corresponding query results. If the table does not change, the query will return the result directly, which is very efficient; if the table changes very frequently, the content in the cache should be constantly updated, and the granularity at this time is very large, which will become a bottleneck. So in many cases, this option is turned off and the parameter is set to 0.

3 、 tmp_table_size:

In the case of group by or distinct, if the sql statement does not need an index, the system internal temporary table is used to record the intermediate state. If the tmp_table_size is not large enough, MySQL will automatically use physical disks, which will have a great impact on query performance. Increasing this parameter can reduce the probability of this happening. (note: this takes up physical memory, taking into account the actual memory idle)

4 、 innodb_buffer_pool_size:

The most needed cache of innodb, which is used to cache innodb index pages, undo pages and other auxiliary data. It is recommended to configure 50% of physical memory.

5 、 innodb_buffer_pool_instances:

This parameter divides the original whole buffer pool into multiple memory spaces, each of which independently manages its own free linked list, refresh linked list, LRU and other data structures. This greatly increases concurrency and makes more efficient use of caching

6. Innodb_log_file_size and innodb_log_files_in_group

The combination of these two parameters determines the size of redo space. The larger the redo space, the larger the incremental update log that can be stored, which can effectively reduce the speed of elimination of dirty buffer pool pages, reduce the number of checkpoint, and reduce the disk IO replacement rate, thus improving the writing efficiency of the database. However, it may also cause the recovery time to be prolonged when the database exits abnormally.

7. Innodb_old_blocks_pct and innodb_old_blocks_time

These two parameters control the expiration and movement behavior of cached data in buffer pool. The combination of these two parameters can optimize some problems such as large-scale update buffer caused by full table scanning.

8 、 innodb_numa_interleave:

This parameter prevents memory from being swapped to the SWAP partition, affecting performance

9 、 innodb_autoinc_lock_mode:

In the case of self-incrementing columns in innodb, self-increment is automatically generated when data is inserted. This parameter controls the way in which self-increment is generated. At present, there are three options: 012; use 2, that is, in special leave, so that marked auto-inc locks are not used in insert data, avoiding the deadlock problem of auto-inc, greatly improving performance in insert...select scenarios, and improving the efficiency of concurrent execution when doing ordinary insert.

-Note: the self-appreciation generated at this time is not continuous, and the binlog format needs to be set to ROW to ensure the security and consistency of the data.

10 、 innodb_flush_method

Innodb refreshes data and logs to disk files. The default is NULL, but in fact, on Unix systems, the default is that fsync; can be set to O_DIRECT when using SSD or PCIE storage. This parameter will improve performance.

11 、 innodb_doublewrite

If atomic writes are supported during underlying storage, write can be turned off twice to improve efficiency

12 、 innodb_io_capacity

If you are using SSD devices, you can appropriately increase this parameter value to speed up the frequency of flush.

13 、 innodb_thread_concurrency

On real columns with large concurrency, increasing this value can reduce the cost of innodb switching between concurrent threads and increase the concurrent throughput of the system.

14 、 innodb_flush_log_at_trx_commit

The value of 0 1 / 2 / 0 has the best performance, but is not safe; 1 is the safest, but the performance is the worst. If the disk used is good enough, it is recommended to use 1, otherwise 2 is selectively set according to the actual situation.

15 、 sync_binlog

The way MySQL synchronizes binlog to disk, 1 is the safest, but its performance is the worst; 0 has the best performance, but is the least secure. Recommended setting 1

16 、 binlog_format

The log format of MySQL is officially defaulted to ROW after MySQL5.7.7, which is the most recommended setting.

17 、 binlog_order_commits

The order in which transactions are written to binlog when they are committed is a double-edged sword. If opened, transactions are guaranteed to be written to binaries in the same order, and if closed, performance can be improved. Let's decide according to the actual situation.

18 、 tx_isolation

Set the isolation level of MySQL, and it is recommended to set read committed. On the premise of ensuring performance, set binlog_format=ROW at the same time to ensure the consistency of master-slave database of synchronized data through binlog, take into account security, and meet the needs of most businesses.

19 、 slave_parallel_workers

In multithreaded replication, if you set this parameter to a non-zero value, you can turn on multithreading to play back the log concurrently to improve the synchronization performance of slave.

Optimization of MySQL where we need to pay attention to first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.