mysql - 分面搜索 mysql (EAV)。如何提高分面计数的速度？

Question

我使用 mysql 进行多面搜索，它对测试数据很好。然后我上传 7000 个项目和 20000 个过滤器项目关系。现在一些查询来计算每个过滤器的项目数量需要很多时间。每个类别都有自己的过滤器。每个过滤器都有自己的过滤器值 (filter_v_id)。为简单起见，我将展示两个表格：

CREATE TABLE `item_cat` (
  `item_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `publicate` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `item_cat`
  ADD PRIMARY KEY (`item_id`,`category_id`),
  ADD KEY `item_id` (`item_id`),
  ADD KEY `category_id` (`category_id`);


CREATE TABLE `filter_item` (
  `item_id` int(11) NOT NULL,
  `filter_id` int(11) NOT NULL,
  `filter_v_id` int(11) NOT NULL,
  `filter_v` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `filter_item`
  ADD PRIMARY KEY (`item_id`,`filter_id`,`filter_v_id`),
  ADD KEY `item_id` (`item_id`),
  ADD KEY `item_id_2` (`item_id`,`filter_id`),
  ADD KEY `item_id_3` (`item_id`,`filter_id`,`filter_v_id`),
  ADD KEY `filter_id` (`filter_id`,`filter_v_id`),
  ADD KEY `filter_id_2` (`filter_id`);

DB Fiddle - 快速查询 Category_id=70 有 46 个项目，任何一组过滤器都快速（100-150ms）

SELECT `filter_id`,`filter_v_id`, count(`item_id`) as `cnt` FROM        
(SELECT * FROM `item_cat` WHERE `category_id`='70') as `ic`         
JOIN `filter_item` as `fi` USING(`item_id`) 
WHERE `item_id` IN(         
SELECT `ic`.`item_id` FROM          
    (SELECT * FROM `item_cat` WHERE `category_id`='70') as `ic`             
    JOIN `filter_item` as `fi` USING(`item_id`)                     
    GROUP BY `ic`.`item_id`         
    HAVING (                
        (`fi`.`filter_id` = '1' OR  MAX(`fi`.`filter_v_id` = '932')=1  OR  MAX(`fi`.`filter_v_id` = '938')=1  OR  MAX(`fi`.`filter_v_id` = '1006')=1 ) 
        AND (`fi`.`filter_id` = '17' OR  MAX(`fi`.`filter_v_id` = '563')=1 ) 
        AND (`fi`.`filter_id` = '28' OR  MAX(`fi`.`filter_v` >= '2' AND `fi`.`filter_v` <= '22500')=1) 
        AND (`fi`.`filter_id` = '46' OR  MAX(`fi`.`filter_v_id` = '580')=1  OR  MAX(`fi`.`filter_v_id` = '595')=1  OR  MAX(`fi`.`filter_v_id` = '559')=1 ) 
        AND (`fi`.`filter_id` = '70' OR  MAX(`fi`.`filter_v_id` = '564')=1 ) 
        AND (`fi`.`filter_id` = '73' OR  MAX(`fi`.`filter_v_id` = '562')=1 ) 
        AND (`fi`.`filter_id` = '74' OR  MAX(`fi`.`filter_v_id` = '561')=1 )            
    )
)   
GROUP BY `filter_id`,`filter_v_id`  

DB Fiddle - 慢查询 Category_id=52 有 651 个项目，一个应用的过滤器花费了 3000 毫秒+

SELECT  `filter_id`,`filter_v_id`, count(`item_id`) as `cnt` FROM   
(SELECT * FROM `item_cat` WHERE `category_id`='52') as `ic`
JOIN `filter_item` as `fi` USING(`item_id`) 
WHERE `item_id` IN(         
    SELECT `item_id` FROM           
    (SELECT * FROM `item_cat` WHERE `category_id`='52') as `ic`
    JOIN `filter_item` as `fi` USING(`item_id`)
    GROUP BY `ic`.`item_id`         
    HAVING (                
        (`fi`.`filter_id` = '47' OR  MAX(`fi`.`filter_v_id` = '474')=1 )            
    )               
)
GROUP BY `filter_id`,`filter_v_id`

请告诉我如何提高查询速度？

标签： mysqlfaceted-search