MySQL の高パフォーマンス テーブルを設計する方法

優れた論理設計と物理設計は、高パフォーマンスの基礎です。スキーマは、システムが実行するクエリ ステートメントに従って設計する必要があり、多くの場合、さまざまな要素を考慮する必要があります。

1. 最適化されたデータ型を選択します

MySQL は多くのデータ型をサポートしています。高いパフォーマンスを達成するには、正しいデータ型を選択することが重要です。

通常は小さい方が良いです

データ型が小さいほど、占有するディスク、メモリ、CPU キャッシュが少なくなり、処理に必要な CPU サイクルが少なくなるため、一般的に高速になります。

とにかくシンプルにしてください

単純なデータ型の操作では、通常、必要な CPU サイクルが少なくなります。たとえば、文字セットと照合ルール (照合順序) により、文字比較は整数比較よりも複雑になるため、整数演算は文字演算よりもコストがかかりません。

NULL

は避けてください。

クエリに NULL 列が含まれる場合、NULL 列によりインデックス、インデックス統計、および値の比較がより複雑になるため、MySQL の最適化がより困難になります。 NULL にできるカラムはより多くのストレージ領域を使用するため、MySQL での特別な処理が必要になります。 NULL 許容カラムにインデックスが付けられると、各インデックス レコードに追加のバイトが必要になります。MyISAM では、これによって固定サイズのインデックス (整数カラムが 1 つだけあるインデックスなど) が可変サイズのインデックスになることもあります。

もちろん例外もあります。たとえば、InnoDB は NULL 値の格納に別のビットを使用するため、スパース データのスペース効率が優れています。

1.整数型

数値には、整数と実数の 2 種類があります。整数を格納する場合は、TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT の整数型を使用できます。それぞれ 8、16、24、32、および 64 ビットのストレージ領域を使用します。

整数型にはオプションの **UNSIGNED ** 属性があります。これは、負の値が許可されないことを意味し、正の数の上限がおよそ 2 倍になります。たとえば、TINYINT.UNSIGNED は 0 ～ 255 の範囲を格納できますが、TINYINT の格納範囲は -128 ～ 127 です。

符号付きタイプと符号なしタイプは同じ記憶領域を使用し、同じパフォーマンスを有するため、実際の状況に応じて適切なタイプを選択できます。

あなたの選択により、MySQL がメモリとディスクにデータを保存する方法が決まります。ただし、整数計算では、32 ビット環境であっても、通常、64 ビット BIGINT 整数が使用されます。 (計算に DECIMAL または DOUBLE を使用する一部の集計関数は例外です)。

MySQL では、INT(11) などの整数型の幅を指定できますが、これはほとんどのアプリケーションにとって無意味です。これは、値の正当な範囲を制限するものではなく、MySQL の一部の対話型ツール (MySQL コマンドなど) を指定するだけです。 line client end) を使用して文字数を表示します。保存と計算の目的では、INT(1) と INT(20) は同じです。

2.実数型

実数とは、小数部分を持つ数値です。ただし、DECIMAL は小数部分を格納するためだけでなく、BIGINT より大きい整数を格納するためにも使用できます。

FLOAT 型と DOUBLE 型は、標準の浮動小数点演算を使用した近似計算をサポートします。

DECIMAL タイプは、正確な小数を格納するために使用されます。

浮動小数点型と DECIMAL 型の両方で精度を指定できます。 DECIMAL 列の場合、小数点の前後に許可される最大桁数を指定できます。これは列のスペース消費に影響します。

浮動小数点カラムに必要な精度を指定する方法は数多くありますが、これにより MySQL は別のデータ型を選択したり、格納時に値を四捨五入したりすることになります。これらの精度の定義は標準ではないため、精度ではなくデータ型のみを指定することをお勧めします。

浮動小数点型は、同じ範囲に値を格納する場合、通常、DECIMAL よりも使用するスペースが少なくなります。 FLOAT は 4 バイトのストレージを使用します。 DOUBLE は 8 バイトを占有し、FLOAT よりも精度が高く、範囲も広くなります。整数型と同様に、選択できるのはストレージ タイプだけです。MySQL は内部浮動小数点計算の型として DOUBLE を使用します。

追加のスペースと計算オーバーヘッドが必要となるため、小数の正確な計算を実行する場合にのみ DECIMAL を使用するようにしてください。ただし、データが比較的大きい場合は、DECIMAL の代わりに BIGINT を使用することを検討し、保存する通貨単位に小数点以下の桁数に応じた対応する倍数を掛けます。

3.文字列型

VARCHAR

• 可変長文字列の格納に使用され、長さは 65535
までサポートされます。
• 文字列の長さを記録するには、1 バイトまたは 2 バイトの追加バイトを使用する必要があります。
最適な条件: 文字列の最大長が平均長よりはるかに長く、更新はまれです

文字

固定長、長さの範囲は 1 ～ 255
最適な用途: 非常に短い文字列、または同じ長さに近いすべての値、頻繁に変更される値の保存

寛大さは賢明ではありません

VARCHAR(5) と VARCHAR(200) を使用して「hello」を格納する場合のスペース オーバーヘッドは同じです。では、短い列を使用することには何か利点があるのでしょうか?

大きな利点があることがわかりました。 MySQL は通常、内部値を保持するために固定サイズのメモリ ブロックを割り当てるため、カラムが長いほど多くのメモリを消費します。これは、並べ替えや操作にメモリ内の一時テーブルを使用する場合に特に問題になります。ディスク一時テーブルを使用して並べ替える場合も同様に問題が発生します。

したがって、最善の戦略は、本当に必要なスペースのみを割り当てることです。

4.BLOB 型と TEXT 型

BLOB と TEXT はどちらも大量のデータを格納するように設計された文字列データ型で、それぞれバイナリ モードと文字モードで格納されます。

他の型とは異なり、MySQL は各 BLOB 値と TEXT 値を独立したオブジェクトとして扱います。ストレージ エンジンは通常、保存時に特別な処理を実行します。 BLOB 値と TEXT 値が大きすぎる場合、InnoDB はストレージに専用の「外部」ストレージ領域を使用します。このとき、各値は行に 1 ～ 4 バイトを格納する必要があります。ストレージ領域には実際の値が格納されます。

BLOB と TEXT の唯一の違いは、BLOB 型は照合規則や文字セットなしでバイナリ データを格納するのに対し、TEXT 型には文字セットと照合規則があることです。

5. 日付と時刻のタイプ

ほとんどの場合、このタイプに代わるものはないため、何が最善の選択であるかについては疑問の余地はありません。唯一の問題は、日付と時刻を保存するときに何をする必要があるかです。 MySQL は、DATE TIME と TIMESTAMP という 2 つの類似した日付タイプを提供します。

ただし、現在はタイムスタンプを保存する方法を推奨しているため、ここでは DATE TIME と TIMESTAMP についてはあまり説明しません。

6.その他のタイプ

6.1 識別子の選択

最小のデータ型は、値範囲のニーズを満たし、将来の拡張の余地を確保できるという前提で選択する必要があります。

• 整数型

整数は高速で AUTO_INCREMENT を使用できるため、通常、ID 列には最適な選択です。

• ENUM および SET タイプ

EMUM タイプと SET タイプは、一般に ID 列には適切な選択ではありませんが、固定された状態またはタイプのみを含む一部の静的な「定義テーブル」には適している場合があります。 ENUM 列と SET 列は、注文状況、製品タイプ、人の性別などの固定情報を格納するのに適しています。

• 文字列型

可能であれば、文字列型を ID 列として使用することは避けてください。文字列型はスペースを消費し、一般に数値型よりも遅いためです。

MDS()、SHAl()、または UUID() によって生成される文字列など、完全に「ランダム」な文字列にもさらに注意を払う必要があります。これらの関数によって生成された新しい値は、広い領域に任意に分散されるため、INSERT ステートメントや一部の SELECT ステートメントが非常に遅くなる可能性があります。 UUID 値が保存されている場合は、「-」記号を削除する必要があります。

6.2 特殊な型のデータ

一部のタイプのデータ ウェルは、組み込みタイプと直接一致しません。キロ秒精度の低いタイムスタンプは 1 つの例です。別の例は 1Pv4 アドレスです。IP アドレスの保存には VARCHAR(15) 列がよく使用されますが、実際には文字列ではなく 32 ビットの符号なし整数です。住所を小数点を使用して 4 つのセグメントに分けて表現しているのは、読みやすくするためです。したがって、IP アドレスは符号なし整数として保存する必要があります。 MySQL は、これら 2 つの表現方法の間で変換するための INET_ATON() 関数と INET_NTOA() 関数を提供します。

2. テーブル構造の設計

1. パラダイムとアンチパラダイム

通常、特定のデータを表す方法は、完全に正規化されたものから完全に非正規化されたものまで、またその 2 つの間の妥協点まで数多くあります。正規化されたデータベースでは、各ファクトは 1 回だけ出現します。対照的に、非正規化データベースでは、情報は冗長であり、複数の場所に保存される可能性があります。

パラダイムの長所と短所

パフォーマンスの向上を考慮する場合、特に書き込み集中型のシナリオでは、スキーマを正規化することが推奨されることがよくあります。

• 正規化された更新操作は、通常、非正規化された操作よりも高速です。
• データが適切に正規化されている場合は、重複データがほとんどまたはまったくないため、変更する必要のあるデータが少なくなります。
• 正規化されたテーブルは通常、サイズが小さくメモリへの適合性が高いため、操作がより高速に実行されます。
• 冗長データが少ないということは、リスト データを取得するために必要な DISTINCT または GROUP BY ステートメントが少なくなることを意味します。

アンチパラダイムの長所と短所

関連テーブルが必要ない場合、テーブルがインデックスを使用していない場合でも、ほとんどのクエリの最悪のケースはテーブル全体のスキャンになります。データがメモリよりも大きい場合、ランダムな I/0 が回避されるため、これは連想型よりもはるかに高速になる可能性があります。

個々のテーブルでは、より効率的なインデックス作成戦略を使用することもできます。

正規化と非正規化の混合

実際のアプリケーションでは、多くの場合、これらを混合する必要があり、部分的に正規化されたスキーマ、キャッシュ テーブル、およびその他の技術が使用される場合があります。

パフォーマンスの優先順位など、冗長なフィールドをテーブルに適切に追加しますが、複雑さが増します。テーブル結合クエリを回避できます。

シンプルで使い慣れたデータベース パラダイム

<br> 第一正規形 (1NF): フィールド値はアトミックであり、分割できません (すべてのリレーショナル データベース システムは第一正規形を満たします);<br> 例: 名前フィールド。姓と名が完全に一致します。姓と名が区別される場合は、2 つの独立したフィールドを設定する必要があります。

第 2 正規形 (2NF): テーブルには主キーが必要です。つまり、データの各行は一意に区別できます。
注: 最初に最初の正規形が満たされる必要があります;

第 3 正規形 (3NF): テーブルには、他の関連テーブルの非キー フィールドに関する情報を含めることはできません。つまり、データ テーブルに冗長フィールドを含めることはできません。 注: 最初に第 2 正規形が満たされる必要があります;

2. テーブルフィールドの精度は低くなります

I/O効率的
個別のフィールドは保守が簡単です
シングルテーブル 1G ボリューム 500W の評価
1 行は 200Byte を超えてはなりません
単一テーブル内の INT フィールドは 50 個以下です
単一テーブル内の CHAR(10) フィールドは 20 個までです
単一テーブル内のフィールド数は 20 以内に制御することをお勧めします
TEXT/BLOB を分割すると、TEXT 型の処理パフォーマンスは VARCHAR よりも大幅に低下するため、ハード ディスク一時テーブルの強制生成により多くのスペースが無駄になります。

以上がMySQL の高パフォーマンス テーブルを設計する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。