達人に学ぶDB設計徹底指南書 6章

データベースとパフォーマンス改善

6-2 インデックス設計

インデックス設計は、DBの論理設計やアプリケーションに影響を及ばさないパーフォーマンス改善の手段である。

インデックスも複数種類あるが、一番人気なのは「B-Treeインデックス」。
インデックスによっては、扱えないDBMSもあるが、B-Treeインデックスについては
おおよそ扱えるし、頻繁に使える。

B-Treeインデックスの長所は？

他のインデックスと比べると、平均点の高さ。 どの項目も高い効果である。

項目
1均一性・・「各キー値の間で検索速度にばらつきが少ない」
2持続性・・「データ量の増加に比してパフォーマンス低下が少ない」
3処理汎用生・・「検索、挿入、更新、削除のいずれかの処理もはやい」
4非等値性・・「「＝, >, <」といった統合を使ったSQLでもはやい」
5親ソート性・・「GROUP BY, ORDER BYなどのソートが必要な処理」

B-Treeインデックスの構造

B-Tree構造は、名前の通り、ツリー状(木)のような構造をしている。 最下位のノードに実データが存在しており、最上位のノードから順にノードを辿ってリーフから日データを探す。

1. 1.均一性

「キーとなる値の列に対して、どのような値を指定しても」、同じ速さで結果を得られる。これは「平衝木」の構造だからである。

※ただ、「挿入」、「更新」、「削除」などを繰り返して運用していくうちに構造は崩れ、バランスは悪くなり「非平衝木」になるので注意が必要である。

1. 2.持続性

B-treeは更新が繰り返されると、「性能劣化」が進むが、長期に見ても**緩やかな性能劣化**である。厳密に言えば データ量の対数に比例する。 → 「O(log n)」nはデータ量。テーブルのフルスキャンより速い。

この理由は、B-Treeの構造が平べったく、高さが 「3〜4程度」であるから。低い高さの木では、データ量が増えても変わらない特性。

1. 3.処理汎用性

「更新」以外の「挿入」、「削除」、「検索」の速さもデータ量に対して　O(log n)です。全ての処理速度が同等であり、データが増えても性能劣化の度合いが緩やかです。ビットマップインデックスにはこのような特徴はありません。 ビットマップインデックスは更新速度において、B-Treeを凌駕ソウルが、その他は多大な時間かかります。

1. 4.非等値性

B-Treeは構築される時、必ずキー値をソートするためリーフのーどを一つに絞れない場合でも左や右といった**探索範囲**を絞ることができます。B-Treeが効果をもたらせない検索条件は、※＊否定条件(!=, <>)**です。 ※これは、特定のノード以外のすべてのノードが該当してしまうため、B-Treeによる絞り込みができず全く役に立たない。

1. 5.親ソート性

SQLは、一切の手続きを記述しないため、SELECT文やUPDATE文の中でも明示的にソートを記述することは少ない-✴︎。しかし、以下のような記述をしたときは*暗黙的にDBMS内部でソートが行われる*

✴︎部分だが、ORDER BY 句を使って、明示的にソート処理を行うことは結構ある。

• 集約関数 (COUNT, SUM, AVG, MAX, MIN)
• ORDER BY句
• 集約演算 (UNION, INTERSECT, EXCEPT)
• OLAP関数 (RANK, ROW_NUMBER)

ソートというのは、かなりコストの高い演算です。ソートな内部で*専用のメモリ領域が高く割り当てられる、その内部に一時的にデータを保持して実施される。大量のデータのソートが必要な場合は、メモリに載りきらないためにあふれることがある。
その場合、DBMSは一時的にディスクへデータを書き出す。この場合、I/Oコストが非常に大きなものになる。したがって、**SQL文を記述する際は極力大きなソートを避けることが
パフォーマンス上、望ましい。＊＊
B-Treeインデックスはキー値をソートとして保持する。B-Treeインデックスが存在する列をORDER BY句のキーとして指定した場合、ソート処理をスキップすることが可能になる。
データベースのパフォーマンスにとって鬼門の一つであるソート処理をチューニングする大きな助けになる。

6-3 B-Treeの設置方針

1. 1 大規模なテーブルに作成
2. 2　カーディナリティの高い列に作成
3. 3 SQL文でWhere句や結合条件に使用されている列に作成

Q.B-Treeインデックスとテーブルの規模

先ほどの閾値についてだが、「データ量が少ない場合は大差」はないです。ではその少ないデータ量とは？？

結論、閾値(定まった値)はない。理由は、サーバやストレージなどの環境要因によって異なるから。

強いて言えば、レコード数が1万件以下であればさほど差はない。
これはあくまでも目安なので、簡易的に観測を行って、感覚を掴むことを推奨する。

Q B-Treeとカーディナリティ

B-Treeを作成する列を定める際に、「カーディナリティ」は重要な指標列です。 ※カーディナリティは、種類の多さを表す文言です。

B-Treeインデックスを作る時は、カーディナリティが高い列を選択することが基本である。
特定の値を指定する際に、全体レコード数の5%程度に絞り込めるかどうかがポイントです。

ただ、注意点が2点あります。

1 複数列に対して、インデックスを作成する場合、カーディナリティは複数列の組み合わせを対象にするということです。

2 カーディナリティが高くても、データ値の範囲が偏っているケースでは、効果が薄れる。
検索速度が偏るため、意味がない。

Q B-TreeインデックスとSQL

その他の注意ポイント

1. インデックス列に演算を行っている
   インデックス列に演算を行っている場合です。これは、インデックスを設定しているのはあくまでもカラムであるからです。
   例えば、商品代金 price にインデックス設定している

SELECT * FROM  COMPANY WHERE price * 1.10 > 1000

priceにインデックスを指定しているが price * 1.10に設定はしていない。

1. 索引列に対してSQL関数を適用している

SELECT * FROM SomeTable Where SUB_STR(col_1, 1, 1) = 'a';

インデックスに指定する値は、カラム 「col_1」であって「SUB_STR(col_1, 1, 1)」に指定しているわけではないからである。

理由は「１」と同じである。
2. IS NULL
NULLはデータ値としてみなされないため、データを保持しません。したがって、IS NULL または NOT NULL術後に対しては有効ではない。 ただ、DBMSの実装において依存するのところもあり、一部のDBMSはNULLを用いた場合でもインデックスが有効に作用する。

1. 否定形

否定形ではインデックスを利用できません。例え、利用したとしても検索範囲が広すぎて役に立たない。

SELECT * FROM SomeTable where col_1 <> 100

1. OR を用いる
   orを用いた場合は、インデックスを利用されない。この場合はIN句で書き換える必要があります。

SELECT * FROM SomeTable where col_1 =99 OR col_1 = 100

ORを用いた場合はインデックスを利用できない。代わりに　IN句を使うと良い

WHERE col_1 LIKE (99, 100);

1. 後方一致または、中間一致を用いる
   LIKEを用いるときは、前方一致のみ索引が使用される。

2. 暗黙の型変換をおこなっている
   基本
   でデータ型の異なる列値をSQLにおいて選択条件または結合条件して利用される場合、型を統一する必要があります。
   通常は、明治的に型変換を行なうが、明示的な型変換を行わなくても、SQL文がエラーになるわけではないです。
   もし、明示的に型変換を行わない、つまり暗黙的に型変換をおこなった場合に、その列にインデックスを設定しても
   使用されなくなる。

インデックスを使用したい場合は※※明示的に型変換**をする必要がある。

1. 暗黙の型変換を行っている
   文字列型で定義されたcol_1に対する条件を書く場合の例である。

SELECT * FROM SomeTable where col_1 = 10;

SELECT * FROM SomeTable where col_1 = 10;

    SELECT * FROM SomeTable where col_1 = CAST(10 AS CHAR(2));

データ型の異なる列値をSQLにおいて選択条件 or 結合条件として利用する場合型変換を行って統一する必要がある。
3つ目については明示的に型変換を行っていますが、SQL文がエラーになるわけではない。
列とデータ型の異なる値が条件に指定された場合は、DBMSはn内部的に型変換を行います。しかし、その場合はインデックスを使用されない。これを回避するために、
明示的に条件に使用する値のデータ型を列のデータ型に合わせて行う必要がある。

B-treeインデックスに関するその他の注意事項

B-Treeインデックスについて、一通り学習をすすめたが、最後に留意点がある。

主キーおよび一意制約の列には作成不要

DBMSは主キー制約や一意制約を作成する際、**内部的にはB-Treeインデックスを作成している。**B-Treeインデックスがデータをソートして保持するため、重複値チェックにこれを利用する。そのため、主キー制約や一意制約が存在する列に、二重にインデックスを作成する必要はない。

こうした列をSQL文の中で条件として利用する場合、自動的にインデックスが使われる。

B-treeインデックスは更新性能を劣化させる

B-treeに限らず、インデックス全般の欠点として言われることである。インデックスは一般的にテーブルとは独立のオブジェクトとしてDBMS内部に保持される。したがって、インデックスが作成されている対象の列値が変更されると、インデックス内部の値も変更しなければならない。B-Treeインデックスを作成すればするほど、**当該テーブルに　対する更新性能が劣化　する。このトレードオフには注意が必要である。闇雲にインデックスを貼らないようにする。**

定期的なメンテナンスを行うことが望ましい

インデックスは、テーブルのデータ更新されると、緩やかに(長期的に)構造が崩れて性能が劣化していく。 　崩れるタイミングは算出できないため、定期的なメンテナンスが重要である。 -具体的な対策の例として、インデックスの再構築がある。 対策頻度についても、算出が難しい。というのも、システムの特性上(システムがどの程度の更新量を持つか、など)に依存するので、一概に言えない。

DBMSごとにインデックス構造が崩れている場合の指標値(断片化率や木の高さなど)とその調査方法を調べてみることを推奨

6-4 統計情報

統計情報は、DBMSがユーザーから受け取ったSQLを理解し、SQLの実行をどのように実行するかを決める手段である。ユーザは統計情報を活用し、経路を読み解く必要がある。

オプティマイザと実行計画

DBMSがあらゆるSQLを受け取ると次のような手順を追う。

図解

パーサはユーザーからのSQL文を受け取る
ここでSQL分布が正しいかどウカをチェックする

オープティマイザはDBMSの脳である。SQLの最適な経路を考える。実行計画を考える。
　カタログマネジャーは実行計画で必要な統計情報を管理するモジュール。

カタログマネージャーから統計情報を受け取り、オプティマイザーが最適な経路を選択し、SQLの実行をする

統計情報の設計指針

上記のように、私たち ユーザは統計情報を頼りにSQLの最短・最適な経路を決定する部分があるため、 テーブル設計において、ユーザーにとって重要なポイントは統計情報の取得方法である。

1. 統計情報の収集のタイミング

2. 統計情報の収集の対象

   ◆統計情報の収集のタイミング
   タイミングはデータが大きく更新されるタイミング　
   SQL でいえば　update delete/insertであるレコード件数の増加はもちろんのこと,
   データ分布やデータの偏りが大きく変わることも、データの経路に関連することです。
   更新処理時に、大きく更新が変われば、古い情報と新しい情報の齟齬が生じる。間違った情報に基づいてはオプティマイザも正しい経路を選択できない。

統計情報の収集は、かなりリソースが取られてしまう。そのため、オンラインである日中より真夜中などに統計情報を収集した方がベストである。

◆統計情報の収集の対象
前述の通り、統計情報の収集の対象は「更新処理」です。更新処理で大きくデータが大きく変わる処理のタイミングです。
更新処理で大きくデータが変わらない普遍のテーブルは対象外です。理由は、統計情報の収集は、かなりコストがかかる処理であるからです。
いつ、更新処理が走るかについてはユーザ(エンジニア)はコードを読めばわかるため、わかりやすい。

しかし、気をつけなければいけないことがあります。それは一時的なテーブルです。こちらは後日後述いたします。