代码之家 › 专栏 › 技术社区 › hitwill

非常慢的MySQL代码(内联和JOINS)

inline join mysql

hitwill · 技术社区 · 9 年前

如何重构下面的查询以更快地运行?运行需要几分钟,这大大降低了Web服务器的速度。查询使用内联查询和联接从多个表中获取产品的详细信息。我觉得一定有更快的方法。

写下面的查询最有效的方法是什么?

SELECT p.*, 
   pd.*, pd.NAME AS NAME, pi.*,p.image AS default_image, 
   (SELECT first FROM  product_shipping psi 
    WHERE  psi.product_id = p.product_id)         AS flat_rate, 
   (SELECT NAME FROM   manufacturer m 
    WHERE  m.manufacturer_id = p.manufacturer_id) AS manufacturer, 
   (SELECT price FROM   product_special ps 
    WHERE  ps.product_id = p.product_id AND ps.customer_group_id = 8) AS special_price 
   FROM   product p 
      LEFT JOIN product_description pd 
          ON ( p.product_id = pd.product_id ) 
      LEFT JOIN product_image pi 
          ON ( p.product_id = pi.product_id ) 
    ORDER  BY p.product_id

3 回复 | 直到 9 年前

Gordon Linoff 9 年前

如果没有索引,则需要索引。我想到了以下几点:

product_shipping(product_id, first)
manufacturer(manufacturer_id, name)
product_special(product_id, customer_group_id, price)
product_description(product_id)
product_image(product_id)
product(product_id, manufacturer_id)

如果没有合适的索引,重新排列子查询和联接可能对性能影响不大。

Harper Maddox 9 年前

您的选择中有很多项目是重复的,但由于这是您最初获取数据的方式,所以我一直保持原样 LEFT JOIN s可以比 INNER JOIN 秒:

SELECT 
    product.*,
    product_description.*,
    product_description.NAME AS NAME,
    product_image.*,
    product.image AS default_image,
    product_shipping.first AS flat_rate,
    manufacturer.NAME AS manufacturer,
    product_special.price AS special_price
FROM
    product
        LEFT JOIN product_description ON product.product_id = product_description.product_id
        LEFT JOIN product_image ON product_image.product_id = product_image.product_id
        LEFT JOIN manufacturer ON product.manufacturer_id = manufacturer.id
        LEFT JOIN product_special ON product.product_id = product_special.product_id AND product_special.customer_group_id = 8
ORDER BY product.product_id

Jorge Campos 9 年前

与@Harper给出的答案不同,我选择做内部连接,因为你是在内联选择上做的,所以我的建议是:

 SELECT p.*, 
        pd.*, pd.NAME AS NAME, pi.*,p.image AS default_image, 
        psi.first AS flat_rate, 
        m.NAME AS manufacturer, 
        ps.price AS special_price 
   FROM product p 
       INNER JOIN product_shipping psi 
           ON (psi.product_id = p.product_id)
       INNER JOIN manufacturer m
           ON (m.manufacturer_id = p.manufacturer_id)
       INNER JOIN product_special ps
           ON (ps.product_id = p.product_id AND ps.customer_group_id = 8)
       LEFT JOIN product_description pd 
           ON ( p.product_id = pd.product_id ) 
       LEFT JOIN product_image pi 
           ON ( p.product_id = pi.product_id ) 
 ORDER  BY p.product_id