BRIN、列存与组合索引

• BRIN 特性
• B-Tree 特性
• 写入 vs 查询

BRIN（Block Range Index）与 B-Tree 的取舍：BRIN 只存每个块范围（block range）的 min/max 摘要，索引极小、写入开销小（几乎不影响写入性能），适合自然有序的大表；但查询精度低，需扫描整个块范围过滤，可能读多块。B-Tree 存每个键，查询精确、精度高，但索引大、写入开销大（页分裂）。切换：数据量大、按有序列（时间）查询、写入频繁用 BRIN（快写入、小索引）；需要精确点查、高选择性用 B-Tree。

BRIN 用"小索引快写入"换"查询精度"，B-Tree 相反。有序大表用 BRIN，精确点查用 B-Tree。

• BRIN 空间效率
• min/max
• 大表

BRIN 的极致空间效率源于其结构：每个块范围（block range，默认 128 页）只存一段 min/max 摘要，不存每个键。因此索引大小与"块范围数"成正比，而非"行数"。对 TB 级大表，BRIN 索引可能只有几百 KB，远小于 B-Tree（需存所有键）。它以极小的空间开销支撑超大表，配合自然有序的数据（如时间序列），查询时通过块范围 min/max 过滤（跳过无关块），实现高效查询。空间效率是 BRIN 的最大优势。

BRIN 每块范围存 min/max，索引大小与块范围数成正比，TB 级表也能建极小索引。

• pages_per_range
• 调优
• 权衡

BRIN 索引的 pages_per_range 参数控制每个块范围包含多少页（默认 128）。调优权衡：①pages_per_range 小（如 32）：块范围更细、min/max 范围更小，过滤更精确、查询更快，但索引更大（更多块范围摘要）；②pages_per_range 大（如 256/512）：索引更小、写入开销更小，但每个块范围 min/max 范围大，过滤精度低、查询需扫更多块。调优依据：数据有序性、查询范围大小、索引大小需求。数据越有序，可增大 pages_per_range 保持精度。

pages_per_range 是"索引大小 vs 过滤精度"的权衡，按数据有序性与查询调整。

• BRIN 原理
• 块范围摘要
• 适用场景

BRIN（Block Range Index）原理：把表的物理页按 pages_per_range 分成若干块范围（block range），每个块范围记录该范围内索引列的 min/max 摘要。查询时，通过块范围 min/max 判断是否可能包含查询值，跳过不可能包含的块，减少扫描。它适合"自然排序"的数据：物理顺序与查询列顺序一致（如时间序列日志按时间追加、地理空间按空间排序），此时块范围 min/max 紧凑、过滤高效。若数据无序，块范围范围大、过滤无效，BRIN 效果差。

BRIN 依赖数据物理有序性，块范围摘要在有序数据上过滤高效。时序、地理空间是理想场景。

• PostGIS 空间索引
• R-Tree/GiST
• 最近邻/范围查询

PostGIS 用 GiST（R-Tree 思想）实现空间索引，加速空间查询：①范围查询：ST_DWithin（在 N 距离内）、ST_Intersects（相交）、ST_Within（包含）、ST_Contains 等，通过空间索引快速过滤候选；②最近邻查询：ST_Distance + ORDER BY 或 <-> 运算高效获取最近邻。空间索引用几何对象的最小边界矩形（MBR）组织，缩小搜索范围。建索引：CREATE INDEX ON t USING gist (geom);。空间索引是 PostGIS 高性能查询的关键。

GiST 空间索引用 MBR 组织，加速 PostGIS 的范围与最近邻查询。

• tsvector
• GIN
• 操作符

全文检索用 tsvector（文档的词素）表示，GIN 索引对 tsvector 建立倒排索引（词→文档列表），加速全文查询。查询操作符：@@（tsvector 匹配 tsquery）、@@@（gin 操作符）、to_tsquery（构造查询）、to_tsvector（转换文本）、plainto_tsquery 等。典型查询：WHERE to_tsvector('english', body) @@ to_tsquery('english', 'cat & dog')。GIN 索引让全文检索"词→文档"快速定位，避免全表扫描。还可结合括号分组（grouped）等高级 tsquery 语法。

tsvector 表示文档词素，GIN 倒排索引映射词到文档，@@ 匹配 tsquery 执行全文查询。

• BRIN 监控
• BRIN 日志
• 应用

BRIN 索引非常适合监控数据与日志数据：这类数据按时间追加、物理顺序与时间列一致（自然有序），且数据量大、写入频繁。BRIN 索引极小、写入开销小，可按时间列（如 created_at/timestamp）建 BRIN 索引，查询某时间段时通过块范围 min/max 过滤，跳过无关块，高效。相比 B-Tree，BRIN 在监控/日志的"写入高效 + 索引小 + 时间范围查询"场景优势明显。是监控与日志存储的常用索引。

监控/日志按时间有序、写入频繁，BRIN 的小索引快写入与时间范围过滤完美契合。

• ST_DWithin
• 空间索引
• 使用条件

ST_DWithin(geom1, geom2, distance) 判断两个几何在指定距离内是否相交，它会利用空间索引（GiST）加速：查询时数据库用索引先过滤不可能在距离内的几何（通过 MBR 距离判断），再精确计算。要使用索引，需：①几何列有 GiST 索引；②查询中 ST_DWithin 的几何参数直接使用列（而非函数包裹）；③注意投影（SRID）与距离单位一致性。ST_DWithin 是"并行迭代"的距离查询，索引能大幅减少候选集。

ST_DWithin 利用 GiST 索引先粗过滤再精确计算，需列直接参与且 SRID 一致。

• BRIN min/max
• 块范围
• 存储

BRIN 索引为每个块范围（block range）存储该范围内索引列的 min 与 max 值。查询时，若查询条件与块范围的 [min, max] 区间有交集，则该块范围可能包含匹配行，需扫描；否则跳过该块范围。min/max 是 BRIN 的核心摘要信息，让索引能"砍掉"无关块范围。min/max 存储与块范围数成正比（非行数），故索引极小。它还存储块范围的首尾块号（blkno）。min/max 在有序列上更紧致、过滤更有效。

BRIN 的 min/max 摘要用于块范围过滤，是索引极小且能加速查询的关键。

• BRIN 运维
• vacuum
• 更新

BRIN 索引的运维：BRIN 索引会随数据更新而失效，VACUUM 会更新 BRIN 索引的块范围摘要（min/max），因删除/更新会改变块范围内容。因此周期性 VACUUM 对 BRIN 索引很重要，否则摘要可能过时（min/max 不准确），过滤不精确、查询变慢。PG 的 autovacuum 会自动处理，但大量删除/更新后需手动 VACUUM 更新 BRIN。BRIN 索引维护成本低，无需像 B-Tree 那样重的碎片整理，但需 vacuum 保持摘要新鲜。

BRIN 摘要需 VACUUM 更新，否则 min/max 过时影响过滤。autovacuum 自动处理。

• ST_AsGeoJSON
• GeoJSON
• 输出格式

ST_AsGeoJSON(geom) 把 PostGIS 几何对象转换为 GeoJSON 格式（RFC 7946），用于 Web 地图（Leaflet、Mapbox、GeoServer）等前端展示。参数可指定精度、选项（如 2D/3D）。例子：ST_AsGeoJSON(ST_MakePoint(0,0)) 输出 {"type":"Point","coordinates":[0,0]}。常用于把空间数据输出为前端可消费的 GeoJSON。还有其他转换：ST_AsText、ST_AsBinary、ST_AsEWKT。ST_AsGeoJSON 是 GIS 与 Web 集成的常用函数。

ST_AsGeoJSON 把几何转 GeoJSON，前端地图可直接消费。是 PostGIS 输出格式的关键函数。

• tsvector
• GIN 索引
• 全文检索

tsvector 是 PostgreSQL 的全文检索类型，存储文档的时态词素（lexeme）。对 tsvector 建 GIN 索引（倒排索引：词→文档列表），能高效支持全文查询（@@ 匹配 tsquery）。建索引：CREATE INDEX idx ON t USING gin (to_tsvector('english', body)); 或对 tsvector 列直接建。GIN 索引让全文检索按词定位文档，避免全表扫描。tsvector 的 GIN 索引是全文检索的标准方案，比 GiST 更适合。查询用 @@ 与 to_tsquery。

GIN 对 tsvector 倒排索引"词→文档"，@@ 匹配 tsquery 是全文检索核心。

• GIN 创建
• 全文检索
• 语法

全文检索的 GIN 索引创建：对 to_tsvector 表达式或 tsvector 列建 GIN 索引。语法：CREATE INDEX idx ON t USING gin (to_tsvector('english', body));。若表已有 tsvector 列（如 body_tsv），可直接 CREATE INDEX idx ON t USING gin (body_tsv);。查询时与索引表达式一致（to_tsvector('english', body)）才能命中。可配置解析器（english 等）。GIN 是全文检索的推荐索引，查询快、支持复杂 tsquery。

全文 GIN 索引对 to_tsvector 表达式建倒排索引，查询需匹配表达式。

• 覆盖索引
• 非聚簇索引
• 避免回表

MySQL InnoDB 的覆盖索引（Covering Index）指非聚簇（二级）索引包含查询所需的所有列，使查询无需回表（回聚簇索引查数据行）。InnoDB 二级索引的叶节点包含索引键 + 主键值，若查询的列都在索引中（含主键），则直接读索引满足查询，无需回表。例如 CREATE INDEX idx ON t (a, b)，查询 SELECT a, b FROM t WHERE a = 1 全在索引中，覆盖。覆盖索引减少回表 IO，提升查询性能。

InnoDB 二级索引叶节点含索引键+主键，覆盖索引让查询免回表。SELECT 列需在索引内。

• INCLUDE 列
• B-Tree 索引
• 覆盖

PostgreSQL 的 INCLUDE 子句把附加列（非索引键列）加入 B-Tree 索引，这些列不参与排序/搜索（不作为索引键），但存储在索引页可避免回表。语法：CREATE INDEX idx ON t (a) INCLUDE (b);。查询 SELECT b FROM t WHERE a = 1 时，b 在索引中，免回表。INCLUDE 列与索引键列差异：键列参与排序与匹配，INCLUDE 列仅用于覆盖（避免回表、支持 Schema 变更）。INCLUDE 列不增加索引按键序，比把列全做键更省空间。PG 9.5+ 支持 INCLUDE。

INCLUDE 列是 PG 实现覆盖索引的机制，附加列不参与排序仅做覆盖。

• 覆盖索引
• 避免回表
• 实现

覆盖索引（Covering Index）通过把查询所需的所有列都包含在索引中，使查询直接从索引读取数据，无需回表（返回数据行）。实现：①MySQL：把 SELECT 与 WHERE 的列都加入复合索引（如 CREATE INDEX idx ON t (a, b) 覆盖 SELECT b WHERE a）；②PostgreSQL：用 INCLUDE 子句附加列（CREATE INDEX idx ON t (a) INCLUDE (b)）。覆盖索引避免回表，减少 IO，尤其适合高频查询。但会增加索引空间与写入开销。

覆盖索引让查询列都在索引内，免回表。MySQL 用复合索引，PG 用 INCLUDE。

• 部分索引
• 查询匹配
• 条件

部分索引（Partial Index）是带 WHERE 条件的索引，只索引满足条件的行。查询要用部分索引，必须满足：查询的 WHERE 条件能推导出索引定义的过滤条件，即查询条件比索引条件更严格（查询行是索引行的子集）。否则优化器无法使用部分索引。例如 CREATE INDEX ON t (a) WHERE b > 0，查询 WHERE a = 1 AND b > 0 能用，WHERE a = 1（可能含 b<=0）不能用。部分索引只对满足条件的行生效，因此查询条件必须包含索引的 WHERE 子句。

部分索引查询需满足"查询条件蕴含索引过滤条件"，否则无法使用。适合过滤高频数据。

• 部分索引语法
• WHERE 子句
• 应用

PostgreSQL 部分索引语法：CREATE INDEX index_name ON table_name (columns) WHERE condition;。只有满足 WHERE 条件的行被索引。应用：只索引活跃/热点数据（WHERE active = TRUE）、过滤大量 NULL（WHERE col IS NOT NULL）、只索引特定类型。部分索引更小、更省空间、写入开销小，查询针对子集时高效。需注意查询条件要能匹配索引的 WHERE 子句。

部分索引用 WHERE 限制索引范围，只索引子集，更小更快。

• ICP
• 覆盖索引
• 协同

MySQL 索引条件下推（ICP，Index Condition Pushdown）在索引扫描时，把 WHERE 条件中能由索引判断的部分下推到索引层，减少回表次数。覆盖索引（Covering Index）让查询免回表。协同：①ICP 减少回表（部分条件在索引层过滤）；②覆盖索引消除回表（所有列在索引中）。两者都旨在减少回表与 IO。当覆盖索引与 ICP 结合时，查询在索引层完成全部过滤与读取，无需回表，性能最佳。ICP 对非覆盖索引的辅助查询尤其有效。

ICP 把过滤下推到索引层减少回表，覆盖索引彻底免回表，二者协同优化查询。

• 覆盖索引限制
• 锁
• 列长度

MySQL 覆盖索引的限制：①覆盖索引实际上仍需要回表获取"锁"信息（在 InnoDB 中，二级索引查询最终还是要回聚簇索引确认锁/最新版本），但数据读取免回表；②覆盖索引无法覆盖所有列（如 TEXT/BLOB 大字段、函数无法直接覆盖）；③覆盖索引增加索引体积与写入开销；④只能覆盖查询列，若查询列多则复合索引可能过大；⑤覆盖索引对"全列"（SELECT *）无效。需权衡查询列与索引大小。

覆盖索引免数据回表但有限制：大字段、SELECT *、索引体积等。

• INCLUDE
• 语法
• 覆盖

PostgreSQL INCLUDE 子句语法：CREATE INDEX index_name ON table_name (index_columns) INCLUDE (extra_columns);。index_columns 是索引键列（参与排序/搜索），extra_columns 是附加列（不参与排序，仅存储用于覆盖）。示例：CREATE INDEX idx ON orders (customer_id) INCLUDE (amount, status);。INCLUDE 列可避免回表（查询包含这些列时），且支持仅覆盖索引（Index Only Scan）。PG 9.5+ 支持。INCLUDE 列有数量限制（默认 32 列）。

INCLUDE 子句附加覆盖列，不参与索引排序，实现 Index Only Scan 免回表。

• VARCHAR 列
• 索引体积
• 覆盖索引

覆盖索引包含 VARCHAR 列时，代价：①VARCHAR 列长度可变、占空间大，加入索引使索引体积增大，占用更多存储与内存（buffer pool）；②长字符串索引写入开销大；③覆盖索引的 VARCHAR 列会增加索引页数，影响扫描与缓存。实践中，若 VARCHAR 列很长（如正文字段），不适合入覆盖索引（可用 INCLUDE 或前缀索引）。权衡：覆盖 VARCHAR 列能免回表，但换取更大索引。需评估列长度与查询收益。

VARCHAR 列入覆盖索引增大索引体积与开销，长字段应避免，可用前缀索引。

• 部分索引
• OR 条件
• 兼容性

部分索引与 OR 条件的兼容性：部分索引只索引满足 WHERE 过滤条件的行。当查询含 OR 条件时，优化器需能证明整个查询都落在索引的过滤范围内（即查询条件蕴含索引条件）才能使用部分索引。若 OR 条件中有一部分不满足索引过滤条件，则无法使用该部分索引。例如索引 WHERE active = TRUE，查询 WHERE active = TRUE AND (a = 1 OR b = 2) 可用（整体满足 active），但 WHERE active = TRUE OR age > 30 不可用（age>30 行可能不满足 active）。OR 条件扩大查询范围，可能超出部分索引范围。

部分索引需查询条件蕴含索引条件，OR 条件可能扩大范围导致无法使用。

• 覆盖索引排序
• 覆盖索引分组
• ICP 分工

覆盖索引的额外收益：①排序：若索引键与 ORDER BY 一致，索引已按序存储，可免排序（filesort），直接顺序读索引返回；②分组：若索引键与 GROUP BY 一致，可按索引顺序分组，免额外排序/哈希。覆盖索引在排序/分组时因"免回表 + 索引有序"，直接按索引顺序读取，避免临时表与排序。与 ICP 分工：ICP 把 WHERE 过滤下推到索引层减少回表；覆盖索引消除回表（Index Only Scan）。两者都减少回表，覆盖索引连排序/分组也受益，ICP 主要优化过滤。协同：覆盖索引 + ICP 让查询在索引层完成过滤、排序、读取。

覆盖索引免回表 + 索引有序，加速 ORDER BY/GROUP BY；ICP 只优化过滤下推。

• MergeTree
• 列存
• 物理排序

ClickHouse 的 MergeTree 引擎是列存实现：数据按列存储（每列独立存储），物理上按排序键（ORDER BY）排序。MergeTree 把数据按排序键组织成分区（part），part 内按列存储、按排序键有序，支持高效的列裁剪、压缩与范围/前缀查询（稀疏索引跳过后缀）。写时把批量数据合并排序（merge），查询时利用列存与有序性。适合大数据的 OLAP 分析。列存 + 排序键使 ClickHouse 聚合与范围查询高效。

MergeTree 列存 + 按排序键物理排序，支持列裁剪、压缩与稀疏索引，是 ClickHouse 高性能核心。

• citus_columnar
• 列存扩展
• 应用

citus_columnar 是 PostgreSQL 的列存扩展（基于 Citus 项目），提供列式存储引擎，用于分析/OLAP 场景。应用：把表以列存方式存储，实现列裁剪、高压缩、大表聚合加速，适合数据仓库、分析报表。通过与 Citus 分布式结合，可水平扩展。列存表用于查询（读）场景，写入/更新受限（append-only 风格）。适合读多写少、分析型大表。citus_columnar 让 PG 具备列存能力。

citus_columnar 是 PG 的列存扩展，列式存储 + 压缩加速分析，适合 OLAP，不适合大量写入。

• 倒排索引
• 词到文档
• 原理

倒排索引（Inverted Index）把每个词（token）映射到包含它的文档列表，即"词 → 文档ID 列表"的映射。与正排索引（文档 → 词）相反。查询时，给定词（或词组合），直接通过倒排表定位到相关文档，无需扫描所有文档。适用于全文检索（GIN on tsvector）、JSONB、数组等多值类型。倒排索引构建时需分词、提取每个文档的键，查询时合并多个词的文档列表（AND/OR）。它大幅加速"包含/关键词"查询，但写入开销大。

倒排索引是词到文档的映射，GIN 是其实现，查询按词定位文档，适合全文/多值。

• 列存
• 行存
• OLTP/OLAP

列存与行存的取舍：行存（如 InnoDB、PG Heap）整行连续存储，适合单行点查、频繁更新（OLTP），因为一次读取一行所有列；列存（如 ClickHouse、Parquet）按列连续存储，适合大范围聚合、列裁剪、压缩（OLAP），因为查询只需读相关列。取舍：OLTP 行存高效（单行、事务），OLAP 列存高效（聚合、压缩、列裁剪）。混合场景需权衡。行存更新方便，列存压缩率与聚合效率高。

行存适点查/更新（OLTP），列存适聚合/压缩（OLAP）。按负载选择。

• 按列存储
• 压缩
• 向量化执行

列存（Columnar Storage）原理：按列而非按行存储数据，同一列的值连续存储。优势：①列裁剪：查询只读需要的列，减少 IO；②高压缩：同列数据相似度高，压缩率好（RLE、字典、Delta）；③向量化执行：按列批量处理（SIMD），聚合高效。适用 OLAP 分析：聚合、范围、大表扫描。代表：ClickHouse、Parquet、ORC、citus_columnar。代价：单行点查/更新较慢（需拆多列）。列存是 OLAP 引擎的核心。

列存按列存储 + 压缩 + 向量化，是 OLAP 高性能的关键。点查/更新不如行存。

• MinMax 索引
• Bloom Filter
• 列存辅助索引

列存引擎的辅助索引用于加速过滤：①MinMax 索引：每个数据块（page/part）存列的最小/最大值，查询时跳过不在范围内的块，类似 BRIN，用在有序/范围查询；②Bloom Filter：对列的取值构建布隆过滤器，判断块内是否可能存在某值，能快速排除不可能包含的块（适合等值查询）；③跳数索引（ClickHouse 的 skip index）。这些辅助索引在列存扫描时减少读取的数据块，加速过滤。与列存列裁剪、压缩配合。

MinMax 与 Bloom Filter 是列存的块级过滤索引，跳过无关块加速查询。

• ReplacingMergeTree
• 去重
• 应用

ReplacingMergeTree 是 ClickHouse 的 MergeTree 变体，用于数据去重：在合并（merge）时，对相同排序键（ORDER BY）的行，保留最后一条（默认按版本列或最后插入），丢弃重复行。它不是实时去重，而是在后台 merge 时去重，因此查询时可能暂时看到重复，需配合后再合并或使用 FINAL 关键字。应用：处理重复数据的场景（如重新导入、覆盖更新）。ReplacingMergeTree 适合"按主键合并去重"的日志/事件数据。

ReplacingMergeTree 在 merge 时按排序键去重，保留最后版本，适合重复数据清理。

• 索引列序
• CBO
• 评估

优化器（CBO，Cost-Based Optimizer）通过统计信息评估各索引列序的成本，选择最优执行计划。复合索引列序需要优化器试探：①列的选择性（基数）影响过滤效果；②等值列 vs 范围列的顺序；③统计信息（ANALYZE）提供列基数、分布，帮助优化器估算各行数。优化器会"试探"不同列序的索引可用性，但实际列序由 DBA 设计，优化器基于已建索引的列序评估计划。设计列序时需考虑最左前缀与查询模式，优化器据此选择。

CBO 用统计信息评估索引列序的成本。列序设计（等值在前、范围在后）影响优化器选择。

• 组合索引代价
• 多列维护
• B-Tree 深度

组合索引（复合索引）的代价：①写入开销：每次插入/更新需维护多列组成的索引键，涉及多个列的比较与排序，写入路径更长、索引页更新更多；②索引体积：多列使索引更大，占用更多存储与内存；③B-Tree 深度：多列索引键更长，但 B-Tree 深度通常仍由基数决定，深度增长有限；④维护成本：多建一个组合索引增加写入负担。权衡：组合索引提升查询但增加写入与存储成本，需按查询模式合理设计。

组合索引用写入/存储成本换查询性能。索引越多、列越多，写入开销越大。

• 组合索引排序
• ORDER BY 匹配
• 命中

组合索引 (a, b) 的排序键与 ORDER BY a, b 一致时，可命中索引免排序：索引已按 (a, b) 有序，查询 ORDER BY a, b 直接按索引顺序读取，避免 filesort。但 ORDER BY 需满足最左前缀与方向一致：ORDER BY a、ORDER BY a, b 能命中；ORDER BY b（跳过 a）或 ORDER BY a, b DESC（方向不一致）可能无法完全利用。若 WHERE 使用 a 等值，ORDER BY b 也能利用（a 等值后 b 有序）。优化器利用索引有序性避免排序。

ORDER BY 与索引列序一致（最左前缀 + 方向）才能免排序。

• 列序选择
• 高基数
• 等值在前

组合索引列序选择遵循原则：①等值条件列在前：查询中常作等值（=）过滤的列放前，因为等值后后续列可有序（用于范围/排序）；②高选择性（高基数）列在前：不同值多的列放前，能快速缩小范围；③范围条件列放后：范围（>、<、BETWEEN）列放后，因为范围后最左前缀失效。综合：等值列 > 高基数 > 范围列。列序决定查询能否命中索引与过滤效率，是组合索引设计核心。

列序原则：等值在前、高基数在前、范围在后。正确列序最大化索引效果。

• 覆盖索引
• 组合索引
• 协同

覆盖索引与组合索引协同：设计组合索引时，把 SELECT、WHERE、ORDER BY 的列都纳入索引，使索引同时满足过滤、排序与覆盖（免回表）。例如 CREATE INDEX idx ON orders (customer_id, created_at) INCLUDE (amount)，WHERE customer_id 过滤、ORDER BY created_at 排序、amount 覆盖。这样查询在索引层完成全部工作，免回表、免排序，性能最优。原则：WHERE 列（等值/范围）放索引前部，ORDER BY 列跟进，SELECT 多余列用 INCLUDE 覆盖。

组合索引 + 覆盖（INCLUDE）同时满足过滤、排序、覆盖，是索引设计最佳实践。

• 组合索引
• 聚簇存储
• InnoDB

MySQL InnoDB 的组合索引（二级复合索引）是独立于聚簇索引的结构：索引叶节点存储组合索引键 + 主键值。查询利用组合索引进行过滤，若需非索引列则回表（聚簇索引）。InnoDB 一张表只有一个聚簇索引（主键），组合索引是二级索引。组合索引的列序（最左前缀）决定可匹配的查询。二级索引查询可能回表，除非覆盖索引。聚簇存储决定数据物理顺序，组合索引是辅助结构。

InnoDB 组合索引是二级索引，叶节点存组合键+主键，查询可能回表。

• ICP
• 组合索引
• 配合

MySQL 索引条件下推（ICP）与组合索引配合：ICP 把 WHERE 条件中能由组合索引判断的部分（如范围/非前置列条件）下推到索引层，在索引扫描时过滤，减少回表。例如组合索引 (a, b)，查询 WHERE a = 1 AND b < 5，a 用索引等值，b < 5 是范围，ICP 在索引层过滤 b，减少回表到 a=1 范围内的行。若不支持 ICP，会先按 a 取索引再回表过滤 b。ICP 让组合索引的后续条件也在索引层过滤，提升性能。

ICP 把组合索引的过滤条件下推到索引层，减少回表，配合组合索引优化查询。

• 多列索引
• B-Tree
• 最左前缀

PostgreSQL 多列索引（复合索引）默认用 B-Tree，按列序联合排序（先按第一列，再按第二列...）。它遵循最左前缀原则：查询需匹配索引左起列。多列 B-Tree 支持等值、范围、排序，且因 PG 的 B-Tree 支持 NULLS FIRST/LAST、可对多列排序。PG 多列索引也可用 INCLUDE 附加覆盖列。多列 B-Tree 是 PG 最常用的复合索引，通过列序设计优化查询。

PG 多列索引默认 B-Tree，按列序排序，遵循最左前缀，支持范围/排序。

• DISTINCT
• 组合索引
• 优化

组合索引与 DISTINCT：当 DISTINCT 的列与索引列序一致（覆盖最左前缀）时，优化器可利用索引的有序性实现 DISTINCT 去重，避免排序/哈希。例如索引 (a, b)，SELECT DISTINCT a FROM t 或 SELECT DISTINCT a, b FROM t 可受益于索引有序（distinct 值相邻）。若 DISTINCT 列与索引列序不一致或跳过索引包含列，则无法利用。组合索引有序性让 DISTINCT 免排序，提升性能。

DISTINCT 列与索引列序一致时，利用索引有序免去重排序。

• GROUP BY
• 组合索引
• 优化

组合索引与 GROUP BY：当 GROUP BY 的列与索引列序一致（最左前缀）时，优化器可利用索引有序性实现分组，避免排序/哈希聚合。例如索引 (a, b)，GROUP BY a 或 GROUP BY a, b 可受益（相同键相邻，顺序分组）。若 GROUP BY 列跳过索引列或顺序不一致，则无法利用，需哈希/排序分组。组合索引有序性让 GROUP BY 免排序，提升聚合性能。

GROUP BY 列与索引列序一致时，利用索引有序顺序分组，免排序。

• NULL 前置列
• 索引失效
• 改写

组合索引中，若前置列含 NULL，且查询用 NULL 比较（= NULL 不匹配），可能导致索引失效。原因：NULL 在 SQL 中未知，WHERE col = NULL 恒为 false，无法命中索引；且 NULL 排序/索引行为特殊。改写：用 IS NOT DISTINCT FROM（PG 支持，NULL 视为相等）替代 =，或用 IS NULL / IS NOT NULL 显式条件，或建部分索引（WHERE col IS NOT NULL）过滤 NULL。这些改写让查询能利用索引。组合索引 NULL 处理需注意比较方式。

NULL 比较用 IS NULL 或 IS NOT DISTINCT FROM，避免 = NULL 失效，或用部分索引。

• OR 条件
• 组合索引
• 索引合并

组合索引与 OR 条件：OR 条件通常无法使用单个组合索引高效过滤（除非 OR 各分支都能用同一索引的某部分）。例如 WHERE a = 1 OR b = 2，组合索引 (a, b) 无法同时满足两个分支（最左前缀）。MySQL 可能用索引合并（index merge union）或全表扫描。优化：把 OR 改写为 UNION ALL、或确保 OR 分支都命中索引前缀、或建适当索引。OR 条件对组合索引是挑战，需针对性优化。

OR 条件难以用单个组合索引，可能需索引合并或 UNION 改写。

• ORDER BY
• 组合索引
• 免排序

组合索引与 ORDER BY：当 ORDER BY 的列与索引列序一致（最左前缀 + 方向一致）时，优化器利用索引有序性免排序（filesort），直接按索引顺序读取。例如索引 (a, b)，ORDER BY a、ORDER BY a, b 命中；ORDER BY a DESC, b DESC 也命中（方向一致）；ORDER BY b 或 ORDER BY a, b DESC（方向不一致）不命中。若 WHERE 用 a 等值，ORDER BY b 也可利用（a 等值后 b 有序）。组合索引免排序提升查询性能。

ORDER BY 与索引列序一致（最左前缀+方向）则免排序，否则需 filesort。

• CHECK 约束
• 索引
• 关系

CHECK 约束用于限定列的取值范围（如 CHECK (amount > 0)），它不创建索引，也不需要索引来执行。CHECK 约束在插入/更新时由数据库验证，直接检查值是否满足条件，不涉及索引查找。因此 CHECK 约束不需要索引。它也能帮助优化器（如 CHECK 约束推断范围），但本身不建索引。若需加速对满足 CHECK 条件的查询，可另建部分索引。CHECK 约束与索引是不同概念。

CHECK 约束是数据校验约束，不建索引，在写入时验证。与索引无关。

• 主键索引
• 聚簇索引
• InnoDB

InnoDB 中主键索引就是聚簇索引：数据行按主键顺序存储在聚簇索引的叶节点，主键即聚簇索引。因此主键决定了数据物理布局。如果表没有显式主键，InnoDB 会选择第一个非空唯一索引作为聚簇索引，否则隐式生成 rowid。一张 InnoDB 表只有一个聚簇索引（主键），其他索引都是二级索引（非聚簇）。主键索引与聚簇索引在 InnoDB 是合一的。

InnoDB 主键即聚簇索引，数据存于主键索引叶节点，一张表一个聚簇索引。

• 主键索引类型
• B-Tree
• 其他

主键约束的索引类型：绝大多数数据库（MySQL InnoDB、PostgreSQL）主键默认用 B-Tree（B+Tree）索引。PostgreSQL 默认 B-Tree，MySQL InnoDB 用 B+Tree（聚簇）。原因：主键需支持等值、范围、排序与唯一性，B-Tree 完美支持。Hash 索引不适合主键（不支持范围/排序），其他索引（如 GiST/GIN）也不用于主键。PostgreSQL 主键只能用 B-Tree（或支持唯一性的索引），MySQL 主键固定 B-Tree。主键索引是 B-Tree。

主键默认 B-Tree，支持等值/范围/排序/唯一。Hash 不支持范围，不适合主键。

• 外键索引
• MySQL InnoDB
• PostgreSQL

外键约束是否自动建索引：MySQL InnoDB 自动为外键列创建索引（InnoDB 会自动建外键索引以支持约束检查与级联操作）。PostgreSQL 不会自动为外键列建索引，需手动创建（否则外键约束性能差、级联操作慢）。因此 PG 中需手动 CREATE INDEX ON 外键列。差异：MySQL 自动、PG 手动。外键列索引提升约束检查与连接查询性能。

MySQL InnoDB 自动建外键索引，PG 需手动建。外键列索引提升性能。

• EXCLUSION 约束
• 应用
• GiST

PostgreSQL 的 EXCLUSION 约束（EXCLUDE）用于定义"哪些行不能共存"，是通用约束：EXCLUDE USING gist (col1 WITH =, col2 WITH &&) 表达"col1 相同且 col2 重叠的行不能存在"。应用：时间/区间不重叠（预订时间、课程时段）、IP 范围不重叠、资源唯一占用。底层用 GiST 索引实现（支持重叠 &&、包含 @> 等操作符）。EXCLUDE 约束比 CHECK 更强，能表达跨行约束，是时态/资源约束的利器。

EXCLUDE 约束用 GiST 表达"不重叠/不共存"，适合时间区间、资源占用等约束。

• 外键索引
• InnoDB
• 自动创建

MySQL InnoDB 外键索引：InnoDB 会自动为外键列创建索引（若外键列没有索引则自动建），以支持外键约束检查与级联（ON DELETE CASCADE 等）。这是 InnoDB 的强制要求：外键列必须有索引。无需手动建，但也可手动建更优的复合索引。外键索引加速外键约束检查、避免全表扫描、提升连接性能。若删除外键约束，其自动索引可能保留。InnoDB 自动管理外键索引。

InnoDB 自动为外键建索引，支持约束检查与级联，提升性能。

• EXCLUDE 约束
• GIST
• 关系

PostgreSQL 的 EXCLUDE 约束底层使用索引（通常 GiST）来实现，EXCLUDE USING gist (...) 指定用 GiST 索引。GiST 提供重叠（&&）、包含（@>）、被包含（<@）等操作符，使 EXCLUDE 能表达"不重叠/不共存"约束。EXCLUDE 约束在插入/更新时通过 GiST 索引检查是否存在冲突行。因此 EXCLUDE 约束依赖 GiST 索引（或 btree 等支持的索引）。没有 GiST 支持，EXCLUDE 无法表达区间冲突。

EXCLUDE 用 GiST 索引实现，支持重叠/包含操作符，表达区间不重叠约束。

• 外键索引
• PostgreSQL
• 手动创建

PostgreSQL 的外键约束不会自动创建索引，需手动为外键列建索引。没有索引时，外键约束检查（插入/更新时验证父表存在）与级联操作（ON DELETE CASCADE）会慢（需要扫描/全表）。因此 PostgreSQL 中应手动 CREATE INDEX ON 外键列，提升约束检查与连接查询性能。这是 PG 与 MySQL 的差异（MySQL 自动建）。建议全库外键列手动建索引。

PG 外键不自动建索引，需手动建，否则约束检查与级联慢。

• 唯一索引
• NULL
• 行为

唯一索引的 NULL 行为：标准 SQL 中 NULL 互不相等，因此唯一索引允许多个 NULL 行共存（PostgreSQL、MySQL 如此）。SQL Server 默认把 NULL 视为相同，唯一索引只允许一个 NULL（可用过滤索引规避）。PostgreSQL 还可通过 NULLS NOT DISTINCT 选项（PG 15+）让唯一索引将 NULL 视为相同（只允许一个 NULL）。唯一索引的 NULL 处理因数据库而异，设计时需注意。

唯一索引的 NULL 行为：PG/MySQL 多 NULL，SQL Server 单 NULL，PG 15+ 可配置。

• 唯一索引
• 部分唯一索引
• WHERE

唯一索引可使用 WHERE 子句创建"部分唯一索引"（Partial Unique Index）：只对满足条件的行强制唯一。例如 CREATE UNIQUE INDEX ON users (email) WHERE active = TRUE; 只对活跃用户强制 email 唯一，非活跃用户可重复。应用：对子集数据唯一、软删除（deleted_at IS NULL 时唯一）、多租户部分唯一。部分唯一索引比全表唯一灵活，且更小。PostgreSQL 与 SQLite 原生支持部分唯一索引；MySQL 8.0 不支持带 WHERE 的部分索引，需用生成列（generated column）+ 唯一索引等方式变通实现。

部分唯一索引用 WHERE 限定唯一范围，适合子集唯一与软删除场景。

• AHI 原理
• 内存 hash
• 自动构建

InnoDB 自适应哈希索引（AHI）原理：InnoDB 监控 B+Tree 索引的访问频率，对高频访问的索引页在内存中自动构建哈希索引（覆盖 B+Tree 的连续页面），使等值查询可通过哈希直接定位页，减少 B+Tree 查找与锁。AHI 是数据库自动构建的，"自适应"指针对高频访问，无需用户配置。AHI 位于 buffer pool 中，不持久化。它加速等值查询，但占用 buffer pool 内存。可用 innodb_adaptive_hash_index 控制。

AHI 是 InnoDB 自动为高频 B+Tree 页构建的内存哈希，加速等值查询，占用 buffer pool。

• AHI 等价
• PostgreSQL
• 缓存

PostgreSQL 没有 InnoDB 那样的自适应哈希索引（AHI），但通过共享缓冲（shared_buffers）缓存 B-Tree 索引页实现类似效果：高频访问的索引页缓存在共享内存中，减少磁盘 IO。PostgreSQL 的 B-Tree 索引访问本身很高效（含缓存），且没有 AHI 的"哈希别名"机制。若要哈希加速，可显式用 Hash 索引（PG 10+）。PostgreSQL 用良好的缓冲与索引实现，而非 AHI 式自动哈希。等价能力是"页面缓存 + 高效索引"。

PG 无 AHI，用 shared_buffers 页面缓存 + 高效 B-Tree 实现类似加速，也可用显式 Hash 索引。

• AHI
• B-Tree
• 协同

AHI 与 B-Tree 索引协同：B-Tree 是持久化索引（数据定位），AHI 是内存中的加速层，覆盖 B-Tree 的高频访问页。查询流程：等值查询先查 AHI 哈希（若命中），直接定位到 B-Tree 页，减少 B-Tree 下探；未命中则走 B-Tree 正常查找。AHI 是 B-Tree 的"加速缓存"，加速等值查询的 B-Tree 访问。两者协同：AHI 提升高频等值查询，B-Tree 保证完整索引能力（范围/排序）。AHI 不能替代 B-Tree。

AHI 是 B-Tree 的内存加速层，等值查询先查 AHI 减少 B-Tree 下探，范围查询仍走 B-Tree。

• 字典
• 全文检索
• 词干化/停用词

全文检索的字典（dictionary）用于规范化文本：把词转换为统一形式以便检索。包括：①停用词字典（stop words）：过滤常用停用词（the、a）；②词干化/词形还原（stemming）：把词归并到词干（如 running→run）；③同义词字典（synonym）：把同义词映射到同一词；④简单字典：对词做小写化。PostgreSQL 的全文检索用配置（如 'english'）组合字典，to_tsvector/to_tsquery 使用字典处理文本。字典影响检索质量与索引大小。

字典规范化词：停用词过滤、词干化、同义词，是全文检索质量的关键。

• INCLUDE 列
• 索引键列
• 差异

INCLUDE 列与索引键列差异：①存储占用：INCLUDE 列只存叶节点值，不参与内部节点排序，占用相对较小（仅叶节点）；索引键列存于内部节点与叶节点，参与排序，占用更大；②查询语义：键列参与搜索/排序/范围/最左前缀，INCLUDE 列只用于覆盖（避免回表），不参与搜索与排序；③功能：键列可匹配 WHERE/ORDER BY，INCLUDE 列仅免回表。因此 INCLUDE 列更省空间，不增加排序复杂度，适合"只覆盖不搜索"的列。

INCLUDE 列仅叶节点存储、不参与排序，省空间；键列参与搜索排序、占用更大。

• Run-Length
• Dictionary
• Delta

列存的高压缩得益于技术：①Run-Length Encoding（RLE，行程编码）：连续相同值压缩为"值+重复次数"，适合低基数列（如状态、性别）；②Dictionary（字典编码）：把不同值映射为短码，适合低基数/重复值列；③Delta（增量编码）：存相邻值的差值，适合单调递增列（如时间戳、ID）。这些压缩按列应用，同列数据相似度高，压缩率好。列存压缩减少存储与 IO，提升查询性能，是 OLAP 关键。

RLE、字典、Delta 针对不同数据特征压缩，列存同列相似度高，压缩率高。

• innodb_adaptive_hash_index
• 开启/关闭
• 影响

InnoDB 自适应哈希索引（AHI）由系统变量 innodb_adaptive_hash_index 控制，默认开启（ON）。开启：InnoDB 自动为高频 B+Tree 索引页构建内存哈希，加速等值查询。关闭（SET GLOBAL innodb_adaptive_hash_index = OFF）：禁用 AHI，此时等值查询走 B-Tree，减少 AHI 的 buffer pool 内存占用与维护开销。适合：等值查询少、内存紧张、或 AHI 引起锁竞争时关闭。动态调整，需重启连接生效。

innodb_adaptive_hash_index 控制 AHI，默认开启，等值少或内存紧张时可关闭。

• AHI
• Buffer Pool
• 关系

AHI 与 Buffer Pool 的关系：AHI 存储在 Buffer Pool（缓冲池）中，占用共享缓冲内存。AHI 为 B+Tree 索引页构建哈希索引，这些哈希结构驻留在 Buffer Pool 中。因此：①开启 AHI 会占用 Buffer Pool 内存（减少用于数据页缓存的空间）；②内存紧张时，AHI 可能被淘汰或考虑关闭；③Buffer Pool 大小影响 AHI 可用空间。AHI 是 Buffer Pool 中用于加速索引访问的内存结构，二者共享缓冲空间。

AHI 占用 Buffer Pool 内存，与数据页缓存共享缓冲，内存紧张需权衡。

• SRID
• 转换
• ST_Transform

SRID（Spatial Reference Identifier）标识坐标系，PostGIS 每个几何对象带 SRID。SRID 转换指把几何从一种坐标系转换到另一种：ST_Transform(geom, target_srid) 转换坐标系。常用：WGS84（EPSG:4326，经纬度）与 Web Mercator（EPSG:3857，地图显示）互转，或投影坐标系（如中国 CGCS2000、UTM）转换。距离计算需注意 SRID 单位（4326 是度，需投影或 ST_Distance 球形）。SRID 转换用于投影、显示、距离计算。

ST_Transform 转换坐标系，SRID 决定几何解释，影响距离与显示。

• 列存加载
• ETL
• 批量

列存的数据加载（ETL）特点：列存引擎（如 ClickHouse、列存表）适合批量、追加式加载，因为列存按列写、压缩，批量插入高效（写入时暂存、合并排序）。不利：单行/频繁更新慢（列存不适合行级更新）。ETL 流程：把 OLTP/源数据批量导入列存（INSERT SELECT、COPY、Kafka 流），按排序键/分区组织。加载时利用列存 append-only 特性，避免随机写。列存 ETL 高吞吐、批量，适合数据仓库。

列存适合批量追加加载，单行更新慢，ETL 用批量导入（COPY/流）写入。

• CHECK 约束
• 执行时机
• 验证

CHECK 约束在数据写入时执行验证：INSERT 或 UPDATE 时，数据库检查新值是否满足 CHECK 条件，不满足则拒绝操作（报错）。它不检查已有数据（创建时若已有数据不符，可用 NOT VALID 跳过验证，之后 VALIDATE 验证）。CHECK 约束在每行写入时立即验证，是即时约束。它对已有数据不自动验证（除非创建时验证）。适合数据完整性校验。

CHECK 在 INSERT/UPDATE 时即时验证，NOT VALID 可跳过已有数据验证。

• 唯一约束
• 唯一索引
• 区别

唯一约束（UNIQUE CONSTRAINT）与唯一索引（UNIQUE INDEX）在多数数据库（MySQL、PostgreSQL）中实现相同：都建唯一索引强制唯一性。区别：①唯一约束是约束（逻辑），唯一索引是索引（物理）；②唯一约束自动创建唯一索引，唯一索引可独立创建；③唯一约束可用于外键引用（作为候选键），唯一索引不一定；④管理上：约束用 DROP CONSTRAINT，索引用 DROP INDEX。功能上唯一约束 ≈ 唯一索引 + 约束语义。PostgreSQL 中约束与索引可相互转换。

唯一约束与唯一索引底层都是唯一索引，区别在约束语义与管理方式。