[mysql] 조회, Index냐 Full Scan이냐

 

데이터가 제법 쌓여있는 테이블이 있는데, 거기를 조회할때마다 가급적이면 인덱스를 쓰겠금 유도한다.

그런데 최근 SQLP 공부하면서 인덱스를 무조건 태우는 것이 좋지 않암을 많이 깨닫는다.

 

그럼 인덱스를 사용할때와 아닐때의 차이는 어느 기준으로 분류해야할까를 찾아보고 정리해보앗다.

진행은 MySQL로 했으며 해당테이블은 약 2억개의 데이터가 탑재되어 있는 거래정보 테이블이며, 고객테이블과 조인하는 고정이었다.

 

 

특정 기간을 조회하는 일이 빈번해서 생성일자를 기반한 인덱스가 생성되어있는 상태다. 특정기간 데이터를 조회하려는 것이었고, 조회조건에 생성일자 범위를 넣어 조회했다. 그런데 다음과 같이 인덱스가 아닌 Full scan(ALL)이 되고 있었다. 실행결과는 약 15분정도 소요됐다.

 

 

이번에는 힌트를 이용해 인덱스를 사용하도록 했다. 단순 설정으로는 인덱스를 사용하지 않아 강제로 사용하도록(force) 유도했다. 그리고 나서 실행계획을 보니 row수가 50%정도 절감된 1억개 정도가 조회되었다. 속도도 빨리지겠거니 하고 기대하며 실행해봤지만 수행시간은 약 30분이 걸렸다. 기대한 것보다 오래걸려 왜그런지 찾아봤다.

 

# 인덱스란 소수의 데이터를 조회할 때 사용하는 것

인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다. 온라인 트랜잭션 처리(OLTP) 시스템에서는 소량 데이터를 주로 검색하므로 인덱스 튜닝이 무엇보다 중요하다 - <친절한 SQL 튜닝>

 

인덱스를 튜닝한다는 것은 테이블 엑세스를 줄이는 것이다.인덱스 스캔 후 테이블 레코드를 랜덤 엑세스 하는데, 이 랜덤 액세스가 부하를 일으킨다는 것이다. 그렇다면 왜 랜덤엑세스는 부하를 일으킬까?

 

랜덤엑세스는 특정 장소에 이동하는 것이다. 인덱스를 이용해서 장소에 접근하고, 장소에 저장되어 있는 데이터를 가져오는 것이다. 다음 그림을 보면 이해하는데 도움이 된다.

 

 

 

데이터가 적을 경우에는 이 방식이 효과적이다. 주소가 적혀있는 장소에만 가면 되기 때문에 불필요한 낭비가 없다. 하지만 이 구역을 전체 다 뒤져봐야 한다면? 인덱스 -> I/O 로 계속 반복접근을 해야한다.

 

하지만 Full Scan일 경우는 어떨까? 이때는 특정 주소에 접근하는 방식이 아니라 블록별로 모두 순차적으로 접근한다. 메모리에 적재해야할 것이 많아지긴 하지만 순차적으로 접근하기 때문에 접근비용이 감소한다. 강남에 5개 블록이 있는데 인덱스를 사용하면 가고 싶은 집을 한번씩 들리면서 메모지에 기록하는 꼴인데, 이때 오고가는 비용이 대폭 커지는데 반해 순차접근은 블록, 순서별로 모든 집을 다 들리면서 메모지에 기록하는 꼴이다.

 

작동방식도 달라진다. 인덱스를 사용(Index Range Sca)할 경우 Single Block I/O 방식으로 블록을 읽는다. 캐시에서 블록을 찾지 못하면 매번 레코드하나를 가져오는 동안 잠을자는 I/O 매커니즘이다. 그에반해 Full Scan(정확히는 시퀀셜 엑세스)은 Multi block I/O 방식을 쓴다. 때문에 동시간대 처리하는 양도 크다.

 

그렇다면 Full Scan과 Index를 사용하는 기준점은 테이블 엑세스의 어느정도 일까?

 

 

 

# 인덱스 손익분기점

 

보통 손익분기점은 5~20%의 낮은 수준으로 결정된다. 이 결정기준은 CF(Clustering Factor) 에 따라 많이 달라지는데 CF가 나쁘면 손익분기점은 5%미만에서 결정되며, 심할때는 1%미만으로 낮아진다. 반대로 좋을때는 손익분기점이 90%까지 향상한다.

 

CF(Clustering Factor) 란?

특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미. 잘 정리되어 있다면 인덱스 검색효율이 매우 좋다.

 

이전 조회테이블에 검색조건 날짜간극을 좀더 줄여봤다. 그랬더니 어느순간 Full Scan 대신 인덱스를 사용하기 시작했다. 그 구간이 바로 20%정도에 해당하는 약 4,000만 건을 조회할 때 였다.

 

 

key 를 보면 index를 사용하였고, rows는 3,500만 건에 달한다. 당연한 말이겠지만 전체 조회수가 줄어드니 속도가 좋아진건 당연하다.

 

 

인덱스는 반드시 적은양의 데이터를 조회할 때 유리하다. 그 외에는 Full Scan이 더 유리하다. 인덱스는 랜덤엑세스이고 Full Scan은 시퀀셜엑세스(순차접근)이기 때문이다. 이 사실만 잘 기억하고 있다면 불필요한 인덱스를 태우려고 노력하는 잘못된 행동을 하지 않을 것이다.(사실 옵티마이저가 왠만하면 더 똑똑하다)

 

 

 

참조:

책 - <친절한 SQL 튜닝>

http://wiki.gurubee.net/pages/viewpage.action?pageId=26742992

테이블 Random 액세스 부하 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식창고!