这个SQL语句是一个整合的语句,在一个软件中使用,不可以拆分,它一旦执行,将从原始表库搜索6个用表,从而生成6个临时表.这6个表的结构近似,里面的实际字段很多.并非这里写出就这几个.如下,除了指标之类的字段外,其他这些写出来的都是索引字段.每个表以外连接相连.目前执行很慢.我想请教这里的高手,如何优化才能更快的执行.
a1--a6这些表的结构为:bsc|startdate|starttime|stopdate|stoptime|bts_index|bts_sector|cell_name|cell_ci|cell_lac|指标11|指标12|...
sql语句为:select a1.startdate, concat(hour(a1.starttime),':00:00') as starttime, a1.Sector1,
a1.sum_rt1_指标11+a2.sum_rt2_指标23 as `SDCCH信道切换掉话次数`,
a2.sum_rt2_指标22*0.4 as `二级干扰频带内的空闲信道平均数`,
a3.sum_rt3_指标33/a5.sum_rt4_指标45 as `上行LLC流量`,a4.DDDDD as `NB_PSI_MES`,
a5.sum_rt4_指标44 as `最坏小区个数`,
a6.sum_rt5_指标55 as `下行报务率`
from
(select startdate, concat(hour(starttime),':00:00') as starttime, concat BSC,'_',CELL_NAME,'_',BTS_INDEX,'_',BTS_SECTOR)as Sector1,
count(distinct(starttime))*90000 as CountDuration,
sum(rt1.`指标11`) as sum_rt1_指标11
sum(rt1.`指标12`) as sum_rt1_指标12
from omdb.rt110celltrxrelatedoverviewcounters1 rt1
where startdate >= '2011-06-21' and startdate <= '2011-06-21' and starttime >= '08:00:00' and starttime < '09:00:00'
group by startdate, hour(starttime), BSC,CELL_NAME,BTS_INDEX,BTS_SECTOR)
a1
left join
a2.....left join......a3.....left join ......a4.....left join......a5........left join ....... a6
a1--a6这些表的结构为:bsc|startdate|starttime|stopdate|stoptime|bts_index|bts_sector|cell_name|cell_ci|cell_lac|指标11|指标12|...
sql语句为:select a1.startdate, concat(hour(a1.starttime),':00:00') as starttime, a1.Sector1,
a1.sum_rt1_指标11+a2.sum_rt2_指标23 as `SDCCH信道切换掉话次数`,
a2.sum_rt2_指标22*0.4 as `二级干扰频带内的空闲信道平均数`,
a3.sum_rt3_指标33/a5.sum_rt4_指标45 as `上行LLC流量`,a4.DDDDD as `NB_PSI_MES`,
a5.sum_rt4_指标44 as `最坏小区个数`,
a6.sum_rt5_指标55 as `下行报务率`
from
(select startdate, concat(hour(starttime),':00:00') as starttime, concat BSC,'_',CELL_NAME,'_',BTS_INDEX,'_',BTS_SECTOR)as Sector1,
count(distinct(starttime))*90000 as CountDuration,
sum(rt1.`指标11`) as sum_rt1_指标11
sum(rt1.`指标12`) as sum_rt1_指标12
from omdb.rt110celltrxrelatedoverviewcounters1 rt1
where startdate >= '2011-06-21' and startdate <= '2011-06-21' and starttime >= '08:00:00' and starttime < '09:00:00'
group by startdate, hour(starttime), BSC,CELL_NAME,BTS_INDEX,BTS_SECTOR)
a1
left join
a2.....left join......a3.....left join ......a4.....left join......a5........left join ....... a6
1 PRIMARY <derived2> ALL 5532
1 PRIMARY <derived3> ALL 5532
1 PRIMARY <derived4> ALL 5532
1 PRIMARY <derived5> ALL 5530
1 PRIMARY <derived6> ALL 5530
1 PRIMARY <derived7> ALL 5530
7 DERIVED b20 ALL 134852 Using filesort
6 DERIVED b11 ALL 134852 Using filesort
5 DERIVED b10 ALL 134852 Using filesort
4 DERIVED rt2 ALL 9443288 Using filesort
3 DERIVED rt281 ALL 334105 Using filesort
2 DERIVED rt1 ALL 334000 Using filesortexplain了一下,展示字段位置我把没有数据的都放最后面了.
操作 键名 类型 唯一 紧凑 字段 基数 整理 空 注释
RT110CELLTRXrelatedoverviewcounters1_Index BTREE 是 否 BSS 2 A
BSC 190 A
StartDate 346 A
StartTime 4453 A
StopDate 4453 A
StopTime 4453 A
BTS_INDEX 167000 A
BTS_SECTOR 334000 A
CELL_NAME 334000 A
CELL_CI 334000 A
CELL_LAC 334000 A
BSC_MCC 334000 A
BSC_MNC 334000 A