SQLパフォーマンス詳解を読んだ

読書記録

仕事で回ってきたタスクで「はぁ〜〜SQL真面目にわからないとだな〜〜」という熱が高まったので、積ん読の順位を入れ替えて読んだ。

を見て f:id:o0h:20201013031330p:plain ってなってたやつです。

4月か、もうずっと前な感じあるぅ。

結論としてめちゃくちゃおもしろかったな、ずっと雰囲気でやっていたインデックスの管理について、雰囲気をつかめたような気がする・・・

「カーディナリティが低いカラムにインデックス張っても意味ないよ」みたいな話とか、理屈で掴めたのがめっちゃ良い。目が覚めるような感覚を覚える。
また、「もしかしたらインデックスヒントやったらワンチャン・・？」みたいなノリで試して→「やっぱ駄目ですよね！アハ！！」とかいう戯れをこれまでに何度も繰り返してきたのについては、少し勘所を掴めそうな気がする。(これはMySQL8以降で変わる部分もありそう、下位バージョンにおいては、機械がよしなにやってくれなそうな部分〜を人力で支援するぞ！！という機会がありそう。みたいなイメージ)

もっと初歩的なことでいうと、「複合インデックス作る時は順序が大事だよ」とか、そういう部分まで含めて、あ〜なるほど〜〜〜って感じに。

いつも技術書等を読む時は、「おっ！」と思った部分は原文を抜き出して自分の感想を添える！という形にしているのだけど、この本は抜き出しが多すぎて比率的に引用って呼べる範囲を超えてそうだな・・・・・という感じすら覚える。

カバーリングインデックスの話も良かったなぁ。。

以下、読書メモ(の一部！！

  • 検索ツリー
    • ルートノード > ブランチノード > リーフノード
    • リーフノードは、ノード同士を双方向連結リストで相互参照している
    • 「リーフ」が、実際のテーブルの行データ情報(=ROWID)を持っている場所
    • 実際にテーブルのデータにアクセスするまでに、ツリーを走査し→リーフノードを手繰り→テーブルからデータを取る、という流れになる
      • この時に、「ツリーだけで済む」ようであれば、極めて低コストで済む。(リーフノードから先については、コストが嵩みやすい)
      • これは「ツリーの深さは増加しにくい」という性質のために、「ツリーだけなら走査しても試行回数がたかが知れている(数回レベル)」というもの
      • 逆に、「ひとつのリーフノードだけでも、数百のエントリを保持していることがある」(P6)
  • カバーリングインデックス
    • where/order/select等、「使う必要がある列(の値)」が全てインデックス上に含まれている場合、「インデックスの表」に載っているデータだけを使って「実際のテーブルのデータ」にアクセスしないで処理を完結させる
  • セカンダリインデックス、クラスタ化インデックスあたりの話
    • セカンダリインデックス = 主キーでないインデックス
    • 主キーはBツリーインデックス、索引構成表、クラスタ化インデックス。ROWIDに直接紐づく
    • セカンダリインデックスは、クラスタ化インデックスに保存された”元のデータ”を経由して、ヒープテーブルにアクセスする
    • 「物理的なポインタ(=ROWID)」をもたず、論理キーを扱う
      • クラスタインデックスのキーの値だけを保持
      • これを「クラスタリングキー」といい、多くの場合は索引構成表のプライマリーキーとなる • それによって、インデックスの順序を入れ替えることが可能になっている
    • 索引構成表を使うと、ROWIDを用いてヒープテーブルにアクセスができるため、テーブルアクセスのコストが低い
    • セカンダリインデックスを利用してテーブルにアクセスすると、まずセカンダリインデックスで検索し(INDEX RANGE SCAN)→クラスタインデックスを検索する(INDEX UNIQUE SCAN)という風に、2つのインデックスを検索することになる。
  • numericな値
    • WHERE numeric_string = 42はインデックスが効かないが WHERE numeric_number = ’42’はインデックスが効く
    • これは「文字列を数値にキャストする」か「数値を文字列にキャストするか」の違いで、前者については、例えば '042' '00042' といった値も数値にしたら42ではあるが、インデックスが作られているのはあくまで0042 といった値に対してなので、つまり「対応するインデックスが存在しない」状態になるため
  • 複合インデックス
    • (col1, col2, col3)というINDEXに対して WHERE col1 = ? AND col2 = ? はインデックスが効くが、 WHERE col2 = ? や WHERE col1 = ? AND col3 = ?は効かないよ
    • 例えば「アーティスト名・アルバム名・曲名」という並び方で作られた索引に対して、「アーティスト名」だけで探す〜はできても、「曲名」を条件に探すっていうのは効率悪いでしょ(それなら全部スキャンしちゃうかもね)っていうイメージ
  • インデックスと順序
    • INDEX RANGE SCANはインデックスの順番で結果を返す
    • 複合INDEX(col1, col2)を作った場合、「col1で絞り込んでcol2でソートする」のようなwhere/order byを付けた場合でも、INDEXをスキャンした通りの結果をそのまま返せる = DBは明示的なSORT工程を踏む必要がない
    • ただ、これは「col1で絞り込んだ範囲であればcol2が順序よく並んでいる」といった場合に限るので、クエリの改修に注意が必要な感じ

あとはこの辺りをもう少しちゃんと読み込みたい

use-the-index-luke.com dev.mysql.com