Oracle複合索引+空值的索引使用問題

昨天在QQ群里討論一個SQL優化的問題，語句大致如下：

select A,min(B) from table group by A;
--A，B都沒有not null約束，A列無空值，B列有空值。
--存在複合索引IX_TEST(A,B)

於是手動測試，環境採用Oracle自帶的scott用戶下的emp表。

1.首先查看如下語句的執行計劃（此時表只有主鍵索引）：

2.添加IX_TEST(deptno,comm)後查看執行計劃：

發現依然是全表掃描。

3.為deptno列添加非空約束後再次查看執行計劃：

4.總結：

Btree索引是不存儲空值的，這個是所有使用Btree索引的資料庫的共同點。

在本例中我們創建了deptno,comm的符合索引。如果deptno沒有非空約束，那麼說明有的record不會出現在索引中，此時想要找到min(comm)就必須回表才能確定deptno為null的行是否有comm的值。此時優化器認為全表掃描比掃描索引再回表更為合理，因此選擇全表掃描。

當我們添加了非空約束後，deptno不可能為空，因此索引的key值數等於表總行數，另一列comm即便為空也不影響min()取值，只需要掃描索引即可得到所需結果，此時優化器選擇索引掃描。

而在Mysql中無論複合索引首列是否存在非空約束，都會使用索引，deptno為null的會全部分在一組取min(comm)，可能是Mysql的BTREE索引與Oracle的有所不同，使得首列為空都可以無需回表。

最後：Oracle的列能添加非空約束的一定要添加。