MySQL (InnoDB) における行のサイズと速度の関係について

集約演算を行うケースでは、行のサイズを小さく保つことはとても重要。アクセス頻度が低いコラムは別テーブルに追い出すとかしたほうがいいくらい。

一方、集約演算を行わないケース (単一行の insert, update 等を含む) の場合は、(クライアントとの通信のための) システムコールがオーバーヘッドになるので、小さなテーブルにたくさんアクセスをするよりも、長い行を持つテーブルに１回アクセスするほうが良い。

たとえば手元の環境での insert on duplicate key update の速度は、

行のサイズ 必要時間
0KB 1
3KB 4
6KB 7
9KB 13
12KB 13

とかそんな感じ (環境やクエリによる変わるので自分で測定してね。9KB の速度低下はページサイズの1/2を超えたからかな)。つまり、行のサイズが1KB程度だと、通信のオーバーヘッドが大きいからあまり問題にならない。

まとめておくと、最適化されたテーブル設計の基本は、

  1. できるだけ少数の SQL クエリでアクセス可能にする
  2. 高速にアクセスできるようテーブル分割をする

という順序。多数の SQL クエリを投げるのは、一般的に間違った戦略。それよりは MySQL 内で join させたほうが良い。

2/11 追記:　id:lethevert さんが書いてらっしゃるように、アクセス頻度によってテーブルを分割したりインデックスを設定したりすることで、HDD へのアクセスを最小化することが必要なのは、言うまでもありません (データベースのレコードサイズ - lethevert is a programmer) 。自分が扱ってるのが、大部分がオンメモリなデータベースだったので触れるのを忘れていましたorz id:lethevert++