ORACLE 索引和MYSQL INNODB 輔助索引對(duì)NULL的處理區(qū)別有哪些

這篇文章將為大家詳細(xì)講解有關(guān)ORACLE 索引和MYSQL INNODB 輔助索引對(duì)NULL的處理區(qū)別有哪些，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

ORACLE 索引和MYSQL INNODB 輔助索引對(duì)NULL的處理

我們清楚ORACLE中的b+索引是對(duì)鍵值的NULL進(jìn)行存儲(chǔ)的，以致于我們 IS NULL這種肯定是用不到索引的，
當(dāng)然這提及的ORACLE表為堆表，索引為單列B+樹索引，(有一種優(yōu)化方式為建立組合索引如create index xx on tab(a,'1')
這樣來保證索引記錄NULL值
這樣DUMP出來為
.....
row#11[7886] flag: ------, lock: 2, len=12
col 0; NULL
col 1; len 1; (1):  31
col 2; len 6; (6):  01 00 00 d5 00 0a
....
記錄了NULL值)


而且在某些情況下，比如
select count(b) from tab ;
這種如果b列沒有顯示的申明為not null屬性也是用不到的，必須加上not null或者在where條件中加上
b is not null。
很明顯這些問題都是ORACLE索引并不存儲(chǔ)對(duì)null值進(jìn)行存儲(chǔ)


而mysql innodb 不同如果 is null可定用到b+索引的，那么說明INNODB 是保存的NULL值的。
本文將通過對(duì)ORACLE INDEX進(jìn)行BLOCK DUMP和對(duì)innodb 輔助索引進(jìn)行內(nèi)部訪問來證明，
為了簡(jiǎn)單起見我還是建立兩個(gè)列的表如下：
ORACLE：
 create table test (a int,b int,primary key(a));
 create index b_index on test(b);
mysql innodb：
 create table test (a int,b int,primary key(a),key(b));
 
插入一些值：
insert into test values(1,1);
insert into test values(5,NULL);
insert into test values(3,1);
insert into test values(4,2);
insert into test values(10,NULL);
insert into test values(7,4);
insert into test values(8,5);
insert into test values(11,NULL);
insert into test values(20,6);
insert into test values(21,6);
insert into test values(19,NULL);
insert into test values(16,7);


我們通過查看執(zhí)行計(jì)劃：
ORACLE:
SQL> select /*+ index(test,b_index)*/ * from test where b is null;


         A          B
---------- ----------
         5
        10
        11
        19


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   104 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     4 |   104 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


mysql：
mysql> explain select * from test where b is null;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | b             | b    | 5       | const |    4 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)


為了起到強(qiáng)制作用ORACLE使用HINT來指定索引，但是由于根本用不到所以O(shè)RACLE已經(jīng)忽略，MYSQL innodb已經(jīng)用到。


接下來我們來分析其內(nèi)部結(jié)構(gòu)：
ORACLE:
SQL>  SELECT OBJECT_ID FROM DBA_OBJECTS where object_name='B_INDEX';


 OBJECT_ID
----------
     75905


SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/home/oracle/diag/rdbms/mytest/mytest/trace/mytest_ora_2996.trc
SQL>  alter session set events 'immediate trace name treedump level 75905';


Session altered.
查看trace文件
核心內(nèi)容：
*** 2016-11-16 22:45:55.053
----- begin tree dump
leaf: 0x10000c3 16777411 (0: nrow: 8 rrow: 8)
----- end tree dump
因?yàn)锽+樹只有一個(gè)節(jié)點(diǎn)就是DBA 16777411，我們單獨(dú)DUMP這個(gè)塊
進(jìn)行DBA換算


SQL>  select dbms_utility.data_block_address_file(16777411),
  2            dbms_utility.data_block_address_block(16777411) from dual;


DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777411)
----------------------------------------------
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777411)
-----------------------------------------------
                                             4
                                            195
進(jìn)行BLOCK DUMP：
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/home/oracle/diag/rdbms/mytest/mytest/trace/mytest_ora_3009.trc
SQL> alter system dump datafile 4 block 195;


查看TRACE 文件：
塊數(shù)據(jù)：
row#0[8020] flag: ------, lock: 2, len=12
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 00 00 b7 00 00
row#1[8008] flag: ------, lock: 2, len=12
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 00 00 b7 00 02
row#2[7996] flag: ------, lock: 2, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 00 00 b7 00 03
row#3[7984] flag: ------, lock: 2, len=12
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  01 00 00 b7 00 05
row#4[7972] flag: ------, lock: 2, len=12
col 0; len 2; (2):  c1 06
col 1; len 6; (6):  01 00 00 b7 00 06
row#5[7960] flag: ------, lock: 2, len=12
col 0; len 2; (2):  c1 07
col 1; len 6; (6):  01 00 00 b7 00 08
row#6[7948] flag: ------, lock: 2, len=12
col 0; len 2; (2):  c1 07
col 1; len 6; (6):  01 00 00 b7 00 09
row#7[7936] flag: ------, lock: 2, len=12
col 0; len 2; (2):  c1 08
col 1; len 6; (6):  01 00 00 b7 00 0b


有8記錄，其順序按照b列大小排序及COL 0，COL2是ROWID
注意COL是number類型有ORACLE自己算法
算法參考：
http://blog.itpub.net/7728585/viewspace-2128563/
其實(shí)這里壓根就沒有存儲(chǔ)4行NULL行因?yàn)槲覀円还?2行，dump出來只有8行


下面看看MYSQL INNODB：
因?yàn)檫x擇了2列的表我的程序可以直接跑出索引結(jié)果：
詳細(xì)參考：
http://blog.itpub.net/7728585/viewspace-2126344/
這里跑一下


./mysqlblock test.ibd -d
current read blocks is : 0 --This Block is file space header blocks!
current read blocks is : 1 --This Block is insert buffer bitmap  blocks!
current read blocks is : 2 --This Block is inode blocks!
current read blocks is : 3 --This Block is data blocks( index pages)!
current read blocks is : 4 --This Block is data blocks( index pages)! --這里是我們需要查看的輔助索引的塊


[root@testmy test]# ./a.out test.ibd 4
Index_no is:117
find first one record!
B:5,A:-2147483616-->      
B:10,A:-2147483592-->
B:11,A:-2147483568-->
B:19,A:-2147483544-->
B:1,A:1-->
B:1,A:3-->
B:2,A:4-->
B:4,A:7-->
B:5,A:8-->
B:6,A:20-->
B:6,A:21-->
B:7,A:16-->


B:5,A:-2147483616--> insert into test values(5,NULL);
B:10,A:-2147483592--> insert into test values(10,NULL);
B:11,A:-2147483568--> insert into test values(11,NULL);
B:19,A:-2147483544--> insert into test values(19,NULL);
我們可以看到INNODB確實(shí)記錄了NULL值，但是這是如何記錄的？
我們上面跑的結(jié)果看到是一個(gè)很大的負(fù)數(shù)，但是這個(gè)程序并沒有考慮NULL值，也就是
全部是not null的情況下正確，

其實(shí)不要忘記了行頭的 NULL辨識(shí)位圖：
nullable field bitmap   (1 bit * null field)
每個(gè)NULL值占用一個(gè)一位(bit),如果不滿一個(gè)字節(jié)按一個(gè)字節(jié)算，如果不存在NULL值
至少占用一個(gè)字節(jié)為00。
接下來我們還是要看看這個(gè)位，老辦法而進(jìn)行打開(無語累)
看了2進(jìn)制后如下：
010000180026 實(shí)際這6個(gè)字節(jié)的第一個(gè)字節(jié)就是NULL 位圖及01
80000005
實(shí)際上MYSQL INNODB也沒有真正的存儲(chǔ)字段的NULL值，而是至少存儲(chǔ)這行的了主鍵值(rowid)
，在行頭記錄了一個(gè)位圖來表示(ORACLE壓根沒有這行的ROWID信息)
01位圖實(shí)際上就是 0000 0001 表示第一個(gè)字段為NULL，
那么使用索引就簡(jiǎn)單了，簡(jiǎn)單掃描相對(duì)的字段位圖標(biāo)示位1的就出來了。
所以官方文檔才有：
For both BTREE and HASHindexes, comparison of a key part with a constant value is a range condition
when using the =, <=>, IN(), IS NULL, or IS NOT NULL operators.
這樣的說法，這IS NULL 對(duì)ORACLE是不成立的。


最后我們來做一下測(cè)試來證明NULL位圖這個(gè)字節(jié)是否對(duì)應(yīng)的是字段順序：
為了簡(jiǎn)單起見建立3個(gè)表
 create table test10 (a int,b int,c int,d int,primary key(a),key(b,c,d));
 create table test11 (a int,b int,c int,d int,primary key(a),key(b,c,d));
 create table test12 (a int,b int,c int,d int,primary key(a),key(b,c,d));
mysql> insert into test10 values(1,NULL,1,NULL);
Query OK, 1 row affected (0.02 sec)
mysql> insert into test11 values(1,1,NULL,NULL);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test12 values(1,NULL,NULL,1);
Query OK, 1 row affected (0.01 sec)
對(duì)于key(b,c,d)來說
b是第一個(gè)字段NULL為0001，c為第二個(gè)字段NULL為0010，d為第三個(gè)字段NULL為0100
我們來看看這個(gè)字節(jié)，按照我們的推論第一個(gè)應(yīng)該為0000 0101,第二個(gè)應(yīng)該為0000 0110,第三個(gè)應(yīng)該為0000 0011
也就是05,06,03
不出所料下面是二進(jìn)制顯示分別為:
05000010fff28000000180000001
06000010fff28000000180000001
03000010fff28000000180000001

可見推論正確。

下面終結(jié)一下2種數(shù)據(jù)庫(kù)索引對(duì)NULL值處理的不同

1、ORACLE B+所以壓根沒有存儲(chǔ)NULL行的ROWID，沒有任何NULL信息。那么涉及到任何NULL的查詢都不能使用索引
    （注意這里不包含文章開頭那種組合索引，指的是B+單列索引，更不包含IOT表。
       今天在發(fā)這個(gè)文章的時(shí)候一哥們不知道為什么會(huì)扯到IOT，畢竟ORACLE中常用
      的HEAP TABLE這種無序的存儲(chǔ)方式來存儲(chǔ)數(shù)據(jù)，而不像INNODB本生就是IOT
       關(guān)于IOT參考我的博客：
       http://blog.itpub.net/7728585/viewspace-1820365/）
2、MYSQL INNODB 存儲(chǔ)了NULL行的信息，至少主鍵是有的，但是NULL值的表示方法是使用一個(gè)BITMAP 位圖字節(jié)(不一定是一個(gè)字節(jié))
   位圖字節(jié)的順序代表了字段的順序，所以使用is null可以使用到索引。

關(guān)于“ORACLE 索引和MYSQL INNODB 輔助索引對(duì)NULL的處理區(qū)別有哪些”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。