インデックス作成とクエリの最適化

1）インデックス作成と最適化の目標

レイテンシー：P50/P95/P99削減。
スループット：スケールアウトなしのQPS成長。
予測可能性：安定した計画と応答時間に「ジャンプ」はありません。
節約：より少ないIO/CPU、より少ないクラウド請求書。
信頼性：正しいアクセスによるロックとデッドロックの削減。

• 任意の最適化は、正確さと一貫性を維持する必要があります。
• メトリクスとプランログでエフェクトを追跡します。

2）基本的なインデックス構造とそれらを適用するタイミング

2.1 Bツリー（デフォルト）

等しい/範囲、並べ替え、'ORDER BY'。
ほとんどの時間/ID/ステータスフィルタに適しています。

2.2ハッシュ

純粋な等式（'='）、メモリでは安価だが順序外（PG：制約は削除されたが、まだニッチな選択肢）。

2.3 GIN/GiST （PostgreSQL）

GIN： 配列/JSONBキー、全文（tsvector）、 containment（'@>'）。
GiST：地理、範囲、kNN。

2.4 BRIN （PostgreSQL）

「自然にソートされた」テーブルによる超安いインデックス（時間ごとに追加されます）。大きいテーブルが付いている時系列のためによい。

2.5ビットマップ（MySQL/InnoDB：ネイティブなし；DW-DBMS/OLAP）

低cardinalityおよびfacetsのために有効、より頻繁に列の貯蔵で。

2.6列インデックス（ClickHouse）

主キー+データskipping （minmax）、セカンダリのskip indexes （bloom、 set）。
集計と範囲を持つOLAPクエリ。

2.7反転インデックス（Elasticsearch/OpenSearch）

全文、ファセット、ハイブリッド検索。正確なフィルタの場合は、キーワードフィールドとdoc値を使用します。

2.8 MongoDB

シングル、複合、マルチキー（配列）、部分、TTL、テキスト、ハッシュ（均一なキーシャーディング用）。

3）キーおよび合成の索引の設計

3.1左プレフィックスルール

インデックス内のフィールドの順序がユーザビリティを決定します。
クエリ'WHERE tenant_id=？AND created_at>=？ORDER BY created_at DESC'→\'（tenant_id、 created_at DESC、 id DESC）'。

3.2タイブレーカー

安定したソートとシークペジネーションのためにユニークな尾（通常は'id'）を追加します。

3.3部分的/フィルタリングされたインデックス

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3.4カバーインデックス

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3.5機能/計算される

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4）仕切りおよびsharding

4.1パーティショニング（PGネイティブ/テーブル継承；MySQL RANGE/LIST）

時間ごとのパーティーの回転（'daily/weekly'）は'VACUUM/DELETE'を簡素化します。
インデックスはローカルパーティション→B-Treeより小さい、高速な計画です。

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4.2パーティションキー

OLTP-'tenant_id' （load localization）。
時系列/OLAP-'ts'（範囲クエリ）。
ハイブリッド：'（tenant_id、 ts）'+サブパーティー。

4.3シャーディング

'tenant_id'または時間による一貫性のあるハッシュ/range-shard。
クロスシャードクエリ→scatter-gatherとk-wayマージ；シャードカーソルを押したままにします。

5）統計、カーディナリティ、計画

5.1最新の統計情報

自動解析（'autovacuum/autoanalyze'）を有効にし、汚れたディストリビューションの'default_statistics_target'を増やします。

5.2高度統計（PG）

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5.3実行計画

• 'Rows'、' Loops'、'Actual time'、'Shared Read/Hit'、'Recheck Cond'。
• 入れ子ループ、ハッシュ結合、マージ結合。
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan。

5.4計画の安定性

パラメータ化（準備文）は、悪い計画に「固執」することができます。プランキャッシュガードレール（PG： 'plan_cache_mode=force_custom_plan'問題のクエリ）または「転送」定数を使用します。

6）結合および種類の最適化

6.1つの戦略

入れ子にされたループ：内部の小さい外的で、速い索引。
Hash Join：大きなセット、ハッシュテーブルに十分なメモリ。
結合を結合：ソートされたエントリ、すでに利用可能な順序で有利。

6.2参加中のインデックス

'A JOIN B ON B.a_id=A。id'の場合、→'B （a_id）'のインデックス。
結合後のフィルタ-内部テーブルのフィルタの列のインデックス。

6.3トリアージュ

対応するインデックスは'ORDER BY'を避けてください。大きいセットの並べ替えはメモリ/ディスクによって高価です。

7）クエリの書き換え

サブクエリの「雪片」を取り除く。JOINで展開します。
CTE-inline （PG ≥ 12 CTEデフォルトのインラインを使用しますが、'MATERIALIZED'は必要に応じて中間結果をコミットできます）。
'SELECT'→フィールドの一覧（IO/ネットワーク節約）を削除します。
「WHERE」からインデックス化されたフォーム（事前計算された列）に計算を転送します。
集計：増分更新を伴う暫定サマリテーブル/実体化ビュー。

8） Butching、制限およびpagination

バッチ挿入/更新：1つ1つではなく500-5000バッチ。
深い'OFFSET'の代わりに'（sort_key、 id）'によるページネーションを求める。
/joyneをソートする前にダイヤルを制限します（プッシュダウン'LIMIT'）。

9）キャッシュと非正規化

アプリケーションレベルのクエリキャッシュ（key=SQL+bind-vars+rightsバージョン）。
重い集計のための具現化されたビュー；回転/参照計画。
「非正規化」（Denormalization）-計算されたフィールド（割引を含む価格）を頻繁に読み取りますが、一貫性のためにトリガー/バックグラウンドタスクを使用して保存します。
ホットキーのL2としてRedis （TTLとイベント障害）。

10）普及したエンジンの指定

10.1 PostgreSQL

独立系：B-Tree、 Hash、 GIN/GiST、 BRIN、 partial、 functional、 INCLUDE。

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10.2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

ヒストグラム統計（'ANALYZE TABLE……HISTOGRAMをアップデートしました。0).

10.3クリックハウス

主キー=ソート；'ORDER BY （tenant_id、 ts、 id）'。

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10.4 MongoDB

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

診断には'hint（）'を使用し、'covered query'を監視します。

10.5 Elasticsearch/OpenSearch

キーワードとテキストフィールド;並べ替え/集計のためのdoc_values。
ヒープセグメンテーション：集計-重い；'size'を制限し、'composite'集計（ページング）を使用します。
正確な比較が必要なアナライザは含めないでください。

11）競争力、インターロックおよびMVCC

短いトランザクション；不必要に「REPEATABLE READ」の下で「long」が読み込まれないようにします。
インデックス操作はロック（書き込みスループット低減）も行います。
オンラインのインデックス作成を計画する：'CREATE INDEX CONCRETELY' （PG）、 'ALGORITHM=INPLACE'/'ONLINE' （MySQL）。
インデックスの1 時間/ID→「ホットページ」の尾に挿入します。キー（UUIDv7/salt）を配布します。

12）観察可能性およびSLO

• 'db_query_latency_ms' （P50/P95/P99）をクエリ名で指定します。
• 'rows_examined'、 'rows_returned'、 'buffer_hit_ratio'。
• 'deadlocks'、 'lock_wait_ms'、' temp_sort_disk_usage'
• 'Index Scan'が期待されていた'Seq Scan'との計画の共有。
• DBMSのバージョン/パラメータを変更すると、回帰アラートが表示されます。

• スレッショルド（200msなど）でスロークエリログを有効にします。
• スパンとのクエリの相関関係（trace_id)。
• 問題のクエリプランを削除し、レトロスペクティブ用にオブジェクトストレージに保存します。

• 'LIMIT<=50'とホットテナントでP95'<=150 ms'を読んでください。
• P95レコード'<=200ms'で、最大1000行のバッチを記録します。

13）安全および複数のテナント

アクセス制御フィールド（'tenant_id'、 'owner_id'）のインデックスが必要です。
ポリシー（RLS/ABAC）は事前フィルタでなければなりません。そうでなければ、オプティマイザは間違って計画します。
クリアテキストで機密項目をインデックス化しない。ハッシュ/トークンを使用します。

14）アンチパターン

seek-cursorの代替なしの深い'OFFSET'。
「すべてのための1つのインデックス」-メモリの過負荷と書き込みパス。
クリティカルパスの'SELECT'。
関数インデックスのない'WHERE'の列の上の関数。
古い統計による不安定な計画。
安定した注文を待っている間に'ORDER BY'が見つかりません。
インデックスのためのインデックス：高価な書き込み/サポートによるROI <0。

15）実装チェックリスト

1.QPSと時間によるトップNリクエスト→3-5候補を選択します。
2.プラン'EXPLAIN ANALYZE'を削除し、カルディナリティと実際をチェックします。
3.設計インデックス：フィールド順序、INCLUDE/partial/functional。
4.大規模テーブル（テナントキー/テナントキー）のパーティショニングを実装します。
5.上書きクエリ：'SELECT'、インライン単純CTE、 restrict setを削除します。
6.ブッチングとシークペジネーションを有効にします。
7.キャッシュの設定：L1/L2、イベントによる障害。
8.計画の監視とスローログ、回帰のアラートを紹介します。
9.実際のデータ配布で負荷テストを実行します。
10.開発ガイドラインの更新（ORMのヒント、インデックス作成、制限）。

16）例の前後

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17） ORMおよびAPIプロトコル

N+1を避ける：貪欲なサンプル（'includes'、 'JOIN FETCH'、 'preload'）。
明示的なフィールド投影、カーソルによるページネート。
gRPC/REST： limit 'page_size'、 fix 'sort'、 use opaque token。
プランキャッシュ：パラメータ化を使用します。呼び出しごとに「unique」 SQLを生成しないでください。

18）移行と運用

オンラインでインデックスを追加し、INVISIBLE/CONCURRENT、テストプラン、スイッチとしてマークします。
インデックスのリビジョン-定期的な衛生クリーニング：重複、未使用、古い機能の「死んだ」。
パーティーの回転計画（古いドロップ）と「VACUUM/OPTIMIZE」スケジュール。

19）サマリー

クエリの最適化はシステムエンジニアリングです。正しいキーとインデックス、きちんとした計画、思慮深いパーティショニングとシャーディング、クエリとORMの規律、キャッシュとオブザビリティ。記述されたパターンに従うことで、データの成長と負荷に耐性がある高速で予測可能で経済的なシステムを得ることができます。