求一Mysql 优化方案

Question

现有两张表
 1.用户表(user)，字段(uid,....,jifen[积分字段])
 2.排行表(paihang) ,字段(id,uid,paiming[当前排名],yspaiming[上一次统计排名])目的：安装用户表(user)中积分值 从高到低排序，积分最高排名第一，这样一次往下类推 2,3,4.....现在方案：
$paiming =1;
$res = $this-> db -> query("SELECT user.id,user.jifen,ph.paiming FROM user LEFT JOIN paihang ph ON ph.uid = user.id ORDER BY user.meili DESC");
while ($value = $this->db -> fetch($res))
{
//如果排行表中没有记录，则追加记录，否则修改记录即可
if($value['paiming'] && $value['paiming']>0)
{
$this->db -> query("UPDATE paihang SET
 jifen={$value['meili']},
 paiming={$paiming},
 yspaiming={$value['paiming']} WHERE uid = $value[id]");
}
else 
{
$this-> db -> query("INSERT INTO paihang (uid,jifen,paiming,yspaiming
)VALUES(
{$value['id']},{$value['meili']},
{$paiming},$paiming)");
}
$paiming ++;
}
return true;如果当用户表比较大时，（10几万的 数据）处理就相当吃力了，求一个优化方案!!!

Answer 1

可以考虑放在一个表里(user) jifen字段加索引，  在这里找当前排名增加另一字段保存上次排名，同样加索引。。=====================数据规模更大的时候，根据应用，再考虑分表

Answer 2

第二张表增加积分字段，这样你查询的时候只需要查询第一张表的积分
然后执行更新语句。语句示例为：
UPDATE paihang SET                                          
 jifen={$value['jifen']},
 paiming={$paiming},
 yspaiming=paiming WHERE uid = $value[id]也就是上次排名等于原来的paiming 字段
同时根据mysql_affected_rows()函数判断是否有记录被更新，没有的话再执行insert操作。

Answer 3

把表合并了了，然后建立索引。一般大的表，数据很多的，都会建立索引，这也是sql优化的一个方案

Answer 4

首先你应该explain分析语句，正确利用建立有效的索引解决问题。

Answer 5

1. 你得把表结构(含索引)贴出来,要不大家都让你建索引....
2. 这个几句sql就行,不用php一个一个insert/update

Answer 6

几句SQL就行？如何做的？学习

Answer 7

left join数据多了就挂了. 
可以用笨办法, 做一个php, shell.程序段
取max最大值.排名第一
然后小于这个值的max第二. 
依次往那个表里写. 看程序多久运行完. 用crontab定个时间, 排名多久更新一次就ok了. 

Answer 8

这位大哥提醒的对，索引是建了的
2. 这个几句sql就行,不用php一个一个insert/update ？？
还请赐教

Answer 9

刚开始我就是查的一张表，然后在循环中查询jifen表，然后再修改jifen表的值，效率还不如left join 

Answer 10

解决方案1：10万条数据瓶颈应该是在left join查询：SELECT user.id,user.jifen,ph.paiming FROM user LEFT JOIN paihang ph ON ph.uid = user.id ORDER BY user.meili DESC对user和paihang表加索引:
alter table user  add index ('id');
alter table paihang add index ('uid');
user表的meili字段是干嘛的，如果是日期的话加索引 
alter table user  add index ('meili');解决方案2：按照楼主的逻辑 每个用户的当前排名和上次排名应该都是唯一的值也就是说 user和paihang是一一对应关系，完全可以放一张表里面，避免使用jion 查询join查询数据量大的时候，能不就就不要用。数据库设计的时候可以用适当的冗余换取数据

Answer 11

经测试，在用户表达到20w数据时，如果采用2张表，用left join 来查询，然后更新paihang表，花费时间在40s左右，而采用一张表 然后再更新user表时，却要花费230s[注：这里假设每条语句都需要更新，如果关闭更新sql语句，则在38s左右]，我在琢磨，是不是当user表里字段比较多时，更新要比字段少的paihang表要更加耗时了？
继续收集良方，继续测试

Answer 12

1. 插入新的user
insert into paihang(uid,jifen)
select u.uid, u.meili
from users u left join paihang p
  on u.uid=p.uid 
where p.uid is null
;
或(应该是第一个快点)
insert into paihang(uid,jifen)
select uid, meili
from users 
where uid not in (select uid from paihang )
;2.更改积分
update paihang p, users u
set p.jifen=u.meili
where p.uid=u.uid
;
3.更新排名
set @c=0
;
update paihang
set yspaiming=paiming, paiming=@c:=@c+1 
order by jifen desc
;你试一下, 如果有速度问题, 贴库结构和索引上来,以及explain的结果

Answer 13

字段越多，表越大，做任何操作的时间都会越长！
但是根据你的描述，单表查询不做更新要花费38s，比left join花费的还要多，这个不可能，你把你的代码贴出来看看就知道问题在哪里了。

Answer 14

也是有可能的, 要看left join里引用的字段, 
楼主的user表估计是有很多的字段..... 这个还是要看表结构
另外,楼主,
看你的code里是把meili更新到排行的jifen里的,
但是user里怎么也有一个jifen??? 

Answer 15

对，user表保存的信息比较多对sql 做了验证，SQL效率应该是没有问题的，在PHPMYADMIN下运行，
SELECT player.id,player.meili,ph.paiming FROM player LEFT JOIN meilipaihang ph ON ph.uid = player.id ORDER BY player.meili DESC
//保存在两张表中，（注：当meilipaihang表中未空是，即没有任何记录时，200,243 总计, 查询花费 0.3445 秒，当meilipaihang不为空，有20w数据时，在7秒左右0）SELECT player.id,player.meili,paihang FROM player ORDER BY player.meili DESC
//和并表   200,243 总计, 查询花费 0.2984 秒
其中 player.id 为主键，meilipaihang.uid 唯一索引

Answer 16

如果放在MYSQL命令下或者PHPMYADMIN等mysql工具下运行效率相当可观，但怎么才能放的程序中执行？？
由于set @c=0;有分号，表示这句执行完毕，下面的 paiming=@c:=@c+1  就找不到值了，即mysql报错
 

Answer 17

还有一个比较变态的写法:
类似
update test2, (select @a:=1) x 
set  num=@a:=@a+1
;
偷个懒,你自己照这个例子改一下