各位大大帮忙优化下这语句呀

Question

sql 优化方面不是很懂 
有3个表  用户表 tbl_user 角色表 tbl_player 登陆表 tbl_log_login 
一个用户 可以有几个角色 一个角色登陆一次就有一条登陆记录想查某个时间段内 输出结果集  用户id  几个角色  登陆次数SET @date1='2010-10-01 00:00:00';
SET @date2='2010-12-02 00:00:00';
SELECT a.uid,COUNT(*) loginNum,COUNT(DISTINCT b.id) playernum
FROM tbl_user a,tbl_player b,tbl_log_login c
WHERE a.uid=b.uid and a.uid=c.uid and b.id=c.pid 
and registerTime>=@date1 and registerTime<@date2
and c.logoutTime>=@date1 and c.logoutTime<@date2
GROUP BY a.uid原查询改了下 这是简单点的 忽略可能出现的错误 大体的都在这了 

Answer 1

贴下 explain的 
1 SIMPLE c ALL 1466794 Using where; Using temporary; Using filesort
1 SIMPLE b ref uid uid 107 mmo3_stat.c.uid 1 Using where
1 SIMPLE a ALL registerTime 36468 Using where

Answer 2

索引情况如何，在AID、ID上建立复合索引
registerTime、logoutTime是哪个表的

Answer 3

本来我想给登陆表加个索引  
alter table tbl_log_login add index tmp_index_tbl_log_login (logoutTime); 没想到运行了N久 N次 卡了

Answer 4

registerTime 是用户表的tbl_user 注册时间、logoutTime 是登陆表tbl_log_login的登出时间

Answer 5

好像就 登陆表没相关条件上的索引  额 貌似没一个额 我加个 uid pid logouTime 索引看看先

Answer 6

加了快多了 - -郁闷昨天为啥我加个索引半天没反应最后还是卡死米用
 alter table tbl_log_login add index tmp_index_uid_log_login (uid,pid,logoutTime);
alter table tbl_user add index tmp_index_uid_user (uid);

Answer 7

还想 left join 一个 这个 用户id 对应的  角色等级=1的数量 LEFT JOIN 
(SELECT uid,COUNT(*) loginNum FROM tbl_player
WHERE level=1 GROUP BY uid) d
ON a.uid=d.uid但不知放哪里好？ 是把上面那3个表联合的结果集作为一个 表 LEFT JOIN (SELECT uid,COUNT(*) loginNum FROM tbl_player
WHERE level=1 GROUP BY uid) d
这个吗？还是有更简单的 我测试下先

Answer 8

有索引就是好,楼主再看看explain 状况如何

Answer 9

创建索引tbl_user(uid,registerTime)
tbl_player(uid)
tbl_log_login(uid,pid,logoutTime)

Answer 10

贴出你的 
explain SELECT a.uid,COUNT(*) loginNum,COUNT(DISTINCT b.id) playernum
FROM tbl_user a,tbl_player b,tbl_log_login c
WHERE a.uid=b.uid and a.uid=c.uid and b.id=c.pid  
and registerTime>=@date1 and registerTime<@date2
and c.logoutTime>=@date1 and c.logoutTime<@date2
GROUP BY a.uid;show index from tbl_user;
show index from tbl_player;
show index from tbl_log_login;

Answer 11

tbl_user(uid,registerTime)
tbl_player(uid,id)
tbl_log_login(uid,pid,logoutTime)

Answer 12

1 SIMPLE a range registerTime,tmp_index_uid_user registerTime 9 38 Using where; Using filesort
1 SIMPLE b ref uid,tmp_index_pid uid 107 a.uid 1 Using index
1 SIMPLE c ref tmp_index_uid_log_login tmp_index_uid_log_login 66 a.uid,b.id 6 Using where; Using index
这个是简单的那3个表的联合查询 加过索引
|  1 | PRIMARY     | <derived3> | ALL   | NULL                    | NULL                    | NULL    | NULL                           | 278964 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | a          | ref   | tmp_index_uid_user      | tmp_index_uid_user      | 107     | e.uid                          |      1 | Using where                                  |
|  1 | PRIMARY     | b          | ref   | uid                     | uid                     | 107     | e.uid                          |      1 | Using index                                  |
|  1 | PRIMARY     | c          | ref   | tmp_index_uid_log_login | tmp_index_uid_log_login | 66      | mmo3_stat.a.uid,mmo3_stat.b.id |      6 | Using where                                  |
|  1 | PRIMARY     | <derived2> | ALL   | NULL                    | NULL                    | NULL    | NULL                           |    776 |                                              |
|  3 | DERIVED     | tbl_user   | index | NULL                    | tmp_index_uid_user      | 107     | NULL                           | 381026 |                                              |
|  2 | DERIVED     | tbl_player | index | NULL                    | uid                     | 107     | NULL                           | 373686 | Using where  这个是加了 left join 的 貌似有点慢 不知道该SELECT XXX IFNULL(max(c.userAgent),max(a.userAgent)),IFNULL(max(c.version),max(a.version))
FROM tbl_log_login c,tbl_player b,tbl_user a
LEFT JOIN
(SELECT uid,COUNT(distinct id) playernumlevel FROM tbl_player
WHERE level=1 and logoutTime>='2010-11-10' and logoutTime<'2010-11-11' GROUP BY uid) d
ON a.uid=d.uid
LEFT JOIN
(SELECT uid,min(registerTime) registerTime FROM tbl_user GROUP BY uid) e
ON a.uid=e.uid
WHERE a.uid=b.uid and a.uid=c.uid and b.id=c.pid
and e.registerTime>='2010-11-10' and e.registerTime<'2010-11-11'
AND cp='30237' AND left(a.uid,4) in(0401,0402,0403)
GROUP BY a.uid;=============
下面是索引情况
tbl_user 0 PRIMARY 1 zone A 6 BTREE
tbl_user 0 PRIMARY 2 uid A 36000 BTREE
tbl_user 1 registerTime 1 registerTime A 36000 YES BTREE
tbl_user 1 convertTime 1 convertTime A 2 YES BTREE
tbl_user 1 tmp_index_uid_user 1 uid A 36000 BTREE
----------------------
tbl_player 0 PRIMARY 1 zone A 18 BTREE
tbl_player 0 PRIMARY 2 id A 134420 BTREE
tbl_player 0 name 1 name A 134420 BTREE
tbl_player 1 uid 1 uid A 134420 BTREE
tbl_player 1 createDate 1 createDate A 134420 BTREE
tbl_player 1 tmp_index_pid 1 id A 134420 BTREE
--------------------
tbl_log_login 0 PRIMARY 1 zone A BTREE
tbl_log_login 0 PRIMARY 2 id A 1466794 BTREE
tbl_log_login 1 tmp_index_uid_log_login 1 uid A 162977 BTREE
tbl_log_login 1 tmp_index_uid_log_login 2 pid A 244465 BTREE
tbl_log_login 1 tmp_index_uid_log_login 3 logoutTime A 1466794 YES BTREE

Answer 13

各位 大大 有办法把 这句 用临时表Using temporary 去掉吗
这个是简单的那3个表的联合查询 加过索引
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 278964 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | a | ref | tmp_index_uid_user | tmp_index_uid_user | 107 | e.uid | 1 | Using where |
| 1 | PRIMARY | b | ref | uid | uid | 107 | e.uid | 1 | Using index |
| 1 | PRIMARY | c | ref | tmp_index_uid_log_login | tmp_index_uid_log_login | 66 | mmo3_stat.a.uid,mmo3_stat.b.id | 6 | Using where |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 776 | |
| 3 | DERIVED | tbl_user | index | NULL | tmp_index_uid_user | 107 | NULL | 381026 | |
| 2 | DERIVED | tbl_player | index | NULL | uid | 107 | NULL | 373686 | Using where   这个是加了 left join 的 貌似有点慢 不知道该SELECT XXX IFNULL(max(c.userAgent),max(a.userAgent)),IFNULL(max(c.version),max(a.version))
FROM tbl_log_login c,tbl_player b,tbl_user a
LEFT JOIN
(SELECT uid,COUNT(distinct id) playernumlevel FROM tbl_player
WHERE level=1 and logoutTime>='2010-11-10' and logoutTime<'2010-11-11' GROUP BY uid) d
ON a.uid=d.uid
LEFT JOIN
(SELECT uid,min(registerTime) registerTime FROM tbl_user GROUP BY uid) e
ON a.uid=e.uid
WHERE a.uid=b.uid and a.uid=c.uid and b.id=c.pid
and e.registerTime>='2010-11-10' and e.registerTime<'2010-11-11'
AND cp='30237' AND left(a.uid,4) in(0401,0402,0403)
GROUP BY a.uid;

Answer 14

太乱了,看的头晕;
加个order by null 把using sortfile 取消掉,看看怎么样;