MyISAM:

Join 178 Processed in 3.971571 second(s)
递归 178 Processed in 0.476336 second(s)

InnoDB:

Join 178 Processed in 1.142751 second(s)
递归 178 Processed in 0.658390 second(s)

建了十个表,每个表随机生成了1W条数据。

数据库:

CREATE TABLE `t2` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` int(10) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f2` (`f2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

测试代码:

<?php

$db=mysql_connect(‘localhost’,'root’,'root’);
mysql_select_db(‘test’,$db);

//1
$starttime=getProcessedTime();
$sql=”select t1.* from t1
 left join t2 ON t1.f1=t2.f2
 left join t3 ON t2.f2=t3.f3
 left join t4 ON t3.f3=t4.f4
 left join t5 ON t4.f4=t5.f5
 left join t6 ON t5.f5=t6.f6
 left join t7 ON t6.f6=t7.f7
 left join t8 ON t7.f7=t8.f8
 left join t9 ON t8.f8=t9.f9
 left join t10 ON t9.f9=t10.f10
 where t10.f10>0
 group by t1.f1
“;
$rs=mysql_query($sql, $db);
$result=array();
if ($rs){
 //exit(‘ok’);
 while($row=mysql_fetch_array($rs)){
  //echo $row['f1'].’ ‘;
  //print_r($row);
  //exit(‘ok’);
  $result[] = $row;
 }
}
showProcessedTime($starttime, ‘Join ‘.count($result));

//2
$starttime=getProcessedTime();
$where=”f10>0″;
for ($i=10; $i>1; $i–){
 $result = getData($i, $where, $db);
 $where=”f”.($i-1).” in (“.implode(‘,’, $result).”)”;
}
$sql=”select * from t1 where {$where}”;
$rs=mysql_query($sql, $db);
$result=array();
if ($rs){
 while($row=mysql_fetch_array($rs)){
  //echo $row['f1'].’ ‘;
  $result[] = $row;
 }
}
showProcessedTime($starttime, ‘递归 ‘.count($result));

function getData($i, $where, $db){
 $sql=”select f{$i} from t$i where {$where}”;
 $rs=mysql_query($sql, $db);
 $result = array();
 if ($rs){
  while($row=mysql_fetch_array($rs)){
   $result[] = $row["f{$i}"];
  }
 }
 return $result;
}

function getProcessedTime(){
 $mtime = explode(‘ ‘, microtime());
 $starttime = $mtime[0] + $mtime[1];
 return $starttime;
}

//————-运行时间显示,一般在程序结尾—————
function showProcessedTime($starttime,$debug=”"){
 $mtime = explode(‘ ‘, microtime());
 $totaltime = number_format(($mtime[0] + $mtime[1] – $starttime), 6);
 echo “$debug Processed in <b>”.$totaltime.”</b> second(s)<br>”;
}

数据生成:

<?php
$db=mysql_connect(‘localhost’,'root’,'root’);
mysql_select_db(‘test’,$db);

for($t=1;$t<=10;$t++){
 $table=’t’.$t;
 echo ‘<br>Table ‘.$table.’:<br>’;
 for($i=0;$i<10000;$i++){
  $v=array();
  $sql=”INSERT INTO `{$table}` (`f1`,`f2`,`f3`,`f4`,`f5`,`f6`,`f7`,`f8`,`f9`,`f10`) VALUES (NULL”;
  for($j=2;$j<=10;$j++){
   $value=”;
   if ($j==$t){
    $value=rand(1,10000);
   }else{
    $value=getValue();
   }
   $sql.=”, ‘{$value}’”;
  }
  $sql.=”)”;
  $rs=mysql_query($sql);
  //echo $sql;exit;
  if ($rs){
   echo $i;
  }
 }
}

function getValue(){
 $alpha = “ABCDEFGHJKMNPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz”;
 return $alpha;
 $rs=”;
 for($i=0;$i<400;$i++){
  $rs.=substr($alpha, rand(0, strlen($alpha)-1), 1);
 }
 return $rs;
}

/*
CREATE TABLE `t1` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` varchar(500) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `t2` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` int(10) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f2` (`f2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `t3` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` varchar(500) NOT NULL,
  `f3` int(10) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f3` (`f3`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `t4` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` varchar(500) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` int(10) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f4` (`f4`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `t5` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` varchar(500) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` int(10) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f5` (`f5`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `t6` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` varchar(500) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` int(10) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f6` (`f6`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `t7` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` varchar(500) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` int(10) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f7` (`f7`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `t8` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` varchar(500) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` int(10) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f8` (`f8`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `t9` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` varchar(500) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` int(10) NOT NULL,
  `f10` varchar(500) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f9` (`f9`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `t10` (
  `f1` int(10) NOT NULL auto_increment,
  `f2` varchar(500) NOT NULL,
  `f3` varchar(500) NOT NULL,
  `f4` varchar(500) NOT NULL,
  `f5` varchar(500) NOT NULL,
  `f6` varchar(500) NOT NULL,
  `f7` varchar(500) NOT NULL,
  `f8` varchar(500) NOT NULL,
  `f9` varchar(500) NOT NULL,
  `f10` int(10) NOT NULL,
  PRIMARY KEY  (`f1`),
  KEY `f10` (`f10`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

alter table t1 type = InnoDB;  
alter table t2 type = InnoDB;  
alter table t3 type = InnoDB;  
alter table t4 type = InnoDB;  
alter table t5 type = InnoDB;  
alter table t6 type = InnoDB;  
alter table t7 type = InnoDB;  
alter table t8 type = InnoDB;  
alter table t9 type = InnoDB;  
alter table t10 type = InnoDB;
*/

Published in MySQL
Tags: ,

No Responses to “MySQL中使用JOIN与使用递归的效率对比”

Leave a Reply

请输入算式结果(看不清请点击图片)
(必须)