mysql - 优化包含操作和子查询的 SQL 查询

Question

我正在尝试加快 SQL 查询的速度，该查询返回由一些零售商销售的具有给定特征集(宽度、高度、直径和负载)的 15 种最便宜的产品。 de DB 中约有 30 万种产品。总价计算如下:

totalPrice = quantity*(price - discount*price) + shippingCost

地点:

• 折扣(百分比)取决于订购数量、零售商和时间。由于同一组合中可能存在多种折扣，因此将选择最有利的百分比。
• 运费取决于订购数量、一些产品特性以及产品必须交付的目的地

我的问题是，discount 和 shippingsCost 依赖于太多参数来存储每个总价格组合，以便使查询运行得更快。因此，我认为我陷入了子查询的困境。

<小时/>

这是 SQL 查询的简化版本，其中产品数量设置为 2。

SELECT `P`.*, `B`.`name_local` as brandName, `R`.`name` as retailerName, `D`.`amount` as discount,     `S`.`shippingCost`, ROUND(P.price * 2 + IFNULL(S.shippingCost, 0) - IFNULL(P.price * D.amount / 100 * 2, 0), 2 ) as totalPrice
FROM (`Product` P)
JOIN `Brand` B ON `B`.`id` = `P`.`idBrand`
JOIN `Retailer` R ON `R`.`id` = `P`.`idRetailer`
LEFT JOIN `Shipping` S ON `S`.`idRetailer` = `P`.`idRetailer` AND S.nbProduct = (SELECT nbProduct FROM `Shipping` WHERE nbProduct <= 2 ORDER BY nbProduct DESC LIMIT 1)
LEFT JOIN `Discount` D ON `D`.`idRetailer` = `P`.`idRetailer` AND D.amount = (SELECT MAX(amount) FROM Discount D WHERE (D.vehicle = P.vehicle OR D.vehicle = 0) AND D.idRetailer = P.idRetailer AND D.start <= 1451825895 AND D.end >=1451825895)
WHERE `width` =  '195'
AND `height` =  '65'
AND `diameter` =  '15'
AND `load` >= 0
ORDER BY `totalPrice` ASC
LIMIT 15  

我使用的是 mysql 14.14。在我的机器上，执行查询大约需要 150 毫秒。通过避免使用当前时间戳进行折扣，可以更好地利用 mysql 查询缓存。但是，查询第一次执行需要相当长的时间，并且很快就会从查询缓存中刷新(由于组合很多)。以下是查询的 explain 命令的结果:

+----+--------------------+----------+--------+-----------------------------------------------+------------+---------+------------------------+-------+----------------------------------------------+
| id | select_type        | table    | type   | possible_keys                                 | key        | key_len | ref                    | rows  | Extra                                        |
+----+--------------------+----------+--------+-----------------------------------------------+------------+---------+------------------------+-------+----------------------------------------------+
|  1 | PRIMARY            | P        | ref    | idBrand,idRetailer,width,height,diameter,load | width      | 12      | const,const,const      | 13268 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | S        | ref    | idRetailer                                    | idRetailer | 4       | mydb.P.idRetailer      |     1 | Using where                                  |
|  1 | PRIMARY            | B        | eq_ref | PRIMARY                                       | PRIMARY    | 4       | mydb.P.idBrand         |     1 |                                              |
|  1 | PRIMARY            | R        | eq_ref | PRIMARY                                       | PRIMARY    | 4       | mydb.P.idRetailer      |     1 |                                              |
|  1 | PRIMARY            | D        | ref    | idRetailer                                    | idRetailer | 4       | mydb.S.idRetailer      |     1 |                                              |
|  3 | DEPENDENT SUBQUERY | D        | ref    | idRetailer,start                              | idRetailer | 4       | mydb.P.idRetailer      |     1 | Using where                                  |
|  2 | SUBQUERY           | Shipping | ALL    | NULL                                          | NULL       | NULL    | NULL                   |    48 | Using where; Using filesort                  |
+----+--------------------+----------+--------+-----------------------------------------------+------------+---------+------------------------+-------+----------------------------------------------+

有没有一种优雅的方法来加速这种查询，或者我唯一的方法是提高查询缓存的效果(添加 RAM、增加缓存大小等)？

Answer 1

对于第二个(更复杂的子查询)，尝试使用计算出的折扣进行 LEFT JOIN 以使其仅执行一次。像这样

...
LEFT JOIN (SELECT MAX(amount) as amount, D.vehicle, D.idRetailer 
FROM Discount D 
WHERE D.start <= 1451825895 
   AND D.end >=1451825895
GROUP BY D.idRetailer, D.vehicle) D ON D.`idRetailer` = `P`.`idRetailer` 
                                 AND (D.vehicle = P.vehicle OR D.vehicle = 0)