数据库instr使用

0

以前一直知道like查询,不过在一些情况下效率非常糟糕,所以用了instr这个函数。
当然,我这里探究的不是instrlike的效率问题,而是instr的一个细节问题。

看下面一个语句:

select SUM(
    case
    when instr(IC.info_course_title,'T8') > 0 then 1
    when instr(IC.info_course_title,'T9') > 0 then 1
    when instr(IC.info_course_title,'T10') > 0 then 1
    else 0 end
) as sum_a,
SUM(
    case
    when instr(IC.info_course_title,'T19') > 0 then 1
    when instr(IC.info_course_title,'T20') > 0 then 1
    else 0 end
) as sum_b,
SUM(
    case
    when instr(IC.info_course_title,'T1') > 0 then 1
    when instr(IC.info_course_title,'T2') > 0 then 1
    when instr(IC.info_course_title,'T3') > 0 then 1
    when instr(IC.info_course_title,'T4') > 0 then 1
    when instr(IC.info_course_title,'T5') > 0 then 1
    when instr(IC.info_course_title,'T6') > 0 then 1
    when instr(IC.info_course_title,'T7') > 0 then 1
    when instr(IC.info_course_title,'T16') > 0 then 1
    when instr(IC.info_course_title,'T17') > 0 then 1
    when instr(IC.info_course_title,'T18') > 0 then 1
    else 0 end
) as sum_c
from PINFO_TERM_USER PTU 
join ITLMS_REGISTER_COURSE IRC ON PTU.TERM_ID = IRC.TERM_ID
join ITLMS_COURSE IC on IRC.COURSE_ID = IC.ID;

这是我一个朋友的sql,当然很多朋友都喜欢写一些非常非常复杂的sql来处理业务。
但是有时候过于复杂的sql不如拆分一下,因为有些东西自己写程序处理也许会更快。

当然我拆分一下:

select SUM(
    case
    when instr(IC.info_course_title,'T19') > 0 then 1
    when instr(IC.info_course_title,'T20') > 0 then 1
    else 0 end
) as sum_a
from PINFO_TERM_USER PTU
join ITLMS_REGISTER_COURSE IRC ON PTU.TERM_ID = IRC.TERM_ID
join ITLMS_COURSE IC on IRC.COURSE_ID = IC.ID

好了,然后我再优化一下:

select count(ic.id) as sum_a
from PINFO_TERM_USER PTU
join ITLMS_REGISTER_COURSE IRC ON PTU.TERM_ID = IRC.TERM_ID
join ITLMS_COURSE IC on IRC.COURSE_ID = IC.ID where instr(IC.info_course_title,'T19') > 0 or instr(IC.info_course_title,'T20') > 0

当然也许你觉得这好像没有做什么优化是吧。

那么我们看一下结果:

instr

粗略估算一下,应该差不多有15倍左右的一个差距。

至于为什么?我猜想,大概因为第一种查询出来的结果太多了,然后分别在进行instr判断,所以导致时间增长吧。

当然后来又发现可以使用group by这个,然后相加:

SELECT
count(IC.ID) SUM_COUNT,
IC.INFO_COURSE_TITLE
FROM ITLMS_REGISTER_COURSE IRC
JOIN ITLMS_COURSE IC ON IRC.COURSE_ID = IC.ID
JOIN PINFO_TERM_USER PTU ON PTU.TERM_ID = IRC.TERM_ID
group by IC.INFO_COURSE_TITLE

当然效率我在MySQL上面一般,但是听说在Oracle上面非常好。

group by