欲しいデータを取得するくらいにはSQL書けるし、システム要件を満たすくらいにはテーブル設計は出来る、そんな僕が中級者を脱するために勉強している内容を備忘録的に書き綴ります。
予約語は大文字 その他は小文字で記述しています。
あー、インデックスね、はいはい。作ると参照が速くなるやつでしょ？
そのくらいの知識でしたが、INDEXを適切に運用する上で原理など理解していないと、意味の無いINDEXを作ってしまう事があるので勉強しました。
INDEXとは
今回は多くのRDBMSでサポートされているB-TreeINDEXについて解説します。
B-Treeは以下のような形式でデータを保持しています。
ヘッダブロックでは大まかな値の範囲を保持しており、ブランチブロックではさらに細かい範囲を保持
リーフブロックでは実際の値と行への物理的な位置を保持しています。
INDEXが作成されている事で並び替えが速くなるのは、MySQLがこのINDEX順にレコードを表示すれば良いだけなので、
わざわざクイックソートで並び替えを行う事が無くなるため処理が高速になります。
インデックスを作成しすぎると追加更新が遅くなると言われるのは、このB-Treeを更新する処理が走るからです。
INDEXの参照 追加 削除
まずは基本的なコマンドから
-- 参照
mysql> SHOW INDEX FROM table_name;
-- 追加
mysql> ALTER TABLE table_name ADD INDEX index_name(column_name);
-- 追加 複合インデックス
mysql> ALTER TABLE table_name ADD INDEX index_name(column1_name, column2_name);
-- 削除
mysql> ALTER TABLE table_name DROP INDEX index_name;
INDEXが利用されているか調査
前回書いた調査編のSQL分析の項目で使用したEXPLAINコマンドを使って調査する。
INDEXが利用されているか見るべき箇所は「possible_keys」「key」「Extra」です。
possible_keys:SQLを実行する上で利用可能なINDEXとして候補にあがったINDEX
key:実際に選択されたINDEX
Extra:追加情報 Using filesortやUsing temporaryが出たら赤信号
例に漏れず漢のコンピュータ道で詳細に解説されてるので割愛します。
多謝多謝
INDEXが利用されない
WHERE句とORDER BY句が存在するSQL
col1とcol2に別々にINDEXが作成されおり、以下のSQLを発行してもINDEXは利用されない。
mysql > EXPLAIN SELECT * FROM sample WHERE col1 > 100 ORDER BY col2;
この場合は複合INDEXを作成する。
mysql > ALTER TABLE sample ADD INDEX index_name(col1, col2);
複合INDEXで気をつけなければならないのは、一番左にあるINDEX(この例であればcol1)がWHERE句で指定しないとソート処理でINDEXが利用されない。
mysql > EXPLAIN SELECT * FROM sample WHERE col2 > 100 ORDER BY col1;
複合INDEXは下記の形式で作成される。
リーフブロックごとにはきちんと整列された状態だが、全体のリーフブロックで見るとバラバラで保持されているため、結果として全てのレコードを参照しなければならなくなるため、複合INDEXの逆順に条件と並び替えを指定するとINDEXが使用されなくなってしまう。
テーブルの30%を超えるレコードにアクセスがある場合
col1に単一のINDEXが作成されている状態で以下のSQLをEXPLAINで調査したが、まるで使ってなかった。
mysql > EXPLAIN SELECT * FROM sample ORDER BY col1;
MySQLの公式リファレンスに書いてあった。
MySQL では利用可能な場合でもインデックスが使用されない場合があることに注意してください。この一例として、インデックスの使用によって、MySQL がテーブルの 30% を超えるレコードにアクセスする必要が生じる場合が挙げられます（この場合は、必要なシークが大幅に減少するため、テーブルスキャンのほうが高速になる可能性が高くなります）。 ただしこのクエリに、レコードの一部のみを取り出す LIMIT が使用されている場合、結果で返される少数のレコードを迅速に検索できるため、MySQL はインデックスを使用します。
つまり、100万レコードが存在するテーブルに約30万レコード以上がひっかかるようなSQLではINDEXを使わない方が速く処理できるため意図的に使わないとの事。
他にもあるけど疲れたのでここまで。
随時追記します。
参考にさせていただいたサイト
MySQL公式リファレンス MySQL でのインデックスの使用
漢のコンピュータ道 オトコのソートテクニック2008
TsumikiWeb インデックスの基礎知識
久保清隆の成長ノート @ライブレボリューション MySQLパフォーマンスチューニングのためのインデックスの基礎知識
多謝多謝