DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

MySQLパフォーマンス最適化：主要戦略とベストプラクティス

この包括的なガイドでMySQLデータベースの潜在能力を最大限に引き出しましょう。インテリジェントなインデックス戦略、`EXPLAIN`を使用した高度なクエリチューニング、`innodb_buffer_pool_size`などの重要なサーバー設定（`my.cnf`）を網羅。スキーマ設計、ハードウェア考慮事項、スロークエリログによるプロアクティブな監視のベストプラクティスを学びます。この記事では、高速でスケーラブルかつ応答性の高いMySQL環境を構築・維持するための実践的な洞察と具体例を提供します。

MySQLパフォーマンス最適化：主要戦略とベストプラクティス

MySQLのパフォーマンス最適化は、チェックリストとして扱うのをやめ、ワークロードレビューとして捉えることで最も効果を発揮します。データベースはアプリケーションが要求することを正確に実行しています。修正が必要なのはインデックスである場合もあれば、より良いクエリである場合もあります。また、接続数の削減、異なるスキーマの選択、または正午にプライマリで実行すべきでないレポートが原因であることもあります。

最高のMySQLパフォーマンス作業は、まず不必要な作業を減らすことです。ハードウェアと設定は重要ですが、クリーンなワークロードをサポートするものであり、リクエストのたびにデータベースの半分を読み取るクエリを補うものではありません。

1. 最適なインデックス戦略

インデックスはデータベースパフォーマンスの基本であり、特に読み取り負荷の高いワークロードで重要です。インデックスにより、MySQLはテーブル全体をスキャンせずに素早く行を特定でき、SELECT操作、WHERE句のフィルタリング、ORDER BYやGROUP BY句、JOIN操作を劇的に高速化します。

インデックスとは何か、なぜ重要なのか

インデックスは、データベース検索エンジンがデータ検索を高速化するために使用する特別なルックアップテーブルです。本の索引のように考えるとわかりやすいでしょう。トピックを探すためにすべてのページを読む代わりに、索引でトピックを見つけ、該当するページ番号に誘導されます。MySQLでは、インデックスは通常B-Tree構造であり、範囲クエリや完全一致検索に効率的です。

インデックスは読み取りを高速化しますが、インデックス自体も更新する必要があるため、書き込み操作（INSERT、UPDATE、DELETE）にはオーバーヘッドが追加されます。したがって、過剰なインデックス作成を避けるために慎重な検討が必要です。

インデックス作成のベストプラクティス

  • WHERE、JOIN、ORDER BY、GROUP BY句で使用されるカラムにインデックスを作成する：これらがインデックス作成の主な候補です。テーブル間の結合条件で使用されるカラムには、両方のテーブルでインデックスが作成されていることを確認してください。
  • 複合インデックスを優先する：クエリが複数のカラムで頻繁にフィルタリングやソートを行う場合、複合インデックス（(col1, col2, col3)）は複数の単一カラムインデックスよりも効率的です。複合インデックス内のカラムの順序は重要です。等価述語は通常範囲述語の前に配置し、インデックスは選択性の一般的な考え方ではなく、実際のクエリ形状に一致させる必要があります。
    -- last_nameとfirst_nameに複合インデックスを作成
CREATE INDEX idx_last_first_name ON users (last_name, first_name);
  • 過剰なインデックス作成を避ける：インデックスが多すぎると書き込み操作が遅くなり、ディスク容量を過剰に消費します。本当に恩恵を受けるカラムにのみインデックスを作成してください。
  • インデックスの選択性を考慮する：インデックスは、MySQLが調査しなければならない行数を大幅に減らす場合に最も効果的です。カーディナリティが高い（一意な値が多い）カラムはインデックス作成の良い候補です。
  • 定期的にインデックス使用状況をレビューする：SHOW INDEX FROM table_name;を使用して定義とカーディナリティの推定値を確認し、利用可能な場合はsys.schema_unused_indexesをチェックしてください。未使用インデックスのレポートは候補として扱いますが、証明として扱わないでください。サーバーが月次ジョブやまれな管理ワークフローをまだ観測していない可能性があります。

2. クエリ最適化の習得

完璧なインデックスがあっても、不適切に書かれたクエリはパフォーマンスを低下させる可能性があります。クエリ最適化とは、インデックスを効果的に活用し、リソース消費を最小限に抑える効率的なSQLを書くことです。

EXPLAINステートメント：最良の味方

EXPLAINステートメントは、MySQLがクエリをどのように実行するかを理解する上で非常に価値があります。実行計画が表示され、どのインデックスが使用されるか、テーブルがどのように結合されるか、潜在的なパフォーマンスのボトルネックが示されます。

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

主要なEXPLAIN出力の解釈：

  • type：テーブルの結合方法を示します。const、eq_ref、ref、rangeを目指してください。可能であればALL（フルテーブルスキャン）は避けてください。
  • rows：MySQLが調査しなければならない行数の推定値。低いほど良いです。
  • key：MySQLが実際に使用するインデックス。
  • Extra：重要な詳細を提供します：
    • Using filesort：MySQLがデータをソートするために追加のパスを実行する必要がある（遅くなる可能性があります）。
    • Using temporary：MySQLがクエリを処理するために一時テーブルを作成する必要がある（遅くなる可能性があります）。
    • Using index：'カバリングインデックス'が使用されました。つまり、クエリに必要なすべてのデータがインデックス内で直接見つかり、データ行へのアクセスが不要になりました。非常に効率的です。

効率的なWHERE句

  • ページネーションにはLIMITを使用する：結果のサブセットを取得する場合、特にページネーションでは、常にLIMIT句を指定してください。
  • LIKEで先頭のワイルドカードを避ける：LIKE '%keyword'はカラムのインデックス使用を妨げ、フルテーブルスキャンを強制します。LIKE 'keyword%'を優先してください。
  • WHERE句でインデックス付きカラムに関数を使用しない：WHERE YEAR(order_date) = 2023はorder_dateのインデックス使用を妨げます。代わりにWHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'を使用してください。
  • 明確な範囲述語を使用する：WHERE id >= 10 AND id <= 20とWHERE id BETWEEN 10 AND 20は、包括的な範囲では同等です。日付やタイムスタンプの場合、半開区間の方が安全なことがよくあります：
    WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-02-01'

JOINの最適化

  • インデックス付きカラムで結合する：JOIN条件で使用されるカラムが両方のテーブルでインデックスされていることを確認してください。
  • 適切なJOINタイプを選択する：INNER JOIN、LEFT JOIN、RIGHT JOINを理解し、要件に正確に一致するものを使用してください。
  • オプティマイザに任せてから検証する：MySQLは内部結合を並べ替えることができるため、SQLテキストの順序が実行順序とは限りません。EXPLAINを使用して計画を確認してください。悪い計画を測定し、その理由を理解した場合にのみ、オプティマイザヒントを使用してください。

一般的なクエリのベストプラクティス

  • SELECT *を避ける：必要なカラムを明示的にリストしてください。これにより、ネットワークトラフィック、メモリ使用量が削減され、カバリングインデックスが可能になります。
  • サブクエリが悪いと決めつけない：現代のMySQLは多くのサブクエリをうまく最適化できます。計画とタイミングを確認した後にのみ書き換えてください。パフォーマンスが良好で読みやすいサブクエリは、誰も保守したがらない巧妙な結合よりも優れています。
  • バッチ操作：複数行のINSERTやUPDATEでは、各行に個別のステートメントを使用する代わりに、単一のステートメントで複数の値を挿入/更新してください。これによりトランザクションのオーバーヘッドが削減されます。
    -- バッチINSERTの例
INSERT INTO products (name, price) VALUES
('Product A', 10.00),
('Product B', 20.00),
('Product C', 30.00);

3. パフォーマンスのためのデータベーススキーマ設計

適切に設計されたスキーマは、高性能データベースの基盤を形成します。スキーマ設計中に行われる決定は、クエリの効率性とデータ整合性に大きな影響を与えます。

  • 正規化 vs 非正規化：
    • 正規化（例：3NF）はデータの冗長性を減らし、データ整合性を向上させますが、通常はより多くのJOINが必要になります。
    • 非正規化は、制御された冗長性を導入してJOINを減らし、特定の読み取りクエリを高速化しますが、データの一貫性を複雑にする可能性があります。バランスの取れたアプローチ、多くの場合、レポートや特定の高読み取りシナリオのためにわずかに非正規化されたものが一般的です。
  • 適切なデータ型：必要な情報を格納できる最小のデータ型を選択してください。より小さい範囲で十分な場合にBIGINTの代わりにINTを使用したり、短い文字列にTEXTの代わりにVARCHAR(255)を使用したりすると、スペースが節約されパフォーマンスが向上します。
    • CHARは固定長、VARCHARは可変長です。固定長データ（例：常に同じ長さのUUID）にはCHARを、可変長データにはVARCHARを使用してください。
  • 常に主キーを使用する：すべてのInnoDBテーブルには主キーが必要です。多くのOLTPシステムでは、オートインクリメント整数がシンプルで効率的ですが、これだけが有効な選択肢ではありません。セカンダリインデックスを適度に小さく保ち、計画がない限りランダムな書き込みパターンを避ける安定したキーを選択してください。
  • 外部キーにインデックスを作成する：外部キー関係に関与するカラムにインデックスが作成されていることを確認してください。これにより、JOINとカスケード操作が高速化されます。

4. サーバー設定のチューニング（my.cnf/my.ini）

MySQLの動作は、設定ファイル（Linuxではmy.cnf、Windowsではmy.ini）に大きく影響されます。これらの設定をハードウェアとワークロードに合わせて最適化することが重要です。

重要なInnoDB設定

InnoDBストレージエンジンを使用するほとんどの現代的なMySQLデプロイメントでは、以下の設定が最も重要です：

  • innodb_buffer_pool_size：これは最も重要な設定であることが多いです。 InnoDBがテーブルデータとインデックスをキャッシュするメモリ領域です。専用データベースサーバーでの一般的な出発点はRAMの50〜75％ですが、測定後により高くなることもあります。オペレーティングシステム、接続メモリ、バックアップ、監視エージェント用の領域を残してください。
    [mysqld]
innodb_buffer_pool_size = 8G  # 16GB RAMサーバーの例
  • innodb_log_file_size：InnoDBのREDOログのサイズ。ログが大きいと、書き込み負荷の高いワークロードでチェックポイントのプレッシャーを軽減できますが、クラッシュリカバリ時間が長くなる可能性があります。適切な値は書き込みボリュームとリカバリの期待値によって異なります。古いチューニングガイドから固定サイズをコピーしないでください。
  • innodb_flush_log_at_trx_commit：トランザクションの永続性に関するACID準拠の厳格さを制御します。
    • 1（デフォルト）：完全なACID準拠。各トランザクションコミット時にログがディスクにフラッシュされます。最も安全ですが、最も遅いです。
    • 0：ログは約1秒に1回ログファイルに書き込まれます。最速ですが、クラッシュ時に最大1秒間のトランザクションが失われる可能性があります。
    • 2：ログは各コミット時にOSキャッシュに書き込まれ、1秒に1回ディスクにフラッシュされます。妥協案ですが、OSクラッシュ時にトランザクションが失われる可能性があります。
    • アプリケーションのデータ整合性要件とパフォーマンスニーズに基づいて選択してください。

その他の重要な設定

  • max_connections：同時クライアント接続の最大数。高く設定しすぎるとRAMを多く消費し、低く設定しすぎると「Too many connections」エラーが発生する可能性があります。アプリケーションの接続プールとピーク負荷に基づいて調整してください。
  • tmp_table_sizeとmax_heap_table_size：メモリ内一時テーブルの最大サイズを定義します。一時テーブルがこのサイズを超えると、MySQLはそれをディスクに書き込み、大幅な速度低下を引き起こします。EXPLAINで頻繁にUsing temporaryが表示される場合、特に大規模データセットでのGROUP BYやORDER BY操作で、これらの値を増やしてください。
  • sort_buffer_size：ソート操作（ORDER BY、GROUP BY）に使用されるバッファ。クエリが頻繁に大規模なソートを伴い、EXPLAINでUsing filesortが表示される場合、この値を増やすことを検討してください（接続ごと）。
  • join_buffer_size：インデックスなしでテーブルを結合する際のフルテーブルスキャンに使用されます。EXPLAINでこれが表示される場合、通常はインデックス不足を示しますが、バッファを大きくするとインデックスなしの結合に役立つことがあります。
  • query_cache_size：MySQL 5.7.20で非推奨、MySQL 8.0で削除されました。 クエリ結果をキャッシュすることは魅力的に見えますが、特にビジーなサーバーではロック競合が高くなり、パフォーマンスのボトルネックになることがよくあります。一般的には無効にし（query_cache_size = 0）、アプリケーションレベルのキャッシュやより高速なストレージエンジンに依存することをお勧めします。

ヒント： 設定変更後、MySQLサーバーを再起動して変更を有効にしてください。本番環境に適用する前に、必ずステージング環境で変更をテストしてください。

5. ハードウェアとオペレーティングシステムの考慮事項

最も最適化されたMySQLインスタンスでも、不十分なハードウェアや適切に設定されていないオペレーティングシステム設定によってボトルネックが発生する可能性があります。

  • RAM：innodb_buffer_pool_sizeにとって重要です。バッファプールに利用可能なRAMが多いほど、MySQLがディスクにアクセスする頻度が減ります。
  • CPU：マルチコアCPUは、特に同時クエリ実行や複雑な操作に有益です。
  • ディスクI/O：これはしばしば主要なボトルネックです。ビジーな本番MySQLでは、ランダムI/Oが重要であるため、SSDベースのストレージが標準的なベースラインです。自己管理サーバーの場合は、冗長性と書き込み動作を慎重に検討してください。クラウドデータベースの場合は、プロビジョニングされたIOPS、バースト制限、レイテンシ、バックアップウィンドウに注意してください。
  • ネットワークレイテンシ：リモートデータベースアクセスの場合、アプリケーションサーバーとデータベースサーバー間のネットワークレイテンシを最小限に抑えてください。
  • オペレーティングシステムのチューニング：OS設定がデータベースワークロードに最適化されていることを確認してください。Linuxの場合は、vm.swappiness（不要なスワップを防ぐため）、file-max（オープンファイル制限）、ulimit設定の調整を検討してください。

6. プロアクティブな監視と分析

最適化は継続的なプロセスです。継続的な監視は、パフォーマンスの傾向を特定し、ボトルネックを早期に発見し、チューニング作業の影響を検証するのに役立ちます。

  • スロークエリログ：指定された時間（long_query_time）より長くかかるクエリをログに記録するようにMySQLを設定します。これは問題のあるクエリを特定するための主要なツールです。
    [mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
  • スロークエリログの分析：pt-query-digest（Percona Toolkitから）などのツールは、大きなスロークエリログを解析し、集約レポートを提供し、最も頻繁で最も遅いクエリを強調表示します。
  • MySQLステータス変数（SHOW STATUS）：サーバーアクティビティ、メモリ使用量、接続などに関するリアルタイム情報を提供します。問題をライブで特定するのに役立ちます。
    SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
    • Innodb_buffer_pool_readsのInnodb_buffer_pool_read_requestsに対する比率が高い場合、バッファプールのヒット率が低いことを示し、innodb_buffer_pool_sizeが小さすぎる可能性があります。
  • 監視ツール：Percona Monitoring and Management（PMM）、PrometheusとGrafana、MySQL Enterprise Monitorなどの専用監視ソリューションを活用してください。これらは包括的なメトリクス、ダッシュボード、アラートを提供します。
  • 定期的な監査：アプリケーションの進化に伴い、データベーススキーマ、クエリパターン、インデックス使用状況が最適化されたままであることを確認するために、定期的にレビューしてください。

実践的な最適化ワークフロー

遅いMySQLシステムを引き継いだ場合、最初の1時間で10の設定を変更したい衝動を抑えてください。繰り返し可能なフローを使用してください。

スロークエリログとアプリケーショントレースから始めてください。最悪の単一実行時間だけでなく、合計時間で重要なクエリを見つけてください。200ミリ秒かかり、1時間に50,000回実行されるクエリは、夜間に1回20秒かかるレポートよりも悪影響を与える可能性があります。

次に、実際のパラメータ値を含む正確なクエリ形状でEXPLAINを使用します：

EXPLAIN
SELECT id, customer_id, total, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

このようなクエリの場合、(customer_id, status, created_at)のインデックスが役立つ可能性があります。画面が通常、すべての顧客にわたって最初にstatusでフィルタリングする場合、(status, created_at)の方が良いかもしれません。適切なインデックスは、カラム名ではなくアクセスパターンから生まれます。

クエリとインデックスのレビュー後、メモリを確認してください。アクティブなデータセットがバッファプールよりもはるかに大きい場合、MySQLはストレージからの読み取りがより頻繁になります。バッファプールがすでに大きく、サーバーがまだ遅い場合、問題はテーブルスキャン、局所性の低さ、一時テーブル、または書き込みプレッシャーである可能性があります。より多くのメモリは、ワークロードがそれを再利用できる場合にのみ役立ちます。

次に、同時実行性を確認してください。データベースは多くの小さなクエリを処理できますが、無制限の並列作業は処理できません。アプリが開く接続が多すぎると、MySQLは有用な作業を完了するよりもセッションの切り替えに多くの時間を費やす可能性があります。適切な最大値を持つ接続プールは、max_connectionsを上げるよりもパフォーマンスを向上させることがよくあります。

最後に、変更を検証してください。良い最適化はどこかに現れるはずです：調査行数の減少、クエリレイテンシの低下、ディスク読み取りプレッシャーの軽減、ロック待機時間の短縮、レプリカラグの減少、タイムアウトの減少など。メトリクスが動かない場合、変更がボトルネックに対処していないか、測定が曖すぎたかのいずれかです。

MySQLを遅くするよくある間違い

よくある間違いは、すべての外部キーとフィルターカラムに個別にインデックスを作成し、なぜ書き込みが遅いのか疑問に思うことです。外部キーカラムにはしばしばインデックスが必要であり、フィルターカラムもインデックスの恩恵を受けることが多いですが、単一カラムインデックスの山積みは、適切に設計された1つの複合インデックスの代わりにはなりません。

もう一つの間違いは、大きなオフセットでページネーションを使用することです：

SELECT *
FROM events
ORDER BY created_at DESC
LIMIT 50 OFFSET 500000;

MySQLは依然として多数の行を通過する必要があります。深いページには、キーセットページネーションが通常より優れています：

SELECT *
FROM events
WHERE created_at < '2025-05-01 12:00:00'
ORDER BY created_at DESC
LIMIT 50;

長時間トランザクションも、静かな痛みの原因です。ユーザー入力を待ったり、外部APIを呼び出したり、ロックを保持しながら大きなバッチを処理したりするトランザクションは、無関係な作業をブロックする可能性があります。トランザクションは短く保ってください。データベース作業を行い、コミットし、その後で遅い外部作業を行ってください。

グローバルバッファの変更も逆効果になる可能性があります。sort_buffer_sizeやjoin_buffer_sizeなどの設定は接続ごとです。1つのレポートが遅いからといってグローバルに上げると、多くのセッションでメモリ使用量が増加する可能性があります。まずクエリを修正してください。必要に応じて、特別なジョブにはセッションレベルの変更を使用してください。

「良い」状態とは

健全なMySQL環境とは、すべてのクエリが即座に高速であることではありません。チームが高コストのクエリを説明でき、負荷の高いジョブを予測でき、ユーザーが報告する前にボトルネックを確認できることです。スロークエリログが有効になっています。ダッシュボードには、クエリレイテンシ、調査行数、バッファプール読み取り、ロック待機、ディスクレイテンシ、接続数、レプリケーションラグが表示されます。スキーマ変更は実際のデータでテストされます。インデックスには所有者と理由があります。

これは巨大なチューニングチェックリストほど魅力的ではありませんが、アプリケーションが変化してもMySQLを高速に保つ方法です。ワークロードを測定し、不必要な作業を減らし、一度に1つずつ変更し、証拠を保持してください。