mysql - 是一种优化大型mysql查询的方法吗？

Question

这是我的查询，它很慢......我正在寻找优化它的方法。

SELECT p.id, 
       Group_concat(pc.cat_id)   AS groups, 
       p.code, 
       p.NAME, 
       p.price, 
       p.thumbnail, 
       p.image, 
       mc.queries                AS merch_queries, 
       mc.position               AS merch_position, 
       Group_concat(op.image)    AS option_images, 
       cf_RETAIL.value           AS custom_RETAIL, 
       cf_rating.value           AS custom_rating, 
       cf_reviews.value          AS custom_reviews, 
       cf_sku.value              AS custom_sku, 
       cf_brand.value            AS custom_brand, 
       cf_custom_thumbnail.value AS custom_custom_thumbnail 
FROM   s01_products AS p 
       LEFT JOIN s01_categoryxproduct AS pc 
              ON p.id = pc.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'RETAIL') AS cf_RETAIL 
              ON p.id = cf_RETAIL.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'rating') AS cf_rating 
              ON p.id = cf_rating.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'reviews') AS cf_reviews 
              ON p.id = cf_reviews.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'sku') AS cf_sku 
              ON p.id = cf_sku.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'brand') AS cf_brand 
              ON p.id = cf_brand.product_id 
       LEFT JOIN (SELECT pv.product_id, 
                         pv.value 
                  FROM   s01_cfm_prodfields AS pf 
                         INNER JOIN s01_cfm_prodvalues AS pv 
                                 ON pf.id = pv.field_id 
                  WHERE  pf.code = 'custom_thumbnail') AS cf_custom_thumbnail 
              ON p.id = cf_custom_thumbnail.product_id 
       LEFT JOIN (SELECT p.product_id          AS product_id, 
                         Group_concat(q.query) AS queries, 
                         Min(p.position)       AS position 
                  FROM   s01_srch_merchandisingproduct AS p 
                         LEFT JOIN s01_srch_merchandisingquery AS q 
                                ON q.id = p.query_id 
                  GROUP  BY p.product_id) AS mc 
              ON p.id = mc.product_id 
       LEFT JOIN s01_options AS op 
              ON p.id = op.product_id 
                 AND op.image <> '' 
WHERE  p.active = 1 
GROUP  BY p.id 

谢谢你的帮助！

更新的表架构：

**s01_categoryxproduct** 
cat_id, 
product_id, 
disp_order

**s01_products**
id      
catcount    
agrpcount   
pgrpcount   
disp_order  
code            
name            
thumbnail       
image           
price       
cost        
descrip         
weight      
taxable     
active      
sku         
cancat_id   
page_id     
page_title      
dt_created  
dt_updated

**s01_CFM_ProdValues** 
field_id, 
product_id, 
value, 
value_long

**s01_CFM_ProdFields** 
id, 
code, 
name, 
group_id, 
fieldtype, 
info, 
facet

**s01_Options** 
id, 
product_id, 
attr_id, 
disp_order, 
code, 
prompt, 
price, 
cost, 
weight, 
image

**s01_SRCH_MerchandisingProduct** 
id, 
product_id, 
query_id, 
position

**s01_SRCH_MerchandisingQuery** 
id, 
query

标签： mysqlquery-optimizationentity-attribute-value

Answer 1

“过度标准化”和 EAV 是问题所在。

将 prodfields 和 prodvalues 放在同一个表中。将它们分成两个表会导致消耗性能的开销。

有关优化 EAV 表的更多信息：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

更多关于为什么 EAV 不好的讨论：http: //mysql.rjweb.org/doc.php/eav