パフォーマンスに関する話の中で、カラムに値を保存するのに正しいデータ型を使うことの重要性を説いているのを聞くことがよくある。例えば、数値はINTやBIGINTで表現し、IPアドレスにはINT UNSIGNEDを使い、VARCHAR(255)の代わりにVARCHAR(60)を使うといったことだ。
このアドバイスは正しい。しかし、今日はもう少し詳細の説明を試みてみようと思う。
理由
この最適化が正しいと思う3つの理由は以下の通りだ。
文字列として数値データを扱うことは、文字コードや照合処理のCPUオーバーヘッドが余計に必要になってしまう。例えば、
'Montréal' = 'Montreal' = 'MONTREAL'とするコストはゼロではないが、MySQLはデフォルトでそう扱う。正しいデータ型を使うと容量を節約できる。ここで言う「容量」は通常、ディスクにおさまるかというより、キャッシュの効率をよくできるようにメモリ上でデータを扱えるかどうかの意味である。容量の小さいSSDではディスクにおさまるかどうかも重要になる。
転送プロトコルやクライアントライブラリには、可変長でバッファしないものもある。これは私の知識領域から少し範囲外ではあるが、大きな
VARCHARの値ではより多くのメモリを消費するだろうから、必要な長さを判断する努力は意味がある。
メモリ上でデータを扱えるか
この最適化がなぜ正しいかの一番の理由に、上の2.を挙げたい。実際のところ、DBAによる最適化の大部分は、できる限り長い間データがメモリ上で扱われるようにすることだろう。例えば、インデックスによって、テーブルスキャンを防ぎ、必要なデータだけに焦点を当てることができる。
短いプライマリキー : できる限り
PRIMARY KEYを短く保つことによって、たくさんのスペースを節約することができる。InnoDBではPRIMARY KEYの値でクラスタインデックスを使い、行への内部的なポインタとしてセカンダリインデックスを使う。インデックスを張るカラムのサイズを小さくする : インデックスを張った値というのは本質的には重複することになる。なぜなら、その値はインデックスとテーブルの行の両方にあることになるからだ。従って、この非効率さによってデータが増幅されてしまう。
どれくらいメモリ上でデータを扱えるかの効率性は、どれくらいインデックスがアクセスされるかによって来る。例えば、
SELECT DISTINCT(indexed_col) FROM my_tableでは、インデックスの全体がメモリにあるのが最も効率が良いSELECT * FROM my_table WHERE indexed_col BETWEEN x AND yのようなレンジアクセスでは、範囲が大きいほどより大きなメモリを必要とする。これは、レンジアクセスではないがindexed_colがユニークでなく、複数の値にマッチするような場合も同じだ(例えば、SELECT * FROM mytable WHERE indexed_col = x)。- インデックスのカーディナリティがよく(例えば広く分散した値)、値全体の比較的小さな範囲にのみアクセスするクエリを実行するなら、メモリ上でデータを扱えるかどうかはそこまで重要ではない。
インデックスを張らないカラムのサイズを小さくする : 上と同じく、インデックスを張らないカラムのサイズを小さくすることも重要だ。ただし、上の項ほど重要ではない。私が一番気になるのは、テーブルスキャンを含むアクセスがあるか、よくアクセスされる「ホットな」行が多数のページにまたがって分散している場合だ。
ちょっと補足 : MySQL 5.6ではInnoDBページサイズを変更でき、このような場合にメモリにデータを保持させるのに役立つだろう。
これが私の考えた全てだが、何か忘れているだろうか？
