MS SQL 서버의 Clustered (클러스터) 인덱스에 대한 그릇된 이해.

------------------------------------------------------------------------------------------------

출처: http://www.borlandforum.com/impboard/impboard.dll?action=read&db=free&no=15167

         [주정섭(jjsverylong) 님의 글]

------------------------------------------------------------------------------------------------

간혹 개발자들과 이야기하다보면, 대체 어디서 주워들었는지 모르지만, 매우 황당한 사실을 미신처럼 믿고

그 방식대로 코딩 하거나, 전혀 근거없는 상식(?)을 주장하는 개발자들을 매우 자주 보곤한다.

 

해외에서는 이런 박수무당식의 허무 맹랑한 코딩 방식을 비꼬아서 부두 프로그래밍이라고 한다.

부두 프로그래밍이란, 자신만의 근거없는 편견과 오만으로, 전혀 근거없는 개발 사상으로 무장하고서

코딩하는 것을 말한다.

왜 이런 부두 프로그래밍이 난무하는지 아직도 아리송하지만, 대충 나의 경험에 의하면 많은 개발자들이

어떤 사실을 받아들일 때, 그 사실의 논리타당성을 검증하지 않고, 막무가내 혹은 지 편한대로 받아들인다는 것이다.

 

특히 비교적 많이 알려진 대가나 경험자들이 어떤 거짓사실을 유포하면, 그것의 옳고 그름을 따져보기보다는 그냥 마구 믿어버린다는 것이다. 대부분의 사람들은 책을 읽거나 해서 그 사실을 확인해보는 적극성을

가지기 보다는, 유명한 사람이 말하면 그냥 믿고 사용하는 것이 더 편하다고 생각하는 경향이 있는 것 같다.

그래서, 우리는 이명박씨의 말도 안되는 거대한 경제 공약을 너무나 쉽게 믿고, 그를 뽑아주는 실수를

저질렀는지도 모르겠다. 내 경험에 의하면, 쉽게 믿는 것 만큼 바보스런 짓이 없다는 것이다.

특히 이 개발업계에서는 모든 코딩적 개념은 반드시 확인하고 사용해봐야 한다.

과거 내가 경험했던 그런 무책임하고도 거짓 투성이인 개발 관련 속설들 중에서 아직도 빈번히 접하는 것의

예를 들면,

"RDB에서 Trigger는 결코 사용해서는 안되는 위험 천만한 도구다"
"델파이에서 DataAware Component들은 쓸데없이 Lock를 가중시키므로 결코 사용해서는 안된다",
"MS SQL 서버에서 Primay Key Index는 클러스터(clustered) 인덱스로 지정하는 것이 가장 효율적이다."

이런 말도 안되는 속설 중에서 마지막 항인, 과연 MS SQL 서버에서 PK는 클러스터 인덱스로 지정해야

하는 것이 옳은가를 따져 보자. 이글을 쓰기 전에 클러스터 인덱스를 제대로 아는 사람들이 주위에 있는지 물어봤지만, 상당수가 클러스터 인덱스의 기본 개념조차 모르고 있는 경우가 허다 했다.

뭔가를 따져보려면, 그 근본 개념을 잘 알아야만 한다. 점을 믿는 것이 점에 대한 전혀 근거 없는 믿음에서 시작하듯이, 점쟁이들이 실제로 어떻게 점을 치는지를 따져보면 점이 얼마나 허무맹랑한 것인지 알 수 있다.

MS SQL 서버에서 어떤 인덱스를 Clustered로 지정하면, 그 테이블 내의 레코드들은 그 클러스터 인덱스의 키값대로 물리적으로 정렬된다고 한다. 이말인즉, 다음과 같은 공식이 성립한다는 것이다.

" 클러스터 인덱스 필드값들의 순서 == 레코드의 물리적 배열 순서 " 

즉 인덱스 키값 순서대로 레코드가 자동으로 소트되어 저장되도록 보장한다는 것이 클러스터 인덱스의

기본 개념이다. 따라서 당연히 클러스터 인덱스는 테이블 당 하나만 존재한다.

하나의 테이블을 두가지 이상의 방식으로 동시에 물리적으로 정렬한다는 것은 불가능하기 때문이다.

여기서 클러스터 인덱스로 하면 합당한 필드의 성질이 자동으로 결정된다.

레코드 추가 순서대로 필드값이 계속 증가 혹은 감소하는 필드에 대해서, 클러스터 인덱스로 지정하는 것이 바람직하다는 것이다. 이러한 대표적인 필드의 예를 들면 자동증가(Auto increment) 필드이다.

이 경우, 레코드가 추가될 때 마다 물리적으로 재배치하는 작업이 아주 적게 발생하기 때문이다.

클러스터 인덱스인 경우, 기존의 인덱스 키 값들보다 너무 크거나 작은 값이 제멋대로 들어올 경우,

기존 레코드의 물리적 순서를 완전히 재배치해야 하는 문제가 발생한다.

이 과정에서 Page Splitting라고 하는 매우 속도를 깨어먹는 작업이 수반된다.

클러스터 인덱스는 레코드 순서 자체가 인덱스이므로, 인덱스를 저장하기 위한 별도의 페이지를 할당할

필요가 없다. 이 말인즉, 클러스터 인덱스를 사용하는 쿼리를 실행할 경우, 인덱스가 저장된 페이지에서

쿼리 조건에 해당하는 레코드 위치 자료를 얻어내는 작업이 생략된다는 것이다.

그래서 일반 인덱스에 비해서 검색 속도가 빠른 것이다.

그렇다면 클러스터 인덱스의 두번째 특성이 여기서 등장한다.

csfield을 클러스터드 인덱스 필드라고 가정할 경우, where조건문에 csfield가 등장한다면,

equal 조건 검색보다는 range 검색에 더 효과를 발휘한다는 것이다.

// 아래처럼 어떤값과 동등한가를 찾는 쿼리보다는
쿼리1) select * from table1 where csfield = 10

// 아래처럼 특정 범위 검색이 잦은 필드가 클러스터 인덱스에 합당하다.
쿼리2) select * from table1 where csfield >= 10 and  csfield <= 20
쿼리3) select * from table1 where csfield between 10 and 20

클러스터 인덱스를 설정해야할 대표적인 필드의 예로, 매출일자, 매입일자 등을 들 수 있다.

매출일자와 매입일자는 그 특성상 레코드가 추가될 때 거의 증가하는 방향이다.

오늘이 1월 1이면 거의 대부분이 1월1일자의 매출일자로 추가되지 과거나 미래 날자로 매입 혹은 매출일자를 발생 시킬 경우는 거의 없다는 것이다. 또한 매출과 매입일자는 매우 빈번한 범위 검색의 대상이 된다.

매출과 매입일자는 한번 기록하면, 수정할 일이 거의 없는 필드이기도 하다.

즉, 레코드 재배치가 일어날 가능성이 가장 적은 필드에 해당한다는 것이다.

결론적으로, PK는 그 특성상 항상 증가 혹은 감소하는 필드도 아니며 임의의 순서대로 들어오는 경우가

더 많으며, PK 필드는 대부분의 검색이 equal 비교이지, range(범위) 검색이 아니다.

따라서 대부분의 PK는 클러스터 인덱스로 설정해서 별로 얻는 바가 없다 할 수 있다.

개발할 때 어떤 개념이나 방식을 사용하고자 한다면, 그것이 근거가 있는 것인지 항상 파악하려는 자세가

필요하다. 카피캣(CopyCat)처럼 남들이 맹신하는 방식으로 하면  결코 즐겁지 못하다. 속도가 도리어

떨어지는 최적화를 해 놓고도, 델파이나 MS SQL의 버그라고 우기거나, 사서 고생하는 코딩

즉, 삽질지수 어마어마한 코딩을 하게 될수도 있기 때문이다.

개발에서 떠도는 오래된 경험자들의 속담이 있다.
"쓸데없이 많이 아는 체 하려 하지 말고 하나라도 제대로 이해하고 사용하도록 노력하라!"

 

 

 

 

 

최적의 성능을 위한 인덱스의 선택 

 

 

인덱스를 선택하는 것은 많은 SQL Server DBA와 개발자에게 신비로운 대상이다.

물론 우리는 인덱스가 어떤 일을 하며 어떻게 성능을 향상시키는지를 잘 알고 있다.

문제는 이상적인 인덱스 종류와(클러스터드 vs. 넌클러스터드), 인덱스에 포함시킬 컬럼의 개수

(다중 컬럼 인덱스가 필요한지 여부), 그리고 어떤 컬럼에 인덱스를 만들 것이냐 하는 점이다.

여기서는 이 질문에 대한 답변을 간략하게 제공하고자 한다.

불행하게도, 각각의 경우에 대한 절대적인 해답은 존재하지 않는다.

다른 성능 튜닝 및 최적화와 마찬가지로 이상적인 인덱스를 찾으려면 직접 테스트하는 수고를

감수해야 한다.

 

이제 인덱스 생성에 있어서의 보편적인 지침을 먼저 살펴보고 다음에는 클러스터드 인덱스와

넌클러스터드 인덱스를 선택하는 기준에 대해서 자세히 알아보도록 하겠다.

인덱스가 너무 많아서 문제가 될 수도 있는가?

정답은 “그렇다” 이다.

어떤 사람들은 모든 컬럼에 인덱스를 걸면 성능 문제가 모두 해결될 것이라고 생각하기도 하는데

실제로는 전혀 그렇지 않다. 인덱스가 데이터 액세스 속도를 증가시키는 것과 마찬가지로 잘못 선택하면

오히려 액세스 속도를 저하시킬 수도 있다.

 

인덱스가 많음으로 해서 발생하는 문제는 INSERT, UPDATE, DELETE가 발생할 때 마다 SQL Server가

인덱스를 유지하기 위한 작업을 해야만 한다는 것이다.

 

하나의 테이블에 한 두개 정도의 인덱스를 관리하는 것은 SQL Server가 처리하기에 별 부담이 없지만

네 개, 다섯 개 혹은 그 이상의 인덱스를 만들게 되면 성능에 큰 장애 요인으로 작용하게 된다.

 

가능하다면 인덱스를 적게 만드는 것이 좋으며 최적의 성능을 얻기 위해서 적절한 개수의 인덱스를

만드는 것이 요구된다.

단지 인덱스를 추가하는 것이 좋을 것 같다는 생각만으로 무조건 인덱스를 만드는 것은 좋지 않다.

해당 테이블을 이용하는 쿼리에서 사용될 것이 확실한 경우에만 인덱스를 추가해야 한다.

어떤 쿼리가 실행될지 알 수 없다면 확실히 알기 전까지는 아무 인덱스도 만들지 않는 편이 낫다.

어떤 쿼리가 실행될지 추측하고, 거기에 따라서 인덱스를 생성하면 결국 애초의 추측이 틀렸음을

알게 되는 경우가 많기 때문이다.

 

실행될 쿼리의 유형을 먼저 알아야 하고 그 다음에 가장 적절한 인덱스를 만들기 위한 분석 과정을

거친 후에야 비로소 인덱스를 생성하고 실제로 효과가 있는지 테스트 해야 한다.

OLTP 프로그램은 INSERT, UPDATE, DELETE 작업이 매우 많기 때문에 최적의 인덱스를 선택하기 위한 판단을 내리기가 쉽지 않다. SELECT, UPDATE, DELETE 할 레코드를 신속하게 찾기 위해서는 인덱스가 필요하지만 인덱스가 너무 많으면 INSERT, UPDATE, DELETE 작업이 실행될 때 과도한 오버헤드가

발생하게 된다.

 

한편, 주로 읽기 작업이 많은 OLAP 프로그램의 경우에는 INSERT, UPDATE, DELETE 작업에 대해서

걱정할 필요가 없기 때문에 필요한 만큼 인덱스를 만들어도 상관 없다.

이와 같이 인덱스 전략을 수립할 때에는 응용프로그램이 어떻게 사용될 것인가가 큰 차이를 가져오게 된다.

인덱스를 선택할 때 고려해야 할 사항을 하나 더 들자면 선택한 인덱스가 SQL Server 쿼리 옵티마이저에 의해서 사용되지 않을 수도 있다는 점이다. 

 

쿼리 옵티마이저가 인덱스를 사용하지 않기로 결정하였다면 인덱스는 SQL Server에게 부담이 될 뿐이기 때문에 아예 삭제하는 편이 낫다. 그렇다면 SQL Server 쿼리 옵티마이저에서 인덱스가 존재함에도 항상 인덱스를 사용하지는 않는 이유는 무엇일까?

여기서 자세하게 답변하기에는 너무 광범위한 내용이지만 한마디로 요약하자면 SQL Server가 인덱스를 사용하는 것 보다 테이블 검색을 수행하는 편이 더 빠르다고 판단하는 경우가 있기 때문이라고 할 수 있다. 

 

여기에는 두 가지 경우가 있는데 하나는 테이블의 크기가 작은 경우이고(레코드 수가 적음)

또 다른 하나는 인덱스가 걸린 컬럼의 유일한 값이 95%보다 적기 때문이다. 

 

SQL Server가 인덱스를 사용하지 않을지를 어떻게 알 수 있을까?

이에 대한 답변은 SQL Server 쿼리 애널라이저를 사용하는 방법을 살펴본 다음에 알아보기로 하겠다.

 

 

 

 

클러스터드 인덱스를 선택하는 방법

 

하나의 테이블에는 하나의 클러스터드 인덱스만을 만들 수 있기 때문에 어떻게 사용할 것인가를

신중하게 생각해야 한다. 실행할 쿼리의 유형을 고려해 보고 어떤 쿼리가 가장 중요하며
클러스터드 인덱스를 사용했을 때 도움이 될지를 추정해 보도록 한다.

일반적으로는 클러스터드 인덱스를 만들 컬럼을 선택할 때에는 다음과 같은 기준을 사용한다.

• 테이블의 프라이머리 키를 항상 클러스터드 인덱스로 만들어서는 안 된다. 프라이머리 키를 만든 다음에 특별히 지정을 하지 않으면 SQL Server는 자동으로 프라이머리 키를 클러스터드 인덱스로 만든다. 프라이머리 키가 다음과 같은 조건에 부합될 경우에만 클러스터드 인덱스로 만들도록 한다.
  
  
  
• 클러스터드 인덱스는 일정한 범위의 값에 대해서 쿼리를 실행하거나 정렬된 결과를 필요로 할 ‘때 가장 좋은 방법이다. 왜냐하면 데이터가 이미 인덱스 내에서 정렬되어 있기 때문이다. 예를 들면 쿼리에서 BETWEEN, <, >, GROUP BY, ORDER BY, 그리고 MAX, MIN, COUNT와 같은 집합 함수를 사용하는 경우가 여기에 해당된다.
  
  
  
• 클러스터드 인덱스는 유일한 값을 가지고 레코드를 검색하는 쿼리를 사용하거나(예를 들면 직원 번호) 레코드의 거의 모든 데이터를 가져오고자 할 때 효과적이다. 왜냐하면 이 쿼리는 인덱스만으로 처리가 가능하기 때문이다.
  
  
  
• 클러스터드 인덱스는 국가 또는 주(state) 데이터와 같이 유일한 값이 제한적인 컬럼을 액세스 하는 쿼리에 효과적이다. 그러나 컬럼의 데이터가 예/아니오, 남성/여성 처럼 매우 적은 유일한 값을 갖고 있으면 이 컬럼에는 인덱스를 걸지 않는 것이 좋다.
  
  
  
• 클러스터드 인덱스는 JOIN 또는 GROUP BY 절을 사용하는 쿼리에 효과적이다.
• 클러스터드 인덱스는 매우 많은 레코드를 반환하는 쿼리를 사용할 때 효과적이다. 왜냐하면 데이터가 인덱스에 모두 들어 있으므로 다른 곳에서 찾아올 필요가 없기 때문이다.
  
  
  
• 테이블에 INSERT 작업이 많다면 아이디, 날짜 등과 같이 증가하는 컬럼에는 클러스터드 인덱스를 만들지 않아야 한다.왜냐하면 클러스터드 인덱스는 데이터를 물리적으로 정렬되도록 유지하기 때문에 증가하는 컬럼에 생성된 클러스터드 인덱스는 새로운 데이터를 테이블의 동일한 페이지에 삽입하여 테이블이 과도하게 사용됨으로써 I/O 병목현상을 유발할 수 있다. 이 때에는 클러스터드 인덱스로 사용할 다른 컬럼을 찾아보는 것이 좋다.

 

한 가지 난처한 문제는 클러스터드 인덱스를 만들 필요가 있는 컬럼이 하나 이상인 경우가 있다는 것이다.
그러나 하나의 테이블에는 오직 하나의 클러스터드 인덱스만을 생성할 수 있기 때문에 모든 가능성을

평가한 다음에 가장 효과적일 것으로 판단되는 하나만을 선택해야 한다.

 

 

 

넌클러스터드 인덱스를 선택하는 방법

넌클러스터드 인덱스는 테이블에 원하는 만큼 생성할 수 있기 때문에 클러스터드 인덱스를 선택하는 것보다 비교적 쉽다. 다음은 넌클러스터드 인덱스를 생성할 컬럼을 선택하는 방법이다.

• 넌클러스터드 인덱스는 반환되는 레코드의 개수가 적은 쿼리(하나의 레코드 포함)와 인덱스의 선택도가 높은 경우에(95% 이상) 사용하면 좋다.
  
  
  
• 컬럼의 데이터가 95% 이상의 유일한 값을 갖지 않는다면 SQL Server 쿼리 옵티마이저는 해당 컬럼에 생성되어 있는 넌클러스터드 인덱스를 사용하지 않을 가능성이 높다. 따라서 적어도 95% 이상 유일한 값을 갖지 않는 컬럼에는 넌클러스터드 인덱스를 만들지 않는 것이 좋다. 예를 들어 “예”, “아니오”를 데이터로 갖는 컬럼은 95% 이상 유일해야 한다는 조건에 부합되지 않는다.
  
  
  
• 인덱스의 폭(width)을 가능한 좁게 유지한다. 특히 복합(다중 컬럼) 인덱스를 만들 때는 더욱 그러하다. 이렇게 하면 인덱스의 크기가 감소하기 때문에 인덱스를 읽을 때 필요한 읽기 횟수도 줄어들므로 성능 향상을 얻을 수 있다.
  
  
  
• 가능하다면 문자보다는 정수 값을 가진 컬럼에 인덱스를 만들어야 한다.
  정수 값은 문자보다 처리하는데 필요한 오버헤드가 작다.
  
  
  
• 응용프로그램이 같은 테이블에 대해서 동일한 쿼리를 반복해서 실행한다면 해당 테이블에 커버링 인덱스를 만들어 보도록 한다. 커버링 인덱스는 쿼리에서 사용하는 모든 컬럼을 포괄하는 인덱스이다. 인덱스에 찾고자 하는 데이터가 모두 들어 있기 때문에 SQL Server는 데이터를 찾기 위해서 테이블을 참조할 필요가 없으며 결과적으로 논리적 및 물리적 I/O가 감소하는 장점이 있다.
  그러나 인덱스가 과도하게 커지면(컬럼이 너무 많아서) I/O가 증가하여 성능이 떨어질 수도 있다.
  
  
  
• 인덱스는 쿼리의 WHERE 절이 인덱스의 가장 왼쪽의 컬럼과 일치할 때에만 사용된다. 따라서 “City, State”와 같은 복합 인덱스를 만들었다면 WHERE City=’Houston’과 같은 쿼리는 인덱스를 사용하겠지만 WHERE STATE=’TX’는 인덱스를 사용하지 않는다.

일반적으로 테이블에 하나의 인덱스만 필요하다면 클러스터드 인덱스로 만드는 것이 좋다.
그러나 테이블에 하나 이상의 인덱스가 필요한 경우에는 넌클러스터드 인덱스를 만들 수 밖에 없다.
위의 내용을 충실하게 따른다면 최적의 인덱스를 선택할 수 있을 것이다.