MySQLで1000万件を超えるデータを扱う場合、適切な設計が欠かせません。本稿では、既存スキーマの制約下でクエリ性能を劇的に改善する手法を解説します。

テスト環境構築

以下のスキーマで1500万件のトランザクションデータを生成します。

CREATE TABLE `transactions` (
  `transaction_id` INT NOT NULL AUTO_INCREMENT,
  `customer_id` INT DEFAULT NULL,
  `transaction_date` DATE NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`transaction_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_customer_amount` (`customer_id`,`amount`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `customers` (
  `customer_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

データ生成プロシージャをマルチスレッド実行：

DELIMITER $$
CREATE PROCEDURE generate_customers()
BEGIN
  DECLARE counter INT DEFAULT 0;
  WHILE counter < 1000 DO
    INSERT INTO customers (name) 
    VALUES (CONCAT('Customer_', FLOOR(RAND() * 1000000)));
    SET counter = counter + 1;
  END WHILE;
END$$

CREATE PROCEDURE generate_transactions()
BEGIN
  DECLARE cust_id INT;
  DECLARE cust_counter INT DEFAULT 0;
  WHILE cust_counter < 1000 DO
    SELECT customer_id INTO cust_id 
    FROM customers LIMIT cust_counter, 1;
    
    INSERT INTO transactions (customer_id, transaction_date, amount)
    SELECT cust_id, 
           DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * 1096) DAY),
           ROUND(RAND() * 1000, 2)
    FROM seq_0_to_9999; -- 10000件生成するシーケンステーブル
    SET cust_counter = cust_counter + 1;
  END WHILE;
END$$
DELIMITER ;

最適化ステップ

初期クエリの性能問題

顧客ごとの取引金額を集計する基本クエリ：

SELECT c.*, SUM(t.amount) AS total 
FROM customers c 
LEFT JOIN transactions t ON c.customer_id = t.customer_id
GROUP BY c.customer_id;

EXPLAIN結果では全テーブルスキャンが発生し、実行時間は190秒以上。特にtransactionsテーブルでtype: ALLが確認できます。

カバレッジインデックスの適用

従来の単一カラムインデックスでは逆効果でした。代わりに複合インデックスを最適な順序で作成：

DROP INDEX IF EXISTS idx_trans_amount ON transactions;
CREATE INDEX idx_trans_customer_amount 
ON transactions (customer_id, amount);

このインデックス構成により、GROUP BY処理でインデックスのみで完結するようになり、実行時間が10秒に改善。EXPLAINではUsing indexフラグが確認できます。

データボリュームの削減

不要なデータを早期に除外する戦略：

SELECT c.*, SUM(t.amount) AS total 
FROM customers c 
LEFT JOIN transactions t ON c.customer_id = t.customer_id
WHERE c.customer_id > 500
GROUP BY c.customer_id;

WHERE句で顧客IDを絞り込むことで、スキャン対象を50%削減。実行時間が7秒に短縮されました。

強制インデックスの活用

特定条件下で最適化が機能しない場合の対策：

SELECT c.*, SUM(t.amount) AS total 
FROM customers c 
LEFT JOIN transactions t FORCE INDEX (idx_trans_customer_amount) 
  ON c.customer_id = t.customer_id
WHERE t.customer_id IN (501,502,503,504,505)
GROUP BY c.customer_id;

クエリ最適化器が適切なインデックスを選択しないケースで、明示的なインデックス指定によりパフォーマンスを安定化できます。

最適化の核心原則

• インデックス設計ではクエリ実行順序を考慮（WHERE→GROUP BY→SELECTの順）
• カバレッジインデックスでリターンテーブルを回避
• 結合操作ではフィルタリング後の小規模データセットを駆動表に設定
• 1000万件超のデータではパーティショニングを前提に設計

EXPLAINのtype解説

アクセスタイプの重要度順：

• ref：非一意インデックス利用（最適化の目安）
• range：範囲検索（WHERE条件で有効）
• index：インデックストラバーサル（カバレッジインデックス目指す）
• ALL：全テーブルスキャン（避けるべき状態）

カバレッジインデックスではtype: refかつExtra: Using indexが理想形です。