代码之家 › 专栏 › 技术社区 › Kuroki Kaze

SQL magic-query不应该花费15个小时,但确实需要

query-optimization mysql sql

Kuroki Kaze · 技术社区 · 17 年前

date_updated 并计算各组的加权平均数。计算过程持续了约15个小时,我强烈感觉自己 做错了 .

category
element_id
更新日期
value
weight
source_prefix
source_name

这里唯一的钥匙是开着的 元素id

计算过程:

为每个组和子组创建哈希。

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`;

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`;

(现在我查看了它并将其全部写了下来,在我看来,我应该在最后一个查询中使用内部联接(以避免900k*900k临时表)。

不过,是否有一个这样做?

UPD :一些图片供参考:

拆下固定的影像棚链接

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

2 回复 | 直到 11 年前

Andomar 17 年前

使用hashse是数据库引擎执行连接的一种方式。您必须编写自己的基于哈希的联接,这应该是非常罕见的;这当然不像它们中的一个,它有一个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按源+类别+元素执行计算。

有很多方法可以过滤掉旧条目,但这一种往往表现得相当好。

Glen 17 年前

好的,900K行不是一个庞大的表,它相当大,但是您的查询不应该花费那么长的时间。

首先,以上三种说法中哪一种花费的时间最多?

在“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;