MySQL データ型の最適化の原則

MySQL は多くのデータ型をサポートしており、高パフォーマンスを得るには適切なデータ型を選択することが重要です。より良い選択を行うために役立ついくつかの簡単な原則を紹介します。

• 小さいほうが良いことが多い
  

データを正しく保存できる最小のデータ型を使用するようにしてください。一般的に、データ タイプが小さいほど、ディスク、メモリ、CPU キャッシュの使用量が少なくなり、処理に必要な CPU サイクルも少なくなるため、処理速度が速くなります。どのデータ型が最適かわからない場合は、範囲を超えないと思われる最小のデータ型を選択してください。

• シンプルに

通常、単純なデータ型の操作では、必要な CPU サイクルが少なくなります。たとえば、文字セットと照合順序によって文字の比較が整数の比較よりも複雑になるため、整数演算は文字演算よりもコストがかかりません。たとえば、日付と時刻を保存するには文字列ではなく MySQL の組み込み型を使用し、IP アドレスを保存するには整数を使用します。

• nullを避けるようにしてください

NULL 可能列を含むクエリは、NULL 可能列によってインデックス、インデックス統計、および値の比較がより複雑になるため、MySQL では最適化がより困難になります。 NULL 可能列はより多くのストレージスペースを使用するため、MySQL では特別な処理が必要になります。 NULL 可能列にインデックスが付けられると、各インデックス レコードに追加のバイトが必要になり、MyISAM では固定サイズのインデックスが可変サイズのインデックスになることもあります。

一般に、NULL 可能列を NULL 不可列に変更してもパフォーマンスの向上はわずかであるため、問題が発生することが確実でない限り、既存のスキーマでこの状況を見つけて修正する必要はありません。

例外として、InnoDB は別のビットを使用して null 値を格納するため、スパース データ (多くの値が null で、列に null 以外の値を持つ行が数行のみ) ではスペース効率が優れていますが、これは MyISAM には当てはまりません。

列のデータ型を選択するとき。

最初のステップは、適切な大きな型 (数値、文字列、時間など) を決定することです。 2 番目のステップは、特定の型を選択することです。多くの MySQL データ型は同じタイプのデータを保存できますが、保存の長さと範囲が異なり、許容される精度が異なり、必要な物理スペースが異なります。

整数型

これらの型は整数を格納するために使用できます

これらは -2^(N-1)^ から 2^(N-1)^-1 までの範囲の値を格納できます。ここで、N は格納スペースのビット数です。
整数型にはオプションの符号付きプロパティもあり、これは負の値は許可されないことを意味し、正の数の上限を 2 倍にすることができます。例えば、unsigned tinyint に格納できる範囲は 0 から 255 ですが、負の値も許容しますが、格納範囲は -128 から 127 です。

MySQL は、メモリとディスクにデータを保存する方法を決定するために、異なる整数型を選択します。ただし、整数計算では、32 ビット環境であっても、通常は 64 ビットの bigint 整数が使用されます。 （集計関数を除く）

MySQL では整数型の幅を指定することもできます。 int(11) などですが、値の有効範囲を制限するものではなく、一部の MySQL インタラクティブ ツール (SQLyog、navicat など) で表示に使用される文字数のみを指定します。保存と計算の目的では、int(1)とint(11)は同一です。

実数型

実数は小数部分を持つ数値です。 MySQL では、実数型を格納するために、decimal、float、double を使用できます。

float 型と double 型は、標準の浮動小数点演算を使用した近似計算をサポートします。
10 進数型は、正確な 10 進数を格納するために使用されます。MySQL 5.0 以降のバージョンでは、MySQL サーバー自体が高精度の 10 進数計算を実装しています。

浮動小数点型は通常、同じ範囲の値を格納するのに小数点型よりも少ないスペースを使用します。 float は保存に 4 バイトを使用し、double は保存に 8 バイトを使用します。double は float よりも精度が高く、範囲も広くなります。浮動小数点計算では、MySQL は内部の浮動小数点計算タイプとして double を使用します。

小数点以下の正確な計算を考慮する場合は、decimal を使用します (財務データなど)。ただし、データ量が多い場合は、decimal ではなく bigint を使用し、小数点以下の桁数を同じ倍数で乗じて通貨単位を格納することを検討できます。これにより、小数点計算の高コストを回避できます。

Float と double は、CPU でサポートされているネイティブの浮動小数点計算を使用するため、高速です。
Decimal は、MySQL サーバー自体によって実装される高精度の計算です。

文字列型

VARCHAR と CHAR は 2 つの主要な文字列型です。

varchar

varchar 型は可変長文字列を格納するために使用され、最も一般的な文字列データ型です。必要なスペースのみを使用するため、固定長型よりもスペース効率が高くなります。

VARCHAR では、文字列の長さを記録するために 1 バイトまたは 2 バイトの追加バイトが必要です。列の最大長が 255 バイト以下の場合は 1 バイトのみ使用され、それ以外の場合は 2 バイトが使用されます。

VARCHAR はストレージスペースを節約するため、パフォーマンスにも役立ちます。ただし、行の長さは可変であるため、更新によって行が以前よりも長くなる可能性があり、追加の作業が必要になります。行が占めるスペースが大きくなり、ページ上に格納する余地がなくなるためです。この場合、MyISAM は行を異なるフラグメントに分割して保存し、InnoDB は行がページに収まるようにページを分割する必要があります。他のストレージ エンジンでは、データをその場で更新しない場合があります。

varchar を使用するのに最も適したシナリオは、文字列列の最大長が平均長よりもはるかに大きい場合、列がほとんど更新されない場合 (断片化が問題にならない場合)、および utf-8 などの複雑な文字セットを使用する場合で、各文字が異なるバイト数を使用して格納されるときです。

varchar(5) と varchar(200) を使用して 'hello' を格納する場合のスペース コストは同じですが、長い列を使用するとより多くのメモリが消費されます。MySQL は通常、内部値を格納するために固定サイズのメモリ ブロックを割り当てます。これは、並べ替えや操作にメモリ内の一時テーブルを使用する場合に特に問題となり、並べ替えにディスクの一時テーブルを使用する場合も同様に問題となります。したがって、最善の戦略は、本当に必要なスペースだけを割り当てることです。

文字

char 型は固定長です。 MySQL は常に定義された文字列の長さに十分なスペースを割り当てます。 char 値を保存する場合、MySQL は末尾のスペースをすべて削除します。比較を容易にするために、必要に応じて Char 値にスペースが埋め込まれます。

char は非常に短い文字列を保存する場合、またはすべての値が同じ長さに近い場合に適しています。たとえば、パスワードの MD5 値。頻繁に変更されるデータの場合、固定長の char は断片化される可能性が低いため、varchar よりも char の方が適しています。非常に短い列の場合、varchar ではレコード長に余分なバイトが必要になるため、char の方が varchar よりもストレージ スペースの効率が高くなります。

上記は、MySQL データ型の最適化の原則の詳細です。MySQL データ型の最適化の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。