MySQLクエリ最適化実践ガイド：EXPLAIN分析からインデックス設計・スロークエリチューニングまで

EXPLAIN SELECT u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
  AND o.created_at >= '2026-01-01';

-- const: 主キーで単一行検索
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const, rows: 1

-- ref: 非ユニークインデックス検索
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- type: ref（user_idにインデックスがある場合）

-- ALL: フルスキャン - 必ず改善が必要
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- type: ALL（関数適用によりインデックス使用不可）

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.id
HAVING order_count > 5
ORDER BY order_count DESC
LIMIT 10;

-- 統計情報の手動更新
ANALYZE TABLE users;
ANALYZE TABLE orders;

-- B-Treeインデックスの作成
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- このインデックスが効果的なクエリ例
SELECT * FROM orders WHERE user_id = 100 AND created_at >= '2026-01-01';
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
SELECT * FROM orders WHERE user_id IN (100, 200, 300);

-- MEMORYエンジンテーブルでHashインデックスを使用
CREATE TABLE session_cache (
    session_id VARCHAR(64) NOT NULL,
    user_id INT NOT NULL,
    data JSON,
    INDEX USING HASH (session_id)
) ENGINE = MEMORY;

-- InnoDB Adaptive Hash Indexの状態確認
SHOW ENGINE INNODB STATUS\G
-- Adaptive Hash Indexセクションでhit rateを確認

-- Full-textインデックスの作成
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_content (title, body);

-- 自然言語モード検索
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('MySQL パフォーマンス 最適化' IN NATURAL LANGUAGE MODE);

-- ブーリアンモード検索（AND、OR、NOT等）
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL +パフォーマンス -PostgreSQL' IN BOOLEAN MODE);

-- Spatialインデックスの作成
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position POINT NOT NULL SRID 4326,
    SPATIAL INDEX (position)
);

-- 半径内検索
SELECT name, ST_Distance_Sphere(position, ST_SRID(POINT(127.0276, 37.4979), 4326)) AS distance
FROM locations
WHERE ST_Distance_Sphere(position, ST_SRID(POINT(127.0276, 37.4979), 4326)) < 1000;

-- 複合インデックス: (a, b, c)
CREATE INDEX idx_abc ON orders (status, user_id, created_at);

-- インデックス活用可能なクエリ
SELECT * FROM orders WHERE status = 'completed';                          -- (a) 使用
SELECT * FROM orders WHERE status = 'completed' AND user_id = 100;        -- (a, b) 使用
SELECT * FROM orders WHERE status = 'completed' AND user_id = 100
  AND created_at >= '2026-01-01';                                         -- (a, b, c) すべて使用

-- インデックス活用不可能なクエリ
SELECT * FROM orders WHERE user_id = 100;                                 -- (b)のみ - インデックス未使用
SELECT * FROM orders WHERE user_id = 100 AND created_at >= '2026-01-01';  -- (b, c) - インデックス未使用
SELECT * FROM orders WHERE created_at >= '2026-01-01';                    -- (c)のみ - インデックス未使用

-- カバリングインデックスの設計
CREATE INDEX idx_covering ON orders (user_id, status, total_amount);

-- このクエリはインデックスのみで結果返却可能（Using index）
EXPLAIN SELECT user_id, status, total_amount
FROM orders
WHERE user_id = 100 AND status = 'completed';

-- このクエリはカバリングインデックス非適用（SELECT *によりテーブルデータアクセス必要）
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND status = 'completed';

-- 悪い設計: 低カーディナリティカラムのみでインデックス
CREATE INDEX idx_bad ON orders (status);  -- statusは5～10種類の値のみ

-- 良い設計: クエリパターンに合わせた複合インデックス
CREATE INDEX idx_good ON orders (user_id, status, created_at);

-- ソートまで考慮した設計
-- WHERE user_id = ? ORDER BY created_at DESC パターンが頻繁な場合
CREATE INDEX idx_sort ON orders (user_id, created_at DESC);

-- 悪い例: 相関サブクエリ（各行ごとにサブクエリ実行）
SELECT u.name, u.email,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
WHERE u.status = 'active';

-- 良い例: LEFT JOIN + GROUP BY
SELECT u.name, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.name, u.email;

-- または: 派生テーブル（Derived Table）の使用
SELECT u.name, u.email, COALESCE(oc.cnt, 0) AS order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders
    GROUP BY user_id
) oc ON u.id = oc.user_id
WHERE u.status = 'active';

-- 悪い例: OR条件によるインデックス活用困難
SELECT * FROM products
WHERE category_id = 10 OR brand_id = 20;

-- 良い例: UNION ALLで分離（各条件に対してインデックス活用可能）
SELECT * FROM products WHERE category_id = 10
UNION ALL
SELECT * FROM products WHERE brand_id = 20 AND category_id != 10;

-- 悪い例: インデックスカラムに関数適用
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 良い例: 範囲条件に書き換え
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- MySQL 8.0+: 関数ベースインデックス（Expression Index）
CREATE INDEX idx_email_lower ON users ((LOWER(email)));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 悪い例: phone_numberがVARCHARなのに数値で比較
SELECT * FROM users WHERE phone_number = 01012345678;
-- MySQLがphone_numberを数値に変換 -> インデックス未使用

-- 良い例: 型の一致
SELECT * FROM users WHERE phone_number = '01012345678';

-- USE INDEX: 特定インデックスの使用を推奨（オプティマイザが無視する可能性あり）
SELECT * FROM orders USE INDEX (idx_user_created)
WHERE user_id = 100 AND created_at >= '2026-01-01';

-- FORCE INDEX: 特定インデックスを強制使用（フルスキャンの代わりに必ずインデックス使用）
SELECT * FROM orders FORCE INDEX (idx_user_created)
WHERE user_id = 100 AND created_at >= '2026-01-01';

-- IGNORE INDEX: 特定インデックスを無視
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = 'completed' AND user_id = 100;

-- JOIN順序の固定
SELECT /*+ JOIN_ORDER(u, o) */
  u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- 特定テーブルのインデックス指定
SELECT /*+ INDEX(o idx_user_created) */
  o.*
FROM orders o
WHERE o.user_id = 100;

-- Hash Joinの強制
SELECT /*+ HASH_JOIN(u, o) */
  u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 並列クエリ実行（MySQL 8.0.14+）
SELECT /*+ SET_VAR(innodb_parallel_read_threads=4) */
  COUNT(*) FROM large_table;

-- 現在の設定確認
SHOW VARIABLES LIKE 'optimizer_switch';

-- セッションレベルで特定の最適化を無効化
SET SESSION optimizer_switch = 'index_merge_intersection=off';
SET SESSION optimizer_switch = 'derived_merge=off';
SET SESSION optimizer_switch = 'batched_key_access=on';

-- MySQL 8.0.31+: ヒストグラムベースの最適化
ANALYZE TABLE orders UPDATE HISTOGRAM ON user_id, status WITH 100 BUCKETS;

-- スロークエリログの有効化
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1秒以上かかるクエリを記録
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- インデックス未使用クエリも記録
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

-- my.cnfでの恒久設定
-- [mysqld]
-- slow_query_log = 1
-- long_query_time = 1
-- log_queries_not_using_indexes = 1
-- slow_query_log_file = /var/log/mysql/slow-query.log
-- min_examined_row_limit = 1000

# 最も遅いクエリTop 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# 最も頻繁なスロークエリTop 10
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

# 特定パターンのフィルタリング
mysqldumpslow -s t -t 10 -g "orders" /var/log/mysql/slow-query.log

# スローログの分析
pt-query-digest /var/log/mysql/slow-query.log

# 特定時間範囲の分析
pt-query-digest --since="2026-03-10 00:00:00" --until="2026-03-11 00:00:00" \
  /var/log/mysql/slow-query.log

# 結果をファイルに保存
pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow-query-report.txt

-- 現在のバッファプール状態の確認
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- バッファプールヒット率の計算
SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_rate;
-- 99%以上が望ましい

# my.cnf設定例
[mysqld]
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8    # バッファプールサイズ / インスタンス = 各4GB
innodb_buffer_pool_chunk_size = 1G  # オンラインリサイズ単位
innodb_log_file_size = 4G
innodb_log_buffer_size = 64M
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

-- シャットダウン時にバッファプールダンプ、起動時にロード
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
SET GLOBAL innodb_buffer_pool_load_at_startup = ON;

-- 手動ダンプ/ロード
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

-- ロード進捗状態の確認
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

-- MySQL 8.4: EXPLAIN改善の例
EXPLAIN FORMAT=TREE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Invisible Indexの活用（インデックス削除前のテスト）
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- パフォーマンステスト後、問題なければ削除
-- DROP INDEX idx_status ON orders;
-- 問題が発生した場合は再度有効化
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

-- 問題状況: VARCHARカラムに対する数値比較
-- account_noカラムがVARCHAR(20)だがアプリケーションから数値が渡される
EXPLAIN SELECT * FROM accounts WHERE account_no = 123456;
-- type: ALL（全テーブルスキャン！）

-- 原因: MySQLがaccount_noカラムを数値に変換するためインデックス使用不可
-- 解決: 型を一致させる
EXPLAIN SELECT * FROM accounts WHERE account_no = '123456';
-- type: ref（インデックス使用）

-- 問題状況: インデックスがあるがオプティマイザがフルスキャンを選択
-- statusカラムにインデックスがあるが'active'が全体の90%を占める
EXPLAIN SELECT * FROM users WHERE status = 'active';
-- type: ALL（オプティマイザがフルスキャンの方が効率的と判断）

-- 原因: カーディナリティが低くインデックス効率が悪い
-- 解決1: 他の条件と複合インデックスを構成
CREATE INDEX idx_status_created ON users (status, created_at);

-- 解決2: 特定の値に対してのみインデックスが有用な場合
-- MySQLはPartial Indexを直接サポートしないため、クエリパターンの変更を検討

-- 問題状況: インデックスを(created_at, user_id)で作成
-- 頻繁なクエリ: WHERE user_id = ? AND created_at >= ?
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND created_at >= '2026-01-01';
-- インデックスを非効率的に使用（created_atで先にrange scan）

-- 解決: 等値条件カラムを先に配置
DROP INDEX idx_created_user ON orders;
CREATE INDEX idx_user_created ON orders (user_id, created_at);
-- type: range（効率的な範囲スキャン）

-- 問題状況: 500万件DELETE後のクエリパフォーマンス低下
-- 原因: インデックスページに空きスペースが多い（断片化）

-- 診断
SELECT
  TABLE_NAME,
  INDEX_LENGTH,
  DATA_LENGTH,
  DATA_FREE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'orders';

-- 解決: インデックスの再構築
ALTER TABLE orders ENGINE=InnoDB;  -- テーブル + インデックスの再構築
-- または
OPTIMIZE TABLE orders;

-- 未使用インデックスの検索
SELECT
  s.TABLE_SCHEMA,
  s.TABLE_NAME,
  s.INDEX_NAME,
  s.COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
  ON s.TABLE_SCHEMA = p.OBJECT_SCHEMA
  AND s.TABLE_NAME = p.OBJECT_NAME
  AND s.INDEX_NAME = p.INDEX_NAME
WHERE p.COUNT_STAR = 0
  AND s.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND s.INDEX_NAME != 'PRIMARY'
ORDER BY s.TABLE_SCHEMA, s.TABLE_NAME;

-- 重複インデックスの検索（sysスキーマ活用）
SELECT * FROM sys.schema_redundant_indexes;

-- インデックス追加時のロックモード確認
ALTER TABLE orders ADD INDEX idx_new (col1, col2), ALGORITHM=INPLACE, LOCK=NONE;

-- pt-online-schema-changeの使用（大容量テーブルに推奨）
-- トリガーで元テーブルを同期しながらスキーマ変更を実行

pt-online-schema-change \
  --alter "ADD INDEX idx_new (col1, col2)" \
  --execute \
  D=mydb,t=orders

-- ヒストグラムの作成
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON total_amount WITH 254 BUCKETS;

-- ヒストグラムの確認
SELECT
  SCHEMA_NAME,
  TABLE_NAME,
  COLUMN_NAME,
  JSON_EXTRACT(HISTOGRAM, '$.histogram-type') AS histogram_type,
  JSON_EXTRACT(HISTOGRAM, '$.number-of-buckets-specified') AS buckets
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;

-- ヒストグラムの削除
ANALYZE TABLE orders DROP HISTOGRAM ON status;