mysql中in运算符,MySQL"IN"运算符的性能(大？)数值

jbaylina..

20

我一直在做一些测试,正如David Fells在他的回答中所说,它已经得到了很好的优化.作为参考,我创建了一个包含1,000,000个寄存器的InnoDB表,并使用"IN"运算符进行选择,其中包含500,000个随机数,在我的MAC上只需2.5秒; 仅选择偶数寄存器需要0.5秒.

我遇到的唯一问题是我必须max_allowed_packet从my.cnf文件中增加参数.如果没有,就会产生一个神秘的"MYSQL消失"错误.

这是我用来进行测试的PHP代码:

$NROWS =1000000;

$SELECTED = 50;

$NROWSINSERT =15000;

$dsn="mysql:host=localhost;port=8889;dbname=testschema";

$pdo = new PDO($dsn, "root", "root");

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("drop table if exists `uniclau`.`testtable`");

$pdo->exec("CREATE TABLE `testtable` (

`id` INT NOT NULL ,

`text` VARCHAR(45) NULL ,

PRIMARY KEY (`id`) )");

$before = microtime(true);

$Values='';

$SelValues='(';

$c=0;

for ($i=0; $i

$r = rand(0,99);

if ($c>0) $Values .= ",";

$Values .= "( $i , 'This is value $i and r= $r')";

if ($r

if ($SelValues!="(") $SelValues .= ",";

$SelValues .= $i;

}

$c++;

if (($c==100)||(($i==$NROWS-1)&&($c>0))) {

$pdo->exec("INSERT INTO `testtable` VALUES $Values");

$Values = "";

$c=0;

}

}

$SelValues .=')';

echo "
";

$after = microtime(true);

echo "Insert execution time =" . ($after-$before) . "s
";

$before = microtime(true);

$sql = "SELECT count(*) FROM `testtable` WHERE id IN $SelValues";

$result = $pdo->prepare($sql);

$after = microtime(true);

echo "Prepare execution time =" . ($after-$before) . "s
";

$before = microtime(true);

$result->execute();

$c = $result->fetchColumn();

$after = microtime(true);

echo "Random selection = $c Time execution time =" . ($after-$before) . "s
";

$before = microtime(true);

$sql = "SELECT count(*) FROM `testtable` WHERE id %2 = 1";

$result = $pdo->prepare($sql);

$result->execute();

$c = $result->fetchColumn();

$after = microtime(true);

echo "Pairs = $c Exdcution time=" . ($after-$before) . "s
";

结果如下:

Insert execution time =35.2927210331s

Prepare execution time =0.0161771774292s

Random selection = 499102 Time execution time =2.40285992622s

Pairs = 500000 Exdcution time=0.465420007706s