MySQL のデータ型とスキーマの最適化の詳細な説明

現在、MySQL の最適化について学習しています。この記事では、データ型とスキーマの最適化について紹介します。

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

MySQL は多くのデータ型をサポートしており、正しいデータ型を選択する方法がパフォーマンスにとって重要です。次の原則は、データ型を決定するのに役立ちます。

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

可能な限り、データを正しく保存できる最小のデータ型を使用してください。これにより、ディスク、メモリ、キャッシュの使用量が少なくなり、処理にかかる時間も短縮されます。

• シンプルに

2 つのデータ型が同じフィールドを格納できる場合、より単純なデータ型を選択するのが最適なオプションになることがよくあります。たとえば、整数と文字列です。整数の操作コストは文字よりも低いため、2 つを選択する場合は、通常は整数を選択するとパフォーマンスが向上します。

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

列が NULL になる可能性がある場合、MySQL はインデックス作成と値の比較に関してより多くの作業を行う必要があります。パフォーマンスへの影響は大きくありませんが、NULL になるように設計することは避けてください。

上記の原則に加えて、データ型を選択する際に従う手順は、まずデータ、文字列、時間などの適切な大きな型を決定し、次に特定の型を選択することです。ここでは、大きなカテゴリに含まれるいくつかの特定の型について説明します。まず、数値について説明します。数値には、整数と実数の 2 つの型があります。

1.1 整数型

整数型とそれが占めるスペースは次のとおりです。

整数型のストレージ範囲は、空間のサイズに関連します: -2^(N-1) ～ 2^(N-1)-1。ここで、N は空間内のビット数です。

整数型にはオプションの属性 UNSIGNED があります。宣言すると、負の数は許可されないことを意味します。ストレージ範囲は 0 から 2^(N)-1 になり、これは 2 倍になります。

MySQLでは、INT(1)などの整数型の幅も指定できますが、これはあまり意味がなく、値の有効な範囲を制限するものではありません。-2^31から2^31-1までの値は引き続き保存できます。影響を受けるのは、MySQLと対話するインタラクティブツールによって表示される文字数です。

1.2 実数型

実数型の比較は次のとおりです。

上記からわかるように、FLOAT と DOUBLE はどちらも固定の空間サイズを持ちますが、同時に、標準の浮動小数点演算を使用するため、概算でしか計算できません。 DECIMAL は正確な計算を実現できますが、より多くのスペースを占有し、より多くの計算オーバーヘッドを消費します。

DECIMAL が占めるスペースは、指定された精度に関連しています。たとえば、DECIMAL(M,D) の場合:

• Mは数値全体の最大長で、値の範囲は[1, 65]、デフォルト値は10です。
• D は小数点以下の長さで、値の範囲は [0, 30]、D <= M です。デフォルト値は 0 です。

DECIMAL データを保存する場合、MySQL はそれをバイナリ文字列として保存し、4 バイトごとに 9 桁を保存します。数値が 9 桁未満の場合、数値が占めるスペースは次のようになります。

小数点の前後の数字は別々に保存され、小数点も 1 バイトを占有します。以下に 2 つの計算例を示します。

• DECIMAL(18, 9): 整数部分の長さは9で、4バイトを占めます。小数部分の長さは 9 で、4 バイトを占めます。小数点用に1バイトを追加し、合計9バイトが占有されます。
• DECIMAL(20, 9): 整数部分の長さは14バイトで、7(4+3)バイトを占めます。小数部分の長さは 9 で、4 バイトを占めます。小数点用に1バイトを追加し、合計12バイトを占有します。

DECIMAL はまだ多くのスペースを占有することがわかります。そのため、DECIMAL は小数の正確な計算が必要な場合にのみ必要になります。さらに、DECIMAL の代わりに BIGINT を使用することもできます。たとえば、小数点以下 5 桁の計算を確実に行う必要がある場合は、値を 10 の 5 乗で乗算し、BIGINT として保存します。これにより、不正確な浮動小数点ストレージ計算の問題や、正確な DECIMAL 計算の高コストを回避できます。

1.3 文字列型

最もよく使用される文字列型は VARCHAR と CHAR です。可変長文字列である VARCHAR は、文字列の長さを記録するために 1 バイトまたは 2 バイトを追加します。最大長が 255 を超えない場合、長さを記録するのに必要なバイト数は 1 バイトのみです。255 を超える場合は、2 バイトが必要です。 VARCHAR は次の場合に適しています:

• 最大長は平均長よりもはるかに長くなります。
• 断片化を回避するために列の更新頻度が低くなります。
• UTF-8 などの複雑な文字セットでは、各文字を異なるバイトを使用して保存できます。

CHAR は固定長の文字列です。定義された文字列の長さに応じて十分なスペースが割り当てられます。適用可能なシナリオ:

• 短い長さ。
• MD5 など、同様の長さ。
• 頻繁に更新されます。

VARCHAR と CHAR に加えて、BLOB および TEXT 型を使用して大きな文字列を保存できます。 BLOB と TEXT の違いは、BLOB はバイナリ形式で保存されるのに対し、TEXT は文字形式で保存されることです。これは、BLOB 型データには文字セットの概念がなく、文字で並べ替えることができないのに対し、TEXT 型データには文字セットの概念があり、文字で並べ替えることができることも意味します。両者の使用シーンは保存形式によっても決まります。画像などのバイナリデータを保存する場合は BLOB 型、記事などのテキストデータを保存する場合は TEXT 型を使用します。

1.4 日付と時刻の型

MySQL に保存できる最小の時間粒度は秒です。一般的な日付型には、DATETIME と TIMESTAMP があります。

TIMESTAMP によって表示される値はタイムゾーンによって異なります。つまり、異なるタイムゾーンでクエリされた値は異なります。上記の違いに加えて、TIMESTAMP には特別な特性があります。挿入および更新時に、最初の TIMESTAMP 列の値が指定されていない場合、この列の値は現在の時刻に設定されます。

開発プロセス中は、TIMESTAMP を使用するようにしてください。主な理由は、TIMESTAMP は DATETIME の半分のスペースしか占有せず、よりスペース効率が高いためです。

日付と時刻を秒単位で正確に保存したい場合はどうすればよいでしょうか? MySQL では提供されていないため、BIGINT を使用してマイクロ秒レベルのタイムスタンプを保存するか、DOUBLE を使用して秒以下の小数部分を保存することができます。

1.5 識別子の選択

整数は一般的に識別子として最適です。主な理由は、整数は単純で、計算が速く、AUTO_INCREMENT を使用できるからです。

2. パラダイムと反パラダイム

簡単に言えば、パラダイムとは、データ テーブルの構造が準拠する設計標準のレベルです。第 1 正規形では、属性は分離できません。現在の RDBMS システムで構築されたすべてのテーブルは、第 1 正規形に準拠しています。 2 番目のパラダイムは、非プライマリ属性コード (プライマリキーとして理解できる) への部分的な依存を排除​​します。 3 番目のパラダイムでは、非プライマリ属性ペアに対する推移的な依存関係が排除されます。

厳密に正規化されたデータベースでは、各事実データは 1 回だけ表示され、データの冗長性はありません。これにより、次の利点がもたらされます。

• 更新操作が高速化されます。
• 変更するデータが少なくなります。
• テーブルが小さくなり、メモリへの収まりが良くなり、操作の実行が高速化されます。
• DISTINCT または GROUP BY の必要性が低くなります。
  

ただし、データはさまざまなテーブルに分散されているため、クエリを実行するときにテーブルを関連付ける必要があります。反正規化の利点は、関連付けを実行する必要がなく、データが冗長的に格納されることです。

実際のアプリケーションでは、完全な正規化や完全な非正規化は行われません。正規化と非正規化を混在させる必要がある場合がよくあります。部分的に正規化されたスキーマを使用するのが、多くの場合、最善の選択です。データベース設計に関しては、インターネットでこの一文を見ましたが、実感できます。

データベース設計は、次の 3 つの領域に分割する必要があります。

最初のレベル: データベース設計を始めたばかりで、パラダイムの重要性がまだ完全には理解されていません。このときに現れる反パラダイム設計は、たいてい問題を引き起こします。

第 2 レベル: 問題に遭遇して解決するにつれて、パラダイムの真の利点を徐々に理解し、冗長性が低く効率の高いデータベースを迅速に設計できるようになります。

3 番目のレベル: N 年間のトレーニングの後、パラダイムの限界を必ず発見するでしょう。このとき、パラダイムを破り、より合理的な反パラダイム部分を設計することができます。

パラダイムは武術における動作のようなものです。初心者が動作に従わなければ、惨めに死ぬだけです。結局、その技は達人たちがまとめ、まとめた真髄なのです。武術のスキルが向上し、技に習熟するにつれて、必然的に技の限界に気づき、それを忘れるか、独自の技を作り出すことになります。

今後数年間、一生懸命努力して努力を続ける限り、必ず第 2 レベルに到達し、そのパラダイムが古典的であると常に感じるでしょう。このとき、パラダイムに頼りすぎず、パラダイムの限界を素早く突破できる人が、当然ながら達人です。

3. キャッシュテーブルとサマリーテーブル

上記の反正規化に加えて、テーブルに冗長データを格納するだけでなく、検索のニーズを満たすために完全に独立したサマリー テーブルまたはキャッシュ テーブルを作成することもできます。

キャッシュ テーブルとは、スキーマ内の他のテーブルから取得できるデータ、つまり論理的に冗長なデータを格納するテーブルを指します。サマリーテーブルとは、GROUP BY などのステートメントを使用してデータを集計して計算された冗長性のないデータを格納するテーブルを指します。

キャッシュ テーブルは、検索および取得クエリ ステートメントを最適化するために使用できます。ここで使用できる手法は、キャッシュ テーブルに異なるストレージ エンジンを使用することです。たとえば、メイン テーブルでは InnoDB を使用し、キャッシュ テーブルでは MyISAM を使用して、より小さなインデックス スペースを取得できます。キャッシュ テーブルを Lucene などの専用の検索システムに配置することもできます。

サマリー テーブルは、リアルタイムの統計計算にかかる高コストを回避するために設計されています。コストは 2 つの側面から生じます。1 つはテーブル内のほとんどのデータをスキャンする必要があること、もう 1 つは UPDATE 操作に影響する特定のインデックスを作成する必要があることです。たとえば、過去 24 時間の WeChat 友達の数を照会する場合、1 時間ごとにテーブル全体をスキャンし、統計の後にサマリー テーブルにレコードを書き込むことができます。照会時には、サマリー テーブルの最新の 24 レコードのみを照会すればよく、照会するたびにテーブル全体をスキャンして統計を取得する必要はありません。

キャッシュ テーブルとサマリー テーブルを使用する場合は、ニーズに応じて、データをリアルタイムで維持するか、定期的に再構築するかを決定する必要があります。リアルタイムメンテナンスと比較して、定期的な再構築により、より多くのリソースを節約し、テーブルの断片化を減らすことができます。再構築中も、操作中にデータが利用可能であることを保証する必要がありますが、これは「シャドウ テーブル」を通じて実現する必要があります。実際のテーブルの背後にシャドウ テーブルを作成します。データを入力した後、アトミックな名前変更操作によってシャドウ テーブルと元のテーブルを切り替えます。

4. ALTER TABLE操作の高速化

MySQL が ALTER TABLE 操作を実行すると、多くの場合、新しいテーブルを作成し、古いテーブルからデータを取得して新しいテーブルに挿入し、古いテーブルを削除します。テーブルが大きい場合、これには長い時間がかかり、MySQL サービスの中断が発生します。サービスの中断を回避するために、通常は次の 2 つの手法が使用されます。

サービスを提供していないマシンで ALTER TABLE 操作を実行し、サービスを提供しているプラ​​イマリ データベースに切り替えます。
「シャドウコピー」は、元のテーブルとは関係のない新しいテーブルを作成します。データ移行が完了したら、名前変更操作で切り替えます。
ただし、すべての ALTER TABLE 操作でテーブルの再構築が発生するわけではありません。たとえば、フィールドのデフォルト値を変更する場合、MODIFY COLUMN を使用するとテーブルの再構築が発生しますが、ALTER COLUMN を使用するとテーブルの再構築は発生せず、操作速度が非常に速くなります。これは、ALTER COLUMN がデフォルト値を変更すると、テーブルを再構築せずに、既存のテーブルの .frm ファイル (フィールドのデフォルト値が格納されている) が直接変更されるためです。

参照する

高性能MySQL

MySQL DECIMAL データ型

以上がMySQLのデータ型とスキーマ最適化の詳細な説明です。MySQLのデータ型とスキーマ最適化の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。