mysql多表join中，为什么子查询会那么慢，跟oracle怎么相差那么大呢？

Question

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秒，大功告成。
mysql多表join中，为什么子查询会那么慢，跟oracle怎么相差那么大呢？oracle里面先子查询缩小范围然后再join速度会比较快，而mysql中怎么不行呢，这是为什么呢？

Answer 1

Mark... 子查询确实很影响效率，也想知道深层原因。

Answer 2

explain看一下应该是直接join，能够用到索引，而子查询没法用索引的缘故

Answer 3

子查询改成exists好，也许mysql的优化并不如你想象的好

Answer 4

请贴出explain结果
我说的不能索引，是指DERIVED那些

Answer 5

后一个不用子查询的解析结果如下：'1', 'SIMPLE', 'a', 'ref', 'PRIMARY', 'PRIMARY', '4', 'const', '7534', ''
'1', 'SIMPLE', 'b', 'eq_ref', 'PRIMARY', 'PRIMARY', '25', 'const,cdp.a.rpt_id,cdp.a.acc_std,cdp.a.prd_year,cdp.a.prd_qtr,cdp.a.accumulate,cdp.a.biz_cd,cdp.a.acc_cd', '1', ''
'1', 'SIMPLE', 'c', 'eq_ref', 'PRIMARY', 'PRIMARY', '29', 'const,cdp.b.rpt_id,cdp.b.acc_std,cdp.a.prd_year,cdp.a.prd_qtr,cdp.b.accumulate,cdp.b.biz_cd,cdp.a.row_num,cdp.b.acc_cd', '1', 'Using where'

Answer 6

前一个用了集合结果之后再jion的解析结果如下：'1', 'PRIMARY', '<derived3>', 'ALL', NULL, NULL, NULL, NULL, '291', ''
'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '17196', 'Using where; Using join buffer'
'1', 'PRIMARY', '<derived4>', 'ALL', NULL, NULL, NULL, NULL, '17196', ''
'4', 'DERIVED', 'listed_cpny_rpt_fin_trace_6', 'ref', 'PRIMARY', 'PRIMARY', '4', '', '8895', ''
'3', 'DERIVED', 'listed_cpny_fin_rpt_prd', 'ref', 'PRIMARY', 'PRIMARY', '4', '', '290', ''
'2', 'DERIVED', 'listed_cpny_rpt_fin_style_6', 'ref', 'PRIMARY', 'PRIMARY', '4', '', '7534', ''