• 2020/05/07 20:00~22:00
• 今回はp18「遅いインデックスパートII 」からp44「大なり、小なり、BETWEEN」まで読んだ
• 次回は45から

遅いインデックス パートII

• クエリオプティマイザ:SQLを実行計画に変換するコンポーネント

  • Cost-Base-Optimizer
    • 統計情報などからコストを評価する
  • Rule-Base-Optimizer
    • ハードコードされた優先順位で実行計画を生成
    • oracleは10で廃止

• インデックスを遅くする要因

  • 広範囲のインデックス探索
  • 沢山の行を1行づつ読み出す処理
  • 統計情報が存在しない時のデフォルト値が最適でない
    • INDEX RANGE SCANのデフォルト値=40

関数

• 大文字小文字を無視した検索条件を書きたい

  • WHERE UPPER(last_name) = UPPER('winand')

    • FULL SCANが発生する
      • 定数は左辺に置かないと遅くなる？

    • 対策:関数インデックスを使う

      • CREATE INDEX emp_up_name ON employees (UPPER(last_name))
      • SQL Serverは関数インデックスはサポートしない
        • 計算列/生成列にindexをはる
          • ALTER TABLE employees ADD last_name_up AS UPPER(last_name) CREATE INDEX emp_up_name ON employees (last_name_up)
          • viewにindexを貼る方法は？
            • SQL Serverのみ可能だが一般的ではない。
      • 関数indexはdeterministicな関数にしか利用できない
        • deterministic→引数に対して同じ値を返すこと
          • ダメな例;現在時刻や乱数的な要素を隠し入力としてもつ関数
            • Sysdateを使うget_age関数
              • RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, date_of_birth)/12);

    • 注意;ORMがUPPER/LOWERを勝手に使うことがある

      • 例;hibernateの大文字・小文字を区別しない検索

• この本はSQL ServerやPostgreSQLの統計情報の読み方が解説されているらしい。

  • 以外とそういう資料ないので貴重

インデックスの作り過ぎ

• uppere(last_name)の関数indexを作った際に、lower(last_name)も作りたい
  • 余計なindexが増える
    • 1つのテーブルに対して6つまで、という本もある
      • オラクルパフォーマンスチューニング？という本らしい
      • 6個は厳しいのでは

パラメータ化クエリ

• パラメータ化クエリ＝プレースホルダを使った実行
  • メリット
    • セキュリティ=SQLインジェクションの防止
    • パフォーマンス
  • デメリット
    • オプティマイザがコスト計算を使い回す
      • 最初に実行した変なワークロードのクエリで計算したコストがキャッシュされる可能性
• プレースホルダが埋められた状態で評価されるのか？それとも埋められる前に評価されるのか？
  • 歯切れのいい回答が出なかった
  • 埋めらる前に評価される、という風に書かれているように読めるけど？
• JDBCの仕様でプリペアドステイトメントの機能が必須化されている
  • MySQLの過去バージョンではインターフェースのみ準拠して、内部で適当にやっていたらしい？
    • 5系でもそんな感じらしいが最近のは未確認とのこと
• DBI
• DBD

範囲検索

• 複合インデックスの検索時にはインデックスの列の定義順に注意する。
  • インデックスの定義順により実行計画の以下が変わる
    • アクセス述語
    • フィルタ述語