mysql order by 정렬 빼도 될까? 1

일하다보면 사업부서의 요청으로 통계정보를 추출해달라는 요청을 많이 받는다.
DBA가 없는 회사는 개발자들이 직접 작업하는 경우가 많고 이게 업무에 생각보다 많은 부분을 차지하고 있다.

 

SAS나 SAP이 있는 회사라면 그걸 쓰면 될텐데.. 이건 너무 비싸고 그렇다고
별도의 데이터 마트나 샌드박스가 구축은 시간이 오래걸리고 이것도 생각보다 비용이 많이 들어간다.
그리고 결정적으로 SAS 도입을 검토한다고 해서 회의에 참여한 적이 있는데 음.. SAS쪽  사업담당자의

시연을 보면서 들던 생각은  저 정도를 쓰려면 MS의 ACCESS 정도는 문제없이 사용할 정도의 숙련도를 가진 사업담당자야할 거 같고, 교육을 받더라도 이해하고 제대로 사용하기에는 많은 시간이 걸릴 거 같다는 생각이 들었다.

 

일단 여기도 그렇지만 결국 EXCEL이다.
엑셀은 row수가 98만 건이 넘어가면 쉘을 쪼개던지 아니면 파일을 분리해야 한다.
보통은 피벗이나 엑셀함수로 원하는 정보를 만들텐데.. 데이터가 커지면 무한정 루프를 돌면서 업무지연이 발생한다는 것을 알게되었다.

 

최대한 요구사항에 맞게 데이터를 가공해서 전달하고 Excel에서 쓰는 함수를 최소화할 수 있게 전달해 주길 원했고
초기 포멧의 row seq 정렬에 맞게 추출해서 주기를 원했다.
결국 Excel이나 DB나 순서정렬이 문제였고, 거기에 맞게 가공하기 위한 작업을 하려면
DB도 대용량 데이터의 JOIN과 order by를 사용해야하고 filesort와 데이터 사이즈가 커지면 temparory memory를 쓸 수 밖에 없다.

 

다른 것보다 filesort를 쓴다는 것은 옵티마이져가 스캔한 것을 다시 한번 메모리에 올리고 재정렬을 해야하므로 부하가 많이 가는 작업이고, 보통은 DB에 부하를 주지 않게하기위해 out file로 처리해서 데이터를 재가공해서 EXCEL로 전달했다. order by 를 사용하지 않고 정렬이 가능한지 여부를 구글링 해보았는데 대부분 답은 정합성을 보장할 수 없다는 얘기였고, 정말 사용할 수 없는지 테스트 해보기로 했다.

 

1) MyISAM 테이블 엔진 테스트
일단 테이블 생성하고 DB툴을 이용하여 엑셀이나 CSV 파일을 DB에 import 한다.

 

CREATE TABLE `TMP_LOAD_20200619` (
  `SEQ_NO` int(10) unsigned NOT NULL COMMENT '시퀀스',
  `ENTER_NO` varchar(12) NOT NULL COMMENT '가입 시퀀스',
  PRIMARY KEY (`SEQ_NO`),
  UNIQUE KEY IDX_LOAD_20200619_01 (`ENTER_NO`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


select * from TMP_LOAD_20200619;
1	500053900208
2	500053900209
3	500053900210
4	500053900218
5	500053900219
6	500053900220
7	500053900214
8	500053900215
9	500053900216
10	500053900217
...

//50만건 정도 올려보았는데 순서대로 insert 되는 것을 확인했다.
//select 역시 순서대로 나온다.

delete from TMP_LOAD_20200619
where SEQ_NO = 3;
commit;
// 시퀀스 3을 삭제해 보았다.

select * from TMP_LOAD_20200619;
1	500053900208
2	500053900209
4	500053900218
5	500053900219
6	500053900220
7	500053900214
8	500053900215
9	500053900216
10	500053900217
11	500053900228
...

insert into TMP_LOAD_20200619
VALUES (500001,'500053904383');
commit;
// 500001번째 시퀀스를 가진 데이터를 추가로 넣어보았다.

select * from TMP_LOAD_20200619;

1	500053900208
2	500053900209
500001	500053904383
4	500053900218
5	500053900219
6	500053900220
7	500053900214
8	500053900215
9	500053900216
10	500053900217
...

MyISAM 엔진의 특성이다.
초기 데이터에서 삭제하면 select하면 보이진 않지만 빈공간은 사라지지 않고 존재하고 새로 입력한 데이터가 거기에 들어간다. 이거를 다시 생각해보면 MyISAM은 한번 입력한 데이터의 정렬은 유지되며 신규로 update나 delete가 일어나지 않는이상 ASC의 정렬은 유지된다고 판단할 수 있다.

다시말하면 초기데이터가 유지된다면 order by 정렬을 사용하지 않아도 된다는 얘기다.

 

실제로 TMP_LOAD_20200619 테이블을 드라이빙 테이블로 Left Join을 여러개 수행하고 where 조건절로 끊어서 데이터를 가공해서 최종 order by 정렬을 사용한 것과 사용하지 않은 것을 beyond compare로 비교해 보았고
50만건이 100% 일치함을 확인했다.

 

MyISAM을 사용시 초기데이터가 바뀌지 않는다면 order by 정렬을 빼도 될거 같다.

 

2) InnoDB 테이블엔진 테스트

 

CREATE TABLE `TMP_LOAD_20200619` (
  `SEQ_NO` int(10) unsigned NOT NULL COMMENT '시퀀스',
  `ENTER_NO` varchar(12) NOT NULL COMMENT '가입 시퀀스',
  PRIMARY KEY (`SEQ_NO`),
  UNIQUE KEY IDX_LOAD_20200619_01 (`ENTER_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


1	500053900208
2	500053900209
3	500053900210
21	500053900211
22	500053900212
23	500053900213
7	500053900214
8	500053900215
9	500053900216
10	500053900217
..
// 원하는 순서가 아니다.. 뭔가 다르다.

 

 

InnoDB는 데이터 정렬하는 방법이 다르다.
테이블의 데이터를 PK값으로 정렬되어 저장하고 pk의 값의 정렬은

실제 데이터에서 보듯이 내가 생각하는 seq_no 순서대로의 순서가 아니고 clustered index로 저장하여 정렬한다.

 

#클러스터링 인덱스(clustered index)란?
여러개의 인덱스를 하나로 묶는다는 뜻으로 테이블의 PK에 대해서만 적용되는 내용이다.

즉 PK값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다.
중요한 것은 PK값에 의해 레코드의 저장 위치가 결정된다는 것이고, 또한 프라이머리 키값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미한다.

 

#결론

innodb의 PK정렬이 order by 비용을 줄여 빠르다고 하는데, 실제 테스트해 본바로는 select는 대용량 일수록 MyISAM의 순서정렬을 사용하지 않은 쿼리가 훨씬 빨랐다.

데이터 추출작업이 필요할 때 order by를 뺄수 있는 MyISAM 엔진을 적극활용하자.

끝~