...
ウェブホスティングのロゴ

ホスティングでMySQLのスロークエリログを分析する：最適化のヒント

MySQLのスロークエリログ では、どのクエリが時間を食っているのか、どれくらいの頻度で発生するのか、なぜ遅くなるのかをホスティングで教えてくれます。ログを有効化し、分析し、クエリを再構築することで、ページの読み込みを高速化し、サーバーリソースをより効率的に使用するための具体的な手順を紹介します。.

中心点

  • 活性化 閾値を適切に設定する
  • 評価 pt-query-digestとmysqldumpslowを使用。
  • 指標 を解釈する：Query_time、Lock_time、Rows_examined。
  • チューニング インデックス、EXPLAIN、リライトを通して
  • オートメーション ホスティングとモニタリング

遅いクエリログはホスティングで何をするのですか？

ホスティング は共有リソースを意味するため、クエリごとにミリ秒単位でカウントされます。私はログを使用して、定義された制限時間よりも長く実行されたクエリを見つけ、各クエリのQuery_time、Lock_time、Rows_sent、Rows_examinedなどの主要な数値を確認します。これらの数値から、インデックスがないのか、不利な結合があるのか、テーブルのフルスキャンが背後にあるのかがわかります。特に複数のサイトを持つサーバーでは、たった一つの悪いクエリがCPUとI/Oに大きな負担をかけることがあります。ロード時間とサーバー負荷に最も影響するのはこの部分なので、合計時間が最も長いクエリを優先します。.

活性化しきい値と賢明なしきい値

スタートホスティングのアクセスに応じて、my.cnf経由でランタイムにもパーマネントにもできます。迅速なテストのために、私は一時的にログをオンにして、long_query_timeをトラフィックとハードウェアに合った値に設定する。使用頻度の高いサイトでは0.1秒にすることが多いが、I/Oが不必要に大きくならないようにログのサイズに注意する。ファイルへの直接アクセスが制限されている場合は、MySQLシェルのパフォーマンス・スキーマ・オプションを使ってレポートを作成します。微調整が終わったら、最終設定をコンフィギュレーションファイルに書き込み、サービスを再起動する。.

SET GLOBAL slow_query_log = 'ON'；
SET GLOBAL long_query_time = 1；
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log'；
SET GLOBAL log_queries_not_using_indexes = 'ON'；

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

正社員 log_throttle_queries_not_using_indexesやlog_slow_admin_statementsのようなオプションを設定し、ログが爆発することなく、有用であり続けるようにしています。例えば、なぜlong_query_timeが0.5秒なのか、0.1秒なのか。こうすることで、後で改良することができる。共有環境では、プロバイダーとアクティベーションについて話し合うか、プロバイダーのパネルを使うことが多い。モニタリングやメトリクスで効果をきれいに比較できるように、それぞれのアクティベーションを開始日とリンクさせています。.

[mysqld］
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1

スローログを効果的に評価する

生データ そのため、pt-query-digestでまとめ、意味のある期間の合計時間でソートしています。こうすることで、パターンや、非常に変化しやすいクエリ、パラメータによってのみ変化するクエリファミリーを認識することができます。平均値だけでなく、分布もチェックします。簡単な概要としては、mysqldumpslowを使用すると、最も遅い10グループを確認することができます。タイムウィンドウ、データベースフィルタ、およびテキスト分析へのエクスポートを使用して、より詳細な情報を得ます。.

pt-query-digest /var/log/mysql/slow-query.log
pt-query-digest --since '24h' /var/log/mysql/slow-query.log
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

役に立つ は、アプリケーションやPHPの関数が使用されたときに、他のログを見ることでもある。これを行うために、私は既存のログワークフローを使用し、結果をバンドルする。このガイドは、しばしば私に導入部を提供してくれる： ログの分析. .トラフィックのピークとクエリのピークを比較できるように、タイムスタンプを同期させています。これにより、キャッシュミス、cronジョブ、インポートジョブが同時にデータベースを使用しているかどうかを確認することができます。.

メトリクスの正しい解釈

クエリータイム は純粋なランタイムを示している。Lock_timeはロックによる待ち時間を示し、不必要に長いトランザクションや大きなバッチが原因であることが多い。Rows_examinedとRows_sentの比率は、クエリが多くの行を表示し、インデックスが欠落しているかどうかを示します。ログに „No index use “エントリが多く含まれている場合、私はスロットリングを設定し、影響を受けるテーブルを詳しく調べます。対症療法ではなく、常に原因に取り組むことが重要であることに変わりはない：正しいカラムにインデックスがあれば、どんなハードウェアのアップグレードにも勝る。.

指標 私が見たもの 測定
クエリータイムが高い 1バージョンあたりの走行時間が長い EXPLAINのチェック、クエリの書き換え、インデックスの追加
ロック・ハイ ロックの待ち時間 トランザクションの短縮、バッチサイズの縮小、適切な分離
検査済み行 ≫ 送信済み行 スキャンしすぎ、返しすぎ インデックス・フィルター・カラム、サーガビリティの作成
インデックスなし フルテーブルスキャン インデックスを作成し、WHEREでの式を避ける

限界値 最初の1週間は、ノイズに紛れないように調整する。組織的な改善のための十分なヒット数が得られるまで、段階的にlong_query_timeを下げていく。各調整は日付と理由を添えて文書化する。これによって評価を集中させることができる。貴重なヒットは、後の重複作業を省く。.

実践：クエリー・チューニングのステップ・バイ・ステップ

説明する は、私がコードを変更する前のスタートです。私は、„type: ALL“、数字が大きい „rows“、„Using filesort “または „Using temporary “を探します。WHEREやJOINのカラムに対する関数は、インデックスの使用を妨げることが多い。その代わりに、サーガブルな条件を設定し、新しいプランをチェックする。各ステップでは、行削減を早期に、かつ意図的に実行しなければならない。.

EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026；

-- より良い：
CREATE INDEX idx_orders_created ON orders(created_at)；
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'；;

ジョインズ 結合順をチェックし、結合キーにインデックスをマッチさせることで最適化する。複合インデックスがWHERE + ORDER BYをカバーしているかどうかをチェックし、ファイルソートを回避する。プレビューのみが必要な場合はLIMITを設定する。変更率の低い同一のクエリを繰り返す場合は、アプリケーションレベルで結果キャッシュを保存する。インデックスとロックについてのより詳細な紹介はこちら： インデックスとロック.

CMSとショップのインデックス戦略

ワードプレス, WooCommerceやショップシステムは、典型的なパターンを作り出します：多くの読み取り、選択的な書き込み、多くの場合メタテーブルや商品テーブルを使用します。私は、ホームページ、カテゴリー、検索、チェックアウトといった最も一般的なルートを分析し、フィルター、ソート、結合カラムに特別なインデックスを配置します。インデックス（例えば（status、created_at、id））をカバーすることで、テーブルへの再アクセスの手間を省くことができる。接頭辞を検索するには、LIKE ‚%wort%‘の代わりに、適切なインデックスフォームまたはフルテキストを使用する。同じ負荷プロファイルでライブ実行の前後で各インデックスの変化を測定します。.

成長 カーディナリティとヒストグラムを使ってデータセットをチェックし、レアな値にインデックスを付けないようにしている。書き込み負荷とメモリ要件を抑制するため、インデックスの数を少なくしている。連結複合インデックスは、複数の個別インデックスを置き換える。MySQLのオートバキュームのようなタスクは、定期的に分析し、必要なときだけ再構築することで調整している。これにより、オプティマイザの信頼性が保たれる。.

サーバー設定、キャッシュ、メモリー

イノDB バッファプールサイズは、一律の値ではなく、アクティブなデータレコードとインデックスサイズに基づいて決めている。tmp_table_sizeとmax_heap_table_sizeを設定し、ディスクに残るテンポラリテーブルが少なくなるようにしている。書き込みのセキュリティとレイテンシのために、innodb_flush_log_at_trx_commitをアプリケーションに合わせて適切なバランスにしている。アプリケーションレベルでは、データベースへのリクエストが少なくなるように、頻繁に発生する結果をキャッシュし、HTTPキャッシュを使っている。.

ハードウェア やネットワークの影響も診断に含まれる：遅いストレージI/Oや過負荷のCPUは、クエリによってすぐに認識される。そのため、私はデータベース・メトリクスと並行してIO-waitを測定している。より多くのリザーブが必要な場合は、測定可能なターゲットで垂直または水平スケーリングを計画します。このガイドでは、ボトルネック、チューニング、リソースの概要をコンパクトに説明します： ハードウェアとキャッシュ. .こうすることで、やみくもに間違ったノブを回していないことを確認できる。.

ホスティングにおける並行処理とロック

ロック時間 長いトランザクションが多くの行に触れたり、クリーンアップ・ジョブがゴールデンタイムに実行されたりすると、ロックは増大する。私は書き込み操作を短縮し、大きな更新を小さなバッチに分割し、ロックの保持時間を短縮します。適切な分離レベルにより、データの一貫性を損なうことなく競合を減らします。セカンダリ・インデックスと適切なWHERE条件でホットスポットを緩和し、影響を受ける行を少なくします。ユーザーのアクションが優先されるように、トラフィックの少ない時間帯にバックグラウンド・ジョブをスケジューリングします。.

デッドロック 同じテーブル、順序の変更、同じ行など、繰り返し起こるパターンを使って分析します。コードとストアドプロシージャのアクセスシーケンスを標準化する。ジッターを使った再試行ロジックで一時的な衝突を解決する。可能であれば、最もコストのかかるオペレーションをジョブキューに分離する。これにより、ばらつきが著しく減少し、知覚されるパフォーマンスが向上する。.

自動化されたアラームとワークフロー

ルーティン beats行動主義：私は、トラフィックとリリースの頻度に応じて、毎日または毎週ログを分析する。小さなスクリプトで直近数分の新しいヒットをカウントし、しきい値が上がったらメールを送っています。また、定期的にpt-query-digestレポートを作成し、常にトップ10に目を光らせています。私はリリース日をより注意深く監視しています。これにより、ユーザーが気づく前にリグレッションに気づくことができます。.

#！/bin/bash
LOG_FILE="/var/log/mysql/slow-query.log"
THRESHOLD=100
RECENT_COUNT=$(awk -v cutoff="$(date -d '5 minutes ago' '+%Y-%m-%dT%H:%M')" '/^# Time:/ { if ($3 >= cutoff) count++ } END { print count+0 }' "$LOG_FILE")
if [ "$RECENT_COUNT" -gt "$THRESHOLD" ]; then
    echo "ALERT: $RECENT_COUNT slow queries" | mail -s "MySQL Alert" [email protected]
fi

透明性 私は明確な責任を課している：誰がピークに反応し、誰が指標を調整し、誰がリリースをテストするのか。結果を短い変更履歴にまとめる。こうすることで、チームメンバー全員が、なぜその変更が行われたのか、どのような効果があったのかを理解することができる。構造化されたプロセスは時間を節約し、誤報を防ぐ。.

エラー画像と迅速な修正

フル テーブルスキャンが不釣り合いに高い負荷を引き起こす。私はまず、フィルター・カラムに適切なインデックスがないか、あるいは式がインデックスをブロックしていないかをチェックする。トランザクションを短縮し、競合する処理を均等にすることで、高いロックタイムを排除する。log_throttle_queries_not_using_indexesと現実的なlong_query_timeを使用して、溢れるログを消します。私は、各修正を直ちに元の数値と比較して測定し、成功が目に見える形で残るようにする。.

ストレージ-クエリーのピーク時に、IOウェイトやディスクレイテンシーを増加させることで、ボトルネックを認識します。そして、変更不可能なフィールドの更新頻度を減らすなど、不必要な書き込み操作を減らす。テーブルが大きくなったら、アーカイブやパーティション戦略を計画し、ホットなデータがメモリに残るようにする。ピーク時の管理ステートメントについては、log_slow_admin_statementsをオンにして、サイレント・コスト・ドライバを特定します。小規模で的を絞った修正は、大規模な組織再編成よりも早く効果が出る。.

マネージド環境とクラウド環境における特別な機能

マネージドホスティング やクラウドサービスでは、ファイルアクセスが制限されることが多い。そのような場合、私はlog_outputをTABLEに設定し、データベースから直接スローログを評価する。MySQL 8.0では、SET PERSISTを使って、my.cnfに直接アクセスすることなく永続的に設定を行うこともできる。クラウドパラメータグループ（マネージドサービス用など）では、同じ変数を入力し、再起動のためのメンテナンスウィンドウをスケジュールする。.

-- 許可された場合：再起動せずに設定を持続する
SET PERSIST slow_query_log = ON；
SET PERSIST long_query_time = 0.5；
SET PERSIST log_output = 'TABLE'; -- ファイル・アクセスが制限されている場合のFILEに代わるもの

-- log_output=TABLEでの評価
SELECT start_time、user_host、query_time、lock_time、rows_sent、rows_examined、sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 50；;

ヒントトラフィックが多い場合、テーブル・ロギングはさらなるオーバーヘッドを発生させるため、log_output=FILEの方がパフォーマンスが高くなることがある。しかし、制限の多い環境では、TABLEが唯一の方法であることが多い。私は、より厳しい制限（min_examined_row_limitなど）を設定して、ボリュームをコントロールできるようにしている。.

ローテーション、保管、データ保護

ローテーション ディスクがログでいっぱいになるのを防ぎます。毎日またはサイズごとにローテーションし、古いファイルを圧縮し、明確な保持ポリシー（例えば14日間）を維持する。ローテーションの後、ログフラッシュを行い、MySQLが新しいファイルにきれいに書き込むようにしています。これにより、解析と運用が安定します。.

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log{。
  毎日
  ローテート14
  サイズ100M
  圧縮
  欠落
  空
  640を作成する mysqlの管理者
  ポスト回転
    test -x /usr/bin/mysqladmin || exit 0
    /usr/bin/mysqladmin flush-logs
  終了スクリプト
}

データ保護 は必須です。スローログにはパラメータ値が含まれることがあります。私はアクセス（ファイルパーミッション、グループ）を厳しく制限し、機密データがログに記録されていないかチェックします。必要であれば、アプリケーションでパラメータバインディングを行い、ログに個人設定されたプレーンテキストが表示されないようにします。チームで共有する場合は、生のログではなく、集計されたレポートを共有することを好みます。.

パフォーマンススキーマとシステムスキーマの使用

パフォーマンス・スキーム は、有効化されたスローログがなくてもメトリクスを提供します。ステートメントに関連するコンシューマーをアクティブにして、システムビューを分析します。利点: 似たようなクエリをグループ化して、トップダイジェストとレイテンシの分布をほぼリアルタイムで見ることができます。.

-- ステートメント履歴のコンシューマを有効化する（実行時に可能な限り）
UPDATE performance_schema.setup_consumers
set enabled = 'yes'
WHERE NAME IN ('events_statements_history', 'events_statements_history_long')；

-- 高価なクエリーグループの概要
SELECT schema_name, digest_text, count_star、
       ROUND(sum_timer_wait/1e12, 3) AS total_s、
       ROUND(avg_timer_wait/1e9, 3) AS avg_ms、
       ROUND(max_timer_wait/1e9, 3) AS pmax_ms
FROM sys.statement_analysis
ORDER BY sum_timer_wait DESC
LIMIT 10；;

コンビネーション 低速ログ（低速の異常値）とパフォーマンス・スキーム（幅、頻度）から、個々のケースと体系的なコスト要因の両方がわかる。私は両方のビューをトラフィックパターンと比較し、優先順位をつけたToDoを作成している。.

EXPLAIN ANALYZEとオプティマイザー・トレース

EXPLAIN ANALYZE (MySQL 8.0.18 現在) は、実測時間による推定を補足しています。行の推定値と実際の値を比較し、オプティマイザによる誤判定を発見します。矛盾したプランの場合は、オプティマイザのトレースを分析して、インデックスが選択されなかった理由を調べます。.

-- 測定値による計画
分析を説明する
SELECT o.id, o.created_at
FROM オーダー o
JOIN 顧客 c ON c.id = o.customer_id
WHERE c.country = 'DE' AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50；

-- オプティマイザの決定をトレースする
SET optimizer_trace="enabled=on"；
SELECT ...; -- 分析されるクエリ
SELECT TRACE FROM information_schema.OPTIMIZER_TRACEG
SET optimiser_trace="enabled=off"；;

結果推定値が大きくずれている場合は、統計情報を更新したり（ANALYZE TABLE）、ヒストグラムを追加したり、インデックスやクエリの形を変えたりして、選択性が早期に有効になるようにする。.

ほとんどの場合うまくいくパターンを書き換える

またはUNION ALL異なるカラムに対する複数のOR条件は、しばしばインデックスの使用を妨げます。私はそれらを2つの選択的なクエリに分離し、重複を除外できる場合は結果をマージします。.

-- その前に
SELECT * FROM t WHERE a = ?OR b = ?

-- より良い：
(より良い： （SELECT * FROM t WHERE a = ?）
UNION ALL
(SELECT * FROM t WHERE b = ? AND a  ?)；;

ページネーションOFFSET/LIMITはOFFSETが大きくなるにつれて高くなる。私はキーセットのページネーションに切り替え、適切なソートキー（理想的にはインデックス付きで単調）を使用する。.

-- 高い：
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 5000；

-- より良い（キーセット）：
SELECT id, title
FROM posts
WHERE created_at < :cursor
ORDER BY created_at DESC
LIMIT 50；;

総合指数順序のカウント。選択性とクエリーパターン（最初にWHEREフィルター、次にカラムのソート）に従ってインデックス内のカラムをソートする。目標は、ファイルソートとテーブル検索を回避するカバーリングインデックスである。.

MySQL 8 の機能インデックスと生成インデックス

表現 のWHERE/JOINはしばしばインデックスをブロックする。MySQL8.0では、式にインデックスを作成したり、生成されたカラムを使用してサーガビリティを作成したりします。これは数値メタ値や JSON フィールドの CAST に特に便利です。.

-- 例：テキスト・フィールドの数値ソート
ALTER TABLE product ADD COLUMN price_num DECIMAL(10,2)
  GENERATED ALWAYS AS (CAST(price AS DECIMAL(10,2)))ストアされる；
CREATE INDEX idx_product_price_num ON product(price_num)；

-- CASTなし、インデックスありのクエリ
SELECT * FROM product
WHERE price_num BETWEEN 10 AND 50
ORDER BY price_num；;

練習私は、新しいインデックスが本当に機能するかどうかをテストし（EXPLAIN）、スローログでその効果を測定します。生成されたカラムは、プレフィックスや正規化されたバリアント（LOWER(email)）を効率的にフィルタリングするのにも役立ちます。.

CMS／ショップ・パターンへの、より的を絞ったアプローチ

メタテーブル (例えば、wp_postmeta)は、(post_id, meta_key)または(meta_key, meta_value)の複合インデックスの恩恵を受けます。meta_value_numericで頻繁にフィルタリングする場合、すべてのクエリでCASTする代わりに、上記のように生成されたカラムを使用します。冗長な部分を削除し（非正規化ライト）、読み取りアクセスをインデックスフレンドリーにすることで、検索ページを高速化している。.

-- WordPressの典型的な例：投稿のメタデータに素早くアクセスする
CREATE INDEX idx_postmeta_postid_metakey ON wp_postmeta(post_id, meta_key)；
CREATE INDEX idx_postmeta_metakey_metavalue ON wp_postmeta(meta_key, meta_value(100))；;

チェックアウト-短いトランザクション、必要な行のみ、使用するWHERE条件にぴったり合ったインデックスなどです。レポートについては、非同期集計（中間テーブル）を計画し、ユーザーフローが遅くならないようにしています。.

スローログの限界と補足指標

小規模で高速なクエリーが多い はスローログでは目立たないが、負荷には加算される。そのため、スループット（クエリ/秒）、95/99パーセンタイル、インデックスを持たないクエリの割合も追跡している。パフォーマンススキーマやAPMツールで、N+1パターンを認識し、結合、バッチロード処理、キャッシュによって具体的に解決する。.

サンプリング はログが大きくなりすぎた場合に便利です。私はlong_query_timeを少し増やしたり、min_examined_row_limitを設定して関連するクエリだけを含めるようにしている。重要: 時系列が比較できるように、常に変更を記録しておくこと。.

作業方法発見から持続可能な改善へ

ベースライン まず、ビフォア・レポート（タイムウィンドウ、トラフィック、コンフィギュレーション）を保存する。その後、クエリーファミリーを次々と最適化し、同じタイムウィンドウを比較する。各修正はリポジトリに文書化される（何を、なぜ、前/後の測定値）。こうすることで、成功が追跡可能になり、リグレッションを防ぐことができる。.

# 大まかな手順（例）
1) pt-query-digest --since '7d' slow-query.log > baseline.txt
2) 上位3つのクエリダイジェストを選択（合計時間で）
3) EXPLAIN/EXPLAIN ANALYZEを実行し、インデックスと書き換え案を作成する。
4) テストデータの作成、負荷プロファイルのシミュレーション
5) 監視を伴うロールアウト（48時間の制限強化）
6) 比較レポート：pt-query-digest --since '48h' > after.txt
7) 結果を文書化し、次回の計画を立てる

計画の安定性 計画が変更された場合（新しいバージョン、統計の変更）、私はヒストグラム、ANALYZE TABLE、インデックス・ランドスケープをチェックします。私は、オプティマイザを恒久的に拘束しないように、選択的に、文書化された方法でのみヒントを設定します。.

明確なステップでまとめる

スタート つまり、ログを有効にし、適切な制限値を設定し、最初の週のデータを収集する。その後、pt-query-digestで要約し、合計時間と分散に従って優先順位を付け、上位のドライバーを選択します。EXPLAIN、サージャブル条件、適切なインデックスを使ってクエリを最適化し、短いトランザクションでロックを制御する。サーバー側では、バッファ、一時テーブル、フラッシュ戦略を適切に設定する。最後に、アラームを自動化し、このサイクルを定期的に繰り返します。こうすることで、トラフィックやデータ量が増えてもデータベースを高速に保つことができます。.

現在の記事