当前位置：首页 > news >正文

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

news 2025/5/14 16:45:15

优化审核模块响应时间从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);