『High Performance MySQL 第4版』を読んだ内容を章ごとにまとめています。

---

第6章 スキーマの設計と管理

概要

本章は、論理設計・物理設計・クエリ実行の相互作用を「MySQL固有の実装」を踏まえて整理し、さらにスキーマ変更が頻繁に起きる前提で、組織として安全・高速に変更を回すためのスキーマ管理（CI/CD・ツール選定・本番適用）までを扱う。

カウンターテーブルやサマリテーブルを追加することは、クエリを最適化する方法だが、メンテナンスにコストがかかる。

最適なデータ型を選択する

まず守るべき3原則

• 小さいほどよい：ディスク/メモリ/CPUキャッシュを節約し、処理も速くなる。将来拡張の余地は見積もるが、過大な型は避ける。
• シンプルがよい：整数は文字列より比較が安い。日時は文字列ではなく組み込み型、IPは文字列ではなく整数、といった本質に合う型を選ぶ。
• 可能ならNULLを避ける：インデックス/統計/比較が複雑になり、ストレージも増える。ただし既存スキーマのNULL潰しは効果が小さいことも多く、優先度を上げすぎない。

整数（TINYINT(8bit), SMALLINT(16bit), MEDIUMINT(24bit), INT(32bit), BIGINT(64bit)）

• 必要な範囲に対して最小の整数型を選ぶ。
• UNSIGNEDは負数を禁止し、正の上限をほぼ2倍にできる。(-128～127 -> 0～255)
• ただし演算は内部的にBIGINTで行われることが多く、格納型だけで計算コストが劇的に変わるわけではない（格納/比較/インデックスサイズへの影響が重要）。
• INT(11)のように桁数を指定できるが、これは対話型ツールで表示用に確保する文字数を指定するだけで、ストレージ・計算目的では指定した桁数は関係ない

実数（FLOAT, DOUBLE, DECIMAL）

• 分数部分を持つ数
• FLOAT/DOUBLEは近似（誤差あり）で省メモリ、DECIMALは正確だがコストが高い。
• 浮動小数点型FLOATは4バイト、DOUBLEは8バイト。DECIMALは精度に応じてストレージ量が変わり、一般により多くの容量と計算コストがかかるため「必要な場合のみ」使う。
• 浮動小数点型の内部計算にはDOUBLEを使用
• 金額など正確さが要る場合はDECIMALを検討しつつ、倍率を掛けた整数（例：最小通貨単位の倍数をBIGINT）で表現される設計方法もある。

文字列

VARCHAR/CHAR

• VARCHARは可変長（長さバイトが必要）。最大長が平均長より大きいときに有利。
• 長さバイト:VARCHAR カラムの最大長が255バイト以下の場合は1バイト、それ以上の場合は2バイト必要
  • VARCHAR(10): 最大11バイトの記憶スペースを使用
  • VARCHAR(1000): 最大1,002バイトの記憶スペースを使用
• 行が伸びる更新があると、断片化やページ分割など追加コストが起きうる（エンジン依存）。
• CHARは固定長で、短く固定の値（例：MD5ハッシュ）や頻繁に更新される列で有利になりうる。
• ※MD5は「固定長の例」であり、パスワード用途としての推奨ではない。
• 短い列ではCHARの方が省スペースになりうる（例：単一バイト文字セットならCHAR(1)は1バイトだが、VARCHAR(1)は長さバイト込みで2バイト）。
• CHARは末尾スペースが取得時に削除される挙動があるため、末尾スペースも意味を持つデータでは注意が必要。
• VARCHAR(200)のように余裕を盛ると、内部処理（ソート/一時テーブルなど）で悲観的に大きな領域が確保される場合があり不利になりうる。
• 値が同じ長さでも、定義上の最大長が大きいほど不利になる場面があるため、必要な分だけ確保するのが基本。

BLOB/TEXT

• 大量のデータをバイナリまたは文字列としてストアするための型
• 値がオブジェクト扱いになり、行内に1〜4バイトの参照＋外部領域に格納される場合がある（InnoDB）。
• BLOB/TEXTはそれぞれファミリーで、サイズ別にTINY*/SMALL*/MEDIUM*/LONG*がある（BLOBはSMALLBLOB、TEXTはSMALLTEXTの同義）。
• BLOBはバイナリ（文字セット/照合順序なし）、TEXTは文字列（文字セット/照合順序あり）。
• MySQLはBLOB/TEXTを全長でソートしない。先頭からmax_sort_lengthで設定されたバイトまでしか見ないため、ソート結果が直感とズレることがある（必要ならmax_sort_length調整）。
• max_sort_lengthが4の場合、”abcdef…” と “abcdZZZ…” は 先頭4バイトが同じ扱いになり、期待した順序にならない（同順位扱いで順序がブレる）
• ※ソートに指定することは非推奨
• 全長でのインデックスやソートに制約がある（インデックスをソートに使えない等）。
• 大きなバイナリ（例：画像）をDBに入れるとスキーマ変更など運用作業が遅くなりやすい。可能ならオブジェクトストア（S3など）に逃がし、DBはパス/ファイル名など参照を持つようにする。

ENUM

• あらかじめ定義された個別の文字列値のセットを格納することができる型
• 値の集合が限定されるなら省サイズになりうる（定義した候補数に応じて1〜2バイトにパックされる）。
• 内部的には「定義リスト中の位置」を表す整数として保持されるため、数値コンテキストで見ると数値になるなど二面性がある。
• ENUM('1','2','3')のように数字っぽい値を並べると紛らわしいので避ける。
• ソート順は文字列順ではなく内部整数順になる。意図した順序で並べたいなら、定義順を工夫する。
• FIELD()で明示順序も指定できるが、インデックスでのソートが効かなくなる。
• 両側がENUMならJOINが速くなるケースもある一方、VARCHAR/CHARとENUMのJOINは遅くなることがあるため、関連テーブル間で型を揃えるのが前提。
• 有効値の追加/変更がスキーマ変更を必ず伴う。変更頻度が高い領域に使うと運用コストが跳ねる（後半のスキーマ管理の仕組みが前提）。

日付と時刻

• 日時を持つならDATETIMEかTIMESTAMPが主候補。
• DATETIME：広い範囲（1000〜9999年）。タイムゾーン非依存で、YYYYMMDDHHMMSS相当の形式で格納される。8バイト。
• TIMESTAMP：Unix timestamp（1970-01-01 00:00:00 GMTからの秒数）として格納される。4バイトで省サイズだが範囲が狭い（1970〜2038年）。
• 表示値はタイムゾーン設定（サーバー/OS/接続）に依存するため、複数タイムゾーンをまたぐ場合にDATETIMEと挙動が大きく異なる。
• 先頭のTIMESTAMP列は、未指定INSERTで「現在時刻が入る」、UPDATEで「自動更新される」などの特殊なデフォルト挙動がある（列ごとに設定可能）。NOT NULLがデフォルトという違いもある。
• UNIX_TIMESTAMP()/FROM_UNIXTIME()で相互変換できる。
• 代替案として、UTCのUnix epoch（秒）を整数で持つ設計もある（32-bit signedなら2038年、32-bit unsignedなら2106年、64-bitならさらに先）。ただし設計判断は「必要な範囲」「容量」「小数秒」「タイムゾーン処理をDBに寄せるかコードで扱うか」で決める。

ビット圧縮型

• ビット圧縮型は「複数のtrue/false（フラグ）をビットに詰めて、列数やサイズを減らそう」という発想の型/表現。
• BIT：一見省スペースに見えるが、InnoDBでは結局「そのビット数を入れられる最小の整数型」で持つため、期待ほど容量が減らないことがある。
• 取得すると「0/1」ではなくバイナリっぽく見えるなど扱いが紛らわしいので、基本は避ける（使うなら表示/比較に注意）。
• 複数フラグをまとめたいなら、SETか「整数ビットマスク（ビット演算）」が候補。
• SET：書きやすいが、要素（値）の追加はALTER TABLEが必要。
• 整数ビットマスク：TINYINTなどにフラグを割り当て、&で判定する。要素追加にDDLが不要なのは強みだが、クエリが読みにくくなりやすい（ACL例）。

JSONデータ

• MySQLにはJSON型があり、JSON内のキーをSQLから参照できる。
• ただし「列を作らずに済むから」とJSONに寄せすぎると、サイズと検索で不利になりやすいので、SQL（列）版と比べて考える。
• サイズ：JSONはキー名や記号（{} : など）ぶんが増える。例では列に展開したSQL版（16KB×3ページ）の方が、JSON版（16KB×5ページ）より小さかった。
• 検索：全件取得はどちらも十分速い一方、単一行検索は「列＋インデックス」で1行に絞れるSQL版が有利になりやすい（例：SQL 0.33ms、JSON 0.58ms）。
• 対策：JSONでも、よく検索するキーを生成列（generated/virtual column）として取り出してインデックスを張れば、性能をSQL版に近づけられる。
• JSONの「入れやすさ」と、性能/サイズのトレードオフ。アクセス回数が多いほど差が積み上がる。
• JSONが向く：柔軟性が欲しい／属性が変わりやすい／アクセス頻度がそこまで高くない、など「入れやすさ」を優先したい場合。
• JSONを避けたい：サイズが重要／条件検索や単一行検索を大量に回す、など性能差が効いてくる場合。
• 折衷案：全体はJSONで持ちつつ、検索で使うキーだけは生成列＋インデックスで引き上げる。

識別子の選び方

• 識別子は「その行を指すためのID」で、検索やJOINで何度も比較されるので、ここが重いと全体が遅くなりやすい。
• 基本方針は「小さく・速い型で・揃える」。
• 整数が第一候補：たいてい最も速く、AUTO_INCREMENTも使える。将来の件数を見積もって枯渇しないサイズを選ぶ。
• 型は完全一致：親子テーブル（主キー/外部キー）で、UNSIGNEDまで含めて同じにする。違う型を混ぜると暗黙変換が起きて遅くなったり、バグになりやすい。
• 必要最小サイズ：例えばTINYINTで足りるのにINTにすると、外部キーやインデックスが太り、全体が重くなる。
• ENUM/SETは基本は識別子に不向き（「状態/種別」など固定の定義用途なら例外的にあり）。
• 文字列ID（特にMD5/SHA1/UUIDのようなランダム値）は、インデックスの並びがバラけやすく、INSERTや一部の検索を遅くしうる。
• UUIDを保存するなら、（文字列のままより）UNHEX()でBINARY(16)にして格納し、表示はHEX()にする方法がある。

特殊なデータ

• 「見た目は文字列」でも、実体が数値などのデータは、その本質に合う型で持つ（例：IPv4）。
• IPv4アドレスはVARCHAR(15)で持ちがちだが、本質は符号なし32ビット整数なので、整数として格納した方が小さく速い。
• 文字列だと概算で~16バイト、整数なら4バイトにできる。
• INET_ATON()/INET_NTOA()で文字列 ↔ 数値を相互変換できる。
• 読みやすさが必要なら、ビューで表示用に変換した形を見せる方法がある。

MySQLにおけるスキーマ設計の落とし穴

カラムが多すぎる

• MySQLはストレージエンジンから受け取った行バッファを列へデコードするコストがあり、極端に幅広いテーブル（数百カラム）はCPUを食いやすい。
• 実際に使う列が少なくても「列数が多い」だけで負担になることがある（デコードコスト）。

JOINが多すぎる

• EAV（Entity-Attribute-Value）は典型的な悪手で、MySQLのJOINテーブル数制限（61）に当たりやすい。
• 制限未満でも、プラン生成/最適化のコストが問題になる。高並行・低レイテンシが要るなら、目安として1クエリあたり十数テーブル以下を狙う。
• 補足：EAVは「entity_id＋attr_name＋attr_value」のように、1行＝1属性で柔軟に持つ設計。列追加を避けられる一方、欲しい形（通常の行/列）に戻すために自己JOINが増えやすく、MySQLでは特に辛くなりやすい。

ENUMの濫用

• 数値の羅列（例：'0'..'31'）をENUMで表すなど、本来は「辞書テーブル＋整数（外部キー）」で表すべきものまでENUM化すると設計が歪む。
• 「型があるから何でもENUM」は危険。増減でDDLが必要になる運用コストも含めて判断する。

SETの濫用

• SETは「複数選択」を表す型。set('Y','N')のように実質ブール（true/false）をSETで表すのは誤り。
• その場合はENUM('Y','N')やTINYINTなど、意図に合う型を選ぶ。

NULLの過剰回避

• NULLを避けるのは有益だが、未知値を表すべき場面で魔法の定数に逃げるとバグを呼ぶ（例：DATETIMEの'0000-00-00 00:00:00'）。
• 新規システムではSQL_MODEで不正な日付を禁止するのが有効。
• MySQLはNULLもインデックスに含める（Oracleと異なる）。

スキーマ管理

• ゴールは、スキーマ変更を「たまにDBチームが手作業でやるイベント」ではなく、コードと同じようにレビュー・テスト・安全な適用まで仕組み化して、開発速度を落とさないこと。

データストア・プラットフォームとして考える

• スキーマは時間とともに進化するため、変更を例外ではなく頻繁に起きるイベントとして設計する。
• 変更作業がDBチームの手作業に寄り、組織全体のボトルネックにならないようにする。
• 「エンジニアの速度（設計→本番までの時間）」が重要で、スキーマ変更がその足かせになりやすい。

パートナーチームを成功させる

• DBチームが全部やるのを前提にせず、プロダクトチームが安全に変更を出せる道筋を整備する。

CIと統合する

• スキーマ変更もコードと同様にPR/CIで扱い、既存のデプロイ文化に寄せる（開発速度の改善に効く）。
• 変更のレビュー・自動テスト・環境昇格を、普段のソフトウェアデプロイと同じ流れに寄せる。

スキーマ変更をソース管理する

• スキーマもコードと同じくGitで管理する（=「スキーマ as code」）。
• 目的は2つ：①「今あるべきスキーマ」を明確にする ②「いつ・誰が・何を変えたか」を追えるようにする（監査/コンプライアンスにも効く）。
• 変更はPRでレビューし、CIで自動チェックできる形（差分/DDL/マイグレーション）で管理する。
• ツール選定の観点：
• 有償：コストモデル、オンライン変更の実装方針、既存の開発言語/CIとの統合性。
• OSS：例としてSkeema（差分追跡に強い）。CI統合や本番適用権限はセキュリティと協調が必要。
• 運用：lintでルール（charset/collation、禁止したい外部キー等）を機械的に担保し、全社DBを1リポジトリに集約して重くならないようにする。

本番でスキーマ変更を走らせる

ネイティブDDL

• MySQL自身のDDL（オンラインDDL含む）で変更する。8.0で選択肢は増えたが、万能ではない。
• 変更内容によってINPLACE/INSTANTでできないものがあるので、本番前に可否と影響を把握しておく（例：主キー変更、charset変更、暗号化有効化、外部キー追加/削除など）。
• 大規模テーブルでは、DDLが重くてロールバックしたり、負荷が高くなりすぎることがあるため、速度制御（スロットリング）や段階適用が欲しくなる。

外部ツール

• pt-online-schema-change：トリガで差分を追随しながら入れ替える。実績は強いが、トリガの制約や性能影響、並行マイグレーションの難しさがある。外部キーも要注意。
• gh-ost：トリガなしで、レプリカとして接続してrow-based binlogを読みながら追随する。外部キーがあると基本中断するため、「外部キーを使わない」運用とセットなら安全寄り。
• 選定は、MySQLバージョン、外部キー有無、binlog設定、許容できる運用トレードオフで決める。

スキーマ変更のCI/CDパイプライン

• やりたいことは「スキーマ変更も、コードと同じようにPR→CI→段階適用で回す」。
• DBクラスタごとにリポジトリを分ける：DBごとにルール（lint）やスピード感が違うため、1つにまとめて全社で詰まらないようにする。
• 手順を固定する：ラップトップで作成→PR→CIでチェック/テスト→検証環境→本番、を文書化し、PRモデルで自動化する。
• 安全のデフォルトを用意する：オンライン変更ツールのベース設定（最大スレッド/最大負荷など）を配布し、設計ポリシーをpre-commit/CIで機械的に守らせる。
• チームで運用を変えられる：新規プロダクトは自動昇格寄り、ミッションクリティカルは人の承認必須、など柔軟にする。

まとめ

• 要点は「小さく・シンプルに」。型選びも設計も運用も、これがパフォーマンスと扱いやすさに直結する。
• MySQLは実装上、列が多すぎる・JOINが多すぎる設計のコストが出やすいので、極端を避ける。
• VARCHAR/TEXT、ENUM/SET/BITなどは便利だが落とし穴もあるので、挙動と運用コストまで理解して使う。
• スキーマは必ず変わる前提で、ソース管理＋CI/CD＋オンラインDDL/外部ツールを組み合わせて、安全に速く変更を回す。

業務での活かし方

• 設計
  • サイズ最適化（小さい型、不要な可変長/NULLを減らす）を優先し、CPU/I/Oを抑える。
  • 分数を扱う数値は、誤差が困るならDECIMAL（または倍率を掛けた整数）を検討する。
  • TIMESTAMPは省サイズだが範囲が狭い（1970〜2038年）＋タイムゾーン依存なので、要件に合うか必ず確認する（合わないならDATETIMEやepoch整数も候補）。
  • CHARは固定長で更新が多い列に向くことがあるが、末尾スペースの挙動に注意する。
  • VARCHARは必要な分だけ確保する（最大長を盛ると、ソート/一時テーブルなどの内部処理で不利になりうる）。
  • 識別子は整数＋AUTO_INCREMENTを第一候補にし、関連テーブルで型を完全一致させる（UNSIGNEDまで揃える）。
  • UUID等のランダム文字列は扱いが重くなりやすい。保存するならBINARY(16)（UNHEX()/HEX()）なども検討する。
  • IPv4はVARCHAR(15)より、符号なし32ビット整数として持ち、INET_ATON()/INET_NTOA()で変換する。
  • 画像など大きいバイナリはDBに入れず、オブジェクトストア（S3など）＋DBは参照（パス/キー）にする。
  • BLOB/TEXTは全長ソート/インデックスに制約があるので、ソートキーにしない設計を優先する。
  • JSONは「入れやすさ」と「サイズ/検索性能」のトレードオフ。検索で使うキーは生成列＋インデックスで補強する。
  • MySQLは「列が多すぎる」「JOINが多すぎる」設計のコストが出やすい。EAVのような設計は特に避ける。
• 運用
  • スキーマはGitで管理し、PRレビュー→CIチェック→検証→本番の流れで回す（スキーマ as code）。
  • lintでチームのルール（charset/collation、外部キー方針など）を機械的に守らせる。
  • 本番DDLはINSTANT/INPLACE可否を事前に把握し、難しい変更はオンライン変更ツール（gh-ost/pt-online-schema-change等）も含めて選ぶ。