はじめに
SQLの検索効率をあげるために、上司から「インデックスのはりかたを考えてもいいね!」とアドバイスを受けたのですが、「インデックスって…なに?」ってなっちゃったので勉強しました。
(関係ないんですが、筆者はハンター×ハンターでゴンがピトーに「タスケネクチャ…って、なに?」ってなるシーンがすきです)
インデックスとは
具体的なデータベースとは別に、検索用に最適化されたデータベースを生成するもの
※イメージ
データ構造
検索用に最適化されたデータベースがどんなものか知るために、データ構造について紹介します。
B-tree構造
- 読み方:ビーツリー
- MongoDBのインデックスで採用されている構造
- 計算量 O(log₂n)
- n = 1024 のとき log₂1024 = 10
- 1024個データがあったら、最大計算量は10回
- cf)インデックスをはってない場合、フルテーブルスキャンを行うため1024個データがあれば最大計算量は1024回
- 各ノードは値を保持している
https://www.ei.fukui-nct.ac.jp/2021/01/27/btree-bplus-tree-hash-2020/
特徴
- データが増えても計算量はそんなに増えない
- 各ノードは値を保持しているので、データが見つかればそこで検索は終了する
B+tree構造
- 読み方:ビープラスツリー
- B-treeを改良したもの
- RDBでよく使われている構造
- MYSQLのストレージエンジンであるinnodb(インノディービー)でも採用されている
- 各ノードではなく、最下層のノード(リーフノード)でデータを保持している
- リーフノードはポインタで繋がっている
https://www.ei.fukui-nct.ac.jp/2021/01/27/btree-bplus-tree-hash-2020/
特徴
- 範囲検索に強い
インデックスの使い方
作成
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);
削除
ALTER TABLE テーブル名 DROP INDEX インデックス名;
検証
- EXPLAIN
- SQLの実行計画に関する情報を取得するためステートメント
- 実行計画とは、「どのインデックスを使ってクエリを処理するか」または「インデックスを使わずにテーブルスキャンでどのようにクエリを処理するか」をMySQLが判断した結果のこと
- SQLの実行計画に関する情報を取得するためステートメント
EXPLAIN SELECT カラム名1, カラム名2... FROM テーブル名 WHERE 条件...;
余談
- EXPLAINを利用しているときに気になったのが、「主キー」「外部キー」の存在。
- これらがどうなっているかというと、MySQLでは「主キー」「外部キー」に対してDBが自動でインデックスを作成してくれていた!
- インデックスなんて知らないんだけど!?って思ってたけど、意外にも既にお世話になってた人でした…すまん
- 「MUL」とは
- Multiple keyの略称
- PRIMARY KEYやUNIQUEが設定されていないインデックスは同じ値を取り得るので「Multiple(多様な)」key、というみたい
- よく外部キーの隣に書いてあるので、筆者は最近まで「外部キー」=「MUL」だと勘違いしてました…つらい
インデックスのデメリット
実はデメリットもあるので、インデックスをはるのは必要最小限にするのが◎
- 索引用のデータが新たに生成されるため、データ容量を圧迫する
- テーブルデータが更新される際に、索引用のデータも更新が必要になるため、データの追加・更新・削除時の処理速度が低下する
インデックスのはり方
- 原則はカーディナリティ(多重度、選択度)が高いものに貼る
- ダブリが少ないデータが◎
複合インデックス
- 複数のカラムを組み合わせた1つのインデックスのこと
- 順番が超大事で、以下の2つの例は全くちがうインデックスが作成される
- 例1)先に部署名のインデックスを作成し、後から社員番号のインデックスを作成する
ALTER TABLE テーブル名 ADD INDEX インデックス名(部署名,社員番号)
- 例2)先に社員番号のインデックスを作成し、後から部署名のインデックスを作成する
ALTER TABLE テーブル名 ADD INDEX インデックス名(社員番号,部署名)
- 例1)先に部署名のインデックスを作成し、後から社員番号のインデックスを作成する
- 複合インデックスが使えない場合がある
- 例1の場合
- 使える時
SELECT * FROM 社員 WHERE 部署名 = "A" AND 社員番号 = "03";
SELECT * FROM 社員 WHERE 部署名 = "A";
- 使えないとき
SELECT * FROM 社員 WHERE 部署名 = "A";
- 使える時
- 複合インデックスの最初に指定したカラムがないと、インデックスは使えない!
- 例1の場合
終わりに
今回調べたことをきっかけに、社内で先輩方がどのようにインデックスをはられているのかみたりすることが勉強になってよかったです。
ただ、普段知らなくても仕事ができてしまうものなので、忘れ去られていく可能性があるのがやばいなと思いました。(後からインデックスを不必要に追加してごちゃごちゃしてきちゃったりとか)
インデックスの管理の方法について、今後考えていきたいなと思います。
コメント