mysql慢查询日志

学习日志，如有出错，请批评指正，非常感谢

慢查询日志（slow query log）是MySQL自带的几种日志文件中非常重要的一种日志(另还有错误日志、查询日志、二进制日志)。MySQL的慢查询日志是用于记录执行时间超过指定阈值的SQL查询语句的一种机制。这个阈值由MySQL的参数long_query_time 指定，默认值为10秒。当一个查询的执行时间超过这个阈值时，MySQL就会将这个查询的详细信息记录到慢查询日志中，以供进一步分析和优化使用

要开启MySQL慢查询日志，需要修改MySQL服务器的配置文件my.cnf（my.ini），
mysql的配置文件路径：

• linux路径

/etc/mysql/my.cnf

• windows路径 5.7 是版本号

完成配置后，需要重启MySQL服务器才能使配置生效。

临时开启慢查询日志
我们也可以在命令行，或者Navicat 中执行如下命令开启慢查询日志：

SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/mysql-slow.log';
SET GLOBAL long_query_time = 10;
SET GLOBAL log_output = 'FILE';

常用命令：

# 查看慢查询日志的完整路径和文件名
show variables like 'slow_query_log_file'; 

# 查看当前MySQL服务器的慢查询日志是否开启
show variables like 'slow_query_log';

# 查询慢查询的阈值
show variables like '%long_query_time%'; 

# 查询慢查询日志输出方式
show variables like '%log_output%'; 

------------------------------------------慢日志配置文件已开启--------------------------------------------- 

如何分析慢查询日志

查看默认慢查询日志位置：

show variables like 'slow_query%';
开启慢查询日志：set global slow_query_log='ON';

大于1秒钟的数据记录到慢日志中（如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满掉）

set global long_query_time=1;

注：1秒时间太长，本次测试实现设置为0.01秒

查询:select * from film a left join inventory b on a.film_id = b.film_id

mysqldumpslow 工具是慢查询自带的分析慢查询工具，一般只要安装了mysql，就会有该工具。
完成语法如下：

linux中使用方法：

windows使用方法：

1、首先需要安装Perl，在windows下安装Perl，安装过程很简单，从官网

http://strawberryperl.com/ 下载windows安装包，安装好之后，测试perl -v，如果能显示版本号，表示安装成功。

mysqldumpslow是一个perl脚本，只需下载并赋权即可执行。

2、windows 的mysql里面不包含mysqldumpslow，所以需要自己下载

出现以上的情况，就是安装成功，以后Windows下的mysql，就可以使用这个压测工具了

------------------------------所有工具已安装就绪--------------------------------------------------------------------

使用示例：

锁定时间最长的前10条
  perl mysqldumpslow.pl -s l -t 1 mysql_slow.log
得到返回记录集最多的10个SQL
perl mysqldumpslow.pl -s r -t 10 mysql_slow.log
得到访问次数最多的10个SQL
perl mysqldumpslow.pl -s c -t 10 mysql_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
perl mysqldumpslow.pl -s t -t 10 -g “left join” mysql_slow.log
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况
perl mysqldumpslow.pl -s r -t 20 mysql_slow.log | more

其中，-s t 表示按查询总时间排序，-s c表示按查询次数排序，-t 10表示显示前10条查询信息。

问题出现：为什么不能打开这个文件，mysqldumpslow.pl 贴入下面，大神们请指教

#!/usr/bin/perl

# Copyright (c) 2000-2002, 2005-2008 MySQL AB, 2008, 2009 Sun Microsystems, Inc.
# Use is subject to license terms.
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU Library General Public
# License as published by the Free Software Foundation; version 2
# of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# Library General Public License for more details.
#
# You should have received a copy of the GNU Library General Public
# License along with this library; if not, write to the Free
# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
# MA 02110-1301, USA

# mysqldumpslow - parse and summarize the MySQL slow query log

# Original version by Tim Bunce, sometime in 2000.
# Further changes by Tim Bunce, 8th March 2001.
# Handling of strings with \ and double '' by Monty 11 Aug 2001.

use strict;
use Getopt::Long;

# t=time, l=lock time, r=rows
# at, al, and ar are the corresponding averages

my %opt = (
    s => 'at',
    h => '*',
);

GetOptions(\%opt,
    'v|verbose+',# verbose
    'help+',    # write usage info
    'd|debug+',    # debug
    's=s',    # what to sort by (al, at, ar, c, t, l, r)
    'r!',    # reverse the sort order (largest last instead of first)
    't=i',    # just show the top n queries
    'a!',    # don't abstract all numbers to N and strings to 'S'
    'n=i',    # abstract numbers with at least n digits within names
    'g=s',    # grep: only consider stmts that include this string
    'h=s',    # hostname of db server for *-slow.log filename (can be wildcard)
    'i=s',    # name of server instance (if using mysql.server startup script)
    'l!',    # don't subtract lock time from total time
) or usage("bad option");

$opt{'help'} and usage();

warn "\nReading mysql slow query log from @ARGV\n";

my @pending;
my %stmt;
$/ = ";\n#";        # read entire statements using paragraph mode
while ( defined($_ = shift @pending) or defined($_ = <>) ) {
    warn "[[$_]]\n" if $opt{d};    # show raw paragraph being read

    my @chunks = split /^\/.*Version.*started with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m;
    if (@chunks > 1) {
    unshift @pending, map { length($_) ? $_ : () } @chunks;
    warn "<<".join(">>\n<<",@chunks).">>" if $opt{d};
    next;
    }

    s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//;
    my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) : ('','');
###$re
    s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+)\s+Rows_examined: ([0-9.]+).*\n//;
    my ($t, $l, $r,$re) = ($1, $2, $3,$4);
    $t -= $l unless $opt{l};

    # remove fluff that mysqld writes to log when it (re)starts:
    s!^/.*Version.*started with:.*\n!!mg;
    s!^Tcp port: \d+  Unix socket: \S+\n!!mg;
    s!^Time.*Id.*Command.*Argument.*\n!!mg;

    s/^use \w+;\n//;    # not consistently added
    s/^SET timestamp=\d+;\n//;

    s/^[     ]*\n//mg;    # delete blank lines
    s/^[     ]*/  /mg;    # normalize leading whitespace
    s/\s*;\s*(#\s*)?$//;    # remove trailing semicolon(+newline-hash)

    next if $opt{g} and !m/$opt{g}/io;

    unless ($opt{a}) {
    s/\b\d+\b/N/g;
    s/\b0x[0-9A-Fa-f]+\b/N/g;
        s/''/'S'/g;
        s/""/"S"/g;
        s/(\\')//g;
        s/(\\")//g;
        s/'[^']+'/'S'/g;
        s/"[^"]+"/"S"/g;
    # -n=8: turn log_20001231 into log_NNNNNNNN
    s/([a-z_]+)(\d{$opt{n},})/$1.('N' x length($2))/ieg if $opt{n};
    # abbreviate massive "in (...)" statements and similar
    s!(([NS],){100,})!sprintf("$2,{repeated %d times}",length($1)/2)!eg;
    }

    my $s = $stmt{$_} ||= { users=>{}, hosts=>{} };
    $s->{c} += 1;
    $s->{t} += $t;
    $s->{l} += $l;
    $s->{r} += $r;
###$re
    $s->{re} += $re;
    $s->{users}->{$user}++ if $user;
    $s->{hosts}->{$host}++ if $host;

    warn "{{$_}}\n\n" if $opt{d};    # show processed statement string
}

foreach (keys %stmt) {
    my $v = $stmt{$_} || die;
    my ($c, $t, $l, $r ,$re) = @{ $v }{qw(c t l r re)};
    $v->{at} = $t / $c;
    $v->{al} = $l / $c;
    $v->{ar} = $r / $c;
    $v->{are} = $re/ $c;
}

my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt;
@sorted = @sorted[0 .. $opt{t}-1] if $opt{t};
@sorted = reverse @sorted         if $opt{r};

foreach (@sorted) {
    my $v = $stmt{$_} || die;
    my ($c, $t,$at, $l,$al, $r,$ar,$re,$are) = @{ $v }{qw(c t at l al r ar re are)};
    my @users = keys %{$v->{users}};
    my $user  = (@users==1) ? $users[0] : sprintf "%dusers",scalar @users;
    my @hosts = keys %{$v->{hosts}};
    my $host  = (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts;
    printf "Count: %d  Time=%.2fs (%ds)  Lock=%.2fs (%ds)  Rows=%.1f (%d)  Rows_examined=%.1f (%d), $user\@$host\n%s\n\n",
        $c, $at,$t, $al,$l, $ar,$r,$are,$re, $_;
}

sub usage {
    my $str= shift;
    my $text= <<HERE;
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

HERE
    if ($str) {
      print STDERR "ERROR: $str\n\n";
      print STDERR $text;
      exit 1;
    } else {
      print $text;
      exit 0;
    }
}
 

------------------------------------工具     执行慢日志   结束   ---------------------------------------------------------

通过explain查询分析SQL的执行计划
SQL的执行计划侧面反映出了SQL的执行效率，具体执行方式如下所示：在执行的SQL前面加上explain关键词即可；

2、每个字段的说明：

• id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。

• select_type：表示查询中每个select子句的类型（简单 OR复杂）

  • simple：查询中不包含子查询或者union

  • primary：一般出现于复杂查询语句，比如包含子查询的sql就会被标识为primary

  • union：表示连接查询的第二个或者更后面的查询语句

  • dependent union：union中的第二个或后面的select语句，取决于外面的查询

  • union result： 连接查询的结果

  • subquery：子查询中的第一个select语句

  • dependent subquery：子查询中的第一个select语句，取决于外面的查询

  • derived： select (from子句的子查询)

• table：表示查询的表

• partitions：如果你的查询是基于分区表，显示查询将访问的分区

• type：表的连接类型标识（最佳到最差递推）

  • system：表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计

  • const：数据表最多只有一个匹配行，所以很快，可理解为const是最优化的

  • eq_ref：用于使用 = 比较带索引的列

  • ref：查询条件索引既不是unique也不是primary key的情况，用于=或<或>操作符的带索引的列

  • ref_or_null：如同ref，但添加了mysql可以专门搜索包含null值的行。解决子查询中常用该联接类型

  • 上面五个算是比较好的类型标识了，下面开始拉垮的类型标识介绍

  • index_merge：使用了索引合并优化方法

    • 在这种情况下，key列包含了使用的索引清单

    • key_len包含了使用的索引的最长的关键元素

  • unique_subquery：该类型替换了下面形式的IN子查询的ref

    • value in(select primary_key from table where some_expr)

  • index_subquery：该联接类型类似于unique_subquery

    • 可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引

    • value in (select key_column from table where some_expr)

  • range：只检索给定范围的行,使用一个索引来选择行

  • index：该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小

  • all ：进行完整的表扫描。（性能最差）

• possible keys：指出MySQL能使用哪个索引在该表中定位到数据

  • 如果该列为null，说明没有使用索引，可以创建索引来提高性能

• key：显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是 null

• ken_len：显示MySQL确定使用的索引的长度。如果键是NULL,则长度为NULL

• ref：显示使用哪个列或常数与key一起从表中选择行

• rows：显示MySQL认为它执行查询时必须检查的行数

• Extra：该列包含MySQL解决查询的详细信息

  • Distinct：MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行

  • Not exists：能够对查询进行left join 优化,发现1个匹配left join标准的行后,不再为前面的的行组合在该表内检查更多的行

  • range checked for each record (index map: #)

    • MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用

  • Using filesort：MySQL需要额外的一次传递,以找出如何按排序顺序检索行

  • Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息

  • Using temporary：为了解决查询,MySQL需要创建一个临时表来容纳结果

  • Using where：where子句用于限制哪一个行匹配下一个表或发送到客户

  • Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描

  • Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

• MySQL优化show profile

  Query Profile是MySQL自带的一种Query诊断分析工具，可以完整的显示一条sql执行的各方面的详细信息，默认关闭；

• 查看是否开启Profile功能

  • select @@profiling; 默认为0【关闭】

  • show variables like '%profil%'; 默认OFF【关闭】

• 开启Profile ：set profiling=1;

• 然后我们测试两个sql来看看效果如何？

  • select count (*) from test3;

  • select count(id) from test3;

  • 执行show profiles;

    • 我们就已经看到count (列) 要比 count(*) 耗时要少一半多

得到两条SQL的Query_ID，我们根据Query_ID去查询详细的信息 

• 我们想看的不是两个sql的数据对比，一次测试结果也不足以为为准，但是我们可以发现

  • 刚开始建立连接staring的耗时，查询完毕发送数据Sending data在整个耗时中占比都是很高的

  • 所以我们使用连接池

  • 建议SQL语句之返回我们想要的数据，即可实现对这一前一后的耗时优化

  • 主要还是分为一个大的模块：CPU、磁盘IO、数据访问、数据运算影响