MySQL 1095_优化MySQL聚合查询

我在MySQL中有一个非常大的表(大约100万条记录),其中包含有关文件的信息.其中一条信息是每个文件的修改日期.

我需要编写一个查询来计算适合指定日期范围的文件数.为此,我创建了一个小表来指定这些范围(以天为单位),如下所示：

DateRanges

range_id range_name range_start range_end

1 0-90 0 90

2 91-180 91 180

3 181-365 181 365

4 366-1095 366 1095

5 1096+ 1096 999999999

并编写了一个如下所示的查询：

SELECT r.range_name, sum(IF((DATEDIFF(CURDATE(),t.file_last_access) > r.range_start and DATEDIFF(CURDATE(),t.file_last_access) < r.range_end),1,0)) as FileCount

FROM `DateRanges` r, `HugeFileTable` t

GROUP BY r.range_name

但是,可以预见的是,这个查询需要永远运行.我想这是因为我要求MySQL通过HugeFileTable 5次,每次都对每个文件执行DATEDIFF()计算.

我想做的是通过记录只通过一次HugeFileTable记录,并为每个文件增加相应range_name运行总计的计数.我无法弄明白该怎么做….

任何人都可以帮忙吗？

谢谢.

编辑：MySQL版本：5.0.45,表是MyISAM

EDIT2：这是评论中要求的descibe

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE r ALL NULL NULL NULL NULL 5 Using temporary; Using filesort

1 SIMPLE t ALL NULL NULL NULL NULL 96506321

解决方法:

首先,在HugeFileTable.file_last_access上创建一个索引.

然后尝试以下查询：

SELECT r.range_name, COUNT(t.file_last_access) as FileCount

FROM `DateRanges` r

JOIN `HugeFileTable` t

ON (t.file_last_access BETWEEN

CURDATE() + INTERVAL r.range_start DAY AND

CURDATE() + INTERVAL r.range_end DAY)

GROUP BY r.range_name;

这是我在MySQL 5.0.75上尝试此查询时获得的EXPLAIN计划(为简洁而编译)：

+-------+-------+------------------+----------------------------------------------+

| table | type | key | Extra |

+-------+-------+------------------+----------------------------------------------+

| t | index | file_last_access | Using index; Using temporary; Using filesort |

| r | ALL | NULL | Using where |

+-------+-------+------------------+----------------------------------------------+

它仍然不会表现得很好.通过使用GROUP BY,查询会产生一个临时表,这可能很昂贵.你可以做的不多.

但至少此查询会删除原始查询中的笛卡尔积.

更新：这是另一个使用相关子查询的查询,但我已经删除了GROUP BY.

SELECT r.range_name,

(SELECT COUNT(*)

FROM `HugeFileTable` t

WHERE t.file_last_access BETWEEN

CURDATE() - INTERVAL r.range_end DAY AND

CURDATE() - INTERVAL r.range_start DAY

) as FileCount

FROM `DateRanges` r;

EXPLAIN计划没有显示临时表或文件排序(至少我的测试表中有大量的行)：

+----+--------------------+-------+-------+------------------+--------------------------+

| id | select_type | table | type | key | Extra |

+----+--------------------+-------+-------+------------------+--------------------------+

| 1 | PRIMARY | r | ALL | NULL | |

| 2 | DEPENDENT SUBQUERY | t | index | file_last_access | Using where; Using index |

+----+--------------------+-------+-------+------------------+--------------------------+

在您的数据集上尝试此查询,看看它是否表现更好.

标签：mysql,aggregate,large-data-volumes

来源： https://codeday.me/bug/20190622/1260460.html