권순용의 DB 이야기
SQL의 성능은 처리 범위에 좌우된다 9 13 18,859

by axiom 결합칼럼 인덱스 결합 인덱스 인덱스 SQL튜닝 index [2012.12.15]


데이터베이스가 대용량으로 변화면서 우리는 항상 성능을 고민하게 된다. 이는 많은 데이터 중 우리가 원하는 데이터를 추출하는 것이 말처럼 쉽지만은 않기 때문이다.

이와 같은 현상은 데이터가 증가하면 증가할수록 더욱 심해질 것이다. 그래서 대용량의 데이터에서 우리가 원하는 적은 데이터를 추출하기위해 인덱스를 이용하게 된다.

보통 인덱스는 결합 칼럼 인덱스나 단일 칼럼 인덱스로 구성할 수 있다. 그렇다면 과연 우리는 어떤 형태의 인덱스를 선택해야 할까?

결론부터 말하자면 대부분의 SQL에서는 결합 칼럼 인덱스를 선택하는 편이 좋다. 이제부터 그 이유에 대해 알아보자.

SQL의 성능은 처리 범위에 좌우된다

SQL의 성능은 무엇에 의해 좌우되는가? 여러 가지 요소에의해 SQL의 성능은 좌우될 것이다. SQL의 성능은 다음의 세가지 항목에 의해 좌우된다.

  • - 처리 범위의 양
  • - 랜덤 액세스의 양
  • - 정렬의 양

그럼 처리 범위가 적다는 뜻은 무엇을 의미할까? 처리 범위가 적다는 말은 액세스해야 하는 데이터가 적다는 것을 의미한다.

액세스해야 하는 데이터가 적다면 우리는 인덱스를 이용하여 성능을 보장 받을 수 있을 것이다. 이는 마치 우리가사전에서 GIRL이라는 단어를 찾을 때 사전의 인덱스를 이용하여 찾는 것과 같을 것이다.

다음 예제를 확인해 보자.

SELECT 카드번호, 사용액
  FROM 거래내역
 WHERE 거래일자 = '200803'
   AND 사용_구분 = '정상'

여기에서 인덱스에 의한 처리 범위를 확인해 보자. 그렇다면 위의 SQL을 위해 생성할 수 있는 인덱스의 종류는 어떻게 되는가?

  • - 거래일자 인덱스
  • - 사용_구분 인덱스
  • - 거래일자 + 사용_구분 인덱스
  • - 사용_구분 + 거래일자 인덱스

거래일자 인덱스

거래일자 인덱스를 이용한다면 처리 범위는 거래일자 칼럼에 의해서만 감소하게 된다. 따라서, 거래일자 칼럼의 값이'200803'인 데이터를 모두 액세스할 것이다.

액세스한 데이터에 대해 사용_구분 칼럼의 값이'정상'인 데이터만을 결과로 추출하게 된다.

결국, 거래일자 칼럼에 의해서만 처리 범위가 감소하게 되며 사용_구분 칼럼에 의해서는 처리 범위가 감소하지 않게 된다.

사용_구분 인덱스

사용_구분 칼럼으로 인덱스를 생성하면 사용_구분 칼럼의값이'정상'인 모든 데이터를 액세스한다.

거래일자 칼럼의값은 처리 범위를 감소시키기 위한 어떠한 역할도 수행하지않게 된다.

결국, 사용_구분 인덱스로 인덱스를 생성하게 되면 사용_구분 칼럼의 값만으로 처리 범위가 감소하게 된다.

거래일자 + 사용_구분 인덱스

해당 인덱스는 거래일자 칼럼의 값이'200703'인 데이터중 사용_구분 칼럼의 값이'정상'인 데이터만 액세스한다.

거래일자+사용_구분 인덱스를 이용하는 순간 처리 범위는 거래일자 칼럼에 의해 감소하게 되며 감소된 처리 범위에서 사용_구분 칼럼의 값이'정상'인 데이터만을 액세스하게 된다.

거래일자 칼럼과 사용_구분 칼럼에 의해 처리 범위가 감소한다. 따라서, 앞서 언급한 단일 칼럼 인덱스에 비해 처리 범위가 더 많이 감소하게 되므로 성능은 더욱 향상될 것이다.

사용_구분 + 거래일자 인덱스

세 번째 인덱스와 마찬가지로 사용_구분 칼럼의 값이'정상'인 데이터 중에 거래일자 칼럼의 값이'200803'인 데이터만을 추출하게 된다.

따라서, 해당 인덱스도 사용_구분 칼럼과 거래일자 칼럼에 의해 동시에 처리 범위가 감소하게 된다.이와 같이 결합 칼럼 인덱스를 생성한다면 처리 범위를 더 많이 감소시킬 수 있게 된다.

이와 같은 이유에서 단일 칼럼 인덱스보다는 결합 칼럼 인덱스를 생성하는 것이 해당 SQL에대해 더 빠른 성능을 기대할 수 있게 한다.

WHERE 절에 사용되는 연산자

그렇다면 WHERE 조건에 존재하는 모든 칼럼으로 순서에 상관없이 결합 칼럼 인덱스를 생성하면 인덱스를 구성하는 모든 칼럼에 의해 처리 범위가 감소하게 되는가?

당연히 그것은 아니다. 이를 이해하려면 WHERE 절에 사용하는 연산자의 종류를 이해해야 한다.

- 점 조건 : IN, = 연산자를 이용한 조건을 의미하며 해당 연산자는하나의 점만을 의미하게 된다.

- 선분 조건 : LIKE, BETWEEN, <, > 등과 같이 점 조건을 제외한연산자를 사용한 조건을 의미한다. 선분 조건은 하나의 점만을 의미하는 것이 아니면 해당 조건을 만족하는 모든 실수를 의미하게 된다.

WHERE 절에 사용하는 조건은 점 조건과 선분 조건으로구분된다. 이와 같이 조건에 사용된 연산자에 의해 액세스해야 하는 처리 범위의 차이가 발생한다.

  • - 점 조건 + 점 조건 : 두 조건에 의해 처리 범위 감소
  • - 점 조건 + 선분 조건 : 두 조건에 의해 처리 범위 감소
  • - 선분 조건 + 선분 조건 : 앞의 선분 조건에 의해 처리 범위 감소
  • - 선분 조건 + 점 조건 : 앞의 선분 조건에 의해서만 처리 범위 감소

이 내용은 간단하지만 매우 중요한 의미를 내포하고 있다. 예를 들어 확인해 보자.

SELECT 카드번호, 사용액
  FROM 거래내역
 WHERE 거래일자 > '200803'
   AND 사용_구분 = '정상'

이런 SQL을 수행한다면 앞서 언급한대로 거래일자 조건은 선분 조건이며 사용_구분 조건은 점 조건이 된다. 따라서, 해당 SQL에 대해 최적의 인덱스를 이용하고자 한다면 사용_구분+거래일자 인덱스를 생성해야 한다.

이와 같이 인덱스를 생성한다면 점 조건+선분 조건으로 구성되므로 두 개의 조건에 의해 처리 범위가 감소하게 된다.

이와 같은 현상이 왜 발생하는지는 다음 강좌에서 자세히 언급하도록 하겠다. 인덱스에서 가장 중요한 요소는 처리 범위를 최소화 시킬 수 있어야 한다는 것이다.

그 중심에는 결합칼럼 인덱스가 존재한다는 것을 명심하길 바란다. 또한, 결합칼럼 인덱스를 생성하고자 한다면 점 조건과 선분 조건의 순서에 의해 처리 범위가 변한다는 것에 주의해야 할 것이다.

- 강좌 URL : http://www.gurubee.net/lecture/2228

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 아발란체 [2012.12.21 11:12:58]

보물 같은 자료네요.

근데!
대부분의 SQL에서는 복합 인덱스가 좋다는 말은... 문제의 소지가 있는 것 같습니다.
검색이 없는 Unique 아이디로 CRUD를 하는 시스템에서는 Unique Sequence Value가 유리하며
게시판 같은 테이블에서도 동일하며
검색 키워드에 대한 단일 또는 복합 인덱스를 따로 두는 것이 보다 효율적인 경우도 있기 때문에
무조건 게시판 같은 것을 만들 때 복합키가 있어야 바람직하다고 생각하지는 않습니다.
오히려 어떤 경우 기본키가 복합키로 구성되어 값들 노출 위험이 높아지고
관리 포인트만 많아질 수도 있다는 것도 염두해야할 것 같습니다.


by 손님 [2013.02.06 11:07:32]
1 그래서 그런 몇몇 특별한 경우말고 대부분의 경우라고 말씀하시는듯 하네요

by 마농 [2013.02.06 12:07:04]

PK 설정 측면만 본다면 아발란체님 말이 맞지만...
이글은 PK 설정이 아닌, 인덱스 설정을 이야기 하고 있네요.
PK 와 인덱스를 동일시하면 안되죠.


by 김한식 [2013.10.18 22:55:54]
배울게 많네요. 감사합니다.

by 윤영헌 [2013.11.20 14:19:39]

데이터를 추출할때 추출범위에 대한 조건을 설정할때 인덱스 구성을 어떻게 해야 하는지를 말해주는 것 같습니다. RDB를 처음 접하는 분들께는 유용한 내용인거 같습니다.

by 신화씨앤씨 [2014.02.19 21:18:16]

배울게 많아서 감사합니다


by 참된신자 [2014.07.30 16:19:15]

감사합니다. :D


by 헬로헬로일 [2014.10.12 03:28:43]

잘보고가용


by 무파사 [2015.01.27 09:10:04]

많이 배우고 갑니다 ~


by 문수영 [2015.03.19 17:46:13]

감사합니다~~ 잘봤습니다.


by 비비엘소프트 [2016.01.25 12:44:15]

좋은 자료 감사합니다!


by 닭강정호떡 [2016.12.20 23:15:40]

좋은자료 잘보구 갑니다. 감사합니다!


by 진스 [2017.02.24 10:08:31]

감사합니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입