索引和優化查詢

1）索引化和優化目標

潛伏期：減少P50/P95/P99。
吞吐量：QPS的增長沒有水平縮放。
可預見性：計劃穩定，響應時間沒有「飛躍」。
節省：IO/CPU更少,雲費更少。
可靠性：通過正確的可用性減少鎖定和阻塞。

• 任何優化都必須保持正確性和一致性。
• 跟蹤計劃指標和邏輯中的效果。

2）索引的基本結構以及何時應用

2.1 B樹（默認值）

等於/範圍，排序，「ORDER BY」。
適用於大多數時間/ID/狀態過濾器。

2.2 Hash

純等式（'='）在內存上更便宜，但沒有順序（PG：取消限制，但仍然是一個利基選擇）。

2.3 GIN / GiST (PostgreSQL)

GIN： 數組/JSONB密鑰、全文、containment（'@>'）。
GiST：地理，範圍，kNN。

2.4 BRIN (PostgreSQL)

超便宜的指數通過「自然排序」的桌子（時間僅適合）。適用於帶有大表的時間系列。

2.5位圖（MySQL/InnoDB：非本機；DW-DBMS/OLAP）

對低基數和小面有效，通常在柱狀存儲器中。

2.6列索引（ClickHouse）

Primary key + data skipping (minmax), secondary через `skip indexes` (bloom, set).

帶有聚合和範圍的OLAP查詢。

2.7反向索引（Elasticsearch/OpenSearch）

全文，面板，混合搜索。對於精確的過濾器,請使用keyword字段和doc values。

2.8 MongoDB

Single，compound，multikey（數組），partial，TTL，text，hashed（用於通過均勻鍵進行散列）。

3）密鑰和復合索引設計

3.1左前綴規則"

索引中的字段順序決定了可用性。

查詢'WHERE tenant_id=？AND created_at >=?ORDER BY created_at DESC` → индекс `(tenant_id, created_at DESC, id DESC)`.

3.2 Tie-breaker

添加獨特的尾巴（通常為「id」）以進行穩定的排序和seek分頁。

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覆蓋索引

在索引中包含「可讀」字段（MySQL： 「INCLUDE」；PG 11+: `INCLUDE`):

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）參與和混亂

4.1參與（PG本機/表繼承；MySQL RANGE/LIST)

按時間輪換（「daily/weekly」）簡化了「VACUUM/DELETE」。
指數是本地分期→小於B樹，比計劃更快。

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

4.2黨派鑰匙

在OLTP中-通過「tenant_id」（負載本地化）。
在時間系列/OLAP中-通過「ts」（範圍查詢）。
混合體：「（tenant_id，ts）」+子條紋。

4.3 Sharding

通過「tenant_id」或時間進行一致性收縮/範圍檢查。
跨碼查詢→ scatter-gather和k-way merge；保持每張收件箱。

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`.
• Типы join: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5.4計劃穩定性

參數化（預置狀態）可能在不良計劃上「紮根」。使用plan cache guerrails （PG： 'plan_cache_mode=force_custom_plan'對於有問題的查詢）或「鉆孔」常數。

6）優化加入和排序

6.1個戰略

Nested Loop：內部較小的外部快速指數。
Hash Join：大套件，在hash表下有足夠的內存。
Merge Join：排序的輸入，如果已經有順序，則有利可圖。

6.2合並下的索引

對於'A JOIN B ON B.a_id=A.id' →索引為'B （a_id）'。
對於join後過濾器,是內部表過濾器列上的索引。

6.3個分類

避免在沒有相應索引的情況下使用「ORDER BY」；通過內存/磁盤對大型集進行排序。

7）重寫查詢（query rewrite）

擺脫分帶的「雪花」；在JOIN中展開。
使用CTE-inline （PG ≥12 inlines CTE默認,但如果需要,「MATERIALIZED」可以提交中間結果）。
清除「SELECT」 →列出字段（節省IO/網絡）。
將計算從「WHERE」遷移到索引形式（預測列）。
聚合：具有增量更新的預匯總表/實例化視圖。

8） Batching,限制和分區

Batch-insert/Update： 500-5000封裝而不是後綴。
Seek分割為'（sort_key，id）'而不是深'OFFSET'。
排序/join之前的設置限制（推下「LIMIT」）。

9）緩存和非正規化

應用程序級別的Query-cache（鍵=SQL+bind-vars+權限版本）。
重型單元的材料化視圖；輪換/反射計劃。
非歸一化：存儲經常可讀的可計算字段（按折扣計算的價格）,但具有用於一致性的觸發/背景任務。
Redis是「熱鍵」的L2（具有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……UPDATE HISTOGRAM` в 8.0).

10.3 ClickHouse

主鍵=排序；'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

Keyword vs文本字段；排序/聚合doc_values。
重分段：聚合-重分段；限制「大小」並使用「合成」聚合（逐頁采樣）。
不要在需要精確比較的地方打開分析儀。

11）競爭力、鎖定和MVCC

短期交易；避免在「REPEATABLE READ」下進行「長期」閱讀而無需。
索引操作也采用鎖定（通過引導減少寫入）。
計劃在線索引：「CREATE INDEX CONCURRENTLY」（PG），「ALGORITHM=INPLACE」/「ONLINE」（MySQL）。
按小時/ID插入尾巴→索引的「熱頁」；分配密鑰（UUIDv7/鹽）。

12）可觀察性和SLO

• 「db_query_latency_ms」 （P50/P95/P99）按查詢名稱排列。
• `rows_examined`, `rows_returned`, `buffer_hit_ratio`.
• `deadlocks`, `lock_wait_ms`, `temp_sort_disk_usage`.
• 「Seq Scan」計劃中預期「Index Scan」的比例。
• 更改DBMS版本/參數時出現倒退。

• 打開帶閾值的慢查詢日誌（例如200毫秒）。
• 查詢與spans的相關性（trace_id)。
• 刪除有問題的查詢計劃,並存儲在對象存儲中以進行回顧。

• 在「LIMIT<=50」和「熱」tenant下讀取P95「<=150 ms」。
• P95記錄「<=200 ms」在戰鬥中最多可達1000行。

13）安全性和多重性

訪問控制字段（「tenant_id」，「owner_id」）上的索引是強制性的。
保單（RLS/ABAC）必須是預過濾器；否則，優化程序計劃不正確。
不要以開放形式索引敏感字段；使用哈希/令牌。

14）反模式

深度為「OFFSET」，沒有seek光標替代品。
「每個所有一個索引」是內存過載和寫路徑。
關鍵路徑中的「SELECT」。
「WHERE」中列上方的函數沒有函數索引。
由於舊的統計數據，計劃不穩定。
在等待穩定順序時不存在「ORDER BY」。
索引為索引：ROI <0由於昂貴的記錄/支持。

15）實施支票

1.QPS和時間的前N查詢→選擇3-5名候選人。
2.刪除「EXPLAIN ANALYZE」計劃，檢查基數vs實際。
3.設計索引：字段順序,INCLUDE/partial/functional。
4.為大型表（臨時/影子鍵）實施分批。
5.重寫查詢：刪除「SELECT」,將簡單的CTE插頁,限制設置。
6.包括batching和seek分割。
7.配置緩存：L1/L2、事件障礙。

8.引入計劃監控和慢速日誌,Alertes回歸.

9.使用實際數據分布進行負載測試。
10.更新開發天線（ORM-hint,索引,限制）。

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：限制「page_size」，固定「sort」，使用不透明的令牌。
計劃緩存：使用參數化；不要為每個調用生成「唯一」SQL。

18）遷移和運營

在線添加索引和標記為INVISIBLE/CONCURRENTLY，測試計劃，然後切換。
索引修訂版是定期的衛生清潔：舊幻影的副本，未使用的「死亡」。
分期輪換計劃（舊分期付款）和「VACUUM/OPTIMIZE」時間表。

19）總結

查詢優化是系統工程：正確的密鑰和索引，整潔的計劃，深思熟慮的分期和散列，查詢和ORM的紀律，緩存和可觀察性。通過遵循所描述的模式，您將獲得一個快速，可預測且經濟高效的系統，可以抵抗數據和負載的增長。