理屈で考える、データベースのチューニング / Database tuning How-To

Oracle データベースの内部構造に着目して、さらなるチューニングを行うために必要な基礎知識をまとめた資料です。

Transcript

1. 理屈で考える、 データベースのチューニング 第1回 Oracle DBのさらなるパフォーマンスチューニングに必要な基礎知識 2020年09月30日 ラクーンホールディングス 羽山 純 1
2. はじめに • DBの内部構造を知りましょう • 処理速度・処理時間を具体的な数値でイメージしましょう ◦ その数値は多少間違ってても問題ありません • 各処理を分析すると問題の原因を把握できます • 分析のためには内部構造の知識が生きてきます • 問題の原因が分かれば解決策を考えられます ◦ やみくもに解決策を考えるのは風邪で胃薬を飲むようなもの ◦ DBの中でなにが起きているのか理解しましょう • 計測結果を疑う、みかけの数値に騙されないこと ◦ パフォーマンス測定では内部構造を知らないと騙されやすい ◦ 2回目以降の実行は速いなど問題の再現性が低いことがある 2
3. アジェンダ • DBの性能を知る • 内部構造を知る • データブロックを理解する • ROWIDとは何か • MySQLとの比較 • レコードサイズとパフォーマンス • レコードの分散 • 10年後を考える 3
4. DBの性能を知る 4
5. ストレージの性能を知る • DB性能で重要なのがストレージのランダムI/O性能 • ここでは 4,000 IOPS(I/O per Second)として考える （※ 性能としては少し遅めの水準） • 1分間に行えるI/Oが24万回、これが絶対的な限界値 • 具体的な数値でイメージすることが大切 4,000回/秒 （= 24万回/分） 5
6. 4,000 IOPS とはどの程度の性能か • AWS RDS で Multi-AZ 4,000 IOPS プロビジョンドIOPS ストレージを割り当てると月10万円以上かかる ➢ 価格から分かるように低すぎる性能ではない • ストレージI/O 1回を8KBとする（≒ランダムアクセス） 1秒間 ⇒ 8KB × 4,000回 ≒ 32MB/秒 1分間 ⇒ 32MB × 60秒 = 1,920MB ≒ 2GB/分 プロビジョンド IOPS ストレージ 4,000 IOPS Multi-AZ アジアパシフィック(東京) Multi-AZ プロビジョンド IOPS 料金 ⇒ 毎月 0.24USD/IOPS 4,000 プロビジョンドIOPS x 0.24 USD = 960.00 USD/月 2020年9月現在 6
7. 4,000 IOPS / 8KBブロックの読み込み速度 1秒間 8KB × 4,000回 ≒ 32MB 1分間 32MB × 60秒 ≒ 2GB 7
8. ストレージ性能と最適化 • 2GBのテーブルを読み込むには1分かかる、意外と遅い • 実際の読み込みでは様々な最適化がある ◦ DBバッファキャッシュ ◦ マルチブロックリード（※後ほど説明） • DBバッファキャッシュはメモリ上に確保されるキャッシュ ◦ メモリ読み込みはストレージよりも圧倒的に速い 読み込み済のデータを メモリに保持する ストレージ DBバッファキャッシュ 8
9. DBバッファキャッシュ • OLTP（オンライントランザクション）で利用されるDBでは DBバッファキャッシュのヒット率99%以上が普通 • ヒット率が高いならストレージ性能は重要ではない？ ⇒ NO、たった1%のストレージ読込がボトルネックになる 読込データ量の割合 ストレージ読込 1% DBバッファ キャッシュ 99% 9
10. DBバッファキャッシュ • 読み込み速度が「メモリ : ストレージ = 1,000 : 1」とする メモリ読込で1秒の処理 ⇒ 以下の条件でかかる時間は？ ◦ キャッシュヒット率 100% (⇒ メモリ 100%) ⇒ 1秒 ◦ キャッシュヒット率 0% (⇒ ストレージ 100%) ⇒ 1秒 × 1,000倍 = 1,000秒 ◦ キャッシュヒット率 99% (⇒ メモリ99% ストレージ1%) ⇒ 0.99秒 + (0.01秒 × 1,000倍) = 10.99秒 ≒ 11秒 難しいなぁ・・・ 10
11. 読み込み速度比 ⇒ メモリ : ストレージ = 1,000 : 1 キャッシュヒット率 ⇒ 99% 読込時間と所要時間の関係 読込データ量の割合 ストレージ 1% DBバッファ キャッシュ 99% 所要時間の割合 ストレージ 91% DBバッファキャッシュ 9% 11
12. • 汎用目的のDBは「I/Oバウンド」な状態が多い • CPUは休みながらストレージ読み込みを待って処理する • I/Oバウンドならば「ストレージ性能」≒「DBの処理性能」 I/Oバウンド ストレージは遅い CPUは速いがデータがこない ストレージを待ちながら処理 ストレージ性能 ≒ 処理速度 12
13. CPUバウンド • 「CPUバウンド」になりやすいDBは ◦ データがすべてメモリに収まる（メモリ多い、データ少ない） ◦ 利用用途が限定的でキャッシュヒット率が高い • 結果としてパフォーマンス的な課題がさほどない メモリは速い CPUの速度に負けないスピードで データが来る 13
14. CPU性能について • 大抵のDBはI/Oバウンド、今回はI/OバウンドなDBを扱う • I/Oバウンドの場合、 CPU性能はDBパフォーマンスにさほど影響しない CPU性能が良くてもヒマで寝てるだけ 14
15. 内部構造を知る 15
16. なぜDBの内部構造の理解が必要か？ • 内部構造を知らなくてもSQLを知ればデータを取り出せる • アプリケーションはSQLを介して、DBの内部構造と疎結合 • 開発者はSQLを介して、DBの内部構造と疎結合 • 開発にsqlite、本番でMySQLなどDB実装の切り替えも可能 だけれども 16
17. • パフォーマンスの領域では内部構造の理解が不可欠 • DBは魔法の箱ではない、無理な処理をすれば時間がかかる • 例えば、順不同に並ぶ英和辞典で単語を探す ◦ 単語を見つけるまで最初のページから全て探す必要がある ◦ 荒唐無稽に聞こえるがDBでは意外とやりがち ➢ 実はテーブルフルスキャンしてるSQL ◦ 見出し語のアルファベット順に並んでれば一瞬で探せる ➢ DBでもインデックスがあれば速くなる ◦ DBの立場になって、その処理に無理がないか考える なぜDBの内部構造の理解が必要か？ 英和辞典 17 順不同に並ぶ英和辞書
18. なぜDBの内部構造の理解が必要か？ • 人は無意識に道具の内部構造を考えて最適な利用をする ◦ DBではこの仕事をオプティマイザが担当 • フランスの旅行ガイドブックから「ラメールプラール」とい うオムレツ屋を探す 1. モンサンミシェル⇒レストランとたどって探す 2. 巻末索引から探す 3. 最初から最後まで全ページ探す 1. 2. 索引 巻末索引で探す 地域を絞って探す 18
19. • 旅行ガイドブックの構造はDBの構造に近い ◦ 本編・観光情報（⇒テーブルのデータ） ◦ 本編の掲載順番は決まりがない（⇒レコードの保存順） ◦ 名称から探すために巻末索引がある（⇒インデックス） ◦ 巻末索引には本編への参照がある（⇒ROWID ※後述） なぜDBの内部構造の理解が必要か？ 索引 巻末に索引がある 観光情報は順不同 19
20. なぜDBの内部構造の理解が必要か？ • 図書館で本を探す場合 ◦ 蔵書のエリア配置・並び順を把握していて最短でたどり着く ◦ 情報端末で蔵書検索をする • たどり着き方は複数あり、どれが最適かは状況により変わる • DBは最適な方法を選択（=オプティマイザ）してくれる ◦ 開発者はDBの立場になって「どう処理するか？」を考える • DBとして最適は全体の最適ではない、開発者の手助けが必要 ◦ そもそも必要のない処理である可能性 ◦ さらに効率的な絞り込み条件を追加できる ◦ 運用を変更したら効率的な処理にできる ◦ バッチ処理だから処理速度が遅くてもよい • DBの内部構造を知らないと開発者はうまく手助けできない 20
21. データブロックを理解する 21
22. テーブルのデータは、ストレージの表領域のセグメントの エクステントのデータブロックに保存される ⇒知らなくてOK Oracle DBのデータ構造 表領域 セグメント 表領域 セグメント セグメント セグメント セグメント セグメント セグメント セグメント エクステント エクステント エクステント エクステント エクステント エクステント データブロック ストレージ 22
23. • 覚えてもらいたいのは「データブロック」だけ • データブロックのサイズは選択できる ◦ 2KB, 4KB, 8KB, 16KB, 32KBなど • テーブルやインデックスの実データが入っている • データブロックがOracleにおけるI/Oの最小単位 • DBバッファキャッシュへのキャッシュもデータブロック単位 • 今回は8KBとする データブロック データブロック レコードデータ 空き領域 23
24. • テーブルの各レコードはデータブロックに保存される • データブロックはCREATE TABLE時に割り当てられ、空き データブロックがなくなると、再度割り当てられる • 1つのデータブロックには複数レコード入る データブロック データブロック ID Name Price Updated 1 AA 100 2012-10-04 2 BB 53 2014-12-01 3 CC 500 2018-06-12 4 DD 12 2019-03-28 5 EE 212 2020-05-03 ... ... ... ... 24
25. • データブロックのうち大部分はレコードデータに利用 • カラム型や保存するデータによって消費サイズが異なる • VARCHAR2はバイト列分を消費、数値型も可変長 • 数値カラムを9個持つテーブルで1行がおおよそ36byteなら ◦ 8KBのデータブロック1つには200レコード以上入る ◦ 1回のI/Oで200レコードも取得できる データブロック データブロック 25 数値カラムを9個ほど持つ テーブルなら1ブロックに 200レコード程度入る
26. • 1ブロックに200レコード入るテーブルを考える • 200万レコード取得する場合、200万 ÷ 200 = 1万ブロック • 4,000 IOPS なので、10,000 ブロック ÷ 4,000 IOPS = 2.5秒 • （うまくいけば）2.5秒で200万レコード取得できそう • 具体的な数値で考えることが重要、おおよそで問題なし ◦ ただしマルチブロックリードならもっと速い データブロック データブロック 26 1ブロックに200 レコード入るとする
27. データブロックに関する問題 Q: 注文でレコードが挿入される「注文テーブル」がある。 「2019年5月の注文」と「全期間の電化製品ジャンルの注文」 が共に約200万レコードだったとすると、それぞれの条件で 200万レコード取得にかかる時間に差はあるか？ （※「注文日」及び「ジャンル」にはインデックスがあるものとする） 1. 「2019年5月の注文」の方が早く取得可能 2. 「全期間の電化製品ジャンルの注文」の方が早く取得可能 3. 両方ともほとんど差はない 27
28. データブロックに関する問題 A: 「2019年5月の注文」の方が早く取得可能 これはなぜか？ • レコード挿入は空きデータブロックに対して連続で行われる • その結果、同一タイミングで挿入されたレコードは同一の データブロックに含まれやすい • 「注文日」と「レコード挿入タイミング」には相関があり 同一ブロック内に条件に当てはまるデータが連続しやすい ◦ データブロック1つに200レコード含むなら、その大半が条件に 当てはまるレコードとなりやすい ◦ 200万 ÷ 200 = 1万ブロック の取得で済む ◦ 分散していると200万ブロック取得が必要 28
29. データブロックの分散 • ジャンルで絞り込む場合は、テーブル全体にバラバラに レコードがちらばっている • 1データブロック取得しても、必要なレコードは数行しか含ま れない、8KBのブロックのうち大半が必要のないレコード 注文日での絞り込み 必要なレコードが1つのブロック内に 連続している 200レコード利用 200レコード利用 200レコード利用 ジャンルでの絞り込み 必要なレコードが様々なブロックに 分散している それぞれ数レコード利用 29
30. • 使用可能ブロックは挿入可能、使用済みブロックは挿入不可 • 空き領域が残り少なくなると使用済みブロックに変化する • DELETEなど、十分な空き領域ができると使用可能へ戻る 同一ブロックにデータが集まる理由 データブロック データブロック 空き領域が十分ある場合は 新しいレコードを入れる 空き領域が少ない場合は 新しいレコードを入れない 空き領域 空き領域 30
31. 使用可能/使用済みの状態遷移 使用可能 31 使用可能 使用済み INSERT 使用済み INSERT UPDATE DELETE 使用済み DELETE INSERT 使用可能 INSERT 空き領域が一定未満に なるまでは使用可能 INSERT INSERT INSERT INSERT INSERT 十分空きができるま では使用済みのまま ① ② ③ ④ ⑤ ⑥
32. データブロックサイズの考察 • データが分散している場合 ◦ 各データブロックに必要なレコードが数行しか含まれない ◦ 沢山のデータブロックを取得する必要がある ◦ それならば、ブロックサイズを小さくした方が有利？ ➢ 大抵の場合はNO、なぜならば • 一般的なDBストレージ（SAS/iSCSI/FCなど）はブロックサ イズを変えてもIOPSはさほど変化しない ◦ 4KB ⇒ 2KB IOPSにさほど影響なし ◦ 8KB ⇒ 16KB IOPSが1～2割程度の減少（あくまで一例） 32
33. ブロックサイズとIOPSとスループット • 厳密な数値は考えず、おおまかに理解する • ブロックサイズが 8KB ⇒ 16KB に変化した場合、 IOPSは2割減少すると考える 4,000 IOPS ⇒ 3,200 IOPS • スループットはブロックサイズが大きい方が有利 16KB 8KB 4,000 IOPS ブロックサイズ IOPS 3,200 IOPS 32MB/秒 51MB/秒 スループット 33 2倍 0.8倍 1.6倍
34. 大きいブロックサイズの考察 • ブロックサイズが大きいと IOPS は若干減少する • 1レコードだけ取得する場合は不利 ◦ 巨大ブロックを読み込んでも、利用するのは1レコードだけ ◦ INDEX UNIQUE SCAN ⇒ TABLE ACCESS BY INDEX ROWID たった1レコードのために ブロック全体を取得する データブロック 大半のデータは 読み込んでも使わない 34
35. 大きいブロックサイズの考察 • ブロック内で連続するレコードを取得する場合は有利 ◦ 日付範囲などは必要なレコードがブロック内で連続しやすい ◦ 同時期に挿入されたレコードは同一ブロック内に固まりやすい ◦ ブロック読込回数が減り、スループットが高くなる データブロック 取得したブロック内のデータは ほとんどが必要なレコード 35
36. • DBバッファキャッシュの無駄が多い ◦ キャッシュするのはデータブロック単位 ◦ 1つが大きいためキャッシュできるデータブロック数が少ない 大きいブロックサイズの考察 データブロック データブロック DBバッファキャッシュ 1ブロックが大きいため 入るブロック数が少ない 36
37. 小さいブロックサイズの考察 • ブロックサイズが小さいと IOPS は多少増加する • 1レコードだけ取得する場合は有利 ◦ 読み込んだけど利用しないデータが最小 ◦ INDEX UNIQUE SCAN ⇒ TABLE ACCESS BY INDEX ROWID データブロック ブロックが小さいため 無駄が少ない 37
38. 小さいブロックサイズの考察 • ブロック内で連続するレコードを取得する場合は不利 ◦ 日付範囲などは必要なレコードがブロック内で連続しやすい ◦ 同時期に挿入されたレコードは同一ブロック内に固まりやすい ◦ ブロック読込回数が増え、スループットが低くなる データブロック ブロックが小さいため 沢山のブロック読込が必要 38
39. • フルスキャンの場合だけ、スループットを高くできる ◦ マルチブロックリード機能で複数ブロックを同時取得 ◦ TABLE ACCESS FULL, INDEX FAST FULL SCAN ◦ しかしフルスキャンが許されるのはバッチ処理くらい 小さいブロックサイズの考察 複数ブロックを1回のI/Oで 取得するので速い データブロック ざくっと一気に マルチブロックリード 狙ったブロックを１つずつ 通常リード 39
40. • DBバッファキャッシュの無駄が少ない ◦ キャッシュするのはデータブロック単位 ◦ 小さいため沢山のデータブロックをキャッシュ可能 小さいブロックサイズの考察 DBバッファキャッシュ データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック 1ブロックが小さいため 沢山のブロックが入る 40
41. ブロックサイズはなにが正解か？ • ブロックサイズは表領域単位で変更できる、 可能ならばテーブルごとにチューニングするのがベスト • しかし現実的には難易度が高い、実施コストが高くなりがち • 開発者としてはDBのブロックサイズを把握して、 それに合わせたチューニングを行うのがベター ◦ 構造的にパフォーマンスがでないからバッチ処理にする ◦ 1ページで取得するアイテム数を減らして速度をだす ス◦ーピー「配られたカードで勝負するしかないのさ」 孫子「彼を知り己を知れば百戦殆うからず」 41
42. ROWIDとは何か 42
43. ROWIDとは • ROWIDはDBストレージ内の住所のようなもの • データブロックとブロック内の位置などを特定可能 • ROWIDが分かれば1回のI/Oで必要なレコードを取得できる 必要なデータブロックの 場所を特定できる 膨大な数のデータブロック 43
44. ROWIDの構造 • 「AAAVREAAEAAAACXAAA」 という形式 ◦ 6桁: AAAVRE ⇒ データオブジェクト番号 ◦ 3桁: AAE ⇒ 相対ファイル番号 ◦ 6桁: AAAACX ⇒ データブロック番号 ◦ 3桁: AAA ⇒ 行番号 • dbms_rowidパッケージで解析できる select rowid, dbms_rowid.rowid_object(rowid) as data_object_id, dbms_rowid.rowid_relative_fno(rowid) as relative_fno, dbms_rowid.rowid_block_number(rowid) as block_number, dbms_rowid.rowid_row_number(rowid) as row_number, scott.emp.empno, scott.emp.ename from scott.emp ; 44
45. • scott.emp テーブルのROWIDを解析してみる • 各レコードはすべてファイル番号4、データブロック番号151 に入っていることが分かる • このテーブルはフルスキャンに必要なI/O回数が1回と分かる ROWIDの解析例 ROWID DATA_OBJECT_ID RELATIVE_FNO BLOCK_NUMBER ROW_NUMBER EMPNO ENAME AAAVREAAEAAAACXAAA 87108 4 151 0 7369 SMITH AAAVREAAEAAAACXAAB 87108 4 151 1 7499 ALLEN AAAVREAAEAAAACXAAC 87108 4 151 2 7521 WARD AAAVREAAEAAAACXAAD 87108 4 151 3 7566 JONES AAAVREAAEAAAACXAAE 87108 4 151 4 7654 MARTIN AAAVREAAEAAAACXAAF 87108 4 151 5 7698 BLAKE AAAVREAAEAAAACXAAG 87108 4 151 6 7782 CLARK AAAVREAAEAAAACXAAH 87108 4 151 7 7788 SCOTT AAAVREAAEAAAACXAAI 87108 4 151 8 7839 KING AAAVREAAEAAAACXAAJ 87108 4 151 9 7844 TURNER AAAVREAAEAAAACXAAK 87108 4 151 10 7876 ADAMS AAAVREAAEAAAACXAAL 87108 4 151 11 7900 JAMES AAAVREAAEAAAACXAAM 87108 4 151 12 7902 FORD AAAVREAAEAAAACXAAN 87108 4 151 13 7934 MILLER 45
46. ROWIDはどこで使われるか • テーブルはヒープ表と呼ばれる • レコードは任意のブロックに格納される • 各レコードはROWIDによって格納位置を識別できる • では、ROWIDはなにに使われるのか？ 各レコードはROWIDを持つ データブロック ID Name Price Updated 1 AA 100 2012-10-04 2 BB 53 2014-12-01 3 CC 500 2018-06-12 4 DD 12 2019-03-28 5 EE 212 2020-05-03 ... ... ... ... 46
47. • ROWIDの主な利用用途はインデックス • B+tree構造で「インデックス対象のデータ値」と 「レコードへのROWID（参照）」を持つ ROWIDはどこで使われるか 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ ブランチブロック リーフブロック データ値とROWIDを持つ 47
48. • 例えば ID=232 のレコードを探す場合を考える ◦ ブランチブロックを2つたどる ⇒ リーフブロックでROWIDを 取得 ⇒ ROWIDでテーブルのレコードを取得 ROWIDはどこで使われるか 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ ブランチブロック リーフブロック このROWIDでテーブル からレコードを取得 48
49. プライマリキーでレコード取得 • プライマリキーでレコードを取得する例 ◦ インデックスのブランチノード取得 × 2回 ◦ インデックスのリーフノード取得（ROWIDを取得） ◦ ROWIDからテーブルのレコード取得 ➢ 計4回程度I/Oのラウンドトリップ、遅くないけど速くもない • テーブルからデータを直接取得する場合 ◦ マルチブロックリード機能、高いスループットで取得可能 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ 3 CC 500 2018-06-12 インデックス走査 レコード取得 49
50. • それぞれ10,000レコード取得に必要なコストを考える • インデックス走査とレコード取得が10,000レコード分必要 • ブランチノードはキャッシュのヒット率 が高い テーブル直接とインデックス経由のレコード取得 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ 3 CC 500 2018-06-12 インデックス走査 レコード取得 10,000回 インデックス経由で10,000レコード取得する場合 50
51. • テーブルの場合はマルチブロックリード機能で 複数ブロック・複数レコードをまとめて取得可能 • ただしテーブル内の全行取得が必要 • どちらが速いかは状況次第 ◦ 全体で5万行程度なら？ブロックの分散状況は？ ◦ 1レコードが巨大なら？逆に小さいなら？ • 時間経過でレコードが増加するならインデックス利用が必須 ◦ マルチブロックリードが速くてもいつかは破綻する テーブル直接とインデックス経由のレコード取得 テーブルから直接10,000レコード取得する場合 ID Name Price Updated 1 AA 100 2012-10-04 2 BB 53 2014-12-01 3 CC 500 2018-06-12 4 DD 12 2019-03-28 5 EE 212 2020-05-03 ... ... ... ... 1回のI/Oで複数行取得できる 51
52. MySQL(InnoDB)との比較 52
53. なぜ他のDBを知る必要があるのか • プロダクトとして長期間生き残り利用されているDBは それだけの間エンジニア達によって磨かれている • 各DBの構造は違いがあり速度特性も異なる、ただそれは特徴 の違いであり、どちらかが圧倒的に優れているわけではない • いかなるDBも銀の弾丸で高速化されているわけではない、 速いのには理由があるし、遅いのにも必ず理由がある • 各DBの構造の違いを知ることは、チューニングにおける限界 点を理解する助けになる • 卓越したエンジニア達が磨いてきたプロダクトには「存在す る理由」がある 53
54. • InnoDBのテーブルはクラスタインデックス構成 • テーブル全体がB+treeインデックス構造になっていて、 レコードのデータをリーフページに持つ MySQL(InnoDB)との比較 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,AA,100,2012 3,BB,120,2019 … 10,CC,99,2020 11,DD,210,2009 16,EE,180,2010 … 25,FF,510,1980 231,KK,615,2015 232,LL,880,2002 … 250,PP,980,1995 ・・・・・・・・・ 非リーフページ リーフページ インデックス構造で レコードデータも持つ 54
55. • プライマリキーでレコード取得する場合はインデックス走査 だけでレコードも取得できて有利 • Oracleの場合はROWIDからレコードを取得する一手間が必要 MySQL(InnoDB)との比較 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,AA,100,2012 3,BB,120,2019 … 10,CC,99,2020 11,DD,210,2009 16,EE,180,2010 … 25,FF,510,1980 231,KK,615,2015 232,LL,880,2002 … 250,PP,980,1995 ・・・・・・・・・ 非リーフページ リーフページ インデックス走査だけで レコードデータも取得可能 55
56. • InnoDBにはROWIDにあたるものがない • セカンダリインデックス（プライマリキー以外のインデック ス）は、レコードのポインタとしてプライマリキー値を持つ • セカンダリインデックスはインデックス走査が2回必要、 ROWIDでダイレクトに取得できるOracleよりも遅い • プライマリキーのデータサイズが大きいと無駄が多くなる InnoDBにおけるセカンダリインデックス 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ セカンダリインデックス走査 （プライマリキー値の取得） 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,ACXAAA 3,ADXAAC … 10,ADXABG 11,ADXAAD 16,AEAAAA … 25,AEBAAB 231,AGXAAC 232,AHAAAB … 250,AHBAAK ・・・・・・・・・ クラスタインデックス走査 （レコードの取得） 56
57. • クラスタインデックスのため、テーブルフルスキャンは プライマリキー順に取得される ⇒ 開発者に優しい • Oracleと比べてチューニングの余地は制限される MySQL(InnoDB)との比較 0..40 41..80 81..250 0..10 11..25 26..40 41..51 52..80 81..121 122..230 231..250 0,AA,100,2012 3,BB,120,2019 … 10,CC,99,2020 11,DD,210,2009 16,EE,180,2010 … 25,FF,510,1980 231,KK,615,2015 232,LL,880,2002 … 250,PP,980,1995 ・・・・・・・・・ 先頭から順に取得 57
58. レコードサイズとパフォーマンス 58
59. 1レコードのサイズ • レコードのデータはデータブロックに格納される • ブロックサイズは2～32KB、数値や日付データなら十分大き いが、文字列を入れると意外と小さい • 商品説明カラムに4000byte割り当てた items テーブルでは、 8KBのブロックに2レコード程度しか入らない データブロック 59 巨大な1レコード 巨大な1レコード
60. 1レコードのサイズ • 1レコードのサイズを考慮してパフォーマンスを考える • レコードサイズが大きいと大量レコード取得で遅い • ROWIDで1レコード取得するコストはさほど変わらない ◦ ブロックが分散した10,000レコード取得は？ ◦ ブロック内に連続した10,000レコードの取得は？ 巨大な1レコード 巨大な1レコード 1レコード 大きい 1レコード 小さい 60
61. 1レコードのサイズを設計 • 絞り込み/並び替えのカラムを小さいテーブルに切り出す • レコードサイズを小さく設計、テーブルサイズが小さいと 様々なシーンでパフォーマンスを出しやすい 巨大な1レコード 巨大な1レコード 1レコード 大きい 1レコード 小さい 読み込むレコードが多い ⇒ 読み込むブロック数が多い ⇒ 遅い 必要なカラムを 小さいテーブルに 切り出す 61 読み込むレコードが多い ⇒ 読み込むブロック数は少ない ⇒ 速い
62. レコードの分散 62
63. • 題材としてフォロー型のSNSを考える • フォロー関係は N:N となる • あるユーザーのフォロワーの一覧を取得するコストは？ データの分散状況とパフォーマンス 63 FOLLOWEE_ID FOLLOWER_ID CREATED_ON ... ... ... 256 284 2007-09-05 255 544 2007-09-05 440 729 2007-09-05 591 932 2007-09-05 139 926 2007-09-05 289 460 2007-09-05 350 64 2007-09-05 68 397 2007-09-05 902 581 2007-09-05 666 45 2007-09-05 978 260 2007-09-05 696 208 2007-09-05 ... ... ... フォローテーブル フォローした人 フォローされた人
64. • フォロー関係は時間と共に徐々に増える • 1ユーザーで絞ると各レコードのブロックは分散している • フォロワーが10万人、10万レコードが7～8万ブロックに分散 ◦ 8万ブロック ⇒ 4,000 IOPS なら20秒かかる データの分散状況とパフォーマンス フォローテーブル (FOLLOWEE_ID = 350) FOLLOWEE_ID FOLLOWER_ID CREATED_ON ... ... ... 350 468 2004-08-22 350 174 2004-08-22 ... ... ... 350 537 2008-04-08 ... ... ... 350 860 2018-08-24 ... ... ... 350 64 2007-09-05 ... ... ... 350 326 2019-06-05 ... ... ... 350 841 2005-05-01 ... ... ... 64
65. • 分散したレコードをDELETEしてINSERTするだけでも ブロックはまとまる • ブロックの分散が解消するとI/O回数は激減する データの分散とパフォーマンス フォローテーブル FOLLOWEE_ID FOLLOWER_ID CREATED_ON ... ... ... 350 284 2004-08-22 350 544 2017-03-27 ... ... ... 350 932 2008-04-08 ... ... ... 350 460 2018-08-24 ... ... ... 350 64 2007-09-05 ... ... ... 350 45 2019-06-05 ... ... ... 350 208 2005-05-01 ... ... ... FOLLOWEE_ID FOLLOWER_ID CREATED_ON ... ... ... 350 284 2004-08-22 350 544 2017-03-27 350 932 2008-04-08 350 460 2018-08-24 350 64 2007-09-05 350 45 2019-06-05 350 208 2005-05-01 ... ... ... フォローテーブル DELETE/INSERT 10万レコード 8万ブロック ⇒ 20秒 ※1.25レコード/ブロック 10万レコード 400ブロック ⇒ 0.1秒 ※250レコード/ブロック 65
66. 10年後を考える 66
67. DB設計のライフサイクル • システムのライフサイクルは部品ごとに異なる • アプリケーションプログラムは数年でリプレースを検討する • DB設計はそれより長い間使われる、ライフサイクルが長い • 今作ったテーブルが10年後も使われる想定での設計が必要 FOLLOWEE_ID FOLLOWER_ID CREATED_ON ... ... ... 256 284 2007-09-05 255 544 2007-09-05 440 729 2007-09-05 591 932 2007-09-05 139 926 2007-09-05 289 460 2007-09-05 350 64 2007-09-05 68 397 2007-09-05 902 581 2007-09-05 666 45 2007-09-05 978 260 2007-09-05 696 208 2007-09-05 ... ... ... FOLLOWEE_ID FOLLOWER_ID CREATED_ON 25 28 2000-09-05 25 54 2000-09-05 44 72 2000-09-05 59 93 2000-09-05 13 92 2000-09-05 28 46 2000-09-05 ... ... ... 数千行だったテーブル 数億行になってもパフォー マンスを維持できるか？ 10年後 67
68. DB設計のライフサイクル • 大規模テーブルのフルスキャンは原則として行わない ◦ レコード数と処理速度を比例関係にしない ◦ レコード数nに対して、計算量が O(n) ならいずれ破綻する 68 レコード数 n 計算量 =処理速度 O(n2) O(n) O(log n) O(1)
69. DB設計のライフサイクル • 処理対象のレコードを限定できる設計を導入 ◦ 履歴レコードは参照範囲を過去1年に限定など ▪ 範囲内のレコードはDBバッファキャッシュに含む前提 ▪ ブロック分散に気をつける ◦ 大規模なソートを排除、order by狙いのインデックスを利用 ▪ ソートには対象の全レコードが必要（＝フルスキャン） ▪ CPU・メモリだけを消費するソートは許容できる ◦ 生きてるレコードと死んだレコードを区別する ▪ 死んだレコードを処理速度に影響させない などなど 69
70. まとめ 70
71. まとめ 今回はOracle データベースの内部構造に着目して 「遅い理由」 「速い理由」 を考えるための基礎を学んでみました どんな事象にも必ず理由があります 仕組みを知り、バックグラウンドを把握して それではじめてベストな方法を見つけだすことができます 71