test-insert

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

$loop_time=new Benchmark;
$estimated=$rows=0;
for ($i=1 ; $i <= $small_loop_count ; $i++)
{
  $rows+=fetch_all_rows($dbh,"select $sel from bench1 order by id2,id3",1);
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
					 $small_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for order_by_big ($small_loop_count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";


$sel=$limits->{'order_by_unused'} ? "dummy1" : "dummy1,id3";
$loop_time=new Benchmark;
$estimated=$rows=0;
for ($i=1 ; $i <= $range_loop_count ; $i++)
{
  $start=$opt_loop_count/$range_loop_count*$i;
  $end=$start+$i;
  $rows+=fetch_all_rows($dbh,"select $sel from bench1 where id>=$start and id <= $end order by id3",1);
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
					 $range_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for order_by_range ($range_loop_count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";

$sel=$limits->{'order_by_unused'} ? "dummy1" : "dummy1,id";
$loop_time=new Benchmark;
$estimated=$rows=0;
for ($i=1 ; $i <= $range_loop_count ; $i++)
{
  $start=$opt_loop_count/$range_loop_count*$i;
  $end=$start+$i;
  $rows+=fetch_all_rows($dbh,"select $sel from bench1 where id>=$start and id <= $end order by id",1);
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
					 $range_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for order_by_key_prefix ($range_loop_count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";

$sel=$limits->{'order_by_unused'} ? "id2" : "id2,id3";
$loop_time=new Benchmark;
$estimated=$rows=0;
for ($i=1 ; $i <= $range_loop_count ; $i++)
{
  $start=$opt_loop_count/$range_loop_count*$i;
  $end=$start+$range_loop_count;
  $rows+=fetch_all_rows($dbh,"select $sel from bench1 where id3>=$start and id3 <= $end order by id3",1);
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
					 $range_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for order_by_key2_diff ($range_loop_count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";

#
# Test of select on 2 different keys with or
# (In this case database can only use keys if they do an automatic union).
#

$loop_time=new Benchmark;
$estimated=0;
$rows=0;
$count=0;
for ($i=1 ; $i <= $range_loop_count ; $i++)
{
  my $rnd=$i;
  my $rnd2=$random[$i];
  $rows+=fetch_all_rows($dbh,"select id2 from bench1 where id=$rnd or id3=$rnd2",1);
  $count++;
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count,
					 $range_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for select_diff_key ($count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";


# Test select that is very popular when using ODBC

check_or_range("id","select_range_prefix");
check_or_range("id3","select_range_key2");

# Check reading on direct key on id and id3

check_select_key("*","id","select_key_prefix");
check_select_key2("*","id","id2","select_key");
check_select_key2("id,id2","id","id2","select_key_return_key");
check_select_key("*","id3","select_key2");
check_select_key("id3","id3","select_key2_return_key");
check_select_key("id,id2","id3","select_key2_return_prim");

####
#### A lot of simple selects on ranges
####

@Q=("select * from bench1 where !id!=3 or !id!=2 or !id!=1 or !id!=4 or !id!=16 or !id!=10",
    6,
    "select * from bench1 where !id!>=" . ($total_rows-1) ." or !id!<1",
    2,
    "select * from bench1 where !id!>=1 and !id!<=2",
    2,
    "select * from bench1 where (!id!>=1 and !id!<=2) or (!id!>=1 and !id!<=2)",
    2,
    "select * from bench1 where !id!>=1 and !id!<=10 and !id!<=5",
    5,
    "select * from bench1 where (!id!>0 and !id!<2) or !id!>=" . ($total_rows-1),
    2,
    "select * from bench1 where (!id!>0 and !id!<2) or (!id!>= " . ($opt_loop_count/2) . " and !id! <= " . ($opt_loop_count/2+2) . ") or !id! = " . ($opt_loop_count/2-1),
    5,
    "select * from bench1 where (!id!>=5 and !id!<=10) or (!id!>=1 and !id!<=4)",
    10,
    "select * from bench1 where (!id!=1 or !id!=2) and (!id!=3 or !id!=4)",
    0,
    "select * from bench1 where (!id!=1 or !id!=2) and (!id!=2 or !id!=3)",
    1,
    "select * from bench1 where (!id!=1 or !id!=5 or !id!=20 or !id!=40) and (!id!=1 or !id!>=20 or !id!=4)",
    3,
    "select * from bench1 where ((!id!=1 or !id!=3) or (!id!>1 and !id!<3)) and !id!<=2",
    2,
    "select * from bench1 where (!id! >= 0 and !id! < 4) or (!id! >=4 and !id! < 6)",
    6,
    "select * from bench1 where !id! <= -1 or (!id! >= 0 and !id! <= 5) or (!id! >=4 and !id! < 6) or (!id! >=6 and !id! <=7) or (!id!>7 and !id! <= 8)",
    9,
    "select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>=0 and !id! <=10)",
    11,
    "select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>2 and !id! <=10)",
    10,
    "select * from bench1 where (!id!>1 or !id! <1) and !id!<=2",
    2,
    "select * from bench1 where !id! <= 2 and (!id!>1 or !id! <=1)",
    3,
    "select * from bench1 where (!id!>=1 or !id! <1) and !id!<=2",
    3,
    "select * from bench1 where (!id!>=1 or !id! <=2) and !id!<=2",
    3
    );

print "\nTest of compares with simple ranges\n";
check_select_range("id","select_range_prefix");
check_select_range("id3","select_range_key2");

####
#### Some group queries
####

if ($limits->{'group_functions'})
{
  $loop_time=new Benchmark;
  $count=1;

  $estimated=0;
  for ($tests=0 ; $tests < $small_loop_count ; $tests++)
  {
    $sth=$dbh->prepare($query="select count(*) from bench1") or die $DBI::errstr;
    $sth->execute or die $sth->errstr;
    if (($sth->fetchrow_array)[0] != $total_rows)
    {
      print "Warning: '$query' returned wrong result\n";
    }
    $sth->finish;

    # min, max in keys are very normal
    $count+=7;
    fetch_all_rows($dbh,"select min(id) from bench1");
    fetch_all_rows($dbh,"select max(id) from bench1");
    fetch_all_rows($dbh,"select sum(id+0.0) from bench1");
    fetch_all_rows($dbh,"select min(id3),max(id3),sum(id3-0.0) from bench1");
    if ($limits->{'group_func_sql_min_str'})
    {
      fetch_all_rows($dbh,"select min(dummy1),max(dummy1) from bench1");
    }
    $count++;
    $sth=$dbh->prepare($query="select count(*) from bench1 where id >= " .
		       ($opt_loop_count*2)) or die $DBI::errstr;
    $sth->execute or die $DBI::errstr;
    if (($sth->fetchrow_array)[0] != $opt_loop_count)
    {
      print "Warning: '$query' returned wrong result\n";
    }
    $sth->finish;

    $count++;
    $sth=$dbh->prepare($query="select count(*),sum(id+0.0),min(id),max(id),avg(id-0.0) from bench1") or die $DBI::errstr;
    $sth->execute or die $DBI::errstr;
    @row=$sth->fetchrow_array;
    if ($row[0] != $total_rows ||
	int($row[1]+0.5) != int((($total_rows-1)/2*$total_rows)+0.5) ||
	$row[2] != 0 ||
	$row[3] != $total_rows-1 ||
	1-$row[4]/(($total_rows-1)/2) > 0.001)
    {
      # PostgreSQL 6.3 fails here
      print "Warning: '$query' returned wrong result: @row\n";
    }
    $sth->finish;

    if ($limits->{'func_odbc_mod'})
    {
      $tmp="mod(id,10)";
      if ($limits->{'func_extra_%'})
      {
	$tmp="id % 10";		# For postgreSQL
      }
      $count++;
      if ($limits->{'group_by_alias'}) {
	if (fetch_all_rows($dbh,$query=$server->query("select $tmp as last_digit,count(*) from bench1 group by last_digit")) != 10)
	{
	  print "Warning: '$query' returned wrong number of rows\n";
	}
      } elsif ($limits->{'group_by_position'}) {
	if (fetch_all_rows($dbh,$query=$server->query("select $tmp,count(*) from bench1 group by 1")) != 10)
	{
	  print "Warning: '$query' returned wrong number of rows\n";
	}
      }
    }

    if ($limits->{'order_by_position'} && $limits->{'group_by_position'})
    {
      $count++;
      if (fetch_all_rows($dbh, $query="select id,id3,dummy1 from bench1 where id < 100+$count-$count group by id,id3,dummy1 order by id desc,id3,dummy1") != 100)
      {
	print "Warning: '$query' returned wrong number of rows\n";
      }
    }
    $end_time=new Benchmark;
    last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
					   $small_loop_count));
  }
  print_time($estimated);
  print " for select_group ($count): " .
      timestr(timediff($end_time, $loop_time),"all") . "\n";

  $loop_time=new Benchmark;
  $count=$estimated=0;
  for ($tests=1 ; $tests <= $range_loop_count*5 ; $tests++)
  {
    $count+=6;
    fetch_all_rows($dbh,"select min(id) from bench1");
    fetch_all_rows($dbh,"select max(id) from bench1");
    fetch_all_rows($dbh,"select min(id2) from bench1 where id=$tests");
    fetch_all_rows($dbh,"select max(id2) from bench1 where id=$tests");
    if ($limits->{'group_func_sql_min_str'})
    {
      fetch_all_rows($dbh,"select min(dummy1) from bench1 where id=$tests");
      fetch_all_rows($dbh,"select max(dummy1) from bench1 where id=$tests");
    }
    $end_time=new Benchmark;
    last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
					   $range_loop_count*5));
  }
  if ($estimated)
  { print "Estimated time"; }
  else
  { print "Time"; }
  print " for min_max_on_key ($count): " .
      timestr(timediff($end_time, $loop_time),"all") . "\n";

  $loop_time=new Benchmark;
  $count=$estimated=0;
  for ($tests=1 ; $tests <= $small_loop_count ; $tests++)
  {
    $count+=6;
    fetch_all_rows($dbh,"select min(id2) from bench1");
    fetch_all_rows($dbh,"select max(id2) from bench1");
    fetch_all_rows($dbh,"select min(id3) from bench1 where id2=$tests");
    fetch_all_rows($dbh,"select max(id3) from bench1 where id2=$tests");
    if ($limits->{'group_func_sql_min_str'})
    {
      fetch_all_rows($dbh,"select min(dummy1) from bench1 where id2=$tests");
      fetch_all_rows($dbh,"select max(dummy1) from bench1 where id2=$tests");
    }
    $end_time=new Benchmark;
    last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
					   $range_loop_count));
  }
  if ($estimated)
  { print "Estimated time"; }
  else
  { print "Time"; }
  print " for min_max ($count): " .
      timestr(timediff($end_time, $loop_time),"all") . "\n";

  $loop_time=new Benchmark;
  $count=0;
  $total=$opt_loop_count*3;
  for ($tests=0 ; $tests < $total ; $tests+=$total/100)
  {
    $count+=1;
    fetch_all_rows($dbh,"select count(id) from bench1 where id < $tests");
  }
  $end_time=new Benchmark;
  print "Time for count_on_key ($count): " .
      timestr(timediff($end_time, $loop_time),"all") . "\n";

  $loop_time=new Benchmark;
  $count=0;
  for ($tests=0 ; $tests < $total ; $tests+=$total/100)
  {
    $count+=1;
    fetch_all_rows($dbh,"select count(dummy1) from bench1 where id2 < $tests");
  }
  $end_time=new Benchmark;
  print "Time for count ($count): " .
      timestr(timediff($end_time, $loop_time),"all") . "\n";

  if ($limits->{'group_distinct_functions'})
  {
    $loop_time=new Benchmark;
    $count=$estimated=0;
    for ($tests=1 ; $tests <= $small_loop_count ; $tests++)
    {
      $count+=2;
      fetch_all_rows($dbh,"select count(distinct dummy1) from bench1");
      fetch_all_rows($dbh,"select dummy1,count(distinct id) from bench1 group by dummy1");
      $end_time=new Benchmark;
      last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
					     $small_loop_count));
    }
    if ($estimated)
    { print "Estimated time"; }
    else
    { print "Time"; }
    print " for count_distinct_big ($count): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";
  }
}


if ($server->small_rollback_segment())
{
  $dbh->disconnect;				# close connection
  $dbh = $server->connect();
}

####
#### Some updates on the table
####

$loop_time=new Benchmark;

if ($limits->{'functions'})
{
  print "\nTesting update of keys with functions\n";
  my $update_loop_count=$opt_loop_count/2;
  for ($i=0 ; $i < $update_loop_count ; $i++)
  {
    my $tmp=$opt_loop_count+$random[$i]; # $opt_loop_count*2 <= $tmp < $total_rows
    $sth = $dbh->do("update bench1 set id3=-$tmp where id3=$tmp") or die $DBI::errstr;
  }

  $end_time=new Benchmark;
  print "Time for update_of_key ($update_loop_count):  " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";

  if ($opt_lock_tables)
  {
    do_query($dbh,"UNLOCK TABLES");
  }
  if ($opt_fast && defined($server->{vacuum}))
  {
    $server->vacuum(1,\$dbh,"bench1");
  }
  if ($opt_lock_tables)
  {
    $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
  }

  if ($server->small_rollback_segment())
  {
    $dbh->disconnect;				# close connection
    $dbh = $server->connect();
  }

  $loop_time=new Benchmark;
  $count=0;
  $step=int($opt_loop_count/$range_loop_count+1);
  for ($i= 0 ; $i < $opt_loop_count ; $i+= $step)
  {
    $count++;
    $sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 <= $i") or die $DBI::errstr;
  }

  if ($server->small_rollback_segment())
  {
    $dbh->disconnect;				# close connection
    $dbh = $server->connect();
  }
  $count++;
  $sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 < $opt_loop_count") or die $DBI::errstr;

  if ($server->small_rollback_segment())
  {
    $dbh->disconnect;				# close connection
    $dbh = $server->connect();
  }
  $count++;
  $sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= $opt_loop_count and id3 < ". ($opt_loop_count*2)) or die $DBI::errstr;

  #
  # Check that everything was updated
  # In principle we shouldn't time this in the update loop..
  #

  if ($server->small_rollback_segment())
  {
    $dbh->disconnect;				# close connection
    $dbh = $server->connect();
  }
  $row_count=0;
  if (($sth=$dbh->prepare("select count(*) from bench1 where id3>=0"))
      && $sth->execute)
  {
    ($row_count)=$sth->fetchrow;
  }
  $result=1 + $opt_loop_count-$update_loop_count;
  if ($row_count != $result)
  {
    print "Warning: Update check returned $row_count instead of $result\n";
  }

  $sth->finish;
  if ($server->small_rollback_segment())
  {
    $dbh->disconnect;				# close connection
    $dbh = $server->connect();
  }