1.適切な主キーを設定する

InnoDBの主キーはクラスターインデックスだということを意識しよう から導き出されること

  • 更新する可能性がある項目は主キーにしない（主キーの更新はコスト高い！）
  • 主キーの項目長はなるべく小さく（全部のインデックスページの容量に悪影響！）
  • できる限り主キーでアクセスする（副次インデックスにくらべて倍は速い！）

トランザクション系テーブルに主キーとして、AUTO_INCREMENTを使うのは100%ではないが、安全策と言えます。
マスター系のテーブルには自然キーを使うほうが良いでしょう。

2.物理フォーマットの選択

　※show table statusでRow_formatの設定値は確認出来ます。

InnoDBの物理フォーマット

MySQL 5.0.3以前のバージョンでは、REDUNDANTフォーマット、以降のバージョンではCOMPACTフォーマットがデフォルトになっている

  • 少なくともREDUNDANTフォーマットでは、固定長が有利な点は一つもない
  • 固定長にたいしても、カラム数やカラム長といった冗長な情報を含む

COMPACTフォーマットで文字列型にすべて可変長を使用すれば、実際のデータ内容に応じて、20-30%程度は容量が節約できます。
これはディスク容量だけではなく、バッファプールの効率にも直結します。

MyISAMの物理フォーマット

  • 静的テーブル（FIXED）
  • データ型にVARCHAR、TEXT、BLOBを含んでいない場合に選択される
    • なにより一番速い
      • データファイル中の行がディスク上で即時見つけられる
      • キャッシュ動作が単純になる
    • 動的フォーマットテーブルよりもディスクの領域を消費する
      • CHARやVARCHARは不足部分に空白が埋められる
      • BINARYとVARBINARYカラムは不足部分にが0×00で埋められる
    • クラッシュした後も修復出来る確率が高い
  • 動的テーブル（DYNAMIC）
    • 可変長項目(VARCHAR、 VARBINARY、 BLOB、または TEXT)を含むとこれが採用される
    • BLOBやTEXTカラムが無いテーブルなら静的テーブル（FIXED）も指定可能
  • 圧縮テーブル

    • これはmyisampackで作成するもの

結論

　　　　InnoDBにはCOMPACTフォーマット
　　　　MyISAMにはFIXED
　を使用しましょう。

　※MyISAMをログ出力系にだけ使用するような場合は別です。この場合、MyISAMは
　　INSERTの処理性能によって選択されたので検索を速くする必要がないわけですから。

3.適切なデータ型の選択(文字列型)

　上記の物理フォーマットのことからも
　　InnoDBなら必ず可変長を選ぶ！
　　　MyISAMなら固定長を選ぶ！
　と言い切ってよいと思います。

4.適切なデータ型の選択(数値型)

　できる限り効率性の高い（最小）の型を使用する！

　MySQLのデータ型についてはこちらをどうぞ
　このページの下の方に記載されている「初期化パラメータsql_mode」についてはMySQLのSQL_MODEとストリクトモードのほうが詳しく書いてあります。

 

関連会社インターオフィスのブログ記事を転載しています。