高トラフィックサイト向けMySQLパフォーマンスチューニング

あなたのウェブサイトが成長している一方で、トラフィックの増加に伴いページが遅くなり、ユーザーから不満の声が上がっています。多くの場合、データベースがボトルネックです。MySQLは強力ですが、高い同時実行性と大規模なデータセットを処理するにはチューニングが必要です。このガイドでは、インデックス、クエリ最適化、設定、キャッシュ、モニタリングを網羅し、高トラフィックサイト向けにMySQLを最適化する実践的な手順を提供します。小規模なアプリでも大規模なプラットフォームでも、これらのテクニックはMySQLサーバーからより多くのパフォーマンスを引き出すのに役立ちます。

1. インデックス：高速クエリの基盤

適切なインデックスがないと、MySQLはクエリごとにテーブル全体をスキャンし、スケール時に壊滅的な影響を及ぼします。まずスロークエリを分析し、WHERE、JOIN、ORDER BY句で使用されるカラムにインデックスを追加しましょう。

EXPLAIN文を使用して、MySQLがクエリをどのように実行するかを確認します。type: ALL（フルテーブルスキャン）を探し、ref、eq_ref、またはrangeを目指します。また、余分な処理を示すUsing filesortやUsing temporaryにも注意してください。

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped' ORDER BY created_at DESC;

このクエリが遅い場合は、(customer_id, status, created_at)の複合インデックスを検討してください。カラムの順序が重要です：等価条件を先に、次に範囲またはソートカラムを配置します。

過剰なインデックスは避けましょう。各インデックスは書き込みオーバーヘッドを追加します。performance_schemaやsys.schema_unused_indexesを使用して、未使用のインデックスを定期的に確認してください。

2. クエリ最適化テクニック

インデックスがあっても、不適切に書かれたクエリはパフォーマンスを殺します。以下が重要な実践です：

• 必要なカラムのみを選択：SELECT *を避け、使用するカラムのみを取得します。これによりI/Oとメモリが削減されます。
• ページネーションにLIMITを使用：全行を取得する代わりに、LIMITとOFFSETでページ分割します。大きなオフセットには、キーセットページネーション（例：WHERE id > last_id LIMIT 20）を使用します。
• インデックスカラムに関数を使用しない：WHERE YEAR(created_at) = 2025はインデックスの使用を妨げます。WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'と書き換えましょう。
• JOINを賢く使用：結合カラムがインデックスされ、同じデータ型であることを確認します。1つのクエリで過剰なテーブルを結合しないようにしましょう。
• サブクエリにはINよりEXISTSを優先：存在チェックでは、EXISTSの方がパフォーマンスが良いことが多いです。

スロークエリログを有効にして問題のあるクエリを特定します。long_query_time = 1（またはそれ以下）を設定し、pt-query-digestやNginx Log Analyzer（ウェブサーバーログも管理している場合）などのツールでログを分析します。

3. MySQLサーバー設定のチューニング

MySQLのデフォルト設定は保守的です。高トラフィックサイトでは、my.cnf（Windowsではmy.ini）の主要なパラメータを調整します。本番環境に適用する前に、必ずステージング環境で変更をテストしてください。

変更後、MySQLを再起動し、パフォーマンスを監視します。SHOW STATUSを使用して、Innodb_buffer_pool_read_requestsとInnodb_buffer_pool_readsなどのメトリクスを確認します（キャッシュヒット率は高いはずです）。

4. データベース負荷を軽減するキャッシュ戦略

キャッシュは高トラフィックサイトの最良の味方です。複数のレイヤーを実装しましょう：

• アプリケーションレベルのキャッシュ：RedisやMemcachedを使用してクエリ結果や計算データを保存します。例えば、ホームページの商品リストを5分間キャッシュします。
• MySQLクエリキャッシュ：MySQL 8.0で非推奨；避けてください。
• InnoDBバッファプール：前述の通り、これが重要です。ワーキングセットを保持するのに十分な大きさを確保してください。
• フルページキャッシュ：CDNやリバースプロキシ（Nginxなど）を使用して静的HTMLを配信し、PHPとMySQLを完全にバイパスします。

キャッシュする際は、常に有効期限と、データ変更時に無効化する戦略（ライトスルーや時間ベースなど）を設定してください。

5. コネクション処理とプーリング

リクエストごとに新しいMySQLコネクションを開くのは高コストです。永続接続やコネクションプールを使用しましょう。PHPでは、永続接続を有効にしたmysqliやPDOを使用します。JavaやPythonなどのアプリケーションサーバーでは、プール（HikariCP、SQLAlchemyプールなど）を使用します。

Threads_connectedとThreads_runningを監視します。Threads_runningがCPUコア数を常に超える場合は、クエリの最適化や水平スケーリングが必要かもしれません。

6. モニタリングと継続的改善

パフォーマンスチューニングは継続的なプロセスです。以下を監視しましょう：

• スロークエリログ：定期的に分析します。
• Performance Schema：待機、I/O、ロックに関する詳細なメトリクスを提供します。
• システムメトリクス：データベースサーバーのCPU、メモリ、ディスクI/O。
• レプリケーション遅延：レプリカを使用している場合、遅延が最小であることを確認します。

mysqldumpslow、pt-query-digest、MySQL Workbenchなどのツールを使用してパフォーマンスを可視化します。異常に対するアラートを自動化しましょう。

FAQ

MySQLでスロークエリを見つけるにはどうすればよいですか？

slow_query_log = ONとlong_query_timeを低い値（例：1秒）に設定してスロークエリログを有効にします。ログファイルにはその時間を超えたクエリが含まれます。pt-query-digestやmysqldumpslowで分析しましょう。

高トラフィックサイトに理想的なinnodb_buffer_pool_sizeは？

専用データベースサーバーでは、利用可能なRAMの70〜80%に設定します。これにより、ほとんどのデータとインデックスがメモリにキャッシュされ、ディスクI/Oが最小化されます。バッファプールのヒット率を監視し、読み取りが多いワークロードでは99%以上であるべきです。

MySQLクエリキャッシュを使用すべきですか？

いいえ。クエリキャッシュはMySQL 8.0で非推奨となり、ミューテックスの競合によりパフォーマンス問題を引き起こす可能性があります。代わりにアプリケーションレベルのキャッシュ（Redisなど）やInnoDBバッファプールを使用しましょう。

サーバーログを分析する準備はできましたか？無料のNginx Log Analyzerを試して、トラフィックパターンの洞察を得てスタックを最適化しましょう。