order by rand 쿼리 튜닝

MySQL에서 랜덤 row를 뽑기 위해 order by rand 를 많이 사용하는데 sort 에 참여하는 row 수가 많아지면 
부담이 될 수 밖에 없다.  

 

쿼리는 아래 붉은 색 쿼리이며

Where 조건을 만족하는 min(num),max(num) 을 구하고 이 범위 ( min(num) <=  rand <= max(num) ) 내에서 random 으로 num 을 뽑아서

이 num 과 가장 근접한 where 조건을 만족시키는 row를 return 하는 방식으로 진행

Min, max 구하는 쿼리가 조금 긴데.. 이 부분은 mysql optimizer 가 인덱스를 통한 min,max 최적화를 잘 수행하지 못하여 가장 성능이 잘 나오는 쿼리로 작성하여 조금 길어짐

 

 

 

최초 쿼리 >

 

SELECT t.*
               FROM team_common as t
               WHERE t.type = 'AI'
                       and t.level = 1

               ORDER BY rand()
               LIMIT 1;
기존 변경 >

select t.* from
( select num from team_common where type='AI' and level=1 order by rand() limit 1 ) a
 inner  join team_common t
 where t.num=a.num;

신규 변경 >

case1)

select tc.*

from

(select cast( (max_num-min_num) * rand() as signed ) + min_num as rand_num

             from

             (

                           select

                           (select num from team_common t use index for order by (level) where t.type = 'AI' and t.level = 1 order by level,type,num desc limit 1) as max_num,

                           (select num from team_common t use index for order by (level) where t.type = 'AI' and t.level = 1 order by level,type,num asc limit 1) as min_num

             ) max_min_t ) t_rand

STRAIGHT_JOIN

team_common tc

where

tc.level = 1 and

tc.type  = 'AI' and

tc.num >= t_rand.rand_num

order by num limit 1;

 

case2)

select tc.*

from

(select cast( (max_num-min_num) * rand() as signed ) + min_num as rand_num

             from

             (

                           select

                           (select num from team_common t use index for order by (level) where t.type = 'AI' and t.level = 1 and t.name not in ('헬로키티','다나와') order by level,type,num desc limit 1) as max_num,

                           (select num from team_common t use index for order by (level) where t.type = 'AI' and t.level = 1 and t.name not in ('헬로키티','다나와') order by level,type,num asc limit 1) as min_num

             ) max_min_t ) t_rand

STRAIGHT_JOIN

team_common tc

where

tc.level = 1 and

tc.type  = 'AI' and

tc.name not in ('헬로키티','다나와') and

tc.num >= t_rand.rand_num

order by num limit 1;

http://ndba.egloos.com/3440696