データベースでインデックスを使用する方法【データベースの効率的なプログラミング-学習ノート7章】

多くのデータベースでは、Bツリーの構造を使用してインデックスを保存します.
Bツリー、最上位ノード:ルートノード最下層ノード:リーフノードの両者の間のノード:中間ノードBツリー、顕著な特徴:ルートノードから、各リーフノードまでの距離は等しい.このように,任意の値を取得すると,同じ数のノードが通過する.

CREATE INDEX idx_lname_pinyin ON employee(lname_pinyin);
SHOW INDEX FROM employee\G
DROP INDEX idx_lname_pinyin ON employee;

プライマリ・キーの定義に伴って作成された特別なインデックス.
Clustered Index)
1つのテーブルで、1つの叢生インデックスしかありません.
通常のインデックスは、リーフノードに実際のテーブルへのポインタを保存します.
また,群生インデックスは,葉ノードに保存されているのが実際のデータである.
インデックスが生い茂り、
インデックスを保存するために専用のハードディスク(HDD)領域を使用する必要がなく、リソースを節約
インデックスをチェックしてから実際のテーブルにアクセスする必要がなくなり、効率が向上
ジャングルインデックスを作成する場合、テーブル内のデータをソートする必要があるため、データ挿入、更新、削除などの操作を実行する場合、通常のインデックスより遅い.
複合インデックス

CREATE INDEX idx_pinyin ON employee( lname_pinyin, fname_pinyin);

SHOW INDEX FROM employee\G

一意性インデックス
UNIQUEキーを使用して、一意性インデックスと呼ばれる重複しないインデックスを作成します.特定のカラムに一意性インデックスを作成することは、カラムに一意性制約を追加することに相当します.一意性インデックスを作成するとき、オブジェクト列に重複データが含まれている場合は、作成に失敗し、エラーが発生します.作成に成功した後、重複データを挿入すると、挿入に失敗し、エラーが発生します.ユニークなインデックスを作成するために複数のカラムを指定します.これらのカラムの組合せデータが重複しない限り、正常に作成できます.

CREATE UNIQUE INDEX idx_fname ON employee(fname);
CREATE UNIQUE INDEX idx_lname ON employee(lname);
CREATE UNIQUE INDEX idx_fullname ON employee(lname, fname);

EXPLAIN SELECT * FROM employee WHERE lname_pinyin='wang'\G

CREATE INDEX idx_lname_pinyin ON employee(lname_pinyin);
EXPLAIN SELECT * FROM employee WHERE lname_pinyin='wang'\G

EXPLAINコマンドの説明:P 100テーブル
インデックスが作成された後、遍歴の回数はインデックスが作成される前とあまり変わりません.
説明:インデックスを作成する場合、選択したカラム名が不合理であり、適切なカラムを選択してインデックスを再構築する必要があります.これはインデックスの優劣を分析する方法です.
インデックスが追加されても、検索のたびにカラムインデックスが使用される保証はありません.
SQL検索文が適切に記述されていない場合、インデックスは使用できません.
1、LIKE演算子、ファジイ検索を行う場合、前方一致の検索時のみインデックスを使用できます.
以下のように、インデックスは使用されません.

SELECT * FROM employee WHERE lname_pinyin LIKE '%w%';
SELECT * FROM employee WHERE lname_pinyin LIKE '%w';

2、IS NOT NULL、<>を使用した場合、インデックスも使用しません.次のようになります.

SELECT * FROM employee WHERE lname_pinyin IS NOT NULL;
SELECT * FROM employee WHERE lname_pinyin <> 'wang';

3、列に対して演算または関数を使用した場合、インデックスは使用されません.以下のようにします.

SELECT * FROM employee WHERE YEAR(birth) = '1980'; --      
SELECT * FROM employee WHERE birth >= '1980-01-01' AND birth <= '1980-12-31'; --     

4、複合索引の第一列は、WHERE条件文に含まれていない.

CREATE INDEX idx_pinyin ON employee(lname_pinyin, fname_pinyin);
SELECT * FROM employee WHERE lname_pinyin = 'wang' AND fname_pinyin = 'xiao'; --    
SELECT * FROM employee WHERE lname_pinyin = 'wang'; --    

SELECT * FROM employee WHERE fname_pinyin = 'xiao'; --       
SELECT * FROM employee WHERE lname_pinyin = 'wang' OR fname_pinyin = 'xiao'; --       

実際の開発過程では、EXPLAINコマンドを積極的に使用し、インデックスの使用状況を確認し、直ちに修正する必要があります.