전문가칼럼

DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.

쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (상)

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2014-09-19 00:00
조회
18581




◎ 연재기사 ◎


물탱크 구조로 알아본 오라클의 블록 옵션 ‘PCTFREE와 PCTUSED’


이산가족 찾기 생방송을 통해 배우는 DB 원리


개발자에게 맞는 DB 공부방법 찾기: 물리적 분류와 논리적 분류 그리고 인덱스


데이터베이스 인덱스의 오해와 진실


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (상)


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (하)


누구도 알려주지 않았던 ‘오라클 인덱스 생성도’의 비밀


누구도 알려주지 않았던 ‘오라클 쿼리 작성의 비법’


퀴리 최적화 및 튜닝을 위한 오라클 공정쿼리 작성법


만능 쿼리와 한 방 쿼리


오라클 옵티마이저 ‘CBO와 RBO’ 이해하기


재미있는 DB 이야기 ‘60갑자와 쿼리’


그림으로 배우는 ‘오라클 조인의 방식’ 이야기


반드시 알아야 하는 오라클 힌트절 7가지


오라클 플랜을 보는 법


개발자들의 영원한 숙제 ‘NULL 이야기’


알면 유용한 오라클 기능 ‘GATHER_PLAN_STATISTICS’


알면 유용한 오라클 기능들


오라클 DICTIONARY를 활용한 DB툴 프로그램 ‘FreeSQL’


이제는 말할 수 있다: 주식 자동매매 프로그램(상)


이제는 말할 수 있다: 주식 자동매매 프로그램(하)


개발자들이 자주 접하는 오라클 에러 메세지


재미있는 DB 이야기 ‘사라진 날짜를 찾아라’


오라클 랜덤 함수와 사용자 정의 함수


그림으로 배우는 ‘공정쿼리와 인덱스 생성도’


이병국의 개발자를 위한 DB 이야기: 디폴트 세팅의 함정과 오라클 파라미터


재미있는 DB 이야기 ‘놀라운 마방진의 세계’


오라클 운반 최소 단위 BLOCK


이병국의 개발자를 위한 DB 이야기: 이세돌과 알파고의 세기의 대결


이병국의 개발자를 위한 DB 이야기(30회) : DB 엔지니어의 가볍게 읽는 세상 이야기


이병국의 개발자를 위한 DB 이야기: 튜닝(31회) : 개발자를 위한 DB 튜닝 실전(1편)


이병국의 개발자를 위한 DB 이야기: 튜닝(32회) : 개발자를 위한 튜닝 실전(2편)


이병국의 개발자를 위한 DB 이야기: 튜닝(33회) : 개발자를 위한 튜닝 실전(3편)


이병국의 개발자를 위한 DB 이야기: 튜닝(34회) : 개발자를 위한 DB 튜닝 실전(4편)


이병국의 개발자를 위한 DB 이야기: 튜닝(35회) : 개발자를 위한 튜닝 실전(5편)


이병국의 개발자를 위한 DB 이야기: 페이징 처리에 대한 이해 (36회)


보기 좋은 떡이 먹기도 좋다 - 좋은 쿼리 좋은 성능


테이블의 수직분할과 수평분할에 대한 이해


DB 성능 제고를 위한 채번의 이해와 방식별 장단점 비교


이병국의 개발자를 위한 DB 이야기: 마지막회 : ‘개발자를 위한 DB 이야기’ 연재를 마치며



이병국의 개발자를 위한 DB 이야기: 인덱스(5회)

쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (상)



이병국 andongcn@dreamwiz.com
프리랜서 DB 엔지니어로서 동아제약 전산실에서 SW 개발 업무를 시작으로 프리랜서 개발자로 독립해 활동하던 중 우연한 기회에 DB와 인연을 맺게 됐다. 현재 삼성생명 전산 운영팀에서 쿼리 성능을 개선하는 DB 튜닝과 IOA 업무를 맡고 있다. 개발자 출신의 DB 엔지니어로 활동하면서 개발자에게 DB 관련 지식이 꼭 필요함을 절감했다. ‘정보의 불균형이 시장 왜곡을 가져온다’는 ‘레몬시장이론’은 중고차 거래에서 흔히 나타나기 쉽다. 좋은 차와 나쁜 차를 아는 중개인과 모르는 구매자 사이에는 정보의 비대칭 때문에 구매자가 손해를 볼 수 있다. 구매자도 차에 대해 기본적인 지식을 알고 있어야, 정보의 균형이 맞으므로 서로 손해를 보지 않고 합리적인 가격에 차를 거래할 수 있다. 마찬가지로 개발자들도 DB에 대해 기본적인 지식을 습득하여 정보의 균형을 맞추면, DB 엔지니어와 협업이 더 쉬워지고 한 단계 더 발전한 자신의 모습을 발견할 것이다.



성공과 실패의 경험을 나누자, 용기와 희망을 나누자
개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB 엔지니어로서
활동하면서 얻은 경험과 지식을 나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라,
DB 전문가를 꿈꾸는 대학생에서 DB 분야에 입문한 지 1~2년 된 기 입문자가
쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다.
물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.
전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등
개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다.
DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다.
그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다.
이 연재는 시리즈로 나갈 것이다.
연재를 끝까지 읽는 독자라면, 준전문가 수준의 DB 원리를 아는 것을 목표로 한다.

장자의 도가사상과 맹자의 유가사상

장자는 노자의 도가 사상을 계승하였고, 맹자는 공자의 유가 사상을 계승하였다. 맹자는 인간의 본성을 선하다고 전제하였고 인의에 따라 세상을 다스려야 한다고 주장하였다. 맹자는 자연의 순리대로가 아니라 인위적인 예와 의에 따라 사회를 다스리고 인간의 선한 본성을 회복해야 한다고 주장하였다.

반면에 장자는 노자의 도가 사상을 계승하였다. 그는 인위적인 통치가 아니라 자연의 순리에 따를 것을 주장하였다. 장자의 도는 항상 현실 가능한 도이며 실천 불가능한 이상향의 도는 아니었다. 장자의 제자들 중에 깨달음을 얻지 못한 자가 없었다고 한다. 요즘처럼 복잡하고 시끄러운 세상에서는 간단 명료하게 살아가는 장자의 지혜가 필요하다. 나는 장자가 말한 아래 문구를 무척 좋아한다.

column_img_1431.jpg

쉬운 것이 올바른 것이다.
올바르게 시작하면 모든 것이 쉬워진다.
쉽게 앞으로 나아가라. 그게 올바르다.
쉬운 것을 찾아내는 올바른 방법은 올바른 방법을 잊어 버리고
그게 쉽다는 것을 잊어버리는 것이다.

- 장자 -

쉬운 것이 올바르다. 올바른 것이 쉽다. 이 말처럼 이번 연재도 쉽게 쓰고자 노력했다. 예전 개발자 시절의 경험으로 볼 때, 개발자들이 DB의 인덱스를 얼마나 어렵게 인식하는지는 잘 알고 있다. 장자는 은유와 과장된 표현 방법을 많이 사용 하였는데, 이는 듣는 사람에게 그 의미를 쉽게 전달할 수 있기 때문이었다. 그 만큼 은유나 과장이 들어간 이야기는 사람의 흥미를 더 끌게 마련이다. 이번 연재뿐만 아니라 앞으로의 계속된 연재에도 장자의 방법을 많이 따르고자 한다.

이번 연재의 내용은 “인덱스를 잘 만들기 위한 기본 원리들에 대한 이해” 이다. 기존의 도서나 블로그에 많이 인용된 부분과는 다른 내용들이 있다. DB 인덱스의 원론적인 규칙은 존재하나, 실제로 업무 적용이나 그 사용에 있어서는 맞지 않는 부분들이 있다. 10여 년을 개발자로 지내면서 실제로 경험한 내용을 바탕으로 기술하였으며, 인덱스에 대한 전반적인 기본 규칙을 쉽게 이해하도록 설명하였다.



데이터베이스에서 인덱스란

우리는 SQL을 작성하면서 성능을 보장하기 위해 가정 먼저 무엇을 고려하는가 바로 인덱스다. 인덱스는 도서의 목차나 색인처럼 빨리 찾기 위한 용도로서 많이 인식되기도 하지만, 인덱스는 분류 정보이기도 하다.

분류는 물리적 분류와 논리적 분류로 나눌 수 있다. 현실 세계에서 우리가 접하는 대부분의 분류는 물리적 분류다. 물리적 분류는 하나의 공간에 한 가지 분류만 가능하다. 하지만 정보화 시대에 접어들면서 세상은 복잡해지고 분류할 정보는 점점 많아졌다. 컴퓨터가 사용되면서 대량의 정보에 대한 다양한 분류가 필요해졌다.

하나의 공간에 한 가지 분류만 필요하다면 인덱스는 필요 없을 것이다. 테이블에서 바로 분류하면 될 것이다. 하지만 디지털 세계에서의 자료는 하나의 공간에 여러 가지 분류가 필요하다. 이것을 가능하게 하는 방법은 분류 대상(테이블)과 분류 정보(인덱스)를 나누는 것이다. 결국 테이블과 인덱스는 분리되었다. 테이블에 인덱스는 종속적인 관계이면서 1:다의 관계다. 종속적이므로 테이블을 삭제하면 인덱스도 삭제된다. 참고로 테이블과 뷰는 종속적인 관계가 아니기에 테이블을 삭제해도 뷰는 삭제되지 않는다.

분류 대상(테이블)과 분류 정보(인덱스)를 분리함으로써 다양한 분류가 동시에 가능하게 되었다. 이것이 논리적 분류다. 인덱스는 논리적 분류다. 우리가 알고 있는 모든 DB는 테이블과 인덱스가 분리-관리되고 있다. DB 관련 도서나 블로그에서 인덱스의 특징을 다음과 같이 다양하게 내리고 있다.

-인덱스는 책의 목차와 같다.
-인덱스는 테이블의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조다.
-인덱스는 SQL명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해서 생성하는 객체다.
-오라클에서 인덱스 내부 구조는 B-tree 구조다.
-인덱스를 생성하는 구문에 대한 내용... Create Index...

필자는 여기에 다음의 내용을 추가하고 싶다.

-분류대상과 분류정보를 분리함으로써 다양한 분류가 동시에 가능하게 되었다.
-테이블은 분류대상이고 인덱스는 분류 정보다.
-인덱스는 논리적 분류다.
-분류는 물리적(현실세계) 분류와 논리적(정보화세계) 분류로 나눌 수 있다



인덱스 대상 후보 컬럼 선정 기준

인덱스는 테이블에서 찾고자 하는 레코드를 빨리 찾아가는 색인 또는 목차의 개념이기도 하고, 분류 대상(테이블)에 대한 논리적인 분류 정보(인덱스)이기도 하다. 테이블에 존재하는 수많은 컬럼이 모두 인덱스 컬럼으로 사용 가능한 것은 아니다. 컬럼들 중에서 인덱스로 사용 가능한 컬럼이 있고, 그렇지 않은 컬럼도 있다. 또한 가능하다고 무조건 인덱스로 만들 수 있는 것은 아니다. 조건절 혹은 조인절에서 자주 사용되어야 한다. 필자는 아래 5가지 규칙에 따라서 인덱스 대상 후보 컬럼을 결정한다.

1. 분포도가 좋은 컬럼인가
2. 갱신이 자주 발생하지 않는 컬럼인가
3. 조건절에서 자주 사용되는 컬럼인가
4. 조인의 연결고리에 사용되는 컬럼인가
5. SORT 발생을 제거하는 컬럼인가



첫째, 분포도가 좋은 컬럼인가
분포도란 전체 레코드에서 식별 가능한 수에 대한 백분율을 의미한다. 예를 들면 고객 테이블에서 성별 컬럼은 식별 가능한 수가 2개(남자, 여자)이고 분포도는 50%다. 나이 컬럼은 식별 가능한 수가 100개(1세~100세)이고 분포도는 1%다. 식별 가능한 수가 클수록 분포도는 낮으며, 분포도가 낮을수록 분포도가 좋다고 한다. 분포도가 좋은 컬럼은 인덱스 후보 컬럼이 될 수 있다. 아래 그림에서 고객 테이블의 레코드는 1,000 만 건이다. 각 컬럼의 분포도를 구해 보았다(균등 분포됨을 가정함).



column_img_1432.jpg

앞서, 분포도가 좋은 컬럼은 인덱스 후보 컬럼이 될 수 있다고 했다. 그렇다면 좋은 분포도의 기준은 무엇인가 일반적인 DB 전문서나 블로그를 살펴보면, 전체 레코드의 10% 이내를 주장하는 글도 있고 3% 이내를 주장하는 글도 있다. 나름대로 타당한 원리를 바탕으로 하였으나 현실은 그렇치 않다.

테이블 Full Scan시 멀티 블록으로 운반됨을 감안해야 하고 Index Random 접근 시, Leaf Block 접근도 감안해야 한다. 또한 인덱스 생성으로 인한 부하까지도 고려한다면, 인덱스 후보 컬럼으로 허용 가능한 기준치는 1% 이내라고 생각한다. 수많은 실전 경험을 통해서도 분포도가 1% 이상이면 인덱스의 효율성은 없었다. 오히려 테이블 Full Scan이 좋았던 적이 더 많았다.

올바른 규칙이라도 모두 행동해야 하는 것은 아니다. 초등학교 입학한 자녀를 둔 부모님들이 많이 겪는 문제가 있다. 학교에서 선생님이 아이들에게, 휴지를 보면 주우라고 가르친다. 그 중에 순진무구한 어린이는 등교 길에 휴지를 줍다가 지각을 하기도 하고, 하교 길에 휴지 줍다가 집에 늦게 돌아와서 부모를 걱정하게 하기도 한다. 휴지를 줍는 것은 좋은 일이지만, 그렇다고 그대로 실천하기엔 세상이 만만치 않다.

컬럼의 분포도가 10% 이내 혹은 3% 이내의 컬럼은 인덱스로 생성 가능하다는 주장은 기본 원리로만 이해해야지, 무조건 적용하지 말기 바란다. 필자가 주장하는 1% 이내도 배치 쿼리에서나 가능한 일이다. 온라인 쿼리에서는 1% 분포도도 좋은 분포도가 아니다. 테이블의 크기는 소형/중형/대용량 등으로 제각각이다. 이러한 테이블에 동일한 기준 1%를 적용하는 것은 문제가 있어 보인다. 예를 들어 천만 건으로 구성된 고객 테이블에서 1%는 10만 건이다. 온라인 쿼리에서 10만 건을 조회할 이유는 없을 것이다.

온라인 쿼리에서의 인덱스 후보 컬럼 선정 기준은, 분포도가 1% 이내 조건 외에도 한 가지를 더 추가할 필요가 있다. 화면에서 페이징을 구현한다고 가정할 때, 10페이지 이내의 건수에 해당하는 컬럼 정도만 인덱스 후보 컬럼으로 선정하자.

앞 <그림 1>에서 생년월일 컬럼의 분포도는 0.002739…%다. 분포도가 아주 좋다. 그렇다고 인덱스 후보 컬럼으로 선정할 수 있는가

고객 마케팅팀에서 이벤트를 하거나 감사 메일을 보낼 때, 생일이 몇 월 며칠인 고객이 중요하지, 생일이 몇 년 몇 월 며칠인 고객은 중요하지 않다. 컬럼의 분포도가 좋으나 업무적으로 봤을 때 조건절에 사용되지 않을 컬럼이므로 인덱스 후보 컬럼으로 선정할 수 없다.

만약 주문 테이블에 배송여부 컬럼이 있다고 가정하자. 배송여부 컬럼의 식별 가능한 수는 2개(Y,N)이며 분포도는 50%임을 우리는 알 수 있다. 컬럼의 분포도가 아주 좋지 않다. 그렇다면 배송여부 컬럼은 인덱스 후보 컬럼으로 선정 할 수 없을까

그렇치 않다. 논리적으로 식별 가능한 수는 Y, N 2가지 경우로서 분포도가 좋지 않다. 하지만 실제 테이블에 존재하는 물리적인 레코드의 배송여부 값은 대부분 Y이며, 최근에 주문한 극소수의 레코드 값만 N일 것이다. 논리적으로 판단하면 분포도가 좋지 않았으나, 물리적으로 실제값을 확인 했을 때는 배송여부 컬럼의 값에 따라 분포도가 극단적으로 쏠림을 알 수 있다. 분포도는 논리적으로 판단하기보다는 물리적으로 판단해야 한다. 아래와 같은 쿼리라면, 배송여부 컬럼은 인덱스 후보 컬럼으로서 자격이 충분하다.

SELECT *
FROM 주문
WHERE 배송여부 = ‘N’
ORDER BY 주문일자 ASC

서해안 대부도에 별장이 있는 지인이 있어서 가끔 놀러 가서 망둥어 낚시를 한다. 세상에! 이렇게 쉬운 낚시를 지금까지 경험하지 못했다. 낚싯대를 던지자마자 망둥어가 그냥 잡혀 올라왔다. 필자는 낚시를 좋아하지 않지만 망둥어 낚시만은 예외다. 특별한 떡밥이나 미끼도 필요 없다. 먹을 수 있는 모든 것이 미끼가 되었다. 망둥어를 잡기 위한 떡밥을 만든다면 팔리지 않을 것이다.

앞의 <그림 1>에서 성별 컬럼이 바로 그러하다. 고객 테이블에서 남자 고객은 50%의 분포도를 가진다. ‘물 반 고기 반’이라는 말이 있듯이 ‘남자 반 여자 반’이다. 물 반 고기 반인데 무슨 떡밥이 필요할 것이며, 그물이 필요할 것인가 남자 반 여자 반인데 무슨 인덱스가 필요할 것인가 당연히 필요 없다. 망둥어처럼 낚싯대를 던지자 마자 잡히는 물고기가 있는 반면, 천만 번 낚싯대를 던져서 한 번 잡히는 특별한 물고기가 있다고 하자. 여러분들은 천만 번 던질 것인가 아니면 떡밥을 살 것인가 그러한 떡밥을 만드는 회사는 얼마나 매출이 좋을까

여러분은 아마 낚싯대를 천만 번 던지는 수고를 하기보다는 약간의 비용이 들더라도 떡밥을 사서 한 번만에 그 특별한 물고기를 잡으려 할 것이다. <그림 1>에서 고객번호 컬럼이 바로 그러하다. 특정 고객번호를 찾기 위해서는 1000만 건 고객 테이블을 Full Scan을 해야 한다. 하지만 약간의 부하가 있더라도 인덱스를 만들어 놓으면, 한 번의 Random Access로 우리가 원하는 고객의 정보를 찾을 수 있다.

항상 인덱스를 만들 때 컬럼의 분포도가 좋은지 여부를 고민해야 한다. 인덱스 후보 컬럼으로서 자격이 있는지 고민해야 한다. 해당 컬럼이 특별한 물고기인지 고민해야 한다.

지금까지 단일인덱스의 분포도에 대해 설명했다. 그렇다면 결합인덱스의 분포도는 어떻게 판단할 것인가 단일인덱스에서는 컬럼의 분포도가 중요한 요소이지만, 결합 인덱스에서는 컬럼들의 순서가 중요한 요소다. 그렇다고 결합 인덱스에서 분포도가 나빠도 된다는 이야기는 아니다. 결합 인덱스도 각각의 컬럼은 분포도가 나쁠지라도 전체 컬럼의 분포도는 좋아야 하는 것은 당연하다.

둘째, 갱신이 자주 발생하지 않는 컬럼인가
인덱스 컬럼이 자주 갱신(update) 되면, 아래 <그림 2>처럼 인덱스 밸런싱이 깨어진다. 인덱스는 조회를 빨리 하기 위해 만들어졌지만, 그만큼 우리가 희생해야 하는 부분이 있다. 테이블에 처리(insert, update, delete) 작업을 할 때, 인덱스에 대한 정보도 반영해야 하기 때문이다. 가급적 update 항목에서 사용하는 컬럼은 인덱스 후보 컬럼에서 배제하는 것이 좋다.



column_img_1433.jpg

update 항목에 속하는 컬럼인지 아닌지, 우리는 컬럼명으로 어느 정도 알 수 있다. 수정일자, 종료일자, 취소일자, 승인일자, 상태코드, 결과코드, 배송여부, 진행상태코드 등을 예로 들 수 있다. 이러한 update 유발 컬럼이라도 인덱스에 반드시 사용해야 하는 경우도 있다. 배송여부, 진행상태코드 등의 컬럼이 그 예이다. 이러한 컬럼은 단일인덱스에 사용되기보다는 결합 인덱스의 후행 컬럼으로 주로 사용된다. 갱신이 자주 발생하는 컬럼은, 인덱스 후보 컬럼 선정 시 더 엄격한 기준으로 대해야 한다.



셋째, 조건절에서 자주 사용되는 컬럼인가
조건절에 자주 사용한다는 의미는 중요한 조건절이라는 의미도 있지만, 범용적인 조건절이라는 의미가 더 크다. 테이블에 종속적인 인덱스가 많으면 많을수록 데이터 처리시 부하가 클 것이다. 그렇다면 우리는 가능한 한 필요한 만큼의 인덱스만 만들도록 노력해야 한다.



column_img_1434.jpg

위의 <그림 3>처럼 동일한 테이블에 쿼리1, 쿼리2, 쿼리3 이 있다고 가정하자. 그리고 컬럼 1~7은 해당 쿼리의 조건절에서 사용하는 인덱스 후보 컬럼이다. 우리가 필요로 하는 인덱스는 각각의 쿼리 1, 2, 3에 하나씩 총 3개가 필요하다. 만약 한 개의 인덱스만 만들 수 있다면 여러분은 어떤 인덱스를 만들 것인가 이와 같은 경우는 실제 운영하면서 많이 만나는 문제다. 내용을 요약하면 다음과 같다.

쿼리 일일 구동 횟수: 쿼리3(1,000번) > 쿼리1(100번) > 쿼리2(10번)
조건절에서 자주 사용: 컬럼3(3회) > 컬럼2(2회)= 컬럼4 = 컬럼6 > 컬럼1(1회) = 컬럼5 = 컬럼7

필자는 다음과 같이 인덱스를 만든다.
결합 인덱스 = 컬럼3 + 컬럼4 + 컬럼6 + 컬럼7
제일 먼저 가장 빈번히 구동되는 쿼리 3을 기준으로 인덱스를 만든다. 그리고 다른 쿼리도 범용적으로 사용할 수 있도록 조건절에서 자주 사용하는 컬럼을 결합 인덱스의 선행으로 두었다.

넷째, 조인의 연결고리에 사용되는 컬럼인가
인덱스는 조건절에서 사용되기도 하고 조인절에서 사용되기도 한다. 조건절에서 사용하는 인덱스는 최초로 접근하는 테이블을 결정하는 중요한 인덱스이며, 데이터 접근 범위를 줄여주는 역할을 한다. 조인절에서 사용하는 인덱스는 테이블간의 관계를 맺는 인덱스로서, 데이터 접근 범위를 항상 줄여주는 것은 아니다. 1:다 관계의 테이블 조인에서는 오히려 접근 범위가 커지기도 한다.

오라클 쿼리에서 테이블간의 관계를 연결해주는 조인의 방법에는 다음과 같이 3가지가 있다.

-Nested Loop Join: 온라인 쿼리에서 90% 이상을 차지, 조인절에 인덱스가 반드시 있어야 한다.
-Sort Merge Join: 거의 발견할 수 없다. 조인절에 인덱스가 반드시 있어야 하는 것은 아니다.
-Hash Join: 배치 쿼리에서 30% 이상을 차지, 조인절에 인덱스가 반드시 있어야 하는 것은 아니다.

위의 테이블간 조인 방법 3가지 중에서 개발자들이 접하는 대부분의 방식은 Nested Loop Join이다. 현재 운영중인 쿼리의 90% 이상을 차지하고 있다. 이 수치는 아마 대부분의 회사가 비슷할 것이다. 이 경우에는 조인절에 인덱스가 반드시 있어야 한다. 솔직히 말하면 선후가 바뀌었다. 위의 3가지 조인 방식에 따라서 인덱스가 필요한 것이 아니라, 인덱스 존재 여부에 따라서 위의 3가지 조인 방법이 결정되는 경우가 많다.



column_img_1435.jpg

위의 <그림 4>처럼 고객 테이블과 주문 테이블이, 고객번호 컬럼으로 조인되어 있다고 가정하자. 인덱스를 생성해야 할 위치는 몇 번인가 아래 4가지 경우에서 가장 저비용의 방법을 선택하면 된다.

-1번 위치에 인덱스를 생성할 경우: 주문 테이블에서 고객 테이블로 접근(조인연결)
-2번 위치에 인덱스를 생성할 경우: 고객 테이블에서 주문 테이블로 접근(조인연결)
-1번, 2번 위치에 모두다 있을 경우: 오라클에서 통계 정보를 바탕으로 테이블간 방향을 결정
-1번, 2번 위치에 모두다 없을 경우: Sort Merge Join 방식이나 Hash Join 방식으로 플랜이 결정

다섯째, SORT 발생을 제거하는 컬럼인가
인덱스는 기본적으로 위치정보 + 순서 정보로 구성된다. 조건절에 사용하는 컬럼만 인덱스 후보 컬럼이 되는 것은 아니다. ORDER BY 절에 있는 컬럼도 인덱스 후보 컬럼으로 사용할 수 있다. 하나의 테이블에 인덱스가 많으면 많을수록 부하가 점점 증가 하듯이 결합 인덱스도 컬럼 수가 많으면 많을수록 부하가 점점 증가한다.

결국 SORT 발생 제거를 위해서 인덱스에 해당 컬럼을 포함시킬지 말지는 비용 측면을 따져 봐야 한다. 결합 인덱스에 조건절 컬럼 이외에 ORDER BY 절 컬럼을 추가할 때 발생하는 인덱스 부하와, 추가하지 않을 때 발생하는 SORT 부하를 비교해 결정한다. 만약 소트 부하가 더 크다라고 판단되면 ORDER BY 절 컬럼도 인덱스 후보로 선정 가능하다.

SELECT *
FROM 주문
WHERE 상품코드 = ‘텐트’
AND 배송여부 = ‘N’
ORDER BY 주문일자 DESC

위의 쿼리에서 상품코드, 배송여부는 위치정보 컬럼이고 주문일자 컬럼은 순서정보 컬럼이다. 우리는 아래와 같이 2가지 인덱스를 고려 할 수 있다.

결합 인덱스 1: 상품코드(위치) + 배송여부(위치)
결합 인덱스 2: 상품코드(위치) + 배송여부(위치) + 주문일자(순서)

SORT 부하가 인덱스 부하 보다 크다면 결합 인덱스2를 고려해야 하며, SORT 부하가 인덱스 부하보다 작다면 결합 인덱스 1을 고려해야 한다. 그런데 이러한 부하 비교는 명확하게 수치적으로 구할 수 없다. 대부분의 개발자들은 아마 결합 인덱스 2로 결정할 것이다. 왜냐하면 인덱스 부하는 DBA가 해결 할 문제이지만 SORT 부하는 개발자가 해결해야 할 문제이기 때문이다.

필자는 아래와 같은 기준으로 결정을 한다. 상품코드, 배송여부 컬럼과 같이 위치정보 컬럼만으로 조회한 건수가 수백 건 단위라면 결합 인덱스 1로 결정한다. 수천 건 단위라면 결합 인덱스 2로 결정한다. 덧붙여서 쿼리 구동 횟수가 많을수록 결합 인덱스 2로 결정할 가능성이 높다. 이런 고민도 하기 싫은 개발자들은 결합 인덱스 2로 결정하기 바란다(DBA에게는 미안한 이야기지만..)

병원의 예를 들어 보자. 전산화 이전의 병원에서는 종이로 된 환자 진료 카드로 환자정보를 관리하였다. 만약 창고에 수만 명의 환자 진료 카드가 수백 개의 박스에 보관돼 있다고 하였을 때, 수만 명의 환자 진료 카드를 가져오기 위한 노력은 미미할 것이다. 수백 개의 박스만 이동하면 되기 때문이다. 하지만 가져온 수만 명의 환자 진료 카드를 어떤 순서로 정렬 하고자 할 때, 얼마나 많은 노력이 필요할지는 여러분들도 충분히 알 것이다.

DB에서도 마찬가지다. 조건에 따른 결과 데이터를 가져오는 데에 필요한 부하보다는, 가져온 데이터를 SORT하는 데 필요한 부하가 수십 배 이상인 경우가 아주 흔하다. 가져온 데이터의 건수가 많으면 많을수록 부하는 급격하게 증가한다. 그래서 인덱스를 생성할 때 SORT 발생을 제거하는 컬럼인지 여부는 그만큼 매우 중요하다. 대부분의 개발자들은 인덱스를 생성할 때 조건절 컬럼만 인덱스 후보 컬럼으로 생각하는 경향이 있다. 하지만 ORDER BY 절 컬럼도 인덱스 후보 컬럼으로 중요하게 사용되는 경우가 있음을 알아야 한다.

인덱스 기본 원리들에 대한 내용은 이번 연재에 이어서 다음 연재에도 계속된다.



용기를 갖자
오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘하늘에서 뚝 떨어져
새로 만들어진 것’이 아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는
그런 보편적인 원리를 바탕으로 만들어졌으므로 쉽게 접근하고 이해할 수 있다.
서두에서 말했듯이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 어려워하지 말고
용기를 내고 하나씩 터득해 나가기를 바란다.
이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서
더 친숙하고 더 쉽게 이해하고 접근하길 바랄 뿐이다.
이 글을 읽으면서 궁금하거나 의계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며...


[지난 문제의 정답과 풀이] 원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제

지난 연재에 출제한 ‘원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제’에 대한 정답과 해설은 아래와 같다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다.

column_img_1436.jpg

정답: ① ② ③ (1번이 가장 빠름, 2번이 중간, 3번이 가장 느림)
① 우편번호 테이블 10만 건 Full Scan 발생
② 우편번호 테이블 10만 건 Full Scan 발생 + 고객 인덱스(Random Access)
③ 우편번호 인덱스(Random Access) + 고객 테이블 100만 건 Full Scan 한 번 이상 발생

해설: 조건절에 인덱스가 없는 것보다 조인절에 인덱스가 없는 것이 더 치명적이다. 조건절에 인덱스가 없다면 Full Scan은 한 번만 발생하지만, 조인절에 인덱스가 없다면 테이블간 Join 방식에 따라서 Full Scan은 한 번 혹은 그 이상이 발생할 수 있다.



[이번 호 문제] 원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제

각 연재의 말미에 간단하고 재미있는, 생각해 보는 문제를 출제하려 한다. 모든 문제는 DB의 원리를 이해할 수 있는 문제로 출제할 예정이다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해 할 수 있다. 정답과 풀이는 다음 연재에서 한다.



column_img_1437.jpg