How to optimize SQL statements for slow queries-SQL Optimization

How to optimize SQL statements for slow queries-SQL Optimization

In actual log analysis, there are usually a large number of slow logs, and there will be a large number of records for the same query, here we need to find the most problematic and optimized log from slow log query. There are many analysis tools in this regard. The most basic analysis tool is the output example of mysqldumpslow and mysqldumpslow (Perl script) that comes with MySQL:

[root@cloudlu bin]# ./mysqldumpslow -s t -t 1 /usr/local/mysql/data/cloudlu-slow.logReading mysql slow query log from /usr/local/mysql/data/cloudlu-slow.logCount: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=3.0 (3), root[root]@localhost  select * from t

At first glance, it is very clear that its output mainly counts the number of occurrences of different slow SQL statements (Count 1), the maximum execution Time (Time 0.00 s ), total Time consumed (Time 0 s), Lock wait Time (Lock 0.00 s), and Lock wait Time (Lock 0 s ), total number of Rows sent to the client (Rows 3.0), total number of scanned Rows (Rows 3), user (root), and SQL statement itself. Its most common parameters include:

-S sorting option: c query times r returns the number of record rows t query time
-T n: top n queries are displayed.

The general analysis is almost the same, but the percentage and other data mysqldumpslow is not perfect. Therefore, many MySQL slow log analysis tools are available in the world, including mysqlsla (Perl script) and pt-query-digest (Perl script), which can provide Count, SQL Execution times and percentage of the total slow log count, Time, execution Time, including the total Time, average Time, minimum, maximum Time, and Time as a percentage of the total slow SQL Time, 95% of Time: the fastest and slowest SQL statements are removed. The SQL Execution Time with a coverage rate of 95%, Lock Time, Lock wait Time, 95% of Lock, and 95% of slow SQL wait Lock Time are removed, rows sent: Number of result Rows, including average, minimum, maximum, Rows examined, and number of scanned Rows. It can also generate table reports and store analysis results. Here we will not introduce them one by one.

Through these slow log analysis software, you can find that the slow query statement has completed most of the SQL optimization. Run the explain or desc command in MySQL to check the slow query statement.

mysql> explain select * from test.t \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 2        Extra: NULL1 row in set (0.00 sec)

Its output format details can be followed by MySQL explain format, the most important thing in the output is:

1. type: ALL is the most efficient and important

2. key: whether the Key is used. What is the length of the key?

3. Extra: it is best not to show filesort and temporary. The most important thing is to focus on orderby and groupby.

Note: SQL optimization is a very complicated process, and the east wall may be replaced by the west wall. For example, after an index is added to a database table, the query speed is faster, but the storage space is increased, the time consumed for insert/delete operations is also increased. If you execute this optimization in a system with multiple writes and fewer reads, it may be counterproductive. Therefore, after optimization, do not care about it. You must continuously monitor the system to prevent new bottlenecks from being introduced.