mysql - 已编制索引时提高计数和求和的性能

Question

首先，这是我的查询:

SELECT 
    COUNT(*) as velocity_count, 
    SUM(`disbursements`.`amount`) as summation_amount 
FROM `disbursements` 
WHERE 
    `disbursements`.`accumulation_hash` = '40ad7f250cf23919bd8cc4619850a40444c5e90c978f88635a09ccf66a82ffb38e39ea51cdfd651b0ebdac5f5ca37cd7a17e0f60fea6cbce1397ccff5fa37346' 
    AND `disbursements`.`caller_id` = 1 
    AND `disbursements`.`active` = 1 
    AND (version_hash != '86b4111677294b27a1805643d193b8d437b6ddb170b4ed5dec39aa89bf070d160cbbcd697dfc1988efea8429b1f1557625bf956180c65d3dcd3a318280e0d2da') 
    AND (`disbursements`.`created_at` BETWEEN '2012-12-15 23:33:22' 
    AND '2013-01-14 23:33:22') LIMIT 1

解释扩展返回以下内容:

+----+-------------+---------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table         | type  | possible_keys                                                                                                                                 | key                          | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+---------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | disbursements | range | unique_request_index,index_disbursements_on_caller_id,disbursement_summation_index,disbursement_velocity_index,disbursement_version_out_index | disbursement_summation_index | 1543    | NULL | 191422 |   100.00 | Using where; Using index |
+----+-------------+---------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+------+--------+----------+--------------------------+

实际查询计数大约 95,000 行。如果我解释另一个命中约 50 行的查询，则解释是相同的，只是估计的行数更少。

选择的索引依次是accumulation_hash、caller_id、active、version_hash、created_at、amount。

我试过使用 COUNT(id) 或 COUNT(caller_id) 因为它们是非空字段并返回与 count(*) 相同的东西，但它对计划没有任何影响或实际查询的运行时间。

这也是一个繁重的插入表，自上次运行以来，基本上每个查询都会插入或更新一行，因此 mysql 查询缓存并不是完全有用。

在我使用诸如 memcache 或 redis 之类的东西制作某种分桶时间序列缓存之前，是否有明显的解决方案可以使它更快地工作？一个正常的 ~50 行查询在 5MS 内返回，90k+ 行的查询需要 500-900MS，我真的无法承受超过 100MS 的任何东西。

我应该指出日期是一个滚动的 30 天窗口，需要基本上是实时的。过期可能会以大约 1 分钟的粒度发生，但需要在提交后立即看到新项目。我也在RDS上，Read IOPS基本上是0，cpu在60-80%左右。当我不查询 90,000 多个记录项时，CPU 使用率通常保持在 10% 以下。

Answer 1

您可以尝试在 version_hash 之前具有 created_at 的索引(可能会更好地进行索引范围扫描......不清楚非相等谓词如何在 version_hash 上影响计划，但我怀疑它禁用了对 created_at 列的范围扫描。

除此之外，查询和索引看起来与您将要获得的一样好，EXPLAIN 输出显示索引满足查询。

考虑到该语句聚合了 95,000 多行，尤其是考虑到 1543 字节的键长度，该语句的性能听起来并不太不合理。这比我通常处理的要大得多。

索引中列的数据类型是什么，什么是簇键或主键？

accumulation_hash - 128-character representation of 512-bit value
caller_id - integer or numeric (?)
active - integer or numeric (?)
version_hash - another 128-characters
created_at - datetime (8bytes) or timestamp (4bytes)
amount - numeric or integer 

95,000 行，每行 1543 字节，相当于 140MB 的数据。