0

MySQL中使用JOIN与使用递归的效率对比

Posted in MySQL at 七月 2nd, 2010 / No Comments »

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

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

Tag Clouds

apache centos centos7 chrome CVS div eclipse firefox git ie6 ios ipad iphone jquery js Linux mysql openfire opensuse php PHP安全 PHP语法 proxy putty Rewrite samba shell SMTP ssh suse ThinkPad ThinkPHP VirtualBox vmware windows WordPress 中文 代理 全屏 发邮件 命令行 搜索引擎 正则表达式 汉字 面向对象

Categories

Archives

Blogroll