데이터베이스 데이터 삽입 후 순위 발췌 구현 및 테스트.

RDB상에서 효율적으로 레코드를 한건 삽입하고 바로 이 레코드의 순위를 알기위해 실험한다.

다음의 테이블을 만들었다. 20개의 잉여 컬럼은 심심하니 그냥 넣었다.

 

 

이제 프로시저로 새로운 데이터를 자동으로 생성되게 해보자. 일단 시간이 굉장히 오래걸리기 때문에 백만건을 투입해본다.

프로시저는 다음과 같다.

 

 

현재 똥컴 시스템은 i5-4670 3.4Ghz / 8g 재원으로, 들어가는데 한참걸린다. 랜덤함수때문에 그런것인가.

백만건 약 14분 소요. 50MB 속도

(480만건 per 1hr)

 

일단 번외로 count(*)의 MyISAM 과 InnoDB간 속도차이를 측정해본다. 전자는 아예 행정보를 들고있고 InnoDB는 근사치를 내어준다.

 아래와같이, InnoDB는 근사치를 들고있다.

무거운 엔진인 InnoDB의 경우 통계정보로 구성된 근사치를 내어준다고 하는데, 백만건 대상으로 count(*)에 0.5초가 걸린한편, MyISAM은 따로 이를 세는것이 아니기 때문에 0.02초가 걸렸다. InnoDB의 경우 선형적으로 count(*) 속도가 늘어난다.

즉, 전체 건수를 발췌하거나 단순히 대용량 테이블에서 SELECT만 할 경우에는 MyISAM이 나은 선택이며, 이 외의 경우에는 모두 InnoDB가 나은 선택으로 보인다.

이제 RANK를 하나하나 행마다 줘보기로 한다.

만건, 십만건, 백만건, 천만건 순으로 해보기로한다.

 

 

 

InnoDB의 경우,

일단 만건, 0.08초.

십만건, 0.3초

백만건, 3초

천만건, 33초

결과적으로는 정렬을 하는것에 따른 시간으로, 대충 선형적인 시간이 걸린다. 내부적으로 메모리버퍼와 파일을 이용한 병합정렬로 구현이 되어있는듯 하다.

 

MyISAM의 경우,

백만건, 3초

천만건, 36초

오차범위 안에서 같은 속도.

 

이제, 인덱스를 걸어볼수도 있다. (인덱스 설계시 주의 http://egloos.zum.com/tiger5net/v/5660848)

 

이제 테이블에 소트를 걸어보면 어떨까? MyISAM 혹은 인덱스가 걸리지않은 InnoDB의 경우, alter table 명령어로 쉽게 소트를 할수 있다.

 

 

다만, InnoDB상에서 인덱스를 건다는 것은 그 자체의 인덱스가 내부적으로 소트된다는 뜻이므로,

ORDER BY ignored as there is a user-defined clustered   

index in the table XXXXX

와 같은 에러 메시지를 보게 될것이다.

순위를 구하는 SQL은 다음과 같이 쉽게 해볼수가 있는데, 천만건에서는 한참 돌아간다. 1분.

 

 

 

일단 촉촉한 초코칩을 먹다 생각해낸 방도는

score에 인덱스를 건다. InnoDB는 어차피 인덱스를 거는 순간 알아서 정렬이 어딘가에 되어있다고 하므로, 이 인덱스를 가지고 나보다 score가 높은 놈들의 갯수를 센다. 천만건대상 4초가 걸린다. 그리고 나머지 정보는 그냥 가져다 붙인다.

 

 

 

이는, 내가 방금 넣은 row에대한 점수와 id를 알고있다는 전제하에 가능하다. 즉, 새로운 레코드가 들어왔을때 적당히 순위를 내줄때 써먹을 수 있어보인다.