索引和优化查询

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执行计划

参见"EXPLAIN（ANALYZE，BUFFERS，VERBOSE）"；关键字段：

`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版本/参数时出现倒退。

Logi/Tracing：

打开带阈值的慢查询日志（例如200毫秒）。
查询与spans的相关性（trace_id)。
删除有问题的查询计划,并存储在对象存储中以进行回顾。

SLO示例：

在"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的纪律，缓存和可观察性。通过遵循所描述的模式，您将获得一个快速，可预测且经济高效的系统，可以抵抗数据和负载的增长。

索引和优化查询

联系我们

快速联系

视频即将更新

我们目前正忙于各项项目