Zenn
株式会社ソルクシーズ（事業戦略室）Publicationへの投稿
🐈

インデックスとの付き合い方

Kojiro Ohara
2023/02/02に公開
SQL
初心者
tech

TL;DR

  • インデックスが効かない形を把握する。
  • 実行計画を確認する。
  • エビデンスを持ってDBAと対話する。

インデックスの利用方法を改善することから始める

それなりの規模のプロジェクトに属する多くのエンジニアは、インデックスなどテーブルの定義に必ずしも関われるとは限りません。
DBA（データベース管理者）が各業務担当からあがってくる要件に従い、適切なデータベース設計を行い実装します。
求めているパフォーマンスが得られない場合、DBAに責任を求めたくなることもあるでしょう。
責任を他所へ求める前に自分たちのできることがないかをインデックスを利用する立場から考えてみます。

インデックスが効かない例

SQL Antipatterns: Avoiding the Pitfalls of Database Programmingでは次のような比喩が示されています。

電話帳にたとえると、データベースのインデックスを考えることができます。
姓がチャールズである電話帳のすべての人を調べるように頼んだら、それは簡単な作業です。
同じ姓の人はすべて一緒にリストされます。これは、電話帳がそのように並べられているためです。
ただし、名がチャールズである電話帳のすべての人を検索するように頼んだ場合、これは電話帳の姓の順序の恩恵を受けることはできません。
姓に関係なく、誰でもその名前を持つことができるため、電話帳全体を1行ずつ検索する必要があります。
電話帳は、last_name、first_nameの複合データベース インデックスのように、姓、名の順に並べられます。
このインデックスは、名での検索には役立ちません。

インデックスを正しく理解していないと効率のよい検索ができないことを示唆しています。
いくつかの例を紹介します。
思ったようなパフォーマンスが出ない場合に同じような実装がないか確認してください。

インデックス列に演算や関数を適用する

バグの報告日（date_reported）にインデックスを設定している例です。

SELECT
  *
FROM
  bugs
WHERE
  MONTH(`date_reported`) = 4;

4月の値を取得するためにdate_reportedをMONTH関数で月に変換して比較しています。
このような場合はインデックスが利用されません。

次のような場合も同様です。

SELECT
  *
FROM
  items
WHERE
  price * 1.1 > 100;

このような場合はprice > 100/1.1とすることでインデックスを利用できます。

実行結果の比較

左がprice * 1.1 > 100、右がprice > 100/1.1の実行結果（MySQL WorkbenchのVisual Explain）です。
左ではインデックスが活用されず、全件スキャン（Full Table Scan）になっていることがわかります。

順序が正しくない

電話帳の例を振り返ります。
電話帳では次のような形でインデックスが定義されています。

CREATE
  INDEX TelephoneBook
ON Accounts(last_name, first_name);

このとき、次のようなクエリはインデックスの恩恵を受けることはできません。

SELECT
  *
FROM
  Accounts
ORDER BY
  first_name,
  last_name;

先述の例のとおり、first_nameから参照しようとしているためです。

論理和（OR）

SELECT
  *
FROM
  Accounts
WHERE
  last_name = 'Miwa'
OR
  first_name = 'Miwa';

ひとつ前の問題と同様にfirst_nameはインデックスからの恩恵は得られません。

あいまい検索（LIKE）

SELECT
  *
FROM
  Accounts
WHERE
  last_name
LIKE
  '%a'; -- 後方一致

SELECT
  *
FROM
  Accounts
WHERE
  last_name
LIKE
  '%a%'; -- 部分一致

LIKEは前方一致のみがインデックスを利用できます。

否定形

SELECT
  *
FROM
  items
WHERE
  price <> 100;

否定形（<>, NOT EQUAL, NOT IN）はインデックスを利用できません。

暗黙の型変換

カラムcodeがcharで定義されている場合、次のようなクエリではインデックスが利用されません。

SELECT
  *
FROM
  items
WHERE
  code = 10;

実行計画（EXPLAIN）を利用しよう

データベース製品にはそれぞれ実行計画を確認する構文が用意されています。

製品 構文 関連リンク
Microsoft SQL Server SET SHOWPLAN_XMLなど 参照
MySQL EXPLAIN 参照
Oracle EXPLAIN PLAN 参照
PostgreSQL EXPLAIN 参照

また、製品によってはビジュアルツールが用意されており、そこで実行計画を確認できます。
MySQL Workbenchではクエリタブ上部のExecute the EXPLAIN command on the statement under the cursorボタンで実行計画の可視化を実行できます。

実行計画の可視化

ツールをうまく活用して、インデックスが正しく活用されているか確認しましょう。

データベース管理者と対話をする

実行計画などで、インデックスの不備が、業務クエリのパフォーマンスに影響を与えていることが把握できたら、データベース管理者に報告して、改善を求めます。
このとき、可能な限り次のことに気をつけましょう。

  • 必要なエビデンスを用意する。
  • 業務での必要性を正しく説明する。
  • 同じテーブルを利用する他のクエリへの影響を確認する。

通常データベース管理者は各開発者より業務をより広い範囲で見ています。
常に要望どおりにインデックスが改善されるとは限りません。
その際はデータベース管理者と解決策について対話しましょう。

まとめ

簡単にインデックスが利用されない形を確認しました。
ここで紹介したもの以外にもインデックスが利用できない条件はあります。
より知識を深めたい方は参考書籍などで学習してください。
各製品のマニュアルでも学習可能です。

  • インデックスが効かない形を把握する。
  • 実行計画を確認する。
  • エビデンスを持ってDBAと対話する。

参考書籍

株式会社ソルクシーズ（事業戦略室）
株式会社ソルクシーズ（事業戦略室）Publication

株式会社ソルクシーズの事業戦略室のアカウントです。 ジュニアエンジニア向けのお役立ち記事を中心に投稿しています。 採用サイト：solxyz.co.jp/recruit/ 未経験採用も実施中です！

Discussion