Latency Association of MySQL Performance Optimization

Background: An exception occurred when the load alarm of a business database occurred. The cpuusr reached 30-40, which remained high. Use tools to view the SQL statements being executed by the database. The most advanced ones are:

Background: An exception occurred when the load alarm of a business database occurred. The cpu usr reached 30-40, which remained high. Use tools to view the SQL statements being executed by the database. The most advanced ones are:

Background]
An error occurred while alerting the load of a business database. The cpu usr reached 30-40, which remained high. Use tools to view the SQL statements being executed by the database. The most advanced ones are:

Infoend_time order by id asc LIMIT 149420, 20;

The table's data volume is about. This SQL statement is a typical sorting + paging query: order by col limit N, OFFSET M, mySQL needs to scan N rows before taking M rows when executing such SQL statements. For such sorting operations on large data volumes, It is very fast to retrieve the first few rows of data, but the closer the back, the worse the performance of SQL, because the larger the N, mySQL needs to scan unnecessary data and then discard it, which takes a lot of time.

[Analysis]
There are many methods for limit optimization,
1. Add cache at the front end to reduce the query operations that fall into the database
2. Optimize SQL statements
3. Use bookmarks to record the latest/large id values of the last query and trace the M-Row Records backwards.
4. Use Sphinx for search optimization.
For the second method, we recommend that you use the "delayed Association" method to optimize the sorting operation. What is "delayed Association": the primary key is returned by overwriting the index query, then, associate the original table with the primary key to obtain the required data.

[Solution]
Based on the concept of latency Association, modify the SQL statement as follows:
Before Optimization

Execution time:

After optimization:

SELECT. * FROM relation a, (select id from relation where biz_type = '0' AND end_time> = '2017-05-29 'order by id asc LIMIT 2014, 20) B where. id = B. id

Execution time:

The execution time after optimization is 1/3 of the original.

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04