ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理差异有哪些
发布时间：2021-12-26 13:00:42 所属栏目：MySql教程 来源：互联网
导读：这篇文章将为大家详细讲解有关ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理区别有哪些，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理 我们清楚ORACLE中的b+索引是
|
这篇文章将为大家详细讲解有关ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理区别有哪些，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 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值) 而mysql innodb 不同如果 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); 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); 我们通过查看执行计划： 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; 查看TRACE 文件： 块数据： 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/ 这里跑一下 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位图这个字节是否对应的是字段顺序： 为了简单起见建立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+单列索引，更不包含IOT表。 今天在发这个文章的时候一哥们不知道为什么会扯到IOT，毕竟ORACLE中常用 的HEAP TABLE这种无序的存储方式来存储数据，而不像INNODB本生就是IOT 关于IOT参考我的博客： http://blog.itpub.net/7728585/viewspace-1820365/） 2、MYSQL INNODB 存储了NULL行的信息，至少主键是有的，但是NULL值的表示方法是使用一个BITMAP 位图字节(不一定是一个字节) 位图字节的顺序代表了字段的顺序，所以使用is null可以使用到索引。 关于“ORACLE 索引和MYSQL INNODB 辅助索引对NULL的处理区别有哪些”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。 （编辑：52站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
