mysql - 添加的联接越多，选择所需的时间就越长

Question

我有一个疑问其中 8 个连接中的每一个单独需要 0.0007 秒，但是当我将它们组合起来时，查询所需的时间呈指数级增长，最终查询时间为 3 秒。

基本上，我有一种灵活的方式来请求带有附加字段的 field 。前三个字段看起来相当不错，但后来我注意到当我达到 7 时，它需要很长的时间。我随机化了附加字段的顺序，但事实证明添加什么字段并不重要。

无附加字段[0.52 毫秒]

SELECT 
  `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags 
FROM 
   `listing_venue` AS `v` 
INNER JOIN 
   `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id WHERE  (v.venue_id = 79) AND (v.published = 1) 
GROUP BY 
   `venue_id`

1 个附加字段 [0.72 毫秒]

SELECT 
   `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`
FROM 
   `listing_venue` AS `v` 
INNER JOIN 
   `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id 
INNER JOIN 
   `listing_field` AS `addressfield` 
LEFT JOIN
   `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id 
WHERE 
   (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') 
GROUP BY 
   `venue_id`

2 个附加字段 [1.42 毫秒]

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') GROUP BY `venue_id`

3 -> 1.13 毫秒

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id INNER JOIN `listing_field` AS `address_nearfield` LEFT JOIN `listing_venue_field_rel` AS `address_nearvalue` ON address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') AND (address_nearfield.field_nm = 'address_near') GROUP BY `venue_id`

4 -> 1.94 毫秒

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near`, `phone_numbervalue`.`value` AS `phone_number` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id INNER JOIN `listing_field` AS `address_nearfield` LEFT JOIN `listing_venue_field_rel` AS `address_nearvalue` ON address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id INNER JOIN `listing_field` AS `phone_numberfield` LEFT JOIN `listing_venue_field_rel` AS `phone_numbervalue` ON phone_numbervalue.venue_id = v.venue_id AND phone_numbervalue.field_id = phone_numberfield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') AND (address_nearfield.field_nm = 'address_near') AND (phone_numberfield.field_nm = 'phone_number') GROUP BY `venue_id`

5->9.7 毫秒

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near`, `phone_numbervalue`.`value` AS `phone_number`, `websitevalue`.`value` AS `website` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id INNER JOIN `listing_field` AS `address_nearfield` LEFT JOIN `listing_venue_field_rel` AS `address_nearvalue` ON address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id INNER JOIN `listing_field` AS `phone_numberfield` LEFT JOIN `listing_venue_field_rel` AS `phone_numbervalue` ON phone_numbervalue.venue_id = v.venue_id AND phone_numbervalue.field_id = phone_numberfield.field_id INNER JOIN `listing_field` AS `websitefield` LEFT JOIN `listing_venue_field_rel` AS `websitevalue` ON websitevalue.venue_id = v.venue_id AND websitevalue.field_id = websitefield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') AND (address_nearfield.field_nm = 'address_near') AND (phone_numberfield.field_nm = 'phone_number') AND (websitefield.field_nm = 'website') GROUP BY `venue_id`

6 -> 230.52 毫秒

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near`, `phone_numbervalue`.`value` AS `phone_number`, `websitevalue`.`value` AS `website`, `price_maxvalue`.`value` AS `price_max` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id INNER JOIN `listing_field` AS `address_nearfield` LEFT JOIN `listing_venue_field_rel` AS `address_nearvalue` ON address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id INNER JOIN `listing_field` AS `phone_numberfield` LEFT JOIN `listing_venue_field_rel` AS `phone_numbervalue` ON phone_numbervalue.venue_id = v.venue_id AND phone_numbervalue.field_id = phone_numberfield.field_id INNER JOIN `listing_field` AS `websitefield` LEFT JOIN `listing_venue_field_rel` AS `websitevalue` ON websitevalue.venue_id = v.venue_id AND websitevalue.field_id = websitefield.field_id INNER JOIN `listing_field` AS `price_maxfield` LEFT JOIN `listing_venue_field_rel` AS `price_maxvalue` ON price_maxvalue.venue_id = v.venue_id AND price_maxvalue.field_id = price_maxfield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') AND (address_nearfield.field_nm = 'address_near') AND (phone_numberfield.field_nm = 'phone_number') AND (websitefield.field_nm = 'website') AND (price_maxfield.field_nm = 'price_max') GROUP BY `venue_id`

7-> 3375.29 毫秒

SELECT 
    `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near`, `phone_numbervalue`.`value` AS `phone_number`, `websitevalue`.`value` AS `website`, `price_maxvalue`.`value` AS `price_max`, `price_minvalue`.`value` AS `price_min` 
FROM 
    `listing_venue` AS `v` 
INNER JOIN 
    `listing_venue_tag_rel` AS `vtr` 
    ON 
        vtr.venue_id = v.venue_id 
INNER JOIN
    `listing_field` AS `addressfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `addressvalue` 
    ON 
        addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id 
INNER JOIN 
    `listing_field` AS `address_zhfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `address_zhvalue` 
    ON 
        address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id 
INNER JOIN 
    `listing_field` AS `address_nearfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `address_nearvalue` 
    ON 
    address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id 
INNER JOIN 
    `listing_field` AS `phone_numberfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `phone_numbervalue` 
    ON 
        phone_numbervalue.venue_id = v.venue_id AND phone_numbervalue.field_id = phone_numberfield.field_id 
INNER JOIN 
    `listing_field` AS `websitefield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `websitevalue` 
    ON 
        websitevalue.venue_id = v.venue_id AND websitevalue.field_id = websitefield.field_id 
INNER JOIN 
    `listing_field` AS `price_maxfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `price_maxvalue` 
    ON 
        price_maxvalue.venue_id = v.venue_id AND price_maxvalue.field_id = price_maxfield.field_id 
INNER JOIN 
    `listing_field` AS `price_minfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `price_minvalue`
    ON 
        price_minvalue.venue_id = v.venue_id AND price_minvalue.field_id = price_minfield.field_id 
WHERE 
    (v.venue_id = 79) 
AND (v.published = 1) 
AND (addressfield.field_nm = 'address') 
AND (address_zhfield.field_nm = 'address_zh') 
AND (address_nearfield.field_nm = 'address_near') 
AND (phone_numberfield.field_nm = 'phone_number') 
AND (websitefield.field_nm = 'website') 
AND (price_maxfield.field_nm = 'price_max') 
AND (price_minfield.field_nm = 'price_min') GROUP BY `venue_id`

显然我可以将查询分成 7 个，但我不明白为什么会发生这种奇怪的时间增长。

此外，我不确定我是否使用了正确的连接。字段可能是空的，所以这就是为什么我要加入venue_field_rel...

这是表结构

listing_field:
field_id | field_nm

listing_venue_field_rel
venue_id | field_id| value

listing_venue
venue_id | venue_nm

解释一下

id  select_type     table               type    possible_keys                                                                                       key                 key_len     ref     rows    Extra
1   SIMPLE          v                   const   PRIMARY,item_id_UNIQUE                                                                              PRIMARY             4           const   1    
1   SIMPLE          addressfield        ALL     NULL                                                                                                NULL                NULL        NULL    7       Using where
1   SIMPLE          addressvalue        ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          address_zhfield     ALL     NULL                                                                                                NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          address_zhvalue     ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          address_nearfield   ALL     NULL                                                                                                NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          address_nearvalue   ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          phone_numberfield   ALL     NULL                                                                                                NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          phone_numbervalue   ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          websitefield        ALL                                                                                                         NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          websitevalue        ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          price_maxfield      ALL                                                                                                         NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          price_maxvalue      ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          price_minfield      ALL                                                                                                         NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          price_minvalue      ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          vtr                 ref     item_tag,fk_venue_id                                                                                fk_venue_id         4           const   11      Using index

编辑我“索引唯一”field_nm，这将速度降低到7毫秒

id  select_type table               type    possible_keys           key                 key_len     ref     rows    Extra
1   SIMPLE      v                   const   PRIMARY,item_id_UNIQUE  PRIMARY             4           const   1    
1   SIMPLE      addressfield        const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      address_zhfield     const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      address_nearfield   const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      phone_numberfield   const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      websitefield        const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      price_maxfield      const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      addressvalue        ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      address_zhvalue     ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      address_nearvalue   ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      phone_numbervalue   ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      websitevalue        ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      price_maxvalue      ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      vtr                 ref     item_tag,fk_venue_id    fk_venue_id         4           const   17  Using index

但是我确信查询可以进一步优化

Answer 1

我认为您可能在这里进行交叉连接

内连接 listing_field AS address_zhfield 左连接

您没有 ON 子句，因此您会得到与其他所有内容的笛卡尔积，这可能会导致指数级减速。