β

ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理区别

Harries Blog™ 17 阅读

ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理

我们清楚ORACLE中的b+索引是对键值的NULL进行存储的,以致于我们 IS NULL这种肯定是用不到索引的,
当然这提及的ORACLE表为堆表,索引为单列B+树索引,(有一种优化方式为建立组合索引如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索引并不存储对null值进行存储

mysql inno db 不同如果 is null可定用到b+索引的,那么说明INNODB 是保存的NULL值的。
本文将通过对ORACLE INDEX进行BLOCK DUMP和对innodb 辅助索引进行内部访问来证明,
为了简单起见我还是建立两个列的表如下:
ORACLE:
create table test (a int,b int,primary key (a));
create index b_index on test(b);
my sql innodb:
create table test (a int,b int,primary key(a),key(b));

插入一些值:
insert into test value s(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);

我们通过查看执行计划:
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)

为了起到强制作用ORACLE使用HINT来指定索引,但是由于根本用不到所以ORACLE已经忽略,MYSQL innodb已经用到。

接下来我们来分析其内部结构:
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文件
核心内容:
*** 2016-11-16 22:45:55.053
—– begin tree dump
leaf: 0x10000c3 16777411 (0: nrow: 8 rrow: 8)
—– end tree dump
因为B+树只有一个节点就是DBA 16777411,我们单独DUMP这个块
进行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
进行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;

查看TR ACE 文件:
数据
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/
其实这里压根就没有存储4行NULL行因为我们一共12行,dump出来只有8行

下面看看MYSQL INNODB:
因为选择了2列的表我的程序可以直接跑出索引结果:
详细参考:
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 bit map blocks!
current read blocks is : 2 –This Block is i node 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确实记录了NULL值,但是这是如何记录的?
我们上面跑的结果看到是一个很大的负数,但是这个程序并没有考虑NULL值,也就是
全部是not null的情况下正确,

其实不要忘记了行头的 NULL辨识位图:
nullable field bitmap   (1 bit * null field)
每个NULL值占用一个一位(bit),如果不满一个字节按一个字节算,如果不存在NULL值
至少占用一个字节为00。
接下来我们还是要看看这个位,老办法而进行打开(无语累)
看了2进制后如下:
010000180026 实际这6个字节的第一个字节就是NULL 位图及01
80000005
实际上MYSQL INNODB也没有真正的存储字段的NULL值,而是至少存储这行的了 主键值 (rowid)
,在行头记录了一个位图来表示(ORACLE压根没有这行的ROWID信息)
01位图实际上就是 0000 0001 表示第一个字段为NULL,
那么使用索引就简单了,简单扫描相对的字段位图标示位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 对ORACLE是不成立的。

最后我们来做一下 测试 来证明NULL位图这个字节是否对应的是字段顺序:
为了简单起见建立3个表
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)
对于key(b,c,d)来说
b是第一个字段NULL为0001,c为第二个字段NULL为0010,d为第三个字段NULL为0100
我们来看看这个字节,按照我们的推论第一个应该为0000 0101,第二个应该为0000 0110,第三个应该为0000 0011
也就是05,06,03
不出所料下面是二进制显示分别为:
05000010fff28000000180000001
06000010fff28000000180000001
03000010fff28000000180000001

可见推论正确。

下面终结一下2种 数据库 索引对NULL值处理的不同

1、ORACLE B+所以压根没有存储NULL行的ROWID,没有任何NULL信息。那么涉及到任何NULL的查询都不能使用索引
(注意这里不包含 文章 开头那种组合索引,指的是B+单列索引,更不包含 IO T表。
今天在发这个文章的时候一哥们不知道为什么会扯到IOT,毕竟ORACLE中常用
的HEAP TABLE这种无序的存储方式来存储数据,而不像INNODB本生就是IOT
关于IOT参考我的 博客
http://blog.itpub.net/7728585/viewspace-1820365/)
2、MYSQL INNODB 存储了NULL行的信息,至少主键是有的,但是NULL值的表示方法是使用一个BITMAP 位图字节(不一定是一个字节)
位图字节的顺序代表了字段的顺序,所以使用is null可以使用到索引。

再说点题外话,我记录博客仅仅是习惯,因为学习ORACLE很多年了也写了很多年了,将博客作为一种笔记,因为技术这个东西今天你研究学习的很深入,
过几天不用肯定就忘了,如果留下博客可以查阅就方便复习一些,当然也记录一些问题和经验给大家,近1年多才开始学习MYSQL同时学习一些底层的编程
技术,当然学习MYSQL避免不了和ORACLE比较,我想用这种学习方法来加深映像,当然水平有限确实可能有一些问题,也欢迎指出。

PS:如果您想和业内技术大牛交流的话,请加qq群(521249302)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处: Harries Blog™ » ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理区别

作者:Harries Blog™
追心中的海,逐世界的梦

发表评论