MySQLの大規模テーブル最適化ソリューションについての簡単な説明

背景

Alibaba Cloud RDS for MySQL（MySQL バージョン 5.7）データベースのビジネス テーブルの新しいデータの量は、毎月 1,000 万を超えています。データ量が増え続けるにつれて、当社のビジネスでは大きなテーブルに対するクエリが遅くなります。ビジネスのピーク時には、メインのビジネス テーブルに対する遅いクエリに数十秒かかり、ビジネスに深刻な影響を及ぼします。

プログラム概要

1. データベース設計とインデックスの最適化

MySQLデータベース自体は非常に柔軟性が高いため、開発者のテーブル設計とインデックスの最適化能力に大きく依存し、パフォーマンスが不十分になります。ここでは、最適化の提案をいくつか紹介します。

• 時間型をタイムスタンプ形式に変換し、int型で保存し、インデックスを作成してクエリの効率を高めます。
• フィールドを null 以外として定義することをお勧めします。null 値はクエリが難しく、余分なインデックス スペースを占有します。
• ENUMの代わりにTINYINT型を使用する
• 正確な浮動小数点数を保存するには、FLOAT や DOUBLE の代わりに DECIMAL を使用する必要があります。
• フィールドの長さはビジネスニーズに応じて決定する必要があります。あまり長く設定しないでください。
• TEXT タイプは使用しないようにしてください。どうしても使用する必要がある場合は、一般的でない大きなフィールドを他のテーブルに分割することをお勧めします。
• MySQL にはインデックス フィールドの長さの制限があります。InnoDB エンジンの各インデックス列の長さのデフォルトの制限は 767 バイトです。すべてのインデックス列の合計長は 3072 バイトを超えることはできません (MySQL 8.0 の単一インデックスは 1024 文字を作成できます)。
• 大きなテーブルにDDLが必要な場合はDBAに連絡してください
  

左端のインデックスマッチングルール

名前が示すように、左端が最初です。複合インデックスを作成するときは、ビジネス ニーズに応じて、where 句で最も頻繁に使用される列を左端に配置する必要があります。複合インデックスにとって非常に重要な問題は、列の順序をどのように配置するかということです。たとえば、c1 と c2 という 2 つのフィールドが where の後で使用される場合、インデックスの順序は (c1, c2) とすべきでしょうか、それとも (c2, c1) とすべきでしょうか。正しいアプローチは、重複値が少ない列を先頭に配置することです。たとえば、列内の値の 95% が重複していない場合、この列は通常先頭に配置できます。

• 複合インデックス index(a,b,c)
• a=3の場合、aのみが使用される。
• a=3、b=5の場合はa、bを使用します。
• ここで、a=3、b=5、c=4はa、b、cを使用します。
• b=3またはc=4の場合はインデックスを使用しない
• a=3、c=4の場合、aのみが使用される。
• ここでa=3、b>10、c=7はa、bを使用する。
• ここで、a=3、bは'xx%'、c=7はa、bを使用します。
• 実際、これは複数のインデックスを作成するのと同じです: key(a)、key(a,b)、key(a,b,c)

2. データベースをPloarDBの読み書き分離に切り替える

PolarDB は Alibaba Cloud が独自に開発した次世代リレーショナル クラウド データベースで、MySQL と 100% 互換性があり、最大 100 TB のストレージ容量を備えています。単一のデータベースを最大 16 ノードまで拡張できるため、企業におけるさまざまなデータベース アプリケーション シナリオに適しています。 PolarDB は、ストレージとコンピューティングを分離するアーキテクチャを採用しています。すべてのコンピューティング ノードはデータのコピーを共有し、分単位の構成のアップグレードとアップグレード、第 2 レベルの障害回復、グローバルなデータ一貫性、無料のデータ バックアップおよび災害復旧サービスを提供します。

クラスタアーキテクチャ、コンピューティングとストレージの分離
PolarDB は、マルチノード クラスター アーキテクチャを使用します。クラスターには、Writer ノード (マスター ノード) と複数の Reader ノード (読み取り専用ノード) があります。各ノードは、分散ファイル システム (PolarFileSystem) を介して、基盤となるストレージ (PolarStore) を共有します。

読み取りと書き込みの分離 アプリケーションがクラスター アドレスを使用する場合、PolarDB は内部プロキシ レイヤー (プロキシ) を介して外部サービスを提供します。アプリケーションの要求は、データベース ノードにアクセスする前に、まずプロキシを通過します。プロキシ層は、セキュリティ認証と保護を実行できるだけでなく、SQL を解析し、書き込み操作 (トランザクション、UPDATE、INSERT、DELETE、DDL など) をマスター ノードに送信し、読み取り操作 (SELECT など) を複数の読み取り専用ノードに均等に分散して、自動的な読み取りと書き込みの分離を実現します。アプリケーションの場合、単一ポイント データベースを使用するのと同じくらい簡単です。

オフラインハイブリッドシナリオでは、相互の影響を避けるために、異なるサービスが異なる接続アドレスと異なるデータノードを使用します。

Sysbench パフォーマンス ストレス テスト レポート:

PloarDB 4コア 16G 2台

PloarDB 8コア 32G 2台

3. 履歴データをシャードテーブルからMySQL 8.0 X-Engineストレージエンジンに移行する

ビジネス テーブルには 3 か月分のデータが保持され (これは企業のニーズに基づいています)、履歴データは履歴データベース X-Engine ストレージ エンジン テーブルに月次テーブルとして分割されます。X-Engine ストレージ エンジン テーブルを選択する理由は何ですか? その利点は何ですか?

コスト削減: X-EngineのストレージコストはInnoDBの約半分です。

X-Engine 階層型ストレージは QPS を向上させます。階層型ストレージ構造を使用してホット データとコールド データを異なるレイヤーに保存し、コールド データが配置されているレイヤーをデフォルトで圧縮します。

X-Engine は、Alibaba Cloud のデータベース製品部門によって開発されたオンライン トランザクション処理 (OLTP) データベース ストレージ エンジンです。
X-Engine ストレージ エンジンは、MySQL とシームレスに互換性があるだけでなく (MySQL Pluginable Storage Engine 機能のおかげです)、階層型ストレージ アーキテクチャも使用します。大規模で膨大なデータを保存し、高同時トランザクション処理機能を提供し、ストレージ コストを削減することが目標であるため、ほとんどの大規模データ シナリオでは、データへのアクセスは不均一で、頻繁にアクセスされるホット データは実際にはわずかな割合を占めます。X-Engine は、データ アクセスの頻度に基づいてデータを複数のレベルに分割します。各レベルのデータのアクセス特性に基づいて、対応するストレージ構造を設計し、適切なストレージ デバイスに書き込みます。

• X-Engine は、階層型ストレージのアーキテクチャ基盤として LSM-Tree を使用し、次の点を考慮して再設計されました。
• ホットデータ層とデータ更新はメモリストレージを使用し、メモリデータベース技術（ロックフリーインデックス構造/追加のみ）によりトランザクション処理のパフォーマンスが向上します。
• パイプライン トランザクション処理メカニズムは、トランザクション処理の複数のステージを並行して実行し、スループットを大幅に向上させます。
• アクセス頻度の低いデータは徐々に削除されるか、永続ストレージ層に統合され、マルチレベルストレージデバイス (NVM/SSD/HDD) と組み合わせて保存されます。
• 圧縮プロセスには多くの最適化が施され、パフォーマンスに大きな影響を与えています。
• データ ストレージの粒度を分割し、集中したデータ更新ホットスポットの特性を活用し、マージ プロセス中に可能な限りデータを再利用します。
• LSM の形状を細かく制御することで、I/O とコンピューティングのコストが削減され、マージ プロセス中のスペースの増加が効果的に緩和されます。
• 同時に、よりきめ細かいアクセス制御とキャッシュ メカニズムを使用して、読み取りパフォーマンスを最適化します。
  

4. Alibaba Cloud PloarDB MySQL 8.0バージョンの並列クエリ

テーブルを分割した後もデータ量は依然として大きく、遅いクエリの問題は完全には解決されず、ビジネステーブルのサイズが縮小されるだけです。遅いクエリのこの部分では、PolarDBの並列クエリ最適化を使用する必要があります。

PolarDB MySQL 8.0 では、並列クエリ フレームワークがリリースされました。クエリするデータの量が一定のしきい値に達すると、並列クエリ フレームワークが自動的に開始され、クエリ時間が飛躍的に短縮されます。データはストレージ レイヤーで異なるスレッドに分割され、複数のスレッドが並列で計算します。結果パイプラインはメイン スレッドに集約され、最終的にメイン スレッドが簡単なマージを行ってユーザーに返すため、クエリの効率が向上します。
Parallel Query は、マルチコア CPU の並列処理機能を使用します。8 コア 32 GB 構成を例にとると、概略図は次のようになります。

並列クエリは、大規模なテーブル クエリ、複数のテーブルを結合するクエリ、大規模な計算ワ​​ークロードを伴うクエリなど、ほとんどの SELECT ステートメントに適用できます。非常に短いクエリの場合、効果はそれほど劇的ではありません。

並列クエリの使用: ヒント構文を使用して、単一のステートメントを制御できます。たとえば、システムで並列クエリがデフォルトでオフになっているが、高頻度で実行される低速の SQL クエリを高速化する必要がある場合、ヒントを使用して特定の SQL を高速化できます。

SELECT /+PARALLEL(x)/ … FROM …; – x > 0

SELECT /*+ SET_VAR(max_parallel_degree=n) */ * FROM … // n > 0

クエリテスト: データベース構成 16 コア 32G 単一テーブルデータ量が 3000 万を超える

並列クエリ前の時間は 4326 ミリ秒でしたが、並列クエリを追加した後は 525 ミリ秒になり、パフォーマンスが 8.24 倍向上しました。

5. Hologreのインタラクティブ分析

並列クエリ最適化を使用することで、大規模テーブルに対する低速クエリの効率は向上しましたが、リアルタイム レポートやリアルタイムの大画面などの特定の要件はまだ達成できず、処理にはビッグ データに頼るしかありません。
ここで私はAlibaba Cloudのインタラクティブ分析Hologre（
(https://help.aliyun.com/product/113622.html) より

6. 追記

数千万のデータを持つ大規模なテーブルの最適化は、ビジネス シナリオに基づいており、コストを犠牲にして最適化されています。データベースをすぐに水平に分割して拡張することではありません。これは、運用と保守、ビジネスに大きな課題をもたらし、多くの場合、効果は良くない可能性があります。データベース設計、インデックスの最適化、テーブル パーティション分割戦略が整っているかどうかに関係なく、ビジネス ニーズに応じて適切なテクノロジを選択して実装する必要があります。

これで、MySQL の大規模テーブル最適化ソリューションに関するこの記事は終了です。MySQL の大規模テーブル最適化に関するより関連性の高いコンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。