Mysqlデータベースインデックスを初心者向けに解説-mysql チュートリアル-php.cn

中心となる概念

主キーインデックス / 副キーインデックス
クラスター化インデックス / 非クラスター化インデックス
テーブルルックアップ / インデックスカバレッジ
インデックスプッシュダウン
複合インデックス / 左端のプレフィックスマッチング
プレフィックスインデックス
説明

1. 【インデックス定義】

1.インデックス定義

データ自体に加えて、データベースシステムは特定の検索アルゴリズムを満たすデータ構造も維持します。これらの構造は、特定の方法でデータを参照 (ポイント)し、高度な検索アルゴリズムを実装できるようにします。 これらのデータ構造はインデックスです。

2.インデックスのデータ構造

B ツリー / B+ ツリー (MySQL の InnoDB エンジンはデフォルトのインデックス構造として B+ ツリーを使用します)
ハッシュテーブル
ソートされた配列

3. B ツリーではなく B+ ツリーを選択する理由

B ツリー構造: レコードはツリーノードに格納されます。

Mysql Database Index Explained for Beginners

B+ ツリー構造: レコードはツリーのリーフノードにのみ保存されます。

Mysql Database Index Explained for Beginners

データサイズが 1KB、インデックスサイズが 16B、データベースがディスクデータページを使用し、デフォルトのディスクページサイズが 16K であると仮定すると、同じ 3 つの I/O 操作で次の結果が得られます。

B ツリーは 16*16*16=4096 レコードをフェッチできます。
B+ ツリーは 1000*1000*1000=10 億 レコードをフェッチできます。

2. 【インデックスの種類】

1.主キーインデックスと副キーインデックス

主キーインデックス: インデックスのリーフノードはデータ行です。
セカンダリインデックス: インデックスのリーフノードは、KEY フィールドと主キーインデックスです。したがって、セカンダリインデックスを通じてクエリを実行すると、まず主キー値が検索され、次に InnoDB は主キーインデックスを通じて対応するデータブロックを検索します。
InnoDB では、プライマリインデックスファイルはクラスター化インデックスと呼ばれるデータ行を直接保存し、セカンダリインデックスは主キー参照を指します。
MyISAM では、プライマリインデックスとセカンダリインデックスの両方が物理行 (ディスクの位置) を指します。

Mysql Database Index Explained for Beginners

2.クラスター化インデックスと非クラスター化インデックス

クラスター化インデックスは、ディスク上の実際のデータを再編成し、1 つ以上の指定された列値で並べ替えます。データの格納順序とインデックスの順序が一致しているのが特徴です。一般に、主キーはデフォルトでクラスター化インデックスを作成し、テーブルでは 1 つのクラスター化インデックスのみが許可されます (理由: データは 1 つの順序でのみ保存できます)。画像に示すように、InnoDB のプライマリインデックスとセカンダリインデックスはクラスター化インデックスです。
クラスター化インデックスのリーフノードがデータレコードであるのに対し、非クラスター化インデックスのリーフノードはデータレコードへのポインターです。最大の違いは、データレコードの順序がインデックスの順序と一致しないことです。

3.クラスター化インデックスの利点と欠点

利点: 主キーによってエントリをクエリする場合、テーブル検索を実行する必要はありません (データは主キーノードの下にあります)。
欠点: 不規則なデータ挿入により、ページ分割が頻繁に発生する可能性があります。

3. [拡張インデックスの概念]

1.テーブルルックアップ

テーブルルックアップの概念には、主キーインデックスクエリと非主キーインデックスクエリの違いが関係します。

クエリが select * from T where ID=500 の場合、主キークエリは ID ツリーを検索するだけで済みます。
クエリが select * from T where k=5 の場合、非主キーインデックスクエリは、最初に k インデックスツリーを検索して ID 値 500 を取得し、次に ID インデックスツリーを再度検索する必要があります。
非主キーインデックスから主キーインデックスに戻るプロセスは、テーブルルックアップと呼ばれます。

非主キーインデックスに基づくクエリでは、追加のインデックスツリーをスキャンする必要があります。 したがって、アプリケーションでは主キークエリの使用を試みる必要があります。記憶領域の観点から見ると、非主キーインデックスツリーのリーフノードには主キーの値が格納されるため、主キーフィールドをできるだけ短くすることをお勧めします。この方法では、非主キーインデックスツリーのリーフノードが小さくなり、非主キーインデックスが占有するスペースが少なくなります。一般に、非主キーインデックスが占有する領域を最小限に抑えるために、自動インクリメント主キーを作成することをお勧めします。

2.インデックスカバレッジ

WHERE 句の条件が非主キーインデックスの場合、クエリはまず非主キーインデックスを通じて主キーインデックスを見つけます (主キーは非主キーインデックスのリーフノードにあります)。キーインデックス検索ツリー) を参照し、主キーインデックスを通じてクエリのコンテンツを見つけます。このプロセスで、主キーインデックスツリーに戻ることをテーブルルックアップと呼びます。
ただし、クエリの内容が主キー値である場合、テーブル検索を行わずにクエリ結果を直接提供できます。言い換えれば、非主キーインデックスは、このクエリのクエリ要件をすでに「カバー」しているため、カバーインデックスと呼ばれます。
カバリングインデックスは、プライマリインデックスへのテーブルルックアップを行わずに、補助インデックスからクエリ結果を直接取得できます。これにより、検索数が削減されます (補助インデックスツリーからクラスタードインデックスツリーに移動する必要がありません)。 IO 操作 (補助インデックスツリーはディスクから一度により多くのノードをロードできます) により、パフォーマンスが向上します。

3.複合インデックス

複合インデックスとは、テーブルの複数の列にインデックスを付けることを指します。

シナリオ 1:

複合インデックス (a, b) は a、b でソートされます (最初に a でソートされ、a が同じ場合は次に b でソートされます)。したがって、次のステートメントは複合インデックスを直接使用して結果を取得できます (実際には、左端の接頭辞の原則が使用されます):

select … from xxx where a=xxx;
select … from xxx where a=xxx order by b;

次のステートメントでは複合クエリを使用できません:

select … from xxx where b=xxx;

シナリオ 2:

複合インデックス (a、b、c) の場合、次のステートメントは複合インデックスを通じて結果を直接取得できます。

select … from xxx where a=xxx order by b;
select … from xxx where a=xxx and b=xxx order by c;

次のステートメントは複合インデックスを使用できず、ファイルソート操作が必要です:

select … from xxx where a=xxx order by c;

概要:

例として複合インデックス (a、b、c) を使用すると、そのようなインデックスを作成することは、インデックス a、ab、abc を作成することと同じです。 3 つのインデックスを 1 つのインデックスで置き換えることは確かに有益です。インデックスが追加されるたびに、書き込み操作のオーバーヘッドとディスク領域の使用量が増加します。

4.左端のプレフィックス原則

上記の複合インデックスの例から、一番左のプレフィックスの原則を理解できます。
インデックスの完全な定義だけでなく、左端のプレフィックスを満たす限り、検索を高速化するために使用できます。この左端のプレフィックスは、複合インデックスの左端の N フィールド、または文字列インデックスの左端の M 文字です。インデックスの「左端のプレフィックス」原則を使用してレコードを検索し、冗長なインデックス定義を回避します。
したがって、左端のプレフィックスの原則に基づいて、複合インデックスを定義するときはインデックス内のフィールドの順序を考慮することが重要です。評価基準はインデックスの再利用性です。たとえば、(a, b) にすでにインデックスがある場合、通常、a に別のインデックスを作成する必要はありません。

5.インデックスプッシュダウン

MySQL 5.6 では、インデックスプッシュダウン最適化が導入されました。これにより、インデックストラバーサル中にインデックスに含まれるフィールドに基づいて条件を満たさないレコードをフィルタリングして、テーブルルックアップの数を削減できます。

テーブルの作成

CREATE TABLE `test` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
   `age` int(11) NOT NULL DEFAULT '0',
   `name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `idx_name_age` (`name`,`age`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ログイン後にコピー

SELECT * from user where name like 'Chen%' 左端のプレフィックス原則、idx_name_age インデックス
SELECT * 名前が「Chen%」、年齢が 20 のユーザーから
- バージョン 5.6 より前では、まず名前インデックスに基づいて 2 つのレコードを照合し (この時点では age=20 条件を無視します)、対応する 2 つの ID を見つけてテーブル検索を実行し、次に age=20 に基づいてフィルタリングします。
- バージョン 5.6 以降、インデックスプッシュダウンが導入されました。名前に基づいて 2 つのレコードを照合した後、テーブル検索を実行する前に age=20 条件を無視せず、テーブル検索の前に年齢に基づいてフィルタリングします。このインデックスプッシュダウンにより、テーブルルックアップの数が減り、クエリのパフォーマンスが向上します。

6.プレフィックスインデックス

インデックスが長い文字シーケンスである場合、大量のメモリを消費し、速度が遅くなる可能性があります。この場合、プレフィックスインデックスを使用できます。値全体にインデックスを付ける代わりに、最初の数文字にインデックスを付けてスペースを節約し、良好なパフォーマンスを実現します。 プレフィックスインデックスはインデックスの最初の数文字を使用します。ただし、インデックスの重複率を減らすには、プレフィックスインデックスの一意性を評価する必要があります。

まず、現在の文字列フィールドの一意性の比率を計算します: select 1.0*count(distinct name)/count(*) from test
次に、さまざまなプレフィックスの一意性の比率を計算します。
- select 1.0*count(distinct left(name,1))/count(*) from test 名前の最初の文字をプレフィックスインデックスとして使用します
- 名前の最初の 2 文字をプレフィックスインデックスとしてテストから 1.0*count(distinct left(name,2))/count(*) を選択します
- ...
left(str, n) が大幅に増加しない場合は、プレフィックスインデックスのカットオフ値として n を選択します。
インデックス変更テーブルの作成 test add key(name(n));

4. 【インデックスの見方】

インデックスを追加した後、それらをどのように表示できますか?または、ステートメントの実行が遅い場合、どのようにトラブルシューティングすればよいですか?

Explain は、インデックスが有効かどうかを確認するためによく使用されます。

遅いクエリのログを取得したら、どのステートメントが遅いかを観察します。ステートメントの前に Explain を追加し、再度実行します。 Explain はクエリにフラグを設定し、ステートメントを実行する代わりに実行計画の各ステップに関する情報を返します。 実行計画と実行の各部分を示す 1 行以上の情報を返します。注文します。