これらのデータベース軍事規制を理解するために、MySQL を例として取り上げます。

データベースは常にアプリケーションの最も重要な部分ですが、同時に、データベースのテーブルとインデックスが最初に適切に設計されていない場合、後で水平方向に拡張する際にボトルネックになることがよくあります。データベース、サブデータベース、およびテーブルで問題が発生する可能性があります。

インターネット企業の場合、一般的に MySQL データベースが使用されます。

1. データベースの全体的なアーキテクチャ

まず、次のように MySQL データの全体的なアーキテクチャを見てみましょう:

これは非常に古典的な MySQL システム アーキテクチャの図であり、この図を通して、次の機能を確認できます。 MySQL の各部分。

クライアントがデータベースに接続すると、まず接続プールに接続します。この接続プールは、ユーザー接続を管理し、特定の認証と認可を実行するために使用されます。

データベースに接続した後、クライアントは SQL ステートメントを送信し、SQL インターフェース モジュールはユーザーの SQL ステートメントを受け取ります。

SQL ステートメントは厳密な文法規則に準拠する必要があることが多いため、ステートメントを解析するには文法パーサーが必要です。文法を解析する原則は、ステートメントから構文ツリーまでのコンパイル原則で学んだとおりです。

ユーザーが属するクエリを最適化して、最速のクエリ パスを選択できるようにするのがオプティマイザーの役割です。

クエリを高速化するために、クエリ キャッシュ モジュールがあり、クエリ キャッシュにヒットしたクエリ結果がある場合、クエリ ステートメントはクエリ キャッシュからデータを直接フェッチできます。

上記のコンポーネントはすべてデータベース サービス層であり、その後にデータベース エンジン層が続きます。現在の主流のデータベース エンジンは InnoDB です。

データベースへの変更はすべて、プライマリおよびセカンダリ レプリケーションの基礎となるデータベース サービス層のバイナリ ログに記録されます。

データベース エンジン層の有名な図は次のとおりです:

ストレージ エンジン層にはキャッシュとログもあり、最終的なデータはディスクにドロップされます。

ストレージ エンジン層のキャッシュもパフォーマンスを向上させるために使用されますが、データベース サービス層のキャッシュはクエリ キャッシュであるのに対し、データベース エンジン層のキャッシュはデータベース サービス層のキャッシュとは異なります。読むのも書くのも。データベース サービス層のキャッシュはクエリ ロジックに基づいていますが、データベース エンジンのキャッシュは物理的なデータ ページに基づいています。

データがデータベース エンジン層のキャッシュに書き込まれているだけであっても、データベース サービス層では、データは永続化されているとみなされます。これにより、キャッシュ ページとハード上のページの間でデータの不整合が発生します。この不整合は、データベース エンジン層のログによって整合性が確保されます。

つまり、データベース エンジン層のログはデータベース サービス層のログとは異なります。サービス層のログは変更ロジックを 1 つずつ記録しますが、エンジン層のログはキャッシュ ページとデータの物理的な違いを記録します。ページ。

2. データベースのワークフロー

クエリを受信すると、MySQL アーキテクチャの各コンポーネントは次のように動作します:

クライアントはデータベース サービス層との TCP 接続を確立し、接続管理モジュールが確立されます。接続スレッドを要求します。接続プールにアイドル状態の接続スレッドがある場合は、その接続スレッドがこの接続に割り当てられます。ない場合は、接続の最大数を超えることなく、このクライアントを担当する新しい接続スレッドが作成されます。

実際の操作の前に、ユーザーモジュールを呼び出して認可チェックを行い、ユーザーに権限があるかどうかを確認する必要があります。渡された後、サービスが提供され、接続スレッドはクライアントからの SQL ステートメントの受信と処理を開始します。

接続スレッドは SQL ステートメントを受信した後、構文分析と意味分析のためにそのステートメントを SQL ステートメント解析モジュールに渡します。

クエリ ステートメントの場合は、まずクエリ キャッシュに結果があるかどうかを確認でき、結果がある場合はクライアントに直接返すことができます。

クエリ キャッシュに結果がない場合は、実際にデータベース エンジン層にクエリを実行し、それをクエリ最適化のために SQL オプティマイザーに送信する必要があります。テーブルの変更の場合は、挿入、更新、削除、作成、および変更の処理モジュールに渡されて処理されます。

次のステップは、データベース エンジン レイヤーをリクエストし、テーブルを開いて、必要に応じて対応するロックを取得することです。

次の処理プロセスは、InnoDB などのデータベース エンジン層に進みます。

データベース エンジン層では、まずキャッシュ ページに対応するデータがあるかどうかをクエリする必要があります。存在する場合は、それをディスクから読み取る必要があります。

対応するデータがディスク上で見つかると、キャッシュにロードされ、後続のクエリがより効率的になります。メモリが限られているため、キャッシュされたページが頻繁にアクセスされるように、キャッシュ ページを管理するために柔軟な LRU テーブルがよく使用されます。データ。

データを取得したら、それをクライアントに返し、接続を閉じ、接続スレッドを解放してプロセスは終了します。

3. データベースインデックスの原則

プロセス全体で最も簡単にボトルネックポイントと呼ばれるものは、データの読み取りと書き込みです。これは、多くの場合、ディスクの連続的またはランダムな読み取りと書き込み、およびディスクの読み取りと書き込みの速度を意味します。多くの場合、比較的遅いです。

プロセスをスピードアップしたらどうなるでしょうか?インデックスを作成することであることは誰もが推測していると思います。

なぜインデックス作成によりこのプロセスが高速化できるのでしょうか?

誰もがこのグルメシティを訪れたことがあると思います。そこにはたくさんのレストランがあります。急いでいない、お腹が空いていない、検索パフォーマンスの要件がない場合は、ゆっくりと各店を見て回ってください。食べたいお店が見つかるまで。しかし、お腹が空いたときや、レストランの予約をしたときは、必ずそのレストランに直行したいですよね。このとき、目的のレストランの場所をすぐに見つけるために、フロアインデックスマップを参照することがよくあります。それを見つけたら、すぐにトピックに進みます。これは時間を大幅に節約します。これがインデックスの役割です。

つまり、インデックスは値を通じてその場所をすばやく見つけて、すぐにアクセスできるようにすることです。

インデックスのもう 1 つの機能は、実際にデータを見なくてもある程度の判断ができることです。たとえば、ショッピングモールに特定のレストランがあるかどうかは、インデックスを見るだけではわかりません。実際にモールに行ってすべての四川料理レストランを見つけるには、四川料理レストランを渡り歩くのではなく、インデックスを見るだけで済みます。

では、MySQL ではインデックスはどのように機能するのでしょうか?

MySQL のインデックス構造は、多くの場合 B+ ツリーです。

M-order B+ ツリーには次のプロパティがあります:

1. ノードはインデックス ノードとデータ ノードに分割されます。インデックス ノードは B ツリーの内部ノードに相当し、すべてのインデックス ノードが B ツリーを形成し、B ツリーのすべての特性を持ちます。インデックスノードにはKeyとポインタが格納され、特定の要素は格納されません。データ ノードは B ツリーの外部ノードに相当します。B ツリーの外部ノードは空であり、B+ ツリーで実際のデータ要素を格納するために使用されます。ポインタがありません。

2. インデックス ノード全体で構成される B ツリーは、特定のキーを持つデータ要素がどの外部ノードにあるかを見つけるためにのみ使用されます。インデックス ノードでキーが見つかった後も、問題は終了ではありません。データ ノードの検索を続行し、データ ノード内の要素を読み出すか、バイナリ検索または順次スキャンを実行して実際のデータを見つける必要があります。要素。

3. 次数 M はインデックス ノード部分の次数を制御するためにのみ使用され、各データ ノードに含まれる要素の数については M とは関係ありません。

4. すべてのデータノードをつなぎ合わせ、順番にアクセスできるリンクリストもあります。

この定義は比較的抽象的です。具体的な例を見てみましょう。

この図から、これが 3 次 B+ ツリーであり、外部データ ノードには最大 5 つの項目が含まれていることがわかります。挿入されたデータがデータ ノード内にあり、分割とマージが発生しない場合、インデックス ノードで構成される B ツリーは変更されません。

項目 76 が 71 から 75 への外部ノードに挿入されると、71、72、および 73 がデータ ノードになり、74、75、および 76 がインデックス ノードになります。 、これは 74 プロセスのキーを挿入することに相当します。

外部ノード41～43のうち43を削除すると、インデックスノードの場合は41、42、61、62、63が1つのノードにマージされます。キー60。

検索する場合、B+ ツリー層の高さは非常に小さいため、値 62 を見つけたい場合、それが 40 より大きいことがルート ノードで見つかった場合、比較的早く配置できます。 70 未満の場合は右側にアクセスし、60 より大きい場合は右側にアクセスします。2 番目のリーフ ノードで 62 が見つかり、正常に特定されました。

MySQL の InnoDB には 2 種類の B+ ツリー インデックスがあり、1 つはクラスター化インデックスと呼ばれ、もう 1 つはセカンダリ インデックスと呼ばれます。

クラスター化インデックスのリーフ ノードはデータ ノードであり、多くの場合、主キーがクラスター化インデックスとして使用されます。セカンダリ インデックスのリーフ ノードには、KEY フィールドと主キーの値が格納されます。したがって、セカンダリ インデックスを介してデータにアクセスするには、インデックスに 2 回アクセスする必要があります。

複数の列にインデックスを付けることができる、複合インデックスまたは複合インデックスと呼ばれるインデックスの形式もあります。

この種のインデックスの並べ替えルールは、最初に最初の列を比較し、最初の列が等しい場合は 2 番目の列を比較する、というように続きます。

4. データベース インデックスの利点と欠点

データベース インデックスの最も明白な利点は、I/O を削減することです。以下では、いくつかのシナリオを分析します。

= 条件を含むフィールドの場合、B+ ツリーを直接検索でき、非常に少ない数のハードディスク読み取り (B+ ツリーの高さに相当) でリーフ ノードに到達し、その場所を直接見つけることができます。データの。

範囲フィールドの場合、B+ ツリーがソートされているため、ツリーを通じて範囲をすばやく見つけることができます。

同様に、orderby、group by、distinct/max、minについても、B+ツリーがソートされているため、結果が早く得られます。

データをカバーするインデックスと呼ばれる一般的なシナリオもあります。たとえば、2 つのフィールド A と B が条件フィールドとして使用され、C と D を同時に選択すると、A=a AND B=b がよく現れ、結合インデックス (A, B) が構築されます。セカンダリ インデックスなので、検索する場合、対応するリーフ ノードとレコードはセカンダリ インデックスの B+ ツリーからすぐに見つかりますが、一部のレコードにはクラスター化インデックスの ID が含まれるため、クラスター化インデックスの B+ ツリーを検索する必要があります。一度テーブル内の実際のレコードを見つけてから、レコード内で C と D を読み上げます。ジョイント インデックスを確立するときにジョイント インデックスが (A、B、C、D) である場合、すべてのデータはセカンダリ インデックスの B+ ツリー内にあり、直接返すことができるため、ツリーの検索プロセスが軽減されます。

もちろん、インデックス作成には代償が伴います。世界には無料のランチはありません。

インデックスによってもたらされる利点のほとんどは読み取り効率の向上ですが、インデックスによってもたらされる代償は書き込み効率の低下です。

データを挿入および変更すると、インデックスが変更される場合があります。

挿入する場合、クラスター化インデックスは主キーに基づいて構築されることが多いため、主キーに自動インクリメントを使用するのが最善です。そうすることで、挿入されたデータは常に最後にあり、連続的であり、より効率的です。 。主キーには UUID を使用しないでください。これにより、ランダムな書き込みが発生し、効率が低下します。ビジネスに関連する主キーは使用しないでください。ビジネスに関連するということは、主キーが更新され、削除や再挿入が必要になり、効率が低下することを意味するためです。

B+ ツリーの原理についての上記の紹介を通じて、B+ ツリーでの分割コストは依然として比較的高く、挿入プロセス中に分割が頻繁に発生することがわかります。

データの改変は、基本的には削除して再挿入することに相当し、コストが比較的高くなります。

一部の文字列列のセカンダリ インデックスにより、ランダムな書き込みと読み取りが頻繁に発生し、I/O に大きな負荷がかかります。

5. データベース軍事規制の背後にある原則を解釈する

これら 2 つのインデックスの原則を理解すると、いわゆるデータベース軍事規制の多くがこのようになる理由を説明できます。以下で一つずつ説明していきましょう。

どのような状況で、個別のインデックスの代わりに結合インデックスを使用する必要がありますか?

条件文 A=a AND B=b があるとします。A と B が 2 つの別個のインデックスである場合、B については 1 つのインデックスのみが AND 条件で機能し、結合された場合は 1 つずつ判断する必要があります。インデックス (A、B) を使用すると、ツリーをトラバースするだけで効率が大幅に向上します。ただし、A=a OR B=b の場合、OR 関係により、結合されたインデックスは機能しないため、この時点では、2 つのインデックスを同時に使用できます。

なぜインデックスを区別する必要があるのですか? 結合インデックスでは、区別されたものが最初に配置される必要がありますか?

性別を使用するなどの区別がない場合、データを検索するには依然としてテーブルの半分を走査する必要があり、インデックスは無意味になります。

複合インデックスがある場合でも、単一列インデックスが必要ですか?

結合インデックスが (A, B) の場合、この結合インデックスは条件 A=a に使用できます。結合インデックスは最初に最初の列に従って並べ替えられるため、別のインデックスを作成する必要はありません。 A の場合は使用されますが、 B=b には使用されません。2 番目の列は最初の列が同じである場合にのみ比較されるため、2 番目の列は同じであり、異なるノードに分散される可能性があり、迅速に比較する方法はありません。それを見つけてください。

インデックスは多ければ多いほど良いのでしょうか?

もちろん、必要な場合にのみインデックスを追加してください。インデックスは挿入と変更の効率を低下させるだけでなく、クエリ時にクエリ オプティマイザーが混乱し、インデックスが見つからなくなる可能性があります。クエリ パスに基づいて、低速インデックスが選択されます。

なぜ自動インクリメント主キーを使用するのですか?

文字列主キーとランダム主キーではデータがランダムに挿入され、効率が比較的低いため、B+ ツリーや頻繁なマージと分割を避けるために、主キーの更新頻度を下げる必要があります。

NULL を使用しないようにするのはなぜですか?

NULL は B+ ツリーでの処理がより難しく、多くの場合、処理に特別なロジックが必要となるため、効率が低下します。

頻繁に更新されるフィールドにインデックスを作成してみませんか?

フィールドの更新は、対応するインデックスも更新する必要があることを意味します。多くの場合、インデックスは、書き込み段階で事前に特定のデータ構造を形成し、それによって読み取り段階を効率化する方法です。ただし、フィールドの書き込みが多く、読み取りが少ない場合は、インデックスを使用することはお勧めできません。

クエリ条件で関数を使用しないのはなぜですか?

例えば、ID+1=10の条件では、ID+1などの操作のクエリフェーズではインデックスが生成されることはありません。最初にすべてのインデックスを計算してから比較する方法では、コストが高すぎるため、ID=10-1 を使用する必要があります。

NOT などの否定的なクエリ条件を使用しないのはなぜですか?

B+ ツリーの場合、ベース ノードは 40 であると想像できます。条件が 20 に等しい場合は、左に移動して確認します。条件が 50 に等しい場合は、右に移動して確認します。は 66 に等しくありません。インデックスはどうすればよいですか?すべてを経験するまではわかりません。

あいまいクエリがワイルドカード文字で始まらないのはなぜですか?

B+ ツリーの場合、ルートが文字 def で、ワイルドカードが後ろにある場合 (abc% など)、左側を検索する必要があります (efg% など)、右側を検索する必要があります。ワイルドカード文字が先頭にあります (%abc)。どこに行けばよいかわかりません。どちら側でも、すべてスキャンしましょう。

なぜ OR を IN に変更したり、Union を使用したりする必要があるのでしょうか?

OR クエリ条件の最適化は、特に OR 条件が多い場合に困難になることが多く、データベースは IN で条件をソートします。二分探索法による一元処理。さまざまなフィールドに対して Union を使用すると、各サブクエリでインデックスを使用できるようになります。

なぜデータ型をできるだけ小さくする必要があるのでしょうか? 長い文字型では、文字型の代わりに整数型がよく使用されます。

データベースはページに保存されるため、各ページのサイズは同じですが、データ型が大きいほどページ数は大きくなり、各ページに配置されるデータは小さくなり、ツリーの高さは低くなります。データを検索するために読み込む I/O の数が比較的多くなり、挿入時にノードが分割されやすくなり、効率が低下します。文字型の代わりに整数を使用する理由は、IP アドレスなどのインデックス付けでは整数の方が効率的であるためです。インデックスを使用してクエリする必要がある長い文字タイプがある場合、インデックスが大きくなりすぎないように、フィールド全体ではなくフィールドのプレフィックスにインデックスを付けることを検討できます。

6. クエリ最適化手法

最適化する必要がある SQL ステートメントを見つけるには、まず問題のある SQL ステートメントを収集する必要があります。

MySQL データベースは、slow_query_log パラメータを通じて、実行時間が特定のしきい値を超えた SQL 引用符のリストを取得できます。

インデックスを使用しない SQL ステートメントは、long_queries_not_using_indexes パラメーターを通じて有効にすることができます。

min_examined_row_limit、スキャンされたレコードの数がこの値を超える SQL ステートメントのみが低速 SQL ログに記録されます。

問題のあるステートメントを見つけたら、次のステップは、explainSQL を通じて SQL 実行計画を取得することです。インデックスを介してレコードをスキャンするかどうかに関係なく、インデックスを作成することで実行効率を最適化できます。スキャン記録が多すぎるかどうか。ロックが長時間保持されているかどうか、またはロックの競合が発生しているかどうか。返されるレコードの数が多いかどうか。

次にカスタマイズされた最適化を実行できます。インデックスでカバーされていないフィルター条件に関係するフィールドについては、より区別性の高いフィールドにインデックスを作成します。複数のフィールドが関係する場合は、結合インデックスを作成してみてください。

スキャンされたレコードの数は非常に多いですが、返されるレコードの数は少なく、SQL ステートメントに含まれるフィールドを再評価し、識別性の高いフィールドを複数選択してインデックスを作成します。

スキャンされたレコードの数が非常に多く、返されたレコードの数も非常に多く、SQL フィルタリング条件

schema_redundant_indexes を追加して、どの冗長なインデックスがあるかを確認します。

複数のインデックスに同じ順序でフィールドが含まれる場合、結合インデックス schema_unused_indexes を作成して、どのインデックスが使用されていないかを確認できます。

7. 読み取りと書き込みの分離の原則

データベースは書き込み量を減らし、読み取り量を増やすことが多いため、パフォーマンスの最適化の最初のステップは読み取りと書き込みを分離することです。

マスター/スレーブ レプリケーションは、マスター ノードのサービス層のログに基づいて実装され、スレーブ ノードにはこのログを読み取ってローカルに書き込むための IO スレッドがあります。別のスレッドがローカル ログから読み取り、スレーブ ノードで再実行します。

写真はマスタースレーブ非同期レプリケーションのフローチャートを示しています。マスター インスタンスはエンジンに書き込みを行った後、成功を返し、イベントをスレーブ インスタンスに送信してスレーブ インスタンス上で実行します。この同期方法は高速ですが、レプリケーションがない場合にマスターがハングアップすると、データ損失の問題が発生する可能性があります。

データベースの同期レプリケーションも異なります。もちろん、これにより、NetEase データベース チームはグループ送信、並列レプリケーション、その他のテクノロジによってパフォーマンスが向上します。 。

マスター/スレーブ レプリケーションでは、読み取りと書き込みの分離戦略をデータベース DAO レイヤーで設定でき、これはデータベース ミドルウェアを通じて行うこともできます。

実際、データベース ログには他にも多くの用途があります。たとえば、Canal (Alibaba オープン ソース プロジェクト: MySQL データベース Binlog に基づく増分サブスクリプションと消費) を使用してデータベースの Binlog をサブスクライブし、キャッシュの更新に使用できます。等