Recently, with the help and joint efforts of the operation and Maintenance Department and DBA colleagues, we have optimized and corrected the slow SQL in the project, and the effect is still obvious. Here I give you a big compliment. In order to make us more reasonable in the PROCESSING of SQL, the formation of practical, can learn from, can refer to the optimization of the scheme, I am here to sort out the slow SQL solution for your reference.


Slow SQL system performance


First of all, how do we distinguish the system encountered SQL slow query problem? In my opinion, slow SQL has the following three characteristics:


1. The database CPU has a high load. Generally, there is a lot of calculation logic in the query statement, causing the database CPU load.

2. The server is stuck due to high I/O load. This is usually associated with a full table query without an index.

3, the query statement is normal, the index is normal but still slow. If the index appears healthy but the query is slow, you need to check whether the index does not take effect.




Enable the SQL slow query log function


If this is happening to your system and you are not using a product like RDS from Aliyun, then the next step is to open Mysql’s slow query log to further locate the problem. MySQL provides a slow query log, which records all SQL and related information that takes longer than long_query_time (default: 10s).

[mysqld] : [mysqld] : [mysqld] : [mysqld] :

[mysqld]slow_query_log=1
slow_query_log_file=/var/log/mysql/log-slow-queries.log
long_query_time=2Copy the code

In real projects, the logs generated for slow queries can be very large and not very analytical

Mysql official also provides the mysqlDumpslow tool, which is convenient for us to analyze slow query logs. If you are interested, you can go to the Mysql official to check.

SQL tuning

Some SQL that appears in slow query logs may not be a performance problem per se, but may be due to lock waits, high server stress, and so on. You need to analyze the actual execution plan of SQL statements, rather than see how long it takes to execute the SQL again. You need to locate specific SQL problems by using slow query logs or open source slow query systems, and then use Explain tools to gradually tune and understand some details of MySQL in executing this data. Whether it was optimized, whether it was indexed, and so on. Based on the results returned by Explain, we can further analyze whether search should be optimized and how indexes should be optimized based on MySQL execution details.


As for the principles of index creation and optimization, I particularly recommend the summary of meituan-Dianping technical team, which is particularly good, and I quote it here:

  1. A = 1 and b = 2 and C > 3 and d = 4; a = 2 and C > 3 and d = 4; D (a,b, D,c); d (a, B,d,c);

  2. (a = 1 and b = 2 and c = 3) (a,b,c) (a,b,c) (a,b,c)

  3. Try to select columns with high distinctness as the index. The distinctness formula is count(DISTINCT Col)/count(*), which indicates the proportion of fields that are not duplicated. The larger the ratio is, the fewer records we scan. Is there any rule of thumb for this ratio? This value is also difficult to determine in different application scenarios. Generally, we require join fields to be above 0.1, that is, 10 records are scanned for 1 item on average.

  4. From_unixtime (create_time) = ‘2014-05-29’; from_unixtime(create_time) = ‘2014-05-29’; from_unixtime(create_time) = ‘2014-05-29’; Obviously the cost is too great. Create_time = unix_timestamp(‘ 2014-05-29 ‘);

  5. Expand indexes as much as possible, do not create new ones. For example, if you want to add (a,b) to a table that already has an index of A, you only need to modify the original index.



A summary


Based on the ideas of this article, the solution of SQL slow query can be carried out according to the following steps:


1. Enable slow log query to determine whether SQL statements occupy too many resources. If yes, optimize insert, Group BY, Order BY, and JOIN statements without changing service intentions.

2. Consider adjusting MySQL system parameters such as innodb_buffer_pool_size, innodb_log_file_size, and table_cache.

3. Determine whether the timeout problem of row lock is caused by high concurrency.

4. If the amount of data is too large, further database and table should be considered, which can be referred to previous articles 1 and 2.


Scan the QR code or manually search wechat public account [architecture stack] : ForestNotes

Welcome to reprint, bring the following QR code