MySQLでAuto Increment利用による速度低下

Webアプリを開発する時に切っても切れない関係にあるのがMySQLなどのRBBMSです．これらをいかに上手に扱うかが，エンジニアリングの面白いところでもあり，難しいところでもあります．

私は今までデータベースでテーブルの設計をするときには，各テーブルに通し番号を記憶するためのフィールドを作成して，Auto Incrementで番号を振っていました．

こうしておくと，私が何も考えなくても（Insert文でNULLを指定しておけば），各レコードに連番が付与されるので，非常に便利です．

ただ，知ってる人からすると当たり前じゃんと思われるかもしれませんが，Auto Increment属性を指定すると，Insertの速度が遅くなってしまうんですよね．というわけで，どれぐらい速度が遅くなるのか，実際に調べてみました．

実験環境

実験環境は下記のとおり．私の手元にあったSONY製のノートPC上にApache + PHP + MySQLをインストールしています．

• Sony VGN-Z90NS
• Microsoft Windows Vista
• Apache 2.2.12
• PHP 5.3.0
• MySQL 5.1.37

実験コード

実験に利用したコードはこんな感じです．

Auto Incrementありのテーブル定義

CREATE TABLE IF NOT EXISTS `autoinc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Auto Incrementありのテーブルに10000件のデータを突っ込むコード

list($micro, $Unixtime) = explode(" ", microtime());
$time1 = $Unixtime + $micro;
$pdo = new PDO($dsn, $db_username, $db_pass);
$data  = "hoge";
for($i = 0; $i < 10000; $i++){
    try {
        $stmt = $pdo->prepare("INSERT INTO autoinc (id,data) VALUES (NULL, :data)");
        $stmt->bindParam(":data", $data);
        $data = $stmt->execute();
    } catch(PDOException $e){
        var_dump($e->getMessage());
    }
}
$pdo = null;
list($micro, $Unixtime) = explode(" ", microtime());
$time2 = $Unixtime + $micro;
echo $time2 – $time1;

Auto Incrementなしのテーブル定義

CREATE TABLE IF NOT EXISTS `noinc` (
  `id` int(11) NOT NULL,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Auto Incrementなしのテーブルに10000件のデータを突っ込むコード

list($micro, $Unixtime) = explode(" ", microtime());
$time1 = $Unixtime + $micro;
$pdo = new PDO($dsn, $db_username, $db_pass);
$pdo->beginTransaction();
$data  = "hoge";
for($i = 0; $i < 10000; $i++){
    try {
        $stmt = $pdo->prepare("INSERT INTO noinc (id,data) VALUES (:id, :data)");
        $stmt->bindParam(":id", $i);
        $stmt->bindParam(":data", $data);
        $data = $stmt->execute();
    } catch(PDOException $e){
        var_dump($e->getMessage());
    }
}
$pdo->commit();
$pdo = null;
list($micro, $Unixtime) = explode(" ", microtime());
$time2 = $Unixtime + $micro;
echo $time2 – $time1;

実験の結果

上記のコードを用いて何度か実験して平均を取ってみたところ，こんな感じの結果になった．

• Auto Incrementあり：1.72秒
• Auto Incrementなし：1.52秒

1割程度の差だが，明らかにAuto Incrementを利用することによって速度低下が起こっている．この1割の速度差を大きいと見るか，小さいと見るかについては，開発するアプリケーションの性質によって異なってくるものだと思うので，実際に，データのCreate，Read，Update，Deleteの頻度を考慮して検討すると良いと思います．

最近，データベースは非常に奥が深いことがわかってきました．もっと真面目に勉強しないといけませんね．