SQL魔术-查询不应该花15个小时，但是可以

好的，所以我有一个非常可怕的MySQL表(900k条记录，总共180 MB)，我想从子组中提取具有更高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临时表)。

还是有一种正常方式来做到这一点吗？

UPD :一些图片供引用:

删除了无效的ImageShack链接

UPD :建议的解决方案的说明:

+----+-------------+-------+------+---------------+------------+---------+--------------------------------------------------------------------------------------+--------+----------+----------------------------------------------+
| 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                                  |
+----+-------------+-------+------+---------------+------------+---------+--------------------------------------------------------------------------------------+--------+----------+----------------------------------------------+

最佳答案

使用散列是数据库引擎可执行联接的方式之一。您必须编写自己的基于散列的联接，这种情况很少见；这肯定看起来不像其中之一，因为它有一个900k行表和一些聚合。

根据您的评论，此查询可能会满足您的需求:

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)上的索引。

有很多方法可以过滤掉旧条目，但是这种方法往往表现良好。