MySQL データベース インデックスの面接の質問 (基本的なプログラマー スキル)

導入

インデックス作成はMysqlにとって難しい問題ですが、プログラマーにとっては非常に重要な基本スキルでもあります。通常のプロジェクト開発においては、 SQL最適化の重要な手段となります。就職面接では、面接官が求職者のデータベース パフォーマンスの最適化を調べるためによく使用する重要な考慮事項です。したがって、インデックス作成の原則を徹底的に習得し、それを実際のデータベース クエリに適用できることは、すべてのプログラマーにとって必要な能力です。この記事では、インデックスの原則とインデックスの設計原則の観点からMysqlインデックスについて説明します。この記事を読めば、 Mysqlインデックス クエリ データに関する理解を Alibaba の面接官に完全に納得させることができると信じています。準備はできたか？私たちは出発しました。

インデックスの原則

インデックスを設計して最適化する前に、まずインデックスの原理を深く理解しましょう。すべての設計と最適化は、原則を完全に理解した上で行う必要があるためです。

SQLクエリを実行するときは、同じテーブルと同じデータを使用することを多くの人が知っています。インデックスの有無にかかわらずデータをクエリします。両者の間には大きな違いがあります。では、なぜこのようなギャップがあるのでしょうか?簡単に言えば、ビジネス データを辞書に例えると、インデックスはこの辞書のディレクトリになります。単語を調べるように頼んだ場合、ディレクトリを使用して調べなければ、ページを 1 ページずつめくるしかありません。運が悪ければ、目的の単語を見つけるために最後のページまでめくらなければならないかもしれません。これが伝説的なフル テーブル スキャンです。ただし、ディレクトリを検索すると、単語が配置されているページをすばやく見つけて、対応する単語を見つけることができます。ご存知のように、インデックスの力はデータクエリの効率を向上させることにあります。さて、これでインデックスについての知覚的な理解が得られました。それでは、さらに詳しく見てみましょう。

Mysqlのインデックスのデータ構造はB+ツリーであることは皆さんご存知でしょう ( BツリーやHashインデックスなどの構造の長所と短所については、この記事の焦点では​​ないためここでは説明しません)。そこで、ディスク上のインデックスのB+ツリーが段階的にどのように成長していくのかを見てみましょう。

1. データページ

日常的なプロジェクト開発では、ビジネス データのほとんどはリレーショナル データとして存在します。その後、データベース内の各テーブルのデータは最終的にサーバーのハードディスクに保存されます。このデータがどのように保存されるかについて考えたことはありますか?実際、 Mysqlデータベースで私たちが毎日使用するデータベース テーブルは、人間が理解できる論理テーブルです。実際にはデータ ページとしてディスク上に保存されます。データ ページは、ディスクとメモリ間のやり取りの基本単位です。MySQL のMysqlストレージ エンジンは、実際にはディスク上のデータ ページInnodb直接操作するのではなく、 buffer poolを介してディスク上のデータ ページとやり取りします。データ ページの構造を次の図に示します。

同時に、隣接するデータ ページは双方向リンク リストを通じて相互に参照されます。下の図のように、オレンジがかった赤色の部分がデータページで、真ん中の小さなボックスが具体的なデータとして分かります。 MysqlのInnoDBストレージ エンジンのデータ ページ サイズは16KBです。 MysqlのInnodbストレージ エンジンは、ページ番号を通じてデータ ページを一意に特定するため、各データ ページには独自のページ番号が設定されます。上の図からわかるように、各データ ページには対応するPage Header Page Headerがあり、現在のデータ ページのページ番号、次のページのページ番号、および前のページのページ番号が格納されます。

隣接するデータは、ポインターを通じて相互参照します。ポインターは、データ ページのページ番号を示します。各データ ページには、連続したデータ セグメントが格納されます。各データ行のレコード ヘッダーには、実データの次の行のアドレス オフセットが格納されます。これは、次のデータ行のアドレスを指すポインターを持つものとして簡単に理解できます。したがって、データ ページ内には、実際にはデータ行に関する一方向のリンク リストが存在します。この一方向リンクリストは主キーidに関するもので、小さいものから大きいものの順に並べられています。

上記のデータ ページ構造から、データが挿入されるたびに、 User Records領域が大きくなり、対応するUser Record領域が縮小されることがわかります。 User Record領域が消費されると、ページ分割が発生し、新しいデータ ページが形成されます。ここで注目すべきは、 Mysqlで自動増分主キーを使用すると、データ行がidの昇順に並べられることを保証できるということです。ただし、主キーが自分で設定されていて自動増分されていない場合は、後で挿入されたデータの主キー値が前のデータの主キー値よりも小さくなる可能性があります。この場合、ページを分割するときに、 Mysql主キーのサイズに応じて並べ替えます。ここで何か疑問があるかと思いますが、なぜ主キーのサイズに応じて並べなければならないのでしょうか?実際、それは後続のデータクエリに関係しています。データページ内のデータを主キーの順序で並べることは、インデックスの正常な動作の基礎となります。一般的なプロセスを次の図に示します。

2. ページディレクトリ

各データ ページには独自のページ ディレクトリがあります。上記のページ構造のPage Directory 、実際にはデータ行を検索するために使用されます。データ ページ内のデータは、実際にはグループ別に割り当てられます。ページ ディレクトリ内の異なるスロットは、実際にはデータ ページ内の異なるグループに対応しています。データを照会するときは、 idを通じて対応するスロットを見つけ、対応するスロットに基づいてデータ ページ内の対応するデータ行グループを把握し、対応するデータが見つかるまでデータ行グループ内のデータをトラバースします。

3. インデックス原則分析

（1）インデックスの基礎

上記の 2 つのセクションのデータ ページに関する基本的な知識があれば、インデックスの原則を理解しやすくなります。インデックスがない場合、データ クエリはすべてフル テーブル スキャンによって実行されます。クエリ データ ページ内の各データ行を走査し、条件を満たすデータ項目が見つかるまですべてのデータ ページを走査します。したがって、クエリの効率は非常に低くなります。では、データクエリの効率を向上させるにはどうすればよいでしょうか?データ ページ番号を見つけるために、辞書ディレクトリのような主キー ディレクトリを持つことは可能ですか?答えは「はい」です。これはまさにMysqlが行うことです。 Mysql 、実際には伝説的な主キー インデックスである主キー ディレクトリを使用して、データ クエリを最適化します。主キー ディレクトリには 2 つの重要な要素が含まれています。1 つはデータ ページ内の最小の主キーであり、もう 1 つは現在のデータ ページのページ番号です。このようにして、この主キー ディレクトリを通じてデータを照会できます。

たとえば、主キーid=5でデータを照会する場合は、まず主キー ディレクトリを検索します。このとき、主キーid=5主キーid=1より大きいが、 id=8より小さいことが分かるので、データは実際にはページ番号1のデータページにあると判断できます。

もちろん、実際にはMysqlには多くのデータ ページが存在するため、対応する主キー インデックスも多数存在します。この場合、バイナリ検索によってデータ ページを見つけ、対応するデータを見つける必要があります。

（２）インデックスページ

現在、さまざまなインターネット企業が急速に発展しており、それに伴う事業量も膨大になっています。そのため、データベース内のデータ量も非常に大きくなります。テーブルには数百万、数千万のデータが存在するのが一般的です。上記の主キーディレクトリに従って、多数の主キーとデータページ番号を保存する必要があります。バイナリ検索を実行しても、データのクエリ効率は比較的低くなります。

Mysql実際にはインデックス ステートメントをインデックス ページに保存します。データ量が多い場合は、対応するインデックスの数が増えるため、特別なインデックス ページを使用してインデックス データを保存します。さらに、これらのインデックス ページの上位層では、主キーとインデックス ページ番号を使用してインデックス ページのクエリと検索が継続されるため、次の構造が得られます。ここでのid番号は、対応する最小のid番号を指します。

インデックス ページ内のデータが増えると、インデックス ページも分割されます。このように、インデックス ページはさまざまなレベルを形成し、インデックス ページ レイヤー、インデックス ページ、データ ページの 3 つのページ データはB+ツリーと呼ばれるものを形成します。下の図は、インデックスのB+ツリー構造を示しています。これにより、完全なテーブルスキャンよりもはるかに効率的にデータ クエリを完了できます。 B+のリーフ ノードのみにデータが格納されます。次の図は、クラスター化インデックスとも呼ばれる主キー インデックスです。実際、その基本的な考え方は分割統治であることがわかります。データの量は膨大ですよね？そこで、データを多くのデータ ページに分割します。データ ページはたくさんありますよね？そこで、インデックス ページを使用してデータ ページを整理します。インデックス ページはたくさんありますよね？そこで、インデックス ページを使用してインデックスを作成します。

B+ツリーのデータ クエリ プロセスを見てみましょう。たとえば、ID 3 のデータをクエリする必要がある場合、インデックス ページでインデックス ページ 3 に移動する必要があることを決定します。次に、インデックス ページ3では、 id=1を引き続き決定し、インデックス ページでは、ページ番号1のデータ ページである必要があることを決定します。このデータ ページをトラバースし、最終的に対応するデータを見つけます。

上記のインデックス ページとデータ ページで構成されるB+ツリーはクラスター化インデックスです。もちろん、他のフィールドを通じて通常のインデックスを作成することもできます。通常のインデックスのリーフノードには、特定のデータではなく、対応する主キーidが格納されます。インデックスにはテーブルバックトラックの問題があります。つまり、対応するidをクエリした後、 idに基づいてクラスター化インデックス内の特定のデータをクエリし続ける必要があります。このような操作を通じてのみ、 select *のすべてのデータをクエリできます。もちろん、カバーリング インデックスを使用することで、このようなクエリの無駄を回避できます。

要約する

この記事では、 MysqlのInnoDBのインデックス作成原理をステップバイステップの図を使用して説明し、対応するB+ツリー インデックス構造を構築します。データクエリの具体的なプロセスについて説明します。皆さんはインデックスについてより深く理解できたと思います。後ほど、実践的な観点から、インデックスの設計方法とインデックス障害への対処方法を分析します。