PostgreSQL实战指南：多领域数据库应用与实践 - 第4章：索引优化与查询性能提升

1
2
3
4
5
6
7
8

CREATE [UNIQUE] INDEX index_name
ON table_name (column_name [ASC | DESC], ...);

-- 指定索引类型
CREATE INDEX index_name
ON table_name USING index_type (column_name);

DROP INDEX index_name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- 1. 为 users 表的 email 列创建唯一索引，加速邮箱查询并强制唯一性
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- 2. 为 products 表的 product_name 列创建普通索引，加速按商品名查找
CREATE INDEX idx_products_name ON products (product_name);

-- 3. 为 orders 表的 user_id 和 order_date 列创建复合索引，加速按用户和日期查询订单
-- 注意：复合索引的列顺序很重要。如果 WHERE 子句只使用 user_id，或同时使用 user_id 和 order_date，该索引都能生效。
-- 如果只使用 order_date，则索引可能无法完全利用。
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date DESC);

-- 4. 为 order_items 表的 order_id 和 product_id 创建索引，加速订单项的联接和查找
-- (尽管我们已经有 UNIQUE(order_id, product_id) 约束，它会自动创建索引，但明确声明有助于理解)
CREATE INDEX idx_order_items_order_product ON order_items (order_id, product_id);

-- 5. 演示 GIN 索引用于 JSONB 字段 (假设 products 表有一个 details JSONB 列)
ALTER TABLE products ADD COLUMN details JSONB;
UPDATE products SET details = '{"brand": "Dell", "weight": 1.5, "specs": {"cpu": "i7", "ram": "16GB"}}' WHERE product_id = 1;
UPDATE products SET details = '{"color": "black", "layout": "US", "backlit": true}' WHERE product_id = 2;

-- 为 JSONB 字段创建一个 GIN 索引，用于高效查询 JSONB 内部的键值对
CREATE INDEX idx_products_details_gin ON products USING GIN (details);

-- 查询 JSONB 字段的示例 (需要 GIN 索引才能高效执行)
SELECT product_name, details
FROM products
WHERE details @> '{"brand": "Dell"}'; -- @> 操作符检查 JSONB 是否包含指定的键值对
SELECT product_name, details
FROM products
WHERE details ? 'color'; -- ? 操作符检查 JSONB 是否包含指定的键

-- 6. 删除一个索引
-- DROP INDEX idx_products_name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

-- 场景：查找价格低于 50.00 的所有商品，并按价格降序排列

-- 1. 无索引时的性能分析 (假设之前没有在 price 列上创建索引)
EXPLAIN ANALYZE
SELECT product_name, price
FROM products
WHERE price < 50.00
ORDER BY price DESC;

-- 预期输出 (可能类似):
-- Seq Scan on products  (cost=0.00..12.50 rows=X width=Y) (actual time=0.0XX..0.YYY rows=Z loops=1)
--   Filter: (price < 50.00)
--   Sort Method: quicksort  Memory: XXXkB

-- 分析：
--   `Seq Scan` 表示进行了全表扫描，即使只有几行数据满足条件，也要扫描整个表。
--   `Sort Method: quicksort` 表示需要额外的内存进行排序。

-- 2. 为 price 列创建索引
CREATE INDEX idx_products_price ON products (price);

-- 3. 再次分析查询
EXPLAIN ANALYZE
SELECT product_name, price
FROM products
WHERE price < 50.00
ORDER BY price DESC;

-- 预期输出 (可能类似，取决于数据量和PostgreSQL版本):
-- Index Scan using idx_products_price on products  (cost=0.XX..0.YY rows=Z width=W) (actual time=0.0AA..0.BBB rows=Z loops=1)
--   Index Cond: (price < 50.00)
-- 分析：
--   `Index Scan` 表示PostgreSQL使用了我们新创建的索引来定位满足条件的行，效率更高。
--   如果查询只涉及索引列，可能会出现 `Index Only Scan`，性能更佳。

-- 场景：查找用户 Alice 的所有订单详情，包括商品信息（多表联接）
EXPLAIN ANALYZE
SELECT
    u.username,
    o.order_id,
    o.order_date,
    o.total_amount,
    o.status,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM
    users u
JOIN
    orders o ON u.user_id = o.user_id
JOIN
    order_items oi ON o.order_id = oi.order_id
JOIN
    products p ON oi.product_id = p.product_id
WHERE
    u.username = 'alice';

-- 分析上述查询计划，关注联接类型（Nested Loop、Hash Join、Merge Join）、扫描类型。
-- 如果 `users` 表上的 `username` 没有索引，`WHERE u.username = 'alice'` 将导致全表扫描。
-- 如果 `order_items` 或 `products` 表上的联接键没有索引，也会影响性能。
-- 根据分析结果，可以考虑为 `users.username` 创建索引，为联接列创建或调整索引。