mysql在按另一列排序的列中选择特定数量的重复值

gdx19jrr 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(365)

SELECT notes.* FROM notes 
WHERE notes.id IN (
SELECT T1.id 
FROM notes as T1 
WHERE ( 
    SELECT COUNT(*)
    FROM notes as T2 
    WHERE T2.category_id = T1.category_id 
    AND T2.created_at > T1.created_at 
    AND T1.user_id = T2.user_id
) < N
) 
AND user_id = 2

此查询在每个类别中选择n行，并按创建时间排序。一切正常，直到：
1）结果数大于50，因为性能下降是线性的或更糟：对于200行，即使有索引，也只有2.6秒。
2）结果中给出了几个相等的数值。在这种情况下，您将得到超过n行的分类。
主要的问题是如何优化这个查询，或者是否可以编写另一个具有相同功能的查询？对于特定的用户id，1000行所需的性能为0.5秒。第2点是可选的。性能是主要问题。http://sqlfiddle.com/#!9/aa713f/3号
解释：

1   PRIMARY notes   
NULL
ref PRIMARY,user_id user_id 4   const   654 100.00  
NULL

1   PRIMARY T1  
NULL
eq_ref  PRIMARY PRIMARY 4   admin_bt.notes.id   1   100.00  Using where 
3   DEPENDENT SUBQUERY  T2  
NULL
ref category_id,created_at,user_id,catcrbabusr  catcrbabusr 4   admin_bt.T1.category_id 1148    3.33    Using where; Using index

sql mysql optimization greatest-n-per-group query-optimization

来源：https://stackoverflow.com/questions/51684243/mysql-select-specific-amount-of-duplicate-values-in-column-sorted-by-another-col

2条答案

按热度按时间

c6ubokkw1#

相关查询的成本自然很高，特别是在遍历大量第一级行时。它们通常不适合oltp。它们可以批量处理。
如果您需要向联机页面显示此响应，那么您可能应该在应用程序中缓存结果并定期（每10分钟）刷新一次。
在任何情况下，即使使用索引，表越大，查询的速度就越慢。
另一种选择是定期预处理子查询并将其存储在表中。然后对它提出质疑。此查询适用于快速响应。我不知道mysql是否有，但是postgresql有“物化视图”可以满足这个目的（可以按需重新刷新）。

赞(0）回复(0）举报 2021-06-20

ebdffaop2#

我不确定我是否完全理解您的查询的逻辑，但下面的查询至少会产生相同的结果（而且肯定会在更短的时间内）：
这假定索引位于（用户标识、类别标识、创建位置）

SELECT x.* 
  FROM notes x
  JOIN 
     ( SELECT user_id
            , category_id
            , MAX(created_at) created_at
         FROM notes
        WHERE user_id IN(2)
        GROUP
           BY user_id
            , category_id
     ) y
    ON y.user_id = x.user_id
   AND y.category_id = x.category_id
   AND y.created_at = x.created_at;

这是另一个你可能喜欢玩的主意。。。

SELECT id
     , created_at
     , user_id
     , category_id
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = category_id THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev := category_id
         FROM notes x
            , (SELECT @prev:=null,@i:=0) vars
        WHERE user_id = 2
        ORDER  
           BY category_id
            , created_at
      ) n
  WHERE i <= 2;

赞(0）回复(0）举报 2021-06-20

我来回答

mysql在按另一列排序的列中选择特定数量的重复值

2条答案

相关问题

热门标签

最新问答