[데브피아 컬럼] DB 튜닝 이야기 - (2회) INDEX 과연 달면 빠른가?

출처: http://www.devpia.com/DevStudy/Lecture/OffLineDetail.aspx?nSemiID=1431&lectype=evt

데브피아에 DB 튜닝관련 컬럼 연재가 2회차가 올라왔습니다. ^^

지난회에는 인덱스를 생성했으나 컬럼의 가공, 내부적 변형, null과의 비교, 부정형 조건등으로 인하여 인덱스를 사용하지 못하는 경우를 보았다. 그럼 과연 인덱스를 타기만 하면 무조건 빠를까? 불행하게도 그렇지 않다. 대부분의 경우는 빠르겠지만 경우에 따라서는 인덱스를 타기 때문에 느려지는 경우가 많이 발생한다. EMPLOYEE에 성별 컬럼을 추가하고 절반정도 되게 남성과 여성을아래와 같은 분포도로 넣었다. SELECT GENDER , COUNT(*) CNT, ROUND(COUNT(*) / 15132,3)*100 RATIO FROM EMPLOYEES GROUP BY GENDER; G ---- F M CNT -------- 7590 7542 RATIO --------- 50.2 49.8   그리고 아래와 같은 INDEX를 생성하였다. CREATE INDEX IDX_GENDER ON EMPLOYEES(GENDER); 그러면 이제 2개의 SQL의 수행 결과를 보자. 첫번째 경우는 INDEX를 탄경우다. 아래와 같이 HINT를 주어서 OPTIMIZER의 PLAN을 고정하였다. /*+ INDEX(E IDX_GENDER) */ 는 E라는 별명의 테이블에 IDX_GENDER이라는 INDEX를 이용하여 테이블에 데이터를 가져오라는 뜻이다. SELECT /*+ INDEX( E IDX_GENDER) */ GENDER, COUNT(*), AVG(SALARY) FROM EMPLOYEES E WHERE GENDER = 'M' GROUP BY GENDER Call ---------- Parse Execute Fetch ---------- Total Count ---------- 1 1 2 ---------- 4 CPU Time ---------- 0.000 0.000 0.030 ---------- 0.030 Elapsed Time ------------- 0.000 0.000 0.026 ------------- 0.026 Disk ---------- 0 0 0 ---------- 0 Query ---------- 0 0 127 ---------- 127 Current ---------- 0 0 0 ---------- 0 Rows ---------- 0 0 1 ---------- 1 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user: SCOTT (ID=54) Rows ---------- 0 1 7542 7542 Row Source Operation --------------------------------------------------- STATEMENT SORT GROUP BY NOSORT (cr=127 pr=0 pw=0 time=25567 us) TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=127 pr=0 pw=0 time=15176 us) INDEX RANGE SCAN IDX_GENDER (cr=16 pr=0 pw=0 time=61 us)OF IDX_GENDER (NONUNIQUE) 두번째 경우는 INDEX를 타지 않은 경우다. 아래와 같이 HINT를 주어서 OPTIMIZER의 PLAN을 고정하였다. /*+ FULL(E) */ 는 E라는 별명의 테이블을 할 때 테이블 전체를 다 읽어서 처리(FULL TABLE SCAN)하라는 뜻이다. SELECT /*+ FULL(E) */ GENDER, COUNT(*), AVG(SALARY) FROM EMPLOYEES E WHERE GENDER = 'M' GROUP BY GENDER Call ---------- Parse Execute Fetch ---------- Total Count ---------- 1 1 2 ---------- 4 CPU Time ---------- 0.000 0.000 0.010 ---------- 0.010 Elapsed Time ------------- 0.000 0.000 0.014 ------------- 0.015 Disk ---------- 0 0 0 ---------- 0 Query ---------- 0 0 115 ---------- 115 Current ---------- 0 0 0 ---------- 0 Rows ---------- 0 0 1 ---------- 1 Misses in library cache during parse: 0 Optimizer goal: RULE Parsing user: SCOTT (ID=54) Rows ---------- 0 1 7542 Row Source Operation --------------------------------------------------- STATEMENT SORT GROUP BY NOSORT (cr=115 pr=0 pw=0 time=14410 us) TABLE ACCESS FULL EMPLOYEES (cr=115 pr=0 pw=0 time=181 us) 인덱스를 탄 경우는 0.03초가 걸렸고 인덱스를 타지 않은경우는 0.01초가 걸렸다. 인덱스를 타서 3배나 더 느려졌다!. 이것이 가능한가? 그러면 왜 인덱스를 탔는데도 시간이 더 걸리는 것인가? 이유는 Disk io에 있다. 일반적으로 Full table scan을 할때는 한번에 1개의 block씩 i/o를 하지 않고 muti block를 한번에 요구한다. 그 이유는 읽을 양이 많다고 미리 가정하기 때문이다. 따라서 Oracle의 경우 db_file_multiblock_read_count라는 파라미터가 있고 일반적으로 8또는 16을 설정한다. 만약 16이라면 한번 I/O에 16개의 BLOCK을 읽어오게 되는것이다. 따라서 EMPLOYEE 115BLOCK을 한번에 16개씩 읽으면 약 8번의 IO 요청으로 완료가 된다. 그러나 Index를 사용할 경우 index를 사용하면 기본적으로 대량의 io가 발생할 것이라고 가정하지 않기 때문에 1개의 block씩 i/o를 하게 된다. 따라서 16개의 index block과 115개의 block의 물리적 i/o가 발생한다 16+115를 하면 총 131번의 물리적 io가 발생하게 되는것이다. 논리적으로는 인덱스 1개보고 테이블 1개 블락을 읽고를 7542번+1번을 하게되는 것이다. 마지막 1번은 다음에 더 이상 ‘M’이 없는지 확인하기위해서 1번 더 읽는다. 어째든 인덱스를 사용되는 것이 더욱 느리다는 것이다. 현지 EMPLOYEES 테이블은 15132건이다. 만약 이 데이터가 많아진다면 차이는 점점 더 많이 날것이다. 그러면 어느정도은 INDEX를 타고 어느 정도는 Full Table Scan이 오히려 더 좋은가? 시스템의 성능또는 데이터의 양에 따라 차이가 조금씩있으나 일반적인 기준은 있다. 아래 TYPE이라는 컬럼에 A-F까지 값을 가지고 있으며 가각 49.8%부터 0.9%까지의 분포를 가지는 값들을 가지고 있다. SELECT TYPE , COUNT(*) CNT, ROUND(COUNT(*) / 15132,3)*100 RATIO FROM EMPLOYEES GROUP BY TYPE; T -------- A B C D E F CNT -------- 7542 4533 1515 799 603 140 RATIO --------- 49.8 30 10 5.3 4 0.9 아래는 예제로 사용되었던 SQL과 그에 따른 응답시간을 비교한 표이다. FTS(Full Table Scan) SQL SELECT /*+ FULL(E) */ GENDER, COUNT(*), AVG(SALARY) FROM EMPLOYEES E WHERE TYPE = 'F' GROUP BY GENDER; Rows -------- 0 1 140 Row Source Operation --------------------------------------------------- STATEMENT 1 HASH GROUP BY (cr=115 pr=0 pw=0 time=6925 us) 140 TABLE ACCESS FULL EMPLOYEES (cr=115 pr=0 pw=0 time=10323 us) INDEX(Index Scan) SQL SELECT /*+ INDEX(E IDX_TYPE) */ GENDER, COUNT(*), AVG(SALARY) FROM EMPLOYEES E WHERE TYPE = 'F' GROUP BY GENDER; Rows -------- 0 1 140 140 Row Source Operation --------------------------------------------------- STATEMENT HASH GROUP BY (cr=87 pr=0 pw=0 time=3227 us) 140 TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=87 pr=0 pw=0 time=2956 us) INDEX RANGE SCAN IDX_TYPE (cr=2 pr=0 pw=0 time=539 us)OF IDX_TYPE (NONUNIQUE)   결과를 보자 약 15132건의 테이블을 ACCESS하는데 10%이상이 되는 경우는 FTS이 더빠르고 10%이하인 경우는 INDEX를 타는 경우가 더 빠르다. 10% 미만일때는 INDEX를 타고 10%가 넘으면 인덱스를 안타게 할수 있는가? 결론적으로 가능하다. 아래 SQL을 보자 2개의 SQL을 UNION ALL로 결합하고 비교조건을(굵은색)을 줌으로서 논리적 비교를 통해서 실제로 FTS의 조건은 타지 않고 INDEX쪽만 수행하도록 하였다. 아래 수행결과를 보면 INDEX를 타는 곳에서만 ROWS가 나온 것을 알수있다. SELECT /*+ FULL(E) */ GENDER, COUNT(*), AVG(SALARY) FROM EMPLOYEES E WHERE TYPE = 'F'    AND TYPE IN ( 'A','B','C') GROUP BY GENDER UNION ALL SELECT /*+ INDEX(E IDX_TYPE) */ GENDER, COUNT(*), AVG(SALARY) FROM EMPLOYEES E WHERE TYPE = 'F'    AND TYPE IN ( 'D','E','F') GROUP BY GENDER; Rows -------- 0 1 0 0 0 1 140 140 Row Source Operation --------------------------------------------------- STATEMENT UNION-ALL (cr=87 pr=0 pw=0 time=2743 us) HASH GROUP BY (cr=0 pr=0 pw=0 time=235 us) FILTER (cr=0 pr=0 pw=0 time=8 us) TABLE ACCESS FULL EMPLOYEES (cr=0 pr=0 pw=0 time=0 us) HASH GROUP BY (cr=87 pr=0 pw=0 time=2477 us) TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=87 pr=0 pw=0 time=2328 us) INDEX RANGE SCAN IDX_TYPE (cr=2 pr=0 pw=0 time=205 us)OF IDX_TYPE (NONUNIQUE) 그렇나 이렇게 프로그램을 한다면 프로그램이 힘들어 질것이다. 따라서 현재 Optimizer들은 실제 값에 따라서 FTS이 유리한지 아니면 INDEX SCAN이 유지한지 값을 보고 PLAN이 바뀌도록 되어있다. 물론 이를 위해서는 컬럼에 대한 분포도 정보를 DB가 가지고 있어야 한다. 이는 ANALYZER를 통해서 DB가 취득하게 된다. 그럼 이제 간단하다 10%이상에 데이터를 INDEX를 타면 속도가 오히려 느려지므로 10%이하의 데이터를 찾고자 할 때만 INDEX를 생성하면 간단하게 해결될것이다! 그러나 과연 그럴까? INDEX를 생성하면 일반적으로 SELECT의 속도는 향상을 보지만 반대로 INSERT,UPDATE,DELETE는 저하되게 된다. 위에 도표를 보면 INDEX의 숫자가 증가함에 따라서 속도가 느려지는 것을 알수 있다. 즉, 인덱스의 생성으로 SELECT는 빨라질수도 있고 느려질수도 있다. 그러나 DML(INSERT,UPDATE,DELETE)는 항상 느려진다. 따라서 INDEX를 무작정 다는 것은 DML 성능을 느리게 한다. 그럼 어떤 기준으로 인덱스를 생성할지 말지를 결정할 것인가? 아래 2가지 시간을 고령하자. 이익시간 = INDEX생성으로 빨라진시간 * 수행 QUERY수 비용시간 = INDEX생성으로 느려진 INSERT시간 * INSERT수행횟수                 + INDEX생성으로 느려진 UPDATE시간 * UPDATE수행횟수                 + INDEX생성으로 느려진 DELETE시간 * DELETE수행횟수 이익시간이 비용시간 보다 크다면 인덱스를 생성하는 것이 좋을 것이다. 반대로 이익시간 < 비용시간 보다 작다면 인덱스를 만드는 것이 손해보는 경우다. 이런 경우라면 인덱스를 만들면 안되는 것이 유리하다 할수 있다. 그러나 반드시 그런 것은 아니다. 그것은 수행 시간을 고려해야한다. 낮에일반적으로 QUERY가 빠르게 수행되고 주로 밤에 BATCH에서 DML이 수행되고 있다고 가정할 때 DML이 더 느려지는 것이 그렇게 문제가 되지 않는다면 INDEX를 생성할 수도 있는것이다. 어디까지나 Application 사용의 관점에서 효율적인 것을 찾는 것이 중요하다. 인덱스를 사용하여 손해보는 경우는 아래와 같다. 같은 값이 많은 컬럼 INDEX를 타면 10%이상 선택하는 경우 예) 남녀성별등.. 조회보다 DML의 부담이 큰 경우 이익시간 < 비용시간 경우 그러나 이때도 사용환경을 고려하여 인덱스를 생성할 수 있다. 데이터가 적은 테이블 일반적으로 db_file_multiblock_read_count보다 적은 수의 BLOCK을 가진테이블은 INDEX를 타지 않는 것이 빠르다. 그러나 integrity를 위해서 PK와 FK는 달아야 한다. 이번 회에는 INDEX를 타서 오히려 손해를 보는 경우와 그를 방지하는 방법을 보았다. 인덱스를 생성한다고 인덱스를 반드시 타는 것도 아니며 또한 인덱스를 탄다고 반드시 빠른 것도 아니다. 따라서 INDEX의 생성과사용 전략은 그렇게 쉬운 문제가 아니다. 빠른 시스템을 위해서는 고려할 점이 많다는 것이다. 물론 필자가 다룬 것은 테이블중에 일반테이블과 일반 INDEX에 대해서만 다루었기때문에 PARTITION이나 BITMAP같은 다른 구조의 인덱스에서는 다른 특성을 가진다. 그러나 이러한 것은 대용량이나 DW의 특수한 용도에 사용되므로 대부분의 경우에는 고려하지 않아도 크게 문제되지 않을 것이다. 못조록 필자의 글이 독자들에게 도움이되는 길이기를 바라면서 이글을 마무리하고자 한다. 마지막으로 당부 드리고 싶은 말은 SQL을 작성하시고 항상 PLAN을 확인하시기 바랍니다. PLAN에 익숙해지고 OPTIMIZER를 이해할 때 비로소 OPTIMIZER가 여러분의 심부름꾼이 될수 있기때문이다.