该技能围绕数据库索引的内部原理与工程实践展开，覆盖 PostgreSQL、MySQL 和 MongoDB。
【用途】 用于回答与数据库索引相关的问题：如何选择索引类型、如何分析查询计划、如何权衡索引带来的写入开销，以及如何诊断和优化慢查询。
【索引基础】 索引是独立的数据结构，把列值映射到行的物理位置。没有索引时数据库需做顺序扫描（全表扫描）；有索引时可直接定位。PostgreSQL 与 MySQL（InnoDB）默认使用 B-tree：一种平衡的有序树，根节点含边界键与子节点指针，内部节点逐层缩小范围，叶子节点保存索引值及指向表行的指针；叶子节点按序链接，因此范围扫描可沿叶子链遍历。一次查找从根到叶为 O(log n) 步，千万行表通常需 3-4 次页读取。查找过程包括：查询规划器判断索引可用、读取根页并沿内部页指针比较、到达含目标值与堆指针的叶子页、从堆取行（除非索引覆盖所需全部列）。
【建索引与不建索引的时机】 适合建索引的列包括：WHERE 子句中等值或范围条件的高选择性列、JOIN 条件（尤其外键列）、ORDER BY/GROUP BY 匹配排序顺序的列，以及唯一约束（隐式创建索引）。应避免在以下情况建索引：几千行以内的小表；高写入低读取的表（日志、事件流）；低基数（如布尔标志、仅 3-5 个值的状态列），但针对稀有价值的部分索引仍可能有效；查询中很少使用的列。建议在改动前后进行测量，而非凭猜测。
【索引类型】 B-tree：PostgreSQL 与 MySQL 默认，支持等值、范围、IS NULL、LIKE '前缀%' 和 ORDER BY。 Hash：仅支持等值比较；PostgreSQL 自 10 版起 hash 索引有 WAL 日志，但很少优于 B-tree；MySQL InnoDB 仅有内部自适应哈希索引。 GIN（PostgreSQL）：面向数组、JSONB、全文检索（tsvector）等含多元素的值，擅长包含查询，但比 B-tree 更大、更新更慢。示例：CREATE INDEX idx_tags ON articles USING gin(tags)。 GiST（PostgreSQL）：支持几何数据、范围类型、全文检索和最近邻查询，对某些类型是有损的（可能返回需复核的误报）。 BRIN（PostgreSQL）：为每个块范围保存最小/最大摘要，索引极小，仅在物理行顺序与列值相关时有效（如追加写表的 timestamp 列）。
【复合、部分与覆盖索引】 复合索引遵循最左前缀规则：(a, b, c) 可满足仅按 a、a 与 b、a 与 b 与 c 的过滤，但无法高效支持仅 b、仅 c 或不含 a 的 b 与 c。列顺序上应将等值列放前、范围列放后。部分索引仅包含满足条件的行，例如 CREATE INDEX idx_active_users ON users(email) WHERE active = true，可减小体积和维护成本，PostgreSQL 支持而 MySQL 不支持。覆盖索引包含查询所需全部列，可实现仅索引扫描；PostgreSQL 11+ 可用 INCLUDE 添加非搜索列，MySQL InnoDB 会自动把主键追加到每个二级索引。表达式索引针对函数或表达式结果，仅当查询匹配完全相同的表达式时才会使用。
【查询计划分析】 EXPLAIN 只显示计划，EXPLAIN ANALYZE 会实际执行并给出真实耗时与行数，建议仅在开发环境使用，绝不要对生产环境的破坏性语句使用。常见扫描类型有 Seq Scan、Index Scan、Index Only Scan、Bitmap Index Scan。关注点包括：预估行数与实际行数差距大说明统计信息过时，需运行 ANALYZE；大表上带选择性的 WHERE 却出现 Seq Scan，说明可能缺索引；高成本 Sort 节点可能由匹配 ORDER BY 的索引消除；无索引列上的嵌套循环连接会产生 O(n*m) 行为。
【索引维护】 PostgreSQL 的 UPDATE 与 DELETE 不会立即移除索引项，死元组累积会造成索引膨胀；可查询 pg_stat_user_indexes 监控大小。REINDEX 可重建索引消除膨胀，标准模式会阻塞写入，PostgreSQL 12+ 可用 REINDEX INDEX CONCURRENTLY 在线重建。可通过查询 idx_scan = 0 找出未使用索引，但在删除前需确认没有罕见但关键的查询依赖它们。
【PostgreSQL 与 MySQL 差异】 PostgreSQL 的聚集索引可选（通过 CLUSTER），二级索引指向堆元组 ctid，支持部分索引、INCLUDE 列、GIN/GiST/BRIN，Hash 索引需显式创建且有 WAL 日志，在线 DDL 用 CREATE INDEX CONCURRENTLY。MySQL InnoDB 的聚集索引始终建立在主键上，二级索引存储主键值，不支持部分索引和 INCLUDE，无 GIN/GiST/BRIN，仅有内部自适应哈希索引，在线 DDL 使用 ALTER TABLE ... ALGORITHM=INPLACE。InnoDB 中二级索引查找需要二次遍历，因此主键选择很关键，建议使用紧凑的自增整数。
【MongoDB 索引】 支持单字段与复合索引（复合同样遵循最左前缀规则，方向 1 升序、-1 降序对排序有影响）；文本索引每个集合仅一个，支持语言相关的词干与停用词；TTL 索引可按时间自动删除文档，仅作用于 Date 字段且为单字段；稀疏索引只包含字段存在的文档，partialFilterExpression（3.2+）提供更精细的控制。
【常见反模式】 给每一列都建索引；冗余索引（(a, b) 已使单独的 (a) 冗余）；未使用索引；为不存在的查询建索引（应按实测工作负载而非表结构建索引）；忽视索引对写入的影响。
【写入影响】 每次 INSERT 需一次堆写入加每个索引一次写入（B-tree 插入可能触发页分裂）；对有索引列的 UPDATE 会标记旧索引项失效并插入新项（PostgreSQL 为标记，MySQL 为移除）；DELETE 需清理该行的所有索引项。PostgreSQL 的 HOT 更新在更新列无索引且新元组同页时可避免索引更新，这也是不宜给频繁更新列建索引的理由。建议在高写入表上对加索引前后的写入吞吐做基准测试。
【慢查询诊断示例】 对 500 万行 users 表上一条耗时 4 秒的查询，示例流程为：运行 EXPLAIN ANALYZE 发现 Seq Scan 与大量被过滤行；判断缺少 status 与 created_at 上的索引；按等值列在前的原则创建复合索引（status, created_at DESC）；考虑用 INCLUDE (email) 做覆盖索引避免回表；再次用 EXPLAIN ANALYZE 验证预期变为 Index Only Scan 或 Index Scan；部署后查询 pg_stat_user_indexes 确认索引被使用且未拖累写入。
【参考链接】 PostgreSQL 索引类型：https://www.postgresql.org/docs/current/indexes-types.html Use The Index, Luke：https://use-the-index-luke.com/ MySQL InnoDB 索引：https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html MongoDB 索引：https://www.mongodb.com/docs/manual/indexes/