针对千万级数据多字段 SUM 查询超时问题,我提供以下优化方案:
-- 示例:为常用查询创建复合索引
CREATE INDEX idx_query_optimize ON your_table(condition_field1, condition_field2, sum_field);
-- 示例:优化后的查询
SELECT field1, field2, SUM(value1), SUM(value2)
FROM your_table
WHERE condition_field = 'value'
GROUP BY field1, field2
LIMIT 1000;
-- 示例:创建预计算汇总表
CREATE TABLE summary_daily (
date DATE,
category VARCHAR(50),
total_value1 DECIMAL(18,2),
total_value2 DECIMAL(18,2),
PRIMARY KEY (date, category)
);
-- 定时更新汇总表
INSERT INTO summary_daily
SELECT
DATE(create_time) AS date,
category,
SUM(value1) AS total_value1,
SUM(value2) AS total_value2
FROM your_table
WHERE DATE(create_time) = CURRENT_DATE - INTERVAL 1 DAY
GROUP BY DATE(create_time), category
ON DUPLICATE KEY UPDATE
total_value1 = VALUES(total_value1),
total_value2 = VALUES(total_value2);
需要更具体的建议,请提供您的表结构、查询语句和数据库类型信息。