关于统计查询的优化.doc

各位：最近在优化肿瘤系统的一个查询统计的时候碰到一个性能问题。具体的统计要求如下：相信大家在以往的工作中也碰到过类似的的统计。根据我们平常的经验做法，我们一般会把统计SQL携程类似的形式：select g.icd_group, g.group_name, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG 3 and IS_DELETE = 0 and t.PRESENT_RESIDENCE like 4401%) as total, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a0, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG 3 and t.PRESENT_RESIDENCE like 4401% and 1 * 12 months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a0_5, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 5 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a5_10, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 10 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a10_15, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 15 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a15_20, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 20 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a20_25, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 25 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a25_30, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 30 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a30_35, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 35 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a35_40, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 40 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a40_45, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 45 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a45_50, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 50 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a50_55, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 55 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a55_60, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 60 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a60_65, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 65 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a65_70, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 70 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a70_75, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEENdiagnose_date(t., t.birthday) and 75 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a75_80, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG months_BETWEEN(t.diagnose_date, t.birthday) and 80 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a80_85, (select count(*) from report t where t.INTERVIEW_DISTRIBUTE_STATE != 5 and t.icd_group like g.icd_group | % and t.IS_PASS_REVIEW = 2 and t.REPEATED_DISPOSE_FLAG 3 and t.PRESENT_RESIDENCE like 4401% and 85 * 12 = months_BETWEEN(t.diagnose_date, t.birthday) and IS_DELETE = 0) as a85_ from dict_icd_group g order by g.sn但是，这种统计方法在处理肿瘤系统十万多的数据量的情况下性能已经达不到要求，更不用说百万以上的数据量了，不管怎么设置索引处理时间都在10秒以上。因此，这次在做性能优化的时候我尝试了一个多年前构思但一直没有真正实现过的一种统计方法。