我有一个带有 2 个整数字段的 mysql 表“items”:seid 和 tiid
该表大约有 35000000 条记录，因此非常大。

seid  tiid  
-----------
1     1  
2     2  
2     3  
2     4  
3     4  
4     1  
4     2

该表在两个字段上都有一个主键，一个索引在 seid 上，一个索引在 tiid 上。

有人输入了 1 个或多个 tiid 值，现在我想获得结果最多的 seid。

例如，当有人键入 1、2、3 时，我希望得到 seid 2 和 4 作为结果。他们在 tiid 值上都有 2 个匹配项。

到目前为止我的查询:

SELECT COUNT(*) as c, seid
  FROM items
 WHERE tiid IN (1,2,3) 
GROUP BY seid
HAVING c = (SELECT COUNT(*) as c, seid
              FROM items
             WHERE tiid IN (1,2,3) 
          GROUP BY seid
          ORDER BY c DESC 
             LIMIT 1)

但是这个查询非常慢，因为表很大。

有谁知道如何为此目的构建更好的查询？

最佳答案

所以我找到了 2 个解决方案，第一个:

SELECT c,GROUP_CONCAT(CAST(seid AS CHAR)) as seid_list 
FROM (
    SELECT COUNT(*) as c, seid FROM items 
    WHERE tiid IN (1,2,3) 
    GROUP BY seid ORDER BY c DESC
) T1 
GROUP BY c 
ORDER BY c DESC
LIMIT 1;
+---+-----------+
| c | seid_list |
+---+-----------+
| 2 | 2,4       | 
+---+-----------+

编辑:

EXPLAIN SELECT c,GROUP_CONCAT(CAST(seid AS CHAR)) as seid_list  FROM (     SELECT COUNT(*) as c, seid FROM items      WHERE tiid IN (1,2,3)      GROUP BY seid ORDER BY c DESC ) T1  GROUP BY c  ORDER BY c DESC LIMIT 1;
+----+-------------+------------+-------+------------------+---------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table      | type  | possible_keys    | key     | key_len | ref  | rows | Extra                                                     |
+----+-------------+------------+-------+------------------+---------+---------+------+------+-----------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL             | NULL    | NULL    | NULL |    3 | Using filesort                                            | 
|  2 | DERIVED     | items      | range | PRIMARY,tiid_idx | PRIMARY | 4       | NULL |    4 | Using where; Using index; Using temporary; Using filesort | 
+----+-------------+------------+-------+------------------+---------+---------+------+------+-----------------------------------------------------------+

重新编辑:

第一个解决方案有一个问题，如果有数十亿行，结果字段可能太大。所以这是另一种解决方案，它通过使用 MySQl 变量应用经典的最大内存/检查来避免双彩虹效应:

SELECT c,seid
  FROM (
   SELECT c,seid,CASE WHEN @mmax<=c THEN @mmax:=c ELSE 0 END 'mymax'
     FROM (
       SELECT COUNT(*) as c, seid FROM items WHERE tiid IN (1,2,3)
        GROUP BY seid
        ORDER BY c DESC
    ) res1
   ,(SELECT @mmax:=0) initmax
   ORDER BY c DESC
 ) res2 WHERE mymax>0;
+---+------+
| c | seid |
+---+------+
| 2 |    4 | 
| 2 |    2 | 
+---+------+

解释:

+----+-------------+------------+--------+------------------+---------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table      | type   | possible_keys    | key     | key_len | ref  | rows | Extra                                                     |
+----+-------------+------------+--------+------------------+---------+---------+------+------+-----------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL             | NULL    | NULL    | NULL |    3 | Using where                                               | 
|  2 | DERIVED     | <derived4> | system | NULL             | NULL    | NULL    | NULL |    1 | Using filesort                                            | 
|  2 | DERIVED     | <derived3> | ALL    | NULL             | NULL    | NULL    | NULL |    3 |                                                           | 
|  4 | DERIVED     | NULL       | NULL   | NULL             | NULL    | NULL    | NULL | NULL | No tables used                                            | 
|  3 | DERIVED     | items      | range  | PRIMARY,tiid_idx | PRIMARY | 4       | NULL |    4 | Using where; Using index; Using temporary; Using filesort | 
+----+-------------+------------+--------+------------------+---------+---------+------+------+-----------------------------------------------------------+

关于sql - 在大表查询中获取计数匹配非常慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4674313/