Question

我很抱歉。我确信,我需要做的是增加正确的指数,但我所尝试的所有事情都没有工作。

问题是:

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

表结构是:

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;

任何帮助都将受到高度赞赏。

Answer 1

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:

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

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

Answer 2

问题是,你重新使用你<代码>中的功能。按条款分类的组别,因此,MySQL必须在每一记录上评价<代码>Date(Datetime),然后才能将结果分类。 d 建议在<代码>Date(Datetime)上添加一个计算出来的领域,然后可以确定,看这是否有助于你的工作。

Answer 3

我希望你能够指出,在你提出编制数百万份记录表之前,你应认真考虑如何使用这一数据并作出相应的规划。

现在发生的情况是,你的询问不能使用任何指数,因此无法扫描整个表格,从而形成对策。处理相对较大的表格的方式并非最快。

You have some things to consider if you want to get to a better state:

How fast is it collecting data?
How much history do you need?
How granular are your reporting requirements?
Are you able to suspend logging to make table changes?

If the answer is "No" to the last question you could always create a new table/solution and start writing records there... importing in old data if/as needed.

报告精度非常重要,例如,你可以把每天价值的数据压缩为24个记录。将目前时间排在指数上,然后按每小时平均处理。每个装货表以样本日期为基础,可以删除处理过的旧表格。

当然,每小时可能不会被罚款。

视您的留用需要,您可能希望考虑某种分门别类的储存。这可以让你对抽样数据子集提出质询,简单地说,如果旧的分门槛没有足够长的可用性,就予以放弃或存档。

Anyhow, you seem to be on the edge of having some type of massive sampling, reporting and/or monitoring system (particularly if you were reporting on a variety of sites or pages with different characteristics). You may want to put some effort into designing this so it will fit your needs... ;)

友情链接