ほとんどのプロジェクトでは、mysql クエリのレイテンシが高いのは、パフォーマンスの低い クエリ設計 – ホスティングによるものではありません。具体的にその方法をご紹介します。 データベースの最適化 インデックス、バッファ、接続戦略によってレイテンシが低減される理由、およびインフラストラクチャが主な原因となることはほとんどない理由についてご説明します。.
中心点
以下の重要なポイントにより、データベースへのアクセス速度の低下を正確に分析することができます。.
- インデックス 高速または低速のクエリを決定します。.
- クエリ構造 JOIN とサブクエリは実行時間に影響を与えます。.
- プール 接続の確立によるオーバーヘッドを削減します。.
- バッファプール I/O のレイテンシとブロッキングを低減します。.
- モニタリング クエリ時間、サーバー時間、ネットワーク時間を明確に区別します。.
ホスティングがボトルネックになることはめったにない理由
私はよく、 レイテンシー 「ホスティングの速度が遅い」ことが原因です。これは確かに当てはまる場合もありますが、最大の要因は クエリ. 測定結果によると、内部と外部の MySQL インスタンスには明らかな違いがあることがわかります。クエリ 1 件あたりの処理時間は、内部が 0.0005 秒、外部が 0.02～0.06 秒です（出典 [1]）。 クエリが適切にインデックス化され、構造化され、キャッシュに適している場合は、この 50 倍の差も実際にはそれほど重要ではありません。インデックスなしで同じクエリを 100 回実行すると、サーバーまでの距離に関係なく、時間を失うことになります。そのため、インフラストラクチャを疑う前に、まずクエリのプロファイルを確認します。.
mysqlクエリのレイテンシーを実際に左右する要因
クエリ時間は、クライアントの送信時間、サーバーの処理時間、および ネットワーク 一緒に。典型的なウェブアプリケーションでは、 加工 DB サーバー上で、特にフルテーブルスキャンや誤った結合の場合に発生します。適切なインデックスがない場合、読み込まれるページ数が増加し、オプティマイザーは最適ではないプランを選択し、CPU は過熱します。同時に、チャッティアプリは、多くの小さなラウンドトリップによって、ネットワーク時間を不必要に膨らませることがあります。 そのため、私はクライアント→サーバー、実行、サーバー→クライアントを個別に測定し、実際のボトルネックを明確に把握しています（[5]を参照）。.
トランザクション、ロック、および分離
見過ごされがちな大きなレイテンシ要因は、 錠前 そして長すぎる トランザクション. InnoDB は MVCC と行ロックを使用しますが、 REPEATABLE READ ギャップロックが追加されると、範囲の更新が遅くなる可能性があります。長いトランザクションは、古いバージョンを元に戻す操作で保持し、メモリと I/O の負荷を増大させ、競合する書き込み操作をブロックします。そのため、私は意図的にトランザクションを短くしています。つまり、必要最小限のステートメントのみを使用し、早期にコミットし、トランザクション内でユーザーの操作を待たないことです。.
UPDATE/DELETE には、私は sargable WHERE 条件と適切なインデックスを使用して、不必要に多くの行がロックされないようにします。ロック待機は、パフォーマンススキーマ（events_waits、lock_instances）およびデッドロックログで認識します。繰り返し発生するパターンは、より優れたインデックス、他のアクセス順序、または技術的に許容される場合は SELECT … FOR UPDATE SKIP LOCKED, ワーカーがブロックされないようにするため。 innodb_lock_wait_timeout 私は、リクエストを数分間保持する代わりに、エラーを早期に発見できるよう、意図的に控えめなサイズ設定を行っています。.
インデックス化：最大のレバレッジ
適切なものがなければ インデックス MySQL はテーブル全体を検索します。小さなテーブルでも、不要な CPU-負荷。私は常に EXPLAIN から始め、type=ALL、key=NULL、および rows 対 rows_examined の関係を確認します。WHERE 列および JOIN 列の複合インデックスは、スキャンされる行数を大幅に削減します。インデックス内の順序は重要です。選択した列を最初に、次にその他のフィルターを配置します。さらに詳しく知りたい方は、私のヒントをご覧ください。 MySQLインデックスについて理解する 具体的なクエリパターンを検証します（[3]を参照）。.
クエリ構造：サブクエリの代わりに JOIN を使用
ネストされたサブクエリは、多くの場合、パフォーマンスの低下につながります。 計画 同等のものとして ジョインズ. 行ごとに再計算する相関サブセレクトを、適切なインデックスを使用した明確な結合に置き換えます。その際、フィルターはできるだけ早い段階で設定し、sargable 条件（例：列 = 値ではなく関数（列））に注意します。LIMIT と ORDER BY を使用する場合は、サポートインデックスが必要です。そうしないと、MySQL はメモリまたはディスク上でソートを行います。 また、COUNT(*) を広い範囲にわたって高速化するには、行全体を読み込む代わりに、狭いカバリングインデックスを使用します。.
一時テーブル、ソート、メモリ制限
ソートインデックスやグループ化インデックスがない場合、MySQL は ファイルソート および一時テーブル。RAM 内の小さな一時ファイルは問題ありませんが、それらが tmp_table_size/max_heap_table_size または BLOB/TEXT を含む場合は、 ディスク – レイテンシが急激に増加します。そのため、適切なインデックスでカバーされる ORDER BY/GROUP BY に注意し、列幅と SELECT リストを縮小して、一時構造を小さく保つようにしています。.
JoinバッファとSortバッファは、グローバルに巨大にするのではなく、実際のワークロードに応じて意図的にサイズを設定しています。多くの同時セッションでバッファが大きすぎると、メモリ不足の原因になります。 パフォーマンススキーマ（tmp_disk_tables、sort_merge_passes）およびスローログ（using temporary; using filesort）にヒントがあります。LIMIT と ORDER BY が避けられない場合は、ソート列にインデックスとフィルターを追加することで、MySQL が範囲を indexranged そして、早く打ち切ることができる。.
N+1クエリとORMの落とし穴
古典的な N+1 パターンは、 レイテンシー：リストが読み込まれ、各エントリに続いて 2 つ目のリストが続きます。 クエリー. リクエストごとのクエリ数が多いため、後続のクエリを JOIN または IN 句に置き換えます。ORM は汎用的ではあるものの最適とは言えない SQL を生成することが多いため、ここでは Lazy/Eager ロード設定を使用して対応しています。 適切な場合は、SELECT * ではなく SELECT 列を意図的に選択します。これにより、転送されるデータ量が減少し、キャッシュの効率が向上します。.
データ型とプライマリキーの設計
優れたスキーマ設計は、根本的なレイテンシの削減につながります。私は 最小の適切なデータ型 （BIGINT の代わりに TINYINT/SMALLINT、より短い VARCHAR 長）を使用します。これは、バイト数が少ないほどインデックスとバッファプールの負荷が軽減されるためです。照合順序は比較と選択性に影響します。大文字と小文字を区別しない照合順序は検索を簡略化しますが、パターン検索では選択性が低下する場合があります。長いテキスト列については、必要に応じて以下を使用します。 プレフィックスインデックス, 最初の兆候が十分に選択的であるならば。.
InnoDB では、 主キー 物理的な順序であり、すべての中間インデックスに含まれています。狭い、, 単調なPK (例：BIGINT AUTO_INCREMENT) は、ページ分割、RAM 要求、書き込み償却を最小限に抑えます。 ランダムな UUIDv4 は、継続的な分割とコールドページを引き起こします。UUID が必要な場合は、時間順のバリエーション（例：ソート可能な UUID）を選択するか、技術的な PK を専門的なキーから分離します。幅の広い複合 PK は、すべてのセカンダリインデックスのコストを増加させます。ここでは、明確な PK 戦略が特に有効です。.
コネクションプーリングと接続のライフサイクル
各接続の費用は 時間 そして負担をかける リソース. 各リクエストごとに新しい接続を作成すると、オーバーヘッドが感じられるレイテンシーに加算されます。ワーカーが既存のセッションを再利用できるように、コネクションプーリングを使用しています。アイドルタイムアウトと最大接続数は、ピークをスムーズに吸収できるサイズに設定しています。ProxySQL や言語固有のプーラーなどのツールを使用すると、特に多くの並列リクエストがある場合に、レイテンシーのピークを大幅に低減できます。.
準備済みステートメント、プランの安定性、統計情報のメンテナンス
高いQPSでは、解析と最適化にかなりの時間がかかります。. 準備されたステートメント このオーバーヘッドを削減し、プランを安定させ、モニタリングにおけるクエリの消化を改善します。また、プレースホルダーは、絶えず変化するリテラルによってプランのタイル化を防ぐ役割も果たします。オプティマイザーの推定値が不正確になった場合（rows 対 rows_examined の差が大きく変動した場合）、私は統計情報を更新します（アナライズテーブル）そして、顕著なデータスキューがある場合には ヒストグラム これにより、オプティマイザーはより適切な結合順序とインデックスの決定を行うことができます。.
と一緒に EXPLAIN ANALYZE 推定値を 実際に 処理された行を確認し、カーディナリティやフィルタが誤って評価された箇所を確認します。. 見えないインデックス 私は、製品システムを大幅に変更することなく、安全に代替案をテストするために使用しています。パラメータのスキューによって計画に矛盾が生じた場合、クエリヒントが部分的に役立ちます。ただし、統計とインデックスが正確である場合にのみ、私はクエリヒントを使用します。.
バッファ管理とキャッシュ
InnoDBバッファプールはホット データ RAM内で、高価な ディスク-アクセス。DBホストの利用可能なメモリの約70～80 %にサイズを設定し、バッファプールヒット率を監視し、ページフラッシュを確認します（[3]を参照）。 ダーティページが多すぎたり、ログバッファが不足していると、スループットが低下します。ログとデータのボリュームを分離することで、I/O の競合を回避し、書き込みパフォーマンスを安定させることができます。この微調整は、プロバイダに関係なく、純粋に設定の問題です。.
クエリキャッシュの代わりに外部キャッシュ
MySQLクエリキャッシュは ブレーキ 高い並列性で動作し、8.0 で削除されました。 私は、繰り返し発生する読み取り負荷には Redis または Memcached を使用し、明確に定義されたオブジェクトをキャッシュしています。混同を防ぐため、キャッシュキーはクライアントと言語で厳密に区別しています。無効化は、イベントによる更新後など、イベント駆動で制御しています。これにより、データベースの負荷を軽減し、ラウンドトリップを減らし、応答時間を大幅に安定化させています。.
レプリケーションと読み取りのスケーリング
スケーリングされる読み取り負荷には、以下を使用しています。 リードレプリカ. 私はそこに許容的な読み取りのみをルーティングし、 レプリケーションラグ ユーザーが古いデータを見ないように、常に監視しています。「Read-your-writes」は、書き込み処理の直後にスティッキーセッションまたはプライマリへのターゲットルーティングで解決しています。長いトランザクション、大きなバッチ、DDL はラグを拡大させるため、ここではオフピークのウィンドウと小さなコミットチャンクを計画しています。.
重要：レプリケーションは、不適切なクエリを隠蔽するものではありません。 乗算 まず、インデックスとクエリ構造を整理します。その後、初めて真のリードスプリッティングの価値が生まれます。モニタリング面では、ラグのピークと書き込みのピークを相関させ、binlog およびフラッシュパラメータがレイテンシと耐久性の要件に適合しているかどうかを検証します。.
コンテキスト付きモニタリング
文脈がなければ、あらゆる 指標 不完全なので、私は分離します タイムズ クリーン：クライアント、ネットワーク、サーバー。私は、Rows Examined 対 Rows Sent、クエリ時間の分布 (P95/P99)、およびロックの待機時間を監視しています。 スロークエリログをワークロードのピークと照らし合わせて、原因を特定します。レプリケーションの遅延は、書き込み処理が遅いと読み取りレプリケーションが遅延するため、別途測定します（[5] 参照）。そうすることで初めて、クエリの設計、インデックス、インフラストラクチャのどれに対処すべきかを判断できるのです。.
WordPress：オートロードとオプションテーブル
多くの WordPress サイトは、 オプション-表と大きすぎる オートロード-データを削除します。そのため、私は定期的に自動ロードされるオプションのサイズを確認し、ほとんど使用されないエントリをオンデマンドに移行しています。option_name のインデックスとスリムな SELECT により、フルスキャンを回避しています。Cron イベントを管理し、トランジェントを削除することで、データベースをスリムに保っています。入門ガイドが必要な方は、私のヒントをご覧ください。 オートロードオプション 実用的なチューニング手順について。.
パーティショニングとアーカイブ
パーティショニング 特に、非常に大規模で時間とともに増加するテーブル（ログ、イベント）の場合に役立ちます。個々のクエリの速度を上げるというよりも、 剪定 メンテナンスが簡単：古いパーティションはすぐに削除でき、再編成も計画的に行える。私は、数少なく、意味のある範囲パーティション（例：月単位）を選択している。パーティションが多すぎると、メタデータのオーバーヘッドが増加し、計画が複雑になる可能性がある。ユニークはパーティション列に含まれている必要がある。私は、スキーマでこれを考慮している。.
多くの場合、それだけで十分です。 アーカイブプロセス, 、古いデータをスリムなアーカイブテーブルに移動します。アクティブなワークスペースは縮小し、バッファプールはより頻繁にヒットし、パーティショニングを行わなくてもレイテンシは低下します。書き込み負荷の高いテーブルについては、挿入および更新のコストを抑えるために、不要なセカンダリインデックスを削減します。追加のインデックスは、書き込みパスが 1 つ増えることを意味します。.
インフラが足かせになる場合
クエリが主な手段であるとはいえ、時には インフラストラクチャー ボトルネック。CPUスチール、高い iowait, 、ストレージのレイテンシ、ネットワークのRTT。よくある症状は、計画が良好であるにもかかわらず、数ミリ秒のP95リードが発生したり、負荷がかかるとレイテンシが変動したりすることです。この問題には、近接性（同じAZ/VLAN）、安定したプライベート接続、十分なIOPS/スループット、そしてアプリとDBが同じホストで動作している場合はUnixソケット経由のアクセスによって対処しています。 TLS ハンドシェイクと DNS 解決は、Keep-Alive および Connection Reuse によって回避しています。重要なのは、まず測定してから変更することです。.
実践チェック：測定可能な閾値
コンクリート しきい値 私の負担を軽減する 優先順位付け. 以下の概要は、迅速な位置確認と的を絞った対策のために利用しています。.
| 原因 | 典型的な指標 | しきい値 | 優先順位 | 緊急措置 |
|---|---|---|---|---|
| 外部DBと内部DB | クエリのレイテンシ | 0.0005 秒（内部）/ 0.02～0.06 秒（外部）（出典 [1]） | チャットアプリで人気 | 往復の削減、バッチ処理/JOIN |
| 不足している指標 | 検査済み行数 » 送信済み行数 | 係数 > 100 危機的 | 非常に高い | EXPLAIN を評価し、複合インデックスを作成する |
| 弱いバッファプール | バッファプールヒット率 | < 95 % ホットセット | 高い | バッファプールを拡大し、ワーキングセットを確認する |
| N+1パターン | リクエストあたりのクエリ数 | > 20 単純なリストの場合 | ミディアムハイ | JOIN または IN ではなく、フォローアップクエリ |
| 接続設定 | 接続時間 | P95 > 30 ミリ秒 | ミディアム | プールを有効にし、キープアライブを調整する |
迅速な行動計画
私はまず インデックス そして、その スローログ: EXPLAIN、不足しているキーを追加、サージブルな条件を作成。その後、N+1 を排除し、サブセレクトを JOIN に置き換え、オプションでバッチ処理を行います。 3 番目のステップでは、コネクションプーリングを有効にし、ターゲットを絞った集約によってラウンドトリップを削減します。その後、バッファプールを最適化し、ヒット率を検証し、ホットリードを Redis に移行します。 追加の実践例については、以下をご覧ください。 SQLデータベースの最適化 すぐに実行できるステップで。.
簡単な要約
データベースのレイテンシが高いのは、たいていパフォーマンスの低い クエリ, 、それによってではなく ホスティング. インデックス、クリーンな JOIN、コネクションプーリング、そして適切な大きさのバッファプールが重要です。外部レイテンシーの差は存在しますが、クエリの設計が適切であればその重要性は低くなります。コンテキストを伴ったモニタリングは原因と結果を分離し、より迅速に的を絞った対応を可能にします。この順序に従えば、プロバイダを変更することなく、アプリの速度を顕著に向上させ、レイテンシーを永続的に低減することができます。.
