覆盖索引

概念定义

普通索引先定位行，引擎再逐行回表取整行数据。覆盖索引让整个查询——过滤列、连接键、返回值——都从有序紧凑的索引结构得到，随机点查从执行计划中消失，数据库称之为 index-only scan 或覆盖计划。

工作机制

优化器发现所需列全部在索引中即选择 index-only scan。PostgreSQL 还会查询可见性映射：全页对所有事务可见的页面跳过堆检查，因此 vacuum 新鲜度直接决定加速效果。InnoDB 二级索引隐含主键，天然扩大覆盖面。

使用场景

窄列集合的重复读密集查询：计数器、存在性探测、排行榜、连接投影。与部分索引组合可只覆盖过滤后的子集，让体积与热数据切片成比例。

常见误区

覆盖不等于只匹配 WHERE：SELECT 列表同样要进索引，否则照样回表。加宽索引是有代价的——每次插入与索引列更新都变慢，缓冲池占用上升；为冷门查询堆砌边际列会放大写放大。PostgreSQL 生产中可见性映射抖动会让 EXPLAIN 里的 index-only 实际逐行回堆，需对照堆获取计数。

代码示例

-- B-tree index for equality and range queries
CREATE INDEX idx_products_category_price
    ON products (category_id, unit_price);

-- Partial index: only index active products
CREATE INDEX idx_products_active
    ON products (product_name)
    WHERE discontinued_date IS NULL;

-- Expression index for case-insensitive searches
CREATE INDEX idx_customers_email_lower
    ON customers (LOWER(email));

-- Unique index for constraint enforcement
CREATE UNIQUE INDEX idx_orders_customer_date
    ON orders (customer_id, order_date)
    WHERE status != 'cancelled';

-- Covering index (INCLUDE) for index-only scans
CREATE INDEX idx_order_items_covering
    ON order_items (order_id)
    INCLUDE (product_id, quantity, unit_price);

-- Concurrent index creation (no table locks)
CREATE INDEX CONCURRENTLY idx_products_name
    ON products (product_name);

-- BUFFERS 选项显示磁盘与缓存命中，真实成本的核心
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= '2026-10-01'
ORDER BY o.created_at DESC
LIMIT 50;

-- 检查估算偏差：预估行数与实际行数差距大时统计信息过期
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 统计信息刷新后重测
ANALYZE orders;
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 验证新索引被采用：Index Scan / Index Only Scan
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status);

EXPLAIN (ANALYZE)
SELECT id FROM orders
WHERE customer_id = 42 AND status = 'paid';