Index Shotgun

Know your data, know your queries, and MENTOR your indexes.

• DBAだけでもアプリケーションプログラマだけでもチューニングは為し得ない

Objective: Optimize Performance

• DB開発者の一番の関心事
• 信頼性、セキュリティ、(データの？)正しさ等についてカンファレンスで発表しても、「で、それはパフォーマンスにどう影響するんですか？」といった質問しか来ないことしばしば
• パフォーマンス向上のための一番のテクニックは、インデックスをうまく使うこと

• ソフトウェア開発者はいつ・どのようにインデックスを使うべきか理解していないことが多い

  • 当てずっぽうに使ってしまう

Antpattern: Using Indexes Without a Plan

• 当てずっぽうにインデックスを作成すると陥りがちな失敗

  • インデックスなし、不十分
  • インデックス多すぎ、役に立たないインデックス
  • インデックスを使えないクエリを発行してしまう

No Indexes

• インデックスを作成すると、最新に保つためにオーバーヘッドがある

  • INSERT
  • UPDATE
  • DELETE

• 「だからインデックスを無くそう！」

  • いいえ

• オーバーヘッドは必ずしも無駄ではない

  • UPDATEのオーバヘッドをSELECTで取り返せる

    • 通常のアプリケーションはUPDATE1回に対して何百ものSELECTがある

  • UPDATE/DELETEもインデックスにより高速化しうる

    • 更新/削除対象を主キーインデックスで特定する場合など

Too Many Indexes

• 使わないインデックスは無駄
• 長いVARCHAR型のインデックスなどは大きくなってしまう
• 複合インデックスは順序に注意

• 全カラム・および全組み合わせに対してインデックスを作成してしまう者もいる

  • どのインデックスが役に立つかわからないためそうしてしまう

  • オーバヘッド甚大

    • 取り返せる保証はない

コラム: Indexes Aren’t Standard

• インデックスをはじめ、データストレージの実装・最適化に関して、ANSI標準SQLは一切を規定しない
• インデックスを最大限に活かすにはDB製品のドキュメントを読むこと
• 論理的な概念はDB製品横断的に活かせる

When No Index Can Help

• 電話帳のイメージ(【補】B+木インデックス)

• インデックスは左から右の順にしか使えない

  • 「姓」、ついで「名」でソートされている

    • 「名」の順番にソートするのには使えない

• 関数を作用させると効かない

  • 関数インデックスというのもある

    • 通常のインデックスとは別途作成しておく必要がある

• ORも効かなかったりする

  • UNIONで複数のSELECT文をマージしているのと同じ
  • 片方のSELECT文についてインデックスが効かなかったりする

• 前方一致以外のLIKE

  • これも「左から右」じゃないからダメ

コラム: Low-Selectivity Indexes

• selectivity ratio: COUNT(DISTINCT column)/COUNT(column)
• カーディナリティとほぼ同義？

• カーディナリティ低いとインデックスの効果薄いよ、という話

  • 索引にページ番号が大量に書いてあるようなイメージ
  • 目当てのページにたどり着くために、結局ページと索引を行ったり来たりする羽目に
  • 場合によってはフルスキャンのほうが速いことも

How to Recognize the Antipattern

• こんなのが聞こえてきたら注意

  • 「このクエリ、どうすれば速くできますか？」

    • 情報が足りないので当て推量しかできない

    • 情報

      • インデックス
      • 容量
      • 測定

  • 「全カラムにインデックス作成したのになんで速くならないの？」

    • 定番のantisolution (逆効果)

  • 「インデックスはDBを遅くすると聞いたので使いません」

    • パフォーマンスに関して、包括的に適用できる設計戦略などない

      • 【補】ケースバイケースということ

Legitimate Uses of the Antipattern

• 最適化すべきクエリがわからないと当て推量をせざるをえない
• それでも最善を尽くさなければならない

Solution: MENTOR Your Indexes

• 頭文字

Measure

• まず、どのクエリを最適化しなければならないかを知ること

  • SQL Server Profiler (SQL Server)
  • TKProf (Oracle)
  • slow query log (MySQL)

  • pgFouine (PostgreSQL)

    • 【補】pgBadgerのほうが新しい (2019時点)

• アプリケーション全体で見たときのボトルネックを調べること

  • 実行時間が一番長いが、滅多に発行されないクエリ
  • 実行時間の長さは次点だが、しょっちゅう発行されるクエリ
  • 後者のほうがパフォーマンス影響が大きいことも
• 測定時はクエリキャッシュを無効化すること

• 本番環境で本番データで測定できることが最も望ましい

  • 測定後はプロファイラを無効化するか頻度を下げること

    • プロファイラ自体にオーバヘッドがあるから

Explain

• 最もコストの高いクエリがわかりました
• 続いて、なぜコストが高いのか調べる

• QEP: query execution plan

  • 実行計画

• DB製品によりまちまち

  • 文法

  • レポート結果

    • 「主キーインデックス使ってる」とか

    • 「ファイルソートしてしまっている」とか

      • インデックス使えてない

Nominate

• 実行計画を読み解き、改善に役立てる

  • DB製品のドキュメント参照

• 各DB製品で改善点提案ツールが提供されている

  • インデックス作成案の提示等

Test

• インデックスを作成したら、再度測定せよ
• 上司への報告材料にもなる

Optimize

• インデックスをキャッシュに置く

• インデックスをキャッシュメモリに先読みしておく

  • MySQLのLOAD INDEX INTO CACHE文

Rebuild

• 【補】B+木インデックスの話

• 木の平衡が崩れてくるので再構成する

  • やはりDB製品依存

• どれくらいの頻度で再構成すべきか

  • ケースバイケース

    • テーブルの変更頻度
    • テーブルの大きさ

コラム: The Database Isn’t Always the Bottleneck

• アプリケーションコードが遅いこともある
• まず測定せよ

コラム: Covering Index

• 必要なデータ全部インデックスに含めれば、テーブルへのアクセスがなくなる
• 常に使えるとは限らないが、使えればパフォーマンスにかなりの好影響

英語

• bang for your buck

  • 努力に見合うだけの価値がある