Mysql ( Order by Rand() 튜닝 방법 )

Order by Rand() 구문 튜닝 방법





* 이슈

-> Order by Rand 구문은 테이블의 건수가 증가할수록 Sorting 부하 증가로 DB 에 지속적인 부하를 가중시킬 수 있음.  튜닝이 필요함. 보통 게임 서비스에서 친구 추천이나,
PVP 대상을 찾아 낼때 효과적일 수있음.



* 튜닝 전 SQL

[ 원본 쿼리 ]

SELECT id FROM random_test      
 WHERE level >= ? AND level <= ?          
 AND last_time >= ?        
 AND id != ?  
 AND slotindex != ?
order by rand()
limit 100;



[ AS-IS 수행 시간 ]
평균 0.41 ( Cpu 사용율 높음)





* 튜닝 후 SQL

->  Order by Rand 를 사용하나, 미리 500건으로 축소하여 사용하므로 Sorting 최소화



[ 튜닝 쿼리 ]

SELECT id ,level,last_time,slotindex
FROM ( SELECT id ,level,last_time,slotindex
        FROM ( SELECT min_num + (max_num - min_num  + 1 - 500) * RAND() AS start
               FROM  (select
                            (select id
                               from  random_test t use index for order by (idx_id)
                               WHERE level >= ? AND level <= ?      
                               AND last_time >= ?        
                               AND id != ?    
                               AND slotindex != ? order by id desc limit 1 ) max_num ,
                             (select id
                                from random_test t use index for order by (idx_id)
                                WHERE level >= ? AND level <= ?            
                                AND last_time >= ?        
                                AND id != ?    
                                AND slotindex != ? order by id asc limit 1 ) min_num
                              )NUM_CHECK
                        )AS init
                JOIN random_test y
                WHERE  y.id > init.start
                and level >= ? AND level <= ?        
                AND last_time >= ?        
                AND id != ?
                AND slotindex != ?
                ORDER BY y.id
                    LIMIT 500        
       ) z  ORDER BY RAND()
LIMIT 100;        



[TO-BE 수행 시간 ]

평균 0.01초 ( Cpu 사용율 낮음)





-> 2차 튜닝 예상건

  위는 1차 튜닝이고, 만약 테스트 시에 위 쿼리으로 인해 부하가 커진다면 , 2차 방안으로 위 쿼리에서 Order by RAND 를 삭제 하면 Sorting 부하를 모두 제거 할 수 있습니다.

  다만, 위 쿼리는 500건 이내에서 Random 하게 친구 목록 리스트를 변경하지만 Order by Rand 를 삭제 할 경우 500 건 단위로는 목록이 변할 수 있지만
  500건 이내에서는 계속 동일한 순서로 친구목록이 나오게 되며, 비슷한 리스트가 자주 나올수있습니다.