针对mysql慢查询脚本的优化
有一些同学可能不知道，mysql是存在慢查询检查脚本的，就是在发行版的scripts目录下的
mysqldumpslow.pl，需要perl环境，
安装完成后，就可以通过他对于slowlog进行分析了，但是分析出来的结果一般是这样的：
涉及到工作中的表，模糊化处理了
这样的结果显然不是dba啊程序员们想要的，我这边修改了下脚本，让他可以导出数据为sqlite的数据库格式，效果如下：
有没有感觉爽爆了，想怎么排序怎么查都可以了，而且生成速度很快哦。
这里放上源码，给有需要的同学吧
1 #!/usr/bin/perl 2 3 # Copyright (C) 2000, 2007 MySQL AB, 2009 Sun Microsystems, Inc. 4 # 5 # This program is free software; you can redistribute it and/or 6 # modify it under the terms of the GNU Library General Public 7 # License as published by the Free Software Foundation; version 2 8 # of the License. 9 # 10 # This program is distributed in the hope that it will be useful, 11 # but WITHOUT ANY WARRANTY; without even the implied warranty of 12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 13 # Library General Public License for more details. 14 # 15 # You should have received a copy of the GNU Library General Public 16 # License along with this library; if not, write to the Free 17 # Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, 18 # MA 02111-1307, USA 19 20 # mysqldumpslow - parse and summarize the MySQL slow query log 21 22 # Original version by Tim Bunce, sometime in 2000. 23 # Further changes by Tim Bunce, 8th March 2001. 24 # Handling of strings with \ and double '' by Monty 11 Aug 2001. 25 26 use strict; 27 use Getopt::Long; 28 29 use DBI qw(:sql_types); 30 use POSIX qw(strftime); 31 32 33 # t=time, l=lock time, r=rows 34 # at, al, and ar are the corresponding averages 35 36 my %opt = ( 37 s => 'at', 38 h => '*', 39 ); 40 41 GetOptions(\%opt, 42 'v|verbose+',# verbose 43 'help+', # write usage info 44 'd|debug+', # debug 45 's=s', # what to sort by (al, at, ar, c, t, l, r) 46 'r!', # reverse the sort order (largest last instead of first) 47 't=i', # just show the top n queries 48 'a!', # don't abstract all numbers to N and strings to 'S' 49 'n=i', # abstract numbers with at least n digits within names 50 'g=s', # grep: only consider stmts that include this string 51 'h=s', # hostname of db server for *-slow.log filename (can be wildcard) 52 'i=s', # name of server instance (if using mysql.server startup script) 53 'l!', # don't subtract lock time from total time 54 'db=s', # create sql3 database 55 ) or usage("bad option"); 56 57 $opt{'help'} and usage(); 58 59 unless (@ARGV) { 60 my $defaults = `my_print_defaults mysqld`; 61 my $basedir = ($defaults =~ m/--basedir=(.*)/)[0] 62 or die "Can't determine basedir from 'my_print_defaults mysqld' output: $defaults"; 63 warn "basedir=$basedir\n" if $opt{v}; 64 65 my $datadir = ($defaults =~ m/--datadir=(.*)/)[0]; 66 my $slowlog = ($defaults =~ m/--log-slow-queries=(.*)/)[0]; 67 if (!$datadir or $opt{i}) { 68 # determine the datadir from the instances section of /etc/my.cnf, if any 69 my $instances = `my_print_defaults instances`; 70 die "Can't determine datadir from 'my_print_defaults mysqld' output: $defaults" 71 unless $instances; 72 my @instances = ($instances =~ m/^--(\w+)-/mg); 73 die "No -i 'instance_name' specified to select among known instances: @instances.\n" 74 unless $opt{i}; 75 die "Instance '$opt{i}' is unknown (known instances: @instances)\n" 76 unless grep { $_ eq $opt{i} } @instances; 77 $datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/)[0] 78 or die "Can't determine --$opt{i}-datadir from 'my_print_defaults instances' output: $instances"; 79 warn "datadir=$datadir\n" if $opt{v}; 80 } 81 82 if ( -f $slowlog ) { 83 @ARGV = ($slowlog); 84 die "Can't find '$slowlog'\n" unless @ARGV; 85 } else { 86 @ARGV = <$datadir/$opt{h}-slow.log>; 87 die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV; 88 } 89 } 90 91 92 93 warn "\nReading mysql slow query log from @ARGV\n"; 94 95 96 97 my $dbh; 98 my $sth; 99 if ($opt{db}){ 100 my $dbfile = $opt{db}; 101 $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","",""); 102 103 my $sql = qq{ CREATE TABLE IF NOT EXISTS slowinfo ( 104 source VARCHAR(1024), 105 sourceTempel VARCHAR(1024), 106 Query_time NUMERIC, 107 Lock_time NUMERIC, 108 Rows_sent NUMERIC, 109 Rows_examined NUMERIC, 110 user VARCHAR(255), 111 host VARCHAR(255), 112 timestamp NUMERIC, 113 time VARCHAR(255) 114 ) }; 115 116 $dbh->do($sql); 117 118 if ($dbh){ 119 $sth = $dbh->prepare(" 120 insert into slowinfo values(?,?,?,?,?,?,?,?,?,?); 121 "); 122 } 123 } 124 125 126 my @pending; 127 my %stmt; 128 if ($dbh){ 129 $dbh->do(qq{ BEGIN; }); 130 } 131 132 $/ = ";\n#"; # read entire statements using paragraph mode 133 while ( defined($_ = shift @pending) or defined($_ = <>) ) { 134 warn "[[$_]]\n" if $opt{d}; # show raw paragraph being read 135 136 my @chunks = split /^\/.*Version.*started with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m; 137 if (@chunks > 1) { 138 unshift @pending, map { length($_) ? $_ : () } @chunks; 139 warn "<<".join(">>\n<<",@chunks).">>" if $opt{d}; 140 next; 141 } 142 143 s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//; 144 my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) : ('',''); 145 146 s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+)\s+Rows_examined: ([0-9.]+).*\n//; 147 my ($t, $l, $r, $re) = ($1, $2, $3, $4); 148 $t -= $l unless $opt{l}; 149 150 # remove fluff that mysqld writes to log when it (re)starts: 151 s!^/.*Version.*started with:.*\n!!mg; 152 s!^Tcp port: \d+ Unix socket: \S+\n!!mg; 153 s!^Time.*Id.*Command.*Argument.*\n!!mg; 154 155 s/^use \w+;\n//; # not consistently added 156 s/^SET timestamp=(\d+);\n//; 157 my ($timestamp) = ($1); 158 159 s/^[ ]*\n//mg; # delete blank lines 160 s/^[ ]*/ /mg; # normalize leading whitespace 161 s/\s*;\s*(#\s*)?$//; # remove trailing semicolon(+newline-hash) 162 163 next if $opt{g} and !m/$opt{g}/io; 164 165 my $source = $_; 166 my $sourceTemp; 167 168 if ($opt{a}){ 169 170 s/\b\d+\b/N/g; 171 s/\b0x[0-9A-Fa-f]+\b/N/g; 172 s/''/'S'/g; 173 s/""/"S"/g; 174 s/(\\')//g; 175 s/(\\")//g; 176 s/'[^']+'/'S'/g; 177 s/"[^"]+"/"S"/g; 178 # -n=8: turn log_20001231 into log_NNNNNNNN 179 s/([a-z_]+)(\d{$opt{n},})/$1.('N' x length($2))/ieg if $opt{n}; 180 # abbreviate massive "in (...)" statements and similar 181 s!(([NS],){100,})!sprintf("$2,{repeated %d times}",length($1)/2)!eg; 182 } 183 $sourceTemp = $_; 184 185 if($sth) 186 { 187 188 my $timeStr1 = strftime "%Y-%m-%d %H:%M:%S", localtime($timestamp); 189 190 $sth->bind_param(1, $source, SQL_VARCHAR); 191 $sth->bind_param(2, $sourceTemp, SQL_VARCHAR); 192 $sth->bind_param(3, $t, SQL_DOUBLE); 193 $sth->bind_param(4, $l, SQL_DOUBLE); 194 $sth->bind_param(5, $r, SQL_INTEGER); 195 $sth->bind_param(6, $re, SQL_INTEGER); 196 $sth->bind_param(7, $user, SQL_VARCHAR); 197 $sth->bind_param(8, $host, SQL_VARCHAR); 198 $sth->bind_param(9, $timestamp, SQL_INTEGER); 199 $sth->bind_param(10, $timeStr1, SQL_VARCHAR); 200 201 $sth->execute(); 202 } 203 204 205 206 my $s = $stmt{$_} ||= { users=>{}, hosts=>{} }; 207 $s->{c} += 1; 208 $s->{t} += $t; 209 $s->{l} += $l; 210 $s->{r} += $r; 211 $s->{users}->{$user}++ if $user; 212 $s->{hosts}->{$host}++ if $host; 213 214 warn "{{$_}}\n\n" if $opt{d}; # show processed statement string 215 #printf "string sql %s\r\n", $_; 216 } 217 if ($dbh){ 218 $dbh->do(qq{ COMMIT; }); 219 } 220 221 foreach (keys %stmt) { 222 my $v = $stmt{$_} || die; 223 my ($c, $t, $l, $r) = @{ $v }{qw(c t l r)}; 224 $v->{at} = $t / $c; 225 $v->{al} = $l / $c; 226 $v->{ar} = $r / $c; 227 } 228 229 my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt; 230 @sorted = @sorted[0 .. $opt{t}-1] if $opt{t}; 231 @sorted = reverse @sorted if $opt{r}; 232 233 my @rows; 234 235 foreach (@sorted) { 236 my $v = $stmt{$_} || die; 237 my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)}; 238 my @users = keys %{$v->{users}}; 239 my $user = (@users==1) ? $users[0] : sprintf "%dusers",scalar @users; 240 my @hosts = keys %{$v->{hosts}}; 241 my $host = (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts; 242 my $laststring = sprintf "Count: %d Time=%.2fs (%ds) Lock=%.2fs (%ds) Rows=%.1f (%d), $user\@$host\n%s\n\n", 243 $c, $at,$t, $al,$l, $ar,$r, $_; 244 if ($dbh) 245 { 246 247 } 248 else 249 { 250 print STDOUT $laststring; 251 } 252 } 253 254 255 if ($dbh){ 256 $dbh->disconnect(); 257 } 258 259 260 sub usage { 261 my $str= shift; 262 my $text= <<HERE; 263 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] 264 265 Parse and summarize the MySQL slow query log. Options are 266 267 --verbose verbose 268 --debug debug 269 --help write this text to standard output 270 271 -v verbose 272 -d debug 273 -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default 274 al: average lock time 275 ar: average rows sent 276 at: average query time 277 c: count 278 l: lock time 279 r: rows sent 280 t: query time 281 -r reverse the sort order (largest last instead of first) 282 -t NUM just show the top n queries 283 -a don't abstract all numbers to N and strings to 'S' 284 -n NUM abstract numbers with at least n digits within names 285 -g PATTERN grep: only consider stmts that include this string 286 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), 287 default is '*', i.e. match all 288 -i NAME name of server instance (if using mysql.server startup script) 289 -l don't subtract lock time from total time 290 291 HERE 292 if ($str) { 293 print STDERR "ERROR: $str\n\n"; 294 print STDERR $text; 295 exit 1; 296 } else { 297 print $text; 298 exit 0; 299 } 300 }
