mysql – 使用复杂过滤优化SQL查询

印度阿三17 2019-06-09

展开全文

请注意,以下问题专门针对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值.

现在,想象一下使用DISTINCT对(maker_id,status_id).

SELECT DISTINCT maker_id, status_id FROM Cars;

以下是SQL Fiddle：http:///#!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值对应的DISTINCT(maker_id,status_id)对具有多于1个不同的记录,则返回除了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:///#!9/cb1c7/3中的一个例子

解决方法:

有几种查询模式可以返回指定的结果.有些看起来比其他看起来更复杂.性能可能会有很大差异.

如果MySQL无法利用索引来优化该操作,那么在庞大的集合上执行GROUP BY操作可能成本很高(就资源和已用时间而言).(使用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列中显示“Using index”.我们并不期待“使用filesort”.

这种方法的一个重大缺点是,有效地将两次通过表(或索引).

第一个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条件的原因是我通常使用UNION ALL获得更好的查询计划.当谓词在不同的列和条件上时,MySQL优化器似乎对OR没有做得太好,并且谓词可以用于“驱动”执行计划.使用UNION ALL,将其分为两个查询,我们通常可以为这两个部分制定一个好的计划.

来源：http://www./content-2-232301.html