缓慢的MySQL查询 [英] Slow MySQL query

问题描述

嘿，我的MySQL查询非常慢.我敢肯定，我要做的就是添加正确的索引，但是我尝试执行的所有操作都不起作用.

Hey I have a very slow MySQL query. I'm sure all I need to do is add the correct index but all the things I try don't work.

查询是:

SELECT DATE(DateTime) as 'SpeedDate', avg(LoadTime) as 'LoadTime'
FROM SpeedMonitor
GROUP BY Date(DateTime);

查询的解释为:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  SpeedMonitor    ALL                 7259978 Using temporary; Using filesort

表结构为:

CREATE TABLE `SpeedMonitor` (
  `SMID` int(10) unsigned NOT NULL auto_increment,
  `DateTime` datetime NOT NULL,
  `LoadTime` double unsigned NOT NULL,
  PRIMARY KEY  (`SMID`)
) ENGINE=InnoDB AUTO_INCREMENT=7258294 DEFAULT CHARSET=latin1;

任何帮助将不胜感激.

推荐答案

您只是在查询中要求两列，因此索引可以/应该去那里:

You're just asking for two columns in your query, so indexes could/should go there:

• DateTime
• LoadTime

加快查询速度的另一种方法是将DateTime字段分为两个部分:日期和时间.
这样，db可以直接在日期字段上进行分组，而不用计算DATE(...).

Another way to speed your query up could be split DateTime field in two: date and time.
This way db can group directly on date field instead of calculating DATE(...).

已编辑:
如果您更喜欢使用触发器，请创建一个新列(DATE)并将其命名为 newdate ，然后尝试使用此操作(我现在无法尝试查看它是否正确):

EDITED:
If you prefer using a trigger, create a new column(DATE) and call it newdate, and try with this (I can't try it now to see if it's correct):

CREATE TRIGGER upd_check BEFORE INSERT ON SpeedMonitor
FOR EACH ROW
BEGIN
  SET NEW.newdate=DATE(NEW.DateTime);
END

再次
我刚刚用相同的表speedmonitor创建了一个数据库，其中填充了大约900,000条记录.
然后我运行查询S ELECT newdate,AVG(LoadTime) loadtime FROM speedmonitor GROUP BY newdate，大约花了100秒钟！！
删除newdate字段上的索引(并使用RESET QUERY CACHE和FLUSH TABLES清除缓存)，相同的查询花费了0.6s！ 只是为了比较:查询SELECT DATE(DateTime),AVG(LoadTime) loadtime FROM speedmonitor GROUP BY DATE(DateTime)用了0.9秒.
因此，我认为newdate上的索引不好:将其删除.
我将添加尽可能多的记录，然后再次测试两个查询.

EDITED AGAIN:
I've just created a db with the same table speedmonitor filled with about 900,000 records.
Then I run the query SELECT newdate,AVG(LoadTime) loadtime FROM speedmonitor GROUP BY newdate and it took about 100s!!
Removing index on newdate field (and clearing cache using RESET QUERY CACHE and FLUSH TABLES), the same query took 0.6s!!!
Just for comparison: query SELECT DATE(DateTime),AVG(LoadTime) loadtime FROM speedmonitor GROUP BY DATE(DateTime) took 0.9s.
So I suppose that the index on newdate is not good: remove it.
I'm going to add as many records as I can now and test two queries again.

最终
删除newdate和DateTime列上的索引，在speedmonitor表上具有 8百万条记录，结果如下:

FINAL
Removing indexes on newdate and DateTime columns, having 8mln records on speedmonitor table, here are results:

• 在newdate列上进行选择和分组: 7.5s
• 在DATE(DateTime)字段上进行选择和分组: 13.7s

• selecting and grouping on newdate column: 7.5s
• selecting and grouping on DATE(DateTime) field: 13.7s

我认为这是一个很好的加速方法.
在mysql命令提示符下执行查询需要花费时间.

I think it's a good speedup.
Time is taken executing query inside mysql command prompt.

这篇关于缓慢的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！