MySQL Innodbインデックスの原理の詳細な説明

導入

振り返ってみると、4年前、私がMySQLのインデックスについて学んでいたとき、先生はインデックスについて次のように言っていました。

索引は本の目次のようなものです。ユーザーがインデックスを通じてデータを検索するのは、ユーザーがディレクトリを通じて章内の特定の知識ポイントを検索するのと似ています。これにより、ユーザーは検索速度を効果的に向上させることができます。したがって、インデックスを使用すると、データベース システム全体のパフォーマンスを効果的に向上させることができます。

まあ、それは実際に本当です。しかし、この文章を読んでも、まだ抽象的すぎると思うかもしれません。そこで、さらに詳しく解説したいと思い、この記事を作成しました！

私が述べたことは、MySQL の Innodb エンジンでのみ有効であることに注意してください。 Sql Server、Oracle、Mysql の Mysiam エンジンの正確性は必ずしも正確ではない可能性があります。

InnoDB は、MySQL で最も一般的に使用されているストレージ エンジンです。InnoDB ストレージ エンジンのインデックスを理解することは、日常業務に非常に役立ちます。インデックスの存在は、データベースの行レコードの取得を高速化するためのものです。

インデックスとは何ですか?

インデックスはディレクトリとして翻訳され、検索したいデータをすばやく見つけるために使用されます。たとえば、データベースを本に例えると、索引は本の目次です。本の中で特定の興味深いコンテンツを見つけるために、通常は本全体をめくってコンテンツがどこにあるかを確認することはありません。代わりに、目次を使用してコンテンツの章があるページを見つけ、そのページに直接めくります。

データベース内のインデックスを見てみましょう。

フルテーブルスキャンとインデックススキャン

辞書を例にとると、フルテーブルスキャンとは、特定の単語を探している場合、新華辞書を最後まで読み、目的の単語を見つけることを意味します。フルテーブルスキャンに対応するのはインデックス検索です。これは、テーブルのインデックス部分で必要なデータの特定の場所を見つけ、テーブル内で必要なすべてのデータを見つけるというものです。

はい、では、これ以上何も言わずに、話を始めましょう!

文章

インデックスのポピュラーサイエンス

まず、クラスター化インデックスと非クラスター化インデックスの概念を紹介します。

通常使用するMysqlでは、次の文を使用します。

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
 [インデックスタイプを使用]
 ON tbl_name (index_col_name,...)
 
インデックス列名:
 col_name [(長さ)] [ASC | DESC]

作成されたインデックスは、複合インデックス、プレフィックス インデックス、一意インデックスなど、すべて非クラスター化インデックスです。一部の書籍では、セカンダリ インデックスとも呼ばれています。以下のテキストでは、これを非クラスター化インデックスと呼び、そのデータ構造は B+ ツリーです。

さて、MySQL にはこのクラスター化インデックスを個別に生成するステートメントはありません。 Innodb では、MySQL 内のデータは主キーの順序で保存されます。次に、クラスター化インデックスは各テーブルの主キーに従って B+ ツリーを構築し、リーフ ノードにテーブル全体の行データを格納します。テーブル内のデータは B+ ツリーに従ってのみソートできるため、テーブルにはクラスター化インデックスを 1 つだけ持つことができます。

Innodb では、クラスター化インデックスがデフォルトで主キー インデックスになります。

この時点で、賢明な読者は私に尋ねるはずだ

テーブルに主キーがない場合はどうなりますか?

答えは、主キーがない場合は、次の規則に従ってクラスター化インデックスを構築することです。

主キーがない場合、一意で空でないインデックス列が主キーとして使用され、このテーブルのクラスター化インデックスになります。このようなインデックスがない場合、InnoDB は暗黙的に主キーをクラスター化インデックスとして定義します。

ps:自動インクリメント主キーと主キーとしての UUID の違いを覚えていますか?主キーはクラスター化インデックスを使用するため、主キーが自動増分 ID の場合は、対応するデータもディスク上に隣接して保存する必要があり、書き込みパフォーマンスが比較的高くなります。 UUID 形式の場合、挿入が頻繁に行われると InnoDB はディスク ブロックを頻繁に移動することになり、書き込みパフォーマンスが比較的低下します。

インデックス作成の原則の紹介

まず、主キーを持つテーブルを見てみましょう。以下に示すように、pIdが主キーです。

表の構造図を次のように描きます

上図に示すように、2 つの部分に分かれており、上部は主キーによって形成された B+ ツリーで、下部はディスク上の実際のデータです。次に、次の文を実行すると

pId='11' のテーブルから * を選択

実行プロセスは次のようになります

上図に示すように、ルートから始めて 3 回の検索を実行すると、実際のデータが見つかります。インデックスを使用しない場合は、データの場所が見つかるまでディスクを行ごとにスキャンする必要があります。当然ですが、インデックスを使用すると速度が速くなります。ただし、データを書き込む際には、この B+ ツリーの構造を維持する必要があるため、書き込みパフォーマンスが低下します。

では、非クラスター化インデックスを導入しましょう。次のステートメントを実行します。

テーブル(name)にインデックスindex_nameを作成します。

構造図は以下のとおりです

インデックス フィールドに基づいて新しい B+ ツリーが生成されることに注意してください。したがって、インデックスを追加するたびにテーブルのサイズが増加し、ディスクストレージスペースが占有されます。ただし、リーフ ノードに注意してください。非クラスター化インデックスのリーフ ノードは実際のデータではありません。リーフ ノードは依然としてインデックス ノードであり、インデックス フィールドの値と対応する主キー インデックス (クラスター化インデックス) を格納します。

次の文を実行すると

name='lisi' のテーブルから * を選択

構造図は以下のとおりです

上の図の赤い線から、検索は非クラスター化インデックス ツリーから開始され、その後クラスター化インデックスが見つかることがわかります。クラスター化インデックスに従って、クラスター化インデックスの B+ ツリー内の完全なデータを検索します。

それ

クラスター化インデックス ツリーをクエリしないのはなぜですか?

非クラスター化インデックス ツリーにはインデックス フィールドの値が格納されることに注意してください。このとき、次の文を実行すると

name='lisi' のテーブルから名前を選択します

構造図は以下のとおりです

上の図の赤い線で示されているように、非クラスター化インデックス ツリーで目的の値が見つかった場合、クラスター化インデックス ツリーはクエリされません。 「Select の正しい姿勢」でブロガーが言及したインデックス作成の問題を思い出してください。

select col from table where col = ? を実行し、col にインデックスがある場合、select * from table where col = ?! を実行する場合よりも効率が数倍速くなります。

上の写真を見ると、この文章をより深く理解できるはずです。

では、この時点で次のステートメントを実行すると、何が起こるでしょうか?

テーブル(birthday)にインデックスindex_birthdayを作成します。

構造図は以下のとおりです

インデックスをもう 1 つ追加すると、非クラスター化インデックス ツリーがもう 1 つ生成されます。そのため、インデックスをランダムに追加することはできないと多くの記事で述べられています。なぜなら、非クラスター化インデックス ツリーの数はインデックスの数と同じだからです。挿入操作を実行するときは、これらのツリーの変更を同時に維持する必要があります。したがって、インデックスが多すぎると、挿入のパフォーマンスが低下します。

要約する

そうは言っても、誰もがインデックスの原理を明確に理解する必要があります。詳細は厳密ではないかもしれませんが、R&D 担当者がこれを理解するには十分だと思います。結局のところ、私たちはプロの DBA ではありません。
皆さんが何か得られることを願っています！