求助 mysql语句优化

Question

SELECT count(a.`type_id`) as promotion_count, a.`account`,a.`server_id` FROM 
                (SELECT `type_id`,`account`,`server_id` FROM `yx_hand_player_type` WHERE `type`='1' ) a 
                right join 
                (SELECT `f_account`,`f_server_id`,`gf_account`,`gf_server_id` from `yx_hand_player_type` WHERE `type`!='1') b
                on ((a.`account`=b.`f_account` and a.`server_id`=b.`f_server_id`) or (a.`account`=b.`gf_account` and a.`server_id`=b.`gf_server_id`))
                group by a.`account`, a.`server_id` order by promotion_count desc limit 0,10
============================================================================
一共花去时间 4.785s表的结构如下
主键：
type_id唯一索引： 
account  
server_id  
type  索引：
f_account  
f_server_id索引：
gf_account  
gf_server_id  
=====================补充下
去掉 limit 0,10
=================
总共查询记录 1,715 条
耗时也是 4.8657=========================================
小弟请教各位大虾
如何可以更加提升速度呢
5秒的延迟客户不接受勒

Answer 1

给出你的表结构show create table yx_hand_player_type;
show index from yx_hand_player_type;
explain SELECT count(a.`type_id`) as promotion_count, a.`account`,a.`server_id` FROM 
        (SELECT `type_id`,`account`,`server_id` FROM `yx_hand_player_type` WHERE `type`='1' ) a 
        right join 
        (SELECT `f_account`,`f_server_id`,`gf_account`,`gf_server_id` from `yx_hand_player_type` WHERE `type`!='1') b
        on ((a.`account`=b.`f_account` and a.`server_id`=b.`f_server_id`) or (a.`account`=b.`gf_account` and a.`server_id`=b.`gf_server_id`))
        group by a.`account`, a.`server_id` order by promotion_count desc limit 0,10;给出以上信息。

Answer 2

CREATE TABLE IF NOT EXISTS `yx_hand_player_type` (
  `type_id` int(11) NOT NULL auto_increment,
  `account` varchar(100) collate utf8_bin NOT NULL,
  `server_id` int(11) NOT NULL default '0',
  `type` char(1) collate utf8_bin NOT NULL default '0',
  `f_account` varchar(100) collate utf8_bin NOT NULL default '0',
  `f_server_id` int(11) NOT NULL default '0',
  `gf_account` varchar(100) collate utf8_bin NOT NULL default '0',
  `gf_server_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`type_id`),
  UNIQUE KEY `account` (`account`,`server_id`,`type`),
  KEY `f_account` (`f_account`,`f_server_id`),
  KEY `gf_account` (`gf_account`,`gf_server_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8979 ;

Answer 3

如果这里显示不够清楚
可以去看下我在cu上的帖子
http://bbs3.chinaunix.net/viewthread.php?tid=1542157&page=1&extra=page%3D1

Answer 4

type = 1 的记录能占全部记录的多少？ 10% ?

Answer 5

优化语句需要大量的信息。你的数据分布，索引的分布。这些你都不能提供，那就只能在不同的SQL语句中进行选择了。select a.account,a.server_id,count(*)
from yx_hand_player_type a
where exists (
select 1 from yx_hand_player_type 
where (f_account=a.account and f_server_id=a.server_id)
or (gf_account=a.account and gf_server_id=a.server_id)
)
group by a.account,a.server_id

Answer 6

看楼主不太愿意提供更多的信息。select f_account,f_server_id,count(*)
from (
select f_account,f_server_id
from yx_hand_player_type WHERE `type`='1'
union all
select gf_account,gf_server_id
from yx_hand_player_type WHERE `type`='1'
) t
group by 1,2

Answer 7

右连接和两个子查询可以去掉：
SELECT COUNT(a.`type_id`) as promotion_count, a.`account`,a.`server_id`
FROM `yx_hand_player_type` a, `yx_hand_player_type` b
WHERE a.`type` = '1' and b.`type` <> 1 and
      (a.`account` = b.`f_account` and a.`server_id` = b.`f_server_id`) or
      (a.`account` = b.`gf_account` and a.`server_id` = b.`gf_server_id`)
GROUP BY a.`account`, a.`server_id`
ORDER BY promotion_count DESC LIMIT 0,10
5楼的语句跟楼主原先的意图不相符。

Answer 8

(SELECT `type_id`,`account`,`server_id` FROM `yx_hand_player_type` WHERE `type`='1' ) a 
                right join 
                (SELECT `f_account`,`f_server_id`,`gf_account`,`gf_server_id` from `yx_hand_player_type` WHERE `type`!='1') b ---------------------------------------
主要的问题要看你这里的a和b这2个表的记录多不多，因为这里面会生成中间表的，涉及到磁盘IO的话，那当然就慢啦
建议把处理逻辑修改

Answer 9

试一下这样:
select count(type_id) as promotion_count,account,server_id from ( (select * from yx_hand_player_type where (account,server_id)=(f_account,f_server_id) or (account,server_id)=(gf_account,gf_server_id)) aa ) where aa.typeid='1' group by account,server_id order by promotion_count desc limit 0,10;

Answer 10

写错了一个 column : where aa.typeid='1' 应该是 where aa.type_id='1'重写一次:
select count(type_id) as promotion_count,account,server_id from ( (select * from yx_hand_player_type where (account,server_id)=(f_account,f_server_id) or (account,server_id)=(gf_account,gf_server_id)) aa ) where aa.type_id='1' group by account,server_id order by promotion_count desc limit 0,10;