MySQLパフォーマンスチューニングの実践的アプローチ

根本原因の特定から始める最適化戦略

クエリが遅いからといって、必ずしもSQL文に問題があるとは限りません。まずはボトルネックを正確に特定し、適切な対策を講じることが重要です。以下はMySQLチューニングの全体像を示すフローです。

遅延の原因は多岐にわたります：キャッシュの無効化、高負荷によるサーバー不安定、不適切なSQL構文、ミスコンフィグレーション、ハードウェア制約などです。

サーバーステータスの監視

接続数が少なく、明確にクエリが遅い場合は、このステップをスキップして直接SQLの最適化に進んでも構いません。

SHOW STATUS;

出力項目が多いので全件は省略しますが、特に注目すべきはQueries（総クエリ数）、Threads_connected（接続スレッド数）、Threads_running（実行中スレッド数）です。

以下のシェルスクリプトで1秒ごとに状態を記録できます：

#!/bin/bash
while true; do
  mysqladmin -uroot -p"your_password" extended-status | \
  awk '/Queries/{q=$4} /Threads_connected/{c=$4} /Threads_running/{r=$4} END{print q, c, r}' >> monitor.log
  sleep 1
done

記録したデータを加工して、1秒あたりのクエリ数を算出：

awk 'NR==1{prev=$1} {diff=$1-prev; prev=$1; print diff, $2, $3}' monitor.log

得られたデータを可視化し、周期的な負荷増加が見られる場合は、キャッシュの有効期限をランダム化（例：3〜9秒の範囲でランダム値）することでピーク負荷を分散させることができます。

最適化対象SQLの特定

方法1: 実行中のスレッドを確認

SHOW PROCESSLIST;

出力例：

+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  9 | root | localhost | test | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+----------+------------------+

State列の値が以下の場合は要注意：

  • Converting HEAP to MyISAM — メモリ不足でディスク使用
  • Create tmp table — 一時テーブル作成
  • Copying to tmp table on disk — ディスクへの一時テーブルコピー
  • Locked — ロック待ち
  • Sorting result — ソート処理

方法2: スロークエリログの活用

my.cnfに以下を追加：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow-queries.log
long_query_time = 2
log_queries_not_using_indexes = 1

設定後、MySQLを再起動し、以下のコマンドで設定を確認：

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

ログ分析にはmysqldumpslowツールを使用：

# 上位10件の頻出クエリ
mysqldumpslow -s c -t 10 /var/lib/mysql/slow-queries.log

# 実行時間の長い上位5件（LEFT JOIN含む）
mysqldumpslow -s t -t 5 -g "left join" /var/lib/mysql/slow-queries.log

SQL実行計画の分析

EXPLAINによる解析

EXPLAIN SELECT product_name, price FROM products WHERE category_id = 3;

重要なカラムの解説：

  • type: const > eq_ref > ref > range > index > ALL（性能順）
  • key: 実際に使用されたインデックス
  • rows: スキャンされる推定行数（少ないほど良い）
  • Extra: Using filesortやUsing temporaryは要改善

PROFILINGによる詳細分析

SET profiling = 1;

-- 実行したいクエリ
SELECT name, email FROM users WHERE status = 'active';

SHOW PROFILES;

SHOW PROFILE FOR QUERY 1;

各ステップの実行時間を詳細に把握できます。

具体的な最適化手法

クエリ改善

  • SELECT * を避けて必要なカラムのみ指定
  • 小規模テーブルを駆動表にする（IN/EXISTSの使い分け）
  • サブクエリよりJOINを優先
  • 冗長カラムを追加してJOINを減らす

インデックス設計

  • WHERE句、JOIN条件、ORDER BY/GROUP BYで使われるカラムに作成
  • 更新頻度が高いカラム、NULLが多いカラム、選択性の低いカラムは避ける
  • 複合インデックスは左端カラムから使用（「最左一致」原則）
  • 文字列検索では前方一致（'abc%'）のみ有効、後方一致（'%abc'）は無効

テーブル設計

  • 最小限のデータ型を使用（TINYINT, SMALLINTなど）
  • NOT NULL制約を積極的に適用
  • TEXT型は分割を検討
  • TIMESTAMPをDATETIMEより優先
  • 単一テーブルのカラム数は20以内が目安

テーブル分割戦略

  • 垂直分割：アクセス頻度の高いカラムと低いカラムを別テーブルに分離
  • 水平分割：ユーザーIDの剰余で複数テーブルに分散
  • 読み書き分離：マスター（書き込み専用）＋スレーブ（読み込み専用）構成

サーバーパラメータ調整

メモリ関連

  • sort_buffer_size：ソート用バッファ
  • join_buffer_size：JOIN用バッファ
  • read_buffer_size：フルスキャン用バッファ

ストレージ関連

  • innodb_log_file_size：トランザクションログサイズ
  • innodb_flush_log_at_trx_commit：コミット時のログフラッシュポリシー（1=安全、2=高速）

その他

  • max_connections：最大接続数
  • tmp_table_size：メモリ内一時テーブルの最大サイズ
  • expire_logs_days：バイナリログの保持日数

ハードウェア最適化（参考）

  • SSD/NVMeストレージの採用
  • RAID-10構成の利用
  • BIOS設定でCPUパフォーマンスモードを有効化
  • メモリ周波数を最大性能モードに設定

タグ: MySQL パフォーマンスチューニング sql最適化

6月21日 17:09 投稿

ホットタグ

©2026 異端開発室