MySQL查询优化
请你帮我优化这个查询。我花了很多时间,并且仍然无法将其改写成足够快的速度(比如说运行在秒钟之内,而不是现在的分钟)。MySQL查询优化
查询:
SELECT m.my_id, m.my_value, m.my_timestamp
FROM (
SELECT my_id, MAX(my_timestamp) AS most_recent_timestamp
FROM my_table
WHERE my_timestamp < '2011-03-01 08:00:00'
GROUP BY my_id
) as tmp
LEFT OUTER JOIN my_table m
ON tmp.my_id = m.my_id AND tmp.most_recent_timestamp = m.my_timestamp
ORDER BY m.my_timestamp;
MY_TABLE定义如下:
CREATE TABLE my_table (
my_id INTEGER NOT NULL,
my_value VARCHAR(4000),
my_timestamp TIMESTAMP default CURRENT_TIMESTAMP NOT NULL,
INDEX MY_ID_IDX (my_id),
INDEX MY_TIMESTAMP_IDX (my_timestamp),
INDEX MY_ID_MY_TIMESTAMP_IDX (my_id, my_timestamp)
);
该查询的目标是选择最近的my_value
每个my_id
一些时间戳之前。 my_table
包含约1亿个条目,并且需要大约8分钟才能完成。
解释:
+----+-------------+-------------+-------+------------------------------------------------+-------------------------+---------+---------------------------+-------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+------------------------------------------------+-------------------------+---------+---------------------------+-------+---------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 90721 | Using temporary; Using filesort | | 1 | PRIMARY | m | ref | MY_ID_IDX,MY_TIMESTAMP_IDX,MY_ID_TIMESTAMP_IDX | MY_TIMESTAMP_IDX | 4 | tmp.most_recent_timestamp | 1 | Using where | | 2 | DERIVED | my_table | range | MY_TIMESTAMP_IDX | MY_ID_MY_TIMESTAMP_IDX | 8 | NULL | 61337 | Using where; Using index for group-by | +----+-------------+-------------+-------+------------------------------------------------+-----------------------+---------+---------------------------+------+---------------------------------------+
我注意到在解释计划中,优化器使用MY_ID_MY_TIMESTAMP_IDX索引为子查询,但不是外部查询。
您可以使用索引提示进行加速。我还更新了ON子句以使用其别名引用tmp.most_recent_timestamp。
SELECT m.my_id, m.my_value, m.my_timestamp
FROM (
SELECT my_id, MAX(my_timestamp) AS most_recent_timestamp
FROM my_table
WHERE my_timestamp < '2011-03-01 08:00:00'
GROUP BY my_id
) as tmp
LEFT OUTER JOIN my_table m use index (MY_ID_MY_TIMESTAMP_IDX)
ON tmp.my_id = m.my_id AND tmp.most_recent_timestamp = m.my_timestamp
ORDER BY m.my_timestamp;
@IKE,我纠正了这个查询。在准备SCCE时错过了“group by”声明。问题是我必须为每个'my_id'获取“最新的时间戳”。 – 2011-03-02 14:55:12
Gotcha。我会更新我的答案以反映这一点。 – 2011-03-02 16:08:41
@Ike,不幸的是我自己尝试过,但这并没有改变优化器的行为。据我了解这是一个MySQL功能(http://www.mysqlperformanceblog.com/2006/08/31/derived-tables-and-views-performance/)。在这一点上,我认为仍然可以在不创建临时表或视图的情况下调整查询。 – 2011-03-03 14:57:11
一招得到一个最新记录可以一起与“限1”连同"self" join
财产以后这样的(未测试)使用order by
,而不是max aggregation
:
SELECT m.my_id, m.my_value, m.my_timestamp
FROM my_table m
WHERE my_timestamp < '2011-03-01 08:00:00'
ORDER BY m.my_timestamp DESC
LIMIT 1
;
更新以上不会因为分组工作是必需的...
其他解决方案具有WHERE-IN-SubSelect而不是您使用的JOIN。
可能会更快。请用您的数据进行测试。
SELECT m.my_id, m.my_value, m.my_timestamp
FROM my_table m
WHERE (m.my_id, m.my_timestamp) IN (
SELECT i.my_id, MAX(i.my_timestamp)
FROM my_table i
WHERE i.my_timestamp < '2011-03-01 08:00:00'
GROUP BY i.my_id
)
ORDER BY m.my_timestamp;
我们需要所有最近的对(id,value)不仅是最近的一对。 – 2011-03-02 14:53:23
拉斯维加斯不是优化,但实际上降低了性能。根据:http://www.mysqlperformanceblog.com/2010/10/25/mysql-limitations-part-3-subqueries/ – 2011-03-05 16:51:18
如果我理解正确的话,你应该能够删除嵌套的选择完全由my_timestamp下降where子句移动到主查询,订单和限制1.
SELECT my_id, my_value, max(my_timestamp)
FROM my_table
WHERE my_timestamp < '2011-03-01 08:00:00'
GROUP BY my_id
*编辑 - 增加了最大和组
将ORDER BY更改为DESC,这是完美的。 – 2011-03-02 14:38:22
唯一的问题是我们需要所有'my_id'的最新条目。我认为这个查询只产生一个结果。 – 2011-03-02 14:51:04
@Alex:你想要多少结果? – 2011-03-02 14:57:49
您确定这是您发布的查询的查询计划吗?该计划提到表“nv”,但查询中没有这样的表。该查询可能不是正确的,因为子选择中的my_id的值可能不是(实际上不可能是)my_timestamp = MAX(my_timestamp)所在行的id。 – outis 2011-03-02 14:26:49
哪个版本的mysql?以及为什么表名1被删除。 – Zimbabao 2011-03-02 14:27:58
不应该将您的连接条件设为'... AND tmp.most_recent_timestamp = m.my_timestamp ...'?内部查询也看起来缺少一个“GROUP BY”。 – 2011-03-02 14:32:28