MYSQL - Count查询占用时间

Question

我正在运行以下查询，它在我的本地系统上花费了超过12秒的时间。

SELECT DISTINCT
entity_id_0
FROM
(SELECT DISTINCT
c0_.entity_id AS entity_id_0,
c0_.sku AS sku_1,
c0_.visible_in_grid AS visible_in_grid_2,
c0_.has_options AS has_options_3,
c0_.required_options AS required_options_4,
c0_.created_at AS created_at_5,
c0_.updated_at AS updated_at_6,
c0_.type_id AS type_id_7,
c0_.entity_type_id AS entity_type_id_8,
c0_.attribute_set_id AS attribute_set_id_9
FROM
catalog_product_entity c0_
INNER JOIN catalog_product_website c1_ ON c0_.entity_id = c1_.product_id
AND (c1_.website_id = 1)
INNER JOIN catalog_product_entity_varchar c2_ ON c0_.entity_id = c2_.entity_id
AND (c2_.attribute_id = 71)
WHERE
(((c2_.value LIKE 'bed%')))
AND c0_.type_id IN ('base' , 'simple', 'bundle', 'configurable')) dctrn_result

如果我只运行subquery，它只需要0.0027秒

SELECT DISTINCT
c0_.entity_id AS entity_id_0,
c0_.sku AS sku_1,
c0_.visible_in_grid AS visible_in_grid_2,
c0_.has_options AS has_options_3,
c0_.required_options AS required_options_4,
c0_.created_at AS created_at_5,
c0_.updated_at AS updated_at_6,
c0_.type_id AS type_id_7,
c0_.entity_type_id AS entity_type_id_8,
c0_.attribute_set_id AS attribute_set_id_9
FROM
catalog_product_entity c0_
INNER JOIN catalog_product_website c1_ ON c0_.entity_id = c1_.product_id
AND (c1_.website_id = 1)
INNER JOIN catalog_product_entity_varchar c2_ ON c0_.entity_id = c2_.entity_id
AND (c2_.attribute_id = 71)
WHERE
(((c2_.value LIKE 'bed%')))
AND c0_.type_id IN ('base' , 'simple', 'bundle', 'configurable')

当我尝试使用explain语句来理解查询时，它显示了以下结果，我不确定如何提高整个查询的性能。

用EXPLAIN查询

Answer 1

不要使用选择不需要的列并导致额外执行步骤/级别的子查询。

SELECT DISTINCT
c0_.entity_id AS entity_id_0  
/*                                  ,
c0_.sku AS sku_1,
c0_.visible_in_grid AS visible_in_grid_2,
c0_.has_options AS has_options_3,
c0_.required_options AS required_options_4,
c0_.created_at AS created_at_5,
c0_.updated_at AS updated_at_6,
c0_.type_id AS type_id_7,
c0_.entity_type_id AS entity_type_id_8,
c0_.attribute_set_id AS attribute_set_id_9
*/
FROM
catalog_product_entity c0_
INNER JOIN catalog_product_website c1_ ON c0_.entity_id = c1_.product_id
AND (c1_.website_id = 1)
INNER JOIN catalog_product_entity_varchar c2_ ON c0_.entity_id = c2_.entity_id
AND (c2_.attribute_id = 71)
WHERE
(((c2_.value LIKE 'bed%')))
AND c0_.type_id IN ('base' , 'simple', 'bundle', 'configurable')

Answer 2

这听起来像EAV模式。当有很多行时，这种设计的性能很差。

这些索引应该有助于一些:

c2_:  INDEX(attribute_id, value, entity_id)
c0_:  INDEX(entity_type, type_id)
c1_:  INDEX(product_id, website_id)
c1_:  INDEX(website_id, product_id)

如果value是TEXT列，上面的索引将不被允许。看看你能不能把它改成一些文明大小的VARCHAR。如果你坚持使用TEXT，那么，试试这个:

c2_:  INDEX(attribute_id, value(20))

为进一步讨论，请提供相关表格的SHOW CREATE TABLE;也许有更多的技术可以帮助你。