如何提高列求和的性能

Question

我正在尝试调整我的查询，但无法继续。有机会对这个查询进行更多调整吗？尤其是SUM子查询。

索引：db_prices.date

原始查询示例：

SELECT
db_villas.id,
db_villas.title1,
db_specials.id AS sid,
db_specials.title1 AS stitle,
db_cities.name AS cityName,
db_counties.name AS countyName,
db_assets.path,
db_villas.bathroom,
db_villas.bedroom,
db_villas.guest,
db_prices.date,
(SELECT SUM(db_prices.price) FROM db_prices WHERE db_prices.date BETWEEN "2016-08-01" AND "2016-09-30" AND db_prices.villaId=db_villas.id) AS price
FROM
db_villas
INNER JOIN db_cities ON db_villas.cityId = db_cities.id
LEFT OUTER JOIN db_specials ON db_villas.specialId = db_specials.id
INNER JOIN db_counties ON db_counties.cityid = db_cities.id AND db_villas.countyId = db_counties.id
INNER JOIN db_assets ON db_assets.guid = db_villas.guid
INNER JOIN db_villafacilities ON db_villafacilities.villaId = db_villas.id
INNER JOIN db_prices ON db_prices.villaId = db_villas.id
WHERE db_prices.date BETWEEN "2016-08-01" AND "2016-09-30" AND db_assets.isMainImage=1 AND db_villas.minRent <= 7
GROUP BY db_villas.id
HAVING (SELECT COUNT(*) FROM db_prices WHERE date BETWEEN "2016-08-01" AND "2016-09-30" AND db_prices.isFree = 0 AND db_prices.villaId = db_villas.id)=0

上面的查询在1.2秒内执行。

当我删除时

(SELECT SUM(db_prices.price) FROM db_prices WHERE db_prices.date BETWEEN "2016-08-01" AND "2016-09-30" AND db_prices.villaId=db_villas.id) AS price

子查询执行时间减少到0.009秒。

如果我只是删除这个部分

AND db_prices.villaId=db_villas.id

从子查询中，它仍然在0.009秒内执行。

Answer 1

MySQL(自v5.7起)有一个查询计划器，它不具备转换依赖子查询的能力

(SELECT SUM(db_prices.price) 
FROM db_prices
WHERE db_prices.date BETWEEN "2016-08-01" AND "2016-09-30"
AND db_prices.villaId=db_villas.id) AS price

转换为可连接的独立子查询。这意味着查询计划器最终会多次运行该查询，占用时间。所以你需要自己改变它。独立子查询将如下所示：

SELECT villaId, 
SUM(price) price,  
SUM(CASE WHEN isFree = 0 THEN 1 ELSE 0 END) not_free_count
FROM db_prices
WHERE date BETWEEN  '2016-08-01' AND '2016-09-30'
GROUP BY villaId

此查询为您提供每个别墅的价格总和，以及未标记为"免费"的别墅数量。这很方便，因为您现在可以将其连接到表的其余部分。像这样：

SELECT db_villas.id,
db_villas.title1, etc etc,
price_summary.price
FROM db_villas
INNER JOIN db_cities ON db_villas.cityId = db_cities.id
LEFT OUTER JOIN db_specials ON db_villas.specialId = db_specials.id
etc etc.
LEFT JOIN (
SELECT villaId, 
SUM(price) price,  
SUM(CASE WHEN isFree = 0 THEN 1 ELSE 0 END) not_free_count
FROM db_prices
WHERE date BETWEEN  '2016-08-01' AND '2016-09-30'
GROUP BY villaId
) price_summary ON db_villas.villaId = price_summmary.villaId
WHERE db_prices.date BETWEEN "2016-08-01" AND "2016-09-30"
AND etc etc
AND price_summary.not_free_count = 0
GROUP BY db_villas.villaId

然后，您需要db_prices(date, villaId, price, isFree)上的复合索引来优化子查询。您可能还需要对其他表的某些其他列进行索引。

专业提示：在加快查询速度方面，许多单列索引无法替代复合索引。单独索引大量列是一种常见且臭名昭著的反模式。阅读以下内容：http://use-the-index-luke.com/

专业提示：您的查询正在使用GROUP BY的非标准MySQL扩展。在您可能很快就会拥有的MySQL版本中，除非您更改一些服务器设置，否则这将停止工作。阅读以下内容：https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Answer 2

要加速此子查询：

(SELECT SUM(p.price)
FROM db_prices p
WHERE p.date BETWEEN '2016-08-01' AND '2016-09-30' AND 
p.villaId = db_villas.id
) AS price

你想要一个索引。最好的索引是覆盖索引，这些列按以下顺序排列：db_prices(villaId, date, price)。覆盖索引包括子查询中的所有列。

列villaId应该是第一个，因为它具有相等条件；则CCD_ 5，因为它也在CCD_。最后，price在索引中只是为了提高处理效率——所有列都在索引中，因此引擎不需要在数据页中查找值。

Answer 3

尝试首先生成聚合并将其与表连接

SELECT
db_villas.id,
db_villas.title1,
db_specials.id AS sid,
db_specials.title1 AS stitle,
db_cities.name AS cityName,
db_counties.name AS countyName,
db_assets.path,
db_villas.bathroom,
db_villas.bedroom,
db_villas.guest,
db_prices.date,
pricesum
FROM (SELECT db_prices.villaId, SUM(db_prices.price) as pricesum FROM db_prices WHERE db_prices.date BETWEEN "2016-08-01" AND "2016-09-30"  group by db_prices.villaId) as prices
INNER JOIN  db_villas ON prices.villaId = db_villas.id
...

有时这会有所帮助。

EDIT更正了一些复制错误

Answer 4

创建一个多列(复合)索引，这将解决问题

要做到这一点，请点击以下查询

create index <some_name> on db_prices(date,villaId);

如果您面临进一步的问题，请张贴您的解释声明以进行进一步调查