MySQL のインデックス構造について話しましょう

この記事では、mysql に関する関連知識を提供します。MySQL のインデックスの公式定義では、インデックス (Index) は、MySQL が効率的にデータを取得するのに役立つデータ構造です。インデックスの本質を理解することができます。はデータ構造です。見てみましょう。皆さんの参考になれば幸いです。

推奨学習: mysql ビデオ チュートリアル

はじめに

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

一般に、インデックス自体も非常に大きく、すべてをメモリに保存することはできないため、インデックスはインデックス ファイルの形式でディスクに保存されることがよくあります。

利点:

1. 大学図書館で書誌索引を構築するのと同様に、データ検索の効率が向上し、データベースの IO コストが削減されます。

2. インデックス列を通じてデータを並べ替えて、データ並べ替えのコストを削減し、CPU 消費量を削減します。

欠点:

1. インデックスによりクエリの速度は大幅に向上しますが、テーブルの INSERT、UPDATE、DELETE などのテーブルの更新速度も低下します。テーブルを更新するとき、MySQL はデータを保存するだけでなく、インデックス ファイルも保存する必要があるためです。インデックス列を追加するフィールドが更新されるたびに、更新によるキー値変更後のインデックス情報が調整されます。

2. 実際、インデックスもテーブルです。このテーブルは主キーとインデックス フィールドを保存し、エンティティ テーブルのレコードをポイントするため、インデックス列もスペースを占有します。

インデックスの例: ( ツリー構造をインデックスとして使用)

左側はデータ テーブルで、合計 2 つの列と 7 つのレコードがあり、一番左はデータ レコードの物理アドレスです。

Col2 の検索を高速化するために、右に示すようにバイナリ検索ツリーを維持できます。各ノードにはインデックス キー値と、インデックス キー値へのポインタが含まれています。対応するデータ レコードの物理アドレス ポインタ。二分検索を使用して、一定の複雑さ内で対応するデータを取得できるため、条件を満たすレコードを迅速に取得できます。

インデックス構造 (ツリー)

インデックスを使用してデータベース テーブルのクエリ速度を高速化するにはどうすればよいですか?説明の便宜上、データベース テーブルには次の 2 つのクエリ要件のみが含まれるように制限します:

1. select* from user where id=1234;

2. select *from user where id>1234 and id

ハッシュ テーブルの代わりにツリーを使用する理由

#Ha 値によるギリシャ語テーブル クエリのパフォーマンスは非常に優れており、時間計算量は O(1) ですが、間隔に応じたデータの高速検索をサポートできないため、要件を満たすことができません。同様に、平衡二分探索ツリーのクエリのパフォーマンスは非常に高く、時間計算量は O(logn) であり、ツリーの順序どおりの走査により順序付けされたデータ シーケンスを出力できますが、クエリのニーズを満たすことはできません。間隔に従ってデータをすばやく検索します。

間隔に応じたデータの高速検索をサポートするために、二分探索木を変換し、二分探索木の葉ノードをリンク リストで連結します。特定の間隔でデータを検索したい場合は、間隔を使用するだけです開始値はツリー内で検索されます。順序付きリンク リストでノードを見つけた後、このノードから開始し、順序付きリンク リスト内のノード データ値が大きくなるまで順序付きリンク リストに沿って移動します。インターバル終了値を超えるまで。

また、ツリー上の多くの操作の時間の複雑さはツリーの高さに比例するため、ツリーの高さを低くするとディスク IO 操作が削減されます。したがって、インデックスを m 分木 (m>2) に構築します。詳細については、次の記事を参照してください。

BTree インデックス

B ツリーを紹介する前に、まず B ツリーについて理解しましょう。

1. 初期化の概要

B ツリー、水色のブロックはディスク ブロックと呼ばれ、各ディスク ブロックには複数のデータ項目が含まれていることがわかります。 （濃い青で表示）およびポインタ（黄色で表示）たとえば、ディスク ブロック 1 にはデータ項目 17 と 35 が含まれ、ポインタ P1、P2、および P3 が含まれています。 P1 は 17 未満のディスク ブロックを表し、P2 は 17 ～ 35 のディスク ブロックを表し、P3 は 35 より大きいディスク ブロックを表します。

注:

実データはリーフ ノード、つまり 3、5、9、10、13、15、28、29、36、60、75、79、90、99 にのみ存在します。 。 (複数のデータで構成されるデータ間隔です: 3~5,...,90~99)

非リーフ ノードには実際のデータは保存されませんが、リーフ ノードをガイドするデータ項目のみが保存されます。 17、35 などの検索方向は、実際にはデータシートに存在しません。

2. 検索プロセス

データ項目 29 を検索する場合は、まずディスク ブロック 1 をディスクからメモリにロードします。このとき、IO が発生します。メモリ内で二分探索を使用して、29 が 17 から 35 の間にあることを確認します。 、ディスク ブロック 1 の P2 をロックします。ポインタ、メモリ時間は (ディスク IO と比較して) 非常に短いため無視できます。ディスク ブロック 3 は、ディスク ブロックの P2 ポインタのディスク アドレスを介してディスクからメモリにロードされます1. 2 番目の IO が発生します (26 と 30 の間の 29)。その間、ディスク ブロック 3 の P2 ポインタがロックされ、ディスク ブロック 8 がポインタを介してメモリにロードされます。3 番目の IO が発生します。同時に、メモリ内でバイナリ検索が実行されて 29 が見つかり、クエリが終了します。合計 3 回の IO です。

B ツリー インデックス

B ツリーは B ツリーに似ており、B ツリーは B ツリーの改良版です。つまり、m フォーク検索ツリーと順序付きリンク リストによって構築されたツリーは B ツリーであり、これが格納されるツリー インデックスです。図: B ツリーと B ツリーの主な機能 違いは次のとおりです:

1. B ツリーの葉ノードは、リンク リストを使用して直列に接続されます。特定の間隔でデータを検索するには、間隔の開始値を使用してツリー内を検索するだけです。順序付きリンク リストでノードを見つけたら、このノードから開始して、順序付きリンク リストに沿って、順序付けされたリンク リストのノード データ値が間隔終了値よりも大きいです。

2. B ツリー内のノードは実際のデータを保存せず、インデックス作成のみに使用されます。 B ツリーはリーフ ノードを介して直接データを取得し、B ツリーの各リーフ ノードにはデータ行のキー値とアドレス情報が格納されます。特定のリーフ ノードがクエリされると、実際のデータ情報はリーフのアドレスを通じて見つかります。ノード。

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

クラスター化インデックスは、別個のインデックス タイプではなく、データの保存方法です。 「クラスター化」という用語は、データ行と隣接するキーと値のクラスターを一緒に保存することを指します。

クラスター化インデックスの利点:

クラスター化インデックスの配置順序によれば、クエリで特定の範囲のデータが表示される場合、データは密接に接続されているため、データベースはデータを抽出する必要がありません。複数のデータはデータをブロックするため、多くの IO 操作が節約されます。

クラスター化インデックスの制限:

1. mysql データベースの場合、現在、innodb データ エンジンのみがクラスター化インデックスをサポートしており、Myisam はクラスター化インデックスをサポートしていません。

2. データの物理ストレージのソート方法は 1 つだけであるため、各 Mysql テーブルにはクラスタード インデックスが 1 つだけ存在します。通常、これはテーブルの主キーです。

3. クラスター化インデックスのクラスター化特性を最大限に活用するには、innodb テーブルの主キー列で順序付けされた連続 ID を使用するようにする必要があり、順序付けされていない ID を使用することはお勧めできません。 uuidとして。

以下に示すように、ディスク上のデータ行の配置がインデックスの並べ替えと一致しているため、左側のインデックスはクラスター化インデックスです。

#インデックス分類

単一値インデックス

つまり、インデックスには 1 つの列のみが含まれ、テーブルには複数の単一値インデックスを含めることができます。 -column Indexes

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
 
单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
 
删除索引：
DROP INDEX idx_customer_name  on customer;

一意のインデックス

インデックス列の値は一意である必要がありますが、NULL 値も許可されます

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
  
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
删除索引：
DROP INDEX idx_customer_no on customer ;

主キー インデックス

主キーを設定した後、データベースは自動的にインデックスを作成します。innodb はクラスター化インデックスです

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (
id INT(10) UNSIGNED   ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
 单独建主键索引：
ALTER TABLE customer 
 add PRIMARY KEY customer(customer_no);  
 
删除建主键索引：
ALTER TABLE customer 
 drop PRIMARY KEY ;  
 
修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

複合インデックス

つまり、インデックスには複数の列が含まれます

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
 
单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
删除索引：
DROP INDEX idx_no_name  on customer ;

パフォーマンス分析

##インデックス作成シナリオ

インデックスの作成が必要になるケース

##1. 主キーにより一意のインデックスが自動的に作成されます

2. クエリ条件として頻繁に使用されるフィールドはインデックスを作成する必要があります3、クエリ内の他のテーブルに関連付けられたフィールド、インデックスを確立するための外部キー関係

4. シングルキー/結合インデックスの選択の問題、結合インデックスの方がコスト効率が高くなります。

5. クエリ内の並べ替えフィールド、並べ替えフィールドがインデックス経由でアクセスされる場合、並べ替え速度が大幅に向上します

6. クエリ内の統計またはグループ化フィールド

どのような状況でインデックスを作成すべきでないのか

1. テーブル レコードが少なすぎる

2. テーブルの理由または頻繁に追加、削除、または変更されるフィールド: クエリの速度は向上しますが、同時にテーブルの INSERT、UPDATE、DELETE などのテーブルの更新速度が低下します。テーブルを更新するとき、MySQL はデータを保存するだけでなく、インデックス ファイルも保存する必要があるためです。

3. Where 条件で使用されていないフィールドにはインデックスは作成されません

4. フィルタリングが適切でない場合、インデックスは作成されません。インデックス構築に最適です。

推奨学習:

mysql ビデオ チュートリアル

以上がMySQL のインデックス構造について話しましょうの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。