はじめに

入社してから、ある機能のパフォーマンス改善をするタスクを行いました。

そこで自分はインデックスを張るという選択をし、実際に改善をすることができました。

しかし、なぜその選択を取ったのかの根拠がうまく言語化できていなかった部分があったため、今回自分なりにまとめます。

インデックスとは

まず、インデックスとはデータベースにおいて、データの検索やクエリの実行速度を構造させるためのデータ構造のことです。

インデックスを張ることでデータを検索する際に全件総なめを防ぐことができ、適切に使用するとパフォーマンスを改善することができます。

具体的なシチュエーション

例えば以下のようなテーブルがあったとします。

商品管理システムでありそうなテーブルです。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

こういったテーブルでカテゴリで検索を行うパターンで考えてみます。

SELECT * FROM products WHERE category_id = 1;

インデックスがない場合

インデックスがない場合は以下の流れで検索が行われます

①クエリが実行される

②全テーブルスキャン

テーブルの最初の行から最後の行までを順番にチェック

各行のcategory_idの値をチェックし、条件に一致するかを判断する

③データを返却する

インデックスがある場合

インデックス張ります

CREATE INDEX idx_category_id ON products(category_id);

インデックスがある場合は以下の流れで検索が行われます。

①クエリが実行される

②インデックスを使用して、category_idが適する(条件に一致する)行のみを効率的に検索

③データを返却する

現実世界で例えると

インデックスは一言で例えると本の索引です。

技術書などを読んでいてこの単語について書かれている場所を読みたい。みたいな時に使用しますよね。

インデックスがない場合

当然該当ページがわからないので最初のページから最後のページまでの全てのページをを順番に見ていく必要があります。テーブル全体をスキャンしていると言えるでしょう。

インデックスがある場合

本ににインデックス(索引)がありますので、目的のページを知り、該当ページを開くことができます。これはインデックスを用いて効率的にデータを検索するのと同じです。

このように、インデックスがあると、必要な情報を素早く見つけることができます。

インデックスを張ることのトレードオフ

ここまで、インデックスを張るとデータの検索が早くなることがわかりました。

しかし、インデックスは検索が早くなる分、テーブルにデータが追加されたり更新されたりする際に負荷がかかるというトレードオフが発生します。

なぜならば、素早く検索するためにデータの並び順を担保する必要があるためです。

※インデックスはテーブルとは別のオブジェクトとして存在しています

先ほどの本の例えで言うならば、新しいページを追加するたびに、そのページの内容をインデックスに追加し、インデックスの順序を保つ必要があります。これにより、インデックスの更新に時間とリソースがかかることになります。

そのためインデックスは張れば張るほど良いと言うわけではなく、適切にインデックス設計を行う必要があります。

インデックスを張ると良さそうな列の特徴や見つけ方。

特徴①:データ量が多いテーブル

データ量が多いテーブルにはインデックスの使用は有効的です。

閾値としては数万件規模のデータ量があるテーブルに使うと良いです。

そのためデータ量が少ない場合はインデックスの効果を享受しづらいです。

※プライマリキーとして設定されることが多いidに関しては自動的にインデックスが作成されるので、idで検索する際には追加でインデックスを張る必要はありません。

特徴②:カーディナリティの高い列

カーディナリティの高い列にもインデックスの使用は有効的です。

これは特定のカラムで存在する値の種類の多さを表す概念です。

例えば冒頭の「具体的なシチュエーション」でproductsテーブルにインデックスを使用した例で考えると、

category_idが数百種類以上　→ カーディナリティが高い

category_idが数個　→ カーディナリティが低い

と表現ができます。

category_idは場合によって変化するかと思いますが、一意な値になるカラム例えば利用者の電話番号などはカーディナリティが高くなりそうです。

目安としてはあるキーで検索すると全体のレコードの5%ぐらいに絞り込むだけのカーディナリティがあると良いです。

見つけ方:スロークエリログを確認する

実行中の遅いクエリの測定を行い、該当するクエリの実行計画を確認し、インデックスが使用されずに検索を行っている箇所を特定します

効果の測定

最後にインデックス使用の効果測定についてです。

パフォーマンスを改善するためには必ず計測が必要なので最後にそのやり方についてまとめます。

そもそもインデックスが使われているか確認する

まずはインデックスが期待通りに使われているか確認をします。

MySQLの場合EXPLAINコマンドで確認ができます

このコマンドはMySQLが選択した実行計画を確認できるコマンドです。この例の場合 keyの項目にidx_category_idと出力されていれば使用されていることになります。

EXPLAIN SELECT * FROM products WHERE category_id = 1;

MySQLのオプティマイザが、作成したインデックスを使用するよりも他のスキャンの仕方が効率的だと判断した場合や、クエリの構造が適していない場合(インデックス列に対して演算をしている等)の場合には使用されないことになるので確認を行いましょう。

実行時間を比較する

実行時間を計測するにはプロファイリングの使用で実現ができます。

1.プロファイリングを有効にします

SET profiling = 1;

2.クエリを実行します

SELECT * FROM products WHERE category_id = 1;

3.プロファイリング表示します

SHOW PROFILES;

この一連の流れを修正前、修正後に行い実行時間を比較することで、効果の測定ができます。

終わりに

インデックスに関わらず、技術の意思決定の振り返りは必ず行っていきたいと思いました。
明日は@yuto14さんの記事です

参考文献