优化审核模块响应时间从8s降至1.2s的数据库解决方案

优化审核模块响应时间从8s降至1.2s的数据库解决方案

要优化审核模块的数据库性能，需要从多个层面进行分析和优化。以下是具体的SQL语句设计和优化方案：

1. 分析当前性能瓶颈

首先需要找出慢查询：

-- 查看慢查询日志中的审核模块相关查询
SELECT * FROM mysql.slow_log 
WHERE query_time > 1 
AND sql_text LIKE '%audit%' OR sql_text LIKE '%审核%'
ORDER BY query_time DESC
LIMIT 10;

-- 或使用performance_schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%audit%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

2. 索引优化

-- 添加缺失的索引（示例）
ALTER TABLE audit_records ADD INDEX idx_status_created (status, created_at);
ALTER TABLE audit_log ADD INDEX idx_user_action (user_id, action_type);

-- 检查冗余索引并删除
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_db_name';

-- 使用覆盖索引优化特定查询
ALTER TABLE audit_details ADD INDEX idx_covering (audit_id, field_name, old_value, new_value);

3. 查询重写优化

-- 优化前（可能很慢的查询）
SELECT * FROM audit_records 
WHERE status = 'pending' 
AND created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY priority DESC, created_at ASC;

-- 优化后
SELECT a.id, a.priority, a.created_at, a.requester_id, 
       u.name AS requester_name, COUNT(c.comment_id) AS comment_count
FROM audit_records a
JOIN users u ON a.requester_id = u.user_id
LEFT JOIN audit_comments c ON a.id = c.audit_id
WHERE a.status = 'pending'
AND a.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY a.id
ORDER BY a.priority DESC, a.created_at ASC
LIMIT 100;

4. 表结构优化

-- 分区表（按时间范围分区）
ALTER TABLE audit_records PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p_2023 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p_2024 VALUES LESS THAN (TO_DAYS('2025-01-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 垂直拆分大表
CREATE TABLE audit_record_details (
    id BIGINT PRIMARY KEY,
    audit_id BIGINT NOT NULL,
    details JSON,
    FOREIGN KEY (audit_id) REFERENCES audit_records(id)
);

5. 缓存策略

-- 使用物化视图（MySQL 8.0+）
CREATE TABLE audit_summary_cache (
    date DATE PRIMARY KEY,
    pending_count INT,
    approved_count INT,
    rejected_count INT,
    last_updated TIMESTAMP
);

-- 定时刷新缓存
REPLACE INTO audit_summary_cache
SELECT 
    DATE(created_at) AS date,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
    SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved_count,
    SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) AS rejected_count,
    NOW() AS last_updated
FROM audit_records
WHERE created_at > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(created_at);

6. 数据库配置优化

-- 调整会话级参数（针对审核模块的特殊查询）
SET SESSION sort_buffer_size = 4M;
SET SESSION read_rnd_buffer_size = 2M;
SET SESSION tmp_table_size = 32M;

-- 长期优化参数
SET GLOBAL innodb_buffer_pool_size = 12G;  -- 根据服务器内存调整
SET GLOBAL innodb_io_capacity = 2000;     -- 对于SSD存储
SET GLOBAL innodb_read_io_threads = 8;
SET GLOBAL innodb_write_io_threads = 8;

7. 归档策略

-- 创建归档表
CREATE TABLE audit_records_archive LIKE audit_records;

-- 定期归档旧数据
INSERT INTO audit_records_archive
SELECT * FROM audit_records 
WHERE status IN ('approved', 'rejected') 
AND created_at < DATE_SUB(NOW(), INTERVAL 180 DAY);

-- 删除已归档数据
DELETE FROM audit_records 
WHERE status IN ('approved', 'rejected') 
AND created_at < DATE_SUB(NOW(), INTERVAL 180 DAY);

实施建议

1. 分阶段实施：先优化查询和索引，再考虑表结构调整
2. 测试环境验证：所有优化先在测试环境验证效果
3. 监控变化：优化后持续监控性能指标
4. A/B测试：可以逐步发布优化，对比新旧版本性能

通过以上综合优化措施，通常可以将审核模块的响应时间从8秒显著降低到1.2秒左右。实际效果取决于具体的数据量、查询模式和硬件配置。