출처: http://juin100.blogspot.kr/2010/01/mysql%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EC%84%B1%EB%8A%A5-%EC%B5%9C%EC%A0%81%ED%99%94-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EB%8A%94-%ED%95%84%EC%9A%94%EC%95%85%EC%9D%B4%EB%8B%A4.html
우리는 데이터베이스의 데이터를 액세스하기 위해 SQL을 사용하게 된다. SQL을 수행하면서 해당 SQL의 성능 향상을 위해
인덱스를 생성하는 경우가 다반사일 것이다. 이와 같이 인덱스는 성능 향상을 위해 매우 중요한 역할을 수행하게 된다. 하지만, 많은
곳에서는 성능 향상을 위한 중요한 인덱스를 잘못 이용하는 경우가 많다. SQL의 성능 향상을 위한 중심에는 인덱스가 존재한다.
이제부터 인덱스의 정확한 이해를 통해 성능 향상을 계획해야 할 것이다.
권순용 | kwontra@hanmail.net
수 많은 프로젝트에서 프로그램을 개발하면서 해당 프로그램에 필요한 인덱스를 생성하고 인덱스를 이용하여 성능 향상을 계획하는 사이트를 많이 보아왔다. 하지만, 많은 사이트에서 인덱스의 잘못된 선정으로 성능 문제가 발생하고 이로 인해 많은 고통을 경험하는 것을 수없이 많이 보아 왔다. 도대체 인덱스에는 어떠한 비밀이 존재하기 때문에 우리는 SQL을 위해 인덱스를 생성하고 성능 저하를 경험해야 하는 것인가? 이는 우리가 인덱스에 대해 두 가지의 잘못된 사실을 진실로 간주하기 때문이다. 이제부터 인덱스의 잘못된 두 가지 사실에 대해 정확히 파헤쳐 보자.
인덱스는 무엇인가?
나 를 알고 적을 안다면 100전 100승이라고 했던가? 따라서, 인덱스를 효과적으로 이용하기 위해서는 인덱스에 대한 정확한 이해가 필요할 것이다. 인덱스의 정확한 이해 없이 인덱스를 논한다는 것은 수박 겉핥기에 지나지 않을 것이다.
여 기서는 인덱스의 물리적 구조를 이야기하고 싶지는 않다. 인덱스의 물리적 구조도 필요하겠지만 우리가 반드시 이해해야 할 것은 인덱스의 논리적 구조이다. 많은 개발자들과 이야기를 하다 보면 인덱스에 대해 많은 것을 알고 있다고 생각하지만 많은 사람들이 인덱스의 논리적 구조에 대해 정확히 이해하지 못하는 경우가 매우 많았다. 필자는 인덱스의 논리적 구조는 사전의 인덱스와 동일하다고 자주 이야기를 한다. 그럼 이제부터 우리가 잘 알고 있는 사전의 인덱스를 정확히 분석해 보자. 이것이 우리가 인덱스의 논리적 구조를 정확히 이해하는 가장 빠른 지름길이 될 것이다.
우리가 학창 시절부터 사용하던 사전을 생각해 보자. 사전의 옆에는 사전의 인덱스가 존재할 것이다. 여기서 각각의 영어 알파벳은 컬럼의 값이라고 생각하면 된다. 예를 들어, GIRL이라는 단어는 4개의 컬럼의 값이며 첫 번째 컬럼의 값은 ‘G’이며 두 번째 컬럼의 값은 ‘I’가 된다고 가정하자. 이와 같은 가정에서 인덱스의 논리적 구조를 확인해 보자.
첫 번째로 인덱스는 첫 번째 컬럼의 값으로 정렬되어 구성되는 특징에 대해 확인해 보자. 사전의 인덱스를 확인해 보면 첫 번째 알파벳이 ‘A’로 시작하는 단어부터 시작하여 알파벳 순서로 정렬되어 사전을 구성하게 된다. 인덱스도 이와 같이 인덱스를 구성하는 첫 번째 컬럼의 값으로 정렬되어 구성된다. 이와 같은 사실은 대부분의 개발자들이나 또는 관리자들이 알고 있는 사실이다.
두 번째로 인덱스의 첫 번째 컬럼의 값이 동일한 경우에는 인덱스의 두 번째 컬럼의 값으로 정렬되는 특징에 대해 확인해 보자. 인덱스를 구성하는 첫 번째 컬럼의 값이 동일한 경우에는 어떻게 인덱스가 구성되겠는가? 이 또한 사전의 인덱스를 생각해보면 바로 답을 유추할 수 있음에도 불구하고 많은 사람들이 정확히 모르고 있다는 것이 현실이다. 만약, 사전이 ‘A’로 시작하는 단어를 순서 없이 모아두고 ‘B’로 시작하는 단어를 어떠한 순서에 상관 없이 모아 둔다면 이와 같이 구성된 사전이 의미 있겠는가?
예 를 들어, GIRL이란 단어를 찾는다면 ‘G’로 시작하는 단어를 모아둔 사전의 페이지를 모두 읽어봐야 할 것이다. 이처럼 단어의 첫 번째 알파벳으로만 사전의 인덱스를 구성한다면 동일한 알파벳으로 시작하는 단어를 모아두었다는 약간의 의미는 있겠지만 그렇게 큰 의미는 없을 것이다. 사전의 인덱스를 확인해 보면 ‘A’로 시작하는 단어는 어떤 순서로 사전에 저장되어 있는가? 동일한 알파벳으로 시작하는 단어는 단어를 구성하는 두 번째 알파벳으로 정렬되어 저장된다.
이와 같기 때문에 GIRL이라는 단어를 찾는다면 ‘G’로 시작하는 단어 중 두 번째 알파벳이 ‘I’인 단어를 바로 찾게 된다. 두 번째 알파벳이 동일한 단어에 대해서는 세 번째 알파벳으로 정렬되어 사전에 저장될 것이다. 사전의 단어들이 이와 저장되기 때문에 우리는 사전을 효과적으로 사용할 수 있을 것이다. 정렬된 구조로 저장되기 때문에 우리가 원하는 단어를 바로 찾아갈 수 있을 것이다.
인 덱스의 논리적 구조에는 위와 같은 특징을 가진다. 인덱스의 첫 번째 컬럼으로 인덱스는 정렬되어 구성되며 인덱스를 구성하는 첫 번째 컬럼의 값이 동일한 경우에는 인덱스를 구성하는 두 번째 컬럼으로 정렬되어 구성된다는 사실은 매우 중요한 사실이다. 이를 이해하고 이제부터 우리가 잘못 알고 있는 인덱스의 비밀을 확인해 보자.
인덱스를 이용해야만 성능은 향상되는가
우 리가 SQL을 작성하면서 성능을 보장하기 위해 가장 먼저 무엇을 고려하는가? 가장 먼저 고려하는 사항은 인덱스일 것이다. 많은 경우에 작성한 SQL에 대해 인덱스를 생성한다면 성능을 보장 받을 수 있다고 생각하게 된다. 과연, 인덱스만 생성한다면 해당 SQL의 성능을 보장할 수 있겠는가? 어떤 SQL은 인덱스 때문에 성능이 엄청 저하될 수 있는 것이 현실이다. 이러한 경우는 경험해본 사람이라면 쉽게 이해할 수 있을 것이다.
그렇다면 어떤 경우의 SQL에는 인덱스가 필요하고 어떤 경우의 SQL에는 인덱스가 필요하지 않은 것일까? 인덱스를 이용하여 성능을 최적화하기 위해서 어떤 컬럼으로 인덱스를 구성할 것인가에 대한 것보다도 해당 SQL이 인덱스를 이용해야 할지 아니면 해당 SQL이 인덱스를 이용하면 안 되는지에 대한 정확한 기준이 필요하다. SQL을 작성하는 사람들은 이러한 기준을 가지고 있는가? 아마도 많은 사람들이 이러한 기준을 가지고 있지 않을 것이다. 이러한 문제는 SQL을 작성하는 개발자들만의 문제가 아니다.
우리가 많이 사용하는 툴들은 해당 SQL의 실행 계획에서 인덱스를 이용하지 못하는 경우에 빨간색을 표시하게 된다. 이와 같은 현상이 마치 무조건 문제인 것처럼 보이게 만들어 무조건 인덱스를 생성하게 만드는 경우도 많다. 이제부터 우리는 어떤 SQL은 인덱스를 이용하고 어떤 SQL은 인덱스를 이용해서는 안 되는지에 대해 정확하게 구분해야 할 것이다.
첫 번째로 인덱스를 이용해서는 안 되는 SQL에 대해 확인해 보자. 어떤 SQL이 인덱스를 이용하면 안 되는지에 대해 언급하기 전에 하나의 예제를 확인해 보자. 어떤 사이트를 지원 했을 때의 일이다. 개발 담당자는 매일 저녁 9시부터 1시간 동안 야간 통계 작업을 수행한 후 SQL의 수행 결과를 확인하고 퇴근을 하는 경우를 보았다. 해당 담당자는 매일 저녁 이와 같은 작업을 1년 동안 수행하고 있었다.
해당 SQL을 확인한 결과 해당 SQL은 해당 테이블의 대부분의 데이터를 액세스하여 통계 데이터를 추출하고 있었다. 해당 데이터를 액세스 하는 과정에는 인덱스를 이용하고 있었다. 해당 SQL을 최적화한 후에는 1시간 동안 수행되던 SQL이 단지 50초 정도에 종료할 수 있었다. 최적화 하는 과정은 해당 SQL이 인덱스를 이용하지 못하게 하고 테이블을 전체 스캔하도록 변경해 주었다.
단지, 인덱스를 이용하는가 아니면 인덱스를 이용하지 않는가에 의해 이와 같이 큰 영향을 미치게 된 것이다. 다른 어느 사이트에서도 이러한 문제를 인식하지 못하고 당연히 오래 수행되는 작업이라고 생각하고 매일 작업을 수행하고 있는 사이트가 있을 것이다.
바로 이것이 SQL이 인덱스를 이용해야 하는지 이용해서는 안 되는지에 기준을 제시해 줄 것이다. 그렇다면 인덱스를 이용해야 할지 인덱스를 이용하면 안되는지에 대한 기준을 제시하는 요소는 무엇인가? 바로 액세스 하는 데이터의 양이다.
해당 테이블에서 많은 양의 데이터를 액세스 한다면 인덱스를 이용하여 테이블을 액세스 하는 경우에는 인덱스를 액세스 한 후 테이블을 액세스 하는 랜덤 액세스 가 발생하기 때문에 성능은 매우 저하된다. 이와 같은 경우라면 인덱스를 이용하여 테이블을 액세스 하는 방법보다는 인덱스를 이용하지 않고 테이블을 전체 액세스 하는 경우가 더 빠른 성능을 보장하게 될 것이다.
예 를 들어, 1만개의 단어를 저장하고 있는 사전에서 5000개의 단어를 찾는다고 가정하자. 해당 사전은 한 페이지에 20개 씩의 단어가 기록되어 있으며 그렇기 때문에 전체 페이지는 500 페이지가 된다고 가정하자. 이와 같다면 여러분들은 사전의 인덱스를 이용하여 원하는 5000개의 단어를 찾을 것인가 아니면 사전의 인덱스를 이용하지 않고 테이블의 데이터를 액세스 할 것인가?
대 부분의 사람들은 사전의 인덱스를 이용하지 않고 테이블의 데이터를 액세스 해야 더 빠른 성능을 보장할 수 있을 거라고 이야기한다. 이는 분명히 맞는 답이다. 20개의 단어가 저장되어 있는 하나의 페이지에서 평균 10개의 단어는 우리가 찾고자 하는 단어일 것이다. 이와 같은 경우 사전의 인덱스를 이용한다면 하나의 페이지를 10번씩 액세스 하게 된다. 하지만, 사전의 인덱스를 이용하지 않고 사전을 처음부터 끝까지 읽게 된다면 하나의 페이지에서 10개의 단어를 찾을 수 있기 때문에 우리는 하나의 페이지를 한번만 액세스 하면 될 것이다. 하나의 페이지를 10번 액세스 하는 것이 빠르겠는가 아니면 하나의 페이지를 한번만 액세스 하는 것이 빠르겠는가? 두 말할 것도 없이 하나의 페이지를 한번만 액세스 하는 것이 빠를 것이다. 이와 같은 차이에 의해 성능에 있어서는 엄청난 차이가 발생할 수 밖에 없게 된다.
결국, 인덱스를 이용해야 할지 아닐지는 액세스 하는 데이터의 양에 의해 좌우된다. SQL의 성능을 최적화하기 위해 무조건 인덱스를 생성해서는 안될 것이다. 해당 SQL이 테이블의 많은 데이터를 액세스 해야 한다면 인덱스를 이용하는 것보다는 테이블을 전체 스캔하는 방법이 성능을 보장한다는 것을 명심하길 바란다.
두 번째로 인덱스를 이용해야 하는 SQL을 확인해 보자. SQL은 위와 같이 테이블을 전체 스캔해야 하는 SQL을 제외하면 인덱스를 이용하여 데이터를 액세스 해야 할 것이다.
그 렇다면 테이블의 데이터 중 어느 정도의 데이터를 액세스 하는 것이 많은 양의 데이터를 액세스 하는 것일까? 또는 어느 정도의 데이터를 액세스 해야 적은 양의 데이터를 액세스 하는 것일까? 일반적으로 해당 테이블의 3%~5% 정도의 데이터가 기준이 된다. 해당 테이블의 데이터가 10만건이라고 가정하자. 그렇다면 3000건에서 5000건의 데이터가 기준이 될 것이다. 따라서, 1000건의 데이터를 액세스 해야 한다면 인덱스를 이용하는 것이 성능을 보장할 수 있게 된다.
하 지만 10만건의 데이터를 액세스 하는 경우에는 3%~5%의 기준을 넘게 되므로 인덱스를 이용하는 것보다는 인덱스를 이용하지 않는 것이 더 유리할 것이다. 테이블의 데이터가 대용량이라면 3%~5%의 기준 값은 낮아질 것이다. 그렇기 때문에 초 대용량 테이블은 1%가 기준이 되기도 한다. 이와 같은 정확한 기준 값이 중요한 것은 아니다.
중요한 것은 많은 데이터를 액세스 하는 SQL이 인덱스를 이용한다면 우리가 원하는 성능을 보장 받을 수 없으며 반대로 인덱스를 이용해야 하는 SQL이 인덱스를 이용하지 않는다면 이 또한 성능을 보장 받을 수 없다는 것이다.
이 와 같기 때문에 SQL을 작성하는 경우 해당 SQL이 인덱스를 이용해야 할지 아니면 테이블을 전체 스캔해야 할지를 가장 먼저 고려해야 할 것이다. 이제는 맹목적으로 해당 SQL에 인덱스를 생성해야 성능을 보장 받을 수 있다는 잘못된 사실에서 벗어나야 할 것이다.
인덱스 컬럼들의 순서와 분포도는 많은 상관 관계가 없다
SQL 에 필요한 인덱스를 생성한다면 우리는 많은 경우에 결합 인덱스를 생성하게 된다. 결합 인덱스를 생성하면서 많은 경우에는 해당 컬럼의 분포도를 고려하여 분포도가 좋은 컬럼을 인덱스의 첫 번째 컬럼으로 구성하는 경우를 많이 보았을 것이다. 과연, 이와 같이 분포도가 좋은 컬럼을 결합 인덱스의 첫번째 컬럼으로 선정하는 방식이 우리가 선택할 수 있는 최상의 인덱스 선정일까?
결 론부터 언급하자면 결합 인덱스에서는 컬럼의 분포도는 의미가 없게 된다. 이 뜻은 결합 인덱스를 생성하는 경우 각 컬럼의 분포도는 의미가 없다는 것이다. 분포도를 고려하지 않고 결합 인덱스를 생성한다는 것은 말이 되지 않는다고 할 수도 있을 것이다. 하지만, 분명한 것은 결합 인덱스에서의 분포도는 큰 의미를 가지지 않는다. 왜 이와 같은 현상이 발생하는 것일까?
카 드 회사에서 카드 가입자의 카드번호만을 관리하는 테이블에서 카드번호 컬럼은 분포도가 매우 좋을 것이다. 하지만, 여기서 우리는 하나의 함정에 빠지게 된다. 그것은 무엇인가? 바로 분포도가 좋다는 뜻에 대한 함정이다. 우리가 카드번호 값에 대해 분포도가 좋다는 뜻은 무엇을 의미하는가? 이는 하나의 카드번호만을 액세스 하는 경우에 해당할 것이다.
모든 카드번호는 ‘1’로 시작한다고 가정하자. 만약, 카드번호 값에 대해 ‘1’로 시작하는 카드번호 값을 액세스 한다면 분포도는 어떠한가? 이와 같이 데이터를 액세스 한다면 아무리 분포도가 좋은 카드번호 컬럼도 많은 데이터가 추출되며 분포도는 안 좋게 된다. 결국, 우리가 항상 이야기 하는 분포도가 좋은 컬럼과 분포도가 나쁜 컬럼 컬럼의 기준에는 우리도 모르게 동일한 데이터를 액세스 하는 경우를 의미하게 된다.
‘111111’번 카드번호 값을 액세스 한다면 우리가 원하는 데이터는 한건의 데이터가 되므로 분포도는 좋게 된다. 하지만, SQL에서 ‘1’로 시작하는 모든 카드번호 데이터를 액세스 한다면 분포도는 나쁘게 된다. 이와 같이 우리가 말하는 분포도는 서로 약속은 안 했지만 해당 컬럼의 값과 동일한 데이터를 추출하는 경우에 해당하게 된다.
결국, 우리가 말하는 분포도는 동일한 값을 의미하게 된다. 하지만, 우리가 추출하고자 하는 데이터는 항상 동일한 데이터만을 의미하지는 않게 된다. 때로는 LIKE 연산자 또는 BETWEEN 연산자 등을 많이 이용하기 때문에 이런 경우라면 해당 컬럼의 분포도는 의미 없게 된다. 이와 같은 이유에서 해당 컬럼의 분포도는 더 이상 결합 인덱스를 생성하는 컬럼의 순서에 중요한 역할을 수행하지 못하게 된다.
인덱스 컬럼들의 순서를 효과적으로 선정하자
인덱스를 구성하는 각각의 컬럼의 분포도가 중요하지 않다면 결합 인덱스를 구성하는 컬럼의 순서를 고려할 경우 가장 먼저 고려해야 하는 요소는 무엇인가? 결합 인덱스를 구성할 경우 우리가 반드시 고려해야 하는 요소는 아래와 같다.
점 조건과 선분 조건
결 합 인덱스의 순서를 정하는 가장 중요한 요소는 해당 컬럼에 사용되는 연산자이다. 아직도 많은 교육과 문서에서 컬럼의 분포도가 인덱스 선정에 중요하다고 언급하는 경우가 있다. 하지만, 이러한 것이 우리에게 많은 오류를 발생시킨다는 것을 이해하길 바란다. 가장 중요한 요소는 해당 컬럼을 액세스 하는 연산자라는 것을 명심하길 바란다. 위에서 점 조건에는 =과 IN 연산자만이 포함되며 나머지 연산자는 선분 조건에 해당된다.
SQL> SELECT ……
FROM TAB1
WHERE COL1 = ‘A’
AND COL2 BETWEEN ‘A’ AND ‘B’;
위 와 같은 SQL이 수행되며 각 컬럼의 분포도는 COL1 컬럼의 경우에는 분포도가 좋으며 COL2 컬럼의 경우에는 분포도가 좋지 않다고 가정하자. 그렇다면 많은 사람들은 분포도만을 고려하여 COL2+COL1 인덱스를 생성하려고 하는 경우가 많다. 하지만, COL2 컬럼은 BETWEEN 연산자를 사용했으므로 해당 컬럼의 분포도는 의미가 없게 된다. 따라서, 위의 SQL에서 최적의 인덱스는 COL1+COL2 인덱스가 된다.
결국, 분포도를 배제하고 연산자를 통해 결합 인덱스를 생성해야 한다. 이와 같이 인덱스를 구성해야만 COL1 컬럼과 COL2컬럼에 의해 처리 범위가 감소하게 된다. 앞의 값의 하나의 값이 아닌 선분 조건이라면 처리 범위는 증가하기 때문이다. 결합 인덱스는 반드시 아래와 같은 특성을 가지게 된다.
● 점 조건+점 조건: 두 컬럼에 의해 처리 범위 감소
● 점 조건+선분 조건: 두 컬럼에 의해 처리 범위 감소
● 선분 조건+선분 조건: 앞의 선분 조건에 의해서만 처리 범위 감소
● 선분 조건+점 조건: 앞의 선분 조건에 의해서만 처리 범위 감소
위 와 같이 컬럼의 분포도가 아닌 컬럼의 연산자에 의해 인덱스는 처리 범위를 감소시키게 되며 처리 범위를 가장 많이 감소시킬 수 있는 형태의 결합 인덱스만이 성능을 보장할 수 있게 된다. 분포도에 의한 결합 인덱스 선정이 아닌 연산자에 의한 결합 인덱스 선정의 중요성을 인식하길 바란다. 이것이야 말로 해당 SQL의 성능을 보장할 수 있는 유일한 방법이다.
SQL 을 작성한 후 무조건 인덱스를 만들려고 하는 생각과 결합 인덱스에서 연산자를 고려하지 않고 분포도가 좋은 컬럼을 앞에 위치시키는 인덱스야 말로 성능을 저하시키는 주범이 된다. 이제부터 최적의 인덱스를 선정하기 위해 우리 함께 노력해야 할 것이다. 인덱스에 대한 우리가 쉽게 빠질 수 있는 함정에 빠지지 않게 항상 주의해야 할 것이다.
[MYSQL]데이터베이스 성능 최적화 인덱스는 필요악이다
권순용 | kwontra@hanmail.net
수 많은 프로젝트에서 프로그램을 개발하면서 해당 프로그램에 필요한 인덱스를 생성하고 인덱스를 이용하여 성능 향상을 계획하는 사이트를 많이 보아왔다. 하지만, 많은 사이트에서 인덱스의 잘못된 선정으로 성능 문제가 발생하고 이로 인해 많은 고통을 경험하는 것을 수없이 많이 보아 왔다. 도대체 인덱스에는 어떠한 비밀이 존재하기 때문에 우리는 SQL을 위해 인덱스를 생성하고 성능 저하를 경험해야 하는 것인가? 이는 우리가 인덱스에 대해 두 가지의 잘못된 사실을 진실로 간주하기 때문이다. 이제부터 인덱스의 잘못된 두 가지 사실에 대해 정확히 파헤쳐 보자.
인덱스는 무엇인가?
나 를 알고 적을 안다면 100전 100승이라고 했던가? 따라서, 인덱스를 효과적으로 이용하기 위해서는 인덱스에 대한 정확한 이해가 필요할 것이다. 인덱스의 정확한 이해 없이 인덱스를 논한다는 것은 수박 겉핥기에 지나지 않을 것이다.
여 기서는 인덱스의 물리적 구조를 이야기하고 싶지는 않다. 인덱스의 물리적 구조도 필요하겠지만 우리가 반드시 이해해야 할 것은 인덱스의 논리적 구조이다. 많은 개발자들과 이야기를 하다 보면 인덱스에 대해 많은 것을 알고 있다고 생각하지만 많은 사람들이 인덱스의 논리적 구조에 대해 정확히 이해하지 못하는 경우가 매우 많았다. 필자는 인덱스의 논리적 구조는 사전의 인덱스와 동일하다고 자주 이야기를 한다. 그럼 이제부터 우리가 잘 알고 있는 사전의 인덱스를 정확히 분석해 보자. 이것이 우리가 인덱스의 논리적 구조를 정확히 이해하는 가장 빠른 지름길이 될 것이다.
우리가 학창 시절부터 사용하던 사전을 생각해 보자. 사전의 옆에는 사전의 인덱스가 존재할 것이다. 여기서 각각의 영어 알파벳은 컬럼의 값이라고 생각하면 된다. 예를 들어, GIRL이라는 단어는 4개의 컬럼의 값이며 첫 번째 컬럼의 값은 ‘G’이며 두 번째 컬럼의 값은 ‘I’가 된다고 가정하자. 이와 같은 가정에서 인덱스의 논리적 구조를 확인해 보자.
첫 번째로 인덱스는 첫 번째 컬럼의 값으로 정렬되어 구성되는 특징에 대해 확인해 보자. 사전의 인덱스를 확인해 보면 첫 번째 알파벳이 ‘A’로 시작하는 단어부터 시작하여 알파벳 순서로 정렬되어 사전을 구성하게 된다. 인덱스도 이와 같이 인덱스를 구성하는 첫 번째 컬럼의 값으로 정렬되어 구성된다. 이와 같은 사실은 대부분의 개발자들이나 또는 관리자들이 알고 있는 사실이다.
두 번째로 인덱스의 첫 번째 컬럼의 값이 동일한 경우에는 인덱스의 두 번째 컬럼의 값으로 정렬되는 특징에 대해 확인해 보자. 인덱스를 구성하는 첫 번째 컬럼의 값이 동일한 경우에는 어떻게 인덱스가 구성되겠는가? 이 또한 사전의 인덱스를 생각해보면 바로 답을 유추할 수 있음에도 불구하고 많은 사람들이 정확히 모르고 있다는 것이 현실이다. 만약, 사전이 ‘A’로 시작하는 단어를 순서 없이 모아두고 ‘B’로 시작하는 단어를 어떠한 순서에 상관 없이 모아 둔다면 이와 같이 구성된 사전이 의미 있겠는가?
예 를 들어, GIRL이란 단어를 찾는다면 ‘G’로 시작하는 단어를 모아둔 사전의 페이지를 모두 읽어봐야 할 것이다. 이처럼 단어의 첫 번째 알파벳으로만 사전의 인덱스를 구성한다면 동일한 알파벳으로 시작하는 단어를 모아두었다는 약간의 의미는 있겠지만 그렇게 큰 의미는 없을 것이다. 사전의 인덱스를 확인해 보면 ‘A’로 시작하는 단어는 어떤 순서로 사전에 저장되어 있는가? 동일한 알파벳으로 시작하는 단어는 단어를 구성하는 두 번째 알파벳으로 정렬되어 저장된다.
이와 같기 때문에 GIRL이라는 단어를 찾는다면 ‘G’로 시작하는 단어 중 두 번째 알파벳이 ‘I’인 단어를 바로 찾게 된다. 두 번째 알파벳이 동일한 단어에 대해서는 세 번째 알파벳으로 정렬되어 사전에 저장될 것이다. 사전의 단어들이 이와 저장되기 때문에 우리는 사전을 효과적으로 사용할 수 있을 것이다. 정렬된 구조로 저장되기 때문에 우리가 원하는 단어를 바로 찾아갈 수 있을 것이다.
인 덱스의 논리적 구조에는 위와 같은 특징을 가진다. 인덱스의 첫 번째 컬럼으로 인덱스는 정렬되어 구성되며 인덱스를 구성하는 첫 번째 컬럼의 값이 동일한 경우에는 인덱스를 구성하는 두 번째 컬럼으로 정렬되어 구성된다는 사실은 매우 중요한 사실이다. 이를 이해하고 이제부터 우리가 잘못 알고 있는 인덱스의 비밀을 확인해 보자.
인덱스를 이용해야만 성능은 향상되는가
우 리가 SQL을 작성하면서 성능을 보장하기 위해 가장 먼저 무엇을 고려하는가? 가장 먼저 고려하는 사항은 인덱스일 것이다. 많은 경우에 작성한 SQL에 대해 인덱스를 생성한다면 성능을 보장 받을 수 있다고 생각하게 된다. 과연, 인덱스만 생성한다면 해당 SQL의 성능을 보장할 수 있겠는가? 어떤 SQL은 인덱스 때문에 성능이 엄청 저하될 수 있는 것이 현실이다. 이러한 경우는 경험해본 사람이라면 쉽게 이해할 수 있을 것이다.
그렇다면 어떤 경우의 SQL에는 인덱스가 필요하고 어떤 경우의 SQL에는 인덱스가 필요하지 않은 것일까? 인덱스를 이용하여 성능을 최적화하기 위해서 어떤 컬럼으로 인덱스를 구성할 것인가에 대한 것보다도 해당 SQL이 인덱스를 이용해야 할지 아니면 해당 SQL이 인덱스를 이용하면 안 되는지에 대한 정확한 기준이 필요하다. SQL을 작성하는 사람들은 이러한 기준을 가지고 있는가? 아마도 많은 사람들이 이러한 기준을 가지고 있지 않을 것이다. 이러한 문제는 SQL을 작성하는 개발자들만의 문제가 아니다.
우리가 많이 사용하는 툴들은 해당 SQL의 실행 계획에서 인덱스를 이용하지 못하는 경우에 빨간색을 표시하게 된다. 이와 같은 현상이 마치 무조건 문제인 것처럼 보이게 만들어 무조건 인덱스를 생성하게 만드는 경우도 많다. 이제부터 우리는 어떤 SQL은 인덱스를 이용하고 어떤 SQL은 인덱스를 이용해서는 안 되는지에 대해 정확하게 구분해야 할 것이다.
첫 번째로 인덱스를 이용해서는 안 되는 SQL에 대해 확인해 보자. 어떤 SQL이 인덱스를 이용하면 안 되는지에 대해 언급하기 전에 하나의 예제를 확인해 보자. 어떤 사이트를 지원 했을 때의 일이다. 개발 담당자는 매일 저녁 9시부터 1시간 동안 야간 통계 작업을 수행한 후 SQL의 수행 결과를 확인하고 퇴근을 하는 경우를 보았다. 해당 담당자는 매일 저녁 이와 같은 작업을 1년 동안 수행하고 있었다.
해당 SQL을 확인한 결과 해당 SQL은 해당 테이블의 대부분의 데이터를 액세스하여 통계 데이터를 추출하고 있었다. 해당 데이터를 액세스 하는 과정에는 인덱스를 이용하고 있었다. 해당 SQL을 최적화한 후에는 1시간 동안 수행되던 SQL이 단지 50초 정도에 종료할 수 있었다. 최적화 하는 과정은 해당 SQL이 인덱스를 이용하지 못하게 하고 테이블을 전체 스캔하도록 변경해 주었다.
단지, 인덱스를 이용하는가 아니면 인덱스를 이용하지 않는가에 의해 이와 같이 큰 영향을 미치게 된 것이다. 다른 어느 사이트에서도 이러한 문제를 인식하지 못하고 당연히 오래 수행되는 작업이라고 생각하고 매일 작업을 수행하고 있는 사이트가 있을 것이다.
바로 이것이 SQL이 인덱스를 이용해야 하는지 이용해서는 안 되는지에 기준을 제시해 줄 것이다. 그렇다면 인덱스를 이용해야 할지 인덱스를 이용하면 안되는지에 대한 기준을 제시하는 요소는 무엇인가? 바로 액세스 하는 데이터의 양이다.
해당 테이블에서 많은 양의 데이터를 액세스 한다면 인덱스를 이용하여 테이블을 액세스 하는 경우에는 인덱스를 액세스 한 후 테이블을 액세스 하는 랜덤 액세스 가 발생하기 때문에 성능은 매우 저하된다. 이와 같은 경우라면 인덱스를 이용하여 테이블을 액세스 하는 방법보다는 인덱스를 이용하지 않고 테이블을 전체 액세스 하는 경우가 더 빠른 성능을 보장하게 될 것이다.
예 를 들어, 1만개의 단어를 저장하고 있는 사전에서 5000개의 단어를 찾는다고 가정하자. 해당 사전은 한 페이지에 20개 씩의 단어가 기록되어 있으며 그렇기 때문에 전체 페이지는 500 페이지가 된다고 가정하자. 이와 같다면 여러분들은 사전의 인덱스를 이용하여 원하는 5000개의 단어를 찾을 것인가 아니면 사전의 인덱스를 이용하지 않고 테이블의 데이터를 액세스 할 것인가?
대 부분의 사람들은 사전의 인덱스를 이용하지 않고 테이블의 데이터를 액세스 해야 더 빠른 성능을 보장할 수 있을 거라고 이야기한다. 이는 분명히 맞는 답이다. 20개의 단어가 저장되어 있는 하나의 페이지에서 평균 10개의 단어는 우리가 찾고자 하는 단어일 것이다. 이와 같은 경우 사전의 인덱스를 이용한다면 하나의 페이지를 10번씩 액세스 하게 된다. 하지만, 사전의 인덱스를 이용하지 않고 사전을 처음부터 끝까지 읽게 된다면 하나의 페이지에서 10개의 단어를 찾을 수 있기 때문에 우리는 하나의 페이지를 한번만 액세스 하면 될 것이다. 하나의 페이지를 10번 액세스 하는 것이 빠르겠는가 아니면 하나의 페이지를 한번만 액세스 하는 것이 빠르겠는가? 두 말할 것도 없이 하나의 페이지를 한번만 액세스 하는 것이 빠를 것이다. 이와 같은 차이에 의해 성능에 있어서는 엄청난 차이가 발생할 수 밖에 없게 된다.
결국, 인덱스를 이용해야 할지 아닐지는 액세스 하는 데이터의 양에 의해 좌우된다. SQL의 성능을 최적화하기 위해 무조건 인덱스를 생성해서는 안될 것이다. 해당 SQL이 테이블의 많은 데이터를 액세스 해야 한다면 인덱스를 이용하는 것보다는 테이블을 전체 스캔하는 방법이 성능을 보장한다는 것을 명심하길 바란다.
두 번째로 인덱스를 이용해야 하는 SQL을 확인해 보자. SQL은 위와 같이 테이블을 전체 스캔해야 하는 SQL을 제외하면 인덱스를 이용하여 데이터를 액세스 해야 할 것이다.
그 렇다면 테이블의 데이터 중 어느 정도의 데이터를 액세스 하는 것이 많은 양의 데이터를 액세스 하는 것일까? 또는 어느 정도의 데이터를 액세스 해야 적은 양의 데이터를 액세스 하는 것일까? 일반적으로 해당 테이블의 3%~5% 정도의 데이터가 기준이 된다. 해당 테이블의 데이터가 10만건이라고 가정하자. 그렇다면 3000건에서 5000건의 데이터가 기준이 될 것이다. 따라서, 1000건의 데이터를 액세스 해야 한다면 인덱스를 이용하는 것이 성능을 보장할 수 있게 된다.
하 지만 10만건의 데이터를 액세스 하는 경우에는 3%~5%의 기준을 넘게 되므로 인덱스를 이용하는 것보다는 인덱스를 이용하지 않는 것이 더 유리할 것이다. 테이블의 데이터가 대용량이라면 3%~5%의 기준 값은 낮아질 것이다. 그렇기 때문에 초 대용량 테이블은 1%가 기준이 되기도 한다. 이와 같은 정확한 기준 값이 중요한 것은 아니다.
중요한 것은 많은 데이터를 액세스 하는 SQL이 인덱스를 이용한다면 우리가 원하는 성능을 보장 받을 수 없으며 반대로 인덱스를 이용해야 하는 SQL이 인덱스를 이용하지 않는다면 이 또한 성능을 보장 받을 수 없다는 것이다.
이 와 같기 때문에 SQL을 작성하는 경우 해당 SQL이 인덱스를 이용해야 할지 아니면 테이블을 전체 스캔해야 할지를 가장 먼저 고려해야 할 것이다. 이제는 맹목적으로 해당 SQL에 인덱스를 생성해야 성능을 보장 받을 수 있다는 잘못된 사실에서 벗어나야 할 것이다.
인덱스 컬럼들의 순서와 분포도는 많은 상관 관계가 없다
SQL 에 필요한 인덱스를 생성한다면 우리는 많은 경우에 결합 인덱스를 생성하게 된다. 결합 인덱스를 생성하면서 많은 경우에는 해당 컬럼의 분포도를 고려하여 분포도가 좋은 컬럼을 인덱스의 첫 번째 컬럼으로 구성하는 경우를 많이 보았을 것이다. 과연, 이와 같이 분포도가 좋은 컬럼을 결합 인덱스의 첫번째 컬럼으로 선정하는 방식이 우리가 선택할 수 있는 최상의 인덱스 선정일까?
결 론부터 언급하자면 결합 인덱스에서는 컬럼의 분포도는 의미가 없게 된다. 이 뜻은 결합 인덱스를 생성하는 경우 각 컬럼의 분포도는 의미가 없다는 것이다. 분포도를 고려하지 않고 결합 인덱스를 생성한다는 것은 말이 되지 않는다고 할 수도 있을 것이다. 하지만, 분명한 것은 결합 인덱스에서의 분포도는 큰 의미를 가지지 않는다. 왜 이와 같은 현상이 발생하는 것일까?
카 드 회사에서 카드 가입자의 카드번호만을 관리하는 테이블에서 카드번호 컬럼은 분포도가 매우 좋을 것이다. 하지만, 여기서 우리는 하나의 함정에 빠지게 된다. 그것은 무엇인가? 바로 분포도가 좋다는 뜻에 대한 함정이다. 우리가 카드번호 값에 대해 분포도가 좋다는 뜻은 무엇을 의미하는가? 이는 하나의 카드번호만을 액세스 하는 경우에 해당할 것이다.
모든 카드번호는 ‘1’로 시작한다고 가정하자. 만약, 카드번호 값에 대해 ‘1’로 시작하는 카드번호 값을 액세스 한다면 분포도는 어떠한가? 이와 같이 데이터를 액세스 한다면 아무리 분포도가 좋은 카드번호 컬럼도 많은 데이터가 추출되며 분포도는 안 좋게 된다. 결국, 우리가 항상 이야기 하는 분포도가 좋은 컬럼과 분포도가 나쁜 컬럼 컬럼의 기준에는 우리도 모르게 동일한 데이터를 액세스 하는 경우를 의미하게 된다.
‘111111’번 카드번호 값을 액세스 한다면 우리가 원하는 데이터는 한건의 데이터가 되므로 분포도는 좋게 된다. 하지만, SQL에서 ‘1’로 시작하는 모든 카드번호 데이터를 액세스 한다면 분포도는 나쁘게 된다. 이와 같이 우리가 말하는 분포도는 서로 약속은 안 했지만 해당 컬럼의 값과 동일한 데이터를 추출하는 경우에 해당하게 된다.
결국, 우리가 말하는 분포도는 동일한 값을 의미하게 된다. 하지만, 우리가 추출하고자 하는 데이터는 항상 동일한 데이터만을 의미하지는 않게 된다. 때로는 LIKE 연산자 또는 BETWEEN 연산자 등을 많이 이용하기 때문에 이런 경우라면 해당 컬럼의 분포도는 의미 없게 된다. 이와 같은 이유에서 해당 컬럼의 분포도는 더 이상 결합 인덱스를 생성하는 컬럼의 순서에 중요한 역할을 수행하지 못하게 된다.
인덱스 컬럼들의 순서를 효과적으로 선정하자
인덱스를 구성하는 각각의 컬럼의 분포도가 중요하지 않다면 결합 인덱스를 구성하는 컬럼의 순서를 고려할 경우 가장 먼저 고려해야 하는 요소는 무엇인가? 결합 인덱스를 구성할 경우 우리가 반드시 고려해야 하는 요소는 아래와 같다.
점 조건과 선분 조건
결 합 인덱스의 순서를 정하는 가장 중요한 요소는 해당 컬럼에 사용되는 연산자이다. 아직도 많은 교육과 문서에서 컬럼의 분포도가 인덱스 선정에 중요하다고 언급하는 경우가 있다. 하지만, 이러한 것이 우리에게 많은 오류를 발생시킨다는 것을 이해하길 바란다. 가장 중요한 요소는 해당 컬럼을 액세스 하는 연산자라는 것을 명심하길 바란다. 위에서 점 조건에는 =과 IN 연산자만이 포함되며 나머지 연산자는 선분 조건에 해당된다.
SQL> SELECT ……
FROM TAB1
WHERE COL1 = ‘A’
AND COL2 BETWEEN ‘A’ AND ‘B’;
위 와 같은 SQL이 수행되며 각 컬럼의 분포도는 COL1 컬럼의 경우에는 분포도가 좋으며 COL2 컬럼의 경우에는 분포도가 좋지 않다고 가정하자. 그렇다면 많은 사람들은 분포도만을 고려하여 COL2+COL1 인덱스를 생성하려고 하는 경우가 많다. 하지만, COL2 컬럼은 BETWEEN 연산자를 사용했으므로 해당 컬럼의 분포도는 의미가 없게 된다. 따라서, 위의 SQL에서 최적의 인덱스는 COL1+COL2 인덱스가 된다.
결국, 분포도를 배제하고 연산자를 통해 결합 인덱스를 생성해야 한다. 이와 같이 인덱스를 구성해야만 COL1 컬럼과 COL2컬럼에 의해 처리 범위가 감소하게 된다. 앞의 값의 하나의 값이 아닌 선분 조건이라면 처리 범위는 증가하기 때문이다. 결합 인덱스는 반드시 아래와 같은 특성을 가지게 된다.
● 점 조건+점 조건: 두 컬럼에 의해 처리 범위 감소
● 점 조건+선분 조건: 두 컬럼에 의해 처리 범위 감소
● 선분 조건+선분 조건: 앞의 선분 조건에 의해서만 처리 범위 감소
● 선분 조건+점 조건: 앞의 선분 조건에 의해서만 처리 범위 감소
위 와 같이 컬럼의 분포도가 아닌 컬럼의 연산자에 의해 인덱스는 처리 범위를 감소시키게 되며 처리 범위를 가장 많이 감소시킬 수 있는 형태의 결합 인덱스만이 성능을 보장할 수 있게 된다. 분포도에 의한 결합 인덱스 선정이 아닌 연산자에 의한 결합 인덱스 선정의 중요성을 인식하길 바란다. 이것이야 말로 해당 SQL의 성능을 보장할 수 있는 유일한 방법이다.
SQL 을 작성한 후 무조건 인덱스를 만들려고 하는 생각과 결합 인덱스에서 연산자를 고려하지 않고 분포도가 좋은 컬럼을 앞에 위치시키는 인덱스야 말로 성능을 저하시키는 주범이 된다. 이제부터 최적의 인덱스를 선정하기 위해 우리 함께 노력해야 할 것이다. 인덱스에 대한 우리가 쉽게 빠질 수 있는 함정에 빠지지 않게 항상 주의해야 할 것이다.
댓글
댓글 쓰기