用hextoraw解决update、delete语句执行慢

问题描述

    近期在做数据同步功能，各模块数据经过 AOP 拦截，形成insert、update、delete语句，统一进入Kafka，然后由消费端取出消费到Oracle。
    但在消费过程中发现，insert一条平均耗时 4 ms， 但更新一条数据平均耗时12s，尤其在更新3、4千万的大表时，可达到30多秒，删除也慢。导致消费堆积。

sql语句如下

新增   INSERT INTO TEST(ID,NAME,AGE,TIME) VALUES ('4089480E7212C8B4017292D62A49000D','张三','32'，sysdate);

修改   UPDATE TEST SET NAME='李四',AGE='45' WHERE ID = '4089480E7212C8B4017292D62A49000D';

删除   DELETE FROM TEST WHERE ID = '4089480E7212C8B4017292D62A49000D';

 

问题分析

    经过查看sql执行计划，判断问题出在，update和delete语句中的

 where id = ‘4089480E7212C8B4017292D62A49000D’

    因为我们表的主键ID采用 RAW(16) 类型，数据库存储的都是二进制，索引自然也是建立在二进制数据基础上。直接用 id = ‘4089480E7212C8B4017292D62A49000D’，相当于使用这个字符串去数据库中与所有id字符串比对，无法利用到索引，从而导致update和delete很慢。

 

解决方法

    使用Oracle中自带的 hextoraw 函数，如

UPDATE TEST SET NAME='李四',AGE='45' WHERE ID = hextoraw('4089480E7212C8B4017292D62A49000D');

    hextoraw 函数是将十六进制转为二进制，因为数据库存的就是二进制，索引也是在二进制数据上建立的，所以where条件根据id查找会走索引，update和delete慢的问题也得到解决。

    经过测试，

select * from test where id = '4089480E7212C8B4017292D62A49000D'
耗时20s


select * from test where id = hextoraw('4089480E7212C8B4017292D62A49000D')
耗时0.12s

 

总结

    所以在分析sql语句执行时，优先考虑是否有索引，查询是否走索引。
    如果 id 使用 自增 或者 字符类型 ，就不存在这个问题了，直接比对即可。
    由于项目运行好长时间了，最开始设计用的UUID当主键，类似的 sql 有很多，随着数据量的不断累积，查询效率大受影响，接下来要好好修改一番了。