OceanBase 索引列的NULL属性测试分析

数据库技术闲谈 2021-03-06

282

本文主要测试OB 2.27 版本里索引列是否为NULL这个设置是否会对常用 SQL 执行计划有影响，顺便分享一些OB使用技巧。

NULL基础知识

数据库中的 NULL 值是比较特殊的存在，它不等于任何值（包括 TRUE、FALSE 或者 NULL），不能做关系运算（大于、小于、等于、不等于）。NULL 值能做IS NULL
或 IS NOT NULL
判断,可以用函数转换为普通值。如 NVL 、NVL2 函数。

在 NOT IN 子查询里，如果子查询返回结果集包含了 NULL 值，则整个 NOT IN 条件都是返回为空。
NULL 值对结果的影响跟 OB 没有关系，不在本文探讨范围内。

构造测试数据（MYSQL）

这里为了模拟生产，用 sysbench工具快速生成两个 1000万的单表。由于开源的sysbench 只支持 OB的 MySQL 租户，所以先从 MySQL 测试起。

date && ./sysbench ./oltp_read_only.lua --mysql-db=sysbenchdb --mysql-host=172.23.152.229 --mysql-port=2883 --mysql-user=user01@obmysql01#obdemo --mysql-password=123456 --tables=1 --table_size=10000000 --report-interval=10 --threads=100 --db-driver=mysql --time=3600 --skip_trx=on --db-ps-mode=disable --create-secondary=off --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 prepare && date

查看生成后的表 sbtest1 结构.

obclient -h127.1 -uuser01@obmysql01#obdemo -P2883 -p123456 -c -A sysbenchdb
show create table sbtest1;

show indexes from sbtest1;

将 sbtest2 表的列 c 置为 null 。并更新部分记录为 null 。

obclient -h127.1 -uuser01@obmysql01#obdemo -P2883 -p123456 -c -A sysbenchdb

alter table sbtest2 modify column c char(120) null;
update /*+ query_timeout(100000000) trx_idle_timeout(1000000000) trx_idle_timeout(1200000000) */ sbtest2 set c = null where mod(id, 10000) = 0; commit;
show create table sbtest2\G
select count(*) from sbtest2 where c is null;

将集群合并一把

// 发起合并
alter system major freeze;

// 查看合并进度
SELECT ZONE,svr_ip,major_version,ss_store_count,merged_ss_store_count,modified_ss_store_count,merge_start_time,merge_finish_time,merge_process
FROM __all_virtual_partition_sstable_image_info;

// 查看合并事件
SELECT DATE_FORMAT(gmt_create, '%b%d %H:%i:%s') gmt_create_ , module, event, name1, value1, name2, value2, rs_svr_ip
FROM __all_rootservice_event_history
WHERE 1 = 1   
  -- AND module IN ('server')
--  AND module NOT IN ('leader_coordinator')
ORDER BY gmt_create DESC
LIMIT 10;

查看表的统计信息

这里的统计信息还不是ORACLE里那个统计信息视图，后者OB还在开发中。这里指的OB自身收集的一些表的数据大小信息。不过，目前要一轮合并后才会更新。

SELECT  t.tenant_id, a.tenant_name,d.database_name,  t.table_name,  tg.tablegroup_name , t.part_num , t2.partition_id, t2.ZONE, t2.svr_ip, t2.role 
    -- , a.primary_zone   , IF(t.locality = '' OR t.locality IS NULL, a.locality, t.locality) AS locality
    , t2.row_count, round(data_size/1024/1024,0) data_size_MB
FROM oceanbase.__all_tenant AS a  
    JOIN oceanbase.__all_virtual_database AS d ON ( a.tenant_id = d.tenant_id  ) 
    JOIN oceanbase.__all_virtual_table AS t  ON (t.tenant_id = d.tenant_id AND t.database_id = d.database_id)   
    JOIN oceanbase.__all_virtual_meta_table t2 ON (t.tenant_id = t2.tenant_id AND (t.table_id=t2.table_id OR t.tablegroup_id=t2.table_id)  AND t2.ROLE IN (1,2) )
    LEFT JOIN oceanbase.__all_virtual_tablegroup AS tg ON (t.tenant_id = tg.tenant_id and  t.tablegroup_id = tg.tablegroup_id) 
WHERE a.tenant_id IN (1002 ) AND  t.table_type IN (3)  
AND d.database_name = 'sysbenchdb'
ORDER BY t.tenant_id, tg.tablegroup_name, d.database_name, t.table_name, t2.partition_id
;

变换这个sql条件，可以查看租户所有表的分布和统计信息。

测试单列索引跟NULL关系

列 c 在表 sbtest1中是不允许为NULL，在sbtest2中是允许为NULL。

增加索引 idx_sbtest_c(c)

create index idx_sbtest_c on sbtest1(c);
create index idx_sbtest_c on sbtest2(c);

OB的租户可以兼容 MySQL 或者 ORACLE，2选1. 有些客户想要是都兼容多好。个人觉得会搞成“两不像”。更深层次的原因是MySQL跟ORACLE标准有不少冲突的地方。就上面这个SQL，在 MySQL 里能成功，在ORACLE里就会报错。

再合并一次，查看表的统计信息。

备注：

表对应的分区大小增长了约500MB. 对于一个普通的表，只有一个分区。普通表的索引分区也是关联到表的分区下。这点跟ORACLE的表和索引分开存储的原理不一样，跟MySQL是一样的。普通索引树的叶节点都是指向主键，表的主键索引等同于表数据。所以，最佳实践是OB的表都要有主键，并且主键列尽可能短。如果业务是符合主键（列很多），还不如新增一个数字列单独作为主键，业务唯一字段用唯一索引代替。

下面开始测试SQL。

执行计划查看

通常看SQL执行计划用 explain 命令。（OB的oracle租户目前也是兼容MySQL的explain命令。以后会兼容ORACLE的 explain plan for 命令。还是MySQL的风格简单一些。）

但是 explain 不执行SQL，得到的执行计划可能跟实际运行的执行计划不一样，在传统数据库ORACLE里也是有可能会这样。不过在分布式数据库OB里，业务事务包含多条SQL的时候，这种可能性会更高。这个跟OBProxy的路由有密切关系，由于比较复杂，依然留待以后再分享。

explain 的方法简单，结果直观，在工具dbeaver里查看也更方便。为避免路由问题干扰执行计划，每次运行explain命令时都把当前会话断开重连一下或者把会话事务设置为自动提交。

另外一种更严谨的方法是查看SQL实际执行计划。这个需要借助两个视图： gv$plan_cache_plan_stat
和 gv$plan_cache_plan_explain
。以前文章也介绍过。后面这个视图比较特殊，是一个类似KV的结构，必须提供完整4个等值条件才会返回结果。

// 查看缓存的SQL执行统计信息
SELECT s.tenant_id, svr_ip, svr_port , plan_Id,sql_id,TYPE, d.database_name , query_sql, first_load_time, avg_exe_usec, slow_count,executions, slowest_exe_usec
FROM `gv$plan_cache_plan_stat` s LEFT JOIN `gv$database`  d ON (s.tenant_id =d.tenant_id  AND s.db_id =d.database_id )
WHERE s.tenant_id=1002   -- 改成具体的 tenant_id 
 AND d.database_name in ('sysbenchdb')
 and query_sql like '%test20210305%'
ORDER BY avg_exe_usec desc;

// 查看具体SQL的执行计划缓存
SELECT  PLAN_DEPTH ,OPERATOR ,NAME ,`ROWS` ,cost,PROPERTY
FROM `gv$plan_cache_plan_explain`   
WHERE tenant_id=1002 AND ip = '172.30.118.70' AND port=2882 AND plan_id=231  ;

下面2种方法都会展示。

IS NULL 测试

下面SQL的注释（HINT）里写了一个标记 test202103-05
，主要是方便从SQL缓存里定位到这条SQL, 不影响实际执行计划和结果正确性。

select /*+ test20210305-01 */ count(*) from sbtest1 where c is null ;
select /*+ test20210305-01 */ count(*) from sbtest2 where c is null ;

从上面结果可以看出，针对索引列做 IS NULL 判断时，不管这个列是不是NULL属性，在OB里都能走索引，只是cost会有点分别。不过列为NOT NULL的时候，优化器会有优化（注意 startup_filter([0])
信息）。

索引列等值查询

select /*+ test20210305-02 */ * from sbtest1 
where c ='10676430684-17587037436-43492067438-02978392453-17382292196-18617843758-31971657195-07771170431-78457836714-38765745127';
select /*+ test20210305-02 */ * from sbtest2 
where c = '10676430684-17587037436-43492067438-02978392453-17382292196-18617843758-31971657195-07771170431-78457836714-38765745127';

从上面也可以看出，OB里索引列的单列查询，不管列是不是NULL，都能用上该索引。

仔细看执行计划看能看出更多有趣的东西。通常执行计划查了索引后还要回到主表再查一次，执行计划会有2步。但是前面说了，OB的普通表的分区和索引的分区是在一起的，都算在表分区内部，所以这个“回表”操作，在执行计划里就是一步。如果是全局索引，那就是两个独立的分区，那执行计划上就会有两步。

索引列模糊查询

select /*+ test20210305-03 */ * from sbtest1  where c like '10676430684-%';
select /*+ test20210305-03 */ * from sbtest2  where c like '10676430684-%';

这个结论跟上面一样，读者可以自己测试验证。

测试多列索引跟NULL关系

前导列等值查询

alter table sbtest2 modify k int null;
update /*+ query_timeout(100000000) trx_idle_timeout(1000000000) trx_idle_timeout(1200000000) */ sbtest2 set k = null where mod(id, 10500) = 0; commit;
show create table sbtest2\G
select count(*) from sbtest2 where c is null;

create index idx_sbtest_k_c on sbtest1(k, c);
create index idx_sbtest_k_c on sbtest2(k, c);

总有朋友担心不收集统计信息会影响SQL执行计划，那只是一个极端情况。在ORACLE里即使你经常收集统计信息，也不能保证有些SQL一定不走错。这个问题很复杂，用简单的思维去解决个人觉得不是好办法。早些年有个故事，听说有个应用，厂商要求使用ORACLE不开CBO，否则不保证解决性能问题。可见这个问题用户和厂商都感觉很痛。OB未来也会做手动收集统计信息这个功能。

这里我就不再做集群合并，也看看是否会影响执行计划选择。

select /*+ test20210305-04 */ * from sbtest1 where k=5028116 ;
select /*+ test20210305-04 */ * from sbtest2 where k=5028116 ;

所有列等值查询

select /*+ test20210305-04 */ * from sbtest1 where k=5028116 and c = '95853251151-92786089934-87253544789-58438024614-82823928273-73185621814-81143279998-07697837769-56511602204-24615932635' ;
select /*+ test20210305-04 */ * from sbtest2 where k=5028116 and c = '95853251151-92786089934-87253544789-58438024614-82823928273-73185621814-81143279998-07697837769-56511602204-24615932635' ;

从上面也可以看出，OB里多列索引里，不管是条件是否包含全部索引列(要包含前导列)，也不管列是不是NULL，都不影响执行计划选择该索引。

前导列 IS NULL 查询

select /*+ test20210305-05 */ * from sbtest1 where k is null and c = '95853251151-92786089934-87253544789-58438024614-82823928273-73185621814-81143279998-07697837769-56511602204-24615932635' ;
select /*+ test20210305-05 */ * from sbtest2 where k is null and c = '95853251151-92786089934-87253544789-58438024614-82823928273-73185621814-81143279998-07697837769-56511602204-24615932635' ;

涉及到多列索引的前导列是 IS NULL 判断时，对于前导列是NOT NULL时，OB这时候没有选择索引，而是扫描表直接给出结果为空。

没有前导列条件的查询

alter table sbtest1 drop index idx_sbtest_c ;
alter table sbtest2 drop index idx_sbtest_c ;
select /*+ test20210305-06 */ * from sbtest1 where c = '95853251151-92786089934-87253544789-58438024614-82823928273-73185621814-81143279998-07697837769-56511602204-24615932635' ;
select /*+ test20210305-06 */ * from sbtest2 where c = '95853251151-92786089934-87253544789-58438024614-82823928273-73185621814-81143279998-07697837769-56511602204-24615932635' ;

通常我们认为多列索引的问题在于如果查询条件没有前导列就不能使用这个多列索引。ORACLE早期解决办法是某些场景下可以使用 INDEX SKIP SCAN
。尽管比 INDEX RANGE SCAN
要慢一些，相比去扫描全表，那依然是个不错的选择。MySQL也只是在8.0后才支持 INDEX SKIP SCAN
。

从上面分析截图看，OB还没有实现INDEX SKIP SCAN
，不过OB的CBO也会考虑使用这个索引。扫描这个索引做过滤（filter
)操作，可能会比去扫描全表更快。当然这取决于这个SQL返回的记录数。如果记录数很大的话，内部回表还是有一定成本，CBO可能也会最终选择扫描全表。INDEX SKIP SCAN
也是同理。

更进一步说，如果这个查询很重要，为这一列建索引就有必要，更推荐去为这一查询条件去建合适的索引。

总之，索引列是否为NULL，不影响这类查询选择这个索引。

ORACLE租户跟MySQL租户有区别吗

构造测试数据

从MySQL租户导出表数据

用ob-dumper-loader
导出 MySQL租户的两个表。

./obdumper --csv -h 127.1 -P 2883 -t obmysql01 -c obdemo -u user01 -p 123456 -D sysbenchdb --table sbtest1,sbtest2 -f /data/1/obdumper --sys-password `cat ~/.syspassword`

2张1000万的表，10秒不到就导出了。

导出的数据目录在 /data/1/obdumper/data/sysbenchdb/TABLE
。

准备oracle租户环境

CREATE USER user01 identified BY 123456;
GRANT CONNECT,resource TO user01;

obclient -hobserver00 -P2883 -uuser01@oboracle01#obdemo -p123456 -c -A user01

CREATE TABLE sbtest1 (
  id number NOT NULL ,
  k number NOT NULL DEFAULT '0',
  c char(120) NOT NULL DEFAULT '',
  pad char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
);

CREATE TABLE sbtest2 (
  id number NOT NULL ,
  k number  NULL ,
  c char(120)  NULL ,
  pad char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
);

到ORACLE租户导入表数据

目前 obdumper 从mysql导出的目录结果让obloader导入到oracle租户会有点问题。原因是ORACLE的表名默认是大写的，MySQL默认是小写的。所以会根据大写的表名会找不到csv文件。

这里手动转一下文件名的大小写。

cd /data/1/obdumper/data/sysbenchdb/TABLE
ls *.csv |awk -F'.csv' '{print "mv "$1".csv "toupper($1)".csv"}' |bash

再用 obloader 导入。

./obloader --csv -h 127.1 -P 2883 -t oboracle01 -c obdemo -u user01 -p 123456 -D user01 --table SBTEST1,SBTEST2 -f /data/1/obdumper --sys-password `cat ~/.syspassword`

2 张 1000万的表导入用了 34s 不到。导入的时候，OB的增量内存消耗速度能到 1.13GB/s。

单表查询测试

再跑一次上面MySQL的查询场景，多个查询执行计划表示，同样的表结构、数据和SQL，在ORACLE租户和MySQL租户上执行计划都是一样的，都是OB自己的SQL引擎的行为。

真找不一样的地方那就是ORACLE的对象名默认大写，MySQL都是小写。这里就不重复贴图了。

多表连接测试

用同样的方法生成2张表 sbtest11 和 sbtest12 。其中 sbtest12 跟 sbtest2 一样列 k 和 c 都允许为NULL，并且有NULL值。

查看所有索引

SELECT table_owner, table_Name, index_name, listagg( column_name,',') WITHIN GROUP (ORDER BY column_position) indexed_columns 
FROM all_ind_columns 
WHERE table_owner = 'USER01' 
GROUP BY table_owner, table_name, index_name
ORDER BY table_owner, table_name, index_name
;
+-------------+------------+-------------------------------+-----------------+
| TABLE_OWNER | TABLE_NAME | INDEX_NAME                    | INDEXED_COLUMNS |
+-------------+------------+-------------------------------+-----------------+
| USER01      | SBTEST1    | IDX_SBTEST1_C_2               | C               |
| USER01      | SBTEST1    | IDX_SBTEST1_K_C_2             | K,C             |
| USER01      | SBTEST1    | SBTEST1_OBPK_1614952866197016 | ID              |
| USER01      | SBTEST11   | IDX_SBTEST1_C                 | C               |
| USER01      | SBTEST11   | IDX_SBTEST1_K_C               | K,C             |
| USER01      | SBTEST11   | SBTEST1_OBPK_1614932239752654 | ID              |
| USER01      | SBTEST12   | IDX_SBTEST2_C                 | C               |
| USER01      | SBTEST12   | IDX_SBTEST2_K_C               | K,C             |
| USER01      | SBTEST12   | SBTEST2_OBPK_1614932244447062 | ID              |
| USER01      | SBTEST2    | IDX_SBTEST2_C_2               | C               |
| USER01      | SBTEST2    | IDX_SBTEST2_K_C_2             | K,C             |
| USER01      | SBTEST2    | SBTEST2_OBPK_1614952868052137 | ID              |
+-------------+------------+-------------------------------+-----------------+
12 rows in set (0.01 sec)

INNER JOIN 查询

EXPLAIN 
SELECT t1.*, t2.* 
FROM SBTEST1 t1 JOIN SBTEST11 t2 ON (t1.k=t2.K)
WHERE t2.C  LIKE '04420889655%'
;

EXPLAIN 
SELECT t1.*, t2.* 
FROM SBTEST2 t1 JOIN SBTEST12 t2 ON (t1.k=t2.K)
WHERE t2.C  LIKE '04420889655%'
;

obclient> EXPLAIN
    -> SELECT t1.*, t2.*
    -> FROM SBTEST1 t1 JOIN SBTEST11 t2 ON (t1.k=t2.K)
    -> WHERE t2.C  LIKE '04420889655%'
    -> \G
*************************** 1. row ***************************
Query Plan: ==========================================================
|ID|OPERATOR        |NAME                 |EST. ROWS|COST|
----------------------------------------------------------
|0 |NESTED-LOOP JOIN|                     |1        |84  |
|1 | TABLE SCAN     |T2(IDX_SBTEST1_C)    |1        |84  |
|2 | TABLE SCAN     |T1(IDX_SBTEST1_K_C_2)|6        |115 |
==========================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD], [T2.ID], [T2.K], [T2.C], [T2.PAD]), filter(nil),
      conds(nil), nl_params_([T2.K])
  1 - output([T2.K], [T2.C], [T2.ID], [T2.PAD]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.K], [T2.C], [T2.ID], [T2.PAD]), partitions(p0)
  2 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.00 sec)

obclient> EXPLAIN
    -> SELECT t1.*, t2.*
    -> FROM SBTEST2 t1 JOIN SBTEST12 t2 ON (t1.k=t2.K)
    -> WHERE t2.C  LIKE '04420889655%'
    -> \G
*************************** 1. row ***************************
Query Plan: ==========================================================
|ID|OPERATOR        |NAME                 |EST. ROWS|COST|
----------------------------------------------------------
|0 |NESTED-LOOP JOIN|                     |1        |84  |
|1 | TABLE SCAN     |T2(IDX_SBTEST2_C)    |1        |84  |
|2 | TABLE SCAN     |T1(IDX_SBTEST2_K_C_2)|6        |115 |
==========================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD], [T2.ID], [T2.K], [T2.C], [T2.PAD]), filter(nil),
      conds(nil), nl_params_([T2.K])
  1 - output([T2.K], [T2.C], [T2.ID], [T2.PAD]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.K], [T2.C], [T2.ID], [T2.PAD]), partitions(p0)
  2 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.01 sec)

结论：不管列是否包含 NULL ，INNER JOIN的执行计划是一样的。

LEFT JOIN 查询

EXPLAIN 
SELECT t1.*, t2.* 
FROM SBTEST1 t1 LEFT JOIN SBTEST11 t2 ON (t1.k=t2.K AND t2.C  LIKE '04420889655%')
;

EXPLAIN 
SELECT t1.*, t2.* 
FROM SBTEST2 t1 LEFT JOIN SBTEST12 t2 ON (t1.k=t2.K AND t2.C  LIKE '04420889655%')
;

obclient> EXPLAIN
    -> SELECT t1.*, t2.*
    -> FROM SBTEST1 t1 LEFT JOIN SBTEST11 t2 ON (t1.k=t2.K AND t2.C  LIKE '04420889655%')
    -> \G
*************************** 1. row ***************************
Query Plan: ==============================================================
|ID|OPERATOR             |NAME             |EST. ROWS|COST   |
--------------------------------------------------------------
|0 |HASH RIGHT OUTER JOIN|                 |10000000 |7488265|
|1 | TABLE SCAN          |T2(IDX_SBTEST1_C)|1        |84     |
|2 | TABLE SCAN          |T1               |10000000 |3945573|
==============================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD], [T2.ID], [T2.K], [T2.C], [T2.PAD]), filter(nil),
      equal_conds([T1.K = T2.K]), other_conds(nil)
  1 - output([T2.K], [T2.C], [T2.ID], [T2.PAD]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.K], [T2.C], [T2.ID], [T2.PAD]), partitions(p0)
  2 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.00 sec)

obclient> EXPLAIN
    -> SELECT t1.*, t2.*
    -> FROM SBTEST2 t1 LEFT JOIN SBTEST12 t2 ON (t1.k=t2.K AND t2.C  LIKE '04420889655%')
    -> \G
*************************** 1. row ***************************
Query Plan: ==============================================================
|ID|OPERATOR             |NAME             |EST. ROWS|COST   |
--------------------------------------------------------------
|0 |HASH RIGHT OUTER JOIN|                 |10000000 |7488266|
|1 | TABLE SCAN          |T2(IDX_SBTEST2_C)|1        |84     |
|2 | TABLE SCAN          |T1               |10000000 |3945573|
==============================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD], [T2.ID], [T2.K], [T2.C], [T2.PAD]), filter(nil),
      equal_conds([T1.K = T2.K]), other_conds(nil)
  1 - output([T2.K], [T2.C], [T2.ID], [T2.PAD]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.K], [T2.C], [T2.ID], [T2.PAD]), partitions(p0)
  2 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.00 sec)

结论：不管列是否包含 NULL ，LEFT JOIN的执行计划是一样的。

EXISTS子查询

EXPLAIN 
SELECT * FROM SBTEST1  t1 
WHERE exists (
    SELECT k FROM sbtest11 t2 WHERE t2.k = t1.k AND t2.C LIKE '04420889655%'
);

EXPLAIN 
SELECT * FROM SBTEST2  t1 
WHERE exists (
    SELECT k FROM sbtest12 t2 WHERE t2.k = t1.k AND t2.C LIKE '04420889655%'
);

obclient> EXPLAIN
    -> SELECT * FROM SBTEST1  t1
    -> WHERE exists (
    -> SELECT k FROM sbtest11 t2 WHERE t2.k = t1.k AND t2.C LIKE '04420889655%'
    -> )\G
*************************** 1. row ***************************
Query Plan: ==========================================================
|ID|OPERATOR        |NAME                 |EST. ROWS|COST|
----------------------------------------------------------
|0 |NESTED-LOOP JOIN|                     |1        |84  |
|1 | SUBPLAN SCAN   |VIEW1                |1        |84  |
|2 |  HASH DISTINCT |                     |1        |84  |
|3 |   TABLE SCAN   |T2(IDX_SBTEST1_C)    |1        |84  |
|4 | TABLE SCAN     |T1(IDX_SBTEST1_K_C_2)|6        |115 |
==========================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD]), filter(nil),
      conds(nil), nl_params_([VIEW1.T2.K])
  1 - output([VIEW1.T2.K]), filter(nil),
      access([VIEW1.T2.K])
  2 - output([T2.K]), filter(nil),
      distinct([T2.K])
  3 - output([T2.K]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.K], [T2.C]), partitions(p0)
  4 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.01 sec)

obclient> EXPLAIN
    -> SELECT * FROM SBTEST2  t1
    -> WHERE exists (
    -> SELECT k FROM sbtest12 t2 WHERE t2.k = t1.k AND t2.C LIKE '04420889655%'
    -> )\G
*************************** 1. row ***************************
Query Plan: ==========================================================
|ID|OPERATOR        |NAME                 |EST. ROWS|COST|
----------------------------------------------------------
|0 |NESTED-LOOP JOIN|                     |1        |84  |
|1 | SUBPLAN SCAN   |VIEW1                |1        |84  |
|2 |  HASH DISTINCT |                     |1        |84  |
|3 |   TABLE SCAN   |T2(IDX_SBTEST2_C)    |1        |84  |
|4 | TABLE SCAN     |T1(IDX_SBTEST2_K_C_2)|6        |115 |
==========================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD]), filter(nil),
      conds(nil), nl_params_([VIEW1.T2.K])
  1 - output([VIEW1.T2.K]), filter(nil),
      access([VIEW1.T2.K])
  2 - output([T2.K]), filter(nil),
      distinct([T2.K])
  3 - output([T2.K]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.K], [T2.C]), partitions(p0)
  4 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.00 sec)

结论：不管列是否包含 NULL ，EXISTS 的执行计划是一样的。

NOT EXISTS 子查询

EXPLAIN 
SELECT * FROM SBTEST1  t1 
WHERE NOT exists (
    SELECT k FROM sbtest11 t2 WHERE t2.k = t1.k AND t2.C LIKE '044208%'
);

EXPLAIN 
SELECT * FROM SBTEST2  t1 
WHERE NOT exists (
    SELECT k FROM sbtest12 t2 WHERE t2.k = t1.k AND t2.C LIKE '044208%'
);

NOT EXISTS 子查询会被转换为 ANTI JOIN 。

obclient> EXPLAIN
    -> SELECT * FROM SBTEST1  t1
    -> WHERE NOT exists (
    -> SELECT k FROM sbtest11 t2 WHERE t2.k = t1.k AND t2.C LIKE '044208%'
    -> )\G
*************************** 1. row ***************************
Query Plan: =============================================================
|ID|OPERATOR            |NAME             |EST. ROWS|COST   |
-------------------------------------------------------------
|0 |HASH RIGHT ANTI JOIN|                 |9999995  |7488278|
|1 | TABLE SCAN         |T2(IDX_SBTEST1_C)|1        |93     |
|2 | TABLE SCAN         |T1               |10000000 |3945573|
=============================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD]), filter(nil),
      equal_conds([T2.K = T1.K]), other_conds(nil)
  1 - output([T2.K]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.K], [T2.C]), partitions(p0)
  2 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.01 sec)

obclient> EXPLAIN
    -> SELECT * FROM SBTEST2  t1
    -> WHERE NOT exists (
    -> SELECT k FROM sbtest12 t2 WHERE t2.k = t1.k AND t2.C LIKE '044208%'
    -> )\G
*************************** 1. row ***************************
Query Plan: =============================================================
|ID|OPERATOR            |NAME             |EST. ROWS|COST   |
-------------------------------------------------------------
|0 |HASH RIGHT ANTI JOIN|                 |9999995  |7488278|
|1 | TABLE SCAN         |T2(IDX_SBTEST2_C)|1        |93     |
|2 | TABLE SCAN         |T1               |10000000 |3945573|
=============================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD]), filter(nil),
      equal_conds([T2.K = T1.K]), other_conds(nil)
  1 - output([T2.K]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.K], [T2.C]), partitions(p0)
  2 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.00 sec)

结论：不管列是否包含 NULL ，NOT EXISTS 的执行计划是一样的。

IN 子查询

EXPLAIN 
SELECT * FROM SBTEST1  t1 
WHERE t1.k IN (
    SELECT k FROM sbtest11 t2 WHERE t2.C LIKE '04420889655%'
);

EXPLAIN 
SELECT * FROM SBTEST2  t1 
WHERE t1.k IN (
    SELECT k FROM sbtest12 t2 WHERE t2.C LIKE '04420889655%'
);

IN 子查询会被转换为 NESTED-LOOP JOIN （是否一定这样还有待进一步测试）。

obclient> EXPLAIN
    -> SELECT * FROM SBTEST1  t1
    -> WHERE t1.k IN (
    -> SELECT k FROM sbtest11 t2 WHERE t2.C LIKE '04420889655%'
    -> )
    -> \G
*************************** 1. row ***************************
Query Plan: ==========================================================
|ID|OPERATOR        |NAME                 |EST. ROWS|COST|
----------------------------------------------------------
|0 |NESTED-LOOP JOIN|                     |1        |84  |
|1 | SUBPLAN SCAN   |VIEW1                |1        |84  |
|2 |  HASH DISTINCT |                     |1        |84  |
|3 |   TABLE SCAN   |T2(IDX_SBTEST1_C)    |1        |84  |
|4 | TABLE SCAN     |T1(IDX_SBTEST1_K_C_2)|6        |115 |
==========================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD]), filter(nil),
      conds(nil), nl_params_([VIEW1.T2.K])
  1 - output([VIEW1.T2.K]), filter(nil),
      access([VIEW1.T2.K])
  2 - output([T2.K]), filter(nil),
      distinct([T2.K])
  3 - output([T2.K]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.C], [T2.K]), partitions(p0)
  4 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.01 sec)

obclient> EXPLAIN
    -> SELECT * FROM SBTEST2  t1
    -> WHERE t1.k IN (
    -> SELECT k FROM sbtest12 t2 WHERE t2.C LIKE '04420889655%'
    -> )\G
*************************** 1. row ***************************
Query Plan: ==========================================================
|ID|OPERATOR        |NAME                 |EST. ROWS|COST|
----------------------------------------------------------
|0 |NESTED-LOOP JOIN|                     |1        |84  |
|1 | SUBPLAN SCAN   |VIEW1                |1        |84  |
|2 |  HASH DISTINCT |                     |1        |84  |
|3 |   TABLE SCAN   |T2(IDX_SBTEST2_C)    |1        |84  |
|4 | TABLE SCAN     |T1(IDX_SBTEST2_K_C_2)|6        |115 |
==========================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD]), filter(nil),
      conds(nil), nl_params_([VIEW1.T2.K])
  1 - output([VIEW1.T2.K]), filter(nil),
      access([VIEW1.T2.K])
  2 - output([T2.K]), filter(nil),
      distinct([T2.K])
  3 - output([T2.K]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.C], [T2.K]), partitions(p0)
  4 - output([T1.K], [T1.ID], [T1.C], [T1.PAD]), filter(nil),
      access([T1.K], [T1.ID], [T1.C], [T1.PAD]), partitions(p0)

1 row in set (0.00 sec)

结论：不管列是否包含 NULL ，IN 子查询的执行计划是一样的。

NOT IN 子查询

EXPLAIN
SELECT * FROM SBTEST1  t1 
WHERE t1.k NOT IN (
    SELECT k FROM sbtest11 t2 WHERE t2.C LIKE '044208%'
) AND t1.c LIKE '044208%'
;

EXPLAIN 
SELECT * FROM SBTEST2  t1 
WHERE t1.k NOT IN (
    SELECT k FROM sbtest12 t2 WHERE t2.C LIKE '044208%'
) AND t1.c LIKE '044208%'
;

NOT IN 子查询会被转换为 ANTI JOIN。第二个子查询 sbtest12 会返回 NULL 值，所以这个查询结果集会为空。

obclient> EXPLAIN
    -> SELECT * FROM SBTEST1  t1
    -> WHERE t1.k NOT IN (
    -> SELECT k FROM sbtest11 t2 WHERE t2.C LIKE '044208%'
    -> ) AND t1.c LIKE '044208%'
    -> \G
*************************** 1. row ***************************
Query Plan: =============================================================
|ID|OPERATOR             |NAME               |EST. ROWS|COST|
-------------------------------------------------------------
|0 |NESTED-LOOP ANTI JOIN|                   |0        |93  |
|1 | TABLE SCAN          |T1(IDX_SBTEST1_C_2)|1        |93  |
|2 | TABLE SCAN          |T2(IDX_SBTEST1_K_C)|1        |40  |
=============================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD]), filter(nil),
      conds(nil), nl_params_([T1.K])
  1 - output([T1.K], [T1.C], [T1.ID], [T1.PAD]), filter([(T_OP_LIKE, T1.C, ?, '\')]),
      access([T1.K], [T1.C], [T1.ID], [T1.PAD]), partitions(p0)
  2 - output([1]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.C]), partitions(p0)

1 row in set (0.00 sec)


obclient> EXPLAIN
    -> SELECT * FROM SBTEST2  t1
    -> WHERE t1.k NOT IN (
    -> SELECT k FROM sbtest12 t2 WHERE t2.C LIKE '044208%'
    -> ) AND t1.c LIKE '044208%'
    -> \G
*************************** 1. row ***************************
Query Plan: =============================================================
|ID|OPERATOR             |NAME               |EST. ROWS|COST|
-------------------------------------------------------------
|0 |NESTED-LOOP ANTI JOIN|                   |0        |185 |
|1 | TABLE SCAN          |T1(IDX_SBTEST2_C_2)|1        |93  |
|2 | MATERIAL            |                   |1        |93  |
|3 |  TABLE SCAN         |T2(IDX_SBTEST2_C)  |1        |93  |
=============================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.ID], [T1.K], [T1.C], [T1.PAD]), filter(nil),
      conds([(T_OP_OR, T1.K = T2.K, (T_OP_IS, T1.K, NULL, 0), (T_OP_IS, T2.K, NULL, 0))]), nl_params_(nil)
  1 - output([T1.K], [T1.C], [T1.ID], [T1.PAD]), filter([(T_OP_LIKE, T1.C, ?, '\')]),
      access([T1.K], [T1.C], [T1.ID], [T1.PAD]), partitions(p0)
  2 - output([T2.K]), filter(nil)
  3 - output([T2.K]), filter([(T_OP_LIKE, T2.C, ?, '\')]),
      access([T2.C], [T2.K]), partitions(p0)

1 row in set (0.00 sec)

在 NOT IN 子查询里，后者子查询可能有NULL值返回，所以两个执行计划不一样了。

仔细观察可以看到 OB对NOT IN子查询都做了一些优化。会转换为（T1.K IS NULL OR T2.K IS NULL OR T1.K=T2.K ）运算，然后返回不匹配的记录。

对比看了一下ORACLE 11G的执行计划，OB 跟 ORACLE也不一样。ORACLE 的 NESTED-LOOP (ANTI JOIN) 不能支持左右两表连接条件都有空值情况存在,而 HASH JOIN(ANTI) 和 MERGE JOIN (ANTI) 可以。OB 没有这个问题。所以在某些场景下 OB 的性能会更好。

结论

除了NOT IN 子查询外，在OceanBase里索引列是否为NULL对是否选择该索引没有影响。列是否为NULL完全有业务设计决定，建议尽可能的指定列为NOT NULL 并指定默认值。这样能为应用规避一些错误。

这条只能作为数据库开发规范建议执行。开发同学可能经常忘记加NOT NULL，在OB里这个对性能没有影响。不必特意去改为NOT NULL。OB目前版本（2.2）还不支持将NULL列变NOT NULL列。实在要加，就新增一个同类型的列加NOT NULL，然后列重命名换过来，数据也复制过来。

OceanBase的索引里存储了NULL数据，在某些特殊场景下，OceanBase SQL引擎能生成更快速的执行计划，进而提升SQL性能。

更多参考

oracle的nested loop anti join :
http://www.juliandyke.com/Optimisation/Operations/NestedLoopsAnti.php
OceanBase 读写分离方案全攻略

如果觉得本文有帮助就点个赞😄️，有问题欢迎进钉钉群讨论。

oceanbase

文章转载自数据库技术闲谈，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。