mysql分区表和普通表关联查询异常

发现线上有数据库load异常,查看show processlist,同一条sql都是在copy tmp,以前执行几十个ms的,现在基本都是几十s,怀疑执行计划有问题
1
可以看到a表(按月分表的分区表)进行全表扫描,a表记录在上千万,b表记录就几千。看一下a、b表的索引情况
1
当初为了快速解决问题,把join sql拆成两条sql,问题解决。今天想追查一下问题,发现一样的sql,执行计划居然正常了
1

目前没有找到根本原因,先记录一下,莫非和统计信息收集有关或者是bug?

类似bug:Bug #69013

puppet循环创建目录方法

业务需求:在特定目录下,判断某个目录是否存在,如果不存在就创建特定目录,存在啥事不做。不用DSL实现

site.pp文件定义,在/xx/7和/xx/8创建Recycle目录,如果存了Recycle,不覆盖

define mkdir_recycle() {
  file { "/xx/${title}/Recycle":
      ensure => directory,
      owner  => 'admin',
      group  => 'admin',
      mode   => 0775;
    }
}
mkdir_recycle{["7","8"]:}
 
# ls /xx/{7,8}/
/xx/7/:
 
/xx/8/:
 
puppet的日志:
notice: /Stage[main]//Mkdir_recycle[7]/File[/xx/7/Recycle]/ensure: created
notice: /Stage[main]//Mkdir_recycle[8]/File[/xx/8/Recycle]/ensure: created
notice: Finished catalog run in 0.05 seconds
 
# ls /xx/{7,8}/
/xx/7/:
Recycle
 
/xx/8/:
Recycle

perl环视(断言)

每次写断言相关匹配都查一下文档,还老实点记在自己blog吧

$ vi test.pl  
#!/usr/bin/perl
USE warnings;
USE strict;
my $line = "alovebcliked";
#(?=EXP)断言自身出现的位置的后面能匹配表达式EXP
IF ( $line =~ /(\w+)(?=love)/ ){
  print "matched one:$1\n";
}
#(?<=EXP)断言自身出现的位置的前面能匹配表达式EXP
IF ($line =~ /(?<=LIKE)\d/){
  print "matched two\n";
}
elsif($line =~ /(?<=LIKE)(\w+)/){
  print "matched two two:$1\n";
}
 
$line = "abc123";
#(?!EXP)断言此位置的后面不能匹配表达式EXP
IF ($line =~ /([a-z]{2})(?!\d+)/){
   print "matched three:$1\n";
}
IF ($line =~ /([a-z]{3})(?!\d+)/){
   print "matched three three:$1\n";
}
$line = "123abc";
#(?<!EXP)断言此位置的前面不能匹配表达式
IF ($line =~ /(?<![a-z])([a-z]{3})/){
   print "matched four:$1\n";
}
$ perl test.pl
matched one:a
matched two two:d
matched three:ab
matched four:abc

perl私有方法两种实现

perl不像其他语言有private声明私有方法,但有变通方法。

一、利用调用者是否是同一个类,来隐藏私有方法(参考http://stackoverflow.com/questions/451521/how-do-i-make-private-functions-in-a-perl-module)

$cat test_priv.pl
package My;
sub new {
        return bless { }, shift;
}
sub private_func {
        my ($s,%args) = @_;
        die "Error: Private method called"
                unless (caller)[0]->isa( ref($s) );
        warn "OK: Private method called by " . (caller)[0];
}
sub public_func {
        my ($s,%args) = @_;
        $s->private_func();
}
package main;
my $obj = My->new();
$obj->public_func();
$obj->private_func();
 
$perl test_priv.pl
OK: Private method called by My at test_priv.pl line 9.
Error: Private method called at test_priv.pl line 7.

生成对象只能通过公共方法才能访问私有方法。

二、利用文件作用域,perl只能继承类的方法的特性

$cat test_priv2.pl
package Test;
sub new {
        return bless { }, shift;
}
my $private_func = sub {
   my $self = shift;
   print "I am private func!\n";
};
sub public_func {
   my $self = shift;
   print "I am public func!\n";
   $self->$private_func();
}
my $obj = Test->new();
$obj->public_func;
 
$perl test_priv2.pl
I am public func!
I am private func!

类继承以后,也能通过public_func来访问匿名函数(private)

caller函数

在写perl中,调试代码要查看是谁调用谁,caller就比较方便实现这个功能

($package, $filename, $line) = caller;
返回包名、文件名、调用的行号

caller返回一数组,可以数组下标来取具体值

$cat caller.pl
#!/usr/bin/perl
#use strict;
#use warnings;
sub b{
    my $sub=(caller(0))[3];
    print "subroutine $sub\n";
    print caller()."\n";
    print caller(0);
}
&b;
 
$perl caller.pl
subroutine main::b
main
maincaller.pl10main::b0256

解释:caller(0)[3]取得是子程序名,caller中参数0代表当前堆栈

$cat caller.pl 
#!/usr/bin/perl
#use strict;
#use warnings;
sub c{
    my $sub=(caller(0))[3];
    print "subroutine $sub\n";
    print caller(1);
    print "\n";
    print caller(2);
    print "\n";
}
sub b{
    my $sub=(caller(0))[3];
    print "subroutine $sub\n";
    print caller(1);
    print "\n";
    &c
}
sub a{
    my $sub=(caller(0))[3];
    print "subroutine $sub\n";
    &b;
}
&a;
 
$perl caller.pl 
subroutine main::a
subroutine main::b
maincaller.pl24main::a0256
subroutine main::c
maincaller.pl22main::b0256
maincaller.pl24main::a0256

解释:caller(1)是返回调用者、caller(2)返回调用的调用者

参考:
http://perldoc.perl.org/functions/caller.html
http://blog.csdn.net/ace_fei/article/details/6851882