大数据量下的索引优化策略

面试指北 · 通用

大数据量查询特点分析

1. 性能瓶颈识别

-- 查询执行时间分析
-- MySQL
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE COMMAND = 'Query' AND TIME > 10;

-- PostgreSQL
SELECT query, state, query_start, now() - query_start as duration
FROM pg_stat_activity 
WHERE state = 'active' AND now() - query_start > interval '10 seconds';

2. 数据分布特征

• 时序数据：按时间分区，时间维度索引
• 用户数据：按用户ID分片，用户维度索引
• 地理数据：空间索引优化
• 日志数据：压缩存储 + 稀疏索引

核心索引策略

1. 分区表 + 局部索引

-- PostgreSQL 分区示例
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT,
    created_at TIMESTAMP,
    amount DECIMAL,
    status VARCHAR(20)
) PARTITION BY RANGE (created_at);

-- 创建月度分区
CREATE TABLE orders_202401 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 每个分区建立局部索引
CREATE INDEX idx_orders_202401_user ON orders_202401(user_id, status);
CREATE INDEX idx_orders_202401_amount ON orders_202401(amount) WHERE amount > 1000;

2. 复合索引优化

-- 基于查询频率设计复合索引
-- 高频查询：按用户查订单
CREATE INDEX idx_orders_user_time ON orders(user_id, created_at DESC, status);

-- 中频查询：按状态查询
CREATE INDEX idx_orders_status_time ON orders(status, created_at DESC) 
WHERE status IN ('pending', 'processing');

-- 低频查询：按金额范围
CREATE INDEX idx_orders_amount ON orders(amount) WHERE amount > 10000;

3. 部分索引（PostgreSQL）

-- 只为活跃数据建索引，减少索引大小
CREATE INDEX idx_active_users ON users(last_login_at, user_id) 
WHERE last_login_at > now() - interval '90 days';

-- 大表中的热点数据索引
CREATE INDEX idx_hot_products ON products(category_id, price) 
WHERE status = 'active' AND stock_quantity > 0;

大表索引建立技巧

1. 在线索引创建

-- MySQL 8.0+
ALTER TABLE large_table ADD INDEX idx_name(column) ALGORITHM=INPLACE, LOCK=NONE;

-- PostgreSQL
CREATE INDEX CONCURRENTLY idx_name ON large_table(column);

2. 批量索引创建

-- 关闭自动提交，批量创建
SET autocommit = 0;
CREATE INDEX idx1 ON table(col1);
CREATE INDEX idx2 ON table(col2);
CREATE INDEX idx3 ON table(col3);
COMMIT;

3. 分段创建策略

-- 大表分段创建索引，避免锁表时间过长
-- 步骤1：创建部分索引
CREATE INDEX idx_temp ON large_table(column) WHERE id BETWEEN 1 AND 1000000;

-- 步骤2：扩展索引范围
DROP INDEX idx_temp;
CREATE INDEX idx_final ON large_table(column);

特定场景优化

1. 时序数据优化

-- BRIN索引适合大表时序数据
CREATE INDEX idx_logs_time_brin ON access_logs USING BRIN(created_at);

-- 复合BRIN索引
CREATE INDEX idx_logs_multi_brin ON access_logs USING BRIN(created_at, user_id);

-- 查询示例
SELECT * FROM access_logs 
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
  AND user_id = 12345;

2. JSON数据优化

-- PostgreSQL GIN索引
CREATE INDEX idx_metadata_gin ON products USING GIN(metadata);

-- 函数索引提取特定JSON字段
CREATE INDEX idx_product_category ON products((metadata->>'category'));

-- 查询优化
SELECT * FROM products 
WHERE metadata->>'category' = 'electronics'
  AND (metadata->>'price')::numeric > 1000;

3. 全文搜索优化

-- PostgreSQL 全文搜索
CREATE INDEX idx_content_fts ON articles USING GIN(to_tsvector('english', content));

-- 多语言支持
CREATE INDEX idx_content_zh ON articles USING GIN(to_tsvector('zhparser', content));

-- 查询示例
SELECT * FROM articles 
WHERE to_tsvector('english', content) @@ plainto_tsquery('database optimization');

索引监控与调优

1. 索引使用情况监控

-- MySQL 索引使用统计
SELECT 
    s.TABLE_NAME,
    s.INDEX_NAME,
    s.CARDINALITY,
    i.STAT_VALUE as pages_used
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS i 
    ON s.TABLE_NAME = i.NAME
WHERE s.TABLE_SCHEMA = 'your_database'
ORDER BY s.CARDINALITY DESC;

-- PostgreSQL 索引效率分析
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

2. 慢查询索引优化

-- 找出缺失索引的慢查询
-- MySQL
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_time_sec,
    SUM_ROWS_EXAMINED/COUNT_STAR as avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest 
WHERE AVG_TIMER_WAIT > 1000000000  -- 超过1秒的查询
ORDER BY AVG_TIMER_WAIT DESC;

3. 索引碎片整理

-- MySQL
OPTIMIZE TABLE large_table;

-- PostgreSQL
REINDEX INDEX CONCURRENTLY idx_name;

-- 定期维护脚本
ANALYZE TABLE large_table;

高级优化技巧

1. 列存储索引（适用场景）

-- MySQL 8.0 列存储引擎
CREATE TABLE analytics_data (
    date_col DATE,
    user_id BIGINT,
    metric_value DECIMAL,
    INDEX idx_date(date_col),
    INDEX idx_user(user_id)
) ENGINE=COLUMNSTORE;

2. 内存表缓存热点数据

-- 热点数据内存表
CREATE TABLE hot_cache (
    id BIGINT PRIMARY KEY,
    data JSON,
    last_updated TIMESTAMP
) ENGINE=MEMORY;

-- 定期同步策略
INSERT INTO hot_cache 
SELECT id, data, updated_at 
FROM large_table 
WHERE updated_at > DATE_SUB(NOW(), INTERVAL 1 HOUR);

3. 读写分离索引策略

-- 主库：写优化，少索引
CREATE INDEX idx_write_only ON table(id, status);

-- 从库：读优化，多索引
CREATE INDEX idx_read_user ON table(user_id, created_at);
CREATE INDEX idx_read_category ON table(category, price);
CREATE INDEX idx_read_search ON table(title, description);

性能测试验证

1. 索引效果对比

-- 测试前后性能对比
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM large_table 
WHERE complex_condition;

-- 关注指标：
-- - Execution time
-- - Buffers hit/read
-- - Rows examined vs returned

2. 并发性能测试

# 使用工具测试并发查询性能
# sysbench, pgbench, 或自定义脚本
sysbench oltp_read_write \
  --table-size=10000000 \
  --mysql-db=testdb \
  --threads=64 \
  --time=300 \
  run

合理的大数据量索引设计需要结合具体业务场景，通过监控数据驱动优化决策，定期评估和调整索引策略。