以前一直认为数据库的执行计划、索引属于数据库调优的范畴,最多只影响数据库的执行效率,但是接触的案例多了,理解执行计划深入了之后发现,有的时候执行计划会影响数据库的执行结果,甚至能使原来正确运行的SQL报错,无法正常产生结果集。下面分享下笔者在日常工作中遇到的两个案例:
注:案例在原库能重现,此处没有提供可以重现的DEMO,只做参考之用,相关表和字段已做脱敏处理。
案例一:
DB:oracle 10.2.0.4
问题来源于一条SQL跑的很慢,原因在于某个字段存在NULL,走不了索引。后来在该字段上建立了函数索引
create index I_IND_BATCHCODE on TS_BATCHCODE(VBATCHCODE,0) parallel nologging;
alter index I_IND_BATCHCODE noparallel;
函数索引建立后SQL where is null 能走索引
=NULL 走不了索引,开发修改SQL为 is null之后走索引,运行结果很快,这条SQL引起的瓶颈消失。
但是后面出了个奇怪的问题,一条SQL增加过滤条件之后能出结果,删除这个条件则结果集为NULL,不符合SQL的逻辑,匪夷所思。
--下面是用友ERP里数据字典里的表,就不做脱敏了
select *
from ic_general_b a
join ic_general_h b
on a.cgeneralhid = b.cgeneralhid
join bd_stordoc c
on b.cwarehouseid = c.pk_stordoc
join bd_invbasdoc d
on a.cinvbasid = d.pk_invbasdoc
join bd_measdoc h
on h.pk_measdoc = d.pk_measdoc
join ts_batchcode e
on a.vbatchcode = e.vbatchcode
left join bd_cubasdoc f
on b.pk_cubasdoc = f.pk_cubasdoc
join bd_invcl g
on d.pk_invcl = g.pk_invcl
left join sm_user i
on b.coperatorid = i.cuserid
where a.dr = 0
and b.dr = 0
and nvl(a.csourcetype, 'm') <> '4K'
and b.cbilltypecode in ('47', '4A', '45')
and a.dbizdate ='2016-02-17'
-- and vbillcode ='TR*******'
加上vbillcode =’TR*******’,能出65条结果,注释掉这个条件,没有任何结果。
查看执行计划发现,能出结果走的是U_IND_BATCHCODE这个索引,不能出结果走的是新建的函数索引I_IND_BATCHCODE。
U_IND_BATCHCODE的建立SQL如下:
create unique index U_IND_BATCHCODE on TS_BATCHCODE(VBATCHCODE) parallel nologging;
alter index U_IND_BATCHCODE noparallel;
删除索引I_IND_BATCHCODE,不走函数索引后,结果正常。
案例二:
DB:sqlserver2014
问题来源于开发人员,开发人员反馈,以前能正常跑通的SQL现在跑不通了,查询报错,表结构没有做任何修改。
报错:消息 8114,级别 16,状态 5,第 1 行
Error converting data type varchar to bigint.
明显的格式转换问题,于是逐步简化SQL,最后定位于这样一个SQL片段:
LEFT JOIN TABLENAME I
ON ON J.STOCK_UNI_CODE=I.SRC_ID
查看字段定义:
STOCK_UNI_CODE bigint
SRC_ID varchar
类型不符,强转可能报错。查看SRC_ID字段,发现虽然大部分是数字,但是还是存在一部分含有字母的字符串。
和开发人员反馈,开发人员表示虽然这个字段是字符串,但是在SQL最后的where条件中,存在一个过滤条件,在这个过滤条件的过滤下,I表的SRC_ID字段全部是数字。
select * from tablename where 过滤条件 and SRC_ID like '%[^0-9]%' --result:null
查看执行计划发现先走的left join,join之后的结果集再走的过滤条件,所以报错,而且这条SQL中多次引用了这个表。
若先做的表过滤再走left join则不会存在问题。最后笔者建议手工强转格式,问题解决。