sql
[読書メモ] SQL アンチパターン: II部 データベース物理設計のアンチパターン
目次

書籍

SQL アンチパターン SQL Antipatterns (2013年, オライリー・ジャパン)

  • Bill Karwin 著

  • 和田 卓人, 和田 省二 監訳

  • 小島 修 訳

1章 インデックスショットガン (闇雲インデックス)

目的:

パフォーマンスを最適化する

  • データベースのパフォーマンスを完全する最善の方法は、 インデックス を効果的に使用すること

  • インデックス:

    • データベースが使うデータ構造で、対象とするデータベース列の値が、 データベースに実際に格納されている行の位置に関連付けられている

    • テーブルを最初から最後まで検索する方法に比べ、インデックスを用いることで検索を容易に行えるようになる

アンチパターン:

闇雲にインデックスを使用する

  • インデックスを全く定義しない or 少ししか定義しない

    • 通常のアプリケーションでは、更新よりも参照のほうが何百倍も多い

    • インデックス維持のためのオーバーヘッドを取り戻せる

  • インデックスを定義し過ぎる or 役立たないインデックスを定義してしまう

    • ほとんどのデータベースは、主キーのインデックスを自動的に作成するので、主キーに明示的にインデックスを定義するのは冗長

    • 検索やソートに使わない列につけても役立たない

    • 冗長だったり使用頻度の極めて低い複合インデックス

  • インデックスを活用しないクエリを実行してしまう

    • 複合インデックスでは列の順序がとても重要!! 検索条件、結合条件、ソート順において、列を定義した順 (左から右) に使わなければならない。

    • WHERE MONTH(date_reported) = 4

      • このインデックスの順番は年ごとに1月1日を開始日とした連続した日付に基づいているため、月を基準にした検索には役立たない

    • LIKE '%crash%'

      • この LIKE述語は、文字列のどの部分とも一致する可能性があるため、ソートされたインデックスデータ構造は役立たない

解決策:

MENTOR の原則に基づいて効果的なインデックス管理を行う

  • Measure (測定)

    • MySQL と PostgreSQL は、指定された閾値より実行時間が長くかかったクエリを記録できる

      • MySQL: スロークエリログ。 long_query_time (デフォルト10秒)

      • PostgreSQL: log_min_duration_statement 。 pgFouine (クエリログ分析支援ツール) もある。

    • クエリパフォーマンス測定中は、クエリ結果のキャッシュをすべて無効化しましょう

  • Explain (解析)

    • クエリの処理が遅くなっている原因を分析します

    • データベースは、クエリ実行計画 (Query Execution Plan: QEP) と呼ばれるクエリ最適化機能によって、クエリ実行にどのインデックスを使うかを判断している

    • 各データベース製品における QEP 取得方法

      IBM DB2

      EXPLAIN, db2expln コマンドまたは Visual Explain

      Microsoft SQL Server

      SET SHOWPLAN_XML または Display Execution Plan

      MySQL

      EXPLAIN

      Oracle

      EXPLAIN PLAN

      PostgresSQL

      EXPLAIN

      SQLite

      EXPLAIN

  • Nominate (指名)

    • クエリの QEP を読んで、クエリがインデックスを使わないでテーブルアクセスしている箇所を探しましょう

    • カバーリングインデックス (P.139)

  • Test (テスト)

    • インデックスの作成後 (インデックス作成でなくても何か策を講じたら) 再びクエリをプロファイリングする

  • Optimaize (最適化)

    • インデックスはコンパクトで、使用頻度の高いデータ構造のため、キャッシュメモリに格納されやすい

    • メモリ上のインデックスにアクセスすることにより、ディスクI/O を伴う読み込みよりもはるかにパフォーマンスを改善できる

    • データベースサーバーでは、キャッシュに割り当てるシステムメモリ量を設定できる

    • 使用頻度の高いインデックスをあらかじめキャッシュにロードしておくことでメリットを得られる場合もあり ( LOAD INDEX INTO CACHE )

  • Rebuild (再構築)

    • インデックスは、バランスが取れているときに最も効果的

    • 長期にわたって行の更新や削除を行っていくと次第に不均衡になっていく

    • できる限りインデックスの効率を高めたいのであれば、定期的にメンテナンスする価値はある

    • 各データベース製品におけるインデックスのメンテナンスコマンド

      IBM DB2

      REBUILD INDEX

      Microsoft SQL Server

      ALTER INDEX ... REORGANIZE 、 ALTER INDEX ... REBUILD 、または DBCC DBREINDEX

      MySQL

      ANALYZE TABLE または OPTIMIZE TABLE

      Oracle

      ALTER INDEX ... REBUILD

      PostgresSQL

      VACUUM または ANALYZE

      SQLite

      VACUUM

まとめ

推測のみに基づいて、闇雲にインデックスをつけてはならない

  • データとクエリについての理解を深め、 MENTOR の原則に基づいてインデックスを管理しましょう