我正在尝试尽可能优化我的查询速度。一个小问题是,我看不到确切的查询速度,因为它是舍入到一整秒。查询确实得到了预期的结果,大约需要1秒。最后的查询应该扩展得更多,因此我正在努力改进它。如何改进这个查询?
该数据库被构建为电力公用事业公司。查询最终应该会计算出发票。我主要有4个表,APX价格,电力交易,电力负载,eans_power。
APX电价为小时电价,电力负荷为季度小时电价。第一步是将这两个连接在一起,每一刻钟。
第二步是,我当前选择了表eans_power中指示的EAN。
最后,我将加入Powerdeals,这些Powerdeals目前只包含一行,并指明从哪个小时开始,到哪个小时,以及从/直到它应该适用的工作日。它由每小时的数量和价格组成。目前它只在小时加入,但它也将扩展到工作日。
MYSQL查询:
SELECT l.DATE, l.PERIOD_FROM, a.PRICE, l.POWERLOAD,
SUM(a.PRICE*l.POWERLOAD), SUM(d.hourly_volume/4)
FROM timeseries.powerload l
INNER JOIN timeseries.apxprice a ON l.DATE = a.DATE
INNER JOIN contracts.eans_power c ON l.ean = c.ean
LEFT OUTER JOIN timeseries.powerdeals d ON d.period_from <= l.period_from
AND d.period_until >= l.period_until
WHERE l.PERIOD_FROM >= a.PERIOD_FROM
AND l.PERIOD_FROM < a.PERIOD_UNTIL
AND l.DATE >= '2018-01-01'
AND l.DATE <= '2018-12-31'
GROUP BY l.date
解释:
1 SIMPLE c NULL system PRIMARY,ean NULL NULL NULL 1 100.00 Using temporary; Using filesort
1 SIMPLE l NULL ref EAN EAN 21 const 35481 11.11 Using index condition
1 SIMPLE d NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE a NULL ref DATE DATE 4 timeseries.l.date 24 11.11 Using index condition
Create table查询:
apxprice
CREATE TABLE `apxprice` (
`apx_id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`period_from` time DEFAULT NULL,
`period_until` time DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`apx_id`),
KEY `DATE` (`date`,`period_from`,`period_until`)
) ENGINE=MyISAM AUTO_INCREMENT=29664 DEFAULT CHARSET=latin1
强大的交易
CREATE TABLE `powerdeals` (
`deal_id` int(11) NOT NULL AUTO_INCREMENT,
`date_deal` date NOT NULL,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
`weekday_from` int(11) NOT NULL,
`weekday_until` int(11) NOT NULL,
`period_from` time NOT NULL,
`period_until` time NOT NULL,
`hourly_volume` int(11) NOT NULL,
`price` int(11) NOT NULL,
`type_deal_id` int(11) NOT NULL,
`contract_id` int(11) NOT NULL,
PRIMARY KEY (`deal_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
电力负载
CREATE TABLE `powerload` (
`powerload_id` int(11) NOT NULL AUTO_INCREMENT,
`ean` varchar(18) DEFAULT NULL,
`date` date DEFAULT NULL,
`period_from` time DEFAULT NULL,
`period_until` time DEFAULT NULL,
`powerload` int(11) DEFAULT NULL,
PRIMARY KEY (`powerload_id`),
KEY `EAN` (`ean`,`date`,`period_from`,`period_until`)
) ENGINE=MyISAM AUTO_INCREMENT=61039 DEFAULT CHARSET=latin1
eans_power
CREATE TABLE `eans_power` (
`ean` char(19) NOT NULL,
`contract_id` int(11) NOT NULL,
`invoicing_id` int(11) NOT NULL,
`street` varchar(255) NOT NULL,
`number` int(11) NOT NULL,
`affix` char(11) NOT NULL,
`postal` char(6) NOT NULL,
`city` varchar(255) NOT NULL,
PRIMARY KEY (`ean`),
KEY `ean` (`ean`,`contract_id`,`invoicing_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
示例数据表
apx_prices
date
强大的交易
contract_id
、、
电力负载
eans_power
结果,不带sum()和group by:
小时卷日期,时间,价格,电源,卷,d.hourly_
结果,带有sum()和group by:
SUM(d.hourly_volume/4)
、
发布于 2019-05-23 06:05:09
初步优化:
CHAR
ean
)要获得使用time- to -second的替代方案,请查看。
因为范围测试(比如l.PERIOD_FROM >= a.PERIOD_FROM AND l.PERIOD_FROM < a.PERIOD_UNTIL
)基本上是不可能优化的,所以我建议您将表扩展为每小时一个条目(如果需要,也可以是每季度一个条目)。通过键查找行比扫描“所有”表要快得多。一整年的9K行是微不足道的。
当您通过这些建议(和注释)时,我将获得更多优化索引的技巧,特别是InnoDB的PRIMARY KEY
。
https://stackoverflow.com/questions/56264517
复制相似问题