如何在mysql查询中提高查询速度
我正在尝试尽可能优化我的查询速度。一个小问题是，我看不到确切的查询速度，因为它是舍入到一整秒。查询确实得到了预期的结果，大约需要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
- apx_id，日期，日期，period_from，period_until，日期
强大的交易
contract_id
- 1,2019-05-15,2018-01-01,2018-12-31,1,5,08:00:00,20:00:00,1000,50,3,1
、、
- 、deal_id、date_deal、start_date、end_date、weekday_from、weekday_until、period_from、period_until、hourly_volume、价格、type_deal_id、price
电力负载
- powerload_id，ean、date、period_from、period_until、date
eans_power
- ean，邮局，街道，contract_id，街道，number，号码，affix，邮局，，道路，14，postal，
- ，城市
结果，不带sum()和group by：
小时卷日期，时间，价格，电源，卷，d.hourly_
- /4，
- 2018-01-01,00:00:00,27.20,9,244.80，NULL
- 2018-01-01,00:15:00,27.20,11,299.20，空
结果，带有sum()和group by：
SUM(d.hourly_volume/4)
- 2018-01-01,08:00:00,26.33,21,46193.84,12250.0000
- 2018-01-02，08:00:00,47.95,43,90623.98,12250.0000
、
- DATE、PERIOD_FROM、PRICE、POWERLOAD、SUM(a.PRICE*l.POWERLOAD)、PRICE
回答 1
Stack Overflow用户
发布于 2019-05-23 06:05:09
初步优化：
- 使用InnoDB，而不是MyISAM。
- 仅对固定长度字符串使用
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
复制相似问题