MySQL 查询速度慢

发布于 2024-10-31 18:21:47 字数 711 浏览 3 评论 0原文

嘿，我的 MySQL 查询非常慢。我确信我需要做的就是添加正确的索引，但我尝试的所有操作都不起作用。

查询是：

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;

任何帮助将不胜感激。

原文

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.

The query is:

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

The Explain for the query is:

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

And the table structure is:

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;

Any help would be greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拧巴小姐 2024-11-07 18:21:47

您只需要在查询中输入两列，因此索引可以/应该放在那里：

DateTime
LoadTime

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

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

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

再次编辑：
我刚刚创建了一个数据库，其中包含相同的表 speedmonitor，其中包含大约 900,000 条记录。
然后我运行查询 SELECT newdate,AVG(LoadTime) loadtime FROM speedmonitor GROUP BY newdate 大约花费了 100 秒！！
删除 newdate 字段上的索引（并使用 RESET QUERY CACHE 和 FLUSH TABLES 清除缓存），相同的查询花费了 0.6 秒！！！
仅用于比较：查询 SELECT DATE(DateTime),AVG(LoadTime) loadtime FROM speedmonitor GROUP BY DATE(DateTime) 花费了 0.9 秒。
所以我认为 newdate 上的索引不好：将其删除。
我现在将添加尽可能多的记录并再次测试两个查询。

最终编辑：
删除 newdate 和 DateTime 列上的索引，在 speedmonitor 表上有 800 万条记录，结果如下：

在 newdate 列上选择和分组：7.5s
在 DATE(DateTime) 上选择和分组字段：13.7s

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

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

DateTime
LoadTime

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(...).

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

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.

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