mysql id in 效率,MySQL的“IN”操作符性能（数量很多）

我一直在做一些testing，正如大卫·费尔斯(David Fells)所说，这个testing已经非常好了。 作为一个参考，我创build了一个带有1000000个寄存器的InnoDB表，并使用500000个随机数的“IN”运算符进行select，在我的MAC中只需要2.5s。 (只select偶数寄存器需要0.5s)。

我唯一的问题是我不得不从my.cnf文件中增加max_allowed_pa​​cket参数。 如果没有，会产生一个神秘的“MYSQL已经消失”的错误。

这里是我用来做testing的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; $i0) $Values .= ","; $Values .= "( $i , 'This is value $i and r= $r')"; if ($r0))) { $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