DBスペシャリスト午後2問1で覚えるRDBMSの機能(1)

インフラ勉強会 3/6 FRI 22:00～

Transcript

1. DBスペシャリスト午後2問1で覚える RDBMSの機能 (1) インデックス・木構造と再帰CTE・ウィンドウ関数 インフラ勉強会　2020/03/06　まつひさ（hmatsu47）
2. 自己紹介 松久裕保（@hmatsu47） https://qiita.com/hmatsu47 名古屋でWebインフラのお守り係（非DBA） ◦ 会社ではAmazon Aurora（MySQL 5.6互換版）を 使ってます MySQL 8.0：趣味・遊び ◦ MySQL 8.0の薄い本を作って配っています https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ◦ 現在8.0.19対応版です ◦ GitHubリポジトリのほか、印刷版を勉強会などでタダで配っています 2
3. わたしの登壇歴 単独セッション • オンプレインフラエンジニアのための AWS移行の始め方（2019/2/21） LTその他で参加 • 名古屋IT事情について話し合う part2（2019/3/17） • Local IT Meetup -Episode1- IWATA オンライン同時配信（2019/9/7） • インフラ勉強会2周年記念非公式イベント・年忘れライブLT大会＠大阪 (の西隣)（2019/12/21） • 技術書同人誌について話そう！夜の部（2020/3/1） その他、オフラインなどいろいろ https://speakerdeck.com/hmatsu47 3
4. わたしの情報処理技術者試験受験歴 • 平成 4年秋 第二種情報処理技術者試験 • 平成 6年春 第一種情報処理技術者試験 （間が空いて…） • 平成26年春 情報セキュリティスペシャリスト試験（SC） ◦ その後、情報処理安全確保支援士に登録（001158） • 平成26年秋 ネットワークスペシャリスト試験（NW） • 平成27年春 データベーススペシャリスト試験（DB） ◦ 午後2は問1を選択して93点でした • 平成28年春 情報セキュリティマネジメント試験（SG） 4
5. 今日のテーマ IPAのデータベーススペシャリスト試験問題を、 • MySQLやPostgreSQL での再現（実行）例を参照しながら、 • 実務と共通する部分 • 実務とは違う部分 を読み解いていきます ※一部文字が小さいページがあります 　スライドを手元にダウンロードしてお使いください https://twitter.com/hmatsu47/status/1234490770134007809 5
6. 伝えたいこと • IPA試験の問題も、使い方次第で実務に役立ちます • 実務とは違うところもあるので、その違いを意識して役立てま しょう ◦ RDBMSによって実装されている機能は異なります ◦ 同じ機能が実装されていても、仕様や挙動が違うこともあります • とはいえ、このセッションでは厳密なことは説明しないので、 雰囲気がわかればOKです 6
7. おしながき • データベーススペシャリスト試験とは？ • 平成31年春期試験・午後２問１の概要 • ①オプティマイザの仕様とインデックス ◦ 「インデックスが効かないケース」 ◦ MySQLとPostgreSQLで確認してみる • ②木構造と再帰共通テーブル式（CTE） ◦ ナイーブツリーで再帰CTEを使う ◦ 経路列挙モデル・入れ子集合モデルへの変更 • ③ウィンドウ関数の紹介 ◦ 試験問題とは別の例で（RANK関数） 7
8. データベーススペシャリスト試験とは？ • 高度試験 ◦ 記述式（スペシャリスト試験）区分の1つ • 高度試験の中では合格率が高めだった（16～18%） ◦ ただし、ここ3年は他の高度試験並みの約14% • 午後問題では概念データモデルおよび関係スキーマの完成問題 の出題が多い ◦ E-R図（エンティティ、リレーションシップ、スーパータイプとサブタ イプ、…） ◦ 関数従属性、主キーと候補キー、正規化 8
9. データベーススペシャリスト試験とは？ • 以前よりDBA・インフラエンジニア寄りになった？ ◦ SQLや物理（テーブル）実装、RDBMSの機能に関する問題が増えた 　　　（IPA 情報処理技術者試験・情報処理安全確保支援士試験のページより引用） • 午後1および午後2問1にその傾向が強くなってきた？ 9
10. 平成31年春期試験・午後2問1の概要 • データベースの実装、設計（＝物理設計）の問題 • 銀行の窓口端末のログを分析して事務手続やアプリケーション ソフトウェアの改善に役立てるために、新たな分析システムを 構築することになった • 木構造をもつログデータを効率よく扱いたい • 性能面を考えて、テーブルのパーティショニングやクラスタ化 を検討したい 10
11. 平成31年春期試験・午後2問1の概要 • データベースの実装、設計（＝物理設計）の問題 • 銀行の窓口端末のログを分析して事務手続やアプリケーション ソフトウェアの改善に役立てるために、新たな分析システムを 構築することになった • 木構造をもつログデータを効率よく扱いたい • 性能面を考えて、テーブルのパーティショニングやクラスタ化 を検討したい 11
12. ①オプティマイザの仕様とインデックス オプティマイザとは？ • SQLの処理内容を最適化し、実行計画を立てる機構 ◦ どのインデックスを使う？テーブルスキャンにする？ ◦ どれを駆動表（外部表）にして結合（JOIN）する？ • プランナともいう 12 構文解析 パーサ・アナライザ リライタなど 実行計画 オプティマイザ （プランナ） 実行 エグゼキュータ など
13. ①オプティマイザの仕様とインデックス 試験問題（平成31年春期試験・午後2問1）P.10〔RDBMSの主な仕様〕より抜粋 試験問題：前方一致以外の文字列検索や、関数を含む検索では… • テーブルスキャン（表探索）になる • インデックス（索引）は使われない 13
14. ①オプティマイザの仕様とインデックス MySQL 8.0で確認してみる（文字列検索） 14 mysql> CREATE DATABASE index_test; Query OK, 1 row affected (0.01 sec) mysql> USE index_test; Database changed mysql> CREATE TABLE like_test ( -> id INT PRIMARY KEY NOT NULL, -> val INT NOT NULL, -> str VARCHAR(255) NOT NULL, -> INDEX idx_val(val), -> INDEX idx_str(str) -> ); Query OK, 0 rows affected (0.04 sec) （データINSERTは省略）
15. ①オプティマイザの仕様とインデックス MySQL 8.0で確認してみる（文字列検索） 15 mysql> SELECT * FROM like_test; +----+-----+---------+ | id | val | str | +----+-----+---------+ | 1 | 100 | abcdefg | | 2 | 100 | bcdefgh | | 3 | 100 | cdefghi | | 4 | 100 | defghij | | 5 | 101 | abcdefg | | 6 | 101 | bcdefgh | | 7 | 101 | cdefghi | | 8 | 102 | abcdefg | | 9 | 102 | bcdefgh | | 10 | 103 | abcdefg | +----+-----+---------+ 10 rows in set (0.00 sec)
16. ①オプティマイザの仕様とインデックス MySQL（InnoDB）のテーブル・インデックス構造 クラスタインデックスになっている（データ行が主キー順に並ぶ） 16
17. ①オプティマイザの仕様とインデックス MySQL（InnoDB）のテーブル・インデックス構造 クラスタインデックスになっている（データ行が主キー順に並ぶ） 17 【注】インデックス自体は木構造（B+木）になって 　いますが、②木構造と再帰共通テーブル式(CTE) 　の説明のときに混乱するので今回はB+木について 　は説明しません。
18. ①オプティマイザの仕様とインデックス WHERE句に単独で文字列の部分一致（b）を指定した場合… 試験問題と同様、テーブルスキャンに • MySQL（InnoDB）の構造上、テーブルスキャン＝主キーの全行スキャン 18 mysql> EXPLAIN SELECT * FROM like_test WHERE str LIKE '%b%'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | like_test | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
19. ①オプティマイザの仕様とインデックス 全10行中、マッチした7行が抽出された 19 mysql> SELECT * FROM like_test WHERE str LIKE '%b%'; +----+-----+---------+ | id | val | str | +----+-----+---------+ | 1 | 100 | abcdefg | | 2 | 100 | bcdefgh | | 5 | 101 | abcdefg | | 6 | 101 | bcdefgh | | 8 | 102 | abcdefg | | 9 | 102 | bcdefgh | | 10 | 103 | abcdefg | +----+-----+---------+ 7 rows in set (0.00 sec)
20. ①オプティマイザの仕様とインデックス 主キー（のリーフノード行）を直接スキャンした 赤字のid行をスキャンして黄色の行がマッチしたので返却 20
21. ①オプティマイザの仕様とインデックス 他の条件とあわせて指定した場合… 他の条件で使えるインデックスがあればそれを使って絞り込む • このケースでは「val = 100」で絞り込んだ行に対してスキャン • 「val = 100」は4行 多くのRDBMSはコストベースで判断して実行計画を立てている • 最低コストの実行計画を選択 21 mysql> EXPLAIN SELECT * FROM like_test WHERE val = 100 AND str LIKE '%b%'; +----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | like_test | NULL | ref | idx_val | idx_val | 4 | const | 4 | 11.11 | Using where | +----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
22. ①オプティマイザの仕様とインデックス スキャンした4行中、マッチした2行が抽出された 22 mysql> SELECT * FROM like_test WHERE val = 100 AND str LIKE '%b%'; +----+-----+---------+ | id | val | str | +----+-----+---------+ | 1 | 100 | abcdefg | | 2 | 100 | bcdefgh | +----+-----+---------+ 2 rows in set (0.00 sec)
23. ①オプティマイザの仕様とインデックス idx_valで絞り込んで主キーのリーフノード行をスキャンした 赤字→青字の行をスキャンして黄色の行がマッチしたので返却 23
24. ①オプティマイザの仕様とインデックス PostgreSQL 12の場合 • テーブルとインデックスは別々のDDLで作成 24 index_test=# CREATE TABLE like_test ( index_test(# id INT PRIMARY KEY NOT NULL, index_test(# val INT NOT NULL, index_test(# str VARCHAR(255) NOT NULL index_test(#); CREATE TABLE index_test=# CREATE INDEX ON like_test (val); CREATE INDEX index_test=# CREATE INDEX ON like_test (str); CREATE INDEX
25. ①オプティマイザの仕様とインデックス PostgreSQL 12の場合 25 index_test=# EXPLAIN VERBOSE SELECT * FROM like_test WHERE str LIKE '%b%'; QUERY PLAN --------------------------------------------------------------------- Seq Scan on public.like_test (cost=0.00..11.75 rows=140 width=524) Output: id, val, str Filter: ((like_test.str)::text ~~ '%b%'::text) (3 rows) index_test=# EXPLAIN VERBOSE SELECT * FROM like_test WHERE val = 100 AND str LIKE '%b%'; QUERY PLAN -------------------------------------------------------------------------------------------- Index Scan using like_test_val_idx on public.like_test (cost=0.14..8.16 rows=1 width=524) Output: id, val, str Index Cond: (like_test.val = 100) Filter: ((like_test.str)::text ~~ '%b%'::text) (4 rows) valのインデックスが効いている
26. ①オプティマイザの仕様とインデックス 試験問題と実際の動作はなぜ違う？ • 試験問題：特定の製品の動作を意図した出題ではない • 仕様を単純化しないと記述式問題の解答が難しくなる • 解答が難しい→正解パターンを集約しづらい • 結果として、採点が難しくなる ◦ ある程度単純化は必要 ◦ 完全に個人の推測ですが 26
27. ②木構造と再帰共通テーブル式（CTE） 試験問題（平成31年春期試験・午後2問1）P.10〔RDBMSの主な仕様〕より抜粋 共通テーブル式（CTE）＝WITH句 • 複雑なSQLを分かりやすくする ◦ サブクエリ部分の分離・抽出など • RECURSIVEを付けると、再帰問い合わせができる ◦ UNION（ALL）と組み合わせる 27
28. ②木構造と再帰共通テーブル式（CTE） 試験問題（平成31年春期試験・午後2問1）P.14〔”ログ関連”テーブルの検討〕より抜粋 28
29. ②木構造と再帰共通テーブル式（CTE） 試験問題（平成31年春期試験・午後2問1）P.14〔”ログ関連”テーブルの検討〕より抜粋 29
30. ②木構造と再帰共通テーブル式（CTE） 試験問題（平成31年春期試験・午後2問1）P.14〔”ログ関連”テーブルの検討〕より抜粋 ※案3以外でも木構造の親子関係は同じ 30
31. ②木構造と再帰共通テーブル式（CTE） • 案1：SQLアンチパターン「ナイーブツリー」 ◦ 親へのリンク（親ID）しか持たない ◦ 再帰問い合わせを使うのなら問題なし（この構造のままでも） ▪ MySQLは8.0から対応なので、5.7以前を使う場合はこの構造では辛い • 案2 : 経路列挙モデルへの変更 ◦ アンチパターン「ジェイウォーク」でもある（非正規形） ◦ バグに繋がる仕様も…（末尾は「/」にしましょう） • 案3 : 入れ子集合モデルへの変更 ◦ 更新負荷が高い ◦ 構造がちょっと複雑になる（バグの原因に…） 31
32. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1） ※カラム名は英数字と記号、ID列は数値に変更（以降すべて同じ） 　外部キー制約は定義せず（出題ケースでは更新処理がないので、外部キー制約を定義しない可能性も） 32 mysql> CREATE DATABASE tree_test; Query OK, 1 row affected (0.01 sec) mysql> USE tree_test; Database changed mysql> CREATE TABLE log_relation1 ( -> log_id INT PRIMARY KEY NOT NULL, -> parent_log_id INT, -> INDEX (parent_log_id) -> ); Query OK, 0 rows affected (0.02 sec) （データINSERTは省略）
33. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1） 33 mysql> SELECT * FROM log_relation1 ORDER BY log_id; +--------+---------------+ | log_id | parent_log_id | +--------+---------------+ | 101 | NULL | | 102 | 101 | | 103 | 101 | | 104 | 103 | | 105 | 103 | | 106 | 103 | | 107 | NULL | | 108 | 107 | | 109 | 107 | +--------+---------------+ 9 rows in set (0.00 sec) MySQL 8.0では必ずしも 主キー順にならなくなった
34. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1問合せ[A]） 34 mysql> WITH RECURSIVE temp(log_id) AS -> (SELECT log_id FROM log_relation1 WHERE log_id = 101 -> UNION ALL -> SELECT a.log_id FROM log_relation1 a, temp b -> WHERE a.parent_log_id = b.log_id) -> SELECT log_id FROM temp; +--------+ | log_id | +--------+ | 101 | | 102 | | 103 | | 104 | | 105 | | 106 | +--------+ 6 rows in set (0.00 sec)
35. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1問合せ[A]） 35
36. ②木構造と再帰共通テーブル式（CTE） EXPLAINではRecursiveが表示される ※再帰で繰り返す処理全行分の情報は表示されない点に注意 36 mysql> EXPLAIN -> WITH RECURSIVE temp(log_id) AS -> (SELECT log_id FROM log_relation1 WHERE log_id = 101 -> UNION ALL -> SELECT a.log_id FROM log_relation1 a, temp b -> WHERE a.parent_log_id = b.log_id) -> SELECT log_id FROM temp; +----+-------------+---------------+------------+-------+---------------+---------------+---------+----------+------+----------+------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+---------------+---------+----------+------+----------+------------------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 2 | DERIVED | log_relation1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 3 | UNION | b | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Recursive; Using where | | 3 | UNION | a | NULL | ref | parent_log_id | parent_log_id | 5 | b.log_id | 2 | 100.00 | Using index | +----+-------------+---------------+------------+-------+---------------+---------------+---------+----------+------+----------+------------------------+ 4 rows in set, 1 warning (0.00 sec)
37. ②木構造と再帰共通テーブル式（CTE） PostgreSQL 12でも同じように行ける（案1問合せ[A]） 37 tree_test=# WITH RECURSIVE temp(log_id) AS tree_test-# (SELECT log_id FROM log_relation1 WHERE log_id = 101 tree_test(# UNION ALL tree_test(# SELECT a.log_id FROM log_relation1 a, temp b tree_test(# WHERE a.parent_log_id = b.log_id) tree_test-# SELECT log_id FROM temp; log_id -------- 101 102 103 104 105 106 (6 rows)
38. ②木構造と再帰共通テーブル式（CTE） EXPLAINするとCTE Scanになる（案1問合せ[A]） 38 tree_test=# EXPLAIN WITH RECURSIVE temp(log_id) AS (SELECT log_id FROM log_relation1 WHERE log_id = 101 UNION ALL SELECT a.log_id FROM log_relation1 a, temp b WHERE a.parent_log_id = b.log_id) SELECT log_id FROM temp; QUERY PLAN ----------------------------------------------------------------------------------------------------------- CTE Scan on temp (cost=456.09..478.71 rows=1131 width=4) CTE temp -> Recursive Union (cost=0.15..456.09 rows=1131 width=4) -> Index Only Scan using log_relation1_pkey on log_relation1 (cost=0.15..8.17 rows=1 width=4) Index Cond: (log_id = 101) -> Hash Join (cost=0.33..42.53 rows=113 width=4) Hash Cond: (a.parent_log_id = b.log_id) -> Seq Scan on log_relation1 a (cost=0.00..32.60 rows=2260 width=8) -> Hash (cost=0.20..0.20 rows=10 width=4) -> WorkTable Scan on temp b (cost=0.00..0.20 rows=10 width=4) (10 rows) ハッシュ結合
39. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1問合せ[B]） 39 mysql> WITH RECURSIVE temp(log_id, parent_log_id) AS -> (SELECT log_id, parent_log_id FROM log_relation1 WHERE log_id = 105 -> UNION ALL -> SELECT a.log_id, a.parent_log_id FROM log_relation1 a, temp b -> WHERE a.log_id = b.parent_log_id) -> SELECT log_id FROM temp; +--------+ | log_id | +--------+ | 105 | | 103 | | 101 | +--------+ 3 rows in set (0.00 sec)
40. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案1問合せ[B]） 40
41. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2） 41 mysql> CREATE TABLE log_relation2 ( -> log_id INT PRIMARY KEY NOT NULL, -> path VARCHAR(500) NOT NULL, -> INDEX (path) -> ); Query OK, 0 rows affected (0.02 sec) （データINSERTは省略） 仮で500文字に
42. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2） 42 mysql> SELECT * FROM log_relation2 ORDER BY log_id; +--------+--------------+ | log_id | path | +--------+--------------+ | 101 | 101/ | | 102 | 101/102/ | | 103 | 101/103/ | | 104 | 101/103/104/ | | 105 | 101/103/105/ | | 106 | 101/103/106/ | | 107 | 107/ | | 108 | 107/108/ | | 109 | 107/109/ | +--------+--------------+ 9 rows in set (0.00 sec) バグ除けのために末尾を/に
43. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2問合せ[A]） 43 mysql> SELECT log_id FROM log_relation2 WHERE path LIKE '101/%'; +--------+ | log_id | +--------+ | 101 | | 102 | | 103 | | 104 | | 105 | | 106 | +--------+ 6 rows in set (0.02 sec)
44. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2問合せ[A]） 試験問題と同様にインデックス検索になる 44 mysql> EXPLAIN -> SELECT log_id FROM log_relation2 WHERE path LIKE '101/%'; +----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | log_relation2 | NULL | range | path | path | 2002 | NULL | 6 | 100.00 | Using where; Using index | +----+-------------+---------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
45. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2問合せ[B]） 45 mysql> SELECT b.log_id FROM log_relation2 a, log_relation2 b -> WHERE a.log_id = 105 AND INSTR(a.path, b.path) = 1; +--------+ | log_id | +--------+ | 101 | | 103 | | 105 | +--------+ 3 rows in set (0.00 sec)
46. ②木構造と再帰共通テーブル式（CTE） MySQL 8.0で確認してみる（案2問合せ[B]） 試験問題とは違ってINDEX（主キー）が使われる • 経路（path）側はINDEXフルスキャンに 46 mysql> EXPLAIN -> SELECT b.log_id FROM log_relation2 a, log_relation2 b -> WHERE a.log_id = 105 AND INSTR(a.path, b.path) = 1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | a | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | index | NULL | path | 2002 | NULL | 9 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.00 sec)
47. ②木構造と再帰共通テーブル式（CTE） PostgreSQL 12でEXPLAINを確認（案2問合せ[B]） こちらも試験問題とは違ってINDEX（主キー）が使われる 47 tree_test=# EXPLAIN SELECT b.log_id FROM log_relation2 a, log_relation2 b WHERE a.log_id = 105 AND POSITION(b.path IN a.path) = 1; QUERY PLAN -------------------------------------------------------------------------------------------------- Nested Loop (cost=0.14..21.66 rows=1 width=4) Join Filter: ("position"((a.path)::text, (b.path)::text) = 1) -> Index Scan using log_relation2_pkey on log_relation2 a (cost=0.14..8.16 rows=1 width=516) Index Cond: (log_id = 105) -> Seq Scan on log_relation2 b (cost=0.00..11.40 rows=140 width=520) (5 rows)
48. ②木構造と再帰共通テーブル式（CTE） 案3の実行例（MySQL・PostgreSQL）は省略 • MySQLの場合「左端番号, 右端番号」「右端番号, 左端番号」 の複合インデックスを作るとカバリングインデックスとなる ◦ インデックスを読むだけで結果を返すことができる ▪ データ行を直接読まなくて良い分読み取り負荷が低い ▪ ただし更新負荷は高くなる ◦ 主キー（ログID）列が暗黙的にインデックスに含まれる ▪ MySQL（InnoDB）がクラスタインデックスを採用していることと関係 ▪ 参考：16ページの図 ◦ やるかどうかはテーブル行数と木構造の深さ次第 48
49. ②木構造と再帰共通テーブル式（CTE） 試験問題は案3を採用 • 案3の弱点「更新」（後から挿入・削除）を行わないため • 要件が違えば最適な選択肢は異なる ◦ おそらく案3（入れ子集合モデル）が採用されるケースは限定的 再帰CTEの参考情報 • MySQL 8.0 https://qiita.com/hmatsu47/items/01211556089b19913d05 • PostgreSQL 12 https://www.sraoss.co.jp/tech-blog/pgsql/pg12-cte/ 49
50. ③ウィンドウ関数の紹介 試験問題（平成31年春期試験・午後2問1）P.10〔RDBMSの主な仕様〕より抜粋 ※今回は出題とは別の例を紹介 50
51. ③ウィンドウ関数の紹介 ウィンドウ関数とは？ • Wikipedia - 窓関数（SQL）より SQL において、窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に 集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されてい る。分析関数やOLAP機能と呼ばれる場合もある。 • PostgreSQL 11.5文書 - 3.5. ウインドウ関数より ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数によ り行われる計算の形式と似たようなものです。 とは言っても、非ウィンドウ集約呼び出しのように、ウィンドウ関 数により行が単一出力行にグループ化されることはありません。 その代わり、行はそれぞれ個別の身元を維持しま す。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることがで きます。 51
52. ③ウィンドウ関数の紹介 ウィンドウ関数とは？ • Wikipedia - 窓関数（SQL）より SQL において、窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に 集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されてい る。分析関数やOLAP機能と呼ばれる場合もある。 • PostgreSQL 11.5文書 - 3.5. ウインドウ関数より ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数によ り行われる計算の形式と似たようなものです。 とは言っても、非ウィンドウ集約呼び出しのように、ウィンドウ関 数により行が単一出力行にグループ化されることはありません。 その代わり、行はそれぞれ個別の身元を維持しま す。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることがで きます。 …なるほどわからん 52
53. ③ウィンドウ関数の紹介 大雑把にいうと、 • 前後の行を参照しながら計算・集計結果を出す ◦ 例1：1行前に記録された時間との差分計算 ▪ 今回の試験問題はコレ ◦ 例2 : 全体ランキング • テーブルを複数の区画に分けて、グループ化せず各々の行に対 して区画ごとの計算・集計結果を出す ◦ 例3：グループ別ランキング ◦ 例4 : 累積値　など …というようなSELECTが可能 53
54. ③ウィンドウ関数の紹介 MySQL 8.0でRANK()を試してみる（大分県の市町村人口データを使って） 54 mysql> SELECT * FROM population ORDER BY population DESC; +-----------------+------------+-------------------+ | name | population | municipality_type | +-----------------+------------+-------------------+ | 大分市 | 477522 | 1 | | 別府市 | 118054 | 1 | | 中津市 | 82857 | 1 | | 佐伯市 | 67722 | 1 | | 日田市 | 62797 | 1 | | 宇佐市 | 53715 | 1 | | 臼杵市 | 36443 | 1 | | 豊後大野市 | 33782 | 1 | | 由布市 | 33097 | 1 | | 杵築市 | 28373 | 1 | | 日出町 | 27965 | 2 | | 国東市 | 26447 | 1 | | 豊後高田市 | 22086 | 1 | | 竹田市 | 20315 | 1 | | 津久見市 | 16174 | 1 | | 玖珠町 | 14619 | 2 | | 九重町 | 8857 | 2 | | 姫島村 | 1778 | 3 | +-----------------+------------+-------------------+ 18 rows in set (0.00 sec)
55. ③ウィンドウ関数の紹介 全行を対象にしたRANK() OVER句で区画（PARTITION）を指定 • PARTITION BYを指定していないのでテーブル全行が対象 • ORDER BYで人口の降順を指定 55 mysql> SELECT -> RANK() OVER (ORDER BY population DESC) AS pop_rank, -> name, -> population, -> municipality_type -> FROM population;
56. ③ウィンドウ関数の紹介 結果：全体順位が表示される（pop_rank） 56 +----------+-----------------+------------+-------------------+ | pop_rank | name | population | municipality_type | +----------+-----------------+------------+-------------------+ | 1 | 大分市 | 477522 | 1 | | 2 | 別府市 | 118054 | 1 | | 3 | 中津市 | 82857 | 1 | | 4 | 佐伯市 | 67722 | 1 | | 5 | 日田市 | 62797 | 1 | | 6 | 宇佐市 | 53715 | 1 | | 7 | 臼杵市 | 36443 | 1 | | 8 | 豊後大野市 | 33782 | 1 | | 9 | 由布市 | 33097 | 1 | | 10 | 杵築市 | 28373 | 1 | | 11 | 日出町 | 27965 | 2 | | 12 | 国東市 | 26447 | 1 | | 13 | 豊後高田市 | 22086 | 1 | | 14 | 竹田市 | 20315 | 1 | | 15 | 津久見市 | 16174 | 1 | | 16 | 玖珠町 | 14619 | 2 | | 17 | 九重町 | 8857 | 2 | | 18 | 姫島村 | 1778 | 3 | +----------+-----------------+------------+-------------------+ 18 rows in set (0.02 sec) 日出町（ひじまち）はこの順位
57. ③ウィンドウ関数の紹介 次は市町村種別で区切ってみる OVER句で区画（PARTITION）を指定 • PARTITION BYで市町村種別を指定→市・町・村別の順位に • ORDER BYで人口の降順を指定 57 mysql> SELECT -> RANK() OVER (PARTITION BY municipality_type ORDER BY population DESC) AS pop_rank, -> name, -> population, -> municipality_type -> FROM population;
58. ③ウィンドウ関数の紹介 結果：市・町・村それぞれの順位が表示される（pop_rank） 58 +----------+-----------------+------------+-------------------+ | pop_rank | name | population | municipality_type | +----------+-----------------+------------+-------------------+ | 1 | 大分市 | 477522 | 1 | | 2 | 別府市 | 118054 | 1 | | 3 | 中津市 | 82857 | 1 | | 4 | 佐伯市 | 67722 | 1 | | 5 | 日田市 | 62797 | 1 | | 6 | 宇佐市 | 53715 | 1 | | 7 | 臼杵市 | 36443 | 1 | | 8 | 豊後大野市 | 33782 | 1 | | 9 | 由布市 | 33097 | 1 | | 10 | 杵築市 | 28373 | 1 | | 11 | 国東市 | 26447 | 1 | | 12 | 豊後高田市 | 22086 | 1 | | 13 | 竹田市 | 20315 | 1 | | 14 | 津久見市 | 16174 | 1 | | 1 | 日出町 | 27965 | 2 | | 2 | 玖珠町 | 14619 | 2 | | 3 | 九重町 | 8857 | 2 | | 1 | 姫島村 | 1778 | 3 | +----------+-----------------+------------+-------------------+ 18 rows in set (0.00 sec) 日出町は「町」の1位に
59. ③ウィンドウ関数の紹介 ほかにも、 • CUME_DIST() 累積分布値 • FIRST_VALUE() 最初の行の値 • LEAD() 次行の値 • PERCENT_RANK() パーセントランク値 • ROW_NUMBER() 行番号 などの専用関数や、COUNT()・SUM()など通常の集約関数を使う ことが可能 59
60. ③ウィンドウ関数の紹介 ウインドウ関数の参考情報 • MySQL 8.0 https://qiita.com/hmatsu47/items/6cc0e69f3895f3e4a486 https://qiita.com/hmatsu47/items/7976e81100604f8984d2 • PostgreSQL 11（12も同じ） https://www.postgresql.jp/document/11/html/functions-window.html https://www.postgresql.jp/document/11/html/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS • 書籍「SQLデータ分析・活用入門」（ソシム）※第7章 https://www.socym.co.jp/book/1226 MySQL / PostgreSQL のどちらにも対応 60
61. まとめ • SQLの実行計画を立てるのがオプティマイザ ◦ 多くのRDBMSでは、オプティマイザはコストベース ◦ コストが最も低い（と見積もった）実行計画を選択する ◦ RDBMSによって（結合などの際に）選択可能な実行計画は異なる • インデックスをうまく使うとコストを低減できる • 木構造のテーブルはユースケースにあわせて設計する ◦ 想定する階層の深さ、更新の有無など • 木構造のテーブルを扱うときは再帰CTEが便利 • 集計・分析にはウィンドウ関数が便利 61
62. まとめ • 実務と違うところ ◦ オプティマイザのようなRDBMSの内部の仕組み ▪ 実際の製品のほうが複雑 ▪ 製品ごとに仕組みが異なる場合がある ◦ SQLの細かい文法（関数名・パラメータ等） • 実務と同じところ ◦ SQLの基本的な使い方 ◦ 採用すべき設計の（基本的な）選択方法 ▪ ユースケースに合わせた設計を行う 62
63. 次回の内容（リクエストがあれば開催します） • テーブルパーティショニング ◦ 1つのテーブルを複数に分割して扱う機能 • パラレルクエリ（パラレルスキャン） ◦ 1つのSQLの処理の中で、複数のスキャンを並列で行う • クラスタリング ◦ 複数のサーバでクラスタを構成する 63