mysqldumpslow的代码注释

未完，待续，需要结合具体代码在调试过程中完善：

#!/usr/bin/perl

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");


#如果为help参数，调用帮助方法。
$opt{'help'} and usage();


#没有参数的情况下，执行如下代码
unless (@ARGV) {
    my $defaults   = `my_print_defaults mysqld`; #打印mysql配置文件
    my $basedir = ($defaults =~ m/--basedir=(.*)/)[0]
or die "Can't determine basedir from 'my_print_defaults mysqld' output: $defaults";
    warn "basedir=$basedir\n" if $opt{v};


    my $datadir = ($defaults =~ m/--datadir=(.*)/)[0];
    my $slowlog = ($defaults =~ m/--slow-query-log-file=(.*)/)[0];
    if (!$datadir or $opt{i}) {
# determine the datadir from the instances section of /etc/my.cnf, if any
my $instances  = `my_print_defaults instances`;
die "Can't determine datadir from 'my_print_defaults mysqld' output: $defaults"
   unless $instances;
my @instances = ($instances =~ m/^--(\w+)-/mg);
die "No -i 'instance_name' specified to select among known instances: @instances.\n"
   unless $opt{i};
die "Instance '$opt{i}' is unknown (known instances: @instances)\n"
   unless grep { $_ eq $opt{i} } @instances;
$datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/)[0]
   or die "Can't determine --$opt{i}-datadir from 'my_print_defaults instances' output: $instances";
warn "datadir=$datadir\n" if $opt{v};
    }


    if ( -f $slowlog ) {
        @ARGV = ($slowlog);
        die "Can't find '$slowlog'\n" unless @ARGV;
    } else {
        @ARGV = <$datadir/$opt{h}-slow.log>;
        die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV;
    }
}


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}; # 如果是调试模式，$_为文本内容


    my @chunks = split /^\/.*Version.*started with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m; #分隔，截取数据内容，剔除表头
    if (@chunks > 1) { #判定表头信息处理后还有数据进行处理
unshift @pending, map { length($_) ? $_ : () } @chunks; # @chunks追加参数
warn "<<".join(">>\n<<",@chunks).">>" if $opt{d};
next;
    }


    s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//; #处理Time
    my ($user,$host,$dummy,$thread_id) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+)\s+\S+(\s+Id:\s+(\d+))?.*\n// ? ($1,$2,$3,$4) : ('','','','','');


    s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+).*\n//; #处理Query_time
    my ($t, $l, $r) = ($1, $2, $3);
    $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 转换数据库开头的语句 use 
    s/^SET timestamp=\d+;\n//; #SET开头的语句


    s/^[ ]*\n//mg; # delete blank lines 删除空行
    s/^[ ]*/  /mg; # normalize leading whitespace  规范空格
    s/\s*;\s*(#\s*)?$//; # remove trailing semicolon(+newline-hash) ：\s*空格+tab


    next if $opt{g} and !m/$opt{g}/io; #确定grep参数


    unless ($opt{a}) { #确定a参数，是否进行数字转换成N。
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;
    $s->{users}->{$user}++ if $user;
    $s->{hosts}->{$host}++ if $host;


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


foreach (keys %stmt) {  #此时%stmt为变形后的语句，计算平均值
    my $v = $stmt{$_} || die;
    my ($c, $t, $l, $r) = @{ $v }{qw(c t l r)};
    $v->{at} = $t / $c;
    $v->{al} = $l / $c;
    $v->{ar} = $r / $c;
}


my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt; #排序字段
@sorted = @sorted[0 .. $opt{t}-1] if $opt{t};  #t参数，显示前n行
@sorted = reverse @sorted         if $opt{r}; #翻转排序参数


foreach (@sorted) {
    my $v = $stmt{$_} || die;
    my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)};
    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), $user\@$host\n%s\n\n",
   $c, $at,$t, $al,$l, $ar,$r, $_;
}


#帮助方法：
sub usage {
    my $str= shift;
    my $text= <<HERE;
help content


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