PolarDB 覆盖索引：包含 INCLUDE 列的索引创建与性能验证

1. 初识PolarDB覆盖索引

作为一名数据库工程师，我经常遇到这样的场景：查询只需要返回表中的少数几个字段，但数据库却不得不扫描整行数据。这就像你去超市只想买瓶水，却不得不推着购物车逛完整个超市一样低效。PolarDB的覆盖索引(INCLUDE索引)就是为解决这类问题而生的利器。

覆盖索引是一种特殊的索引，它不仅包含查询条件中使用的列，还包含SELECT语句中需要返回的列。当查询只需要访问索引中的列时，数据库引擎可以直接从索引中获取所需数据，而无需回表查询主表数据。这就像把常买的商品都放在超市入口处，让你不用深入超市就能快速完成购物。

在PolarDB中，我们可以通过INCLUDE子句来创建包含额外列的B树索引。这些被包含的列不会参与索引的排序，但会被存储在索引的叶子节点中。例如：

-- 创建一个包含额外列的覆盖索引
-- 技术栈：PolarDB PostgreSQL兼容版
CREATE INDEX idx_orders_user_product ON orders (user_id) 
INCLUDE (product_name, price, create_time);

这个索引以user_id作为键列，同时包含了product_name、price和create_time三个列。当查询只需要这些列时，数据库可以直接使用索引而无需访问主表。

2. INCLUDE索引的创建与使用详解

2.1 基本语法与创建示例

让我们通过一个完整的电商数据库示例来深入了解INCLUDE索引。假设我们有一个订单表，结构如下：

-- 创建订单表
CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    status VARCHAR(20) NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- 其他字段...
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 插入测试数据
INSERT INTO orders (user_id, product_id, product_name, price, quantity, status)
SELECT 
    (random()*1000)::bigint,
    (random()*100)::bigint,
    '产品-'||(i%100+1),
    (random()*1000)::numeric(10,2),
    (random()*10)::int+1,
    CASE WHEN random() > 0.5 THEN '已完成' ELSE '处理中' END
FROM generate_series(1, 100000) AS i;

现在，假设我们有一个高频查询：获取某个用户的所有订单的产品名称和价格。我们可以为这个查询创建覆盖索引：

-- 创建包含产品名称和价格的覆盖索引
CREATE INDEX idx_orders_covering ON orders (user_id) 
INCLUDE (product_name, price, create_time);

-- 分析索引使用情况
EXPLAIN ANALYZE 
SELECT product_name, price, create_time 
FROM orders 
WHERE user_id = 123;

这个查询将完全通过索引获取数据，无需访问主表，显著提高了查询效率。

2.2 多列索引与INCLUDE列的组合

INCLUDE索引可以与多列索引结合使用，满足更复杂的查询需求。例如：

-- 创建多列索引并包含额外列
CREATE INDEX idx_orders_user_status ON orders (user_id, status) 
INCLUDE (product_name, price, create_time, update_time);

-- 这个查询可以利用上面的索引
EXPLAIN ANALYZE
SELECT product_name, price, create_time
FROM orders
WHERE user_id = 123 AND status = '已完成';

在这个例子中，查询条件使用了user_id和status，而SELECT只需要product_name、price和create_time，这些都在索引中，因此查询效率很高。

3. 性能验证与对比测试

3.1 无索引情况下的查询性能

让我们先看看没有合适索引时的查询性能：

-- 删除之前创建的索引(如果存在)
DROP INDEX IF EXISTS idx_orders_covering;
DROP INDEX IF EXISTS idx_orders_user_status;

-- 执行查询并分析性能
EXPLAIN ANALYZE
SELECT product_name, price, create_time
FROM orders
WHERE user_id = 123 AND status = '已完成';

这个查询将执行全表扫描或使用不理想的索引，性能较差。

3.2 普通索引与INCLUDE索引对比

现在我们来对比普通索引和INCLUDE索引的性能差异：

-- 创建普通索引
CREATE INDEX idx_orders_user ON orders (user_id);

-- 执行查询1：只使用普通索引
EXPLAIN ANALYZE
SELECT product_name, price, create_time
FROM orders
WHERE user_id = 123;

-- 创建INCLUDE索引
CREATE INDEX idx_orders_user_include ON orders (user_id) 
INCLUDE (product_name, price, create_time);

-- 执行查询2：使用INCLUDE索引
EXPLAIN ANALYZE
SELECT product_name, price, create_time
FROM orders
WHERE user_id = 123;

通过对比执行计划，你会发现使用INCLUDE索引的查询避免了回表操作，性能显著提升。

3.3 大数据量下的性能测试

让我们在更大的数据量下测试性能差异。首先插入1000万条测试数据：

-- 插入1000万条测试数据
INSERT INTO orders (user_id, product_id, product_name, price, quantity, status)
SELECT 
    (random()*10000)::bigint,
    (random()*1000)::bigint,
    '产品-'||(i%1000+1),
    (random()*10000)::numeric(10,2),
    (random()*10)::int+1,
    CASE WHEN random() > 0.5 THEN '已完成' ELSE '处理中' END
FROM generate_series(1, 10000000) AS i;

-- 创建普通索引
CREATE INDEX idx_orders_user_large ON orders (user_id);

-- 测试普通索引查询性能
EXPLAIN ANALYZE
SELECT product_name, price, create_time
FROM orders
WHERE user_id = 1234;

-- 创建INCLUDE索引
CREATE INDEX idx_orders_user_include_large ON orders (user_id) 
INCLUDE (product_name, price, create_time);

-- 测试INCLUDE索引查询性能
EXPLAIN ANALYZE
SELECT product_name, price, create_time
FROM orders
WHERE user_id = 1234;

在大数据量下，INCLUDE索引的性能优势更加明显，查询时间可能减少50%以上。

4. 应用场景与最佳实践

4.1 适用场景

INCLUDE索引特别适合以下场景：

高频查询只涉及少量列：如用户个人中心只显示订单基本信息
宽表查询：表有很多列但查询只需要其中几列
避免回表操作：当索引本身包含查询所需的所有数据时
聚合查询：如COUNT、SUM等只需要索引列时

4.2 不适用场景

INCLUDE索引并非万能，以下情况可能不适合：

查询需要返回的列经常变化：会导致索引过大
表经常更新：INCLUDE列更新会导致索引也需更新
包含的列数据量很大：如TEXT、JSON等大字段

4.3 最佳实践建议

选择性包含列：只包含高频查询需要的列，不要贪多
监控索引使用：定期检查索引使用情况，删除无用索引
考虑写入性能：索引越多，写入性能影响越大
结合其他优化手段：如分区、物化视图等

5. 技术优缺点分析

5.1 优势

减少I/O操作：避免回表，减少磁盘访问
提高查询性能：特别是对于只需要少量列的查询
减少内存占用：不需要加载整行数据到内存
优化器友好：明确的索引覆盖提示有助于优化器选择最佳计划

5.2 局限性

增加索引大小：包含的列越多，索引越大
影响写入性能：每次插入或更新都需要维护索引
存储成本增加：需要额外的存储空间
不适用于所有查询：只有特定查询模式才能受益

6. 注意事项与常见问题

6.1 索引选择性问题

虽然INCLUDE索引能提高查询性能，但前提是索引键列有良好的选择性。如果user_id有10万个不同值，索引效果很好；但如果只有10个不同值，索引效果就会大打折扣。

6.2 索引维护成本

每次表数据更新时，相关的INCLUDE索引也需要更新。对于写密集型的应用，需要权衡查询性能提升和写入性能下降之间的关系。

6.3 索引膨胀问题

长时间运行后，索引可能会出现膨胀，导致性能下降。PolarDB提供了自动维护功能，也可以通过手动VACUUM来维护索引。

-- 手动执行VACUUM和ANALYZE
VACUUM (VERBOSE, ANALYZE) orders;

6.4 多版本并发控制(MVCC)影响

PolarDB使用MVCC实现并发控制，即使使用覆盖索引，在某些情况下仍需要访问主表来检查行可见性。这可能会影响纯索引扫描的效果。

7. 与其他技术的结合使用

7.1 与分区表结合

INCLUDE索引可以与分区表结合使用，在大型数据集上实现更好的性能：

-- 创建分区表
CREATE TABLE orders_partitioned (
    order_id BIGSERIAL,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    status VARCHAR(20) NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id, create_time)
) PARTITION BY RANGE (create_time);

-- 创建分区
CREATE TABLE orders_202301 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
    
-- 在分区上创建INCLUDE索引
CREATE INDEX idx_orders_part_user ON orders_partitioned (user_id) 
INCLUDE (product_name, price) LOCAL;

7.2 与并行查询结合

PolarDB支持并行查询，INCLUDE索引可以与并行扫描结合，进一步提高查询性能：

-- 启用并行查询
SET max_parallel_workers_per_gather = 4;

-- 执行并行索引扫描
EXPLAIN ANALYZE
SELECT product_name, price
FROM orders
WHERE user_id BETWEEN 1000 AND 2000;

8. 总结与建议

PolarDB的INCLUDE索引是一种强大的性能优化工具，特别适合以读为主的场景。通过精心设计的覆盖索引，可以显著减少I/O操作，提高查询性能。然而，索引设计需要根据具体业务需求和数据特点进行权衡。

在实际应用中，建议：

分析查询模式：识别高频查询及其所需列
循序渐进：先为最关键查询创建覆盖索引
持续监控：跟踪索引使用情况和性能变化
定期优化：根据业务变化调整索引策略

通过合理使用INCLUDE索引，你可以在PolarDB上构建出高性能的数据库应用，为用户提供更流畅的体验。

敲码拾光专注于编程技术，涵盖编程语言、代码实战案例、软件开发技巧、IT前沿技术、编程开发工具，是您提升技术能力的优质网络平台。