テック文化祭LT__4_.pdf

テック文化祭LTで登壇した資料です

Transcript

1. 2026/09/26 テック文化祭 _findy DBMS キモくね はやと
2. 自己紹 介 那須 隼人 芝浦工業大学 情報工学コース ３年 趣味: ギター 歌を聴くこと 技術: • 普段バックエンド触ってる • AIを使うの下手 • ちなみに今Findyでインターンしてる（あと４日間）
3. まず
6. 気にせず行きましょう
7. こんな要件みてみましょう idを64bitで自動採番 nameは最大255字でNULL不可 created_atはマイクロ秒付きデフォルトで NULL不可
8. これ、何の書き方かわかりますか？ CREATE TABLE users ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE = InnoDB DEFAULT CHARSET = UTF8MB4; MySQL データベースのエンジンを指定できるようになってる ほとんどInnoDB 多分よく見るsqlのものはこんな感じの記法
9. これ、何の書き方かわかりますか？ エンジンの指定によって 制約とかキャッシュ、インデックスの サポートややり方が変わってくる
10. これは？ CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ); PostgreSQL PostgreSQLにはDATETIME型がない timestampは「タイムゾーンなし」 timestamptzはUTCで管理される 自動採番が雰囲気キモい
11. これは？ CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')) ); SQLite 基本的には型システムが弱い プログラムで担保する前提になっている
12. これは？ CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')) ); あれ? SQLite 基本的には型システムが弱い プログラムで担保する前提になっている
13. idを64bitで自動採番 nameは最大255字でNULL不可 created_atはマイクロ秒付きデフォルトで NULL不可
14. SQLiteの要件 CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')) ); name TEXTだけだと255文字制限にならない、必要ならcheck(length(name) <= 255)とか ロジック側で制御するのが基本的な慣習になっているVARCHAR(255)ってかいても普通に1GB上限になる created_at %fはmsまでしか入れられない。μsまでは入れられないから要件を満たさない
15. なぜ同じ要件なのに、 DDLが違うのか？
16. DBMSの実行順 自動採番を歴史で見る 主キーとストレージ構造 日時型 文字列
17. DMLとDDL DML Data Manipulation Language DML（SELECT / INSERT）などは DBMS間で比較的共通化しやすい DDL Data Definition Language DDL（CREATE TABLE）などは 型・制約・自動採番など DBMS固有の機構に影響されやすい
18. SQLからバイナリまでのパイプライン SQL / DDL Executor Page / File ユーザやアプリケーションが 発行するテキスト命令文 Plannerを使って作った計画に従って実際に実行する オペレーターを呼び出して Storage Engineに命令を出す ブロック単位でメモリとディスクでやり取り テーブルとかの情報を色々ここで保管する Parser / Planner Storage Engine Bytes 構文解析をして抽象構文木（ AST）に変換 コスト計算をして最も効率の高い計画を作る Executorからの指示 論理で物理の橋渡し トランザクション制御、ロック管理、 WAL、 たくさん担う OSのシステムコールで バイナリデータとして記録される
19. DBMSの実行順 自動採番を歴史で見る 主キーとストレージ構造 日時型 文字列
20. 歴史 1986 SQL-86 1995 MySQL・PostgreSQL 2003 SQL:2003 ANSIによって規格化された 標準SQL 87年にISOに承認された 自動採番、日時なし 内部的に最初の バージョンがリリース windows版リリースは1998年 標準が存在しなかったため 日時とかを独自での実装 自動採番の標準化 GENERATED ALWAYS AS IDENTITY 1992 SQL-92 2000 SQLite 2017 PostgreSQL 大幅に機能が拡張された 動的SQL、言語の直交性の改善 外部結合、DDL拡張など 標準なし 型チェックや管理を削ぎ落として メモリの省エネのためrowidとの一体化 AUTOINCREMENT実装 SQL:2003の標準規格に対応 postgreSQL-10のリリース 今までのSERIALを IDENTITYに
21. 自動採番の内部機構 MySQL PostgreSQL SQLite AUTO_INCREMENT GENERATED ... AS IDENTITY INTEGER PRIMARY KEY メモリ上のエンジン内蔵カウンタ AUTO_INCREMENTカウンタを管理 し、MySQL 8.0では値を永続化 MySQL 8.0でRedoログを利用 独立したSEQUENCE オブジェクトを作成 たまに欠番は起こるくらい 今存在する最大のrowidを取得し その+1を行う。 idの再利用も行われる DDLにAUTOINCREMENTを利用すると 内部の管理用テーブルを生成してIDの再 利用を行わないことができる
22. SQLite AUTOINCREMENTの役割 INTEGER PRIMARY KEY ＋ AUTOINCREMENT 値を省略すればrowidを自動生成 通常は max(rowid) + 1 削除済みの最大値は再利用される sqlite_sequenceというテーブルに 最大値を保存 過去に使ったrowidを再利用しない その代わりオーバーヘッドが増える
23. DBMSの実行順 自動採番を歴史で見る 主キーとストレージ構造 日時型 文字列
24. MySQL・SQLiteのB-tree ページサイズは DBMSごとに異なる Root Page インデックス専用ページ Internal Page 2 Leaf 1 PK + Row Data Leaf 2 PK + Row Data Internal Page 3 Leaf 3 PK + Row Data 連番IDは挿入位置が偏りやすく ページ分割やランダムI/Oを抑えやすい MySQL（InnoDB）では主キー順に行データを格納 SQLiteではINTEGER PRIMARY KEYがrowidの別名 Leaf 4 PK + Row Data
25. PostgreSQLのB-tree 行データが置かれている B-Tree Index（主キー） Heap File（テーブル本体） Key: 1 → TID (0,1) Block 0 Tuple 1 / Tuple 2 Key: 2 → TID (0,2) Key: 3 → TID (1,1) Block 1 Tuple 3 / New Tuple (MVCC) PostgreSQLはインデックスと行データを分けて保持する 主キー値は行の物理配置を直接決めない ただしUUIDでもインデックスサイズや局所性の影響はある
26. DBMSの実行順 自動採番を歴史で見る 主キーとストレージ構造 日時型 文字列
27. 日時型の保存表現 MySQL PostgreSQL SQLite DATETIME(6) / TIMESTAMP(6) 専用の日時型。 TIMESTAMPはUTC変換が入る TIMESTAMPTZ(6) 8 bytes・1μs精度。内部値はUTCとして扱う TEXT / INTEGER / REAL 文字列やUnix時刻など、表現を自分で選ぶ
28. DBMSの実行順 自動採番を歴史で見る 主キーとストレージ構造 日時型 文字列
29. 文字列型と制約の担当範囲 MySQL PostgreSQL SQLite VARCHAR(255) varchar(255) / text TEXT 長さ制限あり。 文字セットと照合順序も定 義に出る 長さ制限の有無。 どちらも可変長データ 型親和性。長さ255は自動 では検証されない
30. まとめ DBMSのDDLがやや違うのは ・標準化が遅かったため、それぞれ独自の言語で設定してしまった ・IDの場合はアルゴリズムやDBMSのアーキテクチャの立ち位置 情熱を持ってこのDBMSについて色々調べたので 今回のテーマは満たしてるのでどうか後ろ指刺さないでください 普段は学ばないようなバイナリとかアーキテクチャの部分をしれたので貴 重な知識になったかなと思います。