PHP が MySQL の数千万のテーブルを最適化する方法の詳細な説明

まず第一に、Mysql を使用して数千億のデータを保存することは、確かに非常に大きな課題です。単一の Mysql テーブルには確かに 10 億レベルのデータを保存できますが、現時点ではパフォーマンスが非常に悪いです。プロジェクトの多数の実験により、単一の Mysql テーブルの容量は約 500 万レベルであり、パフォーマンスはその最高の状態で。

大きなテーブルの最適化は、主にデータベース サブデータベースとテーブル サブテーブルによって解決されます。現在、一般的なソリューションは パーティション、サブデータベースとサブテーブル#の 3 つです。 ##、NoSql/NewSql。実際のプロジェクトでは、これら 3 つのソリューションが組み合わされており、現在、ほとんどのシステムのコア データは主に RDBMS ストレージであり、NoSql/NewSql ストレージがそれを補っています。

パーティション

まず、パーティション スキームを理解しましょう。

パーティション テーブルは、複数の関連する基礎となるテーブルによって実装されます。これらの基になるテーブルはハンドル オブジェクトによっても表されるため、各パーティションに直接アクセスすることもできます。ストレージ エンジンは、通常のテーブルを管理するのと同じ方法でパーティションの基になるテーブルを管理します (すべての基になるテーブルは同じストレージ エンジンを使用する必要があります)。パーティション テーブルのインデックスは、基になる各テーブルに同一のインデックスを追加するだけです。このソリューションは、シャーディングの詳細からユーザーを保護します。クエリ条件にシャーディング列がない場合でも、正常に動作します (ただし、現時点ではパフォーマンスは平均的です)。

ただし、その欠点は明らかです。接続数、ネットワーク スループットなど、多くのリソースが 1 台のマシンによって制限されます。実際のアプリケーションでは、どのように分割するかが重要な要素の 1 つです。

例から始めましょう: 顧客情報を例にとると、顧客データの量は 5,000 万以上です。プロジェクトの背景では、顧客の銀行カードのバインド関係、顧客のドキュメントのバインド関係、および顧客のデータを保存する必要があります。拘束力のあるビジネス情報。

このようなビジネス背景の下で、データベースをどのように設計するか。プロジェクトの最初のフェーズでは、各顧客にバインドされた冗長なビジネス情報を含む顧客ビジネス バインディング関係テーブルを確立しました。

基本的な構造はおおよそ次のとおりです。

クエリを実行するときは、銀行カードとビジネス番号インデックス、文書番号はインデックスとして使用されます。需要が増えると、このテーブルのインデックスは 10 を超えるようになります。また、お客様が一度ご契約を解除し、再度ご契約をされた場合には、データは2つ保存されますが、拘束状態が異なります。

5,000 万人の顧客、5 つのビジネス タイプ、顧客あたり平均 2 枚のカードがあると仮定すると、このテーブルのデータ量は驚くべき 5 億に達します。システムはまだ 100 万に達するまで動作しません。このような設計は絶対に不可能で、挿入でもクエリでもシステムがクラッシュします。

mysql データベースのデータは、ファイルの形式でディスクに保存されます。デフォルトでは、/mysql/data に配置されます (my.cnf の datadir から参照できます)。主に 1 つのテーブルです。 1 つはテーブル構造を格納するための frm、1 つはテーブル データを格納するための myd、もう 1 つはテーブル インデックスを格納するための myi です。これら 3 つのファイルは非常に大きく、特に .myd ファイルはほぼ 5G です。最初のパーティション最適化は以下で実行されます。Mysql でサポートされているパーティショニング方法は 4 つあります:

私たちのプロジェクトでは、レンジ パーティショニングとリスト パーティショニングには使用シナリオがありません。バインディング番号をカスタマイズして、範囲またはリスト パーティションを作成します。バインディング番号には実際のビジネス上の意味はなく、クエリを実行することはできません。したがって、HASH パーティションと KEY パーティションが残ります。HASH パーティションは、int 型列のパーティションのみをサポートします。もその 1 つです。

KEY パーティショニングは複数の列をサポートできますが、列の 1 つが int 型である必要もあります。ライブラリのテーブル構造を見ると、どの列も int 型ではないことがわかります。 ?バインディング時間列という列を追加し、この列を int 型に設定し、バインド時間に従ってパーティション分割し、毎日バインドされるユーザーを同じ領域に分割します。

この最適化後、挿入は大幅に高速化されましたが、クエリは依然として非常に遅いのです。なぜでしょうか?

クエリを作成するときは、時間ではなく、銀行カードまたは ID 番号に基づいてのみクエリを実行するため、これはすべてのクエリと同等であり、MySQL はすべてのパーティション テーブルをクエリします。

2 番目のソリューションの最適化を実行します。HASH パーティショニングと KEY パーティショニングでは、列の 1 つが int 型である必要があるため、パーティショニング用に int 型のリストを作成することは可能ですか?

分析の結果、キャッシュカードの数字列に秘密があることが判明しました。銀行カードは通常 16 ～ 19 桁の数字の列です。数字の 1 つを取り出してテーブル パーティションとして使用することは可能ですか? 分析により、この数字の列の 1 つが次のとおりであることがわかりました。確かに 0 から 9 までの乱数です。生成され、銀行カード番号のランダムな数字に基づいて KEY パーティショニングを実行します。クエリを実行するたびに、このランダムな数字を計算によって傍受し、カード番号を追加し、結合クエリを実行します。最後に、作成されるインデックスもパーティション列である必要があり、そうでない場合は、Mysql は引き続きすべてのパーティション テーブル内のデータをクエリします。

銀行カード番号を使用してバインディング関係を照会する問題は解決されました。証明書番号はどうですか?証明書番号を使用してバインディング関係を照会する方法。

前に述べたように、インデックス作成はパーティションの健全性に対して実行する必要があります。そうしないと、テーブル全体のスキャンが発生します。顧客の ID 番号バインディング関係を保存する新しいテーブルを作成しました。各顧客の ID 番号は一意です。新しい ID 番号バインディング関係テーブルでは、ID 番号が主キーとして使用されます。では、パーティションの健全性を計算するにはどうすればよいでしょうか?顧客の書類情報は、ID 番号、香港、マカオ、台湾のパス、自動車運転免許証など、比較的複雑です。乱れた書類番号からパーティション キーを見つける方法。

この問題を解決するために、文書番号結合関係テーブルを 2 つに分割し、1 つのテーブルは ID 番号タイプの保存専用に、もう 1 つのテーブルは他のタイプの文書を保存します。 IDカードタイプの文書結束関係テーブルでは、ID番号の月番号をパーティションキーとして分割し、同じエリアの同じ月に生まれた顧客のID番号をエリアごとに12分割して保存します。 、他の種類の証明書のデータ量が 100,000 を超えない場合は、パーティション分割する必要はありません。

このように、クエリを実行するたびに、まず証明書の種類に基づいてクエリするテーブルを決定し、次にクエリのパーティション キーを計算します。パーティション設計後、2,000万件のユーザーデータを保存する際、銀行カードテーブルのデータ保存ファイルは10個の小さなファイルに分割され、証明書テーブルのデータ保存ファイルは12個の小さなファイルに分割され、2つのクエリの問題が解決されました。また、1 つの疑問が残ります。ビジネス番号をどうするかです。

#顧客は複数の契約サービスを持っていますが、それらを保存するにはどうすればよいですか?現時点では、パーティショニング ソリューションの使用は適切ではなく、テーブル パーティショニング ソリューションが必要です。

サブテーブル

mysql の場合、データ ファイルはファイルの形式でディスクに保存されると前述しました。データファイルが大きすぎると、オペレーティングシステムによる大きなファイルの操作が面倒で時間がかかり、またオペレーティングシステムによっては大きなファイルをサポートしていないため、テーブルを分割する必要があります。

さらに、mysql で一般的に使用されるストレージ エンジンは Innodb であり、その基礎となるデータ構造は B ツリーです。データ ファイルが大きすぎる場合、ノードのクエリでは多くのレベルがクエリされる可能性があり、必然的に複数の IO 操作がメモリにロードされることになり、確実に時間がかかります。

さらに、B ツリー用の Innodb のロック メカニズムもあります。各ノードをロックすることにより、テーブル構造が変更された際にツリーもロックされることになりますが、テーブルファイルが大きい場合にはこれが不可能と考えられます。要約すると、サブテーブルとサブデータベースの操作を実行する必要があります。

サブデータベースとサブテーブルを実行する方法、現在インターネット上には多くのバージョンがあり、よく知られたソリューションとしては、Alibaba の TDDL、DRDS、cobar、JD Finance の sharding-jdbc、民間組織などがあります。 MyCAT、360's Atlas、Meituan の Zebra、NetEase、58、JD.com などの他の企業も自社開発のミドルウェアを持っています。

非常に多くのサブデータベースおよびテーブル ミドルウェア ソリューションは、クライアント モードとプロキシ モードの 2 つのカテゴリに要約できます。

クライアント モード

プロキシ モード

クライアント モードかプロキシ モードか。 SQL の解析、書き換え、ルーティング、実行、結果のマージなど、いくつかの主要な手順は同じです。個人的には、アーキテクチャがシンプルでパフォーマンスの低下が比較的少なく、運用と保守のコストが低いクライアント モードを使用することを好みます。

ビジネス タイプをデータベースとテーブルに分割する方法。データベースとテーブルのシャーディングにおける最も重要なステップは、シャーディング列の選択です。シャーディング列の選択の品質によって、データベース シャーディングおよびテーブル シャーディング スキーム全体が最終的に成功するかどうかが直接決まります。シャーディング列の選択はビジネスに大きく関係します。

私たちのプロジェクト シナリオでは、シャーディング列に最適な選択は間違いなくビジネス番号です。ビジネス番号を通じて、顧客のさまざまなバインディング契約サービスがさまざまなテーブルに保存され、ビジネス番号に従ってクエリの対応するテーブルにルーティングされ、SQL がさらに最適化されます。

関連する php の知識については、php チュートリアル をご覧ください。

以上がPHP が MySQL の数千万のテーブルを最適化する方法の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。