SQLパフォーマンス詳解オンライン読書会(3)ノート

• p45「LIKEフィルタに対するインデックス」から読んだ

• 次回p70 「列の連結」から

like

• 最初のワイルドカードの前までインデックスツリーの操作がされる
• 最初のワイルドカードの前部分の選択性を高くする
  • 'WI%ND' > 'WIN%D' > 'WINA%'
  • ワイルドカードから始まるLIKE式は効率が悪いからやめる%TERM%
• Prepared Statement + Like式を使った場合、postgreSQLだとインデックスが使われない。
• 全文検索を使う
  • MySQL
    • match
    • against
  • Oracle
    • contains
  • postgreSQL
    • @@演算子
  • SQL Server
    • containns
• 最初に1つだけワイルドカードのある条件('%TERM')の対処
  • 逆キー索引:REVERSEキーワード(Oracle)

https://jeffkemponoracle.com/2008/01/like-with-wildcard-at-start-can-use-an-index/

インデックスの結合

• インデックスは、2つ使うより1つだけ使う方が高速。

• 二つ以上の範囲条件を含むクエリをサポートする単一のindexはない

  • マルチカラムインデックス+フィルタ述語
    • 選択制の高い列をインデックス定義の最初におく
  • 別々の列にそれぞれindexをはる。
    • 複数をまとめて効率的に使えるインデックス→ビットマップインデックス
      • 事前にクエリが分かっている場合はマルチカラムBツリーインデックスの方が早い
      • insert,update,deleteの性能が悪い
        • DWH向き。OLTPでは使い物にならない。
• 余談:マルチカラムインデックスの単一カラムのみを使用するインデックススキャン
  • オプティマイザの判断なので狙うのは難しい。

部分インデックス

• 特定の行にのみインデックスを張る
• INDEX定義時にwhere句を使うだけなので簡単に作れる。

  CREATE INDEX messages_todo
            ON messages (receiver)
         WHERE processed = 'N'

• MySQLの部分インデックスはこれとはまた違うみたい。
  • 長さがわからないテキストの最初のデータにindexをはるみたいなやつ
    • 2GBのカラムを含むレコードにindexを張るのは無理
• 余談:indexに対するSQL標準はない

OracleのNULL

• 空文字列をNULLとして扱う
  • SQL標準と違っている
• NULLの連結は通常のDBはNULLになる
  • Oracleの場合、空文字になる
• VARCHAR2に空文字を格納できない
  • 格納するとNULLになってしまう。

• このような奇妙な動作に対するOracle互換モードがある

  • DB2,MariaDB,EDB Postgres,etc

• インデックス定義に含まれる列の全てがNULLの場合はindexに含まれない。

  • 全てのindexはNULLを含まない部分インデックスのようになっている

• NULLを含むインデックスを作成したい場合はNULLになることがない列をインデックスに追加する(定数でもOK)

  • CREATE INDEX emp_dob ON employees (date_of_birth, '1')

• インデックスに含まれる列に NOT NULL制約がないとインデックスが使われない→フルスキャンになる。

これ以降眠い&謎のzoom中断によりメモってません。。。

SQLパフォーマンス詳解