系统运行了一段时间,用户反映速度超级的慢,看了下,这条语句执行至少要10多分钟,不知怎么优化啊.....
select t2.area_id,t2.team_id,t2.user_id,
t2.total,t2.total-ifnull(t1.vip_sum_total,0) as total_add,
t2.diamond,t2.diamond-ifnull(t1.diamond_total,0) as diamond_add,
t2.platina,t2.platina-ifnull(t1.platina_total,0) as platina_add,
t2.gold,t2.gold-ifnull(t1.gold_total,0) as gold_add,
t2.silver,t2.silver-ifnull(t1.silver_total,0) as silver_add,
t2.register,t2.register-ifnull(t1.register_total,0) as register_add,
t2.vip,ifnull(t2.add_vip,0)+ifnull((select sum(vip_add) from cus_stat
where advisor_id = user_id and DATE(stat_date)>='2008-01-04'
AND group_type in('4','5') ),0)as vip_add,
t2.latency,t2.latency-ifnull(t1.latency_total,0) as latency_add
from view_cus_stat t2
left join cus_stat t1
on t1.advisor_id = t2.user_id and DATE(t1.stat_date)='2008-01-04' AND t1.group_type in('4','5')
where t2.user_id='0001165AC9C4273FB3E61DC92F43C03F'
group by t2.area_id,t2.team_id,t2.user_id 谢谢大家了!!
select t2.area_id,t2.team_id,t2.user_id,
t2.total,t2.total-ifnull(t1.vip_sum_total,0) as total_add,
t2.diamond,t2.diamond-ifnull(t1.diamond_total,0) as diamond_add,
t2.platina,t2.platina-ifnull(t1.platina_total,0) as platina_add,
t2.gold,t2.gold-ifnull(t1.gold_total,0) as gold_add,
t2.silver,t2.silver-ifnull(t1.silver_total,0) as silver_add,
t2.register,t2.register-ifnull(t1.register_total,0) as register_add,
t2.vip,ifnull(t2.add_vip,0)+ifnull((select sum(vip_add) from cus_stat
where advisor_id = user_id and DATE(stat_date)>='2008-01-04'
AND group_type in('4','5') ),0)as vip_add,
t2.latency,t2.latency-ifnull(t1.latency_total,0) as latency_add
from view_cus_stat t2
left join cus_stat t1
on t1.advisor_id = t2.user_id and DATE(t1.stat_date)='2008-01-04' AND t1.group_type in('4','5')
where t2.user_id='0001165AC9C4273FB3E61DC92F43C03F'
group by t2.area_id,t2.team_id,t2.user_id 谢谢大家了!!
其实这是一个统计报表,view_cus_stat是一个视图,统计今天的各类客户数,
cus_stat则是每天晚上自动统计的各类客户数量,而上一个sql语句为查看截止到今天的数据,而cus_stat表中未统计到今天的数据,所以要用到实时查询,即视图:view_cus_stat
in里的group_type也是根据用户选择要查看的角色而定的,
客户可选择查询各时间段内的各类客户数,比如2007-03-01至2008-03-01内的统计数据的sql语句则为:select t2.area_id,t2.team_id,t2.advisor_id,
t2.vip_sum_total as total,t2.vip_sum_total-t1.vip_sum_total as total_add,
t2.diamond_total as diamond,t2.diamond_total-t1.diamond_total as diamond_add,
t2.platina_total as platina,t2.platina_total-t1.platina_total as platina_add,
t2.gold_total as gold,t2.gold_total-t1.gold_total as gold_add,
t2.silver_total as silver,t2.silver_total-t1.silver_total as silver_add,
t2.register_total as register,t2.register_total-t1.register_total as register_add,
t2.vip_total as vip,ifnull((select sum(vip_add) from cus_stat where advisor_id = advisor_id
and DATE(stat_date)>='2007-03-01'and DATE(stat_date)<='2008-03-01'
AND group_type in('4','5') ),0)as vip_add,
t2.latency_total as latency,t2.latency_total-t1.latency_total as latency_add
from cus_stat t1 left join cus_stat t2 on t1.advisor_id=t2.advisor_id
AND t1.group_type in('4','5')
where DATE(t1.stat_date)='2007-03-01' and DATE(t2.stat_date)='2008-03-01' and t1.advisor_id='0001165AC9C4273FB3E61DC92F43C03F'
group by t2.area_id,t2.team_id,t2.advisor_id
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_cus_stat` AS
select
sql_no_cache `t2`.`area_id` AS `area_id`,
`t1`.`inst_id` AS `inst_id`,
`t4`.`group_type` AS `group_type`,
coalesce((
select
sql_no_cache `pa_team_teaminfo`.`team_id` AS `team_id`
from
`pa_team_teaminfo`
where
((convert(`pa_team_teaminfo`.`team_leader` using utf8) = `t1`.`user_id`) and (`pa_team_teaminfo`.`delete_flag` = 0))),(
select
sql_no_cache `pa_team_teammemberinfo`.`team_id` AS `team_id`
from
`pa_team_teammemberinfo`
where
((convert(`pa_team_teammemberinfo`.`member_user_id` using utf8) = `t1`.`user_id`) and (`pa_team_teammemberinfo`.`delete_flag` = 0)))) AS `team_id`,`t1`.`user_id` AS `user_id`,(
select
sql_no_cache count(`cus_inf`.`cus_id`) AS `count(cus_id)`
from
`cus_inf`
where
((`cus_inf`.`advisor_id` = `t1`.`user_id`) and (`cus_inf`.`delete_flag` = _utf8'0') and (`cus_inf`.`cus_type` in (1,2,3,4,5,6)))) AS `total`,(
select
sql_no_cache count(`cus_inf`.`cus_id`) AS `count(cus_id)`
from
`cus_inf`
where
((`cus_inf`.`advisor_id` = `t1`.`user_id`) and (`cus_inf`.`delete_flag` = _utf8'0') and (`cus_inf`.`cus_type` = 1))) AS `diamond`,(
select
sql_no_cache count(`cus_inf`.`cus_id`) AS `count(cus_id)`
from
`cus_inf`
where
((`cus_inf`.`advisor_id` = `t1`.`user_id`) and (`cus_inf`.`delete_flag` = _utf8'0') and (`cus_inf`.`cus_type` = 2))) AS `platina`,(
select
sql_no_cache count(`cus_inf`.`cus_id`) AS `count(cus_id)`
from
`cus_inf`
where
((`cus_inf`.`advisor_id` = `t1`.`user_id`) and (`cus_inf`.`delete_flag` = _utf8'0') and (`cus_inf`.`cus_type` = 3))) AS `gold`,(
select
sql_no_cache count(`cus_inf`.`cus_id`) AS `count(cus_id)`
from
`cus_inf`
where
((`cus_inf`.`advisor_id` = `t1`.`user_id`) and (`cus_inf`.`delete_flag` = _utf8'0') and (`cus_inf`.`cus_type` = 4))) AS `silver`,(
select
sql_no_cache count(`cus_inf`.`cus_id`) AS `count(cus_id)`
from
`cus_inf`
where
((`cus_inf`.`advisor_id` = `t1`.`user_id`) and (`cus_inf`.`delete_flag` = _utf8'0') and (`cus_inf`.`cus_type` = 5))) AS `register`,(
select
sql_no_cache count(`cus_inf`.`cus_id`) AS `count(cus_id)`
from
`cus_inf`
where
((`cus_inf`.`advisor_id` = `t1`.`user_id`) and (`cus_inf`.`delete_flag` = _utf8'0') and (`cus_inf`.`cus_type` = 6))) AS `vip`,(
select
sql_no_cache count(`cus_inf`.`cus_id`) AS `count(cus_id)`
from
`cus_inf`
where
((`cus_inf`.`advisor_id` = `t1`.`user_id`) and (cast(`cus_inf`.`authentication_date` as date) = cast(now() as date)) and (`cus_inf`.`delete_flag` = _utf8'0') and (`cus_inf`.`cus_type` = 6))) AS `add_vip`,(
select
sql_no_cache count(`cus_inf`.`cus_id`) AS `count(cus_id)`
from
`cus_inf`
where
((`cus_inf`.`advisor_id` = `t1`.`user_id`) and (`cus_inf`.`delete_flag` = _utf8'0') and (`cus_inf`.`cus_type` = 7))) AS `latency`
from
(((`adm_user` `t1` left join `adm_inst` `t2` on((`t1`.`inst_id` = `t2`.`inst_id`))) left join `adm_user_role` `t3` on((convert(`t3`.`user_id` using utf8) = `t1`.`user_id`))) left join `adm_role` `t4` on((`t4`.`role_id` = `t3`.`role_id`)))
where
((`t4`.`group_type` in (4,5)) and (`t1`.`delete_flag` = 0));
EXPLAIN 的结果:谢谢了,帮分析一下!!!
EXPLAIN结果id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL 325
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 noahwm.t3.role_id 1 Using where
1 PRIMARY t1 ref PRIMARY PRIMARY 98 func 1 Using where
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 noahwm.t1.inst_id 1
12 DEPENDENT SUBQUERY cus_inf ALL 25537 Using where
11 DEPENDENT SUBQUERY cus_inf ALL 25537 Using where
10 DEPENDENT SUBQUERY cus_inf ALL 25537 Using where
9 DEPENDENT SUBQUERY cus_inf ALL 25537 Using where
8 DEPENDENT SUBQUERY cus_inf ALL 25537 Using where
7 DEPENDENT SUBQUERY cus_inf ALL 25537 Using where
6 DEPENDENT SUBQUERY cus_inf ALL 25537 Using where
5 DEPENDENT SUBQUERY cus_inf ALL 25537 Using where
4 DEPENDENT SUBQUERY cus_inf ALL 25537 Using where
3 DEPENDENT SUBQUERY pa_team_teammemberinfo ALL 101 Using where
2 DEPENDENT SUBQUERY pa_team_teaminfo ALL 34 Using where