logo标签列表

通过复杂过滤优化SQL查询

mysqlsql
4
请注意以下问题仅适用于MySQL。
假设有一个名为Cars的表，其结构如下（我们可以忽略缺乏适当键限制等因素，因为这与我的问题无关）：
CREATE TABLE Cars
(
  id Integer,
  maker_id Integer,
  status_id Integer,
  notes Varchar(100)
);

现在想象一下像这样加载一些测试数据：
INSERT INTO Cars
(id, maker_id, status_id, notes)
VALUES
(1, 1001, 0, 'test1'),
(2, 1001, 0, 'test2'),
(3, 1001, 0, 'test3'),
(4, 1002, 0, 'test4'),
(5, 1002, 0, 'test5'),
(6, 1002, 1, 'test6'),
(7, 1002, 1, 'test7'),
(8, 1002, 2, 'test8'),
(9, 1003, 3, 'test9'),
(10, 1003, 3, 'test10'),
(11, 1003, 4, 'test11'),
(12, 1003, 4, 'test12'),
(13, 1003, 5, 'test13'),
(14, 1003, 5, 'test14')

这里有14条记录，maker_id （1001、1002、1003）有3个DISTINCT值，而status_id（0、1、2、3、4、5）有6个DISTINCT值。

现在，想象一下将(maker_id, status_id)这些DISTINCT的组合取出来。

SELECT DISTINCT maker_id, status_id FROM Cars;

以下是SQL Fiddle示例的链接：http://sqlfiddle.com/#!9/cb1c7/2。
下面是所需返回的记录（maker_id，status_id）:
- (1001, 0) - (1002, 0) - (1002, 1) - (1002, 2) - (1003, 3) - (1003, 4) - (1003, 5)
需要返回的逻辑如下：
- 如果给定的maker_id值（例如1001）仅具有对应的DISTINCT (maker_id, status_id) 对中的1条不同记录，则仅返回它。在这个例子中：(1001, 0)。 - 如果给定的maker_id值具有多于1个对应的DISTINCT (maker_id, status_id) 对的不同记录，则返回所有记录，除了一个status_id值为0的记录。在这个例子中：(1002, 1)，(1002, 2)，(1003, 3)，(1003, 4)和(1003, 5)。
请注意，我们省略了(1002, 0)。
是否有人能想到更简洁/更有效（就运行时间而言）的编写此查询的方法？在现实世界中，我的表拥有数百万条记录。
我提出了以下解决方案：
SELECT
  subq.maker_id,
  subq.status_id
FROM
(
  SELECT DISTINCT
    maker_id,
    status_id,
    (SELECT COUNT(*) FROM Cars WHERE maker_id = c.maker_id AND status_id != 0 GROUP BY maker_id) AS counter
  FROM Cars AS c
) AS subq

WHERE
  subq.counter IS NULL
  OR (subq.counter IS NOT NULL AND subq.status_id != 0)
;

这是一个 SQL Fiddle 的例子： http://sqlfiddle.com/#!9/cb1c7/3

1
  1. 你有哪些索引？
  2. 首先分别子查询这两个案例。
 - philipxy
是的。这样可以避免分组。 - philipxy
1
@cuddlyhugbear：在一个巨大的表上没有索引就像是一盏闪烁的黄色警示灯，而无法创建索引则像是一声刺耳的喇叭声。我不想乘坐那艘船，因为那艘船肯定有严重的问题。在现实世界中，拥有数百万行数据的表都会有索引。从性能角度来看，UNION ALL比UNION更好，因为它避免了进行排序和去重的开销。 - spencer7593
没有在表上创建索引，我们甚至不能抱有希望，奥比万·克诺比也无法帮助我们提高性能。黑暗面已经获胜了。无论是对于一个巨大的数据集进行排序还是多次通过一个巨大的表，都无法避免。 - spencer7593
感谢您的评论 - 非常感激！关于索引，您说得完全正确。我已经建议这样做，但我自己无法控制，所以希望理性的声音能获胜！ - cuddlyhugbear
显示剩余5条评论
4个回答
2
有几种查询模式可以返回指定的结果。有些看起来比其他的更复杂。性能可能会有很大的差异。
在一个巨大的数据集上执行 GROUP BY 操作可能是昂贵的（特别是在 MySQL 无法利用索引来优化该操作的情况下，这将消耗资源和时间）。 （使用 GROUP BY 操作是获取每个 maker_id 的 status_id 计数的一种方法。）
而相关子查询在重复执行时可能会很昂贵。我通常只在需要执行次数有限的情况下才能看到相关子查询的性能有所提高。
我认为获得良好性能的最佳方法可能是这样的：
未经测试
 SELECT c.maker_id
      , c.status_id
   FROM Cars c
  WHERE c.status_id > 0

 UNION ALL

 SELECT d.maker_id
      , d.status_id
   FROM Cars d
   LEFT
   JOIN Cars e
     ON e.maker_id = d.maker_id
    AND e.status_id > 0
  WHERE e.maker_id IS NULL  
    AND d.status_id = 0

关于这种查询方法是否比其他查询方法更高效或更简洁，我们需要进行测试。

但是为了获得良好的性能，我们需要使用索引来执行此查询。

..  ON Cars (maker_id, status_id)

我们希望EXPLAIN输出结果在Extra列中显示"使用索引"，而不是"使用文件排序"。
这种方法的一个大缺点是需要对表格（或索引）进行两次遍历。
第一个SELECT非常简单...获取所有status_id不为零的行。我们需要所有这些行。可能会使用索引。
... ON Cars (status_id, maker_id)

如果我们返回表格的大部分内容，那么使用另一个索引的全扫描速度可能会与此一样快，甚至更快。

第二个SELECT使用了反连接模式。这是在获取所有status_id等于零的行，并从该集合中“过滤掉”任何存在其他具有非零status_id的maker_id行的行。

我们使用外连接操作(LEFT JOIN)进行过滤以返回所有具有status_id=0和所有匹配行的行。关键是WHERE子句中的谓词，它过滤掉了所有已经匹配的行。所以我们留下的是没有找到匹配项的行。也就是说，只有status_id=0行的maker_id值。

我们可以使用NOT EXISTS谓词而不是反连接来获得等效的结果。但根据我的经验，有时性能并不好。我们可以重写第二个SELECT(在UNION ALL操作之后)。

 SELECT d.maker_id
      , d.status_id
   FROM Cars d
  WHERE d.status_id = 0
    AND NOT EXISTS
        ( SELECT 1
            FROM Cars e
           WHERE e.maker_id = d.maker_id
             AND e.status_id > 0
        )

这个查询的性能取决于一个合适的索引，就像反连接一样。

重要提示：不要省略 ALL 关键字。UNION ALL 操作只是将两个查询的结果拼接在一起。如果省略 ALL 关键字，则需要 MySQL 执行“排序去重”操作以消除重复行。

注意：使用 UNION ALL 而不是 OR 条件的原因是，我通常会得到更好的查询计划。当谓词在不同的列和条件上时，MySQL 优化器似乎无法很好地处理 OR，并且任何一个谓词都可以用于“驱动”执行计划。通过将其分成两个查询来使用 UNION ALL，我们通常可以为两个部分获得良好的计划。

非常周到的帖子，谢谢！您说得对，使用UNION ALL方法确实可以提高性能。现在正在处理查询的详细信息，以便在实际（大型）数据集上进行操作。 - cuddlyhugbear
@cuddlyhugbear：我在另一个答案中添加了另一种可能的方法。如果表上没有索引，并且无法创建索引（出于任何原因），那么我其他答案中的查询可能会更快。 - spencer7593
等一下，这个答案中的 DISTINCT 或 GROUP BY 在哪里？ - Arth
@cuddlyhugbear: 我完全忽略了去除“重复”的元组的规定。这个查询的快速修复方法是删除 ALL 关键字，将 UNION ALL 替换为只有 UNION，然后瞧，我们就得到了一个排序唯一操作。 - spencer7593
cuddlyhugbear: 你说得对。你需要进行测试。理论上，对两个较小的集合执行“唯一排序”操作可能比对整个集合执行“唯一排序”操作更快。 （两个集合之间的差异是重复项，在这种特定情况下并不是一个问题，因为我们保证这两个集合是不同的。一个集合仅具有status_id=0行，另一个则没有这些行。但如果您需要通过maker_id 对整个集合进行排序，则仅对总体集合排序可能会更快。 “在理论上，理论和实践没有区别。在实践中，有。” - spencer7593
显示剩余3条评论
1
这个查询会有所帮助 :)
select
    distinct c1.maker_id, c1.status_id
from
    Cars AS c1
where
    c1.status_id!=0
    or c1.maker_id not in (
        select distinct c2.maker_id
        from Cars AS c2
        where c2.status_id!=0
    )
1
作为对我第一个答案完全不同的方法...
考虑到一个不可能的情况...表中没有索引，也没有创建索引的可能性...我们基本上只剩下两个非常糟糕的选择：对巨大的集合进行排序并通过它进行几次传递，或者对巨大的表进行大量的遍历。
两种选择中较小的恶就是排序。我们负担不起索引，但我们可以负担得起在查询中动态构建索引所需的循环。还有大量的磁盘IO / tmp。SSD上安装了散热器，对吧？
我们将简单地对整个集合进行排序，并运行几次。
  SELECT t.maker_id
       , t.status_id
    FROM ( SELECT IF(s.status_id=0 AND s.maker_id=@p_maker,NULL,s.status_id) AS status_id
                , @p_maker := s.maker_id AS maker_id
             FROM ( SELECT @p_maker := NULL ) i
            CROSS
             JOIN ( SELECT c.maker_id
                         , c.status_id
                      FROM Cars c
                     GROUP BY c.maker_id DESC, c.status_id DESC 
                     ORDER BY c.maker_id DESC, c.status_id DESC
                  ) s
         ) t
   WHERE t.status_id IS NOT NULL
1

我不完全确定性能如何，但我喜欢这种优雅的写法：

  SELECT maker_id,
         status_id
    FROM cars  
   WHERE status_id != 0
GROUP BY maker_id, status_id
   UNION ALL
  SELECT maker_id,
         MAX(status_id) max_status_id
    FROM cars  
GROUP BY maker_id
  HAVING max_status_id = 0
1
@spencer7593 谢谢，但是重复数据已经在样本数据中了，并且要求明确提到了不同的记录，随后也被要求。 - Arth
1
你说得完全正确。我完全忽略了重复元组的消除。这是一个非常聪明的方法。我喜欢它。 - spencer7593
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接