数据库索引深度解析：B-Tree、Hash、GIN索引原理与优化实战

-- 创建B-Tree索引（默认类型）
CREATE INDEX idx_users_email ON users(email);

-- 复合索引（最左前缀原则）
CREATE INDEX idx_orders_user_status_date 
ON orders(user_id, status, created_at);

-- 查询优化示例

-- ✅ 使用索引（匹配最左前缀）
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed' AND created_at > '2026-01-01';

-- ❌ 不使用索引（跳过user_id）
SELECT * FROM orders WHERE status = 'completed';
SELECT * FROM orders WHERE created_at > '2026-01-01';

-- ✅ 范围查询（仅第一个范围条件后的字段不使用索引）
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2026-01-01' AND status = 'completed';
-- 索引使用：user_id(等值) + created_at(范围)，status无法使用索引

-- 1. 覆盖索引（Index Only Scan）
-- 创建包含所有查询字段的索引
CREATE INDEX idx_users_covering 
ON users(id, name, email) 
WHERE status = 'active';

-- 查询时不需要访问表数据
SELECT id, name, email FROM users WHERE status = 'active';
-- 执行计划：Index Only Scan（最快）

-- 2. 部分索引（Partial Index）
-- 只索引满足条件的行
CREATE INDEX idx_orders_pending 
ON orders(created_at) 
WHERE status = 'pending';

-- 查询pending订单时使用
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

-- 3. 表达式索引
-- 对表达式或函数结果建立索引
CREATE INDEX idx_users_email_lower 
ON users(LOWER(email));

-- 查询时使用相同表达式
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- MySQL Memory引擎支持Hash索引
CREATE TABLE sessions (
    session_id VARCHAR(64) PRIMARY KEY USING HASH,
    user_id INT,
    data TEXT,
    expires_at TIMESTAMP
) ENGINE=MEMORY;

-- ✅ 等值查询（使用Hash索引）
SELECT * FROM sessions WHERE session_id = 'abc123';

-- ❌ 范围查询（不使用Hash索引）
SELECT * FROM sessions WHERE session_id > 'abc100';

-- PostgreSQL支持Hash索引（但通常B-Tree更好）
CREATE INDEX idx_sessions_id_hash 
ON sessions USING HASH(session_id);

-- 适用场景：大量等值查询，无范围查询

-- 创建全文搜索索引
CREATE INDEX idx_articles_content_gin 
ON articles USING GIN(to_tsvector('english', content));

-- 全文搜索查询
SELECT * FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('database & indexing');

-- 使用GIN索引的查询计划
-- Bitmap Index Scan (GIN) -> Bitmap Heap Scan

-- 为JSONB字段创建GIN索引
CREATE INDEX idx_users_metadata_gin 
ON users USING GIN(metadata);

-- 查询JSONB字段
SELECT * FROM users 
WHERE metadata @> '{"preferences": {"theme": "dark"}}';

SELECT * FROM users 
WHERE metadata ? 'email_verified';

SELECT * FROM users 
WHERE metadata -> 'tags' ?| ARRAY['vip', 'premium'];

-- GIN索引支持的操作符：@>, ?, ?|, ?&

-- 为数组字段创建GIN索引
CREATE INDEX idx_products_tags_gin 
ON products USING GIN(tags);

-- 查询包含特定标签的产品
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'sale'];
SELECT * FROM products WHERE tags && ARRAY['new', 'featured'];

-- 空间数据索引
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coordinates POINT
);

CREATE INDEX idx_locations_coords_gist 
ON locations USING GIST(coordinates);

-- 范围查询
SELECT * FROM locations 
WHERE coordinates <@ box '((0,0),(10,10))';

-- 邻近查询
SELECT * FROM locations 
ORDER BY coordinates <-> point '(5,5)' 
LIMIT 10;

-- 时间范围索引
CREATE TABLE meetings (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    time_range TSRANGE
);

CREATE INDEX idx_meetings_time_gist 
ON meetings USING GIST(time_range);

-- 查询重叠的时间段
SELECT * FROM meetings 
WHERE time_range && tsrange('2026-06-01', '2026-06-30');

-- 查询包含特定时间点
SELECT * FROM meetings 
WHERE time_range @> '2026-06-15 10:00:00'::timestamp;

-- BRIN索引适用于物理顺序与逻辑顺序一致的数据
CREATE TABLE logs (
    id BIGSERIAL,
    created_at TIMESTAMP,
    level VARCHAR(10),
    message TEXT
);

-- 创建BRIN索引（空间占用极小）
CREATE INDEX idx_logs_created_brin 
ON logs USING BRIN(created_at) 
WITH (pages_per_range = 32);

-- 时序查询
SELECT * FROM logs 
WHERE created_at BETWEEN '2026-06-01' AND '2026-06-30';

-- BRIN vs B-Tree空间对比
-- BRIN索引：约1MB
-- B-Tree索引：约500MB

-- 设计原则：等值查询字段在前，范围查询字段在后

-- 查询模式
SELECT * FROM orders 
WHERE user_id = 123 
  AND status = 'completed'
  AND created_at > '2026-01-01'
ORDER BY amount DESC;

-- ✅ 推荐索引顺序
CREATE INDEX idx_orders_optimal 
ON orders(user_id, status, created_at, amount);

-- 原因：
-- 1. user_id: 等值查询
-- 2. status: 等值查询
-- 3. created_at: 范围查询（之后的字段无法使用索引）
-- 4. amount: 用于排序（如果可能）

// 索引决策树
func ChooseIndexType(queryPattern QueryPattern) IndexType {
    switch {
    case queryPattern.OnlyEquality:
        // 仅等值查询，考虑Hash索引
        return HashIndex
        
    case queryPattern.RangeQueries:
        // 范围查询，使用B-Tree
        return BTreeIndex
        
    case queryPattern.FullTextSearch:
        // 全文搜索，使用GIN
        return GINIndex
        
    case queryPattern.JSONQueries:
        // JSON查询，使用GIN
        return GINIndex
        
    case queryPattern.SpatialQueries:
        // 空间查询，使用GiST
        return GiSTIndex
        
    case queryPattern.TimeSeries && queryPattern.Ordered:
        // 时序数据且有序，考虑BRIN
        return BRINIndex
        
    default:
        // 默认使用B-Tree
        return BTreeIndex
    }
}

-- 1. 检测未使用的索引
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 2. 检测重复索引
SELECT 
    pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
    (array_agg(idx))[1] as idx1, 
    (array_agg(idx))[2] as idx2
FROM (
    SELECT indexrelid::regclass as idx, 
           (indrelid::text || E'\n' || indclass::text || E'\n' || 
            indkey::text || E'\n' || 
            coalesce(indexprs::text,'') || E'\n' || 
            coalesce(indpred::text,'')) as key
    FROM pg_index
) sub
GROUP BY key 
HAVING count(*) > 1;

-- 3. 重建碎片化索引
REINDEX INDEX idx_users_email;

-- 4. 并发重建索引（不锁表）
CREATE INDEX CONCURRENTLY idx_users_email_new ON users(email);
DROP INDEX CONCURRENTLY idx_users_email;
ALTER INDEX idx_users_email_new RENAME TO idx_users_email;

-- 原始慢查询（10秒）
EXPLAIN ANALYZE
SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
  AND o.created_at > '2026-01-01'
ORDER BY o.total_amount DESC
LIMIT 100;

-- 执行计划：Seq Scan on orders（全表扫描）

-- 优化1：添加复合索引
CREATE INDEX idx_orders_status_date_amount 
ON orders(status, created_at, total_amount DESC);

-- 优化后（50毫秒）
-- 执行计划：Index Scan（索引扫描）

-- 优化2：使用覆盖索引
CREATE INDEX idx_orders_covering 
ON orders(status, created_at, total_amount DESC, user_id, id);

-- 优化后（20毫秒）
-- 执行计划：Index Only Scan（仅索引扫描）