MySQL のインデックス構造について話しましょう-mysql チュートリアル-php.cn

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

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 サイトの他の関連記事を参照してください。