数据库索引优化
B-Tree 索引的有序查找边界
B-Tree（平衡多路查找树）的核心特性是数据有序存储。查找时从根节点开始，通过比较键值大小决定分支走向，时间复杂度为 O(log N)。这一机制的前提是查找必须知道精确的起始边界——只有确定了从哪里开始，
才能在树中定位到对应节点并向后遍历。
复合索引与左前缀原则
多列组成的索引称复合索引（composite index）。PostgreSQL 的复合主键（composite primary key） 底层就是一条多列唯一 B-Tree 索引，因此天然具备复合索引的查询能力，只是额外多了 「组合值唯一 + 各列 NOT NULL」的约束语义。B-Tree 按列声明顺序排序，类比电话簿先按姓再按名： 从最左列开始的连续前缀查询都能高效命中，跳过首列的查询（如只按第二列过滤）则用不上该索引， 这称左前缀原则（leftmost prefix）。索引列序应按最高频查询路径安排， 低频维度的查询单独补索引，而不是为它调整主键列序。
见：PostgreSQL: Multicolumn Indexes
LIKE 查询的前导通配符陷阱
LIKE 模式匹配中，通配符 % 的位置直接决定索引是否可用：
| 模式 | 是否命中索引 | 原因 |
|---|---|---|
'Mark%' | ✅ | 前缀确定，有明确的起始边界 "Mark"，B-Tree 可快速定位 |
'Mar_k' | ✅ | 前缀确定，_ 仅匹配单个字符 |
'%mark' | ❌ | 后缀匹配，前缀字符不固定，无起始边界 |
'%mark%' | ❌ | 前后均不定，无法确定起始边界 |
前导 % 导致索引失效的本质是：B-Tree 无法确定从树的哪个节点开始搜索，优化器只能退化为全表顺序扫描（O(N)），数据量增大时性能差距从毫秒级扩展到秒级甚至分钟级。
前导通配符的优化策略
前缀匹配改写（L2）：若业务允许，将 %keyword% 改写为 keyword%，利用现有 B-Tree 索引。
反向索引（L2）：针对后缀匹配 %mark，可创建反向索引。PostgreSQL 示例：
CREATE INDEX idx_name_reverse ON users (reverse(name));
WHERE reverse(name) LIKE reverse('%mark'); -- 等价于 'kram%'
全文检索（L2）：对于复杂文本搜索，使用数据库内置全文检索而非 LIKE：
-- PostgreSQL
WHERE to_tsvector('chinese', content) @@ to_tsquery('keyword');
Trigram 索引（L1）：PostgreSQL 的 pg_trgm 扩展将文本切分为三字符组合建 GIN 倒排索引，可使 LIKE '%keyword%' 命中索引：
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops);
专用搜索引擎（L3）：当搜索复杂度超出数据库索引能力时，应考虑 Elasticsearch、Meilisearch 等专用方案，而非在数据库层强行优化。
UNIQUE 约束隐式创建索引，别再重复建同键
给一列加 UNIQUE 约束时，PostgreSQL 会在背后自动创建一个同键的唯一 B-Tree 索引——约束要 enforce 「值不重复」就必须能快速判定「这个值是否已存在」，这个能力正是索引给的，所以约束与索引是一体两面。 由此，在已有 UNIQUE 的列上再显式 CREATE INDEX 是纯写放大：每次写入都要维护两棵几乎相同的索引树， 查询却毫无收益。二者的分工是：UNIQUE 既保证不重复又加速查找，普通 index 只加速不约束唯一， 选哪个取决于这列允不允许重复值。另一处易忽略：UNIQUE 列默认允许多个 NULL（PG 认为 NULL 之间 互不相等），要「必有值且互不相同」需再叠加 NOT NULL。