理屈で考える、 データベースのチューニング 第1回 Oracle DBのさらなるパフォーマンスチューニングに必要な基礎知識 2020年09月30日 ラクーンホールディングス 羽山 純 1

はじめに ● DBの内部構造を知りましょう ● 処理速度・処理時間を具体的な数値でイメージしましょう ○ その数値は多少間違ってても問題ありません ● 各処理を分析すると問題の原因を把握できます ● 分析のためには内部構造の知識が生きてきます ● 問題の原因が分かれば解決策を考えられます ○ やみくもに解決策を考えるのは風邪で胃薬を飲むようなもの ○ DBの中でなにが起きているのか理解しましょう ● 計測結果を疑う、みかけの数値に騙されないこと ○ パフォーマンス測定では内部構造を知らないと騙されやすい ○ 2回目以降の実行は速いなど問題の再現性が低いことがある 2

アジェンダ ● DBの性能を知る ● 内部構造を知る ● データブロックを理解する ● ROWIDとは何か ● MySQLとの比較 ● レコードサイズとパフォーマンス ● レコードの分散 ● 10年後を考える 3

DBの性能を知る 4

ストレージの性能を知る ● DB性能で重要なのがストレージのランダムI/O性能 ● ここでは 4,000 IOPS(I/O per Second)として考える （※ 性能としては少し遅めの水準） ● 1分間に行えるI/Oが24万回、これが絶対的な限界値 ● 具体的な数値でイメージすることが大切 4,000回/秒 （= 24万回/分） 5

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

4,000 IOPS / 8KBブロックの読み込み速度 1秒間 8KB × 4,000回 ≒ 32MB 1分間 32MB × 60秒 ≒ 2GB 7

ストレージ性能と最適化 ● 2GBのテーブルを読み込むには1分かかる、意外と遅い ● 実際の読み込みでは様々な最適化がある ○ DBバッファキャッシュ ○ マルチブロックリード（※後ほど説明） ● DBバッファキャッシュはメモリ上に確保されるキャッシュ ○ メモリ読み込みはストレージよりも圧倒的に速い 読み込み済のデータを メモリに保持する ストレージ DBバッファキャッシュ 8

DBバッファキャッシュ ● OLTP（オンライントランザクション）で利用されるDBでは DBバッファキャッシュのヒット率99%以上が普通 ● ヒット率が高いならストレージ性能は重要ではない？ ⇒ NO、たった1%のストレージ読込がボトルネックになる 読込データ量の割合 ストレージ読込 1% DBバッファ キャッシュ 99% 9

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

読み込み速度比 ⇒ メモリ : ストレージ = 1,000 : 1 キャッシュヒット率 ⇒ 99% 読込時間と所要時間の関係 読込データ量の割合 ストレージ 1% DBバッファ キャッシュ 99% 所要時間の割合 ストレージ 91% DBバッファキャッシュ 9% 11

● 汎用目的のDBは「I/Oバウンド」な状態が多い ● CPUは休みながらストレージ読み込みを待って処理する ● I/Oバウンドならば「ストレージ性能」≒「DBの処理性能」 I/Oバウンド ストレージは遅い CPUは速いがデータがこない ストレージを待ちながら処理 ストレージ性能 ≒ 処理速度 12

CPUバウンド ● 「CPUバウンド」になりやすいDBは ○ データがすべてメモリに収まる（メモリ多い、データ少ない） ○ 利用用途が限定的でキャッシュヒット率が高い ● 結果としてパフォーマンス的な課題がさほどない メモリは速い CPUの速度に負けないスピードで データが来る 13

CPU性能について ● 大抵のDBはI/Oバウンド、今回はI/OバウンドなDBを扱う ● I/Oバウンドの場合、 CPU性能はDBパフォーマンスにさほど影響しない CPU性能が良くてもヒマで寝てるだけ 14

内部構造を知る 15

なぜDBの内部構造の理解が必要か？ ● 内部構造を知らなくてもSQLを知ればデータを取り出せる ● アプリケーションはSQLを介して、DBの内部構造と疎結合 ● 開発者はSQLを介して、DBの内部構造と疎結合 ● 開発にsqlite、本番でMySQLなどDB実装の切り替えも可能 だけれども 16

● パフォーマンスの領域では内部構造の理解が不可欠 ● DBは魔法の箱ではない、無理な処理をすれば時間がかかる ● 例えば、順不同に並ぶ英和辞典で単語を探す ○ 単語を見つけるまで最初のページから全て探す必要がある ○ 荒唐無稽に聞こえるがDBでは意外とやりがち ➢ 実はテーブルフルスキャンしてるSQL ○ 見出し語のアルファベット順に並んでれば一瞬で探せる ➢ DBでもインデックスがあれば速くなる ○ DBの立場になって、その処理に無理がないか考える なぜDBの内部構造の理解が必要か？ 英和辞典 17 順不同に並ぶ英和辞書

なぜDBの内部構造の理解が必要か？ ● 人は無意識に道具の内部構造を考えて最適な利用をする ○ DBではこの仕事をオプティマイザが担当 ● フランスの旅行ガイドブックから「ラメールプラール」とい うオムレツ屋を探す 1. モンサンミシェル⇒レストランとたどって探す 2. 巻末索引から探す 3. 最初から最後まで全ページ探す 1. 2. 索引 巻末索引で探す 地域を絞って探す 18

● 旅行ガイドブックの構造はDBの構造に近い ○ 本編・観光情報（⇒テーブルのデータ） ○ 本編の掲載順番は決まりがない（⇒レコードの保存順） ○ 名称から探すために巻末索引がある（⇒インデックス） ○ 巻末索引には本編への参照がある（⇒ROWID ※後述） なぜDBの内部構造の理解が必要か？ 索引 巻末に索引がある 観光情報は順不同 19

なぜDBの内部構造の理解が必要か？ ● 図書館で本を探す場合 ○ 蔵書のエリア配置・並び順を把握していて最短でたどり着く ○ 情報端末で蔵書検索をする ● たどり着き方は複数あり、どれが最適かは状況により変わる ● DBは最適な方法を選択（=オプティマイザ）してくれる ○ 開発者はDBの立場になって「どう処理するか？」を考える ● DBとして最適は全体の最適ではない、開発者の手助けが必要 ○ そもそも必要のない処理である可能性 ○ さらに効率的な絞り込み条件を追加できる ○ 運用を変更したら効率的な処理にできる ○ バッチ処理だから処理速度が遅くてもよい ● DBの内部構造を知らないと開発者はうまく手助けできない 20

データブロックを理解する 21

テーブルのデータは、ストレージの表領域のセグメントの エクステントのデータブロックに保存される ⇒知らなくてOK Oracle DBのデータ構造 表領域 セグメント 表領域 セグメント セグメント セグメント セグメント セグメント セグメント セグメント エクステント エクステント エクステント エクステント エクステント エクステント データブロック ストレージ 22

● 覚えてもらいたいのは「データブロック」だけ ● データブロックのサイズは選択できる ○ 2KB, 4KB, 8KB, 16KB, 32KBなど ● テーブルやインデックスの実データが入っている ● データブロックがOracleにおけるI/Oの最小単位 ● DBバッファキャッシュへのキャッシュもデータブロック単位 ● 今回は8KBとする データブロック データブロック レコードデータ 空き領域 23

● テーブルの各レコードはデータブロックに保存される ● データブロックは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

● データブロックのうち大部分はレコードデータに利用 ● カラム型や保存するデータによって消費サイズが異なる ● VARCHAR2はバイト列分を消費、数値型も可変長 ● 数値カラムを9個持つテーブルで1行がおおよそ36byteなら ○ 8KBのデータブロック1つには200レコード以上入る ○ 1回のI/Oで200レコードも取得できる データブロック データブロック 25 数値カラムを9個ほど持つ テーブルなら1ブロックに 200レコード程度入る

● 1ブロックに200レコード入るテーブルを考える ● 200万レコード取得する場合、200万 ÷ 200 = 1万ブロック ● 4,000 IOPS なので、10,000 ブロック ÷ 4,000 IOPS = 2.5秒 ● （うまくいけば）2.5秒で200万レコード取得できそう ● 具体的な数値で考えることが重要、おおよそで問題なし ○ ただしマルチブロックリードならもっと速い データブロック データブロック 26 1ブロックに200 レコード入るとする

データブロックに関する問題 Q: 注文でレコードが挿入される「注文テーブル」がある。 「2019年5月の注文」と「全期間の電化製品ジャンルの注文」 が共に約200万レコードだったとすると、それぞれの条件で 200万レコード取得にかかる時間に差はあるか？ （※「注文日」及び「ジャンル」にはインデックスがあるものとする） 1. 「2019年5月の注文」の方が早く取得可能 2. 「全期間の電化製品ジャンルの注文」の方が早く取得可能 3. 両方ともほとんど差はない 27

データブロックに関する問題 A: 「2019年5月の注文」の方が早く取得可能 これはなぜか？ ● レコード挿入は空きデータブロックに対して連続で行われる ● その結果、同一タイミングで挿入されたレコードは同一の データブロックに含まれやすい ● 「注文日」と「レコード挿入タイミング」には相関があり 同一ブロック内に条件に当てはまるデータが連続しやすい ○ データブロック1つに200レコード含むなら、その大半が条件に 当てはまるレコードとなりやすい ○ 200万 ÷ 200 = 1万ブロック の取得で済む ○ 分散していると200万ブロック取得が必要 28

データブロックの分散 ● ジャンルで絞り込む場合は、テーブル全体にバラバラに レコードがちらばっている ● 1データブロック取得しても、必要なレコードは数行しか含ま れない、8KBのブロックのうち大半が必要のないレコード 注文日での絞り込み 必要なレコードが1つのブロック内に 連続している 200レコード利用 200レコード利用 200レコード利用 ジャンルでの絞り込み 必要なレコードが様々なブロックに 分散している それぞれ数レコード利用 29

● 使用可能ブロックは挿入可能、使用済みブロックは挿入不可 ● 空き領域が残り少なくなると使用済みブロックに変化する ● DELETEなど、十分な空き領域ができると使用可能へ戻る 同一ブロックにデータが集まる理由 データブロック データブロック 空き領域が十分ある場合は 新しいレコードを入れる 空き領域が少ない場合は 新しいレコードを入れない 空き領域 空き領域 30

使用可能/使用済みの状態遷移 使用可能 31 使用可能 使用済み INSERT 使用済み INSERT UPDATE DELETE 使用済み DELETE INSERT 使用可能 INSERT 空き領域が一定未満に なるまでは使用可能 INSERT INSERT INSERT INSERT INSERT 十分空きができるま では使用済みのまま ① ② ③ ④ ⑤ ⑥

データブロックサイズの考察 ● データが分散している場合 ○ 各データブロックに必要なレコードが数行しか含まれない ○ 沢山のデータブロックを取得する必要がある ○ それならば、ブロックサイズを小さくした方が有利？ ➢ 大抵の場合はNO、なぜならば ● 一般的なDBストレージ（SAS/iSCSI/FCなど）はブロックサ イズを変えてもIOPSはさほど変化しない ○ 4KB ⇒ 2KB IOPSにさほど影響なし ○ 8KB ⇒ 16KB IOPSが1～2割程度の減少（あくまで一例） 32

ブロックサイズと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倍

大きいブロックサイズの考察 ● ブロックサイズが大きいと IOPS は若干減少する ● 1レコードだけ取得する場合は不利 ○ 巨大ブロックを読み込んでも、利用するのは1レコードだけ ○ INDEX UNIQUE SCAN ⇒ TABLE ACCESS BY INDEX ROWID たった1レコードのために ブロック全体を取得する データブロック 大半のデータは 読み込んでも使わない 34

大きいブロックサイズの考察 ● ブロック内で連続するレコードを取得する場合は有利 ○ 日付範囲などは必要なレコードがブロック内で連続しやすい ○ 同時期に挿入されたレコードは同一ブロック内に固まりやすい ○ ブロック読込回数が減り、スループットが高くなる データブロック 取得したブロック内のデータは ほとんどが必要なレコード 35

● DBバッファキャッシュの無駄が多い ○ キャッシュするのはデータブロック単位 ○ 1つが大きいためキャッシュできるデータブロック数が少ない 大きいブロックサイズの考察 データブロック データブロック DBバッファキャッシュ 1ブロックが大きいため 入るブロック数が少ない 36

小さいブロックサイズの考察 ● ブロックサイズが小さいと IOPS は多少増加する ● 1レコードだけ取得する場合は有利 ○ 読み込んだけど利用しないデータが最小 ○ INDEX UNIQUE SCAN ⇒ TABLE ACCESS BY INDEX ROWID データブロック ブロックが小さいため 無駄が少ない 37

小さいブロックサイズの考察 ● ブロック内で連続するレコードを取得する場合は不利 ○ 日付範囲などは必要なレコードがブロック内で連続しやすい ○ 同時期に挿入されたレコードは同一ブロック内に固まりやすい ○ ブロック読込回数が増え、スループットが低くなる データブロック ブロックが小さいため 沢山のブロック読込が必要 38

● フルスキャンの場合だけ、スループットを高くできる ○ マルチブロックリード機能で複数ブロックを同時取得 ○ TABLE ACCESS FULL, INDEX FAST FULL SCAN ○ しかしフルスキャンが許されるのはバッチ処理くらい 小さいブロックサイズの考察 複数ブロックを1回のI/Oで 取得するので速い データブロック ざくっと一気に マルチブロックリード 狙ったブロックを１つずつ 通常リード 39

● DBバッファキャッシュの無駄が少ない ○ キャッシュするのはデータブロック単位 ○ 小さいため沢山のデータブロックをキャッシュ可能 小さいブロックサイズの考察 DBバッファキャッシュ データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック データブロック 1ブロックが小さいため 沢山のブロックが入る 40

ブロックサイズはなにが正解か？ ● ブロックサイズは表領域単位で変更できる、 可能ならばテーブルごとにチューニングするのがベスト ● しかし現実的には難易度が高い、実施コストが高くなりがち ● 開発者としてはDBのブロックサイズを把握して、 それに合わせたチューニングを行うのがベター ○ 構造的にパフォーマンスがでないからバッチ処理にする ○ 1ページで取得するアイテム数を減らして速度をだす ス○ーピー「配られたカードで勝負するしかないのさ」 孫子「彼を知り己を知れば百戦殆うからず」 41

ROWIDとは何か 42

ROWIDとは ● ROWIDはDBストレージ内の住所のようなもの ● データブロックとブロック内の位置などを特定可能 ● ROWIDが分かれば1回のI/Oで必要なレコードを取得できる 必要なデータブロックの 場所を特定できる 膨大な数のデータブロック 43

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

● 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

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

● 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

● 例えば 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

プライマリキーでレコード取得 ● プライマリキーでレコードを取得する例 ○ インデックスのブランチノード取得 × 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

● それぞれ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

● テーブルの場合はマルチブロックリード機能で 複数ブロック・複数レコードをまとめて取得可能 ● ただしテーブル内の全行取得が必要 ● どちらが速いかは状況次第 ○ 全体で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

MySQL(InnoDB)との比較 52

なぜ他のDBを知る必要があるのか ● プロダクトとして長期間生き残り利用されているDBは それだけの間エンジニア達によって磨かれている ● 各DBの構造は違いがあり速度特性も異なる、ただそれは特徴 の違いであり、どちらかが圧倒的に優れているわけではない ● いかなるDBも銀の弾丸で高速化されているわけではない、 速いのには理由があるし、遅いのにも必ず理由がある ● 各DBの構造の違いを知ることは、チューニングにおける限界 点を理解する助けになる ● 卓越したエンジニア達が磨いてきたプロダクトには「存在す る理由」がある 53

● 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

● プライマリキーでレコード取得する場合はインデックス走査 だけでレコードも取得できて有利 ● 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

● 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

● クラスタインデックスのため、テーブルフルスキャンは プライマリキー順に取得される ⇒ 開発者に優しい ● 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

1レコードのサイズ ● レコードのデータはデータブロックに格納される ● ブロックサイズは2～32KB、数値や日付データなら十分大き いが、文字列を入れると意外と小さい ● 商品説明カラムに4000byte割り当てた items テーブルでは、 8KBのブロックに2レコード程度しか入らない データブロック 59 巨大な1レコード 巨大な1レコード

1レコードのサイズ ● 1レコードのサイズを考慮してパフォーマンスを考える ● レコードサイズが大きいと大量レコード取得で遅い ● ROWIDで1レコード取得するコストはさほど変わらない ○ ブロックが分散した10,000レコード取得は？ ○ ブロック内に連続した10,000レコードの取得は？ 巨大な1レコード 巨大な1レコード 1レコード 大きい 1レコード 小さい 60

1レコードのサイズを設計 ● 絞り込み/並び替えのカラムを小さいテーブルに切り出す ● レコードサイズを小さく設計、テーブルサイズが小さいと 様々なシーンでパフォーマンスを出しやすい 巨大な1レコード 巨大な1レコード 1レコード 大きい 1レコード 小さい 読み込むレコードが多い ⇒ 読み込むブロック数が多い ⇒ 遅い 必要なカラムを 小さいテーブルに 切り出す 61 読み込むレコードが多い ⇒ 読み込むブロック数は少ない ⇒ 速い

レコードの分散 62

● 題材としてフォロー型の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 ... ... ... フォローテーブル フォローした人 フォローされた人

● フォロー関係は時間と共に徐々に増える ● 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

● 分散したレコードを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

10年後を考える 66

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

DB設計のライフサイクル ● 大規模テーブルのフルスキャンは原則として行わない ○ レコード数と処理速度を比例関係にしない ○ レコード数nに対して、計算量が O(n) ならいずれ破綻する 68 レコード数 n 計算量 =処理速度 O(n2) O(n) O(log n) O(1)

DB設計のライフサイクル ● 処理対象のレコードを限定できる設計を導入 ○ 履歴レコードは参照範囲を過去1年に限定など ■ 範囲内のレコードはDBバッファキャッシュに含む前提 ■ ブロック分散に気をつける ○ 大規模なソートを排除、order by狙いのインデックスを利用 ■ ソートには対象の全レコードが必要（＝フルスキャン） ■ CPU・メモリだけを消費するソートは許容できる ○ 生きてるレコードと死んだレコードを区別する ■ 死んだレコードを処理速度に影響させない などなど 69

まとめ 70

まとめ 今回はOracle データベースの内部構造に着目して 「遅い理由」 「速い理由」 を考えるための基礎を学んでみました どんな事象にも必ず理由があります 仕組みを知り、バックグラウンドを把握して それではじめてベストな方法を見つけだすことができます 71