事务提交大量sql与合并提交大量sql效率对比

事务提交5000条update，耗时：13.79 秒，内存占用：17.88 MB（大量时间消耗在Query生成sql语句上）

$startTime  = microtime(true);$startMemory = memory_get_usage();$transaction = \Yii::$app->db->beginTransaction();for($i=0; $i< 5000; ++$i) {    \Yii::$app->db->createCommand()->update(UserMenu::tableName(), ['menu_id' => 4], ['user_id' => $id, 'menu_id' => 3])->execute();}$transaction->commit();$endTime    = microtime(true);$runtime    = number_format($endTime - $startTime, 2); //秒$endMemory  = memory_get_usage();$usedMemory = number_format(($endMemory - $startMemory) / 1024 / 1024, 2);    //MBecho("耗时：$runtime 秒，内存占用：$usedMemory MB");

事务提交5000条update，耗时：9.59 秒，内存占用：16.66 MB（一次性生成sql语句）

$transaction = \Yii::$app->db->beginTransaction();$sql = \Yii::$app->db->createCommand()->update(UserMenu::tableName(), ['menu_id' => 4], ['user_id' => $id, 'menu_id' => 3])->getRawSql().';';$startTime  = microtime(true);$startMemory = memory_get_usage();for($i=0; $i< 5000; ++$i) {    \Yii::$app->db->createCommand($sql)->execute();}$transaction->commit();$endTime    = microtime(true);$runtime    = number_format($endTime - $startTime, 2); //秒$endMemory  = memory_get_usage();$usedMemory = number_format(($endMemory - $startMemory) / 1024 / 1024, 2);    //MB
echo("耗时：$runtime 秒，内存占用：$usedMemory MB");

合并sql提交5000条update，耗时：13.05 秒，内存占用：0.37 MB（主要时间都耗在Query生成sql语句上了）

$startTime  = microtime(true);$startMemory = memory_get_usage();$transaction = \Yii::$app->db->beginTransaction();$sql = '';for($i=0; $i< 5000; ++$i) {    $sql .= \Yii::$app->db->createCommand()->update(UserMenu::tableName(), ['menu_id' => 4], ['user_id' => $id, 'menu_id' => 3])->getRawSql().';';}\Yii::$app->db->createCommand($sql)->execute();$transaction->commit();$endTime    = microtime(true);$runtime    = number_format($endTime - $startTime, 2); //秒$endMemory  = memory_get_usage();$usedMemory = number_format(($endMemory - $startMemory) / 1024 / 1024, 2);    //MB
echo("耗时：$runtime 秒，内存占用：$usedMemory MB");

合并sql提交5000条update，耗时：4.84 秒，内存占用：0.00 MB（一次性生成sql语句）

$transaction = \Yii::$app->db->beginTransaction();$sql = '';for($i=0; $i< 5000; ++$i) {    $sql .= \Yii::$app->db->createCommand()->update(UserMenu::tableName(), ['menu_id' => 4], ['user_id' => $id, 'menu_id' => 3])->getRawSql().';';}$startTime  = microtime(true);$startMemory = memory_get_usage();\Yii::$app->db->createCommand($sql)->execute();$transaction->commit();$endTime    = microtime(true);$runtime    = number_format($endTime - $startTime, 2); //秒$endMemory  = memory_get_usage();$usedMemory = number_format(($endMemory - $startMemory) / 1024 / 1024, 2);    //MB
echo("耗时：$runtime 秒，内存占用：$usedMemory MB");