MYSQL 连接/索引优化

Question

我有一个查询，试图查找包含一组给定包裹的所有购物车。对于每个包，我加入相应的 cartitem 表一次，因为我只对包含所有给定包的购物车感兴趣。

当我达到超过 15 个包(连接)时，查询性能会迅速下降。

我在相应的外部列上有两个索引，并且知道 mysql 仅使用其中之一。当我在 2 列(cartitem_package_id，cartitem_cart_id)上添加索引时，它可以工作，但这是解决这种情况的唯一方法吗？我想知道为什么 MYSQL 突然卡在这种情况下，可能是 mysql 内部问题，因为我没有看到这个定义和查询有任何更深层次的问题？这是否可能是查询优化器的问题，我可以做一些事情(例如添加括号)来支持或强制执行特定的查询吗？或者有人在这里有不同的方法，使用另一个查询？

查询看起来像这样:

             SELECT cart_id
             FROM cart
             INNER JOIN cartitem as c1 ON cart_id=c1.cartitem_cart_id AND c1.cartitem_package_id= 7  
             INNER JOIN cartitem as c2 ON cart_id=c2.cartitem_cart_id AND c2.cartitem_package_id= 8  
             INNER JOIN cartitem as c3 ON cart_id=c3.cartitem_cart_id AND c3.cartitem_package_id= 9  
             INNER JOIN cartitem as c4 ON cart_id=c4.cartitem_cart_id AND c4.cartitem_package_id= 10  
             INNER JOIN cartitem as c5 ON cart_id=c5.cartitem_cart_id AND c5.cartitem_package_id= 11  
             INNER JOIN cartitem as c6 ON cart_id=c6.cartitem_cart_id AND c6.cartitem_package_id= 12  
             INNER JOIN cartitem as c7 ON cart_id=c7.cartitem_cart_id AND c7.cartitem_package_id= 13  
             INNER JOIN cartitem as c8 ON cart_id=c8.cartitem_cart_id AND c8.cartitem_package_id= 14  
             INNER JOIN cartitem as c9 ON cart_id=c9.cartitem_cart_id AND c9.cartitem_package_id= 15  
             INNER JOIN cartitem as c10 ON cart_id=c10.cartitem_cart_id AND c10.cartitem_package_id= 16  
             INNER JOIN cartitem as c11 ON cart_id=c11.cartitem_cart_id AND c11.cartitem_package_id= 17  
             INNER JOIN cartitem as c12 ON cart_id=c12.cartitem_cart_id AND c12.cartitem_package_id= 18  
             INNER JOIN cartitem as c13 ON cart_id=c13.cartitem_cart_id AND c13.cartitem_package_id= 19  
             INNER JOIN cartitem as c14 ON cart_id=c14.cartitem_cart_id AND c14.cartitem_package_id= 20  
             INNER JOIN cartitem as c15 ON cart_id=c15.cartitem_cart_id AND c15.cartitem_package_id= 21  
             INNER JOIN cartitem as c16 ON cart_id=c16.cartitem_cart_id AND c16.cartitem_package_id= 22  
             INNER JOIN cartitem as c17 ON cart_id=c17.cartitem_cart_id AND c17.cartitem_package_id= 23 

输出:

No result.

考虑以下示例结构:

CREATE TABLE IF NOT EXISTS `cart` (
  `cart_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cart_state` smallint(20) DEFAULT NULL,
  PRIMARY KEY (`cart_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=80 ;



INSERT INTO `cart` (`cart_id`, `cart_state`) VALUES
(1, 0),(2, 5),(3, 0),(4, 0),(5, 0),(6, 0),(7, 0),(8, 0),(9, 0),(10, 0),(11, 0),(12, 0),(13, 0),(14, 5),(15, 5),(16, 10),(17, 0),(18, 10),(19, 40),(20, 10),(21, 5),(22, 0),(23, 10),(24, 10),(25, 0),(26, 10),(27, 5),(28, 5),(29, 0),(30, 5),(31, 0),(32, 0),(33, 0),(34, 0),(35, 0),(36, 0),(37, 0),(38, 0),(39, 0),(40, 0),(41, 0),(42, 0),(43, 0),(44, 0),(45, 40),(46, 0),(47, 0),(48, 1),(49, 0),(50, 5),(51, 0),(52, 0),(53, 5),(54, 5),(55, 0),(56, 0),(57, 10),(58, 0),(59, 0),(60, 5),(61, 0),(62, 0),(63, 10),(64, 0),(65, 5),(66, 5),(67, 10),(68, 10),(69, 0),(70, 0),(71, 10),(72, 0),(73, 10),(74, 0),(75, 10),(76, 0),(77, 10),(78, 0),(79, 10);


CREATE TABLE IF NOT EXISTS `cartitem` (
  `cartitem_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cartitem_package_id` int(10) unsigned DEFAULT NULL,
  `cartitem_cart_id` int(10) unsigned DEFAULT NULL,
  `cartitem_price` decimal(7,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`cartitem_id`),
  KEY `cartitem_package_id` (`cartitem_package_id`),
  KEY `cartitem_cart_id` (`cartitem_cart_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=89 ;


INSERT INTO `cartitem` (`cartitem_id`, `cartitem_package_id`, `cartitem_cart_id`, `cartitem_price`) VALUES
(1, 4, 2, 200.00),(2, 7, 3, 30.00),(3, 14, 9, 255.00),(4, 14, 9, 255.00),(5, 22, 9, 120.00),(6, 22, 9, 120.00),(7, 13, 13, 300.00),(8, 13, 13, 300.00),(9, 7, 14, 450.00),(10, 13, 14, 250.00),(11, 17, 14, 150.00),(12, 7, 15, 450.00),(13, 13, 15, 250.00),(14, 18, 15, 127.50),(15, 7, 16, 450.00),(16, 17, 16, 150.00),(17, 7, 18, 450.00),(18, 7, 19, 450.00),(19, 17, 19, 150.00),(20, 21, 19, 25.00),(21, 13, 20, 300.00),(22, 7, 21, 550.00),(23, 19, 21, 105.00),(24, 22, 21, 120.00),(25, 17, 22, 150.00),(26, 7, 23, 550.00),(27, 11, 24, 245.00),(31, 7, 26, 450.00),(32, 21, 26, 25.00),(33, 21, 26, 25.00),(34, 22, 26, 120.00),(35, 23, 26, 120.00),(36, 10, 27, 382.50),(37, 22, 27, 120.00),(38, 13, 27, 250.00),(39, 10, 28, 297.50),(43, 7, 29, 550.00),(41, 20, 28, 82.50),(42, 22, 28, 120.00),(44, 7, 30, 550.00),(46, 22, 30, 120.00),(47, 23, 30, 120.00),(48, 21, 18, 25.00),(49, 21, 19, 25.00),(50, 17, 37, 150.00),(51, 17, 37, 150.00),(52, 21, 37, 25.00),(53, 21, 37, 25.00),(54, 4, 45, 1.20),(55, 6, 45, 0.00),(56, 7, 47, 450.00),(57, 4, 50, 200.00),(58, 13, 52, 250.00),(59, 13, 19, 300.00),(60, 9, 19, 0.00),(61, 17, 53, 150.00),(62, 7, 53, 450.00),(63, 22, 18, 120.00),(64, 7, 16, 450.00),(65, 7, 54, 450.00),(66, 7, 57, 450.00),(67, 17, 57, 150.00),(68, 7, 56, 450.00),(69, 17, 59, 150.00),(70, 7, 60, 450.00),(71, 17, 61, 150.00),(72, 17, 63, 150.00),(73, 21, 65, 25.00),(74, 7, 66, 450.00),(75, 7, 67, 450.00),(76, 11, 68, 385.00),(77, 7, 71, 450.00),(78, 11, 73, 385.00),(79, 13, 73, 300.00),(80, 4, 75, 200.00),(82, 7, 73, 30.00),(83, 18, 73, 127.50),(84, 23, 73, 120.00),(85, 7, 73, 30.00),(86, 10, 77, 382.50),(87, 7, 79, 550.00),(88, 17, 79, 150.00);

给定的查询可能是边缘情况，导致本示例中没有结果。

             SELECT cart_id
         FROM cart
         INNER JOIN cartitem as c1 ON cart_id=c1.cartitem_cart_id AND c1.cartitem_package_id= 7
         INNER JOIN cartitem as c3 ON cart_id=c3.cartitem_cart_id AND c3.cartitem_package_id= 9  
         INNER JOIN cartitem as c4 ON cart_id=c4.cartitem_cart_id AND c4.cartitem_package_id= 13  
         INNER JOIN cartitem as c5 ON cart_id=c5.cartitem_cart_id AND c5.cartitem_package_id= 17  
         INNER JOIN cartitem as c6 ON cart_id=c6.cartitem_cart_id AND c6.cartitem_package_id= 21

输出:

cart_id
-------------
19
19

在本例中，查询应返回包含连接到包裹 (7,9,13,17,21) 的商品的所有购物车。

Answer 1

我解决您问题的方法是:

SELECT
    cart_id
FROM
    cart
INNER JOIN
    cartitem
ON
    cart_id = cartitem_cart_id
WHERE
    cartitem_package_id IN (7,9,13,17,21)      -- items that got to be in the cart
GROUP BY
    cart_id
HAVING
    count(distinct cartitem_package_id) = 5    -- number of different packages
;

<强> DEMO 使用您的数据

说明

原则是首先使用所需值的列表进行过滤，这里是您的包。现在计算每个购物车的不同包裹 (GROUP BY cart_id)。如果此计数与过滤器列表中的值数量匹配，则每个包裹都必须在此购物车中。

如果您从子选择中获取这些值，则可以将 IN 子句的值列表替换为子选择。

您应该看到这种方法应该很容易适应类似的需求。