MySQLのパフォーマンス最適化：低速クエリの分析とインデックスのチューニングに関する実践ガイド

年末セール期間中、アリスのECプラットフォームでは、データベースの応答時間が50ミリ秒から5秒へと急上昇し、ユーザーからは「ページが表示されない」「注文送信がタイムアウトする」といった苦情が相次ぎました。運用チームが緊急調査を行ったところ、最適化されていない低速クエリが複数存在し、システム全体の動作を著しく遅らせていたことが判明しました。低速クエリのロギングを有効にして問題のあるSQL文を特定し、EXPLAINを用いて実行プランを分析し、不足していたインデックスを追加し、非効率なクエリを書き換え、InnoDBの設定パラメータを調整した結果、最終的に応答時間を100ミリ秒未満まで短縮することに成功しました。

1. 学習内容

• スロークエリログの設定、収集、および分析の方法
• EXPLAIN 実行プランの各フィールド（type/key/rows/Extra）に関する詳細な分析
• インデックス最適化の主要な戦略：無効なインデックスの回避、カバリングインデックス、および複合インデックスの最左プレフィックス
• クエリの書き換え手法：SELECT * を回避する、サブクエリを JOIN に置き換える、深いページネーションを最適化する
• 主要な設定パラメータの調整：innodb_buffer_pool_size、max_connections など

2. エンドツーエンドのパフォーマンス最適化プロセス

パフォーマンスの最適化は、一度きりの作業ではなく、「発見→分析→最適化→検証」という循環的なプロセスです。

flowchart TD
    A[Identifying Slow Queries] --> B[EXPLAIN Analyze the Execution Plan]
    B --> C{Issue Type?}
    C -->|Missing Index| D[Index Optimization]
    C -->|Inefficient query syntax| E[Query Rewrite]
    C -->|Unreasonable configuration| F[Configuration Tuning]
    D --> G[Verifying Performance Improvements]
    E --> G
    F --> G
    G -->|Still does not meet the standards| A
    G -->|Meet the requirements| H[Deployment Monitoring]

flowchart TD
    A[Identifying Slow Queries] --> B[EXPLAIN Analyze the Execution Plan]
    B --> C{Issue Type?}
    C -->|Missing Index| D[Index Optimization]
    C -->|Inefficient query syntax| E[Query Rewrite]
    C -->|Unreasonable configuration| F[Configuration Tuning]
    D --> G[Verifying Performance Improvements]
    E --> G
    F --> G
    G -->|Still does not meet the standards| A
    G -->|Meet the requirements| H[Deployment Monitoring]

3. スロークエリログ

スロークエリログは、パフォーマンスの問題を特定するための第一の防衛線であり、実行時間が一定の閾値を超えたSQL文を自動的に記録します。

(1) はじめにおよび設定

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

(2) ログ解析ツール

mysqldumpslow は、MySQL に組み込まれているスロークエリログ集計ツールであり、クエリを実行時間や頻度順に並べ替えます。

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

(3) パフォーマンス・スキーマの代替手段

MySQL 5.6 以降のバージョンでは、Performance Schema を使用して遅いクエリを収集する機能がサポートされており、設定ファイルを変更することなく動的に有効化することができます。

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

▶ 例：スロークエリログを有効にし、上位5つのスロークエリを特定する

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 100;
SHOW VARIABLES LIKE 'slow_query_log_file';

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

▶ 例：sysビューを使用して、処理に時間がかかるクエリをすばやく確認する

SELECT query_id, LEFT(query, 80) AS query_text,
       exec_count, avg_timer_ms, rows_examined
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY avg_timer_ms DESC LIMIT 10;

4. EXPLAIN 実行計画の詳細な分析

EXPLAIN は、SQL チューニングにおいて最も重要な診断ツールであり、MySQL がクエリをどのように実行するかを示します。

(1) EXPLAIN 出力フィールドの概要

(2) 「type」フィールドの詳細な説明

typeフィールドは、EXPLAINの中で最も重要なフィールドであり、クエリの効率を直接反映しています。

(3) 追加フィールドのキー値

• Using index: カバレッジインデックス。テーブルを参照する必要がなく、最高のパフォーマンスを発揮する
• Using where: ストレージエンジンがデータを返した後、サーバー層でフィルタリングが行われます
• Using filesort: インデックスを使用したソートはできません。追加のソート操作が必要です。
• Using temporary: 一時テーブルが使用されました。これは、GROUP BY 句にインデックスが設定されていない場合によく見られる現象です。
• Using index condition: インデックス・プッシュダウン（ICP）により、テーブル検索の回数を削減

▶ 例：EXPLAIN を使用した単一テーブルクエリの分析

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE user_id = 42 AND status = 'PAID';

+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | Extra                     |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user | 4    |  120 | Using where               |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+

▶ 例：EXPLAIN を使用した結合クエリの分析

EXPLAIN SELECT o.order_id, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.create_time > '2025-01-01';

5. インデックスの最適化戦略

インデックスはクエリを高速化する強力なツールですが、不適切なインデックスを使用することは、インデックスを全く使用しないことよりも危険です。

(1) カバレッジ指数

カバリングインデックスとは、クエリで使用されるすべてのフィールドがインデックスに含まれているため、行データを読み取るためにテーブルにアクセスする必要がないインデックスのことです。

ALTER TABLE orders ADD INDEX idx_cover (user_id, status, total_amount);

SELECT user_id, status, total_amount FROM orders WHERE user_id = 42 に対するクエリでは、データはすべてインデックスから取得されます。

(2) 複合インデックスと最左前置子

複合インデックスは「最左接頭辞の原則」に従います。つまり、クエリ条件はインデックスの最左の列から一致し始めなければなりません。

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

(3) インデックス障害のシナリオに関するクイックリファレンス

▶ 例：複合インデックスの最左端のプレフィックスの検証

ALTER TABLE products ADD INDEX idx_cat_brand_price (category_id, brand_id, price);

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND brand_id = 10;
EXPLAIN SELECT * FROM products WHERE brand_id = 10;

2番目のクエリでは、左端の列である category_id をスキップするため、type が ALL となります。

▶ 例：カバリングインデックスを使用してテーブルの参照を排除する

ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, total_amount);

EXPLAIN SELECT user_id, status, total_amount
FROM orders WHERE user_id = 42;

「Extra」欄に Using index と表示されている場合、テーブル参照は不要であることを意味します。

6. クエリ最適化の手法

インデックスがあっても、クエリの記述が不適切であれば、その恩恵を受けることはできません。

(1) SELECT * の使用は避ける

SELECT * を使用すると、すべての列が読み込まれ、I/O が増加し、カバリングインデックスが機能しなくなる可能性があります。

SELECT id, username, email FROM users WHERE id = 100;

(2) サブクエリを JOIN に書き換える

関連するサブクエリは各行ごとに1回実行されますが、これをJOINに書き換えることで、スキャン回数を大幅に削減できます。

SELECT o.order_id, o.total_amount
FROM orders o
WHERE o.user_id IN (SELECT id FROM users WHERE vip_level >= 3);

最適化対象：

SELECT o.order_id, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id AND u.vip_level >= 3;

(3) 詳細なページネーションの最適化

オフセットが大きい場合、従来の LIMIT offset, n の性能は著しく低下します。

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

最適化計画—カーソルによるページネーション：

SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

(4) クエリ最適化手法のクイックリファレンス

▶ 例：ディープページネーションの最適化比較

SELECT * FROM orders ORDER BY create_time LIMIT 500000, 20;

遅延関連付けに最適化されています：

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY create_time LIMIT 500000, 20) t
ON o.id = t.id;

このサブクエリは主キーのみを取得し、カバリングインデックスを使用してIDを素早く特定した後、テーブルから完全なデータを取得します。

▶ 例：一括挿入の最適化

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 201, 2), (1001, 202, 1), (1001, 203, 5);

ループ内で単一のINSERT文を3回実行する場合と比較して、これによりネットワークの往復回数とトランザクションのコミットにかかるオーバーヘッドが2回分削減されます。

7. テーブル構造の最適化

適切に設計されたテーブル構造こそがパフォーマンスの基盤であり、SQLを書き直しても、既存の構造を最適化することしかできません。

(1) フィールドタイプの選択

(2) アンチパラダイム・デザイン

クエリ負荷が高い状況では、適度な冗長性を確保することで、JOIN操作の回数を減らすことができます。

CREATE TABLE order_summary (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(64),
    total_amount DECIMAL(10,2),
    INDEX idx_user (user_id)
);

orders テーブルの username 列を複製し、クエリごとに users テーブルと JOIN を行う必要をなくします。

▶ 例：フィールドタイプの最適化の比較

ALTER TABLE products MODIFY COLUMN weight DECIMAL(8,2);
ALTER TABLE products MODIFY COLUMN description TEXT;

プライマリレコードの長さを短縮するために、weight 列を VARCHAR から DECIMAL に変更し、description 列を VARCHAR(5000) から TEXT に変更してください。

8. 設定パラメータの調整

MySQLの設定パラメータは、ストレージエンジンの動作やリソースの割り当てに直接影響を与えます。

(1) 主な設定パラメータと推奨値

(2) パラメータの動的な調整

一部のパラメータは、インスタンスを再起動することなくオンラインで変更できます。

SET GLOBAL innodb_buffer_pool_size = 8589934592;
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

▶ 例：現在の InnoDB バッファプールのヒット率を確認する

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests      | 1024000   |
| Innodb_buffer_pool_reads              | 512       |
+---------------------------------------+-----------+

ヒット率 = 1 - (512 / 1024000) ≈ 99.95%；バッファプールのサイズは適切です。

9. パフォーマンス・スキーマの概要

パフォーマンス・スキーマは、MySQLに組み込まれたパフォーマンス監視エンジンであり、スロークエリログよりも詳細な診断データを提供します。

(1) パフォーマンス・スキーマを有効にする

SHOW VARIABLES LIKE 'performance_schema';
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

(2) 一般的な監視ビュー

▶ 例：使用されていないインデックスの確認

SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'ecommerce'
ORDER BY object_name;

使用されていないインデックスはストレージ容量を無駄にし、INSERT や UPDATE 操作の際に余分なメンテナンスを必要とするため、速やかに削除すべきです。

10. 包括的な実践演習：パフォーマンス診断プロセスの全容

アリスのeコマースプラットフォームを例に、パフォーマンスの低いクエリの特定から、パフォーマンス改善の効果確認に至るまでの全プロセスについて解説します。

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

Count: 328  Time=4.52s  Rows=1.0  Rows_examined=890000
SELECT * FROM orders WHERE YEAR(create_time)=2025 AND status='PAID';

EXPLAIN SELECT * FROM orders
WHERE YEAR(create_time) = 2025 AND status = 'PAID';

type: ALL | key: NULL | rows: 890000 | Extra: Using where

インデックスが失敗した理由：create_time で YEAR() 関数が使用されました。解決策：

ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

type: range | key: idx_status_time | rows: 3200 | Extra: Using index condition

スキャンされた行数は 890,000 行から 3,200 行に減少し、クエリ実行時間は 4.5 秒から 0.08 秒に短縮されました。最後に、バッファプールを調整します：

SET GLOBAL innodb_buffer_pool_size = 8589934592;

❓ よくある質問

📖 まとめ

• スロークエリログは、パフォーマンス最適化の出発点となります。mysqldumpslow またはシステムビューを使用して、ボトルネックを迅速に特定してください。
• EXPLAIN コマンドの「type」フィールドは、品質の高い順に「system」から「ALL」まで並んでいます。目標は、少なくとも「ref」レベルを達成することです。
• インデックスの最適化 ポイント：関数や暗黙の型変換によって生じるインデックスの不一致を回避すること。カバリングインデックスや複合インデックスの最左プレフィックスを有効に活用すること。
• クエリの書き換え：SELECT * を特定の列に置き換え、サブクエリを JOIN に置き換え、深いページネーションにはカーソルまたはレイジー結合を使用する
• 設定の調整：innodb_buffer_pool_size が最も重要なパラメータであり、バッファプールのヒット率は 99% 以上に維持する必要があります。
• パフォーマンス・スキーマは、スローログには記録されない、実行頻度の高い短いクエリや未使用のインデックスを特定できる、きめ細かな監視機能を提供します

📝 練習問題

1. 基本問題（難易度：⭐）：スロークエリログを有効にし、しきい値を0.5秒に設定して、mysqldumpslow を使用して、実行回数が最も多いスロークエリ上位5件を特定してください。

2. 上級演習（難易度 ⭐⭐）：type が ALL であるクエリについて、EXPLAIN を使用して分析し、適切なインデックスを追加して、type が ref または range レベルにアップグレードされることを確認してください。

3. 課題問題（難易度：⭐⭐⭐）：100万行の注文テーブルに対して、クエリ SELECT user_id, status, total_amount FROM orders WHERE user_id = ? AND status = ? を最適化するためにカバリングインデックスを用いたインデックス構成を設計し、最適化前後の出力 EXPLAIN における rows および Extra フィールドの違いを比較してください。

4. 実践演習（難易度：⭐⭐⭐）：JOIN を使用したサブクエリを含む処理の遅い SQL クエリを書き直し、インデックスを活用してクエリ実行時間を数秒から 100 ミリ秒未満に短縮するように最適化してください。最適化の全プロセスを文書化してください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて