PostgreSQL教程

6.1 索引概述

索引的作用

索引是数据库中用于提高查询性能的重要工具：

• 加速查询：通过索引可以快速定位数据，避免全表扫描
• 加速排序：索引可以加速ORDER BY操作
• 加速连接：索引可以加速表之间的连接操作
• 唯一性约束：唯一索引可以保证数据的唯一性

索引的代价

虽然索引能提高查询性能，但也会带来一些代价：

• 存储空间：索引需要额外的存储空间
• 维护成本：INSERT、UPDATE、DELETE操作需要维护索引
• 选择性：不合适的索引可能不会提高性能，反而降低性能

6.2 索引类型

B-tree索引

B-tree是PostgreSQL的默认索引类型，适用于等值查询和范围查询：

-- 创建B-tree索引
CREATE INDEX idx_users_email ON users (email);

-- 创建复合索引
CREATE INDEX idx_users_name ON users (last_name, first_name);

-- 创建部分索引
CREATE INDEX idx_active_users ON users (username) WHERE is_active = true;

Hash索引

Hash索引适用于等值查询，但不支持范围查询：

-- 创建Hash索引
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

GiST索引

GiST索引适用于几何数据、全文搜索等复杂数据类型：

-- 创建GiST索引用于全文搜索
CREATE INDEX idx_products_search ON products USING GIST (search_vector);

-- 创建GiST索引用于几何数据
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

GIN索引

GIN索引适用于数组、JSON等包含多个值的数据类型：

-- 创建GIN索引用于数组
CREATE INDEX idx_students_scores ON students USING GIN (scores);

-- 创建GIN索引用于JSONB
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- 创建GIN索引用于全文搜索
CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('chinese', content));

BRIN索引

BRIN索引适用于大型表的块级索引：

-- 创建BRIN索引
CREATE INDEX idx_large_table_date ON large_table USING BRIN (created_date);

6.3 索引创建和管理

创建索引

-- 基本索引创建
CREATE INDEX idx_products_name ON products (name);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users (username);

-- 复合索引
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- 部分索引
CREATE INDEX idx_products_active ON products (name) WHERE is_active = true;

-- 并发创建索引（不阻塞表操作）
CREATE INDEX CONCURRENTLY idx_users_phone ON users (phone);

查看索引

-- 查看表的索引
\d users

-- 查看索引详细信息
SELECT 
    indexname, 
    indexdef 
FROM pg_indexes 
WHERE tablename = 'users';

-- 查看索引使用统计
SELECT 
    schemaname, 
    tablename, 
    indexname, 
    idx_tup_read, 
    idx_tup_fetch 
FROM pg_stat_user_indexes 
WHERE tablename = 'users';

删除索引

-- 删除索引
DROP INDEX idx_users_email;

-- 如果索引存在则删除
DROP INDEX IF EXISTS idx_users_email;

重建索引

-- 重建索引
REINDEX INDEX idx_users_email;

-- 重建表的所有索引
REINDEX TABLE users;

-- 重建数据库的所有索引
REINDEX DATABASE mydb;

6.4 索引优化

索引选择性

选择性高的列更适合创建索引：

-- 查看列的选择性
SELECT 
    COUNT(DISTINCT email) * 100.0 / COUNT(*) AS selectivity 
FROM users;

复合索引顺序

复合索引的列顺序很重要：

-- 根据查询模式创建复合索引
-- 如果经常按user_id查询，再按order_date排序
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- 查询示例
SELECT * FROM orders WHERE user_id = 1 ORDER BY order_date;
SELECT * FROM orders WHERE user_id = 1 AND order_date > '2023-01-01';

索引维护

-- 查看索引膨胀情况
SELECT 
    schemaname, 
    tablename, 
    indexname, 
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes 
WHERE schemaname = 'public' 
ORDER BY pg_relation_size(indexname::regclass) DESC;

6.5 视图

视图概述

视图是虚拟表，基于SQL查询定义：

• 简化查询：隐藏复杂查询逻辑
• 安全性：限制用户访问特定数据
• 逻辑独立性：表结构变化不影响应用程序

创建视图

-- 创建简单视图
CREATE VIEW active_users AS
SELECT id, username, email, first_name, last_name
FROM users
WHERE is_active = true;

-- 创建复杂视图
CREATE VIEW user_order_summary AS
SELECT 
    u.id,
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 创建物化视图（存储查询结果）
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
    DATE(created_at) AS signup_date,
    COUNT(*) AS user_count
FROM users
GROUP BY DATE(created_at);

使用视图

-- 查询视图
SELECT * FROM active_users;

-- 带条件查询视图
SELECT * FROM user_order_summary 
WHERE order_count > 5;

-- 连接视图
SELECT u.username, uos.order_count
FROM active_users u
JOIN user_order_summary uos ON u.id = uos.id;

修改视图

-- 修改视图定义
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, first_name, last_name, created_at
FROM users
WHERE is_active = true;

删除视图

-- 删除视图
DROP VIEW active_users;

-- 删除物化视图
DROP MATERIALIZED VIEW user_stats;

刷新物化视图

-- 刷新物化视图
REFRESH MATERIALIZED VIEW user_stats;

-- 并发刷新物化视图（不阻塞查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

6.6 物化视图

物化视图优势

• 性能提升：预计算复杂查询结果
• 减少负载：避免重复执行复杂查询
• 数据快照：提供特定时间点的数据视图

物化视图示例

-- 创建销售报告物化视图
CREATE MATERIALIZED VIEW monthly_sales_report AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- 创建带有索引的物化视图
CREATE MATERIALIZED VIEW product_inventory AS
SELECT 
    p.id,
    p.name,
    p.category_id,
    c.name AS category_name,
    p.stock_quantity,
    p.price
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.is_active = true;

-- 为物化视图创建索引
CREATE INDEX idx_product_inventory_category ON product_inventory (category_id);

6.7 索引和视图最佳实践

索引最佳实践

• 选择合适的列：为经常用于WHERE、JOIN、ORDER BY的列创建索引
• 复合索引顺序：将选择性高的列放在前面
• 避免过多索引：每个索引都会增加维护成本
• 定期维护：监控索引使用情况，删除无用索引
• 部分索引：对部分数据创建索引以节省空间

视图最佳实践

• 简化复杂查询：将复杂查询封装在视图中
• 数据安全：通过视图限制用户访问敏感数据
• 逻辑独立性：使用视图减少应用程序与表结构的耦合
• 物化视图：对复杂聚合查询使用物化视图提高性能

-- 电商系统索引优化示例
-- 为用户表创建索引
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_username ON users (username);
CREATE INDEX idx_users_active ON users (is_active) WHERE is_active = true;

-- 为订单表创建索引
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date DESC);
CREATE INDEX idx_orders_status_date ON orders (status, order_date);
CREATE INDEX idx_orders_total ON orders (total_amount);

-- 为产品表创建索引
CREATE INDEX idx_products_category_active ON products (category_id, is_active);
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_products_name_search ON products USING GIN (to_tsvector('english', name));

-- 创建用户订单视图
CREATE VIEW user_recent_orders AS
SELECT 
    u.id AS user_id,
    u.username,
    o.id AS order_id,
    o.order_date,
    o.total_amount,
    o.status
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY o.order_date DESC;

-- 创建销售统计物化视图
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    DATE(order_date) AS sale_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY DATE(order_date);

-- 为物化视图创建索引
CREATE INDEX idx_daily_sales_date ON daily_sales_summary (sale_date);