Posted inMySQL

MySQLストレージ最適化：正しいデータ型選択でI/O高速化とRAM節約を実現する

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

データベースが遅い？犯人はデータ型かもしれない

MySQLを使い始めた頃、一番気にしていたのはクエリが動くかどうか、インデックスが正しいかどうかだった。INTかBIGINTか、VARCHAR(255)かVARCHAR(50)か——「余裕を持たせておけば安心」とそれだけ考えていた。

ところが、MySQL 8.0で約50GBのデータを抱えた本番データベースが異常なI/O高騰の兆候を見せ始めた。スキーマを改めて監査してみると、問題が次々と発覚した。user_idカラムにBIGINTを使っているのに、データは200万レコードを超えたことがなかった。statusカラムにTINYINTではなく文字列"active"/"inactive"を保存していた。emailカラムは「安全のため」と称してVARCHAR(1000)で宣言されていた。

スキーマをリファクタリングした後、クエリは大幅に速くなり、バッファプールの利用効率も格段に向上した。この記事では、その過程で得た実践的なノウハウをまとめる——教科書の理論ではなく、現場の経験だ。

データ型がI/OとRAMに影響する理由

MySQLはデータをページ単位で読み込む（InnoDBのデフォルトは16KB）。1ページに収められる行数は、各行のサイズによって決まる。行が小さいほど、1ページに多くの行を詰め込める。

過剰なデータ型による「太った」行が引き起こす実際の影響：

  • I/O増加：1ページあたりの行数が少なくなるため、同量のデータをスキャンするのに多くのディスク読み込みが必要になる
  • RAMの無駄：InnoDB バッファプール（キャッシュメモリ）が同じRAM容量でも少ない行しか保持できなくなる
  • インデックスの肥大化：キーサイズが大きくなるとB-treeインデックスのトラバーサルが遅くなる——インデックスはカラムのデータ型そのままで格納される

具体例：1,000万行のテーブルで、BIGINTの代わりにINTを選ぶことで1行あたり8バイト節約できれば、合計80MBのデータ削減になる。そのプライマリキーを格納するインデックスも考慮すると、実際の節約量はさらに大きい。

実践：各データ型の正しい選び方

1. 整数型——すべてにBIGINTをデフォルトで使うのをやめる

これが最もよく見かけるミスだ。整数型にはそれぞれ異なる範囲とサイズがある：

-- ストレージサイズと範囲：
TINYINT    -- 1バイト  | 最大127（符号あり）/ 255（符号なし）
SMALLINT   -- 2バイト | 最大32,767 / 65,535
MEDIUMINT  -- 3バイト | 最大8,388,607 / 16,777,215
INT        -- 4バイト | 最大2,147,483,647 / 4,294,967,295
BIGINT     -- 8バイト | 最大〜9.2 × 10^18

実際の適用例：

  • age、star_rating、小さなquantity → TINYINT UNSIGNED（0〜255）を使う。INTは不要
  • year、postal_code → SMALLINT UNSIGNEDまたはYEAR型
  • user_id、product_id → データが40億未満ならINT UNSIGNEDで十分。BIGINTより1行あたり4バイト節約できる
  • BIGINTを使うのは本当に必要な場合のみ：金融トランザクションID、Snowflake ID、分散システム
-- 悪い例：小規模システムのuser_idにBIGINTを使うと1行あたり4バイト無駄
CREATE TABLE orders (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT NOT NULL,
  status     TINYINT NOT NULL DEFAULT 1
);

-- 良い例：1行あたり8バイト節約（id + user_id分）
CREATE TABLE orders (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  status     TINYINT UNSIGNED NOT NULL DEFAULT 1
);

2. 文字列型——VARCHAR(n)は実態に合わせる。「念のため大きく」はNG

VARCHAR(n)は実際の長さ＋1〜2バイトのオーバーヘッドで格納される。しかしnの値は、MySQLがソートやグループ化を行う際の一時メモリに影響する：実際のデータがずっと短くても、テンポラリテーブルでは各行にn × charset_bytes分のメモリを割り当てる。

-- 実際の最大値に合わせた宣言：
email       VARCHAR(254),    -- RFC 5321: メールアドレスの最大長は254文字
username    VARCHAR(50),     -- ユーザー名が50文字を超えることはほぼない
phone       VARCHAR(20),     -- 国際電話番号の最大長は約15文字
slug        VARCHAR(200),    -- URLスラグの実態
country     CHAR(2),         -- ISO国コード: VN, JP, US — 固定長なのでCHARを使う

-- むやみな宣言を避ける：
-- email VARCHAR(1000)   -- ソート時に1行あたり3000バイトを割り当て（UTF-8 × 3）
-- name  VARCHAR(255)    -- 実際の最大値が100なら255で宣言するな

CHAR vs VARCHAR：country code、郵便番号、文字列形式のUUIDなど、長さが固定のデータにはCHAR(n)を使う。長さが可変のデータにはVARCHARを使う。

TEXT型：行内にインライン格納されない——InnoDBは別のページに格納し、行にはポインタのみを保持する。つまりTEXTカラムを読むたびに追加のI/Oが発生する。本当に長いコンテンツを格納する必要がある場合にのみ使用する：

-- 正しい使い方: 本当に長いコンテンツにはTEXT
article_content  MEDIUMTEXT,   -- ブログ記事本文
product_desc     TEXT,
raw_log          LONGTEXT

-- 誤った使い方: 短いデータにTEXTは使わない
note             TEXT          -- noteが数十文字程度なら不要
-- より適切な書き方:
note             VARCHAR(500)

3. ENUM——固定値セットに使う

いくつかの固定値を持つstatusカラム、roleカラム、typeカラム——これがENUMの真価を発揮する場面だ：

-- VARCHARは実際の文字列を格納するため、各値はlen(value)バイトを占有する
status VARCHAR(20)   -- 'active' = 6バイト, 'inactive' = 8バイト

-- ENUMはインデックス（1〜2バイト）を格納し、実際の値はテーブルメタデータに保持される
status ENUM('active', 'inactive', 'banned', 'pending')  -- 常に1バイト
role   ENUM('admin', 'editor', 'viewer', 'guest')

注意すべきデメリット：ENUM値の追加・削除にはALTER TABLEが必要（大テーブルでは高コスト）。値セットが頻繁に変わる場合は、TINYINT UNSIGNED＋アプリケーションコード内の定数の方が柔軟性が高い。

4. 日付・時刻型——TIMESTAMPはDATETIMEより1行あたり4バイト節約できる

-- DATETIME : 8バイト, 範囲1000〜9999年, タイムゾーン非対応
-- TIMESTAMP: 4バイト, 範囲1970〜2038年, サーバーのタイムゾーンに従って自動変換
-- DATE     : 3バイト, 時分秒を格納しない

-- 実際の使い方:
created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
birth_date  DATE,                     -- 3バイト, 時刻不要
event_start DATETIME(3)               -- ミリ秒精度が必要な場合、または2038年以降の範囲が必要な場合

単一タイムゾーンで動作し、2038年以降の日時を格納する必要がないシステムでは、DATETIMEの代わりにTIMESTAMPを使うことで1カラムあたり4バイト節約できる。1,000万行のテーブルに時刻カラムが2つあれば、それだけで80MBの削減になる。

5. NULL——可能な限りNOT NULLで宣言する

NULLを許可するカラムはNULLビットマップに1ビット余分に消費する。MyISAMほど影響は大きくないが、NULLはクエリを複雑にし、オプティマイザがカーディナリティを推定する際に誤った判断を下すことがある。

-- 常に値を持つカラムはNOT NULL + DEFAULTをデフォルトに
view_count  INT UNSIGNED NOT NULL DEFAULT 0,
is_active   TINYINT(1) NOT NULL DEFAULT 1,
created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

-- 「未設定」と「空値」を区別する必要がある場合のみNULLを許可
deleted_at  TIMESTAMP NULL DEFAULT NULL,   -- 論理削除パターン
verified_at TIMESTAMP NULL DEFAULT NULL    -- 未検証 = NULL

6. INFORMATION_SCHEMAで既存スキーマを監査する

最適化の前に、私はよくこのクエリを実行して「肥大化したカラム」を発見する：

-- 見直しが必要なデータ型を持つカラムをリスト表示
SELECT 
  TABLE_NAME,
  COLUMN_NAME,
  COLUMN_TYPE,
  CHARACTER_MAXIMUM_LENGTH,
  IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND (
    DATA_TYPE IN ('text', 'mediumtext', 'longtext')
    OR (DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH > 500)
    OR (DATA_TYPE = 'bigint')
    OR (DATA_TYPE = 'datetime')
  )
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- 各テーブルの実際のサイズを確認
SELECT 
  TABLE_NAME,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 2)              AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2)             AS index_mb,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 20;

まとめ：最初から適用すべき原則

スキーマ設計は「一度設定したら終わり」ではない。データが増えるにつれ、最初の誤った決断が返済困難な技術的負債となる。1億行のテーブルをリファクタリングするコストは、最初から正しく設計するコストの何倍にもなる。

テーブル作成時から適用している原則をまとめると：

  1. 必要最小限の整数型：実際の最大値を見積もり、対応する型を選ぶ。負の値が不要ならUNSIGNEDを追加する。
  2. VARCHAR(n)は実態に合わせる：「念のため」と255や1000で宣言しない——メモリソートに影響する。
  3. 変化の少ない固定値セットにはENUM：status、role、type——VARCHARは使わない。
  4. 2038年を超えないデータにはDATETIMEの代わりにTIMESTAMP——1行あたり4バイト節約。
  5. NOT NULL + DEFAULTをデフォルトに：明確な理由がある場合のみNULLを許可する。
  6. INFORMATION_SCHEMAで定期的に監査：データが大きくなる前に早期発見する。

これらの最適化は一見小さなことに思えるかもしれないが、私の50GBのデータベースでは、クエリ速度においても、バッファプールのRAM使用効率においても、実際に大きな違いをもたらした。

Share:
Tags: