【MySQL】インデックスを使いたい！

はじめに

SQLの検索効率をあげるために、上司から「インデックスのはりかたを考えてもいいね！」とアドバイスを受けたのですが、「インデックスって…なに？」ってなっちゃったので勉強しました。

（関係ないんですが、筆者はハンター×ハンターでゴンがピトーに「タスケネクチャ…って、なに？」ってなるシーンがすきです）

具体的なデータベースとは別に、検索用に最適化されたデータベースを生成するもの

※イメージ

検索用に最適化されたデータベースがどんなものか知るために、データ構造について紹介します。

読み方：ビーツリー
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/

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が判断した結果のこと

EXPLAIN SELECT カラム名1, カラム名2... FROM テーブル名 WHERE 条件...;

EXPLAINを利用しているときに気になったのが、「主キー」「外部キー」の存在。
- これらがどうなっているかというと、MySQLでは「主キー」「外部キー」に対してDBが自動でインデックスを作成してくれていた！
- インデックスなんて知らないんだけど！？って思ってたけど、意外にも既にお世話になってた人でした…すまん
「MUL」とは
- Multiple keyの略称
- PRIMARY KEYやUNIQUEが設定されていないインデックスは同じ値を取り得るので「Multiple（多様な）」key、というみたい
- よく外部キーの隣に書いてあるので、筆者は最近まで「外部キー」＝「MUL」だと勘違いしてました…つらい

実はデメリットもあるので、インデックスをはるのは必要最小限にするのが◎

複数のカラムを組み合わせた１つのインデックスのこと
順番が超大事で、以下の２つの例は全くちがうインデックスが作成される
- 例1）先に部署名のインデックスを作成し、後から社員番号のインデックスを作成する ALTER TABLE テーブル名 ADD INDEX インデックス名（部署名,社員番号）
- 例2）先に社員番号のインデックスを作成し、後から部署名のインデックスを作成する ALTER TABLE テーブル名 ADD INDEX インデックス名（社員番号,部署名）
複合インデックスが使えない場合がある
- 例１の場合
  - 使える時 SELECT * FROM 社員 WHERE 部署名 = "A" AND 社員番号 = "03"; SELECT * FROM 社員 WHERE 部署名 = "A";
  - 使えないとき SELECT * FROM 社員 WHERE 部署名 = "A";
- 複合インデックスの最初に指定したカラムがないと、インデックスは使えない！

今回調べたことをきっかけに、社内で先輩方がどのようにインデックスをはられているのかみたりすることが勉強になってよかったです。

ただ、普段知らなくても仕事ができてしまうものなので、忘れ去られていく可能性があるのがやばいなと思いました。（後からインデックスを不必要に追加してごちゃごちゃしてきちゃったりとか）

インデックスの管理の方法について、今後考えていきたいなと思います。