Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
約6年前投稿 修正あり

MySQLで、正しいデータ型を使うことはどのくらい重要なのか?

テーブル設計においてカラムのデータ型を正しく決めることには、どのような利点があるのかについて。単純に扱う値と同じ型を選ぶべきであるというだけではなく、なぜそうあるべきかについて、内部的な効率の面から解説する。

原文
How important is using the correct datatype in MySQL? | Master MySQL (English)
翻訳依頼者
D98ee74ffe0fafbdc83b23907dda3665
翻訳者
D98ee74ffe0fafbdc83b23907dda3665 doublemarket
原著者への翻訳報告
未報告


パフォーマンスに関する話の中で、カラムに値を保存するのに正しいデータ型を使うことの重要性を説いているのを聞くことがよくある。例えば、数値はINTBIGINTで表現し、IPアドレスにはINT UNSIGNEDを使い、VARCHAR(255)の代わりにVARCHAR(60)を使うといったことだ。

このアドバイスは正しい。しかし、今日はもう少し詳細の説明を試みてみようと思う。

理由

この最適化が正しいと思う3つの理由は以下の通りだ。

  1. 文字列として数値データを扱うことは、文字コードや照合処理のCPUオーバーヘッドが余計に必要になってしまう。例えば、'Montréal' = 'Montreal' = 'MONTREAL' とするコストはゼロではないが、MySQLはデフォルトでそう扱う。

  2. 正しいデータ型を使うと容量を節約できる。ここで言う「容量」は通常、ディスクにおさまるかというより、キャッシュの効率をよくできるようにメモリ上でデータを扱えるかどうかの意味である。容量の小さいSSDではディスクにおさまるかどうかも重要になる。

  3. 転送プロトコルやクライアントライブラリには、可変長でバッファしないものもある。これは私の知識領域から少し範囲外ではあるが、大きな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ページサイズを変更でき、このような場合にメモリにデータを保持させるのに役立つだろう。

これが私の考えた全てだが、何か忘れているだろうか?

次の記事
PostgreSQLのコマンドラインの動きを改善してみる
前の記事
MySQLの大きなテーブルでのパフォーマンスを改善する10の方法

Feed small 記事フィード

新着記事Twitterアカウント

新着翻訳リクエストTwitterアカウント