MySQL 排名统计(常用功能函数)

select actor_id,@curr_cnt:=cnt as cnt ,
@rank:=IF(@prev_cnt!=@curr_cnt,@rank+1,@rank) as rank,
@prev_cnt:=@curr_cnt as dummy
from(
select actor_id,count(*) as cnt from film_actor 
group by actor_id ORDER BY cnt desc
) as der,
(select @curr_cnt:=0,@prev_cnt:=0,@rank:=0 ) as t -- 实际上相当于赋值操作

-- 排名，考虑并列情况
SELECT @rownum:=@rownum+1 AS rownum,IF(@total = cnt ,@rank ,@rank :=@rownum) AS rank ,
@total := cnt as total1, der.* FROM
(
select actor_id,count(*) as cnt from film_actor 
group by actor_id ORDER BY cnt desc
) as der,
(    SELECT    @rank := 0 ,@rownum := 0 ,@total := NULL    ) as t

 

-- c100w 目标表  c10w 源表    对应关系 c10w.id=c100w.id
update c100w,c10w set c100w.grender=c10w.grender where c10w.id=c100w.id   -- 10w 2.083s

 

常见性能优化场景：

1、多表连接查询很慢，而且不论结果多少，查询耗时差不多。可能原因缺少索引导致全表扫码 ，考虑索引优化

查询缺少索引时，减少表连接看耗时是否减少，再排查连接表是否有索引。