MySQL ベストプラクティス

コア原則

• ストレージエンジン（ほとんどの用途で InnoDB）を選択してスキーマを設計する
• EXPLAIN とプロパーなインデックスを使ってクエリを最適化する
• 適切なデータ型を使用してストレージを最小化し、パフォーマンスを向上させる
• コネクションプーリングとクエリキャッシュを適切に実装する
• MySQL 固有のセキュリティ強化プラクティスに従う

スキーマ設計

ストレージエンジンの選択

• デフォルトエンジンとして InnoDB を使用する（ACID準拠、行レベルロック）
• MyISAM は読み取り集約的で非トランザクション的なワークロードのみを考慮する
• MEMORY エンジンは高速要件のある一時テーブルに使用する

CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(12, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
        NOT NULL DEFAULT 'pending',
    INDEX idx_customer (customer_id),
    INDEX idx_date_status (order_date, status),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

データ型

• ニーズに合う最小のデータ型を使用する
• 可能な限り BIGINT より INT UNSIGNED を選ぶ
• 金銭計算には FLOAT/DOUBLE ではなく DECIMAL を使用する
• 固定値セットには ENUM を使用する
• 可変長文字列には VARCHAR、固定長には CHAR を使用する
• 完全な Unicode サポートのため、常に utf8mb4 文字セットを使用する

-- 適切なデータ型の選択
CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    quantity SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    weight DECIMAL(8, 3),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_sku (sku)
) ENGINE=InnoDB;

主キー

• InnoDB テーブルには AUTO_INCREMENT 整数主キーを使用する
• 分散システムの場合、BINARY(16) に保存された UUID を検討する
• 可能な限り複合主キーを避ける

-- UUID ストレージの最適化
CREATE TABLE distributed_events (
    event_id BINARY(16) PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- UUID で挿入
INSERT INTO distributed_events (event_id, event_type, payload)
VALUES (UUID_TO_BIN(UUID()), 'user_signup', '{"user_id": 123}');

-- UUID でクエリ
SELECT * FROM distributed_events
WHERE event_id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');

インデックス戦略

インデックスタイプ

• ほとんどのクエリには B-tree インデックス（デフォルト）を使用する
• テキスト検索には FULLTEXT インデックスを使用する
• 地理的データには SPATIAL インデックスを使用する
• 頻繁に実行されるクエリはカバリングインデックスを検討する

-- 一般的なクエリパターン用の複合インデックス
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- カバリングインデックス
CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, status, total_amount);

-- テキスト検索用の Fulltext インデックス
ALTER TABLE products ADD FULLTEXT INDEX ft_name_desc (name, description);

-- Fulltext を使った検索
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('wireless bluetooth' IN NATURAL LANGUAGE MODE);

インデックスガイドライン

• WHERE、JOIN、ORDER BY、GROUP BY で使用される列にインデックスを張る
• 複合インデックスでは最も選択性の高い列を最初に配置する
• 低いカーディナリティの列だけにはインデックスを張らない
• 未使用のインデックスを監視して削除する

-- インデックスの使用状況を確認
SELECT
    table_schema, table_name, index_name,
    seq_in_index, column_name, cardinality
FROM information_schema.STATISTICS
WHERE table_schema = 'your_database'
ORDER BY table_name, index_name, seq_in_index;

クエリ最適化

EXPLAIN 分析

• EXPLAIN を使ってクエリ実行計画を分析する
• フルテーブルスキャン（type: ALL）を探す
• インデックスが適切に使用されているかを確認する
• 検査された行数と返された行数を監視する

EXPLAIN FORMAT=JSON
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.created_at > '2024-01-01'
GROUP BY c.customer_id;

クエリベストプラクティス

• 本番コードでは SELECT * を避ける
• ページネーションには LIMIT を使用する
• サブクエリより JOIN を優先する
• 繰り返されるクエリにはプリペアドステートメントを使用する

-- 効率的なページネーション
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = ?
ORDER BY order_date DESC
LIMIT 20 OFFSET 0;

-- キーセットページネーション（大きなオフセットではより効率的）
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = ?
    AND (order_date, order_id) < (?, ?)
ORDER BY order_date DESC, order_id DESC
LIMIT 20;

一般的な落とし穴を避ける

-- 避ける: インデックスされた列への関数適用
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- 推奨: 範囲比較
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- 避ける: 暗黙的な型変換
SELECT * FROM users WHERE user_id = '123';  -- user_id は INT

-- 推奨: 適切な型
SELECT * FROM users WHERE user_id = 123;

-- 避ける: 先頭にワイルドカードの LIKE
SELECT * FROM products WHERE name LIKE '%phone%';

-- 推奨: テキスト照合には Fulltext 検索
SELECT * FROM products WHERE MATCH(name) AGAINST('phone');

JSON サポート

• 半構造化データには JSON データ型を使用する（MySQL 5.7+）
• 頻繁にアクセスされる JSON フィールド用に生成列を作成する
• クエリには適切な JSON 関数を使用する

CREATE TABLE events (
    event_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSON NOT NULL,
    -- インデックス用の生成列
    user_id INT UNSIGNED AS (payload->>'$.user_id') STORED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
);

-- JSON データを照会
SELECT event_id, event_type,
       JSON_EXTRACT(payload, '$.action') AS action
FROM events
WHERE JSON_EXTRACT(payload, '$.user_id') = 123;

-- または -> 演算子を使用
SELECT * FROM events WHERE payload->'$.user_id' = 123;

トランザクション管理

• トランザクション処理テーブルは InnoDB を使用する
• ロック競合を最小化するためトランザクションは短く保つ
• 適切な分離レベルを選択する
• デッドロックを適切に処理する

-- エラーハンドリング付きトランザクション
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- エラーをチェックしてコミットまたはロールバック
COMMIT;

-- 分離レベルを設定
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

レプリケーションと高可用性

リードレプリカ

• リードクエリをレプリカに向ける
• 読み取り/書き込み分割のコネクションプーリングを使用する
• レプリケーションラグを監視する

-- レプリケーションステータスを確認
SHOW SLAVE STATUS\G

-- レプリケーションラグを確認
SELECT TIMESTAMPDIFF(SECOND,
    MAX(LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP),
    NOW()) AS lag_seconds
FROM performance_schema.replication_applier_status_by_worker;

セキュリティ

• 強力なパスワードと安全な接続（SSL/TLS）を使用する
• 最小権限の原則を適用する
• SQL インジェクション防止のためプリペアドステートメントを使用する
• 機密操作を監査する

-- 権限が限定されたユーザーを作成
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

-- SSL を必須にする
ALTER USER 'app_user'@'%' REQUIRE SSL;

-- ユーザー権限を表示
SHOW GRANTS FOR 'app_user'@'%';

メンテナンス

定期メンテナンスタスク

-- テーブルを分析してオプティマイザー統計を更新
ANALYZE TABLE orders, customers, products;

-- テーブルを最適化（スペース回収、デフラグ）
OPTIMIZE TABLE orders;

-- テーブルの整合性を確認
CHECK TABLE orders;

監視クエリ

-- スロークエリを検索
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- 現在のプロセスリスト
SHOW FULL PROCESSLIST;

-- InnoDB ステータス
SHOW ENGINE INNODB STATUS;

-- テーブルサイズ
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    table_rows
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
ORDER BY data_length DESC;

設定の推奨事項

# my.cnf 推奨設定

[mysqld]
# InnoDB 設定
innodb_buffer_pool_size = 70%_of_RAM
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# コネクション設定
max_connections = 500
wait_timeout = 300
interactive_timeout = 300

# クエリキャッシュ（MySQL 8.0+ で無効）
query_cache_type = 0

# スロークエリログ
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2