MySQL データ型の選択原則

小さいけれど美しい

一般的に言えば、データを保存する際には、できるだけ少ないストレージスペースしか占有しないデータ型を使用するようにしてください。このようなデータ型は一般に高速で、ディスク容量、メモリ、キャッシュの消費量が少なく、CPU 処理サイクルの消費も少なくなります。

ただし、保存するデータ値の範囲を正確に見積もることが重要です。テーブル構造内の複数の場所でデータ範囲を拡張することは、面倒で時間のかかるプロセスになる可能性があるためです。どのデータ型が適切か迷った場合は、範囲を超えないと思われる最小のスペースを持つ型を選択してください（システムの初期段階やデータテーブルに多くのデータが含まれていない場合は調整することもできます）。

シンプルに

データ型が単純になればなるほど、データを処理するための CPU サイクルが少なくなります。たとえば、文字セットと照合順序によって文字の比較が複雑になるため、整数は文字よりも扱いやすくなります。 2 つの例を挙げると、時間と日付を保存するには、文字列ではなく、MySQL の組み込み型を使用する必要があります。 IP アドレスも整数を使用して保存する必要があります。

NULL値を避ける

多くのデータ テーブルでは NULL 許容列が必要ですが、デフォルト値 NULL をアプリケーションに格納する必要はありません。一般的に言えば、列に NULL を格納するよりも、列が NOT NULL であることを指定する方が適切です。

MySQL では、null 可能列によってインデックス、インデックス統計、および値の比較が複雑になるため、null 可能列を含む最適化がより困難になります。さらに、null 許容列はより多くのストレージ スペースを占有し、特別な処理が必要になります。 NULL 可能列にインデックスが指定されている場合、インデックス エントリごとに追加のバイトが必要になり、MyISAM エンジンによって固定サイズのインデックスが可変サイズのインデックス (たとえば、整数フィールドの単一列インデックス) に変換されることもあります。 ただし、NULL 列を NOT NULL 列に変換しても、パフォーマンスの向上は通常それほど大きくありません。したがって、NULL 列がパフォーマンスに大きな影響を与えることがわかった場合を除き、既存のデータ テーブル構造の変更を優先しないでください。ただし、列にインデックスを作成する必要がある場合は、列の値が空にならないようにする必要があります。通常は、列を直接 NOT NULL に設定するのがよい習慣です。

もちろん、例外もあります。たとえば、InnoDB では NULL 値を格納するために 1 ビットのみが使用されるため、大量のデータを格納するためのスペースを効果的に節約できますが、MyISAM エンジンではそうではありません。

データタイプを選択する手順

データ型を選択する最初のステップは、データ列を表すために使用する一般的なデータ型（数値型、文字列型、または時刻型）を決定することです。通常は直接選択しても問題ありませんが、例外もいくつかあります (金額、タイムスタンプなど)。

2 番目のステップは、特定のタイプを選択することです。 MySQL には、データ値の範囲、精度、物理的なストレージ領域に基づいて、同じデータ型に対して複数のストレージ方法があり、一部のデータ型には特別なプロパティがあります。

たとえば、DATETIME と TIMESTAMP はどちらも時刻と日付を秒単位の精度で保存できます。ただし、TIMESTAMP 型では必要なストレージ容量が半分で、タイムゾーン情報が含まれ、自動更新がサポートされます。しかしその一方で、データを保存できる期間が短いため、こうした特殊な特性が障害となる可能性もあります。

基本的なデータ型を見てみましょう。 MySQL は、INTEGER、BOOL、NUMERIC などのデータ型のエイリアスをサポートしています。これらは単なるエイリアスであり、混乱しているように見えるかもしれませんが、実際にはパフォーマンスには影響しません。エイリアス データ型を使用してデータ テーブルを作成する場合は、SHOW CREATE TABLE を使用したことを思い出し、MySQL が実際にエイリアスではなく基本データ型に変換することを確認できます。

データ型: 列に格納できるデータとそのデータが実際にどのように格納されるかを定義する基本ルール。

データ型は次の目的で使用されます。

1. 列に保存できるデータを制限できます。たとえば、数値データ型の列は数値のみを受け入れることができます。

2. 内部でより効率的にデータを保存できます。たとえば、数値や日付と時刻の値をテキスト文字列よりも簡潔な形式で保存します。

3. 並べ替え順序を変更できるようにします。たとえば、すべてのデータを文字列として扱うと、1 は 10 より前、10 は 2 より前になります (文字列は辞書順にソートされ、左から 1 文字ずつ比較されます)。数値データ型として、値を正しくソートできます。

データ型の紹介

1. 文字列データ型

最も一般的に使用されるデータ型で、名前、住所、電話番号などの文字列を格納します。

文字列には、固定長文字列と可変長文字列の 2 つの基本的な種類があります。

固定長文字列: テーブルの作成時に長さが指定される固定長の文字列を受け入れます。固定長列では、指定された文字数を超える文字は許可されず、指定された量のストレージ スペースが割り当てられます。例: CHAR。

可変長文字列: 可変長のテキストを格納します。可変長データ型には、最大長が固定されているものもあれば、完全に可変長であるものもあります。タイプに関係なく、TEXT など、指定されたデータのみが保存されます (余分なデータは保存されません)。

PS: MySQL は、可変長列よりも固定長列をはるかに高速に処理します。また、MySQL では可変長列 (または列の可変部分) のインデックス作成は許可されません。

データ型の説明:

• CHAR: 1 ～ 255 文字の固定長文字列。長さは作成時に指定する必要があります。指定しない場合、MySQL は CHAR(1) であると想定します。
• ENUM: 最大 64K の文字列の定義済みセットから文字列を受け入れます。
• LONGTEXT: TEXT と同じですが、最大長は 4GB です。
• MEDIUMTEXT: TEXT と同じですが、最大長は 16K です。
• SET: 最大 64 個の文字列の定義済みセットから 0 個以上の文字列を受け入れます。
• TEXT: 最大長 64 KB の可変長テキスト。
• TINYTEXT: TEXT と同じですが、最大長は 255 バイトです。
• VARCHAR: 可変長、255 バイト以下。データの作成時に VARCHAR(n) を指定すると、0 ～ n 文字 (n ≤ 255) の可変長文字列を格納できます。

追伸:

1. 引用符: どの形式の文字列データ型を使用する場合でも、文字列値は引用符 (通常は一重引用符) で囲む必要があります。

2. 従うべき基本的なルール: 値が計算 (合計、平均など) に使用される場合、その値は数値データ型の列に格納されます。値が文字列として使用される場合、文字列データ型の列に格納されます。たとえば、郵便番号 01234 を数値フィールドに保存すると、1 桁が欠落した値 1234 が保存されます。

2. 数値データ型

数値を格納します。 MySQL は複数の数値データ型をサポートしており、それぞれが異なる値の範囲の値を格納します。

サポートされる値の範囲が広いほど、必要なストレージ容量も大きくなります。さらに、一部の数値データ型では小数点 (および小数) の使用がサポートされていますが、他のデータ型では整数のみがサポートされています。表 D-2 に、よく使用される MySQL 数値データ型を示します。

追伸:

1. すべての数値データ型 (BIT と BOOLEAN を除く) は、符号付きまたは符号なしにすることができます。符号付き数値列には正または負の値を格納できますが、符号なし数値列には正の数値のみを格納できます。

2. デフォルトは符号付きです。負の値を保存する必要がない場合は、UNSIGNED を使用すると、2 倍のサイズの値を保存できます。

3. 文字列とは異なり、値は引用符で囲まないでください。

4. MySQL には通貨を保存するための特別なデータ型はありません。通常は DECIMAL(8, 2) が使用されます。

データ型の説明:

• BIT: ビット フィールド、1 ～ 64 ビット。 MySQL 5 より前では、BIT は機能的に TINYINT と同等でした。
• BIGINT: 整数値。-9223372036854775808 から 9223372036854775807 までをサポートします。 UNSIGNED の場合は 0 から 18446744073709551615 までの数値になります。
• BOOLEAN (または BOOL): 0 または 1 のブール フラグ。主にオン/オフ フラグとして使用されます。
• DECIMAL (または DEC): 可変精度の浮動小数点値。
• DOUBLE: 倍精度浮動小数点値
• FLOAT: 単精度浮動小数点値
• INT (または INTEGER): 整数値、-2147483648 から 2147483647 をサポート、UNSIGNED は上記と同じです。
• MEDIUMINT: 整数値、-8388608 から 8388607 をサポート、UNSIGNED は上記と同じです。
• REAL: 4 バイトの浮動小数点値。
• SMALLINT: 整数値、-32768 から 32767 をサポート、UNSIGNED は上記と同じです。
• TINYINT: 整数値、-128 から 127 をサポート、UNSIGNED は上記と同じです。

3. 日付と時刻のデータ型

データ型の説明:

• DATE: 1000-01-01 から 9999-12-31 までの日付を YYYY-MM-DD 形式で示します。
• DATETIME: DATE と TIME の組み合わせ。
• TIMESTAMP: 機能は DATETIME と同じですが、範囲が狭くなります。
• TIME: 形式は HH:MM:SS です。
• 年: 2 桁、範囲 70 から 69 (1970 から 2069)、4 桁、範囲 1901 から 2155

4. バイナリデータ型

画像、マルチメディア、ワードプロセッサ文書など、あらゆるデータ（バイナリ情報も含む）を保存できます。

データ型の説明:

• BLOB: BLOB の最大長は 64 KB です。
• MEDIUMBLOB: 最大 BLOB 長は 16 MB です。
• LONGBLOB: BLOB の最大長は 4GB です。
• TINYBLOB: BLOB の最大長は 255 バイトです。

結論：

MySQL には、データを表現する方法が多数あります。テーブル作成の最初からパフォーマンスに重点を置けるよう、一般的なデータ型の保存範囲や占有バイト数を理解し、製品の推定データ値の範囲や長さに基づいて適切なデータ型をできるだけ選択することをお勧めします。後から調整を行うコストは、設計開始時に慎重に検討する時間コストを上回ることがよくあります。

上記は、MySQL が適切なデータ型を選択する必要がある理由の詳細です。MySQL データ型の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。