mysqlにおけるデータ型の最適化方法の詳細な説明

この記事では、主に MySQL の データ型 に関連する最適化方法を紹介します。これには、複数列インデックスの使用やその他の関連する最適化方法も含まれます。必要な方は、効率化に役立つ

型の選択を参照してください

1。データをできるだけ小さくします

最も基本的な最適化の 1 つは、データ (およびインデックス) がディスク (およびメモリ) 上で占有するスペースをできるだけ少なくすることです。これにより、ディスクの読み取りが高速になり、通常はメイン メモリの使用量が少なくなるため、大幅な改善が得られます。より小さい列にインデックスを付けると、インデックスが使用するリソースも少なくなります。

次のテクニックを使用すると、テーブルのパフォーマンスを向上させ、ストレージ領域を最小限に抑えることができます:

· 可能な限り最も効率的な (最小の) タイプを使用します。 MySQL には、ディスク領域とメモリを節約するための多くの特殊化があります。

·テーブルを小さくできる場合は、より小さい整数型を使用してください。たとえば、MEDIUMINT は INT よりも優れていることがよくあります。

· 可能であれば、列を NOT NULL として宣言します。これによりすべてが高速化され、列ごとに 1 ビットが節約されます。アプリケーションで本当に NULL が必要な場合は、何も疑問を持たずに NULL を使用する必要があります。デフォルトですべての列に NULL を含めることは避けてください。

2. 可変長列の代わりに固定長列を使用します

このガイドラインは、頻繁に変更されるため断片化が起こりやすいテーブルにとって特に重要です。たとえば、VARCHAR 列の代わりに CHAR 列を選択する必要があります。トレードオフとして、固定長列を使用するとテーブルが占有するスペースが増加しますが、スペースのコストに余裕がある場合は、固定長行を使用した方が可変長行を使用するよりもはるかに高速になります。

3. 列を NOT NULL として定義します

これにより、処理が高速になり、必要なスペースが少なくなります。また、特殊な場合の NULL をチェックする必要がないため、クエリが簡素化される場合もあります。

4. ENUM 列の使用を検討する

限られた数の特定の値のみを含む列がある場合は、それを ENUM 列に変換することを検討する必要があります。 ENUM 列の値は内部的に数値として表現されるため、より高速に処理できます。

BLOB 型と TEXT 型について
1. BLOB 型と TEXT 型を使用する利点

BLOB を使用して、パッケージ化されたデータまたはパッケージ化されていないデータをアプリケーションに格納すると、本来は複数の取得操作が必要だったデータを 1 回の取得で完了できるようになります。手術。また、標準のテーブル構造では表現しにくいデータや、時間の経過とともに変化するデータを保存する場合にも役立ちます。

2. BLOB 型と TEXT 型を使用する場合に考えられる欠点

一方、特に多数の DELETE または UPDATE 操作を実行する場合、BLOB 値には固有の問題もあります。 BLOB を削除すると、テーブルに大きなギャップが残ります。後でこのギャップを 1 つのレコード、場合によってはサイズの異なる複数のレコードで埋める必要があります。

必要な場合を除き、大きな BLOB 値または TEXT 値を取得しないでください。たとえば、WHERE 句によって結果が必要な行に正確に制限されることが確実でない限り、SELECT * クエリは良いアイデアではありません。これを行うと、非常に大きな BLOB 値が宛先なしでネットワーク上にドラッグされる可能性があります。これは、別の列に格納されている BLOB 識別情報が役立つもう 1 つの状況です。この列を 検索して目的の行を特定し、修飾された行から BLOB 値を取得できます。

3. 必要なガイドライン

断片化が起こりやすいテーブルには OPTIMIZE TABLE を使用してください
大幅に変更されたテーブル、特に可変長列を持つテーブルは断片化されやすくなります。断片化は、テーブルが格納されているディスク ブロックに未使用の領域を作成するため、悪影響を及ぼします。時間の経過とともに、有効な行を取得するためにより多くのブロックを読み取る必要があり、パフォーマンスが低下します。この問題は可変長行を含むどのテーブルにも存在しますが、BLOB 列の場合はサイズが大きく異なるため、より問題が大きくなります。 OPTIMIZE TABLE を頻繁に使用すると、パフォーマンスの低下を防ぐことができます。

複数列インデックスの使用

複数列インデックス列は便利な場合があります。 1 つの手法は、他の列に基づいてハッシュ値を構築し、それを別の列に格納し、そのハッシュ値を検索することで行を見つけることができるというものです。これは完全一致クエリの場合にのみ機能します。 (ハッシュ値は、「d8b47c1600b29c175819c2dd074eca9a=」などの演算子を使用した範囲検索には役に立ちません)。 MySQL バージョン 3.23 以降では、MD5() 関数を使用してハッシュ値を生成できます。ハッシュ インデックスは、BLOB 列で特に役立ちます。注意すべき点の 1 つは、MySQL 3.23.2 より前のバージョンでは BLOB タイプのインデックスを作成できなかったことです。バージョン 3.23.2 以降でも、ハッシュ値を識別値として使用して BLOB 値を検索する方が、BLOB 列自体を検索するよりも高速です。

BLOB 値を別のテーブルに分離する

場合によっては、テーブルを BLOB 列の後に移動できる場合には、BLOB 列をテーブルから別のサブテーブルに移動することが合理的になる場合があります。は移動されます 固定長行形式に変換します。これにより、メインテーブルの断片化が軽減され、固定長行のパフォーマンス上の利点が活用されます。

テーブルの列を調べるには ANALYZE プロシージャを使用します
MySQL 3.23 以降を使用している場合は、PROCEDURE ANALYSE() を実行して、テーブル内の列について提供される情報を確認する必要があります

ANALYSE([max elements,[max memory]])

クエリの結果を検証しますそして結果の分析を返します。

最大要素 (デフォルトは 256) は、分析が認識する列ごとの個別の値の最大数です。これは、最適な列の型が ENUM 型であるべきかどうかを確認するために ANALYZE によって使用されます。

最大メモリ (デフォルトは 8192) は、分析がすべての個別の値を検索しようとするときに各列に割り当てる必要があるメモリの最大量です。

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])

例:

mysql>SELECT * FROM student PROCEDURE ANALYSE();

mysql>SELECT * FROM student PROCEDURE ANALYSE(16,256);

対応する出力には、テーブル内の各列に最適な列タイプに関する推奨事項を示す列が含まれています。 2 番目の例は、PROCEDURE ANALYSE() に対して、16 を超える値を持つ、または 256 バイトを超える (これらの値は必要に応じて変更できます) 必要とする ENUM 型を提案しないように要求します。このような制限がないと、出力が非常に長くなる可能性があり、ENUM の定義も読みにくくなります。

PROCEDURE ANALYSE() の出力に基づいて、より効率的な型を利用するためにテーブルに変更を加えることができることがわかります。値の型を変更する場合は、ALTER TABLE ステートメントを使用します。

以上がmysqlにおけるデータ型の最適化方法の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。