mysql - 需要帮助让我的 mysql 查询更快

Question

有人可以帮助我或向我解释如何使我的查询更快吗？此查询花费了近 10 秒(在我的本地计算机上)，数据量接近 1GB。

这是我的解释和解释结果

    explain select p.delivery_date, p.delivery_hour, p.resource_id, p.participant_id, p.price, p.date_posted, hour(p.date_posted) as hour_date_posted, date(p.date_posted) as date_date_posted
    ,s.mw
from prices_report as p
left join schedules_report s
on s.delivery_date = p.delivery_date
                    AND s.type_id = p.type_id
            and s.delivery_hour = p.delivery_hour
                    and s.resource_id = p.resource_id
                    and s.participant_id = p.participant_id
                    and hour(s.date_posted) = hour(p.date_posted)
                    and date(s.date_posted) = date(p.date_posted)
WHERE p.delivery_date = '2012-05-22'
AND p.type_id = 'GEN'
ORDER BY p.delivery_date, p.resource_id, p.delivery_hour, p.participant_id, p.type_id, p.date_posted

解释结果:
id:1
选择类型:简单
表:p
类型:引用
可能的键:idx1
key :idx1
key_len : 4
引用:常量
行:40258
额外:使用 where

id:1
选择类型:简单
表:s
类型:引用
可能的键:idx1
key :idx1
key_len:63
ref : const,APC_DB.p.delivery_hour,APC_DB.p.participant_id,APC_DB.p.resource_id,const
行数:99
额外:

表结构:

  CREATE TABLE `prices_report` (
  `id` int(11) NOT NULL auto_increment,
  `delivery_date` date default NULL,
  `delivery_hour` int(2) default NULL,
  `participant_id` varchar(10) default NULL,
  `resource_id` varchar(15) default NULL,
  `type_id` varchar(10) default NULL,
  `price` float default NULL,
  `date_posted` datetime NOT NULL,
  `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `IDX1` USING BTREE (`delivery_date`,`resource_id`,`delivery_hour`,`participant_id`,`type_id`,`date_posted`)
) ENGINE=MyISAM AUTO_INCREMENT=5261441 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;


CREATE TABLE `schedules_report` (
  `id` int(11) NOT NULL auto_increment,
  `delivery_date` date default NULL,
  `delivery_hour` int(2) default NULL,
  `participant_id` varchar(15) default NULL,
  `resource_id` varchar(20) default NULL,
  `type_id` varchar(10) default NULL,
  `mw` float default NULL,
  `loss_factor` float default NULL,
  `date_posted` datetime NOT NULL,
  `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `IDX1` USING BTREE (`delivery_date`,`delivery_hour`,`participant_id`,`resource_id`,`type_id`,`date_posted`)
) ENGINE=MyISAM AUTO_INCREMENT=43369 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

万分感谢

Answer 1

问题似乎是“模糊匹配”:

LEFT JOIN ... ON ...
and hour(s.date_posted) = hour(p.date_posted)
and date(s.date_posted) = date(p.date_posted)

每次触及 p 中的行时，强制 MySQL 计算 s 中所有行的hour(s.date_posted) 和 date(s.date_posted)。

尝试一下:

and s.date_posted
  BETWEEN DATE_SUB(p.date_posted, INTERVAL TIME_TO_SEC(MAKETIME(0,MINUTE(p.date_posted),SECOND(p.date_posted))) SECOND)
  AND DATE_ADD(DATE_SUB(p.date_posted, INTERVAL TIME_TO_SEC(MAKETIME(0,MINUTE(p.date_posted),SECOND(p.date_posted))) SECOND), INTERVAL 1 HOUR)

编辑:

如果您可以接受闰秒计算错误的情况，可以将其编写为更易于阅读的代码

and s.date_posted
  BETWEEN DATE_SUB(p.date_posted, INTERVAL 60*MINUTE(p.date_posted)+SECOND(p.date_posted) SECOND)
  AND DATE_ADD(DATE_SUB(p.date_posted, INTERVAL 60*MINUTE(p.date_posted)+SECOND(p.date_posted) SECOND), INTERVAL 1 HOUR)

编辑 2:在 BETWEEN 的上限中重复部分计算值是有目的的:MySQL 只会计算一次。

编辑3:现在看到您的 SHOW CREATE TABLE，我知道您在 date_posted 上没有单独的索引，而只是一个组合索引。您可能想尝试一下

ALTER TABLE `schedules_report` ADD INDEX(date_posted)