DM数据库类型转换导致索引失效的案例分析

由于数据库中数据类型转换导致索引失效，效率下降的实际案例分析

一、问题现象

Java写了一个新模块，查看单条数据的详细信息时，速度很慢；但是其它类似的模块不存在这样的问题，很流畅，所以开始定位分析问题原因。

二、分析过程

• Java中的写法为

 /**
     * 请求ID
     */
    @ApiModelProperty(value = "请求ID")
    private Integer requestid;

• 数据库中涉及两张表：PRODUCTION_SYSTEM_FAILURE_REPORT和WORKFLOW_REQUESTLOG两张表
  • 其中，表PRODUCTION_SYSTEM_FAILURE_REPORT的数据量很小，只有几百条，REQUESTID字段确实为INTEGER；
  • WORKFLOW_REQUESTLOG表的REQUEST_ID字段为VARCHAR类型，但是数据量为700w左右（数据量较大），字段上已创建二级索引；

分析过程：主业务以PRODUCTION_SYSTEM_FAILURE_REPORT为基础，需要在WORKFLOW_REQUESTLOG表中查询对应request id的数据，由于业务表中id为integer类型，在大表中查询时，索引失效，导致查询效率大大下降。

三、验证过程

1. 在WORKFLOW_REQUESTLOG表中执行以下sql

   select * from WORKFLOW_REQUESTLOG where REQUEST_ID=12345;
   

   耗时：5.82秒（多次执行验证）
   执行计划如下：

   explain select * from "ECO-OA".WORKFLOW_REQUESTLOG where REQUEST_ID=2260252;
   1   #NSET2: [3154, 400802, 1208] 
   2     #PRJT2: [3154, 400802, 1208]; exp_num(26), is_atom(FALSE) 
   3       #SLCT2: [3154, 400802, 1208]; exp_cast(WORKFLOW_REQUESTLOG.REQUEST_ID) = 2260252 -- exp_cast表示类型转换
   4         #CSCN2: [3154, 8016053, 1208]; INDEX33556178(WORKFLOW_REQUESTLOG) -- 表示全表扫描
   

2. 对比执行以下sql

   select * from WORKFLOW_REQUESTLOG where REQUEST_ID='12345';
   

   耗时：0.02秒

   explain select * from "ECO-OA".WORKFLOW_REQUESTLOG where REQUEST_ID='2260252';
   1   #NSET2: [562, 200401, 1208] 
   2     #PRJT2: [562, 200401, 1208]; exp_num(26), is_atom(FALSE) 
   3       #BLKUP2: [562, 200401, 1208]; IDX_REQUEST_ID(WORKFLOW_REQUESTLOG)
   4         #SSEK2: [562, 200401, 1208]; scan_type(ASC), IDX_REQUEST_ID(WORKFLOW_REQUESTLOG), scan_range['2260252','2260252']
   

   对比上面两个执行计划，可以明显看出来，下面这个使用了二级索引-SSEK2和回表-BLKUP2，而上面这个时全表扫描-CSCN2和类型转换-exp_cast，所以速度很慢

四、解决方法

在Java中，查询requestlog 表之前，提前把字段类型转化下就好了。

纸上得来终觉浅，绝知此事要躬行；无论多少次阅读别人的经验贴，抵不上自己实际遇到一次来的印象深刻

类似文章推荐：数据类型不一致对执行计划的影响 | 达梦技术社区