MySQL の大規模テーブル最適化ソリューションの紹介

無料学習の推奨事項: 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 バイトを超えることはできません (mysql8.0 の単一インデックスでは 1024 文字を作成できます)
• 大テーブルに DDL 要件がある場合は、 DBA

左端インデックス一致ルール

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

• 複合インデックスindex(a,b,c)
• ここで、a=3はaのみを使用します
#ここで、a=3とb=5はa,bを使用します
• where a=3 and b=5 and c=4 uses a, b,c
• where b=3 or where c=4 インデックスは使用されません
• where a=3 and c= 4 a=3 および b> 10 および c=7 のみ a
• は 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. サブテーブルの履歴データを MySQL8.0 X-Engine ストレージ エンジンに移行します

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

1. X-Engine のストレージ コストは InnoDB の約半分であり、コストを節約できます。
2. X-Engine 階層型ストレージは QPS を向上させ、階層型ストレージを採用します。構造を構築し、ホット データとコールド データを結合します。これらのデータはさまざまなレベルに保存され、コールド データが配置されているレベルはデフォルトで圧縮されます。

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

## に書き込みます。

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

4. Alibaba Cloud PloarDB MySQL8.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 ミリ秒かかりました。追加後は 525ms となり、パフォーマンスは 8.24 倍向上しました。

##大規模なテーブルでの遅いクエリには並列処理を使用していますが、クエリの最適化により効率は向上しましたが、リアルタイム レポートやリアルタイムの大画面に対する特定の要件の一部はまだ達成できず、処理にはビッグ データのみに依存する必要があります。 。

ここでは、Alibaba Cloud の対話型分析 Hologre をお勧めします (

https://help.aliyun.com/product/113622.html)

# #6追記

数千万の大きなテーブルの最適化はビジネスシナリオに基づいており、コストがかかります。最初からデータベースを水平分割して拡張することはできません。運用、保守、およびビジネスに対する問題が発生します。大きな課題です。多くの場合、結果は良くないかもしれません。データベース設計、インデックスの最適化、テーブルのパーティショニング戦略が整っているかどうかにかかわらず、それらを実装するための適切なテクノロジを、次の基準に基づいて選択する必要があります。ビジネスニーズ。

その他の関連する無料学習の推奨事項: mysql チュートリアル(ビデオ)

以上がMySQL の大規模テーブル最適化ソリューションの紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。