百万レベルのデータ処理ソリューションデータストレージ構造設計テーブルフィールドの設計
インデックス設計
クエリステートメントの最適化
1000万レベルのデータ処理ソリューションデータストレージ構造設計この段階では、データ自体に大きな価値があります。通常のビジネスニーズを満たすだけでなく、データ分析のニーズもいくつかあります。現時点では、データの変動性は高くなく、基本的に元の構造の変更は考慮されていません。一般的に、最適化は、パーティション分割、テーブル シャーディング、データベース シャーディングの 3 つの側面から検討されます。 パーティション:
サブテーブル: サブテーブルは、水平サブテーブルと垂直サブテーブルに分かれています。 水平テーブルパーティション分割は、データベースの読み取りと書き込みの負荷を軽減するために、テーブルを table1、table2 などの同じデータ構造を持つ小さなテーブルに分割します。 垂直テーブル分割は、いくつかのフィールドを分離して新しいテーブルを形成することです。各テーブルのデータ構造は異なるため、高い同時実行性の下でテーブルをロックする状況を最適化できます。 ご想像のとおり、テーブルを分割する場合は、プログラムのロジックを変更する必要があります。そのため、通常はプロジェクトの初期段階で、大量のデータが予想される場合に、テーブルの分割を検討します。コストが非常に高くなるため、後の段階でテーブルを分割することはお勧めしません。 サブライブラリ: サブデータベースは、通常、マスター スレーブ モードです。データベース サーバーのマスター ノードは、1 つ以上のスレーブ ノードの複数のデータベースに複製されます。マスター データベースは書き込み操作を担当し、スレーブ データベースは読み取り操作を担当するため、マスターとスレーブの分離、高可用性、データ バックアップなどの最適化の目標が達成されます。 もちろん、マスタースレーブモードにも、マスタースレーブ同期の遅延、binlog ファイルが大きすぎるために発生する問題など、いくつかの欠陥があります。ここでは詳細には触れません (著者はもうそれを学ぶことができません)。 他の: ホットメーターとコールドメーターは分離されています。履歴データについては、クエリを実行して使用する人が少ない場合は、別のコールド データベースに移動してクエリ専用に提供することで、ホット テーブル内の大量のデータを軽減できます。 データベーステーブルの主キー設計データベースの主キーの設計については、時間属性を持つ自己増加する数値 ID を個人的に推奨します。 (分散型自己増分ID生成アルゴリズム)
なぜこれらのアルゴリズムを使用するのでしょうか? これは、MySQL のデータ ストレージ構造に関連しています。 ビジネスの観点から: データベースを設計するときに、どのフィールドを主キーとして設定するかを考える必要はありません。そして、これらのフィールドは理論上のみ一意です。たとえば、書籍番号を主キーとして使用する場合、書籍番号は理論上のみ一意ですが、実際には重複が発生する可能性があります。そのため、業務に関係のない自動増分IDを主キーとして設定し、帳票番号に一意制約を追加するのが良いでしょう。 技術的に言えば: 1. テーブルが自動増分主キーを使用している場合、新しいレコードが挿入されるたびに、そのレコードは現在のインデックス ノードの後続の位置に順番に追加されます。ページがいっぱいになると、新しいページが自動的に開かれます。 一般的に、クエリと挿入のパフォーマンスが向上します。 2. InnoDB の場合、主キー インデックスはインデックス値と行データの両方をリーフ ノードに格納します。つまり、データ ファイル自体は b+ ツリー形式でデータを格納することになります。 3. 主キーが定義されていない場合は、空でない UNIQUE キーが主キーとして使用されます。空でない UNIQUE キーがない場合、システムは 6 バイトの rowid を主キーとして生成します。クラスター化インデックスでは、N 行が 1 ページを形成します (ページのサイズは通常 16K です)。不規則なデータが挿入されると、B+ツリーのバランスを保つために、ページ分割やページローテーションが頻繁に発生し、挿入速度が遅くなります。したがって、クラスター化インデックスの主キー値は、ランダムな値ではなく、継続的に増加する値にする必要があります (ランダムな文字列や UUID は使用しないでください)。 4. したがって、InnoDB の主キーには、整数、および増加する整数を使用するようにしてください。これはストレージとクエリの両方において非常に効率的です。 MySQL 面接の質問MySQLデータベース数千万データクエリ最適化ソリューション制限ページングクエリが遅くなるほど、クエリは遅くなります。このことから、次のような結論も導き出されます。 1. 制限ステートメントのクエリ時間は、開始レコードの位置に比例します。 2. MySQL の limit ステートメントは非常に便利ですが、レコード数が多いテーブルに直接使用するには適していません。 テーブルはストレージエンジンとしてInnoDBを使用し、自動増分主キーとしてidを使用し、デフォルトの主キーインデックスを使用します。 テストの制限9000000,100からIDを選択します。 現在、最適化ソリューションには、サブクエリを使用してクエリ条件として id を使用する方法と、結合を使用する方法の 2 つがあります。 1. id>= (サブクエリ) フォームの実装 select * from test where id >= (select id from test limit 9000000,1)limit 0,100 参加フォームを使用してください。 SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 9000000,100) b ON a.id = b.id これら 2 種類の最適化されたクエリの使用にかかる時間は比較的近いです。実際、どちらも同じ原理を使用しているため、効果は似ています。しかし、個人的には結合を使用し、サブクエリの使用を最小限に抑えることをお勧めします。注: 現在、クエリは数千万レベルです。数百万レベルに増やすと、速度は速くなります。 SELECT * FROM test a JOIN (SELECT id FROM test LIMIT 1000000,100) b ON a.id = b.id どの MySQL ストレージ エンジンを使用したことがありますか?それぞれの特徴と違いは何でしょうか?これは上級開発者の面接でよく聞かれる質問です。実際、私たちは日々の開発の中でこれによく遭遇します。 MySQL ストレージ エンジンは数多くありますが、最もよく使用されるのは InnoDB と MyISAM です。したがって、面接官が MySQL にどのようなストレージ エンジンがあるか尋ねた場合、よく使用される 2 つのエンジンを伝えるだけで済みます。 では、それぞれの特徴と違いは何でしょうか?MyISAM: 従来の ISAM タイプに基づいたデフォルトのテーブル タイプです。ISAM は Indexed Sequential Access Method (インデックス シーケンシャル アクセス メソッド) の略で、レコードとファイルを保存するための標準的な方法です。トランザクションセーフではなく、外部キーをサポートしていません。多数の選択が実行される場合は、insert MyISAM の方が適しています。 InnoDB: トランザクションセキュリティをサポートするエンジン。外部キー、行ロック、トランザクションをサポートしているのが最大の特徴です。更新や挿入が大量に発生する場合、特に複数の同時実行や高 QPS の状況では、InnoDB を使用することをお勧めします。注意: MySQL 5.5 より前のバージョンでは、デフォルトの検索エンジンは MyISAM です。MySQL 5.5 以降のバージョンでは、デフォルトの検索エンジンは InnoDB に変更されます。 MyISAM と InnoDB の違い1. InnoDB はトランザクションをサポートしますが、MyISAM はサポートしません。 InnoDB の場合、各 SQL ステートメントはデフォルトでトランザクションにカプセル化され、自動的にコミットされるため、速度に影響します。したがって、トランザクションを形成するには、begin と commit の間に複数の SQL ステートメントを配置するのが最適です。 2.InnoDB は外部キーをサポートしますが、MyISAM はサポートしません。 3. InnoDB はクラスター化インデックスであり、インデックス構造として B+Tree を使用します。データ ファイルは (主キー) インデックスにバインドされます (テーブル データ ファイル自体は B+Tree で編成されたインデックス構造です)。主キーが存在する必要があり、主キー インデックスの効率は非常に高くなります。 MyISAM は非クラスター化インデックスであり、インデックス構造として B+Tree も使用します。インデックスとデータ ファイルは別々であり、インデックスはデータ ファイルへのポインタを保存します。主キー インデックスとセカンダリ インデックスは独立しています。 4. InnoDB はテーブル内の特定の行数を保存しないため、select count(*) from table を実行するときにテーブル全体をスキャンする必要があります。 MyISAM は、テーブル全体の行数を保存するために変数を使用します。上記のステートメントを実行するときは、変数を読み取るだけでよいため、非常に高速です。 5. Innodb はフルテキスト インデックスをサポートしていませんが、MyISAM はサポートしています。MyISAM の方がクエリ効率が高くなります。InnoDB 5.7 以降ではフルテキスト インデックスがサポートされています。 6. InnoDB はテーブルレベルと行レベルのロック (デフォルト) をサポートしますが、MyISAM はテーブルレベルのロックをサポートします。 ; 7. InnoDB テーブルには主キーが必要です (ユーザーが指定しない場合は、テーブル自体が主キーを見つけるか生成します)。一方、Myisam には主キーがありません。 8.Innodb ストレージ ファイルは frm と ibd ですが、Myisam は frm、MYD、MYI です。 9.Innodb: frm はテーブル定義ファイル、ibd はデータ ファイルです。 10.Myisam: frm はテーブル定義ファイル、myd はデータ ファイル、myi はインデックス ファイルです。 MySQLの複雑なクエリ文の最適化複雑な SQL の最適化に関しては、ほとんどの場合、複数のテーブルの関連付けが原因で、多数の複雑な SQL ステートメントが発生します。では、このような SQL をどのように最適化すればよいのでしょうか。実際には最適化のためのルーチンがあり、そのルーチンに従うだけで済みます。複雑な SQL 最適化ソリューション: 1. EXPLAIN キーワードを使用して SQL をチェックします。 EXPLAIN は、クエリ ステートメントまたはテーブル構造のパフォーマンス ボトルネックを分析するのに役立ちます。EXPLAIN のクエリ結果では、インデックスの主キーがどのように使用されているか、データ テーブルがどのように検索およびソートされているか、完全なテーブル スキャンがあるかどうかなどもわかります。 2. クエリ条件にインデックス フィールドを使用するようにします。テーブルに複数の条件がある場合は、複合インデックス クエリを使用するようにします。複合インデックスを使用する場合は、フィールドの順序に注意してください。 3. 複数のテーブルを関連付けるために、可能な限り結合を使用し、サブクエリの使用を減らします。テーブルの関連フィールドが主キーを使用できる場合は、主キーを使用します。つまり、インデックス フィールドを可能な限り使用します。関連付けられたフィールドがインデックス フィールドでない場合は、状況に応じてインデックスを追加することを検討できます。 4. ページング バッチ クエリに制限を使用し、一度にすべてを取得しないでください。 5. select * の使用は絶対に避け、特定の必須フィールドを選択するようにし、不要なフィールドのクエリを減らします。 6. すべてを変換または結合してみます。 7. is null または is not null の使用は避けてください。 8. のような使用に注意してください。事前ぼかしと完全ぼかしではインデックスは使用されません。 9. 関数はインデックス エラーの原因となるため、Where の後のクエリ フィールドでの関数の使用を最小限に抑えるようにしてください。 10. インデックスを使用しないため、等しくない (!=) の使用は避けてください。 11. in の代わりに exists を使用し、not in の代わりに not exists を使用すると、より効率的になります。 12. HAVING 句の使用は避けてください。HAVING は、すべてのレコードが取得された後にのみ結果セットをフィルター処理するため、並べ替え、合計、およびその他の操作が必要になります。 WHERE 句を使用してレコード数を制限できる場合は、このオーバーヘッドを削減できます。 13. ORDER BY RAND() は使用しない 上記は、MySQL データベースにおける数千万件のデータのクエリと保存に関する詳細な説明です。MySQL データベースにおける数千万件のデータのクエリと保存の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。 以下もご興味があるかもしれません:
|
>>: 選択ドロップダウンメニューのテキストを左右にスクロールするように設定する
これら 2 つの属性はよく使用されますが、その違いはまとめられていません。それでは、その使い方をまと...
HTTP圧縮とは場合によっては、比較的大きなメッセージ データがクライアントとサーバー間で送信され、...
質問: Vue にブログ投稿をアップロードするためのフォームがあり、タイトル、本文、説明、スニペット...
このチュートリアルでは、LinuxでのMySQL 5.6.33のインストールと設定方法を参考までに紹...
国慶節の休暇後、Windows アップデート後に VMware 仮想マシンが開けなくなり、「VMwa...
vueプロジェクトでは、アップロードした画像に透かしを追加して参照できるようにするアップグレード版...
目次Tomcat でプロジェクトを展開する 3 つの方法プロジェクトをwebappsディレクトリに直...
DIVの背景は半透明ですが、DIV内の文字は半透明ではありませんコードをコピーコードは次のとおりです...
MySQLのマスタースレーブ構成と原理、参考までに具体的な内容は以下のとおりです。 1. 環境の選択...
以前、Docker コンテナとローカル マシン間のファイル転送に関する記事を書きました。しかし、この...
インターネット上には、正しい方法であっても、使用しても正しい結果が得られない方法が数多くあります。正...
背景位置が背景画像の表示に与える影響この2日間のプロジェクトでホームページの写真を入れ替えていたとこ...
モバイル ブラウザは、Web ページを仮想の「ウィンドウ」(ビューポート) に配置します。このウィン...
MySQL 5.7 以降のバージョンでは、冗長インデックス、重複インデックス、およびインデックスを使...
1. psshを確認してインストールします。yum list pssh 2. キーレスログインが設定...