优化查询-mysql

jaxagkaj 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(517)

我试图优化我的查询，因为它需要3.5秒，而且太长了。我的问题是：

SELECT
`products`.*,
IFNULL(SUM(`products_uses`.`quantity`),0) as `usesQuantity`,
IF((`products`.`productQuantity` - IFNULL(SUM(`products_uses`.`quantity`),0)) > 0, (`products`.`productQuantity` - IFNULL(SUM(`products_uses`.`quantity`),0)), 0) AS `totalUses`
FROM `products`
LEFT JOIN `products_uses` ON `products`.`id` = `products_uses`.`productId`
WHERE `products`.`nurseForm` = 1
GROUP BY `products`.`id`
ORDER BY `products`.`fav` DESC, `products`.`productName` ASC

我尝试使用变量进行优化，但没有任何改变：

SELECT
`products`.*,
@usesQuantity := IFNULL(SUM(`products_uses`.`quantity`),0) as `usesQuantity`,
IF((`products`.`productQuantity` - @usesQuantity) > 0, (`products`.`productQuantity` - @usesQuantity), 0) AS `totalUses`
FROM `products`
LEFT JOIN `products_uses` ON `products`.`id` = `products_uses`.`productId`
WHERE `products`.`nurseForm` = 1
GROUP BY `products`.`id`
ORDER BY `products`.`fav` DESC, `products`.`productName` ASC

此查询汇总每个产品使用的数量- IFNULL(SUM( 使用的产品 . 量 ),0) ，每种产品的使用量-

IF((`products`.`productQuantity` - IFNULL(SUM(`products_uses`.`quantity`),0)) > 0, (`products`.`productQuantity` - IFNULL(SUM(`products_uses`.`quantity`),0)), 0)

我尝试将表的结构更改为myisam，而innodb没有任何更改。如何优化此查询？tnx公司

mysql optimization myisam

来源：https://stackoverflow.com/questions/51320857/optimize-query-mysql

2条答案

按热度按时间

vzgqcmou1#

提供 SHOW CREATE TABLE 会有帮助的。同时，我猜。
这可能会（也可能不会）有帮助。它试图在不拖拽所有列的情况下求和 products 周围。它避免了 GROUP BY .

SELECT  p2.*,
        IFNULL(x.usesQuantity, 0) as `usesQuantity`,
        GREATEST(p2.`productQuantity` - IFNULL(x.usesQuantity), 0) AS `totalUses`
    FROM  `products` AS p2
    LEFT JOIN  
        ( SELECT  p.id AS xid,
                  SUM(pu.quantity) as `usesQuantity`
            FROM  products_uses AS pu
            JOIN  products AS p  ON p.id = pu.productId
            WHERE  p.nurseForm = 1 
        ) AS x  ON x.xid = p2.id
    ORDER BY  p2.`fav` DESC,
              p2.`productName` ASC

这些索引应有助于：

products:  INDEX(nurseForm, id)
products:  PRIMARY KEY(id)  -- I am assuming this??
products_uses:  INDEX(productId, quantity)

如果 LEFT 如果没有必要，还会有其他优化。
MySQL5.6将有助于子查询。
mysql 8.0可以帮助 ORDER BY ，同时，由于 DESC 以及 ASC .

赞(0）回复(0）举报 2021-06-20

rwqw0loc2#

我不确定引入用户变量会改变多少。但是在两个表上有条理地添加索引可能会有所帮助。试试这个：

ALTER TABLE products ADD INDEX nurse_index (nurseForm);
ALTER TABLE products_uses ADD INDEX product_index (productId);

第一个索引，在 products.nurseForm 列，可能有助于 WHERE 条款。特别是，如果只有少数记录匹配，这个索引将是一个很大的帮助。
第二个索引，在 products_uses.productId ，可能有助于更快地加入。同样，这取决于你的table有多大。
你也可以跑步 EXPLAIN 看看是否还有其他瓶颈。

赞(0）回复(0）举报 2021-06-20

我来回答

优化查询-mysql

2条答案

相关问题

热门标签

最新问答