MySQL5.6 How to optimize SQL statements for slow queries--SQL optimization

Previous article: MySQL5.6 How to optimize SQL statements for slow queries--Slow log introduction

In the actual log analysis, usually slow log number of logs, while the same query is logged a lot of bars, here you need to find the most problematic from the slow log query, the most need to optimize the log. In this regard, there are a lot of analysis tools, the most basic analysis tool is MySQL's own mysqldumpslow,mysqldumpslow (perl script) Output Example:

[Email protected] 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

It is very clear at a glance that its output mainly counts the number of occurrences of different slow SQL (Count 1), the maximum time to execute (0.00s), the cumulative total time to spend (hours 0s), the time to wait for the lock (lock 0.00s), the total time to wait for the lock (lock 0s ), The total number of rows sent to the client (rows 3.0), the total number of rows scanned (rows 3), the user (root), and the SQL statement itself. Its most commonly used parameters are:

-S sort option: C Query number R returns the number of record lines T query time
-T N: Display top n Query

For the general analysis is almost, but for the percentage and so on data Mysqldumpslow is not perfect. So there are a lot of different MySQL slow log analysis tools in the world, more excellent have Mysqlsla(perl script) and Pt-query-digest(perl script), can provide count, The number of executions of SQL and the percentage of total slow log counts, time, execution times, including total time, average time, minimum, maximum time, percentage of total slow SQL time, 95% of time, removal of the fastest and slowest SQL, coverage accounted for 95% of SQL execution time, Lock time, time to wait for lock, 95% of lock, 95% slow SQL wait lock, rows sent, result row statistics, including average, minimum, maximum, rows examined, number of scanned rows, and the ability to generate a report that stores analysis results. There is no introduction here.

By these slow log analysis software to locate the slow query statement has completed the SQL optimization of the more than half. Next, you can see why the SQL query is slow by looking at the slow query statement by executing the explain or desc command in MySQL.

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 focus on MySQL explain format, the most important thing to note in the output:

1. Type:all is the least efficient, the most important thing to note

2. Key: Whether to use Key,key length

3. Extra: It is best not to appear filesort and temporary, the most important thing is to focus on the order and GroupBy.

Note:sql optimization is a very complex process, it is possible to pay Paul the situation: for example, after the database table is indexed, the query is fast, but the storage space is more, the insertion and deletion operation time is also increased, if in a system with less write-read, the implementation of this optimization may be counterproductive. So after optimization must not be careless, to continuously monitor the system, to prevent the introduction of new bottlenecks.

MySQL5.6 How to optimize SQL statements for slow queries--SQL optimization