RDBMS入門

社内の若手（3〜5年目）向けにRDBMSについてディスクI/Oに着目して説明した勉強会の資料です。

Transcript

1. リレーショナルデータベース マネージメントシステム入門 〜 あるいは、なぜ非正規化するとSELECT文は遅くなるのか 〜 2019/08/16 なかじまゆうじ
2. 前提 • 今回の話はOracle Database 10g〜11gR2の仕組みをベースとしています。 • 他のRDBMSも同じような仕組みを持っていますが、多少異なるものもあります。 • 今日の話をベースにして自分の使っているRDBMSの仕組みを調べてみてください。
3. 今日のポイント • RDBMSで一番遅い処理はディスクI/O（Input / Output）です。 • ディスクI/Oが多いselect文は遅くなりますので、ディスクI/Oが少ないselect文を 書きましょう。 • ディスクI/O量はテーブル設計に依存する部分が大きいです。 • ディスクI/O量が少なくて済むテーブル設計をしましょう。
4. SELECT * FORM M_CODE WHERE NAME = 'タイヤ' 1つ目のselect文
5. SELECT文はどのように実行されるのか • はじめに、最近、全く同じselect文が実行されていないかを確認します。 • 同じselect文が実行されていた場合には、その時の結果を返します。 • この動作は、一部のRDBMSのみが行います。 • 次に、select文の実行計画を作成します。 • 最近、同じselect文が実行されていた場合には、その時の実行計画を使用します。 • 最後に、作成した実行計画に基づいてselect文を実行し、結果を返します。
6. 実行計画とは • 実行計画とは、「select文の結果を作成する処理の順番」のことです。 • selectの場合、テーブルの結合（from句）と絞り込み（where句）を行なった後、 グループ化（group by句）、射影（select句）、さらなる絞り込み（having句）、 ソート（order by句）の順番に処理されます。 • select文の構成によっては、ソートが結合や絞り込みと同時に行われる場合もありま す。 • テーブルの結合と絞り込みは、1ステップあたり1〜2テーブルを対象に行われ、 各ステップの結果である中間状態が最も少ない行数となる順番で実行されます。
7. SELECT文が遅くなる理由 • 取り扱う行数が多い • 実行計画を作る材料が間違っている • 良い実行計画を作るための材料がない • （単純に）返す行数が多い
8. 取り扱う行数が多いとなぜ遅いのか • 読み込むデータ量が多くなる • ディスクI/OやメモリI/Oが多くなる • 比較回数が多くなる • テーブルの結合も絞り込みもグループ化もソートもデータの比較を行っている • 中間状態を保存する大きなメモリ領域が必要となる • ディスクI/OやメモリI/Oが多くなる • メモリが足りない場合にはディスクを使用する場合もある
9. 実行計画を作る材料 • テーブル（の統計情報） • 各列に含まれる値の種類とその分布 • インデックス（の統計情報） • インデックスを使用することでテーブルをどの程度絞り込めるか • （噂では）Oracle Databaseはテーブルを5%以下に絞り込める場合にのみインデックス を使用するらしい。 • 制約（一意制約、参照整合性制約）
10. インデックスのないテーブルを対象とする実行計画 • 「select * from M_CODE where NAME = 'タイヤ'」 • M_CODEは、コードに対する名称が登録されたマスタテーブルです。 • NAMEにはインデックスがありませんので、NAMEが「タイヤ」かどうかを、 テーブル内の全ての行に対して確認する必要があります。 • このような動作をTABLEACCESS FULL（テーブルフルスキャン）と言います。 • テーブル内の全ての行を内容を確認するためには、全ての行を読み込む必要があ ります。
11. RDBMSはデータをどのように格納しているか • RDBMSはデータをOS上のファイルに保存しています。 • RDBMSは1つのファイルをデータブロックという単位に分割し、データブロック 単位で読み書きをしています。 • SQL Serverの場合は8KB、Oracle Databaseの場合は2KB、4KB、8KB、16KB、32KBから 選択するが通常は8KB。 • データブロックは、それぞれデータベースオブジェクト（テーブル、インデック ス、マテリアライズドビューなど）に割り当てられています。
12. RDBMSはデータをどのように格納しているか https://help.sap.com/doc/saphelp_46c/4.6C/ja-JP/08/5742084ae611d1894f0000e829fbbd/content.htm
13. データはデータブロックにどのように格納されてい るのか • テーブルの各行はデータブロックに列順で行 ごとに書き込まれています。 • 1つのデータブロックには複数の行が書き込ま れています。 • 1つの行が複数のデータブロックにまたがって 書き込まれることはありません。 https://docs.oracle.com/cd/E57425_01/121/CNCPT/logical.htm
14. データはデータブロックにどのように格納されてい るのか • 例えば、2Byte、3Byte、3Byteの3つのコード値と、1Byteの1つのフラグ、平均5文 字（10Byte）の1つの名称で構成されたテーブル（平均19Byte/行）は、1つのデー タブロックに431行のデータを格納することができます。（1データブロックを 8KBとした場合。） • 正確には、各データブロックには管理データや更新のための余白部分が存在するため、 もう少し少なくなります。 • もしテーブルが上記のような行だけで構成されており、かつ行数が431行以下の 場合、RDBMSは1つのデータブロックを読むだけでTABLE ACCESS FULLを行うこ とができます。
15. データはデータブロックにどのように格納されてい るのか • これが、上記に加え平均100文字（200Byte）の備考が存在した場合、1つの行の 平均長は219Byteとなり、1つのデータブロックには37行しか格納することができ なくなります。 • このため、備考がない場合に1つのブロックで格納できていたテーブルは、12 データブロックなければ格納できないことになります。 • これは、例え備考が必要なかったとしても、そのselect文を実行するためには12 データブロックの読み込み（12倍のディスクI/O）が必要であることを意味しま す。
16. データはデータブロックにどのように格納されてい るのか • このように非常に平均行長が長いことが原因でデータブロック数が多くなってし まう場合で、かつ通常はそのうち一部の列しか必要ない場合には、テーブルの垂 直分割を行うことで平均行長を短くするチューニングが行われれる場合がありま す。
17. テーブルの垂直分割 • テーブルを縦方向、つまり1つの行を複数のテーブルに分割することを垂直分割 と言います。 • 参照頻度や更新頻度、検索条件になるもの/ならないもの、値が設定される（null にならない）条件などにより列をグループ化し、それぞれ同じ主キーを持つテー ブルとして分割します。
18. テーブルの垂直分割 • こうすることによって、検索に必要な少数の列だけが格納された少数のブロック だけを読み込んで行の選択を行なった後、必要な列が格納されたブロックを読み 込むようになり、より少ないブロックの読み込みでselect結果が取得できるよう になります。 • なお、分割したテーブルのうち1つを主たるテーブルとし、他のテーブルの主 キーを主たるテーブルの主キーを参照する外部キーとして参照整合性制約を設定 することで、不必要な結合が優先的に行われることを抑止することが推奨されま す。
19. テーブルの正規化 • 一般的にデータの整合性確保のために要求されるテーブルの正規化ですが、一部 のカラムを別のテーブルに分離することになるので、テーブルの垂直分割と同じ 効果が得られます。 • ただし、参照整合性制約を設定していないと、不必要な結合が優先的に行われ、 結果としてselect文が遅くなる場合もあります。
20. 余談：列指向データベース • 一般的なRDBMSは行単位でブロックに格納していますが、BigQueryなどのDWH （Data Ware House）向けのDBMSでは列単位で格納しているものがあります。 • これらのDBMSのことを列指向（カラムナー）データベースと言います。 • 列指向データベースは、列を制限した状態での操作（選択や集計）に強く、行単 位での更新に弱いという特徴があります。
21. 余談：テーブルの水平分割 • 反対に横方向に分割することを水平分割と言います。 • こちらは「パーティション」と言った名前でRDBMSに機能として実装されている 場合があります。 • 水平分割は複数ディスクへのディスクI/O分散やTABLEACCESS FULLを部分的に行 わせる、Hot DataとCold Dataを分離しディスクキャッシュを効率的に使用するな どの目的で使用されます。
22. SELECT * FROM T_SALARY WHERE YEAR = 2019 AND MONTH = 4 2つ目のselect文
23. T_SALARYテーブルの構造 • T_SALARYテーブルは、YEAR、MONTH、EMPLOYEE_CODEの3つの列の組みが主 キーとなっています。 • 主キーに含まれる列には必ずnot null制約が設定されるとともに、主キーを構成す る列の組に対して一意制約が設定されます。 • 一意制約が設定された列の組みには、通常、一意インデックスが作成されます。
24. 2つ目のSELECT文 • 「select * from T_SALARY where YEAR = 2019 and MONTH = 4」 • このselect文の場合、一意インデックスの先頭2つの列が指定されていますので、 インデックスを読み込むだけで行を絞り込むことができます。（インデックスが B-Treeの場合。） • 全ての行を確認する必要もないので、比較回数も少なくて済みます。 • select結果を作るためにテーブルも読み込む必要がありますが、必要な行が入った データブロックだけで済みます。 • このようなインデックスの一部だけを使用して絞り込む方法をINDEX RANGE SCAN と言います。
25. B-TREEインデックスの構造 • B-Tree（Balanced Tree）インデックスは木構造をしています。 • B-Treeインデックスのリーフノードには、値とROWIDの組みが格納されています。 • ROWIDとは各行のデータブロックとその内部での位置を示すアドレスのようなもの。 • ルートノードやブランチノードには、値の範囲とその範囲のデータを格納してい るブランチノード（もしくはリーフノード）へのポインタが格納されています。
26. B-TREEインデックスの構造 1...8 9...21 22...43 1...3 4...8 9...13 14... ... 22... ... 1:rowid 2:rowid 3:rowid 4:rowid ... ... ルートノード ブランチノード リーフノード rowid:data rowid:data ... rowid:data rowid:data ... rowid:data rowid:data ... 実表
27. B-TREEインデックスの構造 • 例えば、1,000,000行のデータが1データブロック20行ずつ計50,000ブロックに格納 されているとします。しかしインデックスに使用される列は短くて1ブロックに 1,000行格納できるとすると、リーフノードは1,000ブロック、その上はルート ノード1ブロックで済むことになります。 • この場合、TABLE ACCESS FULLでは50,000ブロックの読み込みが必要ですが、 INDEX UNIQUE SCAN（インデックスによる単一選択）では、ルートノード、 リーフノード、データが格納されているブロックの合わせて3ブロックの読み込 みで済みます。
28. B-TREEインデックスの構造 1...3 4...8 1:rowid 2:rowid 3:rowid 4:rowid ... ルートノード リーフノード rowid:data rowid:data ... rowid:data rowid:data ... rowid:data rowid:data ... 実表
29. インデックスが複数列で構成される場合 • 複数列で構成されるインデックスのことを複合インデックスと呼びます。 • 複合インデックスの場合、その（インデックス内での）列順が重要です。 • 複合インデックスでは、まず初めに指定された列でB-Treeが作成されます。ただ しリーフノードにはROWIDではなく、2つ目に指定された列によって作成された B-Treeのルートノードへのポインタが格納されます。
30. インデックスが複数列で構成される場合 • 2つ目に指定された列によって作成されるB-Treeは、1つではなく1つ目の列に格 納された値の種類と同じ数だけ作成されます。 • 2つ目に指定された列によって作成されたB-Treeのリーフノードには、それぞれ1 つめに指定された列によって絞り込まれた結果に含まれる2つめに指定された列 の値だけが格納されます。 • 3つ目以降に指定された列も、2つ目に指定された列と同様に、それより先に指定 された列によって作成されたB-Treeのリーフノードにつながる形で作成されます。
31. インデックスが複数列で構成される場合 1...3 4...8 1: 2: 3: 4: ... 1列目のツリー 1...3 4...8 1: 2: 3: 4: ... 2列目のツリー 1...3 4...8 1:rowid 2:rowid 3:rowid 4:rowid ... 3列目のツリー 1: ... 1...3 4...8 1...3 4...8 1...3 4...8 1...3 4...8 1:rowid ...
32. インデックスが複数列で構成される場合 • このような構造であるため、3列による複合インデックスのうち先頭2列だけを指 定した絞り込みはインデックスを使用できます（INDEX RANGE SCAN）が、2列 目と3列目を使用した絞り込みにはインデックスを（効率的には）使用できませ ん（INDEX FULL SCAN）。
33. インデックスが複数列で構成される場合 （上位2列指定） 1...3 4...8 1: 2: 3: 4: ... 1列目のツリー 1...3 4...8 1: 2: 3: 4: ... 2列目のツリー 1...3 4...8 1:rowid 2:rowid 3:rowid 4:rowid ... 3列目のツリー 1: ... 1...3 4...8 1...3 4...8 1...3 4...8 1...3 4...8 1:rowid ...
34. インデックスが複数列で構成される場合 （下位2列指定） 1...3 4...8 1: 2: 3: 4: ... 1列目のツリー 1...3 4...8 1: 2: 3: 4: ... 2列目のツリー 1...3 4...8 1:rowid 2:rowid 3:rowid 4:rowid ... 3列目のツリー 1: ... 1...3 4...8 1...3 4...8 1...3 4...8 1...3 4...8 1:rowid ...
35. SELECT SUM(SALARY) FROM T_SALARY WHERE YEAR = 2019 AND MONTH = 4 3つ目のselect文
36. 3つ目のSELECT文 • 「select sum(SALARY) fromT_SALARY whereYEAR = 2019 and MONTH = 4」 • このselect文の場合、一意インデックスの先頭2つの列が指定されていますので、 インデックスを読み込むだけで行を絞り込むことができます。（インデックスが B-Treeの場合。） • しかし、SALARYを読み込むために必ず実表を参照する必要があります。
37. インデックスが複数列で構成される場合 （上位2列指定） 1...3 4...8 1: 2: 3: 4: ... 1列目のツリー 1...3 4...8 1: 2: 3: 4: ... 2列目のツリー 1...3 4...8 1:rowid 2:rowid 3:rowid 4:rowid ... 3列目のツリー 1: ... 1...3 4...8 1...3 4...8 1...3 4...8 1...3 4...8 1:rowid ... rowid:data rowid:data ... rowid:data rowid:data ... rowid:data rowid:data ... 実表
38. カバリングインデックス • インデックスを使用した選択の場合、最後はリーフノードに格納されたROWID を使って実際のデータが格納されているデータブロックにアクセスします。 • しかし、選択結果の行数が多く、かつ1つのデータブロックに少ない行しか格納 できていない場合、実際のデータが格納されているデータブロックへのアクセス のためにディスクI/Oが多くなってしまいます。 • このような場合、select文に必要な全ての列をインデックスに含めることで実際 のデータが格納されているデータブロックへのアクセスを行わなくて済むように する方法があります。これをカバリングインデックスと呼びます。
39. カバリングインデックス 1...3 4...8 1: 2: 3: 4: ... 1列目のツリー 1...3 4...8 1: 2: 3: 4: ... 2列目のツリー A...B C...D A:rowid, rowid,... B:rowid,... C:rowid,... ... 3列目のツリー 1: ... 1...3 4...8 1...3 4...8 A...B C...D A...B C...D Aチー ム :rowid... rowid:data rowid:data ... rowid:data rowid:data ... rowid:data rowid:data ... 実表
40. 「RDBは、物理層隠蔽のかなり早い成功事例と言わ れる。確かに、物理的な概念を抽象化することで、 ユーザフレンドリーになったのは事実。しかし、パ フォーマンス観点からは、物理と論理の分離に成功 したとは言い難い。論理設計（ERモデリング、 UI/UX、そこから生成されるSQL）は、今に至るま で物理から独立に行うことはできなかった。」 BY ミック at Database Lounge Tokyo #5 (2017/09/19) http://mickindex.sakura.ne.jp/database/pdf/DBLounge_20170919.pdf
41. 「ディスクI/Oを少なくする」以外の方法 • ここまで「ディスクI/Oは遅いので、いかにしてディスクI/Oを少なくするか」と いう話をしてきました。 • しかし、そもそもディスクI/Oが遅くなければ、このようなことを考える必要は ありません。 • ではディスクI/Oを速くする方法はないのでしょうか。
42. なぜディスクI/Oには時間がかかるのか • ディスクI/Oに時間がかかるのは、 読みたいデータが書き込まれて いる場所までヘッドを動かし、 ディスクが回転してくるのを 待っているからです。 https://www.fujitsu.com/jp/products/computing/storage/lib-f/tech/beginner/disk/index.html
43. ディスクI/Oを高速化する方法 • 1つは、ディスクの回転速度をあげることです。 • 一般的な3.5inch HDDは7,200rpmで回転しています。 • これを15,000rpmとすることで約2倍の速度でデー タを読み込むことができます。 https://www.atmarkit.co.jp/fwin2k/experiments/defragment/defragment_1.html
44. ディスクI/Oを高速化する方法 • もう一つは、大きなデータを1か所から読み込むの ではなく、小さなデータを複数から並行して読み 込む方法です。 • これをストライピング（もしくはRAID 0）と呼び ます。 https://www.fujitsu.com/jp/products/computing/storage/eternus/glossary/raid/08.html
45. ディスクI/Oを高速化する方法 • そもそも可動部品が物理的に動いていると、動かせる速度に限界があるため、そ れが読み込み速度の限界になります。 • そこで可動部品がない記憶装置が考えられました。 • それが不揮発性メモリを使用したSSD（Solid State Disk）です。
46. RDBMS側でのディスクI/O高速化の取り組み • RBDMSの開発側もディスクI/Oが遅いことを知っているので、RDBMSには専用の ディスクキャッシュが搭載されています。 • キャッシュ単位はディスクI/Oの単位と同じなので、データブロック単位です。 • 単純なFIFO（First-In, First-Out）ではなく利用頻度を加味した優先順位付けを行っ ています。 • select文のオプションで意図的に「キャッシュに載せない」といった制御も行え る場合があります。
47. まとめ • select文の実行時間は実行計画次第。 • ディスクI/Oと比較回数が少ない実行計画が作れれば、select文は速くなる。 • 非正規化はディスクI/Oを増やして比較回数を減らす方法。 • ディスクI/Oを減らすには、平均行長、インデックス、制約の検討が重要。 • ディスクI/O自体を速くする手段もある。
48. 参考文献
49. 参考文献
50. 参考文献
51. 参考文献 https://use-the-index-luke.com/ja
52. N+1問題 最後のキーワード
53. 余談：AUTO STRAGE MANAGEMENT • Oracle Databaseの場合は、ASM（Auto Strage Management）によってRBDMSが直接、 OSを介さずにディスクを管理することができます。 • OSのディスクI/O機能を使用しないことで、よりOracle Databaseに最適化された ディスクI/Oが行えます。 • 概念的には、このディスクが「大きな1つのファイル」のように扱われます。
54. 余談：ハイウォーターマーク • Oracle Databaseは、そのブロックに格納されている行が全て削除されても、その ブロックは「そのテーブル用」として確保したままにします。 • Oracle Databaseは、TABLEACCESS FULLの場合、データが存在しようとしまいと に関わらず、そのテーブルに割り当てられた全てのデータブロックを読み込みま す。 • このため、大量のデータをinsertし、その後、そのほとんどを削除したテーブル に対してTABLE ACCESS FULLを行うと、非常に多くの無駄なディスクI/Oが発生し ます。
55. 余談：ハイウォーターマーク https://docs.oracle.com/cd/E57425_01/121/CNCPT/logical.htm