Оптимизация Запроса Агрегации MySQL

Question

Оптимизация Запроса Агрегации MySQL

У меня есть очень большая таблица (~100 миллионов записей) в MySQL, которая содержит информацию о файлах. Одна из частей информации-это дата изменения каждого файла.

Мне нужно написать запрос, который будет подсчитывать количество файлов, которые вписываются в заданные диапазоны дат. Для этого я сделал небольшую таблицу, которая определяет эти диапазоны (все в днях) и выглядит так:

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 running total. Я не могу понять, как это сделать....

Может ли кто-нибудь помочь с этим?

Спасибо.

EDIT : версия MySQL: 5.0.45, Таблицы-это MyISAM

EDIT2 : Вот описание, которое было предложено в комментариях

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

3 4

mysql aggregate large-data-volumes

3 ответа:

Для начала убедитесь, что file_last_access является индексом для таблицы HugeFileTable.

Я не уверен, что это возможно\лучше, но сначала попробуйте вычислить ограничения по датам (файлы от даты A до даты B), а затем используйте некоторый запрос с >= и

Сравнение будет примерно таким:
 t.file_last_access >= StartDate AND t.file_last_access <= EndDate 

1

Вы могли бы получить небольшое улучшение, удалив CURDATE () и поместив дату в запрос, поскольку он будет запускать эту функцию для каждой строки дважды в вашем SQL.

0

Bill Karwin · Accepted Answer · 2009-05-02 19:05:48

Сначала создайте индекс на 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;
Вот план EXPLAIN, который я получил, когда попробовал этот запрос на MySQL 5.0.75 (отредактировано для краткости):
+-------+-------+------------------+----------------------------------------------+
| table | type  | key              | Extra                                        |
+-------+-------+------------------+----------------------------------------------+
| t     | index | file_last_access | Using index; Using temporary; Using filesort | 
| r     | ALL   | NULL             | Using where                                  | 
+-------+-------+------------------+----------------------------------------------+
Это все еще не будет работать очень хорошо. При использовании GROUP BY запрос создает временную таблицу, которая может быть дорогостоящей. Не так много вы можете сделать об этом.

Но, по крайней мере, этот запрос устраняет декартово произведение, которое было у вас в оригинале запрос.

Update: вот еще один запрос, который использует коррелированный подзапрос, но я устранил 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 | 
+----+--------------------+-------+-------+------------------+--------------------------+
Попробуйте выполнить этот запрос в вашем наборе данных и посмотрите, лучше ли он работает.