如何使用多個 LEFT JOIN 優化 COUNT 查詢

我有查詢為目前類別選擇過濾器並顯示每個過濾器中有多少產品是 Atm 我有 20k 產品和 10k 過濾器進行測試，大約需要 1 秒。執行這對我不利我能做些什麼來優化這個查詢嗎？

我的帶有選定過濾器的查詢範例（根據過濾器組動態添加 pf 表）

SELECT SQL_NO_CACHE sd.filter_group_id,
       fgd.name AS group_name,
       pf.filter_id AS filter_id,
       fd.name,
       COUNT(DISTINCT p2c.product_id) AS total
   FROM oc_product_to_category p2c 
   LEFT JOIN oc_product_filter pf5 ON pf5.product_id = p2c.product_id 
   LEFT JOIN oc_product_filter pf8 ON pf8.product_id = p2c.product_id 
   LEFT JOIN oc_product_filter pf10 ON pf10.product_id = p2c.product_id 
   LEFT JOIN oc_product_filter pf3 ON pf3.product_id = p2c.product_id 
   LEFT JOIN oc_product_filter pf9 ON pf9.product_id = p2c.product_id 
   LEFT JOIN oc_product_filter pf6 ON pf6.product_id = p2c.product_id 
   LEFT JOIN oc_product p ON p.product_id = p2c.product_id
   LEFT JOIN oc_product_filter pf ON pf.product_id = p2c.product_id
   LEFT JOIN oc_filter f ON pf.filter_id = f.filter_id
   LEFT JOIN oc_filter_description fd ON pf.filter_id = fd.filter_id
   LEFT JOIN oc_filter_group_description fgd
         ON fd.filter_group_id = fgd.filter_group_id
   LEFT JOIN oc_sd_filter sd
         ON sd.category_id = p2c.category_id AND sd.status = 1
    WHERE p2c.category_id = '64' 
    AND sd.filter_group_id = fd.filter_group_id 
    AND p.status = 1
        AND pf5.filter_id IN (33) 
        AND pf8.filter_id IN (47,141,143,144,145) 
        AND pf10.filter_id IN (118,120,121,122,123,128,129,130,134,138,157,163) 
        AND pf3.filter_id IN (9,11) 
        AND pf9.filter_id IN (57,58,59,60,61,94,95) 
        AND pf6.filter_id IN (116)
       GROUP BY fd.filter_id, fd.filter_group_id
       ORDER BY sd.sort_order ASC, 
       (CASE WHEN fgd.custom_order = 0 THEN f.sort_order END) ASC, 
       (CASE WHEN fgd.custom_order = 1 THEN COUNT(p2c.product_id) END) DESC

這是我的表結構

       CREATE TABLE `oc_product` (
     `product_id` int(11) NOT NULL,
     `model` varchar(64) NOT NULL,
     `sku` varchar(64) NOT NULL,
     `upc` varchar(12) NOT NULL,
     `ean` varchar(14) NOT NULL,
     `jan` varchar(13) NOT NULL,
     `isbn` varchar(17) NOT NULL,
     `mpn` varchar(64) NOT NULL,
     `location` varchar(128) NOT NULL,
     `quantity` int(4) NOT NULL DEFAULT 0,
     `manufacturer_id` int(11) NOT NULL,
     `price` decimal(15,4) NOT NULL DEFAULT 0.0000,
     `tax_class_id` int(11) NOT NULL,
     `date_available` date NOT NULL DEFAULT '0000-00-00',
     `sort_order` int(11) NOT NULL DEFAULT 0,
     `status` tinyint(1) NOT NULL DEFAULT 0,
     `date_added` datetime NOT NULL,
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


   ALTER TABLE `oc_product`
     ADD PRIMARY KEY (`product_id`),
     ADD KEY `model` (`model`),
     ADD KEY `manufacturer_id` (`manufacturer_id`),
     ADD KEY `sort_order` (`sort_order`),
     ADD KEY `status` (`status`) USING BTREE;

       CREATE TABLE `oc_product_to_category` (
     `product_id` int(11) NOT NULL,
     `category_id` int(11) NOT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

   ALTER TABLE `oc_product_to_category`
     ADD PRIMARY KEY (`product_id`,`category_id`),
     ADD KEY `category_id` (`category_id`);

       CREATE TABLE `oc_product_filter` (
     `product_id` int(11) NOT NULL,
     `filter_id` int(11) NOT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   ALTER TABLE `oc_product_filter`
     ADD PRIMARY KEY (`product_id`,`filter_id`);


       CREATE TABLE `oc_sd_filter` (
     `id` int(11) NOT NULL,
     `category_id` int(11) NOT NULL,
     `filter_group_id` int(11) NOT NULL,
     `status` int(11) NOT NULL,
     `sort_order` int(11) NOT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

   -
   ALTER TABLE `oc_sd_filter`
     ADD PRIMARY KEY (`id`),
     ADD KEY `filter-category` (`category_id`,`filter_group_id`);



   CREATE TABLE `oc_filter` (
     `filter_id` int(11) NOT NULL,
     `filter_group_id` int(11) NOT NULL,
     `sort_order` int(3) NOT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

   ALTER TABLE `oc_filter`
     ADD PRIMARY KEY (`filter_id`);


       CREATE TABLE `oc_filter_description` (
     `filter_id` int(11) NOT NULL,
     `language_id` int(11) NOT NULL,
     `filter_group_id` int(11) NOT NULL,
     `name` varchar(64) NOT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

   ALTER TABLE `oc_filter_description`
     ADD PRIMARY KEY (`filter_id`,`language_id`),
     ADD KEY `filter` (`filter_group_id`);


       CREATE TABLE `oc_filter_group_description` (
     `filter_group_id` int(11) NOT NULL,
     `language_id` int(11) NOT NULL,
     `name` varchar(64) NOT NULL,
     `level` int(11) NOT NULL,
     `custom_order` int(11) NOT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

   ALTER TABLE `oc_filter_group_description`
     ADD PRIMARY KEY (`filter_group_id`,`language_id`);

更新：

pf10.filter_id IN (118,120,121,122,123,128,129,130​​,134,138,157,163)

pf10 代表 filter_group

IN (118,120…..) 表示目前 filter_group 的選定過濾器

對於不同filter_group中的每個選定過濾器，我都會進行LEFT JOIN，然後來自同一組的所有過濾器都進入相同的IN（）

更新 2

經過幾次測試後，我發現在 WHERE caluse

AND sd.filter_group_id = fd.filter_group_id
AND p.status = 1

增加時間很多，如果我像這樣將它們移動到 LEFT JOIN

LEFT JOIN oc_product p ON p.product_id = p2c.product_id AND p.status = 1
LEFT JOIN oc_sd_filter sd ON sd.category_id = p2c.category_id AND sd.status = 1 AND sd.filter_group_id = fd.filter_group_id

執行時間變為 0.0600 左右，但結果不一樣

如何處理這個問題或者我是否需要更改查詢？

我會嘗試通過在幾個 INT 中包含一些位字元串來擺脫大多數 JOIN，然後進行布爾測試。例如，

AND pf3.filter_id IN (9,11) 

變成類似的東西

AND (p.filter3 & 0x...) != 0

包含filter3您擁有的一些標誌，並0x...有 2 位代表案例 9 和 11。（如果我對“9”代表什麼以及標誌如何分組有所了解，我可能能夠更好地描述這一點。）

這將消除許多JOINs.

oc_product_to_category使用 InnoDB 會表現更好。更多討論請參見：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

請提供EXPLAIN SELECT ...。

請不要LEFT JOIN在 aJOIN是什麼意思時使用。

雖然它可以混合使用 MyISAM 和 InnoDB，但最好只使用 InnoDB。

我可能有更多的建議；看看你能用這些技巧做什麼，然後我可以再做一次。

引用自：https://dba.stackexchange.com/questions/292422