개발자를 위한 DB 이야기 - 이병국
데이터베이스 인덱스의 오해와 진실
성공과 실패의 경험을 나누자, 용기와 희망을 나누자 개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB 엔지니어로서 활동하면서 얻은 경험과 지식을 나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서 DB 분야에 입문한 지 1~2년 된 기 입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.
전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면, 준전문가 수준의 DB 원리를 아는 것을 목표로 한다.
결혼인가, 화혼인가?
어느 날 직장 남자 후배의 결혼식에 가려고 사무실 양식 보관함에서 ‘화혼(華婚)’이라고 인쇄된 봉투 하나를 챙겼다. 지나가던 선배가, 내가 들고 있는 봉투를 보면서 한마디 하였다.
결혼(結婚)은 남자 측에 내는 것이고, 화혼은 여자 측에 내는 것이다. 화혼이라는 인쇄된 봉투가 아닌, 결혼이라고 인쇄된 봉투를 챙겨야 한다고 알려 주었다. 선배의 지적에 당황해서 알겠다고 말하고, 내가 알고 있는 내용과 틀린 것 같아 검색 사이트에서 찾아봤다. 크게 다음의 3가지 주장이 있었다.
1. 결혼은 남자 결혼식에서 사용하고 화혼은 여자 결혼식에서 사용한다.
2. 결혼은 나와 동일하거나 아랫사람일 때 사용하고, 화혼은 남녀 구분 없이 남의 결혼식을 높여 부르는 말이다.
3. 결혼과 화혼을 사용하기 보다는 ‘혼인’을 사용해야 한다.
어느 것이 올바른지는 이 연재에서 중요하지 않다. 하지만 요즘 출처가 불분명하고 부정확한 내용이 블로그에 인용되고, 정확한 내용인 것처럼 빠르게 퍼져 나가는 것을 볼 때가 있다.
DB 분야에서도 이런 글을 간혹 목격할 수 있다. 지금까지는, 나만 올바르게 알면 되지 남이 쓴 글에 이렇다 저렇다 다른 의견을 제시 할 필요까지는 없다고 생각했다. 하지만 이번 연재를 계기로 용기를 내서 필자가 알고 있는 부분에 대해 의견을 제시하고자 한다. 논란의 여지가 있을 수 있고 연재 후 어떤 반응이 나올지 궁금하지만, 이 또한 서로 지식 교류이고 정확하게 짚고 넘어가는 기회가 될 것 같아 나름 위안을 삼는다.
결합인덱스의 컬럼 순서 결정방법
검색 사이트에서 결합인덱스의 컬럼 순서 결정방법에 대한 내용을 검색해 보면, 다수의 블로그에서 다음과 같은 내용을 인용하고 있다.
1. 항상 WHERE 조건에 사용되는 컬럼인가?
2. 항상 ‘=‘ 로 사용되는가?
3. 분포도가 좋은 컬럼인가?
4. SORT에 사용되는 컬럼인가?
여기에서 논란이 되는 부분은 3번과 4번이다. 나머지 내용에는 필자도 동의한다. 하지만 구체적인 설명이나 예가 없어서 내용을 덧붙여서 설명하고자 한다.
첫째, 항상 WHERE 조건에 사용되는 컬럼인가?
결합인덱스의 첫번째 컬럼을 조건에서 사용하지 않는다면, 그 인덱스는 사용되지 않는 경우가 대부분이다. 따라서 많은 쿼리에서 공통적으로 사용되는 조건절의 컬럼을 인덱스 선행 컬럼에 주로 사용한다. 다수의 쿼리에서 공통적으로 사용 된다는 것은 필수 조건절이라는 의미와 동일하다. 이러한 필수 조건절은 결합인덱스의 선행 컬럼으로 사용되어야 한다.
둘째, 항상 ‘=‘ 로 사용되는가?
결합인덱스에서 선행 컬럼이 ‘=‘ 조건이 아니라면 후행 컬럼 조건에서 ‘=‘ 을 사용하더라도 처리범위는 줄어들지 않는다. 조건절에서 ‘=‘ 이 아닌 연산자를 사용하는 첫번째 컬럼까지만 인덱스를 타고, 그 다음 후행 컬럼부터는 인덱스를 타지 않고 필터만 한다. 다른 말로 체크 한다고도 한다. 예를 들어, 다음과 같은 결합인덱스가 있다고 하자.
결합인덱스 = 컬럼1 + 컬럼2 + 컬럼3 + 컬럼4 + 컬럼5
만약 아래와 같이 컬럼3 조건에서 BETWEEN을 사용하였다면, 컬럼3까지만 인덱스를 타고 결합인덱스의 후행 컬럼인 컬럼4, 컬럼5는 비록 ‘=‘ 조건이 있더라도 인덱스를 타지 않고 필터만 한다.
WHERE 컬럼1 = ?
AND 컬럼2 = ?
AND 컬럼3 BETWEEN ? AND ? ？ 결합인덱스에서 ‘=‘ 이 아닌 연산자를 사용하는 첫번째 컬럼
AND 컬럼4 = ?
AND 컬럼5 LIKE ?
일반적으로 날짜의 의미를 갖고 있는 주문일자, 생산일자, 결재일자 등의 컬럼은 구간 조건으로 많이 사용되는 컬럼이다. 따라서 결합인덱스의 후행 컬럼으로 주로 사용된다. 결국 결합인덱스는 다음과 같은 컬럼의 순서로 변경할 것을 고려해야 한다.
결합인덱스 = 컬럼1 + 컬럼2 + 컬럼4 + 컬럼3 + 컬럼5
셋째, 분포도가 좋은 컬럼인가?
‘분포도가 좋은 컬럼은 처리 범위를 줄여 주므로 결합인덱스의 선행 컬럼으로 사용해야 한다’고 다수의 블로그에서 인용하고 있다. 과연 그럴까???
필자는 그렇지 않다고 생각한다. 만약 분포도가 좋은 선행 컬럼이라면, 굳이 결합인덱스로 사용할 필요는 없고 단일인덱스로 사용하면 된다. 결합인덱스는 여러 컬럼을 합쳐서 처리 범위를 줄여주는 인덱스인데, 이미 첫번째 선행 컬럼이 분포도가 좋아서 처리 범위가 많이 줄어들었으므로 굳이 결합인덱스로 만들 필요까지는 없다.
결국 분포도가 좋은 컬럼은, 결합인덱스의 선행 컬럼 대상이라기보다는 단일인덱스 컬럼 선정의 중요한 고려사항이다. 지금까지 경험한 대부분의 결합인덱스 컬럼 순서와 분포도의 관계는 다음과 같았고, 블로그에서 설명하는 내용과는 정반대였다.
결합인덱스: 선행컬럼(분포도 나쁨) + … + 중간컬럼(분포도 보통) + … + 후행컬럼(분포도 좋음)
결합인덱스는 여러 컬럼들을 합쳐서 처리 범위를 줄인다는 의미도 있지만, 다수의 단일인덱스를 대체하는 공통의 인덱스라는 의미도 있는데, 이 부분을 간과한 것 같다. <그림 1>을 보면 분포도가 좋은 컬럼(소분류망)이 왜 선행 컬럼이 되어서는 안되는지 이유가 명확해 진다.
만약 위의 <그림 1>에서 분포도가 좋은 소분류 망이 제일 위에 있다면 어떻게 될까? 우리는 고은 모래만 얻을 수 있을 것이다. 하지만 분류기에서 분류망의 순서를 위의 <그림 1>과 같이 배치한다면, 자갈-굵은 모래-고은 모래를 동시에 얻을 수 있다.
또한 대분류망은 자갈, 굵은 모래, 가는 모래를 모두 구할 수 있는 필수 분류망이기도 하다. 이는 결합인덱스 컬럼 순서 결정 방법의 첫 번째 규칙에 해당한다. 결국 결합인덱스에서 선행 컬럼의 순서는 분포도로 접근하기 보다는, 분류의 개념으로 접근하는 것이 오히려 더 좋은 결과를 가져 올 수 있다.
현실 세계에서 우리가 접하는 대부분의 분류 순서는 큰 범위에서 작은 범위로의 분류다. <그림 1>에서 우리는 다수의 단일 인덱스의 역할을 동시에 가능하게 하는 결합인덱스의 구성 원리를 보았다. 그 원리는 아래와 같다.
결합인덱스: 선행컬럼(대분류) + … + 중간컬럼(중분류 ) + … + 후행컬럼(소분류)
넷째, SORT에 사용되는 컬럼인가?
이 부분에도 약간 논란의 여지가 있다. SORT에 사용되었다고 결합인덱스의 선행 컬럼으로 반드시 사용되어야 하는 것은 아니다. 결합인덱스는 결합된 컬럼의 순서로 정렬돼 저장되므로 SORT에 사용되는 컬럼은 결합인덱스의 일부분 일 수는 있으나, 선행 컬럼의 조건일 수는 없다. 오히려 SORT에 사용되는 컬럼은 결합인덱스 후행 컬럼에 자주 사용된다.
인덱스는 도서의 목차나 색인의 역할처럼 빨리 찾는 의미로 인식되고 있는데, 인덱스는 기본적으로 위치(조건) 정보와 순서(SORT) 정보의 특성을 동시에 갖고 있다. 따라서 결합인덱스에는 위치정보를 갖고 있는 컬럼도 있고, 순서 정보를 갖고 있는 컬럼도 있다. 결합인덱스는 다음과 같은 규칙으로 구성돼야 한다.
1. 위치정보 컬럼만으로 구성할 수 있다. 혹은 순서정보 컬럼만으로 구성할 수 있다.
2. 위치정보 컬럼과 순서정보 컬럼의 순으로 구성할 수 있다.
3. 위치정보 컬럼과 순서정보 컬럼의 순서는 혼재돼서는 안되며 뒤바뀌어도 안된다.
결론적으로, 결합인덱스의 컬럼 순서에서 순서정보 컬럼은 위치정보 컬럼의 후행에 있어야 한다.
결합인덱스의 컬럼 순서 결정방법
필자가 생각하는 결합인덱스의 컬럼 순서 결정방법은 위의 내용을 요약 정리한 아래의 내용과 같다.
1. 공통적으로 사용하는 필수 조건절 컬럼을 우선한다.
2. ‘=‘ 조건의 컬럼을 다른 연산자 컬럼보다 우선한다.
3. 대분류 ？ 중분류 ？ 소분류 컬럼순으로 구성한다.
4. 위치(조건) 정보 컬럼은 순서(SORT) 정보 컬럼보다 우선한다.
위의 4가지 방법으로 결합인덱스의 컬럼 순서를 결정할 수 있으며, 각 방법은 우선 순위가 없으므로 복합적으로 판단-결정해야 할 것이다.
다음 연재에서는 3회에 걸쳐서 본격적으로 인덱스에 대해 설명할 것이다. 인덱스는 데이터베이스에서 데이터를 검색할 때 검색되는 데이터의 수를 줄여 성능을 높이기 위해 지정하는 식별자이다. 또한 데이타의 논리적 분류 정보를 가지고 있으며 데이터베이스 요소 중에서 가장 중요한 부분이기도 하다. 잘못된 인덱스로 인하여 성능 저하를 가져올 수 있으므로, 개발자 입장에서도 아주 중요한 부분이다. 연재 내용은 아래와 같다.
1. 인덱스를 잘 만들기 위한 기본 원리들에 대한 이해
2. 인덱스 생성도와 실전 인덱스
3. 인덱스의 종류와 사용 목적에 대한 이해
이 가운데 ‘2. 인덱스 생성도와 실전 인덱스’는 이번 전체 연재에서 필자가 가장 중요하게 생각하는 부분이다.
용기를 갖자
오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘하늘에서 뚝 떨어져 새로 만들어진 것’이 아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는 그런 보편적인 원리를 바탕으로 만들어졌으므로 쉽게 접근하고 이해할 수 있다. 서두에서 말했듯이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 어려워하지 말고 용기를 내고 하나씩 터득해 나가기를 바란다.
이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고 접근하길 바랄 뿐이다. 이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다. 아무리 어렵고 힘든 일이더라고 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며….
원리를 이해하고 논리로 설명하는, 쉬어가는 DB 문제
각 연재의 말미에 간단하고 재미있는, 생각해 보는 문제를 출제하려 한다. 모든 문제는 DB의 원리를 이해할 수 있는 예제 중심의 문제로 출제할 예정이다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다. 정답과 그에 대한 설명은 다음 연재에 소개한다.
