SQL语句优化求助

淡定的米哥 发布于 2017/04/06 18:48
阅读 310
收藏 0
SELECT a.username as name,sum(b.money) as value 
FROM table_a a,table_b b 
where a.id=b.userid
GROUP BY a.username 
ORDER BY value desc 
limit 9

上面的表会做全表扫描,性能上消耗较大,各位大侠这个SQL有无优化的空间?

加载中
0
z
zhangyu023

select *
  from (SELECT a.username as name,
               (select sum(b.money) from table_b b where a.id = b.userid) as value
          FROM table_a a)
 order by value desc


试试看勒,本地没数据 不好测试

0
w
wxsl

额,a和b不关联?这样做好坑的

淡定的米哥
淡定的米哥
不好意思,为了简化写成1=1,引起歧义了。
0
Raveh
Raveh

建表的时候不建个userid字段么? b表里面的money是哪个username的?

淡定的米哥
淡定的米哥
为了简化写成了1=1,已经关联上了
0
不是simaguo
不是simaguo

B表group by userid,肯定会全表扫描,因为哪怕还有一行没扫完,你都不能百分百确定第9名是哪几个,除非money来个汇总字段;B表left joinA表,A表只要扫9个就够了

0
s
saisaikeq

为啥不做关联呢

0
tom-green
tom-green

建议拆分成2部

SELECT b.userid as userid,sum(b.money) as value 
FROM table_b b 
GROUP BY b.userid    --  userid 上有没有索引
ORDER BY value desc 
limit 9


select username from table_a a where in (前一次的userid集合)

0
beyondforever68
beyondforever68

试试这个语句效果对不对:

select a.username, b.value from table_a a, (select b.userid, sum(b.money) as value from table_b p group by p.userid) b where a.id=b.userid order by b.value desc limit 9

0
w
wxsl

select sum(b.money) as value,(select username from table_a a where a.ID = b.userid) as name from table_b b GROUP BY b.userid ORDER BY value desc  limit 9

返回顶部
顶部