達人に学ぶDB設計徹底指南書第2版・読んだまとめpart5

はじめに

本記事では、第6章を読んで得られた学びや感想をまとめました。

※前回記事はこちら

第6章の目次

データベースとパフォーマンス
６ー１　データベースのパフォーマンスを決める要因
６ー２　インデックス設計
６ー３　B-treeインデックスの設計方針
６ー４　統計情報
６ー５　インデックス以外のチューニング

習得したこと

⚫︎アプリケーション透過性
「存在を意識しなくていい」という性質を「透過性（transparency）」と呼ぶ。
アプリケーションから見ればインデックスは空気のように透明。

⚫︎データ透過性
インデックスを作ることでテーブルに格納されているデータの中身が影響を受けることがないし、テーブルの構造も変わることはない。

⚫︎インデックス・ショットガン
アンチパターンのこと。
インデックスを作れば作るほど良いのだ、という考えは🙅‍♀️
やみくもに作るのではなく、正しい指針を理解した上で使って初めて効果が得られる。

⚫︎B-treeの評価は以下オール４
①均一性：平衡木。どんなキーを使っても、常にリーフまでの距離が一定になるため、検索を同じ計算量で行える。
②持続性：データ量の増加に比してパフォーマンスの低下が少ない。
③処理汎用性：検索・挿入・更新・削除のいずれの処理もそこそこ速い。
④非等値性：特定の検索条件を付けてもそこそこ速い。ただし否定条件は使用できない。
⑤親ソート性：ソートが必要な処理を高速化できる。
ソートはかなりのコストの高い演算。
ソートはDBMS内部で専用のメモリ領域が割り当てられており、その内部に一時的にデータを保持して実施されるが、大量のデータのソートが必要な場合、メモリに載りきらないために溢れてしまうことがある。
その場合、DBMSは一時的にストレージへデータを書き出す。
この場合のI/Oコストが非常に大きなものになる。
B-treeインデックスが存在する列をORDER BY句のキーとして指定した場合、ソート処理をスキップすることが可能になる。
これはデータベースのパフォーマンスにとって鬼門の一つであるソート処理をチューニングする大きな助けになる。

⚫︎B-treeインデックスはどの列に作ればいいか
①大規模なテーブルに対して作成する。
②カーディナリティの高い列に作成する。ただし、値が平均的に分散しているのがベスト。
③SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。

・データ量が少ない（目安としてはレコード数が10万件以下）場合、B-treeインデックスを使うよりフルスキャンの任せた方が高速な領域がある。（実際大差はないが、わざわざ無駄なインデックスを作成する必要はない）

・例えば、性別のカーディナリティは、男性・女性・その他の３つと少数。逆に口座番号などは一つの銀行内で被ることはまずないため、カーディナリティは高いと言える。
目安は、特定のキー値を指定したときに、全体のレコード数の5%程度に絞り込めるだけのカーディナリティがあること。

⚫︎インデックスを使用できないパターン
・インデックス列に演算を行なっている
・インデックス列に対してSQL関数を適用している
・IS NULL述語を使っている
・否定形＜＞を用いてる
・後方一致、また中間一致のLIKE述語を用いている（❌：'%a', '%a%')
・暗黙の型変換を行なっている

⚫︎その他の注意事項
・主キーおよび一意制約の列には作成不要
・B-treeインデックスは更新性能を劣化させる（B-treeに限らず）
・定期的なメンテナンスを行うことが望ましい

⚫︎ユーザがSQLを実行した時、最初にSQLを受け取るのが、DBMS内の「パーサ（Parser）」と呼ばれるモジュール。
役割：SQL文が適切な構文であるかをチェックする
⚫︎パーサによるチェックが済むと、次にSQLは「オプティマイザ（Optimizer）」というモジュールへ送られる。（DBMSの頭脳と呼ばれる重要な役割を担っている）
役割：SQLのアクセスパス、実行計画を決めるもの（Optimizeは最適化するという意味）
⚫︎オプティマイザは「カタログマネージャ」というモジュールに、統計情報の照会をかける。
役割：カタログマネージャは、例えるなら図書館の司書。
⚫︎カタログマネージャから統計情報を受け取ると、オプティマイザはたくさんの経路の中から最短（と思われる）経路を選択し、SQLを手続きに変換する。
その時に得られた手続きの手順が、実行計画。それに従ってようやく実データであるテーブルへとアクセスを行う。

⚫︎パーティション
テーブルのある列をキーとしてデータの物理配置をキーごとにまとめる機能。
一つのテーブルに一つしかパーティションキーは指定できない。

⚫︎パーティションプルーニング
パーティション化するとテーブルが物理的にソートされてまとめられる。こうすることで、WHERE句の条件に指定するクエリが特定のデータのみにアクセスすれば良くなるため、読み込みデータ量を減らすことができる。

⚫︎パーティションの種類
・レンジパーティション（時系列の属性をキーにする場合に有効）
・リストパーティション（離散的な値に対してデータを特定の範囲に分割する）
・ハッシュパーティション（カーディナリティが高いキーに有効）

⚫︎パーティションの注意点
・WHERE句でパーティションキーを検索条件に指定しないと意味がない（結局全てのデータを読み込む必要がある）
・パーティションに大きな偏りがないか（パーティションキーはできるだけ均等にデータを割り振れるものを採用すべき）
・複数のパーティションを組み合わせることもできる（例：「リスト＋レンジ」や「レンジ＋ハッシュ」）コンポジット・パーティションと呼ぶ。
・パーティション数の上限がある（カーディナリティが高いならインデックスを使う方が効果的）

⚫︎ヒント句
DBMSもバージョンが上がるごとに賢くなっているが、まだ最適な実行計画を選ぶことができない場合もある。そのような人間の目から見て他の実行計画が良いと思われる場合に、強制的に実行計画を変える機能が「ヒント句」。
ヒント句では、使うインデックスを変えたり、結合のアルゴリズムを指定することができる。

※正し、パフォーマンスチューニングの最終手段として使用する。

⚫︎パラレルクエリ
アプリケーションを変えることなく高速化が可能（ヒント句に似ている）
通常、シングルコアで実行されるクエリの処理を分割してマルチコア化する方法で、パラメータやヒント句で多重度を設定する。

⚫︎パラレルクエリの欠点
パラレルクエリは、サーバーやストレージ帯域のリソースが潤沢に余っているにもかかわらずクエリがそのリソースを十分に利用しきれていない場合にのみ力を発揮するチューニング手段。
そのため、CPUやストレージがすでに性能的に限界に近い状態においてパラレルクエリを有効化すると、逆効果となってクエリの遅延がひどくなる・・
しかも、他のクエリまで巻き添えにしてデータベース全体を遅延させてしまう。（チューニングとは呼びたくない）

⚫︎オンメモリ
これもパラレルクエリと似た特性を持つ。ただし、ストレージI/Oがクエリのボトルネックになっていることが前提条件。
ストレージへのアクセスが遅いのであればテーブルやインデックスのデータをメモリにのせてしまえば良いのではないか、という発想で考えられた。

感想

正直、この章の内容はほとんどが初見。。。
「インデックス」というものは業務でも使用しているのでなんとなく理解できましたが、
索引の役割！遅いクエリにはインデックスを作成したら早くなる！としか知らなかったので、
B-treeの特徴だったり、（意外と評価高い）
ユーザがSQLを実行してから値が返ってくるまでのDBMSのお仕事だったり、
統計情報の収集だったり、
チューニングの種類だったり、
知らないことの連続でした、、🥺頭パンパン・・

毎日何気なくSELECT文だったり、いろんなクエリを実行していますが、
基本的にはDBMSが最適なルートを辿ってデータを返してくれている、
そのDBMSは人間が作った
と考えると、作った人間って本当にすごい。。笑（壮大な感想）

パフォーマンスチューニングに関しては、
もっとDBに詳しくなれば楽しそう、やってみたい。。と思いましたが、
パラレルクエリの欠点の部分で、「CPUやストレージがすでに性能的に限界に近い状態においてパラレルクエリを有効化すると、逆効果となってクエリの遅延がひどくなる」のは怖いですね・・

あと、「統計情報」に関しては、ここに書けないほど理解できなかったので
再度読み返しが必要です👀💦
また理解できたらアウトプットしよう。

次の第7章は論理設計のアンチパターン。
業務でアンチパターンを使用していないか改めて考えながら読みたいです。

関連

達人に学ぶDB設計徹底指南書第2版・読んだまとめpart1
達人に学ぶDB設計徹底指南書第2版・読んだまとめpart2
達人に学ぶDB設計徹底指南書第2版・読んだまとめpart3
達人に学ぶDB設計徹底指南書第2版・読んだまとめpart4