SQL magic - запрос не должен занимать 15 часов, но он

Итак, у меня есть одна действительно чудовищная таблица MySQL (записи 900 тыс., всего 180 МБ), и я хочу извлечь из записей подгрупп с более высоким date_updated и рассчитать средневзвешенное значение в каждой группе. Расчет выполняется в течение ~ 15 часов, и у меня есть сильное чувство, что я делает это неправильно.

Во-первых, чудовищный макет таблицы:

category
element_id
date_updated
value
weight
source_prefix
source_name

Только ключ находится на element_id (BTREE, ~ 8k уникальных элементов).

И процесс расчета:

Сделайте хэш для каждой группы и подгруппы.

CREATE TEMPORARY TABLE `temp1` (INDEX ( `ds_hash` ))
                SELECT `category`, 
                `element_id`, 
                `source_prefix`, 
                `source_name`, 
                `date_updated`, 
                `value`, 
                `weight`, 
                MD5(CONCAT(`category`, `element_id`, `source_prefix`, `source_name`)) AS `subcat_hash`, 
                MD5(CONCAT(`category`, `element_id`, `date_updated`)) AS `cat_hash` 
                FROM `bigbigtable` WHERE `date_updated` <= '2009-04-28'

Я действительно не понимаю эту суету с хэшами, но она работала быстрее. Темная магия, я полагаю.

Найти максимальную дату для каждой подгруппы

CREATE TEMPORARY TABLE `temp2` (INDEX ( `subcat_hash` ))

                SELECT MAX(`date_updated`) AS `maxdate` , `subcat_hash`
                FROM `temp1`
                GROUP BY `subcat_hash`;

Присоединитесь к temp1 с помощью temp2, чтобы найти средневзвешенные значения для категорий

CREATE TEMPORARY TABLE `valuebycats` (INDEX ( `category` ))
            SELECT `temp1`.`element_id`, 
                   `temp1`.`category`, 
                   `temp1`.`source_prefix`, 
                   `temp1`.`source_name`, 
                   `temp1`.`date_updated`, 
                   AVG(`temp1`.`value`) AS `avg_value`,
            SUM(`temp1`.`value` * `temp1`.`weight`) / SUM(`weight`) AS `rating`

            FROM `temp1` LEFT JOIN `temp2` ON `temp1`.`subcat_hash` = `temp2`.`subcat_hash`
            WHERE `temp2`.`subcat_hash` = `temp1`.`subcat_hash`
            AND `temp1`.`date_updated` = `temp2`.`maxdate`

            GROUP BY `temp1`.`cat_hash`;

(теперь, когда я просмотрел его и записал все это, мне кажется, что я должен использовать INNER JOIN в последнем запросе (чтобы избежать 900k * 900k temp table)).

Тем не менее, существует ли обычный способ?

UPD: некоторое изображение для справки:

удалена мертвая ссылка ImageShack

UPD: EXPLAIN для предлагаемого решения:

+----+-------------+-------+------+---------------+------------+---------+--------------------------------------------------------------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref                                                                                  | rows   | filtered | Extra                                        |
+----+-------------+-------+------+---------------+------------+---------+--------------------------------------------------------------------------------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | cur   | ALL  | NULL          | NULL       | NULL    | NULL                                                                                 | 893085 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | next  | ref  | prefix        | prefix     | 1074    | bigbigtable.cur.source_prefix,bigbigtable.cur.source_name,bigbigtable.cur.element_id |      1 |   100.00 | Using where                                  |
+----+-------------+-------+------+---------------+------------+---------+--------------------------------------------------------------------------------------+--------+----------+----------------------------------------------+

Ответ 1

Использование hashses - один из способов, с помощью которых механизм базы данных может выполнять объединение. Должно быть очень редко, что вам нужно написать собственное хэш-соединение; это, конечно, не похоже на одну из них, с таблицей строк в 900 тыс. строк с некоторыми агрегатами.

На основе вашего комментария этот запрос может сделать то, что вы ищете:

SELECT cur.source_prefix, 
       cur.source_name, 
       cur.category, 
       cur.element_id,
       MAX(cur.date_updated) AS DateUpdated, 
       AVG(cur.value) AS AvgValue,
       SUM(cur.value * cur.weight) / SUM(cur.weight) AS Rating
FROM eev0 cur
LEFT JOIN eev0 next
    ON next.date_updated < '2009-05-01'
    AND next.source_prefix = cur.source_prefix 
    AND next.source_name = cur.source_name
    AND next.element_id = cur.element_id
    AND next.date_updated > cur.date_updated
WHERE cur.date_updated < '2009-05-01'
AND next.category IS NULL
GROUP BY cur.source_prefix, cur.source_name, 
    cur.category, cur.element_id

GROUP BY выполняет вычисления для каждого элемента + категории +.

JOIN должен отфильтровать старые записи. Он ищет более поздние записи, а затем оператор WHERE отфильтровывает строки, для которых существует более поздняя запись. Такое объединение использует индекс (source_prefix, source_name, element_id, date_updated).

Существует множество способов отфильтровывать старые записи, но этот процесс имеет тенденцию работать хорошо.

Ответ 2

Хорошо, поэтому строки 900K не являются массивной таблицей, она достаточно большой, но ваши запросы действительно не должны длиться так долго.

Прежде всего, какое из трех утверждений выше занимает больше всего времени?

Первая проблема, которую я вижу, - это ваш первый запрос. Предложение WHERE не включает индексированный столбец. Таким образом, это означает, что он должен выполнить полное сканирование таблицы по всей таблице.

Создайте индекс в столбце "data_updated", затем снова запустите запрос и посмотрите, что он делает для вас.

Если вам не нужен хеш и используйте их только для использования темной магии, то полностью удалите их.

Изменить: кто-то с большим количеством SQL-fu, чем я, вероятно, уменьшит весь ваш набор логики в один оператор SQL без использования временных таблиц.

Изменить: мой SQL немного ржавый, но присоединяетесь ли вы дважды в третьем SQL файле? Может быть, это не изменит ситуацию, но не должно быть:

SELECT temp1.element_id, 
   temp1.category, 
   temp1.source_prefix, 
   temp1.source_name, 
   temp1.date_updated, 
   AVG(temp1.value) AS avg_value,
   SUM(temp1.value * temp1.weight) / SUM(weight) AS rating
FROM temp1 LEFT JOIN temp2 ON temp1.subcat_hash = temp2.subcat_hash
WHERE temp1.date_updated = temp2.maxdate
GROUP BY temp1.cat_hash;

или

SELECT temp1.element_id, 
   temp1.category, 
   temp1.source_prefix, 
   temp1.source_name, 
   temp1.date_updated, 
   AVG(temp1.value) AS avg_value,
   SUM(temp1.value * temp1.weight) / SUM(weight) AS rating
FROM temp1 temp2
WHERE temp2.subcat_hash = temp1.subcat_hash
AND temp1.date_updated = temp2.maxdate
GROUP BY temp1.cat_hash;