mysql - 我如何优化 SUM() mysql 查询

Question

我有一个在超过 60 万条记录上运行的 mysql SUM 查询。我目前正在做的是这样的

 SELECT SUM (payment)
    FROM payment_table
    WHERE
       payment_date BETWEEN ... AND ...
       AND
       payment_status = 'paid'

我将查询更改为这种格式以减少记录集，但仍然花费几乎相同的时间。

SELECT SUM(Payments)
FROM (
    SELECT payment AS Payments FROM payment_table WHERE
     payment_date BETWEEN DATE_FORMAT(NOW(), '2012-2-01') AND DATE_FORMAT(LAST_DAY(DATE_FORMAT(NOW(), '2012-2-01')), '%Y-%m-%d')
    AND
    payment_status = 'paid'
) AS tmp_table

他们有什么方法可以优化这个总和查询吗？编辑:

这是使用 EXPLAIN 运行查询时的结果

insert into ` (id,select_type,table,type,possible_keys,
key,key_len,ref,rows,Extra`) values('1','SIMPLE','lps','index_merge','assigned_user_id,scheduled_payment_date,payment_status,deleted','deleted,assigned_user_id,payment_status','2,109,303',NULL,'23347','Using intersect(deleted,assigned_user_id,payment_status); Using where');

Answer 1

您应该将谓词的数据类型与列相匹配。因为payment_type是 DATE ，将 BETWEEN 值设为 DATE还有:

 WHERE payment_date BETWEEN
     CURDATE() AND LAST_DAY(CURDATE())

匹配类型确保索引将被使用。

相反，您的查询使用 DATE_FORMAT() ，它生成 text 数据类型，因此为了执行比较，mysql 正在转换 payment_dare列到文本，因此它无法使用索引(索引包含 DATE 值，而不是文本值)，因此每一行都会被转换和比较。

如果进行上述更改后仍然遇到性能问题，请执行以下操作:

ANALYZE TABLE payment_table;

它将检查索引列中值的分布，这有助于 mysql 做出正确的索引选择。