MySQL的多表join中，子查询伤不起啊！

leader找到我，说下面的sql执行后cpu100%，但是这三张join的表数据量都在2W左右，不应该啊，是不是数据库服务器配置出了问题啊。sql如下：

select a.excel_id, a.rpt_id, a.acc_std, a.prd_year, a.prd_qtr, a.accumulate, a.biz_cd, a.acc_cd, a.row_num,
a.col_num,a.col_unit , a.col_cur, a.row_unit, a.row_cur, a.data_id, a.item_cd, a.data_val,     
a.data_comment, a.data_trans_comment, a.trans_flag, a.trace_flag, a.trace_edit,   
b.data_src, b.orig_unit, b.pre_unit, c.data_trace
from (select * from cdp.listed_cpny_rpt_fin_style_6 where excel_id = 32) a

inner join (select * from cdp.listed_cpny_fin_rpt_prd where excel_id = 32) b

on (a.excel_id=b.excel_id and a.rpt_id=b.rpt_id and a.acc_std=b.acc_std and a.prd_year=b.prd_year
and a.prd_qtr=b.prd_qtr and a.accumulate=b.accumulate and a.biz_cd=b.biz_cd and a.acc_cd=b.acc_cd )
left outer join (select * from cdp.listed_cpny_rpt_fin_trace_6 where excel_id = 32) c on
(a.excel_id = c.excel_id and a.rpt_id = c.rpt_id and a.acc_std = c.acc_std and a.prd_year = c.prd_year  
and a.prd_qtr = c.prd_qtr and a.accumulate=c.accumulate and a.biz_cd = c.biz_cd
and a.acc_cd = c.acc_cd and a.row_num = c.row_num)

这条sql解析需要3秒，执行查询1000条数据在mysqlworkbeach界面显示出来要150秒，显示都走了索引，我开始以为是mysql参数配置不对呢，马上调整参数
注明下我的数据库服务器配置，4G内存，4核cpu，MySQL版本5.5.08社区版

innodb_buffer_pool_size = 2048M
max_allowed_packet = 128M
join_buffer_size = 16M
tmp_table_size = 1024M

重启数据库服务器，结果explain2.8秒，执行查询1000条数据在mysqlworkbeach界面显示出来要127秒，几乎没有啥改变啊？

迷茫之中，试试改sql吧，终于在网友的提醒之下，去掉子查询如下：

select a.excel_id, a.rpt_id, a.acc_std, a.prd_year, a.prd_qtr, a.accumulate, a.biz_cd, a.acc_cd, a.row_num,
a.col_num,a.col_unit , a.col_cur, a.row_unit, a.row_cur, a.data_id, a.item_cd, a.data_val,     
a.data_comment, a.data_trans_comment, a.trans_flag, a.trace_flag, a.trace_edit,   
b.data_src, b.orig_unit, b.pre_unit  , c.data_trace
from  cdp.listed_cpny_rpt_fin_style_6  a
inner join  cdp.listed_cpny_fin_rpt_prd b
on (a.excel_id=b.excel_id and a.rpt_id=b.rpt_id and a.acc_std=b.acc_std and a.prd_year=b.prd_year
and a.prd_qtr=b.prd_qtr and a.accumulate=b.accumulate and a.biz_cd=b.biz_cd and a.acc_cd=b.acc_cd )
left join  cdp.listed_cpny_rpt_fin_trace_6 c on
(a.excel_id = c.excel_id and a.rpt_id = c.rpt_id and a.acc_std = c.acc_std and a.prd_year = c.prd_year  
and a.prd_qtr = c.prd_qtr and a.accumulate=c.accumulate and a.biz_cd = c.biz_cd
and a.acc_cd = c.acc_cd and a.row_num = c.row_num)
where a.excel_id=32 and b.excel_id=32 and c.excel_id=32 ;

结果explain0.000秒，执行查询1000条数据在mysqlworkbeach界面显示出来要0.047秒，大功告成。



重启数据库服务器

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25267658/viewspace-705236/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25267658/viewspace-705236/