データベース

ウェブホスティングにおけるデータベースのパフォーマンス：クエリ、インデックス、ロック

その方法をお見せしよう。 データベースのパフォーマンス MySQLは、Webホスティングにおいて、フォーカスされたクエリ、ターゲット・インデックス、クリーンなロッキングを提供します。これにより、MySQL の負荷が軽減され、待ち時間が回避され、多数の同時アクセスがあっても信頼性の高い応答時間を実現します。.

中心点

クエリ スリムに保つ投影、フィルター、EXPLAIN
インデックス を具体的に設定する：where、join、order by
ロック 最小化する：行ロック、短いトランザクション
キャッシング を使用する：Redis/Memcached、キーセット・ページネーション
モニタリング エスタブリッシュメントスローログ、パフォーマンススキーム

ウェブホスティングのスキームとリソース：調整ネジ

考え抜かれた スキーム設計 クエリの可読性を犠牲にすることなく、不必要な結合やデータの重複を防ぐことができるため、サーバーの時間を節約することができる。私はテーブルを適切なレベルに正規化し、実測値から結合にコストがかかりすぎることがわかった場合は特に正規化を解除する。共有ホストやマネージドホストでは、CPU、RAM、I/Oプロファイルに注意を払っています。ボトルネックはSQLではなく、不足しているリソースにあることが多いからです。InnoDBの場合は innodb_buffer_pool_size 通常、利用可能なRAMの70-80%を使用し、できるだけ多くのページをメモリ内に保持します。さらに、クエリが低速のデータ・キャリアをブロックしないように、テンポラリ・テーブルがメモリに収まるかどうかもチェックする。.

データモデルとデータ型高速アクセスの基礎

私が選ぶ データタイプ BIGINTの代わりにINT、金額にはDECIMAL、時間指定にはTEXTの代わりにDATETIME。文字列には一貫して utf8mb4 を適切な照合順序（アクセントや大文字小文字を区別しない比較には_ai_ciなど）で使用します。大文字小文字を区別したり、バイナリ比較が必要な場合は、特にカラムレベルで_bin照合順序を使用します。これらの決定は、インデックス・サイズ、ソート動作、そして最終的にはバッファ・プールに収まるデータ量に影響します。.

時点では 主キー キーは無駄のないもの（通常はAUTO_INCREMENT INT/BIGINT）にします。InnoDBのセカンダリインデックスはサフィックスとしてPKを含むので、コンパクトなPKはメモリを節約し、インデックスのみのスキャンを高速化します。単調に成長するPKは、挿入時のページ分割も減らします。時間ベースの分析で書き込みが非常に多いテーブルの場合、私はcreated_atまたはstatus+created_atのセカンダリインデックスを使用し、ソートコストをかけずに典型的なクエリに対応します。.

のために JSON-フィールドで、JSONの特定の部分を抽出する計算（GENERATED）カラムを作成します。これらの生成されたカラムに通常のカラムのようにインデックスを付けることで、JSONパスに対するフィルタがインデックスに基づくようになります。また、派生した値（LOWER(email)など）をWHEREで関数を使用する代わりに仮想カラムとしてマッピングします。.

効率的なクエリーの設計EXPLAIN、フィルタ、プロジェクション

私はいつも最適化を クエリーSELECT-*を使用せず、必要なカラムのみを使用することで、ネットワークとCPUの負荷を軽減します。EXPLAINを使って、インデックスが有効かどうか、オプティマイザがフルテーブルスキャンの代わりにインデックススキャンを使っているかどうかをチェックする。WHEREにLOWER()のような関数を使わず、カラム側でフィルターを作成し、インデックスが有効になるようにする。顕著な待ち時間が発生する場合、私はしばしばクエリ設計に原因があることを指摘します。高いデータベース遅延. .遅いクエリログから、最も時間を浪費するクエリを知ることができ、EXPLAIN ANALYZEと実際のパラメータで微調整を行う。.

をセットした。 準備されたステートメント をバインドパラメータで使用することで、構文解析と計画作成の労力を削減し、計画を安定させることができます。私はしばしば、異なる列にわたるOR条件を、2つのインデックスフレンドリーな部分クエリのUNION ALLで置き換えます。可能であれば 取材クエリー選択されたすべてのカラムを含む適切なインデックスがあれば、追加のテーブル検索を回避し、I/Oを節約できる。私は、インデックス・シーケンスと調和するようにソートを計画する。これにより、ファイル・ソートやテンポラリ・テーブルが不要になる。.

MySQL 8では ウィンドウ機能 結合やサブクエリを置き換え、インデックスに親和性がある場合。大きなLIMIT値では、シークメソッド（キーセット）と安定したカーソル（例えば、ORDER BY created_at, id）を使用することで、決定論的で再現性のあるページビューを実現します。.

日常生活における結合、ページネーション、キャッシュ

私の好み INNER JOIN 技術的に許されるのであれば、LEFT JOINの前に、両方のテーブルの各結合カラムにインデックスを付けます。私はサブクエリをjoinに置き換えることがよくあります。OFFSETは大きなスキップがあると高くつくので、私はページ分割をキーセットのページ分割（WHERE id > ? ORDER BY id LIMIT N）として実装することを好む。めったに変更されない結果は、RedisやMemcachedを使ってキャッシュする。歴史的に存在するクエリ・キャッシュは、管理オーバーヘッドがブレーキになるため、多くの書き込み操作では無効にしておく。.

防ぐ N+1クエリ, 必要なデータレコードをバッチ（サイズに制限のあるINリスト）でロードし、適切な結合を使用して事前に関係を解決する。については キャッシング 明確な無効化ルールを定義している：変更にはライトスルー、揮発性領域には短いTTL、フィードやアーカイブには長いTTL。デプロイメントが古い構造にぶつからないように、キャッシュキーをバージョン部分（スキーマやフィルターのバージョンなど）で構成する。.

実際のアプリケーションでは、キーセットのページネーションに 複合カーソル (created_atやidなど)を使用することで、ソートが安定し、インデックスがサポートされます。ソフトな基準（関連性など）については、先頭のソート基準がインデックス可能であるようにし、関連性はキャッシュや事前計算でタイブレーカーとしてのみ機能するようにする。.

指標を正しく計画する：単一指標から複合指標へ

正確な インデックス は線形検索を対数に変換する：100,000行では、フルスキャンではなく、数回の比較で終わるのが普通です。WHERE、JOIN、ORDER BYで出現するカラムにインデックスを設定し、それらが使用されているかどうかをEXPLAINでチェックする。(A,B,C)は、A、A+B、A+B+Cの検索をカバーするが、Aを含まないB+Cはカバーしない。長い文字列の場合は、最初の10～20バイトのようなプレフィックス・インデックスを使用して、メモリを節約し、キャッシュヒットを増やす。どのように投与指標インデックスが多すぎると、INSERT/UPDATE/DELETEに多くの時間がかかる。.

インデックスタイプ	メリット	デメリット	代表的な使用例
プライマリー	独自性、非常に高速な検索	重複不可	各テーブル、InnoDBのクラスタキー
ユニーク	値の重複を防ぐ	書く努力が増える	電子メール、ユーザー名、スラッグ
インデックス	柔軟なフィルターとソート	保管とメンテナンスの労力	WHEREカラムとJOINカラム
フルテキスト	関連性に基づくテキスト検索	凝ったデザイン、大きめ	タイトルとコンテンツで検索

私は次のことに注意を払っている。 カバーリング指数, これは、必要なすべてのカラム（フィルター、ソート、プロジェクション）を含む。これにより、インデックスの読み込みのみを行う „インデックスを使用する “プランを実現することができる。降順でソートするために、MySQL 8の複合インデックスのDESCコンポーネントのサポートを使用しています。.

実験に使うのは 不可視インデックス を使っている：インデックスを不可視にし、プランとレイテンシを観察して、本番負荷のリスクを負うことなく、削除するか維持するかを決定します。定期的なANALYZE TABLEを無駄のない、的を絞ったものにすることで、統計情報を新鮮なものにし、オプティマイザがカーディナリティを正しく推定できるようにしています。.

WordPress MySQL: 典型的なホットスポットと修正点

時点では ワードプレス-ほとんどのクエリがここで終わるからだ。アーカイブやフィードがソートされた投稿を配信する場合はwp_posts.post_dateを、素早くメタデータを検索する場合はwp_postmeta.meta_keyをインデックスします。WooCommerceでは、多くのメタのJOINを含むことが多い注文や商品のクエリに注意している。私はキーセットのページネーションと適切なインデックスを使用したサーバーサイドのソートで高価な管理者リストを高速化します。また、オブジェクトキャッシュとトランジェントを使用して、繰り返しクエリが常にデータベースにヒットしないようにしています。.

時点では meta_query-フィルタを使うことで、正しいタイピングを保証している。比較をインデックス可能なままにするため、数値をキャストしている。ワイルドカードを先頭に持つ広範なLIKE検索は避け、代わりに検索可能なキーを個別に保存し、インデックスを作成する。可能であれば、あらかじめWP_Queryに必要なメタデータをロードし、テンプレート内のN+1パターンを防ぎます。管理エリアに恒久的な負荷がかからないように、cronジョブとハートビートの頻度を調整しています。.

ロックを理解する：行ロック、MVCC、アイソレーション

最小限に抑える ロック, InnoDBに依存し、短いトランザクションを記述し、本当に必要な行だけを触る。行レベルのロックは同時アクセスを可能にするが、テーブル・ロックは多くのことを止める。MVCCはREAD COMMITTEDのような適切な分離レベルを設定する限り、読者がブロックすることなく読み込めるようにします。SELECT ... FOR UPDATEは書き込みセッションをブロックし、より長い待ち時間の連鎖を生み出す可能性があるため、私は控えめに使っています。ブロックとサイクルに関するより詳細な実践例については、以下のガイドを参照して下さい。ホスティングにおけるデッドロック.

に注目している。 デフォルトの隔離 InnoDBからのREPEATABLE READと、その結果生じる範囲更新時のギャップロック。可能であれば、READ COMMITTEDに切り替え、ファントムが技術的に許されるかどうかをチェックする。書き込み処理を厳密にカプセル化し、トランザクション内でのインタラクティブな待ち時間を避け、ホットスポット（カウンターなど）を別のテーブルに分離するか、条件付きのアトミックUPDATEを使用する。.

トランザクションをスリムに保ち、デッドロックを回避する

持っている トランザクション 可能な限り短くし、書き込み部分の前後にロックを必要としない計算集約的なステップを移動させる。セッション間でサイクルが発生しないように、常に同じカラムとテーブルの順序で更新を行う。長いバッチを小さなチャンクに分割し、その間に他のセッションが処理を進められるようにする。コンフリクトが発生した場合は、セッションを何分も待たせるのではなく、バックオフによるリトライに頼る。ロックとステートメントのタイムアウトは、キューが気づかないうちに蓄積されるのを防ぐ。.

時点では デッドロック 私は、SHOW ENGINE INNODB STATUSとデッドロック情報を分析して、関係するクエリを特定し、アクセスシーケンスを調整します。範囲スキャンを減少させるインデックスを追加することで、タイムアウトの増加以上に解決することがよくあります。影響を受けたSQLをバインディングを含めてログに記録し、病態を再現して恒久的に修正できるようにします。.

スケーリング：レプリケーション、パーティショニング、シャーディング

負荷が大きくなったら、デカップリングする。 アクセスを読む プライマリ・サーバーの書き込み負荷がアプリケーション全体の速度を落とさないように、リード・レプリカを経由する。キャッシュはレプリカの前に置かれ、すべてのリクエストがデータベースに行くわけではありません。歴史的に成長してきた大きなテーブルを、日付やハッシュでパーティショニングして分割することで、メンテナンスやスキャンをより予測しやすくしている。一つのノードが限界に達したら、特化したドメインに従ってシャーディングすることも考えています。アプリケーションとドライバがレプリケーションのラグを処理し、クリティカルな処理にのみ一貫性のあるパスを使用することが重要であることに変わりはない。.

私は次のことを考慮に入れている。 リード・ユア・ライト-要件：クリティカルなフローはプライマリ・サーバーから直接読み込みますが、機密性の低いパスは遅延を伴ってレプリカから読み込むことがあります。ラグ・メトリクスを継続的にチェックし、制限を超えた場合は自動的にプライマリ・サーバに切り替えます。適切なインデックスがない場合は、多くのパーティションにまたがるグローバルなORDER BYを避ける。.

サーバーの設定：適切なパラメータ

バッファープールに加えて、私は次のように調整する。 最大接続数 を実際の並列度に合わせることで、サーバーがあまり多くのセミ・アクティブ・スレッドを管理しないようにしている。thread_cache_sizeを使用して、頻繁な接続による高価なスレッドの新規作成を回避している。tmp_table_sizeとmax_heap_table_sizeを十分大きくして、テンポラリ・テーブルがデータ・キャリアに切り替わることがほとんどないようにしている。多くのRAMを搭載したシステムでは、メモリとSSDが計画通りのパフォーマンスを発揮するように、クリーンなNUMAとI/Oチューニングに注意を払っている。ストレージ・メディアがいっぱいにならずに診断が残るように、ログのローテーションを制限している。.

PHPとNodeの環境では、私は コネクションの再利用 そして限られたワーカープール：何百ものアイドリング状態のコネクションよりも、少数の十分に利用されたコネクションのほうがよい。PHP-FPMでは、pm.max_childrenとpm.max_requestsを設定して、MySQLが接続の洪水で溺れないようにしています。負荷に見合った持続的なコネクションを使用し、オーバーコミットが発生しない場合にのみ使用する。.

モニタリングとトラブルシューティング：私が毎日チェックしていること

測る 継続的遅いクエリログ、パフォーマンススキーマ、ステータス変数は、ユーザが待ち時間に気づく前に、私に傾向を示してくれます。私はEXPLAIN ANALYZEを使用して、個々の演算子の実際の実行時間をチェックし、期待値と比較します。pt-query-digestやmysqltuner.plなどのツールは、インデックス、バッファサイズ、不良パターンに関する情報を提供してくれます。私は毎週フラグメンテーションをチェックし、測定可能な差異がある場合はターゲットとなる OPTIMIZE TABLE を実行します。変更後は、実際のカーディナリティでも最適化が機能するように、常に本番データのダンプでテストしています。.

に対して コアメトリクス 私の場合、バッファ・プールのヒット率、検査行数対送信行数、handler_read_rnd_next（フル・スキャンの割合）、ディスク上のテンポラリ・テーブル、threads_running、InnoDBの行ロック時間、table_open_cache、open_files_limitなどである。異常値の場合、私は特にパフォーマンス・スキーマ・コンシューマーをアクティブにし、sysスキーマ・ビューを使用してホットスポットをクエリーレベルとウェイトレベルに分解する。.

オプティマイザの統計とプランの安定性

持っている統計オプティマイザが選択的述語を正しく評価するように、関連するデータの変更、およびカーディナリティの推定が困難な場合、ヒストグラム（MySQL 8）を使用します。また、カーディナリティの推定が困難な場合は、ヒストグラム（MySQL 8）を使用し、オプティマイザが選択述語を正しく評価するようにしています。ハードオプティマイザヒントは全面的に避け、使用するとしても計測後にごく限られた範囲で使用するのみです。.

運用の変化：オンラインDDLとマイグレーション・パターン

スキーマの変更は アルゴリズム=インスタント/インプレース およびLOCK=NONEが利用可能な場合。これにより、書き込みや読み込みを中断することなく、運用中に新しいカラムやインデックスを導入することができる。高価な再構築の場合は、シャドーテーブルや切り替え可能なビューやフィーチャーフラグを使用する。メインのロードウィンドウの外でインデックスを構築し、I/Oとレプリケーションのレイテンシを監視して、リードレプリカが遅れないようにすることを好む。.

バルクオペレーションとデータメンテナンス

のために 大量挿入 管理されたバッチで複数行のINSERTを使用し、オートコミットをスキップし、トランザクションを小さく保つ。許可されている場合は、LOAD DATA INFILEを使用すると大幅に高速化される。そうでなければ、プリペアド・ステートメントと適切なバッチ・サイズで作業する。大規模な更新の場合は、ロックを短く保ち、バッファ・プールがあふれるのを避けるために、反復的に処理を進める（安定したソートによるLIMITループ）。メンテナンスジョブ（アーカイブ、古いデータの削除）は、生産性を落とさないように、慎重なスロットルロジックで計画する。.

重要なパターンと迅速な対策

私が ピーク負荷 私はOFFSETで高価なページを制限し、キーセットのページネーションに切り替えた。頻繁に使用するフィルターにインデックスがない場合、うまく設定された複合インデックスでさえ、二桁のパーセンテージの利益をもたらす。長いロックの場合は、最大のトランザクションを小さな単位に切り分ける。WordPressのプラグインをアップデートする前にクエリーをテストする。測定しやすいように、クエリーレベルでTiming、Rows Examined、Rows Sentを設定し、進捗を客観的に証明できるようにしている。.

簡単にまとめると

明確な クエリ, 私は、適切なインデックスと無駄のないロックによって、データベースのパフォーマンスを持続的に向上させています。プロジェクションとフィルタリングから始め、EXPLAIN ANALYZEで計測し、スキーマとインデックスを修正する。キャッシュを早めに開始し、リードアクセスが増えたらレプリケーションをオンにし、パーティショニングで非常に大きなテーブルを安定させる。innodb_buffer_pool_size、tmp_table_size、max_connectionsなどのパラメータは、勘ではなくデータに基づいて設定します。一貫して測定し、的を絞った変更を行い、再度測定すれば、ウェブホスティングで短いレスポンスタイムと安定したユーザーエクスペリエンスを達成できるだろう。.