如何读慢查询日志

xfwduke

通常，分析慢查询最好先关注这三种类型的查询：

Long queries(最慢的查询)

     Routine batch jobs will generate long queries,but your normal queries shouldn't take very long.

     批处理查询通常都会很慢，但是常规查询不应该慢

High-impact queries(影响最大的查询)

     Find the queries that constitute most of the server's execution time.Recall that short queries that are executed often may take up a lot of time.

     找出占用服务器时间最多的查询。有时候，很短的查询却会占用很多时间，因为执行的次数可能会很多。

New queries(新出现的查询)

     Find queries that weren't in the top 100 yesterday but are today.These might be new queries,or they might be queries that used to rune quickly and are suffering because of different indexing or another change.

     找出新出现的查询（昨天的top100中没有，今天却出现了）。这些查询以前运行的很快，但是因为某些原因，现在运行很慢。

书上的P69有几种慢查询分析工具。

You can use the slow log statistics to predict how much you'll be able to reduce the server's resource consumption.Suppose you sample queries for an hour(3600 seconds)and find that the total combined execution time for all the queries in the log is 10000 seconds (the total time is greater than the wall-clock time because the queries execute in parallel).If log analysis shows you that the worst query accounts for 3000 seconds of execution time,you'll know that this query is responsible for 30% of the load.Now you know how much you can reduce the server's resource consumption by optimizing this query.

可以通过统计慢查询信息来估算可以优化多少服务器性能。

假设统计1个小时的慢查询信息。所有慢查询诧句的累积运行时间是10000秒（比实际时间要多，因为查询诧句是并行的）。如果分析显示最差的查询的运行总时间是3000秒，这就表示在这段时间内，这条语句占用了30%的服务器负载。现在，就可以估算出优化了这条语句后，能降低多少服务器资源消耗。