刚开始学索引设计,在多表连接是发现设计的索引优化力度不大,真诚请教大家。explain:smoni_gas_hour_moni_data_jz(4492654行):smoni_gas_hour_moni_data_h_cur(2003行):smoni_item_std_limit (29372行) :
select tmp3.*,
(case when t1.item_code=107 then t1.toplimit end) nox_std_limit,
(case when t1.item_code=171 then t1.toplimit end) fume_std_limit,
(case when t1.item_code=187 then t1.toplimit end) so2_std_limit
from
(
select
tmp2.region_code,tmp2.region_name,tmp2.ent_code,tmp2.ent_name,tmp2.stname
,tmp1.*
,sysdate() update_time
from
(
SELECT
stcode,
item_code
,monitor_time
,max(case when item_code=107 then item_value end) nox
,max(case when item_code=107 then is_standard end) nox_is_standard
,max(case when item_code=171 then item_value end) fume
,max(case when item_code=171 then is_standard end) fume_is_standard
,max(case when item_code=187 then item_value end) so2
,max(case when item_code=187 then is_standard end) so2_is_standard
FROM smoni_gas_hour_moni_data_jz
where monitor_time>DATE_SUB(NOW(),INTERVAL 5 day) and item_code in (107,171,187)
GROUP BY stcode,monitor_time
) tmp1
LEFT JOIN envapp.smoni_gas_hour_moni_data_h_cur tmp2
ON tmp2.stcode = tmp1.stcode
AND tmp1.monitor_time BETWEEN DATE_SUB(
tmp2.monitor_time,
INTERVAL 23 HOUR
)
AND tmp2.monitor_time
where tmp2.stcode is not null
) tmp3
left join smoni_item_std_limit t1
on t1.ent_code = tmp3.ent_code and t1.stcode = tmp3.stcode and t1.item_code = tmp3.item_code
select tmp3.*,
(case when t1.item_code=107 then t1.toplimit end) nox_std_limit,
(case when t1.item_code=171 then t1.toplimit end) fume_std_limit,
(case when t1.item_code=187 then t1.toplimit end) so2_std_limit
from
(
select
tmp2.region_code,tmp2.region_name,tmp2.ent_code,tmp2.ent_name,tmp2.stname
,tmp1.*
,sysdate() update_time
from
(
SELECT
stcode,
item_code
,monitor_time
,max(case when item_code=107 then item_value end) nox
,max(case when item_code=107 then is_standard end) nox_is_standard
,max(case when item_code=171 then item_value end) fume
,max(case when item_code=171 then is_standard end) fume_is_standard
,max(case when item_code=187 then item_value end) so2
,max(case when item_code=187 then is_standard end) so2_is_standard
FROM smoni_gas_hour_moni_data_jz
where monitor_time>DATE_SUB(NOW(),INTERVAL 5 day) and item_code in (107,171,187)
GROUP BY stcode,monitor_time
) tmp1
LEFT JOIN envapp.smoni_gas_hour_moni_data_h_cur tmp2
ON tmp2.stcode = tmp1.stcode
AND tmp1.monitor_time BETWEEN DATE_SUB(
tmp2.monitor_time,
INTERVAL 23 HOUR
)
AND tmp2.monitor_time
where tmp2.stcode is not null
) tmp3
left join smoni_item_std_limit t1
on t1.ent_code = tmp3.ent_code and t1.stcode = tmp3.stcode and t1.item_code = tmp3.item_code
解决方案 »
- 求一个mysql存储过程if判断后进行字符串连接的例子
- 吐血请教redhat下mysql问题
- 有没有可能在不停服务情况下, 调整一个表的外键关系?
- 再次请教一个SQL语句的编写
- 请帮我看看 /usr/local/bin/mysql-proxy 命令设置读写分离并启动的
- sql语句中对布尔字段的处理
- DOS下如何在mysql中创建表
- 无法连接MySql 服务器
- (sql)请教一条语句,统计表中记录的有多少条,重复的不算.比喻下表,求表中"张三"的记录有多少条?
- 在线等,简单问题
- 没有访问的时候innodb buffer pool的free buffers是0
- 关于非索引字段被select的问题,大神帮忙解惑一下
在mysql中写这种相对复杂的语句,优化器不一定能产生相对较好的执行计划。你先单独分析一下这段单表的查询,看看是否用了索引,速度如何:
SELECT
stcode,
item_code
,monitor_time
,max(case when item_code=107 then item_value end) nox
,max(case when item_code=107 then is_standard end) nox_is_standard
,max(case when item_code=171 then item_value end) fume
,max(case when item_code=171 then is_standard end) fume_is_standard
,max(case when item_code=187 then item_value end) so2
,max(case when item_code=187 then is_standard end) so2_is_standard
FROM smoni_gas_hour_moni_data_jz
where monitor_time>DATE_SUB(NOW(),INTERVAL 5 day) and item_code in (107,171,187)
GROUP BY stcode,monitor_time