在 Derby DB 中使用 NOT NULL 表达式执行查询的时间很长

Question

我有一个名为 ATTACHMENT 的表，带有以下列：

COLUMN_NAME          TYPE_NAME  COLUMN_SIZE
---------------------------------------------------
DTYPE                VARCHAR    31
ID                   VARCHAR    36
VERSION              BIGINT     19
TYPE                 INTEGER    10
FILENAME             VARCHAR    100
DATA                 BLOB       9437211
SIZE                 INTEGER    10
CHECKSUM             BIGINT     19
AUTHOR               VARCHAR    36
FILEDATE             DATE       10
FILETIME             TIME       8
CREATIONDATE         DATE       10
CREATIONTIME         TIME       8
FILETYPE             INTEGER    10
SYSTEM               SMALLINT    5
ORIGINALPICTUREID    VARCHAR    36
COMPRESSEDPICTUREID  VARCHAR    36
FIRSTUSE             VARCHAR    120

当我运行简单的测试 SQL 查询时：

SELECT ID FROM ATTACHMENT WHERE ORIGINALPICTUREID IS NOT NULL;

此查询执行时间很长（30 秒）

但是当我运行下一个没有 IS NOT NULL 表达式的测试 SQL 查询时：

SELECT ID FROM ATTACHMENT WHERE ORIGINALPICTUREID IS NULL;

此查询仅执行 2 秒。

在实际系统中，我有脚本：

select ATTACHMENT.ID,
       ATTACHMENT.SIZE,
       ATTACHMENT.AUTHOR,
       ATTACHMENT.FILENAME,
       ATTACHMENT.FILETIME,
       ATTACHMENT.FILEDATE,
       ATTACHMENT.CREATIONDATE,
       ATTACHMENT.CREATIONTIME,
       ATTACHMENT.FILETYPE,
       ATTACHMENT.COMPRESSEDPICTUREID,
       ATTACHMENT.ORIGINALPICTUREID,
       ATTACHMENT.FIRSTUSE
from ATTACHMENT,
     MESSAGECONTENT_ATTACHMENT,
     MESSAGECONTENT
where ATTACHMENT.ID not in (select distinct ATTACHMENT.ORIGINALPICTUREID
                            from ATTACHMENT
                            where ATTACHMENT.ORIGINALPICTUREID is not null)
and   ATTACHMENT.ID not in (select distinct COMPRESSEDPICTUREID
                            from ATTACHMENT
                            where ORIGINALPICTUREID is not null)
and   MESSAGECONTENT_ATTACHMENT.MESSAGECONTENT_ID = MESSAGECONTENT.ID
and   MESSAGECONTENT_ATTACHMENT.ATTACHMENTS_ID = ATTACHMENT.ID
and   ATTACHMENT.DTYPE = 'P'
and   MESSAGECONTENT.PERSONIDPATIENT = '0584393a-0955-4c9b-98f7-d31c991d22a3'
and   (ATTACHMENT.FILENAME like '%jpeg' 
       or ATTACHMENT.FILENAME like '%jpg' 
       or ATTACHMENT.FILENAME like '%tiff' 
       or ATTACHMENT.FILENAME like '%tif' 
       or ATTACHMENT.FILENAME like '%bmp' 
       or ATTACHMENT.FILENAME like '%gif' 
       or ATTACHMENT.FILENAME like '%png' 
       or ATTACHMENT.FILENAME like '%ser')

这个脚本执行的时间非常非常长。

你能帮我如何解决我的 Derby DB 中的 SQL 查询中的 IS NOT NULL 表达式问题吗？

谢谢！

Answer 1

你在这个查询上杀死了自己，主要是因为你的区别不是空值......你分别对原始附件和压缩附件吹了两次，但你只对一个病人感兴趣。 我已经重组了查询，从您想要的世卫组织开始......患者个人 ID。 从那里，加入邮件附件。 你只关心任何附着在这个人身上的东西。 这应该会产生非常小的记录集。 在这些记录中，只有那些你愿意查看附件表本身，看看是否有任何符合你的DPTYPE，比如条件和IS NULL。

我会确保您至少在 （PersonIDPatient） 上的 messageContent 表上有一个索引，如果在第一个位置之后有任何其他列，没问题。 与其他表的联接似乎位于其各自的主 ID 列上，并假定您在这些表上具有索引。

SELECT 
      atch.ID, 
      atch.SIZE, 
      atch.AUTHOR, 
      atch.FILENAME, 
      atch.FILETIME, 
      atch.FILEDATE, 
      atch.CREATIONDATE, 
      atch.CREATIONTIME, 
      atch.FILETYPE, 
      atch.COMPRESSEDPICTUREID, 
      atch.ORIGINALPICTUREID, 
      atch.FIRSTUSE 
   FROM 
      MESSAGECONTENT msgCont
         JOIN MESSAGECONTENT_ATTACHMENT msgAtt
            ON msgCont.ID = msgAtt.MESSAGECONTENT_ID
            JOIN ATTACHMENT atch
               ON msgAtt.ATTACHMENTS_ID = atch.ID 
              AND atch.DTYPE = 'P' 
              AND atch.ORIGINALPICTUREID IS NOT NULL
              AND atch.CompressedPictureID IS NOT NULL
              AND (   atch.FILENAME LIKE '%jpeg' 
                   OR atch.FILENAME LIKE '%jpg' 
                   OR atch.FILENAME LIKE '%tiff' 
                   OR atch.FILENAME LIKE '%tif' 
                   OR atch.FILENAME LIKE '%bmp' 
                   OR atch.FILENAME LIKE '%gif' 
                   OR atch.FILENAME LIKE '%png' 
                   OR atch.FILENAME LIKE '%ser')
   WHERE 
      msgCont.PersonIDPatient = '0584393a-0955-4c9b-98f7-d31c991d22a3' 

Answer 2

查询中的 NOT IN 运算符不使用任何索引。

避免在查询中使用 NOT IN 运算符。

为了找到不符合特定标准的结果，它必须根据条件检查所有记录，这使得索引的存在无关紧要。

此外，不要使用通配符 %，请尝试使用全文索引并查询数据库，例如

选择"Col1"、"Col2 "、"..."。

从表

其中 Col1 包含（Col1，"搜索"）和 Col1

包含（Col1，"搜索2"）.........