ANBOB™

国产库在承接Oracle替换方面做了大量的兼容性，如果仅实现了语法兼容，没有相同的语义，这恐怕比不兼容还糟糕，比如原来的oracle的应用迁移到国产库后执行不报错，但却有可能和oracle得到完全不一样的结果。今天我们以万里开源的GreatDB中的sysdate为例， 对最近同事遇到这个案例简单分享。

事出一条简单的SQL

select * from testdb.tab_log where CLICKDATE>sysdate-0.1;

该表是范围分区表，CLICKDATE列为年月日时分秒的datetime类型，该列上有索引，主键是包含这列在内的4个列混合，在oracle中对于该语句，如果返回的数据量较少(<3%)可能会使用index scan 回表. 在oracle是查询最近0.1天内的数据。但这条SQL在GreateDB中执行时间超长，查看执行计划。

GreatDB Cluster[(none)]> explain select * from testdb.tab_log where CLICKDATE>sysdate-0.1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_log
   partitions: P_01,P_02,P_03,P_04,P_05,P_06,P_07,P_08,P_09,P_10,P_11,P_12,P_13,P_14,P_15,P_16,P_17,P_18,P_19,P_20,P_21,P_22,P_23,P_24,P_25,P_26,P_27,P_28,P_29,P_30,P_31,P_AA
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1045527980
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Note:

上面使用的FULL TABLE SCAN.

尝试使用force index 选项如oracle的 index hint强制优化器使用索引

GreatDB Cluster[icdpub]> explain select count(*) from testdb.tab_log FORCE INDEX (IX_BSF_MENUCLICKLOG_CLICKDATE)  where CLICKDATE>sysdate-0.1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_log
   partitions: P_01,P_02,P_03,P_04,P_05,P_06,P_07,P_08,P_09,P_10,P_11,P_12,P_13,P_14,P_15,P_16,P_17,P_18,P_19,P_20,P_21,P_22,P_23,P_24,P_25,P_26,P_27,P_28,P_29,P_30,P_31,P_AA
         type: index
possible_keys: NULL
          key: IX_BSF_MENUCLICKLOG_CLICKDATE
      key_len: 5
          ref: NULL
         rows: 1045527980
     filtered: 33.33
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

GreatDB Cluster[icdpub]> select count(*) from testdb.tab_log FORCE INDEX (IX_BSF_MENUCLICKLOG_CLICKDATE)  where CLICKDATE>sysdate-0.1;

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (42 min 39.45 sec) 

Note:
可以使用索引， 但是注意返回0条记录，耗时42分钟。思考一下，索引有效，选择率也不错无返回数据。为什么没有使用索引？关建是不太可能0.1天内无数据，这是个Wrong Result.

对比GreatDB 和oracle 的sysdate运算
先看ORACLE的结果

NOTE：
oracle后面跟的数值计算是‘天’为单位， 运算后返回的是date类型。

再看GreateDB

 GreatDB Cluster[(none)]> select sysdate,sysdate-0.1;    
+---------------------+------------------+
| sysdate             | sysdate-0.1      |
+---------------------+------------------+
| 2023-09-11 16:52:09 | 20230911165208.9 |
+---------------------+------------------+
1 row in set (0.00 sec)

GreatDB Cluster[(none)]> select now(),now()-0.1,sysdate,sysdate-0.1;
+---------------------+------------------+---------------------+------------------+
| now()               | now()-0.1        | sysdate             | sysdate-0.1      |
+---------------------+------------------+---------------------+------------------+
| 2023-09-13 17:19:07 | 20230913171906.9 | 2023-09-13 17:19:07 | 20230913171906.9 |
+---------------------+------------------+---------------------+------------------+
1 row in set (0.00 sec)

Note：
可见sysdate-0.1返回的并不是原date类型，值也不是0.1天，似乎是0.1秒, 并且sysdate的结果与mysql的now()一样，众所周知GreatDB就是MySQL的衍生产品，那sysdate可能就是now()的同义词，但还不是oracle的sysdate同义，这就是我们开头所说可怕之处。

如何查看返回记录的数据类型？

1，使用–column-type-info

$ mysql --column-type-info  -v
$ select xxx

2,  创建临时表

$ create table test as  select xxx ;
$ dest test

Note:
总之计算后的数据类型为数值型， 和整数运算返回是bigint, 与小数运算返回的decimal. 并不是和oracle一样的datetime类型， 如果类型不一样，在查询时需要隐士转换，可能导致无法使用索引。

GreatDB sysdate – X,  真的是’秒’为单位吗？

SQL> select now(),NOW()-3600;

NOTE:
1小时3600秒，但是我们减去3600，并不是和当前时间差1小时。139744也不是有效的时分秒.

interval 小数 day

select now(),now()-0.1,now()-1,now() - interval 0.6 day,now() - interval 0.1 day;

SELECT now(),DATE_SUB(NOW(), INTERVAL 0.5 DAY) AS c2,DATE_SUB(NOW(), INTERVAL 0.1 DAY) c3 ;

Note:
– interval X day 和date_sub  interval X day的X还是四舍五入法，不足0.5 当0, 否则当1 ‘天’。

DATE_SUB 改写

Oracle

select to_date('2023-09-19 14:33:44','yyyy-mm-dd hh24:mi:ss')-0.1;

GreatDB/MySQL

select now(),now()-0.1*24*60*60, DATE_SUB(NOW(), INTERVAL  0.1*24*60*60 second) ;

改写SQL后再看最补业务SQL的执行计划

select * from testdb.tab_log where CLICKDATE> DATE_SUB(now(),INTERVAL 0.1*24*60*60 second)\G

GreatDB Cluster[(none)]> explain select * from testdb.tab_log where CLICKDATE> DATE_SUB(now(), INTERVAL  0.1*24*60*60 second)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_log
   partitions: P_01,P_02,P_03,P_04,P_05,P_06,P_07,P_08,P_09,P_10,P_11,P_12,P_13,P_14,P_15,P_16,P_17,P_18,P_19,P_20,P_21,P_22,P_23,P_24,P_25,P_26,P_27,P_28,P_29,P_30,P_31,P_AA
         type: range
possible_keys: IX_BSF_MENUCLICKLOG_CLICKDATE
          key: IX_BSF_MENUCLICKLOG_CLICKDATE
      key_len: 5
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Note:
现在默认已可以使用index range scan. 响应时间回到了ms级。 属于理想的性能。

GoldenDB是否存在相同问题？
和GreatDB一样同根MySQL的另一国产库GoldenDB同样也实现了sysdate兼容，我们测试一下GoldenDB是否存在相同的问题?

MySQL [(none)]> select now(),now()-0.1,sysdate,sysdate-0.1;
--------------
select now(),now()-0.1,sysdate,sysdate-0.1
--------------

Field   1:  `NOW()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY 

Field   2:  `NOW()-0.1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     18
Max_length: 16
Decimals:   1
Flags:      NOT_NULL BINARY NUM 

Field   3:  `SYSDATE`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY 

Field   4:  `SYSDATE-0.1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      BINARY 


+---------------------+------------------+---------------------+---------------------+
| NOW()               | NOW()-0.1        | SYSDATE             | SYSDATE-0.1         |
+---------------------+------------------+---------------------+---------------------+
| 2023-09-20 08:23:34 | 20230920082333.9 | 2023-09-20 08:23:34 | 2023-09-20 05:59:34 |
+---------------------+------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Note:
在GoldenDB中sysdate的表现和Oracle一样。 对于两款数据库并不是对比好坏， 只是说明在sysdate这个常用函数上，GoldenDB至少是认真对待了。

— over —

打赏

微信扫一扫，打赏作者吧～