命名规范与 B-tree/GIN 索引

• 主键命名约定
• 一致性
• 可读性

主键命名常用方案：①id：每张表都叫 id，简单统一，但 JOIN 时需别名（如 o.id、c.id），在多表查询中易混淆；②table_id（如 order_id）：包含表名，语义清晰，JOIN 时无需猜测，但命名冗长；③pk_table：显式主键约束命名（如 pk_orders），用于约束名而非列名。推荐：列名用 id 或 表名_id 保持统一，约束名用 pk_表名。关键在于全库一致性、可读性与 JOIN 便利。业界常统一用 id + 表名_id 外键。

主键命名需全库统一，避免歧义。id 简洁，表名_id 语义清晰，约束名 pk_表名 用于约束管理。

• 外键列命名
• 约束命名
• 可读性

外键设计有两个部分：列名与约束名。列名常用 source_id（如 customer_id），表示引用目标表，语义清晰；约束名用 fk_source_target（如 fk_orders_customer），表示来源表与目标表，便于错误定位与维护。取舍：列名用 目标表_id 直观，约束名用 fk_来源_目标 显式表达关系。推荐：列名 表名_id，约束名 fk_来源_目标，全库统一。

外键列名与约束名分离：列名体现引用字段，约束名体现关联关系。规范命名便于排查外键错误。

• 索引命名
• 可读性
• 维护

索引命名常用方案：①idx_t_col（如 idx_orders_customer_id）：前缀 idx_ 表示索引，后接表名与列名，语义清晰；②t_col_idx（后缀 idx）：先表名列名后 idx。两种都表达"表+列+索引"，取舍在风格统一。推荐：idx_表名_列名 或 表名_列名_idx，全库统一。良好命名便于识别索引用途、排查冗余索引与维护。复合索引可含多列名。

索引命名体现"表+列"，便于 DBA 识别与维护。命名规范有助于发现重复索引、管理索引。

• public schema 概念
• search_path
• 命名规范

PostgreSQL 默认每个数据库有一个 public schema，是未指定 schema 时对象（表、索引等）的默认存放位置。public 模式所有人都可访问，安全性需注意。可通过 search_path 控制对象查找顺序（默认 "$user", public）。命名规范：业务表可放 public 或自定义 schema（如 app、tenant），避免多个应用共用 public 造成混乱。生产环境建议用独立 schema 隔离，public 仅作默认。

public 是 PG 默认 schema，理解它对 schema 管理、search_path 与权限控制很重要。

• 查找算法
• 插入与页分裂
• 删除与合并

B-Tree 查找：从根节点开始，根据键值比较选择子节点，逐层下探到叶节点，O(log n)。插入：定位叶节点插入，若叶节点满则触发页分裂（Page Split），把节点分成两半并把中间键上移到父节点，可能递归分裂到根。删除：定位删除，若叶节点过空（低于最小填充）则与兄弟节点合并或借键，可能递归合并，根节点外的节点需保持最小度数。B-Tree 通过分裂与合并维持平衡，保证高度与查找复杂度稳定。

页分裂与合并是 B-Tree 维持平衡的核心。分裂产生页碎片，频繁分裂（随机键）降低写入性能。

• 内部节点与叶节点
• 叶子链表
• 范围扫描

B-Tree（B+Tree）索引由内部节点（非叶）与叶节点组成：内部节点存索引键与指向子节点的指针，用于导航；叶节点存实际键值（及指向行的指针/主键值）。叶节点之间通过链表（兄弟指针）相连，形成有序链表。范围扫描（BETWEEN、>、<）时，先定位到起始叶节点，然后沿叶子链表顺序遍历，无需反复搜索树，高效获取范围内的所有键。这是 B+Tree 比 B-Tree 更适合范围扫描的原因。

叶子链表是范围扫描高效的关键。B+Tree 的叶节点存全部键且有序相连，范围扫描线性遍历。

• 等值查询
• 范围查询
• 索引扫描效率

等值查询（=）利用 B-Tree 从根到叶 O(log n) 定位，返回单行或少行，效率极高。范围查询（BETWEEN、>、<）先定位到范围起始点，再沿叶子链表顺序扫描，返回范围内所有行，效率取决于范围大小（符合条件行数）。若范围大，可能退化为全表/大范围扫描，索引优势减弱。差异：等值查询常数级定位，范围查询与命中行数成正比。优化器根据选择性选择索引扫描或全表扫描。

等值查询 O(log n) 定位，范围查询靠叶子链表且与结果集大小相关。选择性（cardinality）影响优化器判断。

• 索引统计信息
• pg_stat_user_indexes
• mysql.innodb_index_stats

索引统计信息用于优化器估计成本与选择执行计划。PostgreSQL 中 pg_stat_user_indexes 记录索引使用情况（扫描次数、返回行数、（索引大小）），可分析索引是否被使用、是否冗余。MySQL 中 mysql.innodb_index_stats 记录索引的统计（基数、页数、行数），用于优化器。此外 pg_statistic（PG）、information_schema.statistics（MySQL）存列统计。分析这些统计可发现未使用索引、评估索引有效性。

索引统计信息是查询优化与索引维护的基础。通过统计可判断索引是否被使用、是否需要重建。

• 选择性
• 基数
• 优化器决策

基数（Cardinality）是索引列不同值的数量，选择性（Selectivity）= 不同值数 / 总行数。高选择性（如主键、唯一列）意味着每个值对应少量行，索引能高效定位，优化器倾向用索引；低选择性（如性别、布尔值）意味着每个值对应大量行，索引扫描可能不如全表扫描，优化器倾向全表。统计信息（ANALYZE）更新这些基数，优化器据此决策。选择性越高的索引越有效。

选择性与基数是优化器判断索引是否有用的关键。低选择性列建索引收益低。

• Fillfactor 概念
• 页分裂
• 写入性能

Fillfactor（填充因子）控制 B-Tree 索引页的初始填充比例。默认 100%（或 90），若设为 90，则每页预留 10% 空间用于未来插入。对随机插入（如 UUID 主键），预留空间（较小 fillfactor）可减少页分裂，因为新键插入时页内有空间，降低拆分频率，提升写入性能与稳定性。但预留空间也意味着索引更大、占用更多存储。对顺序插入（单调递增），fillfactor 100%（无预留）更高效。需根据写入模式权衡。

Fillfactor 是"预留空间 vs 存储/页分裂"的权衡。随机插入用较小 fillfactor，顺序插入用大 fillfactor。

• 聚簇表
• 聚簇索引
• 物理存储顺序

InnoDB 是索引组织表（IOT，Index-Organized Table），数据行保存在聚簇索引的叶节点中，按主键顺序物理存储。每张 InnoDB 表必须有聚簇索引：①有主键则主键为聚簇索引；②无主键则选第一个非空唯一索引；③都没有则隐式生成 rowid 作聚簇键。行的物理存储顺序按聚簇键（主键）排序，因此主键顺序决定行物理位置，影响插入性能与范围扫描。

InnoDB 的聚簇表特性决定了主键的重要性。主键即聚簇索引，控制数据物理布局。

• 堆表
• IOT
• 存储差异

PostgreSQL 使用堆表（Heap Table）：数据行按插入顺序存储在堆中，物理顺序与主键无关；主键索引是独立的二级索引，索引叶节点存行指针（ctid/TID），查询时需回表（Index Scan + Heap Fetch）。Oracle 聚簇表（IOT，Index-Organized Table）把数据行存储在索引叶节点，按主键物理排序，主键查询无需回表，范围扫描高效。差异：堆表插入灵活、物理顺序自由，但二级索引需回表；IOT 主键有序、无回表，但重排/更新成本高。PG 无 IOT，靠表重组（CLUSTER）临时排序。

堆表 vs IOT 是行存储的两大范式。PG 堆表灵活、IOT 主键有序，各有取舍。

• UUID 随机性
• 聚簇索引
• 页分裂

在 InnoDB 等聚簇表中，数据按主键物理存储。UUID v4 主键完全随机，插入时新键落在页面中间位置，导致页面频繁分裂（Page Split），把已有数据移到新页，产生索引碎片、缓存命中率下降、写入性能下降。相比之下，单调递增主键（自增、雪花 ID）顺序插入，新键落在页尾，几乎不分裂。因此 UUID 主键对聚簇索引不友好，应改用 UUID v7（时间有序）或雪花 ID，或避免聚簇（用非聚簇设计）。

UUID 随机性导致随机插入 → 页分裂 → 碎片化，是聚簇索引的性能杀手。时间有序主键规避此问题。

• 最左前缀
• 复合索引列序
• 查询匹配

最左前缀原则（Leftmost Prefix）：复合索引 (a, b, c) 只能匹配以左起列开始的查询条件，即 WHERE a、WHERE a AND b、WHERE a AND b AND c 能用索引，而 WHERE b、WHERE b AND c、WHERE c 无法用索引（列序不连续）。因此复合索引的列序很重要：高选择性/等值过滤的列放前面，范围查询放后面。最左前缀决定查询能否命中索引，是复合索引设计核心。

复合索引列序决定可匹配的查询前缀。设计列序时把"等值 + 高选择性"列放前，范围列放后。

• 索引合并
• union/intersect
• 优化

索引合并（Index Merge）是 MySQL 对同一表中多个索引分别扫描后合并结果：常见方式包括 union（多个 OR 条件，每个走索引再合并去重）、intersect（多个 AND 条件，每个走索引再取交集）、sort_union。当 OR 条件无法用单个复合索引表达时，索引合并可加速。但索引合并通常比单个复合索引慢（需多索引扫描+合并），且无法用于排序/分组。取舍：能用复合索引优先用复合索引，索引合并是兜底。

Index Merge 让多个索引协同，但不如复合索引高效。OR 条件用 union，AND 用 intersect。

• 索引跳跃扫描
• MySQL 8.0
• 复合索引优化

索引跳跃扫描（Index Skip Scan）用于复合索引：当查询跳过索引最左列、从第二列开始匹配时，MySQL 8.0+ 可通过对最左列的不同值逐一探测（跳过），实现类似"跳过最左列"的索引访问。例如复合索引 (a, b)，查询 WHERE b = 1 时，若 a 基数小，可枚举 a 的不同值与 b 组合扫描，命中索引。相比全表扫描，跳跃扫描能提升性能，但仅当最左列基数小、选择性低时有效。它是 MySQL 8.0 对"最左前缀失效"场景的优化。

Skip Scan 解决"跳过最左列的查询"，但依赖最左列低基数。Oracle 早有此优化，MySQL 8.0 引入。

• 聚簇索引
• 非聚簇索引
• 行存储顺序

聚簇索引（Clustered Index）的叶节点存储实际数据行，行数据按索引键顺序物理存储（如 InnoDB 主键）。非聚簇索引（Secondary Index）的叶节点存储索引键 + 指向行的指针（InnoDB 存主键值），不包含数据行，需回表查到数据。根本差异：聚簇索引决定行物理顺序且数据在索引中，非聚簇索引是独立结构、数据在别处。非聚簇索引查询常需回表（除非覆盖索引）。

聚簇索引把数据与索引合一，非聚簇索引分离。InnoDB 一张表只有一个聚簇索引（主键），可有多个二级索引。

• 覆盖索引
• INCLUDE 子句
• USING 子句

覆盖索引（Covering Index）指索引包含查询所需的所有列，查询无需回表，直接读索引。实现：PostgreSQL 用 INCLUDE 子句把非索引键列加入索引（如 CREATE INDEX idx ON t (a) INCLUDE (b)），这些列不参与排序但可避免回表；MySQL 用普通复合索引覆盖（把查询列都加入索引）。USING 子句指定索引方法（btree/gin 等），与覆盖无关。差异：INCLUDE 列不用于排序/搜索，仅用于覆盖避免回表，比全做索引键更省空间。

INCLUDE 是 PG 实现覆盖索引的优化：附加列不参与索引排序，只做覆盖。避免回表提升查询。

• B-Tree vs B+Tree
• 叶子链表
• 内部节点存储

B-Tree 与 B+Tree 的核心差异：①B-Tree 的每个节点（含内部节点）都存键与数据，B+Tree 只在叶节点存数据，内部节点只存键与指针；②B+Tree 的叶节点通过链表相连，形成有序链表，范围扫描高效；③B+Tree 内部节点不存数据，可容纳更多键，树更矮、磁盘 IO 更少。数据库（MySQL InnoDB、PG）实际用的是 B+Tree/b-tree 变体，叶节点有序链表支撑范围扫描。差异重点是"数据存储位置与叶子链表"。

B+Tree 是 B-Tree 的优化：数据只在叶节点 + 叶子链表，范围扫描与磁盘效率更高。

• OLTP 场景
• OLAP 场景
• 取舍

B-Tree 在 OLTP 场景表现好：OLTP 以点查、小范围查询、单行更新为主，B-Tree 的 O(log n) 定位、有序结构非常适合，且写入频繁但每次量小，B-Tree 动态平衡。在 OLAP 场景 B-Tree 不理想：OLAP 以全表扫描、范围聚合、列裁剪为主，B-Tree 按列存储、顺序扫描、大范围读取效率低，更适合列存/倒排等引擎。取舍：OLTP 用 B-Tree（行存），OLAP 用列存/其他索引。B-Tree 是 OLTP 的默认选择。

B-Tree 适合点查、小范围、频繁更新的 OLTP；OLAP 的大范围聚合用列存更优。

• Latch 与 Lock
• 并发控制
• 协同

B-Tree 并发控制涉及 Latch（闩锁）与 Lock（锁）：Latch 是短时、数据库内部的轻量锁（保护页/内存数据结构），用于 B-Tree 索引页的并发访问，如页分裂/合并时的独占；Lock 是长时、事务级锁（保护逻辑数据，如表/行），与事务绑定。二者协同：Latch 保护索引页的物理结构（短临界区），Lock 保护逻辑数据一致性（事务范围）。B-Tree 并发写（如页分裂）用 Latch 串行化，读写并发用 Latch 的共享/独占模式，配合 Lock 保证事务隔离。

Latch 是物理/内存级锁，Lock 是逻辑/事务级锁。B-Tree 页操作用 Latch 保证并发安全。

• LIKE 前缀匹配
• 范围扫描
• 索引利用

B-Tree 索引按字典序（collation）排序存储键。LIKE 'abc%' 匹配所有以 'abc' 开头的字符串，等价于一个范围查询 [abc, abd)，即从 'abc' 到 'abd' 之间的所有键。B-Tree 可定位到 'abc' 起点，沿叶子链表扫描到 'abd' 之前，命中索引。因此前缀 LIKE（不以 % 开头）能走索引。而 LIKE '%abc'（后缀/中间匹配）无法走索引，因为起点无法确定，需全表扫描。前缀匹配的本质是范围查询。

前缀 LIKE 转为范围扫描，故走索引；后缀/通配符开头无法定位起点，不走索引。

• NULL 索引
• 唯一约束多 NULL
• PG 行为

B-Tree 索引对 NULL 的处理：PostgreSQL 的 B-Tree 默认把 NULL 视为大于所有值，ASC 排序默认排最后（NULLS LAST），且会索引 NULL 值（除非索引列是唯一/主键）。对于唯一约束，PostgreSQL 遵循标准 SQL：NULL 互不相等，因此唯一索引允许多个 NULL 共存（不冲突）。MySQL 也类似允许多个 NULL。这与"NULL 视为未知"的语义一致。注意：PG 的 NULLS FIRST/LAST 可控制 NULL 排序位置。NULL 处理影响查询与唯一性。

PG 唯一索引中多个 NULL 可共存，因 NULL 互不相等。这与 SQL Server 单 NULL 不同。

• 表达式索引
• 函数索引
• 查询匹配

函数索引（Expression Index）对表达式的结果建索引，而非原始列。PostgreSQL 语法：CREATE INDEX idx ON t (LOWER(name));，索引键是 LOWER(name) 的值。查询时 WHERE 必须与索引表达式完全一致（如 WHERE LOWER(name) = 'alice'）才能命中索引。这解决了"函数包裹列导致普通索引失效"的问题。MySQL 也支持函数索引（8.0+）。函数索引需 IMMUTABLE 函数，且增加写入开销。

表达式索引让"对列应用函数"的查询走索引，但 WHERE 表达式必须与索引表达式完全匹配。

• 索引碎片
• 检测
• 重建

索引碎片源于频繁更新/删除、随机插入导致页分裂与空间浪费，表现为索引占用空间大于实际、扫描变慢。检测：PostgreSQL 用 pg_stat_user_indexes 的 idx_scan、pgstattuple 扩展（统计死元组、碎片），MySQL 用 information_schema.tables 的 data_free 或 SHOW TABLE STATUS。重建：PostgreSQL 用 REINDEX INDEX/REINDEX TABLE，MySQL 用 ALTER TABLE ... ENGINE=InnoDB 或 OPTIMIZE TABLE。重建可回收碎片、提升性能，但需注意锁与停机。

碎片检测看索引空间与利用率，重建（REINDEX/OPTIMIZE）回收碎片。需在低峰期执行。

• ALTER TABLE 重建
• 索引重建
• 优化

MySQL 的 ALTER TABLE ... ENGINE=InnoDB 会把表重建为 InnoDB，过程中会重建表的索引（包括主键与二级索引），从而回收碎片、整理存储。它常用于零碎整理（类似 OPTIMIZE TABLE）。但重建表会复制全表数据、重建索引，耗时且占用临时空间，需在低峰期执行。它也会重建聚簇索引，优化数据物理布局。注意：This is not a no-op，会实际重建。

ALTER TABLE ... ENGINE=InnoDB 是 MySQL 重建表与索引的常用手段，等价于 OPTIMIZE 的近似效果。

• REINDEX 语法
• 重建对象
• 并发重建

PostgreSQL REINDEX 语法：REINDEX { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name; 如 REINDEX INDEX idx_orders 重建单个索引，REINDEX TABLE orders 重建表的所有索引，REINDEX SCHEMA/DATABASE 重建整个 schema/数据库的索引。REINDEX 默认会锁表（阻塞写），但 PG 12+ 支持 REINDEX INDEX CONCURRENTLY 在线重建（不阻塞读写）。重建用于回收碎片、修复损坏索引。

REINDEX 支持多种粒度，CONCURRENTLY 实现在线重建避免阻塞。是索引维护的常用命令。

• Hash 索引原理
• 桶
• 等值 vs 范围

Hash 索引通过哈希函数把索引键映射到哈希桶（Bucket），等值查询（=）时对键计算哈希，直接定位到桶，理想情况下 O(1) 定位，无需像 B-Tree 那样从根下探。但哈希索引不支持范围查询（>、<、BETWEEN）与排序，因为哈希打乱了键的有序性，无法按序扫描。且哈希冲突需处理（链表/开放寻址）。Hash 索引适合纯等值查询、键值随机的场景，空间利用率与等值速度好，但范围与排序能力弱。

Hash 索引 O(1) 等值但无序，不支持范围/排序。B-Tree 支持范围但等值 O(log n)。按需选择。

• InnoDB 无显式 Hash
• AHI
• 实现

InnoDB 不支持用户显式创建 Hash 索引（仅支持 B+Tree），但提供了自适应哈希索引（AHI，Adaptive Hash Index）：InnoDB 根据索引访问的频率，自动在内存为高频 B-Tree 索引页构建哈希索引，加速等值查询（减少 B-Tree 下探）。AHI 由 InnoDB 自动维护，无需用户干预，仅内存中（不持久化）。它是"自适应"的：只对频繁访问的索引构建。可通过 innodb_adaptive_hash_index 参数控制。AHI 提升等值查询性能，但占用 buffer pool。

AHI 是 InnoDB 的自动内存哈希优化，针对高频索引页。用户无法显式建 Hash 索引。

• Hash 索引历史
• WAL 问题
• 崩溃恢复

PostgreSQL 的 Hash 索引在早期版本（< PG 10）被标记为"实验性/不可用"，主要原因是：旧版 Hash 索引不记录 WAL（预写日志），崩溃后无法恢复，可能导致索引损坏/不一致；且不支持复制与部分操作。PG 10 重写了 Hash 索引，加入完整 WAL 记录、崩溃恢复支持、与复制兼容，使 Hash 索引变得可靠可用。PG 10 之后 Hash 索引可用于生产。理解历史有助于认识 Hash 索引的演进。

早期 Hash 索引无 WAL 导致崩溃无法恢复，故标记实验性。PG 10 重写修复。

• 表达式索引执行计划
• 精确匹配
• 优化器

表达式索引能否被优化器使用，取决于 WHERE 子句是否与索引表达式"精确匹配"。PostgreSQL 中，索引 (LOWER(name)) 只有 WHERE LOWER(name) = 'alice' 会走索引，而 WHERE LOWER(name) = LOWER('alice') 或不同写法可能不命中（优化器需能识别等价表达式）。优化器通过表达式的等价性判断是否能用表达式索引。用 EXPLAIN 可查看是否走索引。若不匹配，则全表扫描。

表达式索引的执行计划依赖 WHERE 与索引表达式的精确匹配。写法需与索引表达式一致。

• MEMORY 引擎
• HASH 索引
• 应用

MySQL 的 MEMORY 引擎（内存表）支持 HASH 索引与 BTREE 索引。HASH 索引适合等值查询（=），速度快（O(1)）；但 MEMORY 引擎不支持范围查询用 HASH 索引（需 BTREE），且数据存内存、重启丢失，表大小受 max_heap_table_size 限制。应用：适合临时表、缓存、查询中间结果等对等值查询要求高、数据量小的场景。MEMORY 表常配合 HASH 索引用作内存缓存。

MEMORY 引擎的 HASH 索引适合等值查询的内存快速访问，但受内存限制与持久性限制。

• Hash vs B-Tree
• 性能对比
• 场景

PostgreSQL 中 Hash 索引与 B-Tree 索引的性能对比：等值查询（=）时，Hash 索引 O(1) 定位，理论上比 B-Tree 的 O(log n) 快，尤其键值大、随机分布时 Hash 索引更紧凑；但实际差异取决于缓存、数据分布。B-Tree 支持范围、排序、聚簇，Hash 仅等值。基准测试通常显示：等值查询 Hash 略快或相当，B-Tree 功能更全。选择：纯等值查询且可接受无范围场景用 Hash，通用场景用 B-Tree。

Hash 等值略优但功能受限，B-Tree 通用。实际场景多选 B-Tree，除非纯等值大表。

• 函数稳定性
• IMMUTABLE
• 表达式索引

PostgreSQL 表达式索引要求索引使用的函数是 IMMUTABLE（不可变）的，即给定相同输入永远返回相同结果，才能用于索引。STABLE（如 now()）或 VOLATILE（如 random()）函数不能用于表达式索引，因为其值不稳定，索引无法一致地定位。IMMUTABLE 函数（如 LOWER、abs）结果确定，可安全建索引。这是建表达式索引的前提。若函数不稳定，需用 IMMUTABLE 的自定义函数或生成列。

表达式索引依赖 IMMUTABLE 函数保证索引键确定性。STABLE/VOLATILE 函数不能建索引。

• 复合表达式
• 字符串连接
• 表达式索引

CREATE INDEX ON t ((col1 || col2)) 创建基于表达式 (col1 || col2) 的索引，即对两列拼接后的结果建索引。适用于查询 WHERE col1 || col2 = '...' 或前缀匹配场景。注意表达式需用双重括号 (()) 包裹。该索引键是拼接字符串，查询需与表达式一致（如 WHERE col1 || col2 = 'ab'）才能命中。若 col1/col2 是不同类型，需注意类型转换。这是复合表达式索引的例子。

复合表达式索引对拼接结果建索引，需查询匹配表达式。用于组合列检索。

• 哈希复杂度
• O(1)
• 冲突

Hash 索引的 O(1) 复杂度源于哈希函数：对键计算哈希，直接索引到桶地址，无需逐层比较（像 B-Tree）。理想情况下，等值查询的平均访问时间是常数 O(1)，与数据量无关。但需注意：①哈希冲突（多个键映射到同一桶）时需处理（链表/探测），冲突多时退化为线性；②哈希索引整体访问 O(1)，但实际涉及哈希计算、桶访问、冲突处理。验证可通过基准测试：数据量增大时等值查询时间基本不变，体现 O(1)。

O(1) 是理想哈希的平均复杂度，冲突会退化。B-Tree 的 O(log n) 与数据量相关，Hash 更高效于等值。

• MEMORY 引擎
• BTREE vs HASH
• 取舍

MySQL MEMORY 引擎支持 BTREE 与 HASH 索引。HASH：等值查询 O(1) 快速，但不支持范围查询与排序；BTREE：支持范围查询、排序、前缀 LIKE，等值 O(log n)。取舍：若查询全是等值（如按邮箱查用户），用 HASH 更快；若需范围、排序、前缀匹配，用 BTREE。MEMORY 表数据在内存、适合临时/缓存，索引选择取决于查询模式。通常混合场景用 BTREE 更通用。

MEMORY 引擎的 HASH 适合纯等值，BTREE 支持范围/排序，按查询模式选择。

• AHI 开启
• 参数
• 条件

MySQL 自适应哈希索引（AHI）由参数 innodb_adaptive_hash_index 控制，默认开启（ON）。开启后 InnoDB 自动为频繁访问的 B+Tree 索引页构建内存哈希索引。开启条件：①innodb_adaptive_hash_index = ON；②有足够 buffer pool（AHI 占用 buffer pool 内存）；③有高频等值查询的索引页。若等值查询少、内存紧张，可关闭减少额外内存开销。AHI 是自动的，无需手动建。

AHI 默认开启，为高频等值查询自动构建内存哈希，可通过参数控制。

• 早期 Hash 限制
• WAL
• 不可用

PostgreSQL 10 之前 Hash 索引存在严重限制：①不记录 WAL（预写日志），崩溃后无法恢复，索引可能损坏；②不支持复制（流复制/逻辑复制）；③部分操作（如并集、部分操作）不可用；④索引被标记为"实验性"（不可用），官方不推荐使用。因此多数场景甚至无 Hash 索引。PG 10 重写 Hash 索引，加入 WAL 记录、崩溃恢复与复制支持，使其可用于生产。理解早期限制有助于认识版本演进。

早期 Hash 无 WAL 导致崩溃不可恢复，是最大限制。PG 10 重写修复。

• USING HASH
• 语法
• 适用

PostgreSQL 创建 Hash 索引语法：CREATE INDEX index_name ON table_name USING HASH (column);。它显式指定索引方法为 hash。PG 10+ 支持可靠 Hash 索引。Hash 索引适合等值查询（=），但不支持范围/排序。也可用于唯一哈希索引（CREATE UNIQUE INDEX ... USING HASH）。注意：空值、表达式等有特殊处理。Hash 索引用于纯等值查询场景。

USING HASH 显式创建 Hash 索引，PG 10+ 可用。适合等值查询。

• 表达式索引 OLAP
• date_trunc/lower
• 写入放大

表达式索引在 OLAP 中常用：对 date_trunc(created_at) 或 lower(name) 等函数化列建索引，加速按时间分组（GROUP BY date_trunc）或大小写不敏感过滤。代价：表达式索引在写入时需计算并维护索引项，增加写入放大（写放大）与存储；且查询需匹配表达式。选型：①表达式索引：简单，但写放大、查询需匹配表达式；②生成列（GENERATED ALWAYS AS）：把函数值存为列，数据库维护，可建普通索引，查询直观；③物化列/物化视图：预计算聚合。权衡写入频率与查询性能。

表达式索引、生成列、物化列都是"函数化查询加速"方案，需权衡写入放大与查询便利。

• BRIN
• GiST
• GIN

PostgreSQL 各索引方法取舍：①B-Tree：通用，等值/范围/排序，空间与写入适中；②BRIN：块范围索引，极小、写入快，适合自然排序大表（时间序列），但查询精度低（需扫整个块范围）；③GiST：广义搜索树，适合空间（PostGIS）、范围类型，支持复杂查询；④SP-GiST：空间分区 GiST，适合 IP 范围、trie 等不相交分区数据；⑤GIN：倒排索引，适合多值类型（数组、JSONB、tsvector），写入开销大（每个键一项）、空间大但查询快。取舍：数据有序+大表用 BRIN，空间/范围用 GiST/SP-GiST，多值/全文用 GIN，通用用 B-Tree。

索引方法选择取决于数据类型与查询模式。BRIN 省空间、GIN 适合多值、GiST 适合空间/范围。

• fastupdate
• pending list
• cleanup

GIN 索引可开启 fastupdate（默认开启）：新插入的键先暂存到内存中的 pending list（待处理列表），而非立即更新主索引，减少写入开销、提升写入性能。待 pending list 达到阈值（gin_pending_list_limit）或触发 cleanup 时，把 pending list 合并进主 GIN 索引。清理（cleanup）可通过 gin_clean_pending_list() 或 VACUUM 触发。fastupdate 提升写入，但查询时需同时查 pending list（略慢），且崩溃时 pending list 需重放。取舍：写多读少可开 fastupdate，读多可关。

fastupdate 延迟 GIN 键更新，提升写入但查询需合并 pending list。cleanup 定期合并。

• jsonb_path_ops
• jsonb_ops
• 差异

PostgreSQL 中 JSONB 的 GIN 索引有两种操作符类：①jsonb_ops（默认）：为每个键值建立索引项，支持 @>、?、?|、?& 等所有操作符，索引较大、功能全面；②jsonb_path_ops：只对路径（如 $.a.b）建立哈希索引项，不支持 ? 等键存在操作符，但更小、更快（尤其 @> 包含查询）。差异：jsonb_path_ops 体积小、@> 查询快，但功能受限（无 ?/?|/?&）；jsonb_ops 功能全但大。选择：只做 @> 包含查询用 jsonb_path_ops，需要键存在查询用 jsonb_ops。

两种操作符类在"功能 vs 体积/速度"上取舍。jsonb_path_ops 更快更小但少操作符。

• GIN 写入开销
• 键索引项
• 写入放大

GIN 索引的写入开销大：GIN 是倒排索引，对每个键（如 JSONB 的每个键、数组的每个元素）都建立单独的索引项，因此一行数据的多个键会生成多个索引项，写入时需更新多个索引项，产生写入放大（Write Amplification）。对于每个行含大量键的 JSONB/数组，GIN 写入开销显著。缓解：fastupdate 延迟写入（pending list）、合理控制键数量。GIN 查询快但写入贵，适合"写少读多"的多值数据场景。

GIN 每键索引项导致写入放大，适合多值/全文的读多写少场景。

• GIN 原理
• 倒排索引
• 多值类型

GIN（Generalized Inverted Index）是倒排索引：把每个值（键）映射到包含它的行（列表），即"键→行集合"的映射。适合多值类型（数组、JSONB、tsvector、全文检索），因为一行可包含多个键，查询时通过键定位到所有包含它的行。GIN 擅长"包含/包含于"查询（如数组 @>、JSONB @>、全文 to_tsquery）。查询快（直接定位键），但写入放大、空间大。它是全文检索与多值查询的标准索引。

GIN 倒排结构"键→行"，适合多值类型。查询定位键，写入每键一项。

• GiST 空间索引
• R-Tree
• PostGIS

GiST（Generalized Search Tree）是通用搜索树，PostGIS 用它实现空间索引（R-Tree 思想）：把空间对象（点、线、面）按其最小边界矩形（MBR）组织，空间上的相邻对象在树中相近，支持空间查询（相交、包含、最近邻）高效过滤。GiST 索引用于 ST_DWithin、ST_Intersects、ST_Within 等空间运算，加速空间查询。R-Tree 是 GiST 在空间场景的典型实现，通过 MBR 分层来缩小搜索范围。

GiST 是 PostGIS 空间索引的基础，R-Tree 用 MBR 组织空间对象，加速空间过滤。

• GiST 应用
• 空间数据
• 范围类型

GiST（Generalized Search Tree）是通用搜索树，适用于多种数据类型：①空间数据：PostGIS 的地理几何（R-Tree），支持相交、包含、最近邻；②范围类型（range）：行范围（如日期范围）的重叠、包含查询；③全文检索：可支持全文（虽 GIN 更常用）；④自定义类型：可扩展。GiST 通过自定义的"一致性/距离"函数支持相似搜索、最近邻。它灵活但开销大于 B-Tree。按需选择。

GiST 是通用可扩展的搜索树，适合空间、范围、相似等复杂查询。

• SP-GiST
• IP 范围
• trie

SP-GiST（Space-Partitioned GiST）是"空间分区"的搜索树，把搜索空间划分为不相交的子区域，适合数据本身适合分区的类型：①IP 范围（inet）：按 IP 前缀分区，支持 IP 常包含/包含于查询；②trie（前缀树）：按字符串前缀分区，适合文本前缀搜索；③点数据、电信等。它与 GiST 区别：GiST 分区可重叠，SP-GiST 分区不相交，适合"冲突少、可划分"的数据。SP-GiST 更紧凑、查询快，但适用范围有限。

SP-GiST 用不相交分区，适合 IP、trie、点数据等可划分类型。

• 范围类型
• GiST 索引
• 重叠查询

GiST 索引支持范围类型（range，如 int4range、tsrange、daterange）的查询，尤其重叠（&&）、包含（@>）、被包含（<@）等范围运算。范围查询用 GiST 索引可快速定位相关范围，避免全表扫描。典型应用：预订时间不重叠（EXCLUDE 约束 + GiST）、区间重叠判断。建索引：CREATE INDEX idx ON t USING gist (period);。GiST 对范围类型是标准索引方法。

GiST 支持范围类型的重叠/包含查询，配合 EXCLUDE 约束实现区间约束。

• ltree
• SP-GiST
• 树标签

ltree 是 PostgreSQL 的树状标签类型（如 'Top.Science.Astronomy'），用于表示层级标签路径。SP-GiST 索引支持 ltree 的分层查询（如祖先、后代、级联），通过前缀/路径分区加速。典型查询：WHERE path <@ 'Top.Science'（后代）、path @> 'Top.Science.Astronomy'（祖先）、path ~ '*.Astronomy'（模式匹配）。SP-GiST 对 ltree 的路径前缀分区高效，比扫描整表快。适用于分类树、标签层级场景。

SP-GiST 按 ltree 路径前缀分区，加速层级标签查询。

• JSONB GIN
• 操作符
• 查询

GIN 索引在 JSONB 上加速半结构化查询：支持 @>（包含）、<@（被包含）、?（键存在）、?|（任一键存在）、?&（全部键存在）等操作符。建索引：CREATE INDEX idx ON t USING gin (attrs);，可用 jsonb_path_ops 或 jsonb_ops 操作符类。查询如 WHERE attrs @> '{"color": "red"}' 或 WHERE attrs ? 'color' 利用 GIN 倒排索引快速定位。GIN 是 JSONB 查询的标准索引，适合属性多、动态查询场景。

GIN 对 JSONB 的键值建立倒排索引，加速包含/键存在查询。

• fastupdate 参数
• 建索引选项
• 影响

fastupdate 是 GIN 索引的存储参数，控制是否延迟更新：开启（默认 on）时，新插入的键暂存到 pending list，批量合并进主索引，提升写入性能；关闭（off）时，每次插入立即更新主索引。建索引时用 WITH (fastupdate = on/off) 设置。fastupdate = on 提升写入但查询需额外查 pending list，且崩溃时 pending list 需重放。可结合 gin_pending_list_limit 控制 pending list 大小。取舍：写多读少开 on，读多写少关 off。

fastupdate 是 GIN 写入性能与查询/一致性的权衡参数。

• GiST 原理
• GIN 原理
• 差异

GiST 与 GIN 都是 PostgreSQL 的通用索引，但差异：①结构：GiST 是平衡搜索树（泛化 B-Tree），GIN 是倒排索引（键→行列表）；②适用：GiST 适合空间数据、范围类型、最近邻与相似搜索；GIN 适合多值类型（数组、JSONB、tsvector）与包含/全文查询；③性能：GIN 写入放大（每键一项）但查询快，GiST 写入相对均衡、查询因复杂类型略慢；④场景：空间/范围用 GiST，多值/全文用 GIN。选择取决于数据类型。

GiST 是搜索树（空间/范围），GIN 是倒排（多值/全文）。按数据类型选择。

• GIN 索引语法
• USING gin
• 操作符类

PostgreSQL 创建 GIN 索引：CREATE INDEX index_name ON table_name USING GIN (column);。对数组列、JSONB 列、tsvector 列等均可。可指定操作符类（如 attrs jsonb_path_ops）与存储参数（WITH (fastupdate = ...)）。示例：CREATE INDEX idx_tags ON t USING gin (tags);、CREATE INDEX idx_attrs ON t USING gin (attrs);、CREATE INDEX idx_fts ON t USING gin (to_tsvector('english', body));。GIN 索引用于多值类型与全文检索。

USING GIN 创建倒排索引，针对数组/JSONB/tsvector 等多值列。

• 选择依据
• 数据类型
• 查询模式

选择 GIN 还是 GiST 取决于数据类型与查询：①多值类型（数组、JSONB、tsvector）与包含/全文查询用 GIN（倒排，查询快）；②空间数据（PostGIS）、范围类型、最近邻/相似搜索用 GiST（搜索树）；③GIN 写入放大、空间大，GiST 写入相对均衡；④若数据写入频繁，GIN 需考虑 fastupdate。原则：按列类型匹配索引方法，多值/全文→GIN，空间/范围→GiST。

选择核心是"数据类型匹配"：多值/全文用 GIN，空间/范围用 GiST。

• gin_clean_pending_list
• pending list 清理
• 函数

PostgreSQL 的 gin_clean_pending_list(index) 函数用于清理 GIN 索引的 pending list（待处理列表）：把 fastupdate 模式下暂存在内存/待处理列表中的键合并进主 GIN 索引，并返回清理的页数。该函数可手动触发，VACUUM 也会自动清理 pending list。当 fastupdate 开启且 pending list 较大影响查询时，可调用此函数合并。它用于维护 GIN 索引，减少查询时合并 pending list 的开销。

gin_clean_pending_list 手动合并 GIN 的 pending list，优化查询性能。

• snake_case
• camelCase
• SQL 惯例

SQL 业界惯例普遍使用下划线命名（snake_case）：表名、列名用全小写 + 下划线（如 user_profile、created_at）。原因：①SQL 大小写不敏感（未加引号时），全小写避免大小写混淆；②snake_case 在 PostgreSQL 等数据库中被转换为小写，规范；③跨平台、跨语言一致。camelCase 在某些 ORM（如 Java 命名）中常见，但需在 SQL 中加引号（PG 中 "userId"）保持大小写，易乱。业界推荐 snake_case，ORM 层可映射。

snake_case 是 SQL 惯例，避免大小写歧义。camelCase 需引号，易出错。

• 约束命名
• 前缀
• 可读性

约束命名采用前缀约定提升可读性：pk_（主键）、fk_（外键）、uk_（唯一键）、chk_（检查约束）、idx_（索引）。如 pk_orders、fk_orders_customer、uk_users_email、chk_orders_amount。这种命名让 DBA 从名字一眼识别约束类型与作用对象，便于错误定位（如违反约束时看到 fk_orders_customer 知是外键）、约束管理与维护。可读性高、规范统一。推荐约束加类型前缀。

约束命名前缀（pk/fk/uk/chk）提升可读性与可维护性，便于定位错误。

• 表名复数
• 表名单数
• 惯例

表名复数 vs 单数没有绝对标准，是团队约定。观点：①复数（orders）：把表视为"行集合"，语义更直观（表里有多个订单）；②单数（order）：把表视为"实体类型"，更接近对象模型、ORM 友好。业界主流：Rails/Django 等 ORM 默认复数表名，Java/Hibernate 传统多用单数。关键是一致性：全库统一。命名风格（复数/单数）影响 JOIN、查询与 ORM 映射，需团队约定并保持一致。

复数与单数没有正确与否之分，是团队风格约定：复数强调行集合语义，单数更贴近对象模型；关键不在选哪种，而在于全库统一并与 ORM 默认行为对齐，避免 JOIN、查询与映射混乱。