0.はじめに
最近開発中に特定の機能を使用するとDBのCPUが跳ね上がる現象に出くわしました。
詳しくSQLクエリを見ていくと「これインデックス貼っているのに正しく使われてねーじゃん」というオチだったので、どんな時にインデックスを使った検索がされるのかまとめていこうと思います。
1.インデックスを使用した検索の基本
SQLデータベースでのインデックスは、検索速度を大幅に向上させるために利用されるデータ構造です。ただし、すべてのクエリにインデックスが適用されるわけではなく、条件により無効化される場合もあります。以下に、MySQLでインデックスが効果的に使用される条件を紹介します。
インデックスが有効になる条件
完全一致検索
特定カラムが完全一致で検索される場合、インデックスが効率的に使用されます。
SELECT * FROM users WHERE email = 'user@example.com';
範囲検索
比較演算子（<や>）やBETWEENを使った範囲検索でも、インデックスが活用されます。
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
結合条件での使用
インデックスが結合キーに設定されている場合、結合条件でインデックスが使用されるため、結合速度が大幅に向上します。
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
ソート条件の最適化
インデックスはORDER BYやGROUP BYなどのソートにも有効に働きます。
SELECT * FROM products ORDER BY price DESC;
2. 複合インデックスの実践的な使い方
複合インデックスは、複数カラムを対象にしたインデックスで、特に複雑なクエリのパフォーマンスを向上させるために使われます。複合インデックスを最大限に活用するためには、カラムの順序が重要であり、条件に合った設計が求められます。
単一のインデックスに関しては正しく使える方が多いと思いますが、複合インデックスになると間違っているパターンを見かけることが多くなります。（「0.はじめに」で見つけたものもこのパターンでした。）
複合インデックスの基本と最左前提ルール
MySQLでは、複合インデックスの最初のカラムが条件に含まれている場合にのみインデックスが有効に使用されます。これを最左前提ルールと呼びます。
例えば、(customer_id, order_date)という複合インデックスを設定した場合、以下のようにcustomer_idが条件に含まれているクエリでインデックスが利用されます。
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-01-01';
しかし、order_dateのみを条件にした場合はインデックスが無効化され、フルスキャンされることがあります。
複合インデックスのパターン別具体例
頻繁な絞り込みとソート
例えば、customer_idとorder_dateで頻繁に絞り込んでソートする場合、(customer_id, order_date)の複合インデックスが最適です。
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date DESC;
検索カラムの順序を考慮したインデックス設計
3. インデックスが効かないケースとその対策
インデックスを設定していても、条件によっては無視され、パフォーマンスが著しく低下することがあります。代表的なケースとその回避策を以下に示します。
関数適用によるインデックスの無効化
インデックスが設定されたカラムに関数が適用されると無効化されます。例えば、LOWER()を使用した場合、インデックスは無視されます。この場合、関数インデックス（MySQLでは生成関数を使った仮想生成列）を使用することで解決できます。
ALTER TABLE users ADD COLUMN email_lower VARCHAR(255) GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_email_lower ON users (email_lower);
部分一致検索
LIKE '%keyword%'のように前方一致を含む検索は、インデックスを無効化します。このようなケースでは、MySQLの全文検索インデックスを設定することで効率化が図れます。
CREATE FULLTEXT INDEX idx_description ON products(description);
4. MySQLでのクエリプラン確認方法
インデックスが実際に使用されているかを確認するには、MySQLのEXPLAINコマンドを活用することが有効です。クエリプランを確認することで、インデックスがどのように使われているか、あるいは無視されているかを把握できます。以下に、EXPLAINの出力例とその解釈ポイントを詳しく解説します。
- EXPLAINの使用例
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
EXPLAIN出力の解釈ポイント
| 列名 | 説明 |
|---|---|
| type | インデックスの使用方法を示します。（下に行くほど効率が良いです） - ALL: フルテーブルスキャン。- index: インデックス順でのスキャン。- range: 範囲検索でインデックスを使用。- ref: 特定の条件に基づいたインデックス検索。- constまたはeq_ref: 最も効率的なインデックス検索。 |
| possible_keys | クエリで使用可能なインデックスが表示されます。インデックスが使われていない場合の改善検討に役立ちます。 |
| key | 実際に使用されているインデックスが表示されます。NULLの場合、インデックスが使用されていません。 |
| rows | 検索対象として評価された行数を示します。多いほどインデックスが無視されている可能性が高いです。 |
| Extra | クエリの追加情報が表示されます。 - Using where: WHERE句での絞り込み。- Using index: インデックスのみでクエリが完結。- Using filesort: ORDER BYで追加のソートが必要。- Using temporary: 一時テーブルが作成される。 |
具体例での出力の解釈
以下の出力例で、インデックスが実際に活用されているかを見ていきましょう。
このテーブルにはemail, activeというカラムの複合インデックスuniq_idx_users_on_emailが使用されています。
emailで検索
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
- type: refにより検索が行われ、インデックスが使用されていることがわかります
- key: uniq_idx_users_on_emailインデックスが使用されています
email, activeで検索
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com' AND active = 1;
- type: constにより検索が行われ、インデックスが使用されているかつemailよりも効率が良いこともわかります
- key: uniq_idx_users_on_emailインデックスが使用されています
activeで検索
EXPLAIN SELECT * FROM users WHERE active = 1;
- type: ALLにより検索が行われ、検索効率としては最悪なことがわかります
- key: uniq_idx_users_on_emailインデックスが使用できていません
5.おわりに
インデックスはSQLのパフォーマンス最適化において非常に強力なツールです。しかし、単にインデックスを設定するだけでは必ずしも効果が発揮されず、条件によっては無効化されることがあります。今回の記事で紹介した、インデックスが使用される条件、効かないケースと対策、複合インデックスの効果的な使い方、そしてクエリプランの確認方法を理解することで、より適切なインデックス設計と活用ができるようになります。
「思ったよりもクエリが遅いな」という時、どのようなインデックスを貼っていて、どのようなクエリプランになっているのか見直してみてください。
参考