我这里有一个mysql的语句,效率很低。表中有15w数据,一下sql查询要用10s左右,远远不能满足要求: select count(distinct t.pid),
count(t.pid),
sum(concat(t.curr_dose, '')),
t.advi_date
from aidszh_mst_medicine t force index(IDX_ADVIDATE)
where 1 = 1
and t.clin_zonecode like '320103%'
and t.advi_date >= to_date('2010-06-09', 'yyyy-mm-dd')
and t.advi_date <= to_date('2010-07-09', 'yyyy-mm-dd')
and t.clin_no = '320103065'
group by t.advi_date经分析,以上sql走IDX_ADVIDATE索引,去掉强制索引force index(IDX_ADVIDATE)以后效率也是很低。表中的绝大多数数据的
clin_no 列和clin_zonecode 列都是相同的。advi_date最有可能限制数据。查询条件中的所有的列都建立了索引!
改变where条件中的顺序也没有效果!请问高手,我怎么优化我这个sql呢?
count(t.pid),
sum(concat(t.curr_dose, '')),
t.advi_date
from aidszh_mst_medicine t force index(IDX_ADVIDATE)
where 1 = 1
and t.clin_zonecode like '320103%'
and t.advi_date >= to_date('2010-06-09', 'yyyy-mm-dd')
and t.advi_date <= to_date('2010-07-09', 'yyyy-mm-dd')
and t.clin_no = '320103065'
group by t.advi_date经分析,以上sql走IDX_ADVIDATE索引,去掉强制索引force index(IDX_ADVIDATE)以后效率也是很低。表中的绝大多数数据的
clin_no 列和clin_zonecode 列都是相同的。advi_date最有可能限制数据。查询条件中的所有的列都建立了索引!
改变where条件中的顺序也没有效果!请问高手,我怎么优化我这个sql呢?
explain select count(distinct t.pid),
count(t.pid),
sum(concat(t.curr_dose, '')),
t.advi_date
from aidszh_mst_medicine t
where 1 = 1
and t.clin_zonecode like '320103%'
and t.advi_date >= to_date('2010-06-09', 'yyyy-mm-dd')
and t.advi_date <= to_date('2010-07-09', 'yyyy-mm-dd')
and t.clin_no = '320103065'
group by t.advi_date;2.
show index from aidszh_mst_medicine;
1,"SIMPLE","t","ref","IDX_CLINZONE,IDX_CLINNO","IDX_CLINNO","21",67650,"Using where; Using filesort"
2.
show index from aidszh_mst_medicine;"aidszh_mst_medicine",0,"PRIMARY",1,"ID_RECORD","A",120969,NULL,NULL,"","BTREE",""
"aidszh_mst_medicine",1,"IDX_PID",1,"PID","A",1359,NULL,NULL,"YES","BTREE",""
"aidszh_mst_medicine",1,"IDX_ADVIDATE",1,"ADVI_DATE","A",668,NULL,NULL,"YES","BTREE",""
"aidszh_mst_medicine",1,"IDX_CLINZONE",1,"CLIN_ZONECODE","A",10,NULL,NULL,"YES","BTREE",""
"aidszh_mst_medicine",1,"IDX_CLINNO",1,"CLIN_NO","A",10,NULL,NULL,"YES","BTREE",""
"aidszh_mst_medicine",1,"IDX_TMCREATE",1,"TM_CREATE","A",230,NULL,NULL,"YES","BTREE",""
create index xxx on aidszh_mst_medicine(CLIN_NO,IDX_ADVIDATE,IDX_CLINZONE,IDX_PID)
创建了索引,但是效率并没有什么明显的提高。
但是我删除xxx索引以后,效率有明显的提高,只用了1s。
看执行计划,和2楼的是一样的。太奇怪了!
为什么啊??为什么建立了索引然后在删除,效率就有很大的提高呢????
应该不会啊。你的表是什么存储引擎?删除的你的表中索引是什么?show出来看一下。
create index idx_all on aidszh_mst_medicine(CLIN_NO,IDX_ADVIDATE,IDX_CLINZONE,IDX_PID)
建立好索引后,我查了一下效率没什么提高。我就把这个索引idx_all删掉了,结果效率提高了,用了不到1s就好了太奇怪了。我现在把表上的索引删掉一些,只留下了IDX_TMCREATE和pid列的索引,效率提高了很多用了不到1s。
现在走的是全表扫描。我就奇怪了,为什么呢???你看,我强制走IDX_TMCREATE索引效率为什么不高呢?????这一列的值重复的很少的!