gpt4 book ai didi

Mysql查询优化以在没有DISTINCT的情况下转换连接中的子查询

转载 作者:行者123 更新时间:2023-11-29 00:16:31 26 4
gpt4 key购买 nike

我有表:

CREATE TABLE IF NOT EXISTS `bk_cart_rule` (
`id_cart_rule` int(10) unsigned NOT NULL DEFAULT '0',
`cart_rule_restriction` tinyint(1) unsigned NOT NULL DEFAULT '0',
KEY `id_cart_rule` (`id_cart_rule`),
KEY `cart_rule_restriction` (`cart_rule_restriction`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `bk_cart_rule_combination` (
`id_cart_rule_1` int(10) unsigned NOT NULL,
`id_cart_rule_2` int(10) unsigned NOT NULL,
KEY `id_cart_rule_1` (`id_cart_rule_1`),
KEY `id_cart_rule_2` (`id_cart_rule_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `bk_cart_rule_lang` (
`id_cart_rule` int(10) unsigned NOT NULL,
`id_lang` int(10) unsigned NOT NULL,
KEY `id_cart_rule` (`id_cart_rule`),
KEY `id_lang` (`id_lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

还有一个查询:

SELECT SQL_NO_CACHE cr.*, crl.*, 1 as selected FROM bk_cart_rule cr 
LEFT JOIN bk_cart_rule_lang crl ON (cr.id_cart_rule = crl.id_cart_rule AND crl.id_lang = 2)

WHERE cr.id_cart_rule != 375 AND
( cr.cart_rule_restriction = 0 OR
cr.id_cart_rule IN (
SELECT IF(id_cart_rule_1 = 375, id_cart_rule_2, id_cart_rule_1) FROM bk_cart_rule_combination WHERE 375 = id_cart_rule_1 OR 375 = id_cart_rule_2 ) )

明显的优化是:

    SELECT SQL_NO_CACHE DISTINCT cr.*, crl.* 1 as selected FROM bk_cart_rule cr 
LEFT JOIN bk_cart_rule_lang crl ON (cr.id_cart_rule = crl.id_cart_rule AND crl.id_lang = 2)
LEFT JOIN bk_cart_rule_combination crc ON (375 = crc.id_cart_rule_1 AND cr.id_cart_rule = crc.id_cart_rule_2) OR (375 = crc.id_cart_rule_2 AND cr.id_cart_rule = crc.id_cart_rule_1)
WHERE cr.id_cart_rule != 375 AND (cr.cart_rule_restriction = 0 OR NOT ISNULL(crc.id_cart_rule_1))

但是我怎样才能摆脱 DISTINCT(在 bk_cart_rule_combination 中我有两种组合:)

id_cart_rule_1 id_cart_rule_2
375 776
776 375

或者也许有更好的优化可能?

最佳答案

如果购物车规则的顺序不重要,则添加第一个的 id 小于第二个的 id 的约束。也就是把它们按顺序放在表里。

遗憾的是,MySQL 不允许简单的check 约束。相反,您必须以其他方式实现它。这是三个:

  • 实现插入/更新触发器以保持顺序(并防止重复)。
  • 在应用端实现逻辑。
  • 将所有数据修改包装在存储过程中,并在存储过程中实现逻辑。

如果您不想经历所有这些麻烦(这可能有助于解决其他问题),您可以将 select distinct 替换为:

group by least(id_cart_rule_1,  id_cart_rule_2), greatest(id_cart_rule_1,  id_cart_rule_2)

关于Mysql查询优化以在没有DISTINCT的情况下转换连接中的子查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/22785136/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com