MySQL の大規模テーブル最適化ソリューションの紹介-mysql チュートリアル-php.cn

無料学習の推奨事項: mysql データベース(ビデオ)

背景

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

プログラムの概要

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

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のみを使用します
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
を使用します。

2. データベースを次のように切り替えます。 PloarDB 読み取りと書き込みの分離

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

PolarDB はマルチノードクラスターアーキテクチャを採用しており、Writer ノード (マスターノード) と複数の Reader ノード (読み取り専用ノード) が存在します。各ノード分散ファイルシステム (PolarFileSystem) を介して基盤となるストレージ (PolarStore) を共有
アプリケーションがクラスターアドレスを使用する場合、PolarDB は内部プロキシレイヤー (プロキシ)、およびアプリケーションすべてのリクエストは最初にプロキシを通過し、次にデータベースノードにアクセスします。プロキシ層は、セキュリティ認証と保護を実行するだけでなく、SQL を解析し、書き込み操作 (トランザクション、UPDATE、INSERT、DELETE、DDL など) をマスターノードに送信し、読み取り操作 (SELECT など) を均等に分散することもできます。 ) を複数のノードに送信し、読み取りノードは読み取りと書き込みの自動分離を実現します。アプリケーションの場合、単一ポイントのデータベースを使用するのと同じくらい簡単です。

オフライン混合シナリオ: 異なるサービスは異なる接続アドレスを使用し、相互影響を避けるために異なるデータノードを使用します

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

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

#PloarDB 4 コア 16G 2 ユニット

MySQL の大規模テーブル最適化ソリューションの紹介
#PloarDB 8 コア 32G 2 ユニット

MySQL の大規模テーブル最適化ソリューションの紹介
##3. サブテーブルの履歴データを MySQL8.0 X-Engine ストレージエンジンに移行します

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

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

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

## に書き込みます。

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

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

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

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

PolarDB MySQL 8.0 は、並列クエリフレームワークでは、クエリデータの量が特定のしきい値に達すると、並列クエリフレームワークが自動的に開始され、クエリ時間が大幅に短縮されます。

データをストレージ層で異なるスレッドに分割すると、複数のスレッドが並列計算を実行します。パイプラインの結果はメインスレッドに要約され、最後にメインスレッドは単純なマージを実行してユーザーに返し、クエリの効率を向上させます。
Parallel Query はマルチコア CPU の並列処理能力を利用しており、8 コア 32 GB 構成を例にとると、概略図は次のようになります。

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

並列クエリは、大規模なテーブルクエリ、複数テーブルの結合クエリ、計算負荷の大きいクエリなど、ほとんどの 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) MySQL の大規模テーブル最適化ソリューションの紹介

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

# #6追記

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