INSERT@MySQL:auto_incrementカラムはINSERTを遅くするか?

 MySQLでは、テーブルに auto_increment カラムがあるとINSERTが遅くなるという話をよく耳にします。どの程度遅くなるのか測定してみました。
 今回行ったのは auto_increment カラム有無だけが異なる2つのテーブルに対して、値を指定して(つまり事実上 auto_increment の自動加算には任せないで)INSERTを行ったものです。

使用したテーブルおよびINSERT文

 以下2つのテーブル、ai1/ai2 を使用しました。 ai2には idカラムに auto_increment が指定されていることのみが異なります。

ai1: 
  CREATE TABLE ai1 ( id   int          NOT NULL, 
                       col1 varchar(128),
                       PRIMARY KEY (id) );
ai2: 
  CREATE TABLE ai2 ( id   int          NOT NULL AUTO_INCREMENT, 
                       col1 varchar(128),
                       PRIMARY KEY (id) );
INS:例:  INSERT INTO ai1 (id, col1) VALUES (4, 'HP0_Yfmyf');

※INSERTはカラム名を与える方法にした。
※id の値は1から始まり件数ぶんまで INSERT文内にて明示的に指定した。
※col1 の値は 6-100文字でランダムに生成させた文字列
※上記各テーブルに、1万、2万、5万、10万、20万、30万、40万、50万件のINSERTを行った
※テーブルタイプは MyISAM, InnoDB それぞれに対して行った

バージョン、マシンスペック、設定パラメタ

MySQL 5.0.51a を使用。その他の設定パラメタ、マシンスペックについては、
http://d.hatena.ne.jp/sakaik/20080930/mysql_insert_column
を参照。

結果

 MyISAMInnoDB それぞれ以下のようになった。
1-1 が ai1テーブル(auto_increment なし)で、2-1 が ai2 テーブル(auto_increment カラムあり)である。
ai1テーブル、ai2テーブル双方に対し、(各件数に於いて)発行したINSERT文はまったく同一のものである。


MyISAM



InnoDB


感想

  • 微妙な差ではあるが、差があったと判断してよいと思う。
  • MyISAMは途中まで auto_increment 設定有無による差がなかったのだが途中から崩れたという感じ
  • InnoDB は最初(少件数)からじわじわと差があったと判断してよさそう。
  • 今回のINSERT文のような、結局auto_increment にカウントアップをさせないで値を指定するのならば、無駄な auto_increment 指定なんかやめとけ!と結論付けてよさそう。
  • 30秒程かかる処理に「たったの」3秒程度の差、と解釈するか、1割近くの差と解釈するかはそれぞれ。 ただし経験上割合よりも秒数の差がものを言うレンジであると私は思う(差は微々たるもの、とする立ち位置)。

今後の予定

  • 前回 1-1/1-2 (カラム名指定の有無による差)で単なるINSERT文バイト数の差ではないか疑惑に関する追加調査( 1' )
  • auto_increment機能を使用する場合、カラム名指定でNULLを与えたほうが良いのか、カラム名無指定のほうが良いのかについての測定 ( 3 )