ITPub博客

首页 > Linux操作系统 > Linux操作系统 > MySQL的多表join中,子查询伤不起啊!

MySQL的多表join中,子查询伤不起啊!

原创 Linux操作系统 作者:mysqldbd 时间:2011-08-18 11:31:01 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-01-19

  • 博文量
    35
  • 访问量
    78740