如何在使用计数和分组依据时加快查询速度

Question

我有两个表，名为卖方和项目。它们使用"n"到"m"外键关系通过第三个表 (seller_item( 连接。

现在，我

尝试回答要求："作为卖家，我想要一份竞争对手的列表，其中包含我正在销售的商品数量，他们也在销售"。因此，所有卖家的列表，其中包含与一个特定卖家相关的重叠项目计数。我也希望它按计数和限制排序。但是查询使用的是临时表和文件排序，这非常慢。解释 说：

使用地点;使用索引;使用临时的;使用文件排序

我怎样才能加快速度？

下面是查询：

SELECT
          COUNT(*) AS itemCount,
          s.sellerName
        FROM
          seller s,
          seller_item si
        WHERE
          si.itemId IN 
           (SELECT itemId FROM seller_item WHERE sellerId = 4711)
        AND
          si.sellerId=s.id
        GROUP BY
          sellerName
        ORDER BY
          itemCount DESC
        LIMIT 50;

该表定义：

CREATE TABLE `seller` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sellerName` varchar(50) NOT NULL
PRIMARY KEY (`id`),
UNIQUE KEY `unique_index` (`sellerName`),
) ENGINE=InnoDB 
contains about 200.000 rows

--

CREATE TABLE `item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`itemName` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_index` (`itemName`),
) ENGINE=InnoDB
contains about 100.000.000 rows

--

CREATE TABLE `seller_item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sellerId` bigint(20) unsigned NOT NULL,
`itemId` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sellerId` (`sellerId`,`itemId`),
KEY `item_id` (`itemId`),
CONSTRAINT `fk_1` FOREIGN KEY (`sellerId`) REFERENCES `seller` (`id`)            ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `fk_2` FOREIGN KEY (`itemId`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB
contains about 170.000.000 rows

数据库是Mysql Percona 5.6

解释输出：

+----+-------------+-------------+--------+----------------------+-----    ---------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys        | key                | key_len | ref                 | rows | Extra                                           |
+----+-------------+-------------+--------+----------------------+--------------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | s           | index  | PRIMARY,unique_index | unique_index | 152     | NULL                |    1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | si          | ref    | sellerId,item_id     | sellerId     | 8       | tmp.s.id            |    1 | Using index                                  |
|  1 | SIMPLE      | seller_item | eq_ref | sellerId,item_id     |  sellerId     | 16      | const,tmp.si.itemId |    1 | Using where; Using  index                     |
+----+-------------+-------------+--------+----------------------+--------------+---------+---------------------+------+----------------------------------------------+

Answer 1

我怀疑在您大小的数据库上实时快速运行这样的查询是否可行，特别是对于有大量热门商品库存的卖家。

你应该实现它。创建这样的表

CREATE TABLE
        matches
        (
        seller INT NOT NULL,
        competitor INT NOT NULL,
        matches INT NOT NULL,
        PRIMARY KEY
                (seller, competitor)
        )

并在 cron 脚本中批量更新它：

DELETE
FROM    matches
WHERE   seller = :seller
INSERT
INTO    matches (seller, competitor, matches)
SELECT  si.seller, sc.seller, COUNT(*) cnt
FROM    seller_item si
JOIN    seller_item sc
ON      sc.item = si.item
        AND sc.seller <> si.seller
WHERE   si.seller = :seller
GROUP BY
        si.seller, sc.seller
ORDER BY
        cnt DESC
LIMIT   50

您还需要(seller, item) seller_item上的PRIMARY KEY。现在的方式是，按项目查找卖家需要两次查找而不是一次：首先使用 KEY (item) 按项目查找，然后使用 PRIMARY KEY (id) 按 id 进行卖家

Answer 2

我相信您对消除使用临时的能力有误解;使用文件排序步骤来满足查询。 表单的查询

 SELECT COUNT(*), grouping_value
   FROM table
  GROUP BY grouping_value
  ORDER BY COUNT(*) 
  LIMIT n

始终

使用临时内存中结果集，并始终对该结果集进行排序。这是因为在查询运行之前，结果集在任何地方都不存在，并且必须先对其进行排序，然后才能满足 LIMIT 子句。

"文件排序"有点名称错误。这并不一定意味着正在对文件系统中的文件进行排序，只是正在对临时结果集进行排序。如果该结果集很大，则排序可以从 RAM 溢出到文件系统中，但并非必须如此。 请阅读此内容。https://www.percona.com/blog/2009/03/05/what-does-using-filesort-mean-in-mysql/不要被EXPLAIN结果中的Using filesort项目分心。

从此类查询中获得更好性能的技巧之一是最小化排序结果的大小。您已经将它们过滤到您想要的内容;很好。

但是，您仍然可以安排对较少的内容进行排序，只需对seller.id和计数进行排序，然后在知道所需的确切五十行后加入(更长的(sellerName。这样做的好处是，您可以仅使用seller_item表进行聚合，而不是使用连接两者的结果集。

这就是我的意思。此子查询生成所需 50 个sellerId值的列表。 它所要排序的只是计数和sellerId。这比对计数和sellerName进行排序更快，因为在排序操作中需要随机处理的数据和固定长度的数据较少。

SELECT COUNT(*) AS itemCount,
       sellerId
  FROM seller_item 
 WHERE itemId IN
        (SELECT itemId FROM seller_item WHERE sellerId = 4711)
 GROUP BY SellerId
 ORDER BY COUNT(*) DESC
 LIMIT 50

请注意，这会对一个大结果集进行排序，然后丢弃其中的大部分。它为您提供所需的确切五十个卖家ID值。

通过在 GROUP BY 子句后面添加 HAVING COUNT(*) > 1 来筛选出更多行，可以更快地完成此操作，但这会更改查询的含义，并且可能无法满足您的业务需求。

拥有这五十件商品后，您可以检索卖家名称。 整个查询如下所示：

SELECT s.sellerName, c.itemCount
  FROM seller s
  JOIN (
         SELECT COUNT(*) AS itemCount, sellerId
           FROM seller_item 
          WHERE itemId IN
                      (SELECT itemId FROM seller_item WHERE sellerId = 4711)
                GROUP BY SellerId
                ORDER BY COUNT(*) DESC
                LIMIT 50
       ) c ON c.sellerId = s.id
 ORDER BY c.itemCount DESC

索引工作应该花在尝试快速进行内部查询上。无论如何，外部查询都会很快;它只处理五十行，并使用索引id值来查找其他值。

最内在的查询是 SELECT itemId FROM seller_item WHERE sellerId = 4711 。这将从您现有的(sellerId, itemId)复合索引中受益匪浅：它可以随机访问然后扫描该索引，这非常快。

SELECT COUNT(*)...查询将受益于(itemId, sellerId)复合索引。查询的这一部分是困难和缓慢的部分，但此索引仍然会有所帮助。

看，其他人提到了这一点，我也会提到这一点。在该seller_item表上同时具有唯一的组合键(sellerId, itemId)和主键id，恕我直言，这是非常浪费的。

• 它使您的更新和插入速度变慢。
• 这意味着您的表根据无意义的id而不是有意义的值对组织为树。

如果你创建我提到的两个索引中的一个主键，并创建另一个而不使其唯一，你将拥有一个更有效的表。 这些多对多联接表不需要也不应该具有代理键。

Answer 3

重新表述

我认为这是您真正想要的：

SELECT  si2.sellerId, COUNT(DISTINCT si2.itemId) AS itemCount
    FROM  seller_item si1
    JOIN  seller_item si2 ON si2.itemId = si1.itemId
    WHERE  si1.sellerId = 4711
    GROUP BY  si2.sellerId
    ORDER BY  itemCount DESC
    LIMIT  50;

(注意：DISTINCT可能是不必要的。

换句话说：对于卖家#4711，找到他出售的商品，然后找到哪些卖家销售几乎相同的商品。 (我没有尝试从结果集中过滤掉#4711。

更高效的 N：M

但仍然存在效率低下的问题。 让我们剖析您的多对多映射表 (seller_item(。

• 它有一个可能不用于任何事情的id。 摆脱它。
• 然后将UNIQUE(sellerId, itemId)提升到PRIMARY KEY(sellerId, itemId)。
• 现在INDEX(itemId)更改为INDEX(itemId, sellerId)，以便查询的最后阶段可以"使用 index"。

博客进一步讨论。

你

有一个非常大的数据集;你已经调试了你的应用。 考虑删除FOREIGN KEYs;它们有些昂贵。

获取卖家名称

也许可以JOIN到sellers才能得到sellerName。 但是先用sellerId试试。 然后添加名称。 验证计数不会膨胀(经常发生(，并且查询不会变慢。

如果任何一件事出错，那就做

SELECT s.sellerName, x.itemCount
    FROM ( .. the above query .. ) AS x
    JOIN sellers AS s  USING(sellerId);

(

(或者，您可以添加ORDER BY sellerName。

Answer 4

我不确定这在您的数据库上的速度有多快，但我会像这样编写查询。

    select * from (
        select seller.sellerName,
        count(otherSellersItems.itemId) itemCount from (
            select sellerId, itemId from seller_item where sellerId != 4711
        ) otherSellersItems
        inner join ( 
            select itemId from seller_item where sellerId = 4711
        ) thisSellersItems
        on otherSellersItems.itemId = thisSellersItems.itemId
        inner join seller
        on otherSellersItems.sellerId = seller.id
        group by seller.sellerName
    ) itemsSoldByOtherSellers
    order by itemCount desc
    limit 50 ;

Answer 5

由于我们将(可能很大的(结果集限制为最多 50 行，因此我会推迟到我们获得计数之后才能获取卖方名称，因此我们只需要获取 50 个卖方名称。

首先，我们按seller_id获得项目计数

SELECT so.seller_id
     , COUNT(*) AS itemcount
  FROM seller_item si
  JOIN seller_item so
    ON so.item_id = si.item_id
 WHERE si.seller_id = 4711
 GROUP BY so.seller_id
 ORDER BY COUNT(*) DESC, so.seller_id DESC
 LIMIT 50

为了提高性能，我会为连接so提供一个合适的覆盖索引，例如

CREATE UNIQUE INDEX seller_item_UX2 ON seller_item(item_id,seller_id) 

通过使用"覆盖索引"，MySQL可以完全从索引页满足查询，而无需访问基础表中的页面。

创建新索引后，我会将索引放在单例item_id列上，因为该索引现在是多余的。(任何可以有效使用该索引的查询都将能够有效利用以item_id作为前导列的复合索引。

无法

绕过"使用文件排序"操作。MySQL必须评估每一行的COUNT((聚合，然后才能执行排序。MySQL没有办法(给定当前模式(使用索引按顺序返回行以避免排序操作。

一旦我们有了那组(最多(五十行，那么我们就可以得到卖家名称。

要获取卖方名称，我们可以在 SELECT 列表中使用相关的子查询，也可以使用连接操作。

1( 在 SELECT 列表中使用相关的子查询，例如

SELECT so.seller_id
     , ( SELECT s.sellername
           FROM seller s
          WHERE s.seller_id = so.seller_id
          ORDER BY s.seller_id, s.sellername
          LIMIT 1
       ) AS sellername   
     , COUNT(*) AS itemcount
  FROM seller_item si
  JOIN seller_item so
    ON so.item_id = si.item_id
 WHERE si.seller_id = 4711
 GROUP BY so.seller_id
 ORDER BY COUNT(*) DESC, so.seller_id DESC
 LIMIT 50

(

我们知道子查询将(最多(执行五十次，外部查询返回的每一行执行一次。50次处决(有合适的索引可用(并不是那么糟糕，至少与50,000次处决相比。

或者，2( 使用连接操作，例如

SELECT c.seller_id
     , s.sellername
     , c.itemcount
  FROM ( 
         SELECT so.seller_id
              , COUNT(*) AS itemcount
           FROM seller_item si
           JOIN seller_item so
             ON so.item_id = si.item_id
          WHERE si.seller_id = 4711
          GROUP BY so.seller_id
          ORDER BY COUNT(*) DESC, so.seller_id DESC
          LIMIT 50
       ) c
  JOIN seller s
    ON s.seller_id = c.seller_id
 ORDER BY c.itemcount DESC, c.seller_id DESC    

(同样，我们知道内联视图c将返回(最多(五十行，并且获得五十个卖方名称(使用合适的索引(应该很快。

---

汇总表

如果我们对实现进行非规范化，并添加包含item_id(作为主键(和该item_id卖家数量的"计数"的摘要表，我们的查询可以利用这一点。

作为可能看起来像什么的例证：

CREATE TABLE item_seller_count
( item_id BIGINT NOT NULL PRIMARY KEY
, seller_count BIGINT NOT NULL
) Engine=InnoDB
;
INSERT INTO item_seller_count (item_id, seller_count)
SELECT d.item_id
     , COUNT(*)
  FROM seller_item d
 GROUP BY d.item_id
 ORDER BY d.item_id
;
CREATE UNIQUE INDEX item_seller_count_IX1 
  ON item_seller_count (seller_count, item_id)
;

从seller_item表中插入/更新/删除行时，新的汇总表将变为"不同步"。

填充此表将占用资源。但是，提供此功能将加快我们正在处理的查询类型。