mysql 联合查询统计 优化_MYSQL大表关联查询优化，阿里妈妈的DBA看过来

test environment

MYSQL version:mysql-5.1.33-linux

OS

Debian 2.6.18.dfsg.1-24)

CPUINFO:       double  E2180 2.0G

meninfo :1G

my.cnf里

skip-locking

key_buffer = 500M

max_allowed_packet = 1M

table_cache = 512

sort_buffer_size = 256M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

default-character-set = utf8

表A,表B数据量大概500W左右，store engine :MYISAM

下面的语句是由SP拼成的。

select tt.* from (

select t.ad_code,t.game,t.server_name,

sum(t.ad_count) ad_count,

sum(t.pp_reg_count) pp_reg_count,

sum(t.user_reg_count) user_reg_count,

sum(t.pp_recharge_count) pp_recharge_count,

sum(t.pp_recharge_sum) pp_recharge_sum,

max(t.count_pp_convert) count_pp_convert,

max(t.pp_game_convert) pp_game_convert

from (

select t2.ad_code as ad_code,t2.game,t2.server_name,ifnull(t2.ad_count,0) as ad_count,

ifnull(t2.pp_reg_count,0) as pp_reg_count,

ifnull(t3.user_reg_count,0) user_reg_count,

ifnull(t4.pp_recharge_count,0) pp_recharge_count,

ifnull(t4.pp_recharge_sum,0) pp_recharge_sum ,

concat(round(ifnull(ifnull(t2.pp_reg_count,0)/ifnull(t2.ad_count,0)*100,0),2),'%')  as count_pp_convert,

concat(round(ifnull(ifnull(t3.user_reg_count,0)/ifnull(t2.pp_reg_count,0)*100,0),2),'%')  as pp_game_convert

from

(select a.ad_code,game,server_name,a.ad_count,b.pp_reg_count from (select ad_code,sum(ad_count) ad_count from ad_info where

date>='2009-04-01' and date<='2009-04-29' and ad_partner='001' group by ad_code)a ,

(select ad_code,game,server_name,count(passport_code)   as pp_reg_count  from passport_reg_info where  pp_reg_time>=str_to_date('2009-04-01 00:00:00','%Y-%m-%d %H:%i:%s') and pp_reg_time<=str_to_date('2009-04-29 23:59:59','%Y-%m-%d %H:%i:%s') and game='Travian' group by ad_code,game,server_name) b where a.ad_code=b.ad_code) t2

left join

(select ad_code,game,server_name,count(user_id) as user_reg_count  from user_reg_info  where

user_reg_time>=str_to_date('2009-04-01 00:00:00','%Y-%m-%d %H:%i:%s') and user_reg_time<=str_to_date('2009-04-29 23:59:59','%Y-%m-%d %H:%i:%s') and game='Travian' group by ad_code,game,server_name) t3 on t2.ad_code=t3.ad_code  and t2.game=t3.game and t2.server_name=t3.server_name

left join

(select a.ad_code,a.game,a.server_name,count(b.passport_code) pp_recharge_count,sum(b.recharge_sum) as pp_recharge_sum from

(select ad_code,game,server_name,passport_code from passport_reg_info where

pp_reg_time>=str_to_date('2009-04-01 00:00:00','%Y-%m-%d %H:%i:%s') and pp_reg_time<=str_to_date('2009-04-29 23:59:59','%Y-%m-%d %H:%i:%s') and game='Travian') a ,passport_recharge b where a.passport_code =b.passport_code  and b.recharge_type='0' group by a.ad_code,a.game,a.server_name) t4

on t4.ad_code =t2.ad_code and t4.game=t2.game and t4.server_name =t2.server_name limit 0,30)  t group by t.ad_code,t.game,t.server_name with rollup) tt

where (tt.ad_code is null and tt.game is null and tt.server_name is null)

or (tt.ad_code is not null and tt.game is not null and tt.server_name is not null)

该建索引的都建了索引。

但基本上查询还是会死掉。请问该怎么优化呢？

阿里妈妈的MYSQL DBA看过来

[本帖最后由 zeromyth 于 2009-4-30 15:33 编辑]