联合快速MySQL查询的性能下降
问题描述:
我有两个查询在单独运行时运行得非常快,但是当通过使用其中一个作为子查询进行组合时,性能会急剧下降。联合快速MySQL查询的性能下降
FAST QUERY I:
SELECT DISTINCT p.products_image,
p.products_subimage1,
pd.products_name,
p.products_quantity,
p.products_model,
p.products_ordered,
p.products_id,
p.products_price,
p.products_weight,
p.products_length,
p.products_width,
p.products_height,
p.products_tax_class_id,
p.products_status,
IF(s.status, s.specials_new_products_price, NULL) AS
specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) AS
final_price
FROM products p
LEFT JOIN specials s
ON p.products_id = s.products_id
LEFT JOIN products_to_categories p2c
ON p.products_id = p2c.products_id
LEFT JOIN products_description pd
ON p.products_id = pd.products_id
LEFT JOIN products_attributes pa
ON p.products_id = pa.products_id
LEFT JOIN (SELECT `products_id`,
`products_stock_attributes`,
`products_stock_quantity`
FROM products_stock
WHERE `products_stock_quantity` > 0) t
ON t.products_id = p.products_id
WHERE t.products_stock_attributes IN ('1-31', '1-25', '1-18', '1-7')
AND p.products_status = '1'
AND Date_sub(Curdate(), INTERVAL 30 day) >= p.products_date_added
AND pa.options_values_id IN (31, 25, 18, 7)
GROUP BY p.products_id
HAVING Count(DISTINCT pa.options_values_id) = 4
AND Count(DISTINCT t.products_stock_attributes) = 4
FAST QUERY II:
SELECT sul2.*
FROM stock_update_log sul2
INNER JOIN (SELECT products_id,
Max(date_time) AS maxDateTime
FROM stock_update_log
WHERE stock_qty_change > 1
AND id > 154700
AND Date_sub(Curdate(), INTERVAL 360 day)<= From_unixtime(date_time)
GROUP BY products_id)gsul
ON sul2.products_id = gsul.products_id
AND sul2.date_time = gsul.maxdatetime
合并查询(非常慢的):
SELECT DISTINCT p.products_image,
p.products_subimage1,
pd.products_name,
p.products_quantity,
p.products_model,
p.products_ordered,
p.products_id,
p.products_price,
sul.date_time,
p.products_weight,
p.products_length,
p.products_width,
p.products_height,
p.products_tax_class_id,
p.products_status,
IF(s.status, s.specials_new_products_price, NULL) AS
specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) AS
final_price
FROM products p
LEFT JOIN specials s
ON p.products_id = s.products_id
LEFT JOIN products_to_categories p2c
ON p.products_id = p2c.products_id
LEFT JOIN products_description pd
ON p.products_id = pd.products_id
LEFT JOIN (SELECT sul2.*
FROM stock_update_log sul2
INNER JOIN (SELECT products_id,
Max(date_time) AS maxDateTime
FROM stock_update_log
WHERE stock_qty_change > 1
AND id > 154700
AND Date_sub(Curdate(),
INTERVAL 360 day)
<=
From_unixtime(date_time)
GROUP BY products_id)gsul
ON sul2.products_id = gsul.products_id
AND sul2.date_time = gsul.maxdatetime) sul
ON p.products_id = sul.products_id
LEFT JOIN products_attributes pa
ON p.products_id = pa.products_id
LEFT JOIN (SELECT `products_id`,
`products_stock_attributes`,
`products_stock_quantity`
FROM products_stock
WHERE `products_stock_quantity` > 0) t
ON t.products_id = p.products_id
WHERE t.products_stock_attributes IN ('1-31', '1-25', '1-18', '1-7')
AND p.products_status = '1'
AND Date_sub(Curdate(), INTERVAL 360 day) <= From_unixtime(sul.date_time)
AND Date_sub(Curdate(), INTERVAL 30 day) >= p.products_date_added
AND sul.id > 154700
AND sul.stock_qty_change > 1
AND pa.options_values_id IN (31, 25, 18, 7)
GROUP BY p.products_id
HAVING Count(DISTINCT pa.options_values_id) = 4
AND Count(DISTINCT t.products_stock_attributes) = 4
ORDER BY sul.date_time DESC
我一直在尝试几个小时,弄清楚为什么它结合他们这么慢,并试图重写组合查询无济于事,所以我伸出援手来自这里的专家。
什么原因导致它变得如此缓慢,我该怎么做才能使它变得更快?
答
查询II感觉它有一百万行;得到的tmp表没有索引;它隐藏在LEFT JOIN
的后面。尽管如此,优化器似乎足够聪明,可以从混乱开始,并为联合查询提供7904行。
你能避免LEFT
?
然后代码继续进行,但最终必须从表扫描中检查所有这些行与18057行。优化器再一次通过使用“连接缓冲区”来做一件聪明的事情。尽管如此,7904 * 923 * 18057还是有很多行。
这似乎是邪恶部分:
LEFT JOIN
(SELECT `products_id`,
`products_stock_attributes`,
`products_stock_quantity`
FROM products_stock
WHERE `products_stock_quantity` > 0
) t
ON t.products_id = p.products_id
WHERE t.products_stock_attributes IN ('1-31', '1-25', '1-18', '1-7')
所有这些似乎是红旗:(?)
- 不必要
LEFT
; - “where attributes in ...”在子查询之外时,它们可能在里面;
- 你或许可以将
HAVING Count(DISTINCT t.products_stock_attributes) = 4
折叠到子查询中。
是的,将'WHERE t.products_stock_attributes IN'和'HAVING Count(DISTINCT t.products_stock_attributes)= 4'移入子查询解决了这个问题。非常感谢!! :) – CreativeMind