NULL值如何影响数据库搜索中的性能？

Question

在我们的产品中，我们有一个通用搜索引擎，并试图优化搜索性能。查询中使用的很多表都允许使用空值。我们是否应该重新设计我们的表以禁止优化或不优化空值？

我们的产品上都Oracle和MS SQL Server运行。

Answer 1

在Oracle，NULL值未编入索引，我。即此查询：

SELECT * 
FROM table 
WHERE column IS NULL 

将始终使用全表扫描，因为索引不包含您需要的值。

更重要的是，这个查询：

SELECT column 
FROM table 
ORDER BY 
     column 

也将使用全表扫描和排序一样的道理。

如果你的价值观本质上不容许NULL的，然后标记列NOT NULL。

Answer 2

简答：是的，有条件！

空值和性能的主要问题是使用正向查找。

如果你插入一行到表中，空值，它被放置在于其属于自然页面。任何查询该记录的查询都会在适当的位置找到它。易至今....

...但是我们要说的页面填满了，现在该行被拥抱在其他行之间。仍然顺利...

...直到行更新，并且空值现在包含某些内容。行的大小超出了可用空间的大小，因此数据库引擎必须对此做些事情。

服务器要做的最快的事情是将该页的关闭页移动到另一页，并用一个前向指针替换该行的条目。不幸的是，当执行查询时，这需要额外的查找：一个查找行的自然位置，另一个查找当前位置。

因此，您的问题的简短答案是肯定的，这些字段不可为空将有助于搜索性能。如果经常发生在您搜索的记录中的空字段更新为非空，这尤其如此。

当然，还有其他惩罚（特别是I/O，尽管指向深度很小）与更大的数据集关联，然后您在应用程序问题上禁止在概念上需要它们的字段中的空值，但是，嘿，这就是另一个问题:)

Answer 3

如果列不包含空值，最好声明此列NOT NULL，优化程序可能能够采取更有效的路径。

但是，如果你有你的列的NULL你没有太多的选择（比它解决的一个非空的默认值可能会产生更多的问题）。

作为Quassnoi mentionned，空值未在甲骨文索引，或更精确地说，如果所有的索引列是NULL的行不会被索引，这意味着：

• 该空白可潜在地速度因为索引将有较少的行
• 如果向索引或甚至一个常量添加另一个NOT NULL列，仍可以索引NULL行。

以下脚本演示了一种方法，指数NULL值：

CREATE TABLE TEST AS 
SELECT CASE 
      WHEN MOD(ROWNUM, 100) != 0 THEN 
      object_id 
      ELSE 
      NULL 
     END object_id 
    FROM all_objects; 

CREATE INDEX idx_null ON test(object_id, 1); 

SET AUTOTRACE ON EXPLAIN 

SELECT COUNT(*) FROM TEST WHERE object_id IS NULL; 

Answer 4

以我的经验NULL是一个有效的值，通常是指“不知道”。如果你不知道，那么为列设置一些默认值或尝试强制执行一些NOT NULL约束确实毫无意义。 NULL恰好是一个特定的情况。

对NULL的真正挑战是它使检索复杂一点。例如，你不能说WHERE column_name IN（NULL，'value1'，'value2'）。

个人而言，如果您发现很多列或者某些列包含大量空值，我想您可能需要重新访问您的数据模型。也许这些空列可以放入子表中？例如：一个电话号码名称，家庭电话，手机，传真号码，工作号码，紧急号码等等的表格......您只能填入其中的一个或两个，并且会更好地对其进行标准化。

你需要做的是退一步，看看数据将如何被访问。这是一个应该有价值的专栏吗？这是只对某些情况有价值的专栏吗？这是一个将被质疑很多的专栏吗？

Answer 5

是否使用空值是因为它们影响性能，这是数据库设计平衡行为之一。你必须平衡业务需求与性能。

如果需要的话，应该使用空值。例如，你可能在表格中有开始日期和结束日期。您通常不知道创建记录时的结束日期。因此，您必须允许空值，不管它们是否影响性能或不影响性能，因为数据根本就不存在。但是，如果数据必须按业务规则在创建记录时存在，那么您不应该允许空值。这会提高性能，使编码更简单一些，并确保数据完整性得以保留。

如果您有现有数据要更改为不再允许空值，则必须考虑该更改的影响。首先，你知道你需要把什么值放入当前为空的记录中吗？其次，你是否有很多使用isnull或coalesce的代码需要更新（这些东西性能下降，所以如果你不再需要检查它们，你应该改变代码）？你需要一个默认值吗？你真的可以指派一个吗？如果不是，那么一些插入或更新代码会中断，如果它不考虑该字段不能再为空。有时候人们会输入不好的信息来让他们摆脱空位。所以现在价格字段需要包含十进制值和'未知'之类的东西，因此不能正确地成为十进制数据类型，然后你必须去各种长度才能进行计算。这通常会造成性能问题，比创建的空值差或更差。 PLus你需要浏览你的所有代码，并且你曾经使用过一个引用为空或不为空的字段，你需要重写以排除或包含基于可能的坏值，因为数据不被允许为空。

我做了很多从客户端数据导入的数据，每次我们得到一个文件，其中一些应该允许空值的字段没有，我们在导入到我们的系统之前需要清理垃圾数据。电子邮件就是其中之一。通常数据输入不知道这个值，它通常是某种类型的字符串数据，所以用户可以在这里输入任何内容。我们去导入电子邮件并找到“我不知道”的东西。努力尝试实际发送电子邮件到“我不知道”。如果系统需要有效的电子邮件地址并检查是否存在@符号，我们会得到'[email protected]'这样的垃圾数据如何对数据用户有用？

某些空值的性能问题是编写非严格查询的结果，有时只是重新排列where子句而不是省略必要的null可以提高性能。

Answer 6

在执行“NOT IN”查询时，可空字段可能会对性能产生重大影响。由于所有索引字段设置为null的行不会在B-Tree索引中编入索引，因此即使索引存在，Oracle也必须执行全表扫描以检查是否存在空位。例如：

create table t1 as select rownum rn from all_objects; 

create table t2 as select rownum rn from all_objects; 

create unique index t1_idx on t1(rn); 

create unique index t2_idx on t2(rn); 

delete from t2 where rn = 3; 

explain plan for 
select * 
    from t1 
where rn not in (select rn 
        from t2); 

--------------------------------------------------------------------------- 
| Id | Operation   | Name | Rows | Bytes | Cost (%CPU)| Time  | 
--------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT |  | 50173 | 636K| 3162 (1)| 00:00:38 | 
|* 1 | FILTER   |  |  |  |   |   | 
| 2 | TABLE ACCESS FULL| T1 | 50205 | 637K| 24 (5)| 00:00:01 | 
|* 3 | TABLE ACCESS FULL| T2 | 45404 | 576K|  2 (0)| 00:00:01 | 
--------------------------------------------------------------------------- 

查询必须检查空值，因此必须做T2的全表扫描对于t1的每一行。

现在，如果我们使字段不可为空，它可以使用索引。

alter table t1 modify rn not null; 

alter table t2 modify rn not null; 

explain plan for 
select * 
    from t1 
where rn not in (select rn 
        from t2); 

----------------------------------------------------------------------------- 
| Id | Operation   | Name | Rows | Bytes | Cost (%CPU)| Time  | 
----------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT |  | 2412 | 62712 | 24 (9)| 00:00:01 | 
| 1 | NESTED LOOPS ANTI |  | 2412 | 62712 | 24 (9)| 00:00:01 | 
| 2 | INDEX FULL SCAN | T1_IDX | 50205 | 637K| 21 (0)| 00:00:01 | 
|* 3 | INDEX UNIQUE SCAN| T2_IDX | 45498 | 577K|  1 (0)| 00:00:01 | 
----------------------------------------------------------------------------- 

Answer 7

一个额外的答案得出一些额外的关注大卫·阿尔德里奇的评论Quassnoi的接受的答案。

声明：

此查询：

SELECT * FROM表WHERE列 IS NULL

将始终使用全表扫描

是不正确的。下面是使用索引用文字值的反例：

SQL> create table mytable (mycolumn) 
    2 as 
    3 select nullif(level,10000) 
    4  from dual 
    5 connect by level <= 10000 
    6/

Table created. 

SQL> create index i1 on mytable(mycolumn,1) 
    2/

Index created. 

SQL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true) 

PL/SQL procedure successfully completed. 

SQL> set serveroutput off 
SQL> select /*+ gather_plan_statistics */ * 
    2 from mytable 
    3 where mycolumn is null 
    4/

    MYCOLUMN 
---------- 


1 row selected. 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) 
    2/

PLAN_TABLE_OUTPUT 
----------------------------------------------------------------------------------------- 
SQL_ID daxdqjwaww1gr, child number 0 
------------------------------------- 
select /*+ gather_plan_statistics */ * from mytable where mycolumn 
is null 

Plan hash value: 1816312439 

----------------------------------------------------------------------------------- 
| Id | Operation  | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | 
----------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT |  |  1 |  |  1 |00:00:00.01 |  2 | 
|* 1 | INDEX RANGE SCAN| I1 |  1 |  1 |  1 |00:00:00.01 |  2 | 
----------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 

    1 - access("MYCOLUMN" IS NULL) 


19 rows selected. 

正如你所看到的，正在使用的索引。

Regards， Rob。

Answer 8

我会说测试是必需的，但很高兴知道其他人的经验。根据我在ms sql server上的经验，空值可能会导致巨大的性能问题（差异）。在一个非常简单的测试中，我已经看到在45秒内返回的查询在表create语句中的相关字段上设置为非空值，并且在25分钟内没有设置时返回（我放弃了等待并刚刚在估计的查询计划）。

测试数据是100万行x 20列，它们是在Windows 8.1上的i5-3320普通HD和8GB RAM（使用2GB的SQL Server）/ SQL Server 2012企业版上的62个随机小写字母字符构建的。使用随机数据/不规则数据来使测试成为现实的“更糟”的情况是非常重要的。在这两种情况下，表都被重新创建并重新加载了随机数据，这些数据在已经具有适当数量的可用空间的数据库文件上花费了大约30秒。

select count(field0) from myTable where field0 
        not in (select field1 from myTable) 1000000 

CREATE TABLE [dbo].[myTable]([Field0] [nvarchar](64) , ... 

vs 

CREATE TABLE [dbo].[myTable]([Field0] [nvarchar](64) not null, 

由于性能方面的原因，这两个表都有表选项data_compression =页面集，其他所有内容都是默认的。没有索引。

alter table myTable rebuild partition = all with (data_compression = page); 

由于没有空是在内存优化表对此我没有具体然而使用SQL Server将明显做什么要求，在这种特定的情况下，似乎是大量有利于没有在零点最快数据和使用非空表创建。

此表上相同表单的任何后续查询都会在两秒内返回，因此我将假定标准默认统计信息以及可能使（1.3GB）表适合内存的操作正常。 即

select count(field19) from myTable where field19 
         not in (select field18 from myTable) 1000000 

上搁置没有空值和不必处理null情况下也使得查询多simplier，更短，减少错误，非常正常速度更快。如果可能的话，最好尽量避免在ms sql服务器上出现空值，除非它们是明确要求的，并且不能合理地用于解决方案。

从一个新表开始，将其大小调整到10m行/ 13GB，同样的查询需要12分钟，考虑到硬件和没有使用的索引，这是非常可敬的。 IO信息查询完全与IO在20MB/s到60MB/s之间徘徊在一起。重复相同的查询需要9分钟。