SQL 튜닝 - 인덱스

목차

1. 인덱스 컬럼 선정 가이드

2. 인덱스를 사용하지 못하는 경우

3. INDEX SCAN보다 FULL TABLE SCAN이 효율적인 경우

1. 인덱스 컬럼 선정 가이드

조건을 만족하는 값이 전체 데이터의 15~20% 이내라면 인덱스를 생성하는 것이 좋다.

조건절에서 비교해야 하는 컬럼이 여러 개이고, 그 중 하나의 컬럼으로 인덱스를 만드는 것만으로 효과가 미미하다면 여러 컬럼을 조합하여 만드는 결합 인덱스를 생성해야 한다. 

결합 인덱스를 구성하는 컬럼의 순서는 성능을 크게 좌우한다.

 

(1) 조건절에 항상 사용되는 컬럼

결합 인덱스의 첫 번째 컬럼이 조건절에서 사용되지 않으면, 이 인덱스가 활용될 가능성은 줄어든다.

조건절에서 사용된 컬럼이 결합 인덱스의 선두 컬럼으로 구성되어 있어야 성능 검색에 유리히다. 

 

(2) 조건절에서 사용되는 컬럼 가운데 '='로 비교되는 컬럼을 선두에 두고 결합 인덱스를 생성하는 것이 좋다. 이때 IN이나 OR와 같은 경우 내부적으로 '=' 연산을 수행하므로, 이와 같은 표현식을 사용하여 비교하는 컬럼들에 대해서도 결합 인덱스 생성 여부를 고려해야 한다.

 

(3) 분포도가 좋은 컬럼을 선두에 위치

분포도가 좋은 컬럼을 선두에 두면 처리해야 할 대상을 초기에 줄여주므로 검색 성능을 향상시킬 수 있다. 다만, 데이터의 분포도가 좋다고 해도 프로그램에서 많은 양의 데이터를 가져다 쓰는 경우가 대부분이라면 인덱스의 선두 컬럼에 두는 것은 위험하다

 

(4) ORDER BY절에서 자주 사용하는 정렬 순서로 컬럼 순서 지정

ORDER BY절 등을 통해 수행하는 정렬 빈도, 및 순서를 고려하여 결합 인덱스의 컬럼 순서를 선정하기도 한다. 인덱스를 생성하면 데이터를 정렬한 수 인덱스 키로 저장하기 때문에 정렬 작업으로 인한 부담을 줄일 수 있기 때문이다. 단 대상 컬럼의 데이터가 긴 경우라면, 인덱스의 사이즈가 방대해질 수 있으므로 가급적 지양하는 것이 좋다.

 

---

2. 인덱스를 사용하지 못하는 경우

(1) 컬럼의 내부 변형

WHERE TA.product_id = 1234567 (PRODUCT_ID VARCHAR(7))

PRODUCT_ID는 데이터 타입이 VARCHAR임에도 불구하고, 조건절에서 이 컬럼의 비교 조건으로 제시한 값에 작은 따음표를 사용하지 않았다. 이렇게 사용하는 경우 TO_NUMBER(product_id) = 1234567로 내부적인 변형이 일어나 인덱스를 사용한 검색이 불가능해진다. 

 

(2) 컬럼의 외부 변형

WHERE SUBSTR(rank_no, 1, 6) = '123456' -> WHERE rank_no LIKE '123456%'

조건절에서 비교하고 있는 rank_no 컬럼에 SUBSTR이라는 함수를 사용해 변형을 일으킨 경우이다. 이 컬럼에는 이미 인덱스가 만들어져 있었지만, 이와 같은 외부 변형으로 인해 인덱스를 사용할 수 없게 된 것이다.

 

(3) IS NULL, IS NOT NULL을 사용한 비교

B트리 인덱스는 엔트리에 NULL 값을 저장하지 않는다. 따라서 인덱스를 생성한 컬럼에 대해 IS NULL로 비교를 하는 경우 인덱스를 사용하지 않는다. 

WHERE close_date IS NULL

따라서 close_data 컬럼의 NULL인 데이터를 모두 임의의 값인 '00000000'로 바꾸고 해당 컬럼에 DEFAULT 값을 설정하여 앞으로 입력 받을 값에 대해서도 아무 값도 입력하지 않으면 자동적으로 '00000000'이 입력되도록 했다. 그 다음으로 이 컬럼들의 인덱스를 사용할 수 있도록 close_date IS NULL이라는 조건을 close_date = '00000000'로 변경했다. 

 

(4) LIKE 검색 시 변수 앞 '%' 사용

비교할 값의 앞에는 가급적 '%'를 사용하지 않아야 한다. 물론, 요구사항을 충족시키기 위해 어쩔 수 없이 비교할 값의 앞에 '%'를 사용해야 하는 경우도 있지만, 그렇지 않은 경우에도 개발자가 습관적으로 비교할 값의 앞에 '%'를 사용하는 경우도 있다. 이런 경우 해당 컬럼에 인덱스가 있어도 인덱스를 사용할 수가 없다.

 

(5) 여러 컬럼에 대한 OR 조건 사용

OR를 사용하여 여러 컬럼에 대한 조건을 나열할 때, 사용된 컬럼 중 어느 하나라도 인덱스에 포함되어 있지 않으면, FULL TABLE SCAN을 하도록 실행 계획을 세운다. 이 경우 적절한 인덱스를 생성하여 인덱스를 사용한 검색을 할 수 있지만, 인덱스 생성이 여의치 않은 상황이라면 힌트를 사용하거나 각각의 컬럼에 대한 조건을 분리한 후 UNION ALL로 연결하는 방법으로 튜닝할 수 있다.

 

(6) 부정형 비교

부정형 비교라고 하면 <>, !=, NOT IN, NOT EXISTS 등이 되겠다.

WHERE TA.LOAN_CODE != '01'

이 컬럼은 특성상 '01'부터 '09'의 값만 입력될 수 있는 구조로, 인덱스를 사용해 검색하면, 성능이 좋아질 것이기 때문에 TA.loan_code > '01'로 변경하여 생성한 인덱스를 사용할 수 있도록 해야 한다.

 

---

3. 결합 인덱스

1. 인덱스로 설정하는 필드의 속성이 중요하다. title, author 이 순서로 인덱스를 설정한다면 title을 search 하는 경우, index를 생성한 효과를 볼 수 있지만, author 만으로 search 하는 경우, index 를 생성한 것이 소용이 없어진다.

 

2 . 인덱스만 활용하고 테이블 엑세스 회피 -> 빠른 검색 가능

/* 고객명 조회 */

SELECT 고객명 FROM 고객테이블 WHERE 고객번호 = '12345'

/* 인덱스 */

CREATE INDEX IDX_01 ON 고객테이블(고객번호, 고객명)

 

---

4. INDEX SCAN보다 FULL TABLE SCAN이 효율적인 경우

INDEX SCAN이 효율적인 경우는 검색해 오고자 하는 데이터 수가 테이블에 저장된 전체 데이터 수의 10~15%를 밑도는 경우라고 통상적으로 이야기한다. 

 

그러나 검색해 오고자 하는 데이터 수가 테이블에 저장된 전체 데이터 수의 50% 이상의 경우 I/O 측면에서 볼 때, INDEX SCAN은 단일 블록 I/O 방식을 쓰고, FULL TABLE SCAN은 멀티 블록 I/O 방식을 사용한다. 멀티 블록 I/O는 한 번 I/O를 수행할 때 인접한 여러 개의 블록을 동시에 읽어 메모리에 적재하는 방식으로, 그 결과 분포도가 좋지 않은 컬럼에 인덱스가 있는 경우는 멀티 블록 I/O 방식을 채택하는 FULL TABLE SCAN이 INDEX RANGE SCAN에 비해 I/O 호출 횟수가 적어 성능이 오히려 좋아진다.

 

 

---

출처 : 실전 사례로 살펴보는 SQL 튜닝 비법