mysql - 优化 MySQL 表以选择日期范围内的多行

Question

我在 MySQL 中有一个 InnoDB 表，我必须在其中选择和汇总日期范围内的大量数据。似乎我无法达到它运行速度足以满足用例的程度。

表格如下:
user_id: 整数
日期:日期
金额:整数

该表有数亿行。
一个日期范围最多可以返回 1000 万行。
数量为1-10

我在所有三列上都有一个复合索引，顺序为:user_id、date、amount。

我用于选择的查询是:

SELECT   
    SUM(amount)  
FROM table  
WHERE user_id = ?  
AND request_date <= ?  
AND request_date >= ?

我将日期硬编码到查询中。

我还能做些什么来加快这个查询的速度？我应该能够每秒执行大约 20 次查询。

它在具有 8gb RAM 和 4 个 CPU(非专用)的 DI 上运行。

更新
EXPLAIN 的输出是:

select_type: SIMPLE  
type: range
possible_keys: composite  
key: composite  
key_len: 7 
ref: null 
rows: 14994440  
Extra: Using where; Using index

Answer 1

我过去使用过各种技术来做类似的事情。

• 您应该考虑对表进行分区。这涉及创建一个包含分区标识符的列，分区标识符可以是日期或年月

• 通过拆分日期和时间部分，我的性能有所提高。优点是您可以通过查看 date 字段快速获取日期中的所有数据，甚至无需考虑时间部分。

• 如果您知道要请求的数据类型，并且可以允许一些延迟，则可以预先计算。看起来您正在使用日志数据，所以我假设任何比今天更早的查询结果永远不会改变。您应该利用这一点，例如通过拥有一个包含聚合数据的单独表。如果你只需要计算“今天”，事情就会快得多。或者接受数字有点旧，你可以定期预计算。

我说的表格可能是这样的:

CREATE table aggregated_requests AS
SELECT user_id, request_date, SUM(amount) as amount
FROM table  

之后，像这样重写上面的查询，我会非常快:

SELECT SUM(amount)  
FROM aggregated_requests  
WHERE user_id = ?  
AND request_date <= ?  
AND request_date >= ?