インデックスについて改めて調べてみた

インデックスとは

SQLのデータ検索を行うときに用いる索引のようなものをインデックスと言います。適切にインデックスを貼ると、データの取得までにかかる時間が早くなります。

なぜ早くなるのか

インデックスを貼ると、効率的な検索ができるデータ構造（インデックス対象のカラムがどのレコードと対応するかを示すポインタ）を作るためです。

B-treeインデックス

有名なインデックスだとB-treeインデックスがあります。
ルートノード、ブランチノード、リーフノードで構成されており、ルートノードから順に辿っていって、リーフノードの実データのポインタから実データへアクセスします。
テーブルをフルスキャンする必要が無くなるため、効率的にデータを検索することができます。

事前準備

まず検証用にデータを作成します。

CREATE TABLE USERS (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  age INT,
  created_at DATETIME
);

DELIMETERを使って10万件のuserデータをinsertします。

-- 10万件のデータを挿入するスクリプト
DELIMITER //
CREATE PROCEDURE insert_dummy_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 100000 DO
    INSERT INTO users (name, email, age, created_at)
    VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100), NOW());
    SET i = i + 1;
  END WHILE;
END;
//
DELIMITER ;

CALL insert_dummy_data();

実行計画を取ってみる

それでは実行計画を取ってみます。インデックスが貼られているカラムと貼られていないカラムで絞り込みをしたクエリの実行計画を取ってどのようにデータが取得されるのかを見てみます。

インデックスが貼られていないカラムで絞り込み

-- インデックスが貼られていないカラムでWHEREの絞り込み
SELECT * FROM users WHERE name = "User0";

実行計画を見るとNameの列でusers(ALL)とあり、Rowsの列が99701となっています。これはテーブルをフルスキャンで99701行を処理したことを意味しています。

インデックスが貼られているカラムで絞り込み

-- インデックスが貼られているカラムで絞り込み
SELECT * FROM users WHERE id = 1;

一方でインデックスが貼られている主キーのidで絞り込みをするとNameの列はusers(const)でRowsが1となっています。

検索結果がほぼ確実に1行のみになる場合はconstになります。ちなみにconstはデータがどのようにアクセスされるかを表していて、constは非常に早いです。
詳しくはこちらをご覧ください。

今回だとusersテーブルのidは主キーなのでユニークインデックスが貼られます。そのため一意の値となりデータが即座に特定できるのでこの実行計画はconstになるというわけですね。

WHERE句でインデックスが効かないことがある

インデックスを貼っているカラムで絞り込むと基本的にパフォーマンスは向上しますが、インデックスが効果的に機能しないケースがあります。

genderカラムを追加し、レコードの中身にmaleを入れてみます。

ALTER TABLE users ADD COLUMN gender VARCHAR(30) DEFAULT 'male';

genderカラムにインデックスを貼ります。

ALTER TABLE users ADD INDEX gender_index(gender);

ここで以下のSQLの実行計画を取ってみます

SELECT * FROM users WHERE gender = 'male';

データのアクセス方法はrefとなっています。
これはカーディナリティが関係しているからです。

カーディナリティとはデータパターンの豊富さを表していて、今回のgenderカラムのデータはmaleのみなのでカーディナリティは非常に低いです。

なのでこのようなカーディナリティの低いカラムにはインデックスは不要と考えた方がいいでしょう。

どのようなケースでインデックスが機能しない？

先ほどのWHERE句での絞り込みのようにインデックスを貼っていても機能せず、パフォーマンスが向上しないこともあります。

先ほどの例の他にも

• 検索結果がテーブルの内容の大半を占める
• 取得の際に関数や計算を使用する

などがあります。

検索結果がテーブルの内容の大半を占める

これは出力結果が多いとテーブルフルスキャンになってしまうため、インデックスが有効に使われません。またレコード件数が少ないとかえってフルスキャンの方がパフォーマンスが良かったりします。

取得の際に関数や計算を使用する

文字通り取得する際にインデックスが使えないため、パフォーマンスが悪くなってしまいます。計算例えば商品を登録しているテーブルがあったとして登録されている値段の5%オフしたものが100円以下の商品を探したいとしましょう。
そうすると以下のようなSQLを実行することになります。

SELECT * FROM product WHERE price * 0.95 <= 100;

このSQLではpriceを5％引いた値段を比較するため、例えカラムに対してインデックスを貼られていても全てのレコードの値段を見て計算し、比較する必要があります。そのためテーブルフルスキャンが発生してパフォーマンスが落ちてしまうというわけです。

インデックスを貼りすぎるのh良くない

インデックスは適切に貼ることでデータ取得のパフォーマンスを大きく向上することができます。ただ一方で闇雲に貼りすぎることのデメリットもあります。

それは書き込み処理のパフォーマンスが非常に悪くなるということです。
どういうことかというと、例えば10個のカラムがあるテーブルの全カラムに対してインデックスをつけたとしましょう。
データの登録処理で1つレコードをinsertするとなった時にレコードの挿入処理に加えて、各カラムのインデックスを書き込んでinsertしないといけません。
なので一回のレコード挿入で11回の書き込みが発生してしまうことになります。これがバルクインサートのような複数のレコードを挿入するとなると、とてつもない回数の書き込み処理が走ることになります。

なので適切な箇所を見極めインデックスを貼ることが重要になってきます。