四、索引与性能优化

4.1 索引类型

-- B-tree索引（默认）
CREATE INDEX idx_employees_last_name ON employees(last_name);
CREATE INDEX idx_employees_email ON employees(email) WHERE email IS NOT NULL;  -- 部分索引

-- 唯一索引
CREATE UNIQUE INDEX idx_unique_email ON employees(email);

-- 多列索引
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary);

-- 表达式索引
CREATE INDEX idx_employees_lower_email ON employees(LOWER(email));
CREATE INDEX idx_employees_full_name ON employees((first_name || ' ' || last_name));

-- Hash索引（等值查询）
CREATE INDEX idx_hash_employee_id ON employees USING HASH (employee_id);

-- GiST索引（几何、全文搜索）
CREATE INDEX idx_address_gist ON addresses USING GIST (location);
CREATE INDEX idx_document_gist ON documents USING GIST (document_vector);

-- SP-GiST索引（空间分区）
CREATE INDEX idx_point_spgist ON points USING SPGIST (point);

-- GIN索引（数组、JSONB、全文搜索）
CREATE INDEX idx_tags_gin ON employees USING GIN (tags);
CREATE INDEX idx_metadata_gin ON employees USING GIN (metadata);
CREATE INDEX idx_document_gin ON documents USING GIN (to_tsvector('english', content));

-- BRIN索引（大表，按顺序存储）
CREATE INDEX idx_created_at_brin ON employees USING BRIN (created_at);

-- 覆盖索引
CREATE INDEX idx_covering ON employees(department_id) INCLUDE (first_name, last_name, salary);

4.2 索引使用优化

-- 查看索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename;

-- 查看表扫描统计
SELECT 
    schemaname,
    tablename,
    seq_scan AS sequential_scans,
    seq_tup_read AS sequential_tuples_read,
    idx_scan AS index_scans,
    idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- 分析查询执行计划
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM employees WHERE last_name = '张伟';

-- 查看执行计划格式
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM employees WHERE department_id = 10;

-- 禁用顺序扫描
SET enable_seqscan = OFF;

-- 使用索引提示
SELECT /*+ IndexScan(employees idx_employees_last_name) */ *
FROM employees
WHERE last_name = '张伟';

4.3 查询优化技巧

-- 使用EXPLAIN分析
EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT e.*, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 10000
ORDER BY e.hire_date DESC;

-- 使用物化视图
CREATE MATERIALIZED VIEW dept_salary_stats AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS emp_count,
    AVG(e.salary) AS avg_salary,
    SUM(e.salary) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 创建索引加速物化视图
CREATE UNIQUE INDEX ON dept_salary_stats (department_id);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_salary_stats;

-- 使用聚合下推
CREATE STATISTICS employees_stats (dependencies) ON department_id, salary FROM employees;
ANALYZE employees;

-- 批量操作优化
-- 关闭自动提交
BEGIN;
-- 执行大量插入
INSERT INTO logs SELECT * FROM temp_logs;
COMMIT;

-- 使用COPY命令
COPY employees FROM '/data/employees.csv' DELIMITER ',' CSV HEADER;

-- 使用临时表
CREATE TEMP TABLE temp_data AS SELECT * FROM large_table WHERE condition;
-- 执行复杂操作
DROP TABLE temp_data;

-- 分区表优化
CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10,2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE INDEX idx_sales_2023_date ON sales_2023 (sale_date);

五、高级数据类型

5.1 JSON/JSONB

-- 创建JSONB表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

-- 插入JSON数据
INSERT INTO products (name, attributes) VALUES 
('Laptop', '{"brand": "Dell", "ram": "16GB", "storage": "512GB SSD", "price": 1200}'),
('Phone', '{"brand": "Apple", "model": "iPhone 14", "storage": "256GB", "price": 999}');

-- JSONB查询
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';

-- JSONB索引
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- JSONB操作符
-- @> : 包含
-- ? : 是否存在键
-- ?| : 是否存在任意键
-- ?& : 是否存在所有键
-- - : 删除键
-- #> : 获取JSON对象路径

SELECT * FROM products WHERE attributes ? 'brand';
SELECT * FROM products WHERE attributes ?| ARRAY['brand', 'model'];
SELECT attributes->'brand' FROM products;
SELECT attributes->>'brand' FROM products;
SELECT attributes#>'{brand}' FROM products;

-- JSONB更新
UPDATE products 
SET attributes = attributes || '{"warranty": "2 years"}'::jsonb
WHERE name = 'Laptop';

UPDATE products 
SET attributes = attributes - 'price'
WHERE name = 'Phone';

-- JSONB聚合
SELECT jsonb_agg(attributes) FROM products;
SELECT jsonb_object_agg(name, attributes) FROM products;

-- JSONB函数
SELECT jsonb_pretty(attributes) FROM products;
SELECT jsonb_typeof(attributes->'brand') FROM products;

5.2 数组类型

-- 创建数组类型表
CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[],
    authors TEXT[],
    views INTEGER[]
);

-- 插入数组数据
INSERT INTO articles (title, tags, authors) VALUES 
('PostgreSQL Advanced', ARRAY['database', 'postgresql', 'sql'], '{"John Doe", "Jane Smith"}'),
('JSON in PostgreSQL', ARRAY['json', 'postgresql', 'nosql'], '{"Alice Brown"}');

-- 数组查询
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);
SELECT * FROM articles WHERE tags @> ARRAY['database', 'sql'];
SELECT * FROM articles WHERE tags && ARRAY['json', 'nosql'];

-- 数组索引
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 数组操作符
-- || : 连接
-- && : 重叠
-- @> : 包含
-- <@ : 被包含

SELECT title, array_length(tags, 1) AS tag_count FROM articles;
SELECT title, unnest(tags) FROM articles;

-- 数组函数
SELECT array_append(tags, 'new_tag') FROM articles;
SELECT array_prepend('first_tag', tags) FROM articles;
SELECT array_cat(tags, ARRAY['tag1', 'tag2']) FROM articles;
SELECT array_remove(tags, 'database') FROM articles;

5.3 范围类型

-- 范围类型
CREATE TABLE reservations (
    room_id INTEGER,
    reservation_period TSRANGE,
    guest_name TEXT
);

-- 插入范围数据
INSERT INTO reservations VALUES 
(101, '[2024-01-01 14:00, 2024-01-05 12:00)', '张三'),
(102, '[2024-01-10 15:00, 2024-01-15 11:00)', '李四');

-- 范围查询
SELECT * FROM reservations 
WHERE reservation_period @> '2024-01-02 10:00'::timestamp;

SELECT * FROM reservations 
WHERE reservation_period && '[2024-01-03, 2024-01-07)'::tsrange;

-- 范围函数
SELECT 
    room_id,
    lower(reservation_period) AS check_in,
    upper(reservation_period) AS check_out,
    upper(reservation_period) - lower(reservation_period) AS duration
FROM reservations;

-- 排除约束（防止重叠）
CREATE EXTENSION btree_gist;

CREATE TABLE meeting_rooms (
    room_id INTEGER,
    meeting_time TSRANGE,
    booked_by TEXT,
    EXCLUDE USING gist (room_id WITH =, meeting_time WITH &&)
);

5.4 全文搜索

-- 启用全文搜索
CREATE TABLE documents (
    doc_id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    document_vector TSVECTOR
);

-- 创建全文搜索向量
UPDATE documents SET document_vector = 
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', content), 'B');

-- 创建GIN索引
CREATE INDEX idx_documents_vector ON documents USING GIN (document_vector);

-- 创建触发器自动更新向量
CREATE FUNCTION documents_vector_update() RETURNS TRIGGER AS $$
BEGIN
    NEW.document_vector := 
        setweight(to_tsvector('english', NEW.title), 'A') ||
        setweight(to_tsvector('english', NEW.content), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_documents_vector 
    BEFORE INSERT OR UPDATE ON documents
    FOR EACH ROW EXECUTE FUNCTION documents_vector_update();

-- 全文搜索查询
SELECT title, content
FROM documents
WHERE document_vector @@ to_tsquery('english', 'database & postgresql');

-- 按相关性排序
SELECT 
    title,
    ts_rank(document_vector, query) AS rank
FROM documents,
     to_tsquery('english', 'database & postgresql') query
WHERE document_vector @@ query
ORDER BY rank DESC;

-- 高亮显示
SELECT 
    title,
    ts_headline('english', content, query, 'MaxWords=30, MinWords=15') AS highlighted
FROM documents,
     to_tsquery('english', 'postgresql') query
WHERE document_vector @@ query;

-- 短语搜索
SELECT * FROM documents
WHERE document_vector @@ phraseto_tsquery('english', 'full text search');

-- 中文全文搜索
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n, v, a WITH simple;

六、PL/pgSQL编程

6.1 函数

-- 创建函数
CREATE OR REPLACE FUNCTION get_employee_salary(p_employee_id INTEGER)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_salary NUMERIC;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION '员工ID % 不存在', p_employee_id;
    END IF;
    
    RETURN v_salary;
END;
$$;

-- 使用函数
SELECT get_employee_salary(1001);

-- 表值函数
CREATE OR REPLACE FUNCTION get_employees_by_department(p_dept_id INTEGER)
RETURNS TABLE (
    employee_id INTEGER,
    full_name TEXT,
    salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        e.employee_id,
        e.first_name || ' ' || e.last_name,
        e.salary
    FROM employees e
    WHERE e.department_id = p_dept_id;
END;
$$;

-- 使用表值函数
SELECT * FROM get_employees_by_department(10);

-- 带默认参数的函数
CREATE OR REPLACE FUNCTION calculate_bonus(
    p_salary NUMERIC,
    p_bonus_percent NUMERIC DEFAULT 10
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_salary * (p_bonus_percent / 100);
END;
$$;

-- 调用
SELECT calculate_bonus(5000);
SELECT calculate_bonus(5000, 15);

-- 多态函数
CREATE OR REPLACE FUNCTION array_union(anyarray, anyarray)
RETURNS anyarray
LANGUAGE sql
AS $$
    SELECT ARRAY(SELECT DISTINCT unnest($1 || $2) ORDER BY 1);
$$;

6.2 存储过程

-- 创建存储过程（PostgreSQL 11+）
CREATE OR REPLACE PROCEDURE update_employee_salary(
    p_employee_id INTEGER,
    p_new_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees 
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;
    
    IF NOT FOUND THEN
        RAISE NOTICE '员工ID % 不存在', p_employee_id;
    END IF;
    
    COMMIT;
END;
$$;

-- 调用存储过程
CALL update_employee_salary(1001, 8000);

-- 带事务控制的存储过程
CREATE OR REPLACE PROCEDURE transfer_funds(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_balance NUMERIC;
BEGIN
    -- 检查余额
    SELECT balance INTO v_balance
    FROM accounts
    WHERE account_id = p_from_account;
    
    IF v_balance < p_amount THEN
        RAISE EXCEPTION '余额不足';
    END IF;
    
    -- 执行转账
    UPDATE accounts SET balance = balance - p_amount 
    WHERE account_id = p_from_account;
    
    UPDATE accounts SET balance = balance + p_amount 
    WHERE account_id = p_to_account;
    
    -- 记录日志
    INSERT INTO transaction_log (from_account, to_account, amount, trans_date)
    VALUES (p_from_account, p_to_account, p_amount, NOW());
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

6.3 触发器与触发器函数

-- 创建审计表
CREATE TABLE employee_audit (
    audit_id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    operation CHAR(1),
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO employee_audit (employee_id, operation, new_data, changed_by)
        VALUES (NEW.employee_id, 'I', to_jsonb(NEW), current_user);
        RETURN NEW;
        
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO employee_audit (employee_id, operation, old_data, new_data, changed_by)
        VALUES (NEW.employee_id, 'U', to_jsonb(OLD), to_jsonb(NEW), current_user);
        RETURN NEW;
        
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO employee_audit (employee_id, operation, old_data, changed_by)
        VALUES (OLD.employee_id, 'D', to_jsonb(OLD), current_user);
        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$;

-- 创建触发器
CREATE TRIGGER trg_employees_audit
    AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW EXECUTE FUNCTION audit_employee_changes();

-- 语句级触发器
CREATE OR REPLACE FUNCTION log_ddl_changes()
RETURNS EVENT TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO ddl_log (event_type, object_name, sql_text, event_time)
    VALUES (
        TG_EVENT,
        TG_TAG,
        current_query(),
        NOW()
    );
END;
$$;

-- 创建事件触发器
CREATE EVENT TRIGGER trg_ddl_log
    ON ddl_command_end
    EXECUTE FUNCTION log_ddl_changes();

-- 禁用/启用触发器
ALTER TABLE employees DISABLE TRIGGER trg_employees_audit;
ALTER TABLE employees ENABLE TRIGGER trg_employees_audit;

6.4 游标

-- 显式游标
CREATE OR REPLACE FUNCTION process_employees()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    emp_cursor CURSOR FOR
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE is_active = true
        ORDER BY employee_id;
    
    v_emp_id INTEGER;
    v_first_name VARCHAR(50);
    v_last_name VARCHAR(50);
    v_salary NUMERIC;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name, v_salary;
        EXIT WHEN NOT FOUND;
        
        -- 处理员工数据
        RAISE NOTICE '处理员工: % %', v_first_name, v_last_name;
        
        -- 更新工资
        UPDATE employees 
        SET salary = v_salary * 1.05
        WHERE employee_id = v_emp_id;
    END LOOP;
    
    CLOSE emp_cursor;
END;
$$;

-- 游标变量（REF CURSOR）
CREATE OR REPLACE FUNCTION get_employee_cursor(p_dept_id INTEGER)
RETURNS REFCURSOR
LANGUAGE plpgsql
AS $$
DECLARE
    ref REFCURSOR;
BEGIN
    OPEN ref FOR
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
    
    RETURN ref;
END;
$$;

-- 使用游标变量
BEGIN;
SELECT get_employee_cursor(10);
FETCH ALL IN "<unnamed portal 1>";
COMMIT;

来源：
https://app-ad0bpnnq0o3l.appmiaoda.com

PostgreSQL数据库学习知识点大全（二）

四、索引与性能优化

五、高级数据类型

六、PL/pgSQL编程

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

PostgreSQL数据库学习知识点大全（二）

四、索引与性能优化

五、高级数据类型

六、PL/pgSQL编程

热门文章

最新文章

相关电子书