인덱스 간단 정리(개념, 인덱스컬럼 결정)

 

프로젝트 개발을 하면서 데이터베이스를 다루는 작업을 많이 하게 된다. 무작정 쿼리를 막 날리면 성능상 좋지 않다는 것을 느끼게 될 것이다. 데이터베이스 성능을 향상하는 것이 매우 중요한데 그중에서 인덱스(index)라는 것은 성능 향상에 있어서 필수적이다. 

 

인덱스에 대해 간단하게 설명하자면 데이터들을 어떤 값들을 기준으로 데이터 블록을 나열한 목차라고 생각하면 된다. 아래의 사진을 한번 봐보자. 

 

왼쪽의 표는 Content를 기반으로 생성한 인덱스, 오른쪽의 표는 테이블 생성 시 기본적으로 생성되는 Primary Index이다. 클러스터 인덱스라고도 하며 데이터의 위치를 결정하는 키 값이라고도 할 수 있다.

왼쪽의 Content 인덱스를 통해 Id를 알고 Id를 통해 데이터 주소로 가서 데이터를 조회하는 것이 바로 랜덤 엑세스이다. 

만약 Content 인덱스가 없다고 생각해 보자. 나는 Content 값이 '내용 4'인 데이터를 조회하고 싶다. 하지만 오른쪽의 테이블만 보고는 content 값을 파악할 수 없다. 결국 id 순서로 1부터 4까지 조회하고 나서야 내용 4를 찾게 되는 것이다. '내용4'가 있는데 데이터는 id가 4인 가장 마지막 블록인데 이 하나의 블록을 찾기 위해 4회나 랜덤 엑세스를 한 것이다.

만약 '내용4'가 하나의 값만 있는 것이 아니라 중복이 허용되는 값이면 여러 블록을 찾아야 하는데 이를 위해선 전체 데이터를 랜덤 엑세스하는 수 밖에 없다. 이렇게 전체 데이터를 조회하는 것을 '테이블 스캔' 이라고 한다.

 

이러한 불필요한 랜덤 엑세스를 줄이기 위한 방법 중 하나가 인덱스이다.

이번엔 Content를 기반으로 인덱스를 생성했다고 하자. 똑같이 '내용4'를 찾으려고 보니까 이미 content가 순서대로 나열이 되어있다. 우리는 '내용4'가 있는 데이터 블록의 id 값을 알았고 이 id 값을 통해 클러스터 인덱스에 접근하여 나와있는 데이터 주소로 접근하여 원하는 데이터 만을 얻을 수 있게 되는 것이다. 

데이터가 많을 수록 더 큰 성능 향상을 이룰 수 있을 것이다!

물론 인덱스가 장점만 있는 것은 아니다. 인덱스가 만들어진 테이블에 데이터를 생성하거나 삭제할 때 인덱스에서도 그 데이터를 제외시켜야 하니까 추가적인 비용이 발생하게되고 이는 데이터가 많을 수록 비용이 커지게 된다. 

또한 인덱스에서 원하는 값을 찾는 것 또한(위에서는 내용4를 찾는 것) 비용이 발생한다. 이는 여러가지 방식이 있지만 일반적으로 B-tree 방식을 사용한다고 한다. 이에 대해서는 아래의 링크를 참고하길 바란다.

https://khdscor.tistory.com/47

인덱스의 유형과 특징(1. B-Tree index)

 

그렇기에 찾으려는 데이터의 수가 전체 데이터의 20~30%만 넘어가도 테이블 스캔이 성능상 더 이점이 있다고 한다. 무조건 인덱스를 통한 조회가 이점이 있다고는 생각하지 말자.

 

 

이렇게 인덱스에 대해 간단히 알아보았다.

이제부턴 '개발자를 위한 인덱스 생성과 SQL작성 노하우(이병국)'이라는 책을 참고하여 인덱스에 대한 간단한 개념과 인덱스 후보컬럼 결정 방식 및 결합 인덱스의 결합 순서를 결정하는 방식을 설명하겠다.

 

인덱스라는 데이터베이스를 다뤄봤다면 많이 들어봤을 것이다. 흔히 '테이블의 레코드를 쉽게 찾을 수 있게 해주는 책의 목차와 같다', '인덱스는 컬럼을 기준으로 정렬을 미리 해놓은 것이므로 특정 컬럼을 기준으로 정렬을 할 때 성능 개선을 할 수 있다', '안덱스는 SQL 명령문의 처리 속도를 향상하기 위해 컬럼에 대해서 생성하는 객체다' 등 많이 들어봤을 것이다. 확실히 맞는 말이다. 하지만 여기선 추가로 분류라는 개념으로 인덱스를 접근해 보고자 한다.

위에서 언급한 책을 읽기전에는 단순히 인덱스를 목차라는 개념으로만 이해하고 있었다. 하지만 위 책에서 설명한 방식을 읽으면서 '인덱스는 분류'라는 것을 터득했고 책에서 설명한 내용을 간단히 써보려고 한다. 

 

 

분류는 물리적 분류와 논리적 분류로 나눌 수 있다. 물리적 분류는 말 그대로 시각적으로 물리적인 것들을 분류하는 것이다. 책을 책장에 순서대로 넣고 싶을 때 분류를 한다면 이것은 물리적 분류라고 할 수 있는 것이다. 하지만 만약 어느 특정 음반들을 가수 별, 테마별, 연도별, 국가별 등 다양한 분류를 하고 싶다면 어떨까? 음반 하나를 복제하지 않는 이상 동일한 시공간에서 물리적으로는 이 모든 분류를 만족시키는 물리적 분류는 존재하지 않을 것이다. 여기서 나오는 것이 논리적 분류이다.

물리적 분류는 분류 대상과 분류 정보가 1:1의 관계에 있고 일체형이다. 하지만 음반 분류는 분류 대상과 분류 정보가 1:1이 아닌 1:N의 관계이다. 음반을 가수별, 테마별 연도별, 국가별  등 여러 분류로 나눌 수 있어야 한다. 이러한 분류가 동시에 가능하게 하는 방법은 분류 대상과 분류 정보를 분리하는 것이다. 이것이 논리적 분류이며 디지털 세계에서 대량의 복잡한 자료를 다양하게 분류하는 데 매우 적합하다.

 

DB에서는 인덱스가 논리적 분류의 특성을 갖고 있다.  인덱스는 DBA보다는 개발자에게 더 중요한 개념이다. DBA에게 인덱스는 DB에서 관리해야 할 여러 요소 중에서 하나지만, 개발자에게 인덱스는 빈번하게 사용하는 DB의 핵심 요소이기 때문이다. 

논리적 분류는 간접적이고 추상적인 분류 방식이다. 분류 대상과 분류 정보를 분리하는게 가장 적합하며 인덱스에서는 분류 대상은 테이블이고, 분류 정보는 인덱스이다. 인덱스는 DB에서 데이터를 검색할 때 검색되는 데이터 수를 줄여, 성능을 높이기 위해 지정하는 식별자다. 데이터의 논리적 분류 정보를 갖고 있으며 DB의 여러 요소 중에서 가장 중요한 부분이기도 하다.

인덱스는 기준으로 정한 컬럼으로 정렬을 분류 정보이고 해 놓은 것이고 기준 컬럼은 여러 개로 선정 가능하다. 

참고로 테이블을 삭제할 때 뷰는 자동으로 삭제되지 않는다. 그래서 테이블과 뷰의관계는 종속 관계가 아니다. 뷰는 테이블의 객체 정보를 갖고 있으므로 굳이 함께 삭제할 이유가 없다. 하지만 인덱스는 테이블의 위치 정보를 갖고 있으므로, 테이블 삭제 시 자동으로 삭제돼야 하는 것이다. 

 

 

 

개발자에게 중요한 것은 인덱스의 목적이나 기능이 아니다. 인덱스 생성 구문도 아니다. 인덱스를 만들고 관리하는 일은 DBA가 한다. 개발자는 인덱스를 어떻게 잘 만들 것인지가 중요하다. 즉 어떻게 잘 분류할 것인지를 고민하는게 중요하다. 

그렇다면 인덱스 컬럼의 선정 기준은 어떻게 되는가? 

인덱스는 데이터베이스에서 매우 중요한 부분이기에 잘못된 인덱스가 문제가 돼서 시스템 성능 저하나 장애를 불러올 수 있으므로 개발자 입장에서도 매우 중요하다. 그렇기에 인덱스를 잘 만들어야 한다. 

그렇기에 먼저 고려해야할 건 인덱스 대상 후보컬럼을 선정하는 것이다. 

테이블에 존재하는 수많은 컬럼을 모두 인덱스 컬럼으로 사용할 수 있는 것은 아니다. 조건절이나 조인 절에 사용하는 컬럼을 인덱스 컬럼으로 정하는 것이 좋다. 책에 따르면 아래 5가지 기준에 따라서 인덱스 대상 후보 컬럼을 결정한다고 나온다. 

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

 

분포도가 좋은 컬럼인가?

분포도란 전채 레코드에서 식별 가능한 수에 대한 백분율을 의미한다. 예를 들면 성별컬럼은 남자, 여자만 있으니 분포도는 50%이다. 나이 컬럼이 1~100이라하면 분포도는 1%이다. 식별 가능한 수가 클수록 분포도는 낮으며, 분포도가 낮을수록 분포도가 좋다고 한다. 분포도가 좋은 컬럼은 인덱스 후보 컬림이 될 수 있다. 그렇다고 무조건 분포도에만 의존하는 것은 좋지 않다. 예를 들면 고객테이블에서 생년월일컬럼은 분포도가 (1/100 * 365)) * 100 = 0.002739 정도 되는데 그렇다고 조건절에 사용될 만큼 중요한 컬럼이 아니다. 

그리고 주문 테이블에 '배송여부' 컬럼이 있다고 가정하자. 배송여부 컬럼의 식별 가능한 수는 Y와 N으로 분포도가 50%이다. 분포도가 좋은 편은 아니지만 실제 테이블에 레코드의 배송여부 값은 대부분 Y이다. 극 소수의 값만 N일 것이다. 논리적으로 판단하면 분포도가 좋지 않으나, 물리적인 실제 값을 확인했을 때는 배송여부 컬럼 값에 따라 분포도가 극단적으로 쏠림을 알 수 있다. 만약 아래와 같은 쿼리라면 배송여부 컬럼은 인덱스 후보 컬럼으로서 자격이 충분하다. 

 

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

 

 

갱신이 자주 발생하지 않는 컬럼인가?

인덱스 컬럼이 자주 삽입되면, 인덱스 밸런싱이 깨진다. 테이블에 처리 작업을 할 때, 인덱스에 대한 정보도 반영해야 하기 때문이다. 자세한 사항은 https://khdscor.tistory.com/47를 참고하길 바란다. 

인덱스의 유형과 특징(1. B-Tree index)

 

그렇기에 가급적이면 update 항목에서 사용하는 컬럼은 인덱스 후보컬럼에서 배제하는 것이 좋다. 

 

 

조건절에서 자주 사용되는 컬럼인가?

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

아래의 사진을 한번 봐보자.

 

요약하면

쿼리 일일 구동 횟수: 쿼리 3(1000번) > 쿼리 1(100번) > 쿼리 2(10번)

조건절에서 자주 사용: 칼럼 3(3회) > 컬럼2(2회) = 컬럼4 = 컬럼6 > 컬럼1(1회)= 컬럼5 = 컬럼7

만약 위와 같은 상황이면 아래와 같이 결합 인덱스를 생각할 수 있다.

결합인덱스 = 컬럼3 + 컬럼4 + 컬럼6 + 컬럼7

가장 많이 사용되는 쿼리 3을 기준으로 인덱스를 만들고 다른 쿼리도 범용적으로 사용할 수 있도록 조건절에서 자주 사용하는 칼럼을 결합 인덱스의 선행으로 둔 것이다. 

 

 

조인의 연결고리에 사용되는 컬럼인가?

인덱스는 조건절에서 사용되기도 하고 조인 절에서 사용되기도 한다. 조건절에서 사용하는 인덱스는 최초로 접근하는 테이블을 결정하는 중요한 인덱스이며, 데이터 접근 범위를 줄여주는 역할을 한다. 일반적으로 조인의 방법에는 아래와 같이 3가지가 있다.

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

 

인덱스 존재 여부에 따라 옵티마이저는 어느 조인을 택할지에 대해 선택할 수 있는 것이다.

 

소트 발생을 제거하는 컬럼인가?

인덱스는 기본적을 위치 정보 + 순서 정보로 구성된다. 인덱스는 조건절이나 조인 절 말고도 ORDER BY 절에 있는 컬럼도 인덱스 후보컬럼으로 사용할 수 있다. 만약 ORDER BY절에 사용되는 컬럼으로 인덱스를 생성하면 소트 즉, 정렬이 발생하지 않고 데이터를 정렬된 상태로 가져올 수 있다. 그렇다고 항상 ORDER BY절에 사용되면 생성하는 것은 아니다. 인덱스를 생성할 때 발생하는 부하와 SORT 부하를 비교해 보고 결정한다. 만약 SORT부하가 더 크다면 인덱스 후보로서 생각할 수 있는 것이다. 

아래 쿼리를 한번 봐보자. 

 

SELECT *
FROM 주문
WHERE 상품코드 = '텐트'
AND 배송여부 = 부
ORDER BY 주문일자 ASC

 

여기서 결합 인덱스를 생성하는 건 다음 두 가지 방식일 것이다.

1. 상품코드(위치) + 배송여부(위치)
2. 상품코드(위치) + 배송여부(위치) + 주문 일자

소트 부하가 인덱스 부하보다 크면 두 번째 방식을 선택하고 아니라면 첫 번째 방식을 선택하는 것이다.

하지만 이를 비교하는 명확한 수치는 구할 수 없고 데이터베이스에서는 일반적으로 가져온 데이터를 정렬(소트)하는 부하가 훨씬 더 크므로 ORDER BY절 컬럼은 인덱스 후보컬럼으로 중요하게 사용되는 경우가 많을 것이다. 

 

참고

개발자를 위한 인덱스 생성과 SQL 작성 노하우 - 이병국