天天看點
傳回

oracle vs sqlserver vs MariaDB vs PostgreSQL 處理NULL值比較

作者：執着的花貓Jp
oracle vs sqlserver vs MariaDB vs PostgreSQL 處理NULL值比較

NULL值在資料庫中是一個特殊的存在，你會發現每個資料庫對它的處理方式都有所不同。在這篇文章中，我将測試所有四個資料庫：SQL Server、PostgreSQL、MariaDB和Oracle，并比較它們如何處理表列中的NULL值。

在标準SQL 1992中，NULL的定義如下：

null value (null): A special value, or mark, that is used to
indicate the absence of any data value.

為了檢查列是否包含NULL，我們應該使用“IS NULL”或“IS NOT NULL”謂詞，而不是任何其他謂詞類型。是以讓我們從檢查開始。

  1. 準備測試 table

建立一個簡單的表在所有資料庫上都是一樣的，是以下面是我建立的表，并将在本文中使用：

CREATE TABLE test_tab
(id INTEGER,
f1 INTEGER UNIQUE);

INSERT INTO test_tab VALUES(1,1);
INSERT INTO test_tab VALUES(2,NULL);
IS [NOT] NULL
  1. “IS NULL”和“IS NOT NULL”

我們将從“IS NULL”和“IS NOT NULL”這兩個非常基本的測試開始。我執行了兩個查詢：

SELECT * FROM test_tab WHERE f1 IS NULL;
SELECT * FROM test_tab WHERE f1 IS NOT NULL;

所有資料庫的結果都是相同的：

-- SELECT * FROM test_tab WHERE f1 IS NULL;
id f1
---------- ----------
2

-- SELECT * FROM test_tab WHERE f1 IS NOT NULL;
id f1
---------- ----------
1 1

在開始其他測試之前，我想確定它們對不同的運算符（如“>”、“<”和“=”）的行為相同，是以我執行了以下查詢：

SELECT * FROM test_tab WHERE f1=NULL;
SELECT * FROM test_tab WHERE f1<NULL;
SELECT * FROM test_tab WHERE NULL=NULL;

我甚至嘗試了NULL=NULL，以確定它不會被評估為TRUE。所有資料庫都沒有為所有查詢傳回任何行。現在我們有了一個基線，即所有資料庫的基本NULL比較都是相同的。讓我們繼續前進。

  1. Unique 列

我們知道Oracle和SQL Server之間有差別，但不知道其他兩種，是以想檢查一下。問題是：資料庫如何處理具有NULL值的UNIQUE列。當我建立上面的表時，我将“f1”列定義為UNIQUE。這将在該列上建立一個唯一的索引，并防止其中的重複值。如果“f1”是唯一的，我将無法在“f1”為“1”的地方插入另一行（我在建立表後的第一個插入指令中已經有了這個值）。但是，如果我試圖插入另一個NULL（我已經有一個了），會發生什麼呢。

INSERT INTO test_tab VALUES(3,NULL);

結果如何？MariaDB、PostgreSQL和Oracle表現相同，都可以插入NULL值：

1 row inserted.

然而，SQL Server傳回了完全不同的内容：

Error starting at line : 15 in command -
INSERT INTO test_tab VALUES(3,NULL)
Error at Command Line : 15 Column : 1
Error report -
SQL Error: Violation of UNIQUE KEY constraint 'UQ__test_tab__32139E59C76E5D9B'. Cannot insert duplicate key in object 'dbo.test_tab'. The duplicate key value is (<NULL>).

對于SQL Server，2個NULL是相同的，并且違反了列上的UNIQUE限制。然而，在其他資料庫中，2個NULL不相同，并且資料庫允許一個唯一列有多個NULL值。

  1. Indexes

想檢查索引的使用情況，就必須檢查每個資料庫的執行計劃。我将準備表中的資料：

TRUNCATE TABLE test_tab;
INSERT INTO test_tab VALUES(1,1);
INSERT INTO test_tab VALUES(2,2);
INSERT INTO test_tab VALUES(3,3);
INSERT INTO test_tab VALUES(null,4);
CREATE INDEX tst_null_idx ON test_tab(id);

現在，讓我們檢查傳回NULL的查詢的SQL執行計劃：

MariaDB

-- The Query
EXPLAIN SELECT * FROM test_tab Where id IS NULL;

-- The plan
id select_type table type possible_keys key key_len ref rows Extra
-------------------- ------------------- ----------------- ---------- ---------------- ------------------------ ----------- ---------- ---------- ----------------------------
1 SIMPLE test_tab ref tst_null_idx tst_null_idx 5 const 1 Using index condition

在上面的輸出中，您可以看到“type”列包含值“ref”。這意味着在MariaDB中進行索引周遊，是以MariaDB掃描索引以找到“id”列為NULL的行。

PostgreSQL

-- The query
EXPLAIN SELECT * FROM test_tab Where id IS NULL;

-- The plan
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on test_tab (cost=4.24..14.88 rows=11 width=8)
Recheck Cond: (id IS NULL)
-> Bitmap Index Scan on tst_null_idx (cost=0.00..4.24 rows=11 width=0)
Index Cond: (id IS NULL)

我們在上面的計劃中看到，PostgreSQL使用基于“id is NULL”條件的索引（最後一行）來查找相關行。

SQL Server

-- The query
SET showplan_all on;
SELECT * FROM test_tab with (index(tst_null_idx)) Where id IS NULL;

-- The plan
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------- ---------------------------------------- -------------- -------------- -------------- ----------- ---------------- ----------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
SELECT * FROM test_tab with (index(tst_null_idx)) Where id IS NULL 1 1 0 1 1.0 0.00657038 SELECT false
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([Bmk1000]) 1.0 0.0 4.18E-6 15 0.00657038 [test].[dbo].[test_tab].[id], [test].[dbo].[test_tab].[f1] PLAN_ROW false 1.0
|--Index Seek(OBJECT:([test].[dbo].[test_tab].[tst_null_idx]), SEEK:([test].[dbo].[test_tab].[id]=NULL) ORDERED FORWARD) 1 3 2 Index Seek Index Seek OBJECT:([test].[dbo].[test_tab].[tst_null_idx]), SEEK:([test].[dbo].[test_tab].[id]=NULL) ORDERED FORWARD, FORCEDINDEX [Bmk1000], [test].[dbo].[test_tab].[id] 1.0 0.003125 1.581E-4 19 0.0032831 [Bmk1000], [test].[dbo].[test_tab].[id] PLAN_ROW false 1.0
|--RID Lookup(OBJECT:([test].[dbo].[test_tab]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) 1 5 2 RID Lookup RID Lookup OBJECT:([test].[dbo].[test_tab]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD [test].[dbo].[test_tab].[f1] 1.0 0.003125 1.581E-4 11 0.0032831 [test].[dbo].[test_tab].[f1] PLAN_ROW false 1.0

這個計劃有點長，但如果你看第二行到最後一行，你會看到“Index Seek”，這表明SQL Server使用索引來查找相關的行，就像PostgreSQL和MariaDB一樣。如果你閱讀了該查詢，您可能還會注意到我使用了一個提示（“with（index（tst_null_idx））”）來要求SQL Server使用該索引。如果沒有它，它就不會使用索引，而是簡單地讀取整個表。我的猜測是，這是因為統計資料和效率，這對小表來說是有意義的（Oracle的行為也是一樣的）。因為我想在這種情況下強制它使用索引，是以我使用了提示。

Oracle

-- The queries
SELECT /*+ index(test_tab) */ * FROM test_tab WHERE id IS NULL;
SELECT * FROM TABLE(dbms_xplan.display_cursor());

-- The plan
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 1 | 26 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

oracle是個例外。當其他資料庫使用索引來擷取“id”為NULL的行時，Oracle掃描了整個表（計劃中的最後一行）。如果您檢視查詢，我也嘗試使用提示（/*+索引（test_tab）*/部分），但Oracle隻是忽略了它。之是以會出現這種情況，是因為Oracle不在索引中保留NULL（顯然這是四個索引中唯一不保留NULL的）。是以，當我有一個IS NULL謂詞時，Oracle無法使用索引來查找行，因為資訊根本不存在。在這些情況下，Oracle将始終執行完整的表掃描。請注意，“IS NOT NULL”可以使用索引，因為所有其他值都在索引中，隻有NULL不在。

  1. 結論
  • 使用NULL的基本謂詞（IS NULL、IS NOT NULL和其他具有NULL的運算符，如<、>、=等）在所有四個資料庫上的行為完全相同。
  • 當使用具有UNIQUE限制的列時，Oracle、MariaDB和PostgreSQL允許列中有多行NULL值，而SQL Server隻允許一行NULL值。
  • 當在索引列上使用“IS NULL”謂詞進行查詢時，SQL Server、PostgreSQL和MariaDB可以使用索引來滿足謂詞，而Oracle不能也将執行表掃描。
oracle vs sqlserver vs MariaDB vs PostgreSQL 處理NULL值比較
postgresql SQLSERVER postgre Oracle server serve racle aria
上一篇: android機開應用速度慢,Android機跑好慢 學會這秘密五招手機瞬間加速!
下一篇: Vue 第十一講（元件）實作簡易計數器實作多個計數器（引入全局元件）局部元件的定義給元件添加屬性傳遞事件到父元件元件插槽

繼續閱讀