从隐式转换案例，来挖掘开发人员的技能提升

最新推荐文章于 2024-06-29 01:04:37 发布

bisal(Chen Liu)

最新推荐文章于 2024-06-29 01:04:37 发布

阅读量473

点赞数

本文链接：https://blog.csdn.net/bisal/article/details/104628355

版权

前几天在墨天轮的论坛上，碰到个问题(https://www.modb.pro/issue/1440)，有点意思，值得在这说下。

问题说明：

TBL_TallyABC存在联合主键，

WorkDate，ReceiveDate，StatDate，BillCycle，SettlementOrgID，StatType

SQL的执行计划是全表扫描，

update TBL_TallyABC set Count = Count + 1,Amount = Amount + :1
where WorkDate = :2 and ReceiveDate = :3 and StatDate = :4 and
BillCycle = :5 and SettlementOrgID = :6 and StatType = :7

发现这个SQL造成数据库高峰时段75%的enq: TX - row lock contention等待事件，且锁的模型是6是什么原因呢，怎样减少enq: TX - row lock contention？

从上面的信息，可以知道这几点，

1. update语句的where条件字段都是复合索引的字段。

2. 但是update语句没用上索引，为全表扫描。

3. 执行update语句期间，数据库出现enq: TX - row lock contention争用。

基于以上信息，推测之所以出现行锁争用，是这样的逻辑，

当对表行进行DML操作的时候，需要获取相应锁，enq: TX - row lock contention就是行锁争用，之所以出现这个争用，就是因为UPDATE用了全表扫描，导致一条SQL的执行时间比以前更久，大量UPDATE操作，雪崩效应，就会让行锁争用更明显。

朋友虽然没给出这个问题中具体的表结构、绑定变量值，但是，我们是可以反推出实验的，如下所示，表t_001，含三个字段，分别是timestamp、date和number类型，此时，创建(timestamp, date, number)复合索引，

SQL> create table t_001(a_ts timestamp, a_date date, id number);
Table created.

SQL> alter table t_001 add constraint pk_t_001 primary key(a_ts, a_date, id);
Table altered.

执行select，where条件涉及时间的，统一使用to_date转成date类型，左值a_ts字段是timestamp类型，右值date类型，因为timestamp优先级高于date，因此在这左值不用隐式转换，从使用的索引唯一扫描就能证明这点，复合索引的三个字段都用上了，

SQL> select * from t_001 where id = 1 and a_ts  = to_date('2020-02-15','yyyy-mm-dd') and 
  2  a_date = to_date('2020-02-15','yyyy-mm-dd');

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 76550962

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    35 |     0   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| PK_T_001 |     1 |    35 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A_TS"=TIMESTAMP' 2020-02-15 00:00:00' AND
              "A_DATE"=TO_DATE(' 2020-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ID"=1)

但是，当使用下面的SQL，where条件涉及时间的统一使用to_timestamp转成timestamp类型，左值a_date是date类型，右值转成timestamp类型，因为timestamp优先级高于date，此时左值会进行隐式转换，需要将date转成timestamp，而且从谓词信息，可以看到，Oracle对a_date使用了INTERNAL_FUNCTION函数，对字段使用了函数操作，就会导致字段上的索引失效，谓词从access改为了filter，这个复合索引，仅可用到其中一部分字段，其他字段只能作为过滤条件，从使用的索引范围扫描就能证明这个推测，

SQL> select * from t_001 where id = 1 and a_ts  = to_timestamp('2020-02-15 00:01:00','yyyy-mm-dd hh24:mi:ss')
  2  and a_date = to_timestamp('2020-02-15 00:01:00','yyyy-mm-dd hh24:mi:ss');

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 491049371

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    35 |     0   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PK_T_001 |     1 |    35 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A_TS"=TIMESTAMP' 2020-02-15 00:01:00.000000000' AND
              "ID"=1)
       filter("ID"=1 AND INTERNAL_FUNCTION("A_DATE")=TIMESTAMP'
              2020-02-15 00:01:00.000000000')

隐式转换，说白了，就是当where条件“=”号右值的优先级高于“=”号左值的优先级，此时就需要对左值进行函数操作，借此转换成和右值相同的类型，就像Oracle中nvarchar2、varchar2、char以及timestamp和date。

以下是和隐式转换相关的历史文章，

《浅谈显式转换和隐式转换》

《隐式转换的案例场景》

《一次有意思的错选执行计划问题定位》

对这个问题，如果各位有什么其他的见解，欢迎在文末留言，我们一同探讨。

然而，实际情况是，开发人员很可能因为对字段类型的错误理解，导致写出来的程序出现隐式转换，而且在测试环境，数据量很小，这种性能问题是不能暴露出来的，可到了生产环境，他就会对系统的稳定运行产生影响。

因此，作为开发人员，除了需要了解数据库的CRUD，对可能产生性能隐患的一些数据库基础知识同样需要了解，不能做code的搬运工，而是要做创造者，才可能提升自己的个人价值。

当你要确定自己写的SQL代码在性能上是否存在隐患的时候，就可能会用到执行计划，你要知道怎么得到真实的执行计划，判断执行计划的正确，根据执行计划纠正自己的SQL。

当你要对表结构做调整，例如增加字段、删除字段，你可能需要了解在执行过程中他会持有什么级别的锁，知道这个操作对数据库有什么影响。

当你要删除大表数据，或者更新大表数据的时候，你可能需要根据实际情况，以及对数据库原理的理解，来判断选择合适的方案，方案选择的对，可能秒级完成，否则，几个小时都未必能完成。

其实像这样的例子还很多，开发人员对数据库原理了解的越多，就会越让你在关键时刻做出正确的判断。