从属子查询中的MySQL查询花费的时间太长

Question

我需要一个SQL大师来帮助我加快查询速度。

我有2个表格，数量和价格。数量记录间隔15分钟的两个时间戳之间的数量值。价格记录给定时间戳的价格，对于给定的价格类型，每5分钟有价格5记录。

我需要2个工作时间，例如每个时期的总价格。小时或一天之间，两个时间戳之间。这是通过每个周期（数量乘以15分钟数量窗口中3个价格的平均值）之和来计算的。

例如，假设我想查看1天每小时的总价格。结果集中每行的总价格值是该小时内四个15分钟期间中每一个的总价格的总和。每个15分钟的总价格是通过将该期间的数量乘以该数量期间的3个价格（每5分钟一个）的平均值计算出来的。

下面是我使用的查询，并将结果：

SELECT 
MIN(`quantities`.`start_timestamp`) AS `start`, 
MAX(`quantities`.`end_timestamp`) AS `end`, 
SUM(`quantities`.`quantity` * (
    SELECT AVG(`prices`.`price`) 
    FROM `prices` 
    WHERE `prices`.`timestamp` >= `quantities`.`start_timestamp` 
    AND `prices`.`timestamp` < `quantities`.`end_timestamp` 
    AND `prices`.`type_id` = 1 
)) AS total 
FROM `quantities` 
WHERE `quantities`.`start_timestamp` >= '2010-07-01 00:00:00' 
AND `quantities`.`start_timestamp` < '2010-07-02 00:00:00' 
GROUP BY HOUR( `quantities`.`start_timestamp`); 

+---------------------+---------------------+----------+ 
| start    | end     | total | 
+---------------------+---------------------+----------+ 
| 2010-07-01 00:00:00 | 2010-07-01 01:00:00 | 0.677733 | 
| 2010-07-01 01:00:00 | 2010-07-01 02:00:00 | 0.749133 | 
| 2010-07-01 02:00:00 | 2010-07-01 03:00:00 | 0.835467 | 
| 2010-07-01 03:00:00 | 2010-07-01 04:00:00 | 0.692233 | 
| 2010-07-01 04:00:00 | 2010-07-01 05:00:00 | 0.389533 | 
| 2010-07-01 05:00:00 | 2010-07-01 06:00:00 | 0.335300 | 
| 2010-07-01 06:00:00 | 2010-07-01 07:00:00 | 1.231467 | 
| 2010-07-01 07:00:00 | 2010-07-01 08:00:00 | 0.352800 | 
| 2010-07-01 08:00:00 | 2010-07-01 09:00:00 | 1.447200 | 
| 2010-07-01 09:00:00 | 2010-07-01 10:00:00 | 0.756733 | 
| 2010-07-01 10:00:00 | 2010-07-01 11:00:00 | 0.599467 | 
| 2010-07-01 11:00:00 | 2010-07-01 12:00:00 | 1.056467 | 
| 2010-07-01 12:00:00 | 2010-07-01 13:00:00 | 1.252600 | 
| 2010-07-01 13:00:00 | 2010-07-01 14:00:00 | 1.285567 | 
| 2010-07-01 14:00:00 | 2010-07-01 15:00:00 | 0.442933 | 
| 2010-07-01 15:00:00 | 2010-07-01 16:00:00 | 0.692567 | 
| 2010-07-01 16:00:00 | 2010-07-01 17:00:00 | 1.281067 | 
| 2010-07-01 17:00:00 | 2010-07-01 18:00:00 | 0.652033 | 
| 2010-07-01 18:00:00 | 2010-07-01 19:00:00 | 1.721900 | 
| 2010-07-01 19:00:00 | 2010-07-01 20:00:00 | 1.362400 | 
| 2010-07-01 20:00:00 | 2010-07-01 21:00:00 | 1.099300 | 
| 2010-07-01 21:00:00 | 2010-07-01 22:00:00 | 0.646267 | 
| 2010-07-01 22:00:00 | 2010-07-01 23:00:00 | 0.873100 | 
| 2010-07-01 23:00:00 | 2010-07-02 00:00:00 | 0.546533 | 
+---------------------+---------------------+----------+ 
24 rows in set (5.16 sec) 

我需要的查询比这更快的跑了很多，而且会对虽然这将是可能的。下面是从EXPLAIN EXTENDED结果...

+----+--------------------+------------+-------+-------------------+-----------------+---------+-------+-------+----------------------------------------------+ 
| id | select_type  | table  | type | possible_keys  | key    | key_len | ref | rows | Extra          | 
+----+--------------------+------------+-------+-------------------+-----------------+---------+-------+-------+----------------------------------------------+ 
| 1 | PRIMARY   | quantities | range | start_timestamp | start_timestamp | 8  | NULL | 89 | Using where; Using temporary; Using filesort | 
| 2 | DEPENDENT SUBQUERY | prices  | ref | timestamp,type_id | type_id   | 4  | const | 22930 | Using where         | 
+----+--------------------+------------+-------+-------------------+-----------------+---------+-------+-------+----------------------------------------------+ 
2 rows in set, 3 warnings (0.00 sec) 

我注意到相关子查询不使用时间戳字段中键和查询扫描行的负荷。

任何人都可以帮我把这个跑得更快吗？

下面是创建模式，并用大量的数据填充它需要的SQL语句（2个月的价值）

# Create prices table 

CREATE TABLE `prices` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `timestamp` datetime NOT NULL, 
    `type_id` int(11) NOT NULL, 
    `price` float(8,2) NOT NULL, 
    PRIMARY KEY (`id`), 
    KEY `timestamp` (`timestamp`), 
    KEY `type_id` (`type_id`) 
) ENGINE=MyISAM; 

# Create quantities table 

CREATE TABLE `quantities` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `start_timestamp` datetime NOT NULL, 
    `end_timestamp` datetime NOT NULL, 
    `quantity` float(7,2) NOT NULL, 
    PRIMARY KEY (`id`), 
    KEY `start_timestamp` (`start_timestamp`), 
    KEY `end_timestamp` (`end_timestamp`) 
) ENGINE=MyISAM; 

# Insert first 2 rows into prices, one for each of 2 types, starting 64 days ago 

INSERT INTO `prices` (`id`, `timestamp`, `type_id`, `price`) VALUES 
(NULL, DATE_SUB(CURDATE(), INTERVAL 64 DAY), '1', RAND()), 
(NULL, DATE_SUB(CURDATE(), INTERVAL 64 DAY), '2', RAND()); 

# Fill the prices table with a record for each type, for every 5 minutes, for the next 64 days 

INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 32 DAY), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 16 DAY), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 8 DAY), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 4 DAY), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 2 DAY), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 1 DAY), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 12 HOUR), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 6 HOUR), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 3 HOUR), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 90 MINUTE), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 45 MINUTE), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 20 MINUTE), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 10 MINUTE), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_ADD(`timestamp`, INTERVAL 5 MINUTE), `type_id`, RAND() FROM prices; 
INSERT INTO prices (`timestamp`, `type_id`, `price`) SELECT DATE_SUB(`timestamp`, INTERVAL 5 MINUTE), `type_id`, RAND() FROM prices WHERE MOD((TIME_TO_SEC(`timestamp`) - TIME_TO_SEC(CONCAT(DATE_SUB(CURDATE(), INTERVAL 64 DAY), ' 00:00:00'))), 45 *60) = 0 AND `timestamp` > CONCAT(DATE_SUB(CURDATE(), INTERVAL 64 DAY), ' 00:00:00'); 

# Insert first row into quantities, start timestamp is 64 days ago, end timestamp is start timestamp plus 15 minutes 

INSERT INTO `quantities` (`id`, `start_timestamp`, `end_timestamp`, `quantity`) VALUES (NULL, DATE_SUB(CURDATE(), INTERVAL 64 DAY), DATE_SUB(CURDATE(), INTERVAL '63 23:45' DAY_MINUTE), RAND()); 

# Fill the quantities table with a record for each 15 minute period for the next 64 days 

INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 32 DAY), DATE_ADD(`end_timestamp`, INTERVAL 32 DAY), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 16 DAY), DATE_ADD(`end_timestamp`, INTERVAL 16 DAY), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 8 DAY), DATE_ADD(`end_timestamp`, INTERVAL 8 DAY), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 4 DAY), DATE_ADD(`end_timestamp`, INTERVAL 4 DAY), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 2 DAY), DATE_ADD(`end_timestamp`, INTERVAL 2 DAY), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 1 DAY), DATE_ADD(`end_timestamp`, INTERVAL 1 DAY), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 12 HOUR), DATE_ADD(`end_timestamp`, INTERVAL 12 HOUR), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 6 HOUR), DATE_ADD(`end_timestamp`, INTERVAL 6 HOUR), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 3 HOUR), DATE_ADD(`end_timestamp`, INTERVAL 3 HOUR), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 90 MINUTE), DATE_ADD(`end_timestamp`, INTERVAL 90 MINUTE), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 45 MINUTE), DATE_ADD(`end_timestamp`, INTERVAL 45 MINUTE), RAND() FROM quantities; 
INSERT INTO `quantities` (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_ADD(`start_timestamp`, INTERVAL 15 MINUTE), DATE_ADD(`end_timestamp`, INTERVAL 15 MINUTE), RAND() FROM quantities; 
INSERT INTO quantities (`start_timestamp`, `end_timestamp`, `quantity`) SELECT DATE_SUB(`start_timestamp`, INTERVAL 15 MINUTE), DATE_SUB(`end_timestamp`, INTERVAL 15 MINUTE), RAND() FROM quantities WHERE MOD((TIME_TO_SEC(`start_timestamp`) - TIME_TO_SEC(CONCAT(DATE_SUB(CURDATE(), INTERVAL 64 DAY), ' 00:00:00'))), 45 * 60) = 0 AND `start_timestamp` > CONCAT(DATE_SUB(CURDATE(), INTERVAL 64 DAY), ' 00:00:00'); 

Answer 1

这里是我第一次尝试。 这一个是脏和数据使用以下属性：

• 有3种5最新价格每个季度的数量（如果这是在数据违反了查询将无法正常工作）每个
• 通知，三个基数，这不是由数据完整性检查保证所以因此我称之为脏
• 它也是不灵活，以周期

查询1的变化：

SELECT sql_no_cache 
    min(q.start_timestamp) as start, 
    max(q.end_timestamp) as end, 
    sum((p1.price + p2.price + p3.price)/3*q.quantity) as total 
FROM 
    quantities q join 
    prices p1 on q.start_timestamp = p1.timestamp and p1.type_id = 1 join 
    prices p2 on p2.timestamp = adddate(q.start_timestamp, interval 5 minute) and p2.type_id = 1 join 
    prices p3 on p3.timestamp = adddate(q.start_timestamp, interval 10 minute) and p3.type_id = 1 
WHERE 
    q.start_timestamp between '2010-07-01 00:00:00' and '2010-07-01 23:59:59' 
GROUP BY hour(q.start_timestamp); 

这个在我的慢测试机器上返回结果的时间为0.01秒，原始查询在约6秒内运行，而gnarf的查询在约0.85秒内（所有查询总是用SQL_NO_CACHE关键字进行测试，但不重复使用结果，但在一个温暖的数据库上）。

编辑： 这里是一个版本，是不是在价格方面 查询1A缺少行敏感

SELECT sql_no_cache 
    min(q.start_timestamp) as start, 
    max(q.end_timestamp) as end, 
    sum((COALESCE(p1.price,0) + COALESCE(p2.price,0) + COALESCE(p3.price,0))/( 
     3 - 
     COALESCE(p1.price-p1.price,1) - 
     COALESCE(p2.price-p2.price,1) - 
     COALESCE(p3.price-p3.price,1) 
     ) 
     *q.quantity) as total 
FROM 
    quantities q LEFT JOIN 
    prices p1 on q.start_timestamp = p1.timestamp and p1.type_id = 1 LEFT JOIN 
    prices p2 on p2.timestamp = adddate(q.start_timestamp, interval 5 minute) and p2.type_id = 1 LEFT JOIN 
    prices p3 on p3.timestamp = adddate(q.start_timestamp, interval 10 minute) and p3.type_id = 1 
WHERE 
    q.start_timestamp between '2010-07-01 00:00:00' and '2010-07-01 23:59:59' 
GROUP BY hour(q.start_timestamp); 

EDIT2： 查询2： 这里是一个直接的改进，不同的方法，以最小的变化查询，带来了execuction时候〜我的机器上0.22秒

SELECT sql_no_cache 
MIN(`quantities`.`start_timestamp`) AS `start`, 
MAX(`quantities`.`end_timestamp`) AS `end`, 
SUM(`quantities`.`quantity` * (
    SELECT AVG(`prices`.`price`) 
    FROM `prices` 
    WHERE 
    `prices`.`timestamp` >= '2010-07-01 00:00:00' 
    AND `prices`.`timestamp` < '2010-07-02 00:00:00' 
    AND `prices`.`timestamp` >= `quantities`.`start_timestamp` 
    AND `prices`.`timestamp` < `quantities`.`end_timestamp` 
    AND `prices`.`type_id` = 1 
)) AS total 
FROM `quantities` 
WHERE `quantities`.`start_timestamp` >= '2010-07-01 00:00:00' 
AND `quantities`.`start_timestamp` < '2010-07-02 00:00:00' 
GROUP BY HOUR( `quantities`.`start_timestamp`); 

即MySQL 5.1中，我想我已阅读，在5.5这样的THI ng（合并索引）将可用于查询计划程序。另外，如果你可以让你的start_timestamp和timestamp通过外键相关联，这些外键允许这些相关的查询使用索引（但是为此你需要修改设计并建立某种时间表，然后才能引用按数量和价格）。

查询3： 最后，它做它在〜0.03秒，但最后版本应为稳健和灵活的查询2

SELECT sql_no_cache 
MIN(start), 
MAX(end), 
SUM(subtotal) 
FROM 
(
SELECT sql_no_cache 
q.`start_timestamp` AS `start`, 
q.`end_timestamp` AS `end`, 
AVG(p.`price` * q.`quantity`) AS `subtotal` 
FROM `quantities` q 
LEFT JOIN `prices` p ON p.timestamp >= q.start_timestamp AND 
         p.timestamp < q.end_timestamp AND 
         p.timestamp >= '2010-07-01 00:00:00' AND 
         p.`timestamp` < '2010-07-02 00:00:00' 
WHERE q.`start_timestamp` >= '2010-07-01 00:00:00' 
AND q.`start_timestamp` < '2010-07-02 00:00:00' 
AND p.type_id = 1 
GROUP BY q.`start_timestamp` 
) forced_tmp 
GROUP BY hour(start); 

注：不要忘记删除SQL_NO_CACHE生产中的关键字。

在上述查询中应用了许多反直觉技巧（有时在连接条件中重复的条件加速查询，有时会减慢查询速度）。对于相对简单的查询，Mysql是非常棒的小RDBMS，而且速度非常快，但是当复杂性增加时，很容易遇到上述情况。

所以在一般情况下，我申请了以下原则来设置关于查询的性能我的期望：

• 如果基本结果集< 1000行，然后查询应该做它的业务〜0.01秒（基本结果集是功能上确定结果集的行数）

在这种特殊情况下，您从少于1000行开始（所有价格和数量在一天内，精度为15分钟）应该能够计算出最终结果。

Answer 2

我不知道这是否是快，但试试这个：

SELECT 
    MIN(`quantities`.`start_timestamp`) AS `start`, 
    MAX(`quantities`.`end_timestamp`) AS `end`, 
    (`quantities`.`quantity` * AVG (`prices`.`price`) * COUNT (`prices`.`price`)) AS `total` 
FROM `quantities` 
LEFT JOIN `prices` 
    ON `prices`.`timestamp` >= `quantities`.`start_timestamp` 
    AND `prices`.`timestamp` < `quantities`.`end_timestamp` 
WHERE `quantities`.`start_timestamp` >= '2010-07-01 00:00:00' 
    AND `quantities`.`start_timestamp` < '2010-07-02 00:00:00' 
    AND `prices`.`type_id` = 1 
GROUP BY HOUR( `quantities`.`start_timestamp`); 

还比较结果，因为逻辑有点不同。

我不做SUM（quantety * AVG（价格）

我做AVG（价格）* COUNT（价格）* quantety

Answer 3

这应该返回相同的结果稍微快执行：

SELECT 
    MIN(`quantities`.`start_timestamp`) AS `start`, 
    MAX(`quantities`.`end_timestamp`) AS `end`, 
    SUM(`quantities`.`quantity` * `prices`.`price`) 
    * COUNT(DISTINCT `quantities`.`id`) 
/COUNT(DISTINCT `prices`.`id`) 
    AS total 
FROM `quantities` 
JOIN `prices` ON `prices`.`timestamp` >= `quantities`.`start_timestamp` 
    AND `prices`.`timestamp` < `quantities`.`end_timestamp` 
    AND `prices`.`type_id` = 1 
WHERE `quantities`.`start_timestamp` >= '2010-07-01 00:00:00' 
    AND `quantities`.`start_timestamp` < '2010-07-02 00:00:00' 
GROUP BY HOUR( `quantities`.`start_timestamp`); 

既然你无法计算AVG()的SUM()里面，我不得不做一些有趣的COUNT(DISTINCT)计算每quantities返回的prices数。我想知道如果T他给你同样的结果与 “真实” 数据...

使用JOIN：

+----+-------------+------------+-------+-------------------------------+-----------------+---------+------+-------+----------+----------------------------------------------+ 
| id | select_type | table  | type | possible_keys     | key    | key_len | ref | rows | filtered | Extra          | 
+----+-------------+------------+-------+-------------------------------+-----------------+---------+------+-------+----------+----------------------------------------------+ 
| 1 | SIMPLE  | quantities | range | start_timestamp,end_timestamp | start_timestamp | 8  | NULL | 89 | 100.00 | Using where; Using temporary; Using filesort | 
| 1 | SIMPLE  | prices  | ALL | timestamp,type_id    | NULL   | NULL | NULL | 36862 | 62.20 | Using where; Using join buffer    | 
+----+-------------+------------+-------+-------------------------------+-----------------+---------+------+-------+----------+----------------------------------------------+ 

与相同的查询只增加LEFT到JOIN

+----+-------------+------------+-------+-------------------+-----------------+---------+-------+-------+----------+----------------------------------------------+ 
| id | select_type | table  | type | possible_keys  | key    | key_len | ref | rows | filtered | Extra          | 
+----+-------------+------------+-------+-------------------+-----------------+---------+-------+-------+----------+----------------------------------------------+ 
| 1 | SIMPLE  | quantities | range | start_timestamp | start_timestamp | 8  | NULL | 89 | 100.00 | Using where; Using temporary; Using filesort | 
| 1 | SIMPLE  | prices  | ref | timestamp,type_id | type_id   | 4  | const | 22930 | 100.00 |            | 
+----+-------------+------------+-------+-------------------+-----------------+---------+-------+-------+----------+----------------------------------------------+ 

有趣的是LEFT可以完全消除end_timestamp作为一个可能的关键，并改变选择键这么多，使得它采取15倍，只要...

This reference page可以帮助你多一点，如果你想看看为你的连接指定索引提示

Answer 4

请记住，只是因为你有你的列索引并不一定意味着他们会跑得更快。就目前而言，所创建的索引是针对每个单独的列的，如果您仅限制一列中的数据，则会相当快地返回结果。

所以要尽量避免“使用文件排序”（你需要做的尽可能的），也许尝试以下指标：

CREATE INDEX start_timestamp_end_timestamp_id ON quantities (start_timestamp,end_timestamp,id); 

而对于价格表（结合了3个人类似的事情索引必须为1个指数更快的查找）

一个很好的资源这也解释了非常详细和如何优化索引（什么不同解释的意思，以及如何瞄准）是：http://hackmysql.com/case1