索引优化

索引类型

B-Tree 索引

默认索引类型，适用于等值查询和范围查询：

CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_name_age ON users(name, age);

Hash 索引

只支持等值查询：

CREATE INDEX idx_email_hash ON users USING HASH (email);

GiST 索引

支持几何数据和全文搜索：

CREATE INDEX idx_location ON stores USING GIST (location);
CREATE INDEX idx_search ON articles USING GIST (to_tsvector('english', content));

GIN 索引

支持数组和全文搜索：

CREATE INDEX idx_tags ON posts USING GIN (tags);
CREATE INDEX idx_search ON articles USING GIN (to_tsvector('english', content));

BRIN 索引

块范围索引，适合大表：

CREATE INDEX idx_created_at ON logs USING BRIN (created_at);

SP-GiST 索引

支持四叉树等数据结构：

CREATE INDEX idx_point ON points USING SPGIST (point);

创建索引

基本语法

CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX CONCURRENTLY idx_name ON users(name);

部分索引

CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

表达式索引

CREATE INDEX idx_lower_email ON users(LOWER(email));
CREATE INDEX idx_name_length ON users(LENGTH(name));

多列索引

CREATE INDEX idx_name_age ON users(name, age);

包含索引

CREATE INDEX idx_name_include ON users(name) INCLUDE (email, age);

查看索引

查看表索引

\di
SELECT * FROM pg_indexes WHERE tablename = 'users';
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

查看索引大小

SELECT pg_size_pretty(pg_relation_size('idx_name'));
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes WHERE tablename = 'users';

删除索引

DROP INDEX idx_name;
DROP INDEX IF EXISTS idx_name;
DROP INDEX CONCURRENTLY idx_name;

EXPLAIN 分析

基本使用

EXPLAIN SELECT * FROM users WHERE name = '张三';
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE name = '张三';

输出解读

字段	说明
Seq Scan	顺序扫描
Index Scan	索引扫描
Index Only Scan	仅索引扫描
Bitmap Scan	位图扫描
cost	代价估计
rows	预估行数
width	行宽度
actual time	实际时间
loops	循环次数

扫描类型

类型	说明
Seq Scan	全表扫描
Index Scan	索引扫描后回表
Index Only Scan	仅索引扫描
Bitmap Index Scan	位图索引扫描
Bitmap Heap Scan	位图堆扫描

索引优化策略

1. 选择合适的索引类型

• 等值查询：B-Tree、Hash
• 范围查询：B-Tree
• 数组查询：GIN
• 全文搜索：GIN、GiST
• 几何数据：GiST

2. 复合索引顺序

CREATE INDEX idx_status_age ON users(status, age);

SELECT * FROM users WHERE status = 'active';
SELECT * FROM users WHERE status = 'active' AND age > 20;
SELECT * FROM users WHERE status = 'active' ORDER BY age;

3. 覆盖索引

CREATE INDEX idx_name_email ON users(name) INCLUDE (email);

SELECT name, email FROM users WHERE name = '张三';

4. 部分索引

CREATE INDEX idx_active ON users(email) WHERE status = 'active';

SELECT email FROM users WHERE status = 'active' AND email = 'test@example.com';

5. 表达式索引

CREATE INDEX idx_lower_email ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

索引失效场景

1. 函数操作

SELECT * FROM users WHERE UPPER(name) = '张三';
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name ILIKE '张三';

2. 类型转换

SELECT * FROM users WHERE phone = 13800138000;
SELECT * FROM users WHERE phone = '13800138000';

3. LIKE 前缀通配符

SELECT * FROM users WHERE name LIKE '%张';
SELECT * FROM users WHERE name LIKE '张%';

4. OR 条件

SELECT * FROM users WHERE name = '张三' OR email = 'test@example.com';

5. NOT 操作

SELECT * FROM users WHERE NOT status = 'active';
SELECT * FROM users WHERE status <> 'active';

索引维护

重建索引

REINDEX INDEX idx_name;
REINDEX TABLE users;
REINDEX DATABASE mydb;

更新统计信息

ANALYZE users;
ANALYZE users (name, age);
VACUUM ANALYZE users;

查看索引使用情况

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

查看未使用的索引

SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan AS index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT i.indisunique
    AND idx_scan < 50
    AND pg_relation_size(i.indexrelid) > 1024 * 1024
ORDER BY pg_relation_size(i.indexrelid) DESC;

索引监控

索引命中率

SELECT
    sum(idx_blks_read) AS idx_read,
    sum(idx_blks_hit) AS idx_hit,
    round(sum(idx_blks_hit)::numeric /
        NULLIF(sum(idx_blks_hit) + sum(idx_blks_read), 0) * 100, 2) AS hit_ratio
FROM pg_statio_user_indexes;

索引大小统计

SELECT
    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;

下一步学习

• 事务处理 - 掌握事务处理
• 性能优化 - 学习性能优化