MySQL のインデックス構造について話しましょう
- WBOY転載
- 2022-08-31 17:49:262099ブラウズ
推奨学習: 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) であり、ツリーの順序どおりの走査により順序付けされたデータ シーケンスを出力できますが、クエリのニーズを満たすことはできません。間隔に従ってデータをすばやく検索します。 間隔に応じたデータの高速検索をサポートするために、二分探索木を変換し、二分探索木の葉ノードをリンク リストで連結します。特定の間隔でデータを検索したい場合は、間隔を使用するだけです開始値はツリー内で検索されます。順序付きリンク リストでノードを見つけた後、このノードから開始し、順序付きリンク リスト内のノード データ値が大きくなるまで順序付きリンク リストに沿って移動します。インターバル終了値を超えるまで。
BTree インデックス
B ツリーを紹介する前に、まず B ツリーについて理解しましょう。
データ項目 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 ツリーの葉ノードは、リンク リストを使用して直列に接続されます。特定の間隔でデータを検索するには、間隔の開始値を使用してツリー内を検索するだけです。順序付きリンク リストでノードを見つけたら、このノードから開始して、順序付きリンク リストに沿って、順序付けされたリンク リストのノード データ値が間隔終了値よりも大きいです。 
クラスター化インデックスと非クラスター化インデックス
クラスター化インデックスは、別個のインデックス タイプではなく、データの保存方法です。 「クラスター化」という用語は、データ行と隣接するキーと値のクラスターを一緒に保存することを指します。クラスター化インデックスの利点:
クラスター化インデックスの配置順序によれば、クエリで特定の範囲のデータが表示される場合、データは密接に接続されているため、データベースはデータを抽出する必要がありません。複数のデータはデータをブロックするため、多くの 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 サイトの他の関連記事を参照してください。