mysql - 使用 OR 条件优化 DISTINCT SQL 查询

Question

我有以下 SQL 查询:

SELECT DISTINCT business_key
FROM Memory
WHERE concept <> 'case' OR attrib <> 'status' OR value <> 'closed'

我试图实现的是获取所有没有记录概念=案例和属性=状态和值=关闭的唯一业务键。在具有 500 000 条记录和所有唯一 business_keys 的 MySQL 中运行此查询非常慢:大约 11 秒。

我为 business_key 列、概念、属性和值列放置了索引。我还尝试对所有三列(概念、属性、值)使用组合索引，但结果是一样的。

这是 EXPLAIN EXTENDED 命令的屏幕截图:

有趣的是，在没有不同说明符的情况下运行查询会导致执行速度非常快。

我也试过这个:

SELECT DISTINCT m.business_key
FROM Memory m 
WHERE m.business_key NOT IN 
(SELECT c.business_Key 
 FROM Memory c 
 WHERE c.concept = 'case' AND c.attrib = 'status' AND c.value = 'closed')

结果更差:大约 25 秒

Answer 1

您可以添加复合(concept, attrib, value, business_key) 索引，以便查询(如果 MySQL 决定使用此索引)可以在索引中找到所有信息，而无需读取整张 table 。

您的查询等同于:

SELECT DISTINCT business_key
FROM Memory
WHERE NOT (concept = 'case' AND attrib = 'status' AND value = 'closed')

对此(可能会产生相同的执行计划):

SELECT business_key
FROM Memory
WHERE NOT (concept = 'case' AND attrib = 'status' AND value = 'closed')
GROUP BY business_key

---

由于要放入索引的4列都是VARCHAR(255)，所以索引长度会比较大。 MyISAM 不允许超过 1000 字节，InnoDB 不允许超过 3072。

一种解决方案是削减最后一部分的长度，使索引长度小于 1000:255+255+255+230 = 995:

(概念，属性，值，business_key(220))

它会起作用，但从性能方面来说，索引长度如此之大确实不好。

另一种选择是缩短全部或部分这 4 列的长度，前提是这符合您希望存储在那里的数据。如果您希望列中的最大值为 100，则无需声明长度 255。

您可以考虑的另一种选择是将这 4 列放在 4 个单独的引用表中。 (或者只是那些有重复数据的列。好像business_key会有重复数据，但不会有那么多，所以，为该列制作一个引用表不太好。)

示例:将 concept 值放入新表中，如下所示:

CREATE TABLE Concept_Ref
( concept_id INT AUTO_INCREMENT
, concept VARCHAR(255)
, PRIMARY KEY concept_id
, UNIQUE INDEX concept_idx (concept) 
) ;

INSERT INTO Concept_Ref
  ( concept )
SELECT DISTINCT
    concept
FROM
    Memory ;

然后更改 Memory 表:

ALTER TABLE Memory
ADD COLUMN concept_id INT ;

这样做(一次):

UPDATE 
    Memory m
  JOIN
    Concept_Ref c
      ON c.concept = m.concept
SET m.concept_id = c.concept_id

然后删除 Memory.concept 列:

ALTER TABLE Memory
DROP COLUMN concept ;

如果您将表从 MyISAM 更改为 InnoDB，您还可以添加 FOREIGN KEY 引用。

在对所有 4 列执行相同操作后，不仅 Memory 表中新复合索引的长度会小很多，而且您的表大小也会小很多。此外，使用任何这些列的任何其他索引的长度都将更小。

当然，查询需要编写 4 个 JOIN。并且必须更改和仔细设计此表的任何 INSERT、UPDATE 或 DELETE 语句。

但总的来说，我认为你会有更好的表现。根据您现在的设计，'case'、'status' 和 'closed' 等值似乎重复了很多次。