CS study/데이터베이스

인덱스 기본 사용법 : 연산 최적화(친절한 SQL 튜닝)

블랑v 2024. 11. 21. 20:28

인덱스 기본 사용법

인덱스 컬럼(선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

즉, ‘인덱스를 정상적으로 사용한다’ 라는 개념은 리프 블록에서 ‘시작점’ 을 찾아 순차 스캔하며 결과를 찾는 것이다.

 

인덱스 기본 사용법

인덱스 컬럼(선두 컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.

즉, ‘인덱스를 정상적으로 사용한다’ 라는 개념은 리프 블록에서 ‘시작점’ 을 찾아 순차 스캔하며 결과를 찾는 것이다.

이것은 결국 B-Tree의 그래프에서 리프 노드를 먼저 찾은 뒤, 인덱스 시작점부터 리프 블록의 일부분만 스캔하는 Range Scan을 의미한다.

이는 일반적인 DB의 인덱스 구현은 B+Tree를 기반으로 하므로, 리프 노드에서 시작해 오른쪽 리프 노드로 자연스럽게 넘어가는 범위 탐색(Range Scan)이 가능하다는 의미이다. (*순수한 B-Tree에서는 리프 노드 간 연결이 보장되지 않는다.)

인덱스를 정상적으로 사용한다는 것? : 시작 지점이 명확하다는 것.

"인덱스를 정상적으로 사용한다"는 것은 SQL의 WHERE 조건에서 인덱스 컬럼을 가공하지 않고 직접적으로 사용하는 경우를 의미한다.

-- 정상적으로 사용된 인덱스 : 순수한 조건 비교
SELECT * FROM table WHERE column1 = 10;

-- 서브쿼리나 연산 등.. 정상적이지 않은 인덱스
SELECT * FROM table WHERE FUNC(column1) = 10;
SELECT * FROM table WHERE column1 + 1 = 11;

 

만일 인덱스 컬럼을 가공한다면 스캔 시작점을 찾을 수 없기에.. 리프 블록 전체를 스캔해야만 한다.

즉, ‘일부가 아닌 Index Full Scan’을 사용하게 된다. 이는 당연한 이야기인데, 스캔의 시작 지점을 찾을 수 없기 때문이다.

이를 정리하면 다음과 같은 결론을 낼 수 있다.

 

  1. 인덱스 조건에서 컬럼을 가공하면 B+Tree의 정렬된 구조를 활용할 수 없으며, 시작 지점을 특정할 수 없기 때문에 Index Full Scan이 발생한다.
  2. 이는 인덱스의 핵심인 Range Scan(수평 탐색)을 사용할 수 없다는 이야기와 동일하다.

 

Range Scan(수평 탐색)을 사용할 수 없는 이유

B+Tree 인덱스는 정렬된 상태로 데이터가 저장되어 있다. 예제와 같이 Timestamp 형식이라면 다음과 같이 순수한 값이 보관되어 있을 것이다.

 

 

이 과정에서 정렬 상태가 유지되고, 순차 스캔을 시작할 수 있는 지점을 정확히 찾을 수 있기 때문에 효율적으로 범위 검색이 가능해진다.

그렇다면 가공했을 때는 어떨까? 책에서의 예제와 같이 5월에 속하는 사람을 찾는다면?

B+Tree는 인덱스 컬럼(column1)의 원래 값에 대한 정렬만 알고 있기에, 정렬된 상태를 알 수 없게 된다. 즉, 이는 정렬 속성을 사용할 수 없다는 말과 동일하다.

 

select * from my_table where substr(생년월일, 5, 2) = '05'; ..
... where nvl(주문수량, 0) < 100

-- 혹은 다음과 같은 Like 연산 역시 동일하다.
... where company_name like '%korea%';

-- 아래의 OR 연산은 언뜻 보면 가능해 보이지만, 마찬가지로 시작 장소를 찾을 수 없다.
... where (phone = :tel_no OR name = :=cust_nm)

 

OR 연산에서 인덱스가 비효율적인 이유? + 예외?

 

인덱스는 일반적으로 하나의 컬럼 또는 선두 컬럼(Composite Index의 경우)에 대해 정렬된 상태로 유지된다고 언급했었다.

일반적으로 OR 조건은 여러 개의 독립적인 조건을 조합하므로, 인덱스가 한 번에 시작 지점(start point)을 특정할 수 없다. 하지만 다음과 같다면 어떨까?

 

OR Expansion의 예시를 보자.

select *
  from customer
 where name = :cust_nm -- 고객명 선두 컬럼 인덱스 확보
 union all
select *
  from customer
 where phone_num = :tel_no -- 전화번호 선두 컬럼 인덱스 확보
   and (name <> :cust_nm or name is null)

 

조금 어렵긴 한데, OR Expansion은 OR 조건을 UNION ALL로 쪼개어, 각각의 조건이 독립적으로 인덱스를 활용하도록 유도하는 방법이다. 각각의 조건(name, phone_num)이 독립적으로 인덱스를 사용하므로, OR 조건보다 훨씬 효율적이다. (인덱스를 사용하는 Range Scan이 가능)

IN 조건절의 경우

아래의 조건과 같은 IN의 경우는 어떨까?

... where phone_num in ( :tel_no, :tel_no)

 

사실 IN 연산 역시도 OR 연산과 비슷하게 동작하기 때문에 인덱스 활용이 불가능하다.

위 쿼리는 내부적으로 다음과 같이 OR 조건으로 확장된다

 

WHERE phone_num = :tel_no1
   OR phone_num = :tel_no2
   OR phone_num = :tel_no3

 

OR 조건과 IN 조건의 공통점

  • IN 조건은 OR 조건과 동일하게 여러 값을 각각 독립적으로 처리해야 한다.
  • DBMS는 IN 목록에 있는 각각의 값에 대해 인덱스를 사용하거나, 전체 데이터를 스캔하여 조건을 만족하는 값을 찾아야 한다.

그렇기에 IN 연산의 경우 IN-List Iterator 방식을 사용한다.

(IN 조건의 값이 적당히 적고 해당 컬럼에 인덱스가 있다면) List의 개수만큼 Index Range Scan을 각각 적용하는 것이다.

이것은 옵티마이저의 쿼리 변환 과정에 의해 이루어진다는 점을 기억하자.

 

더 중요한 인덱스 사용 조건

인덱스를 Range Scan하기 위한 가장 큰 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다는 점이다.

 

 

다음과 같은 식으로, 인덱스를 ‘소속팀 + 사원명 + 연령’ 순으로 구성했다.

이 경우 다음과 같은 조건절에 인덱스 스캔이 가능할까?

select 사원번호, 소속팀, 연력, 입사일자, 전화번호
  from 사원
 where 사원명 = '홍길동'

 

사원명 = 홍길동을 통해 언뜻 보면 명확한 인덱스 시작 구간을 찾을 수 있을 것 같지만, select 구문에서 이것만으로는 결정할 수 없는 요소가 있다.

당연하게도 선두 컬럼 위치를 잡을 수가 없기에, FS(Full Scan)가 발생한다.

 

교재 내용의 의문과 정리

이런 이치에 맞게, 교재에서는 다음과 같이 내용을 확장하였다

where 절에 인덱스 선두 컬럼이 가공되지 않는 상태로 있다면, 인덱스 Range Scan은 무조건 가능하다.

select *
  from table
 where year = :stdr_year
 and   substr(과세구분, 1, 4) = :txtn_dcd
 and   기타 컬럼 = :rpt_tmrd
 ..
 
-- 위 사례의 경우 year를 조건절에서 가공하지 않았으므로 Range Scan이 가능하다고 서술한다..

 

인덱스 선두 컬럼이 가공되지 않은 상태로 있다면 Range Scan은 무조건 가능하다는 말은 엄밀히 말해 틀리다. 선두 컬럼이 가공되지 않았더라도, 다른 조건( substr)이 인덱스 활용을 방해하면 Range Scan이 불가능해질 수 있다.

가능할 경우는 다음과 같다.

 

year가 선두 컬럼으로 설정된 복합 인덱스를 사용했을 가능성

CREATE INDEX idx_complex ON table (year, 과세구분, 기타_컬럼);

 

쿼리 최적화 과정을 단순화하여 설명했을 가능성.

"Range Scan 가능"이라는 설명은, 쿼리의 일부가 Range Scan으로 처리될 수 있음을 의미했을 가능성이 크다.

 

  1. year = :stdr_year 조건은 Range Scan을 유도한다.
  2. substr(과세구분, 1, 4) 조건은 Range Scan을 방해하지만, 이미 특정된 year 조건의 범위 내에서 후처리(Post-Filtering) 방식으로 처리된다.

⇒ year 조건으로 Range Scan이 수행된 결과를 대상으로, substr 조건을 메모리나 리프 노드 수준에서 필터링하여 추가로 처리한다.

뭐 결론적으로 말하자면, 선두 컬럼이 가공되지 않은 경우에만 Range Scan 시작이 가능하다는 점을 기억하고, 가공된 컬럼은 후처리(Post-Filtering)로 처리될 가능성이 높다는 점을 기억하자.

 

인덱스를 이용한 소트 연산 생략 : 무료 정렬 찬스

 

쿼리 실행 과정에서 정렬(SORT 연산)이 필요할 때, 인덱스가 이미 정렬된 상태를 가지고 있다면(제공한다면) 이를 그대로 활용하여 추후 실행해야 할 정렬 작업을 생략할 수 있다.

 

인덱스는 정렬되어 있기 때문에 Range Scan이 가능하고, 소트 연산 생략 효과도 부수적으로 얻을 수 있다.

인덱스를 이용한 소트 연산 생략은 데이터베이스 최적화의 중요한 개념 중 하나로, 인덱스가 정렬된 데이터를 저장하는 특성을 활용하여 별도의 정렬 연산(SORT 연산)을 생략하는 방식이다.

 

소트 연산은 주로 ORDER BY, GROUP BY, 혹은 DISTINCT 같은 쿼리의 정렬을 처리하기 위해 발생한다. 이 과정에서 DBMS가 사용하는 정렬 방식이 Merge Sort, Hash Sort와 같은 내부적인 정렬 알고리즘이다.

예시로 다음과 같이 생각해보자. 소트 연산이 발생하는 상황이다.

 

소트 연산 발생 예시

#1. Order By sort 연산

SELECT * 
  FROM employees
 ORDER BY last_name;
 
1. 결과 집합은 last_name 기준으로 정렬되어야 하므로, DBMS는 정렬 연산을 수행한다.
2. 만약 last_name에 인덱스가 없다면, DBMS는 데이터를 모두 읽은 뒤 내부적으로 정렬을 수행한다.
3. 실행 계획에서 SORT(ORDER BY) 또는 Using filesort가 나타난다.

#2. GROUP BY에서 발생하는 소트 연산
SELECT department_id, COUNT(*)
  FROM employees
 GROUP BY department_id;

1. department_id별로 데이터를 그룹화하려면, DBMS는 데이터를 department_id 기준으로 정렬해야 한다.
2. 정렬 후 각 그룹의 데이터를 집계(COUNT(*))한다.
3. 실행 계획에서 SORT(GROUP BY)가 나타날 수 있다.

#3. DISTINCT에서 발생하는 소트 연산
SELECT DISTINCT job_id
  FROM employees;

1. job_id의 고유한 값만 반환하기 위해, DBMS는 중복 값을 제거하기 전에 데이터를 정렬한다.
2. 정렬 후 인접한 값이 동일한지 비교하여 중복 제거를 수행한다.
3. 실행 계획에서 SORT(UNIQUE) 또는 Using filesort가 나타난다.

#4. 복합적인 정렬 연산
SELECT department_id, job_id, COUNT(*)
  FROM employees
 GROUP BY department_id, job_id
 ORDER BY department_id, job_id;

1. GROUP BY는 데이터를 department_id와 job_id 기준으로 그룹화하기 위해 정렬해야 한다.
2. 이후 ORDER BY가 추가로 결과 집합을 같은 기준으로 정렬하므로, 이미 그룹화 과정에서 정렬된 결과를 재사용할 수 있다.
3. 실행 계획에서 GROUP BY와 ORDER BY가 결합된 형태로 SORT(MERGE)가 나타날 수 있다.

소트 연산 생략 예시

#1. Order By sort 연산 최적화
CREATE INDEX idx_last_name ON employees (last_name);
SELECT * 
  FROM employees
 ORDER BY last_name;

1. last_name 컬럼에 인덱스를 생성하면, 데이터가 이미 정렬된 상태로 저장되므로 DBMS는 추가적인 소트 연산을 수행하지 않는다.
2. DBMS는 인덱스를 순차적으로 스캔하며 데이터를 반환한다.
3. 실행 계획에서 Using index가 나타나고, SORT(ORDER BY) 또는 Using filesort는 생략된다.

#2. GROUP BY에서 발생하는 소트 연산 최적화
CREATE INDEX idx_department_id ON employees (department_id);

SELECT department_id, COUNT(*)
  FROM employees
 GROUP BY department_id;

1. department_id 컬럼에 인덱스를 생성하면, 인덱스가 정렬된 상태를 제공하므로 별도의 정렬 없이 그룹화를 수행할 수 있다.
2. DBMS는 인덱스를 따라 데이터를 읽고 그룹화를 바로 수행한다.
3. 실행 계획에서 **SORT(GROUP BY)**는 나타나지 않으며, 인덱스를 사용한 최적화가 수행된다.

#3. DISTINCT에서 발생하는 소트 연산 최적화
CREATE INDEX idx_job_id ON employees (job_id);

SELECT DISTINCT job_id
  FROM employees;

1. job_id 컬럼에 인덱스를 생성하면, 중복 제거를 위해 정렬하지 않아도 인덱스가 이미 정렬된 데이터를 제공한다.
2. DBMS는 인덱스를 순차적으로 스캔하며 중복 제거를 수행할 수 있다.
3. 실행 계획에서 SORT(UNIQUE) 또는 Using filesort는 생략된다.

#4. 복합적인 정렬 연산 최적화
CREATE INDEX idx_multi ON employees (department_id, job_id);

SELECT department_id, job_id, COUNT(*)
  FROM employees
 GROUP BY department_id, job_id
 ORDER BY department_id, job_id;

1. (department_id, job_id)에 대한 복합 인덱스를 생성하면, 정렬된 상태로 데이터를 읽을 수 있다.
2. GROUP BY와 ORDER BY 모두 같은 정렬 순서를 요구하므로, DBMS는 인덱스를 사용하여 중복 정렬 작업을 생략한다.
3. 실행 계획에서 SORT(MERGE)는 생략되며, 인덱스를 사용한 데이터 반환이 이루어진다.

생략 조건과 불가능한 상황

소트 연산의 생략 조건은 크게 다음과 같다.

  1. 인덱스 정렬 : 즉, Range Scan을 사용할 수 있는 선두 컬럼이 특정된 상태
  2. where과 order by 정렬이 인덱스 순서를 따를 것
  3. order by의 경우 오름차순, 내림차순이 통일될 것
  4. 복합 인덱스라면 순서와 조건이 정확할 것.

반대로 생략 불가능한 조건은 다음과 같다. 생략 조건의 반대라고 생각해보자.

  1. 인덱스가 없는 경우
  2. 가공된 컬럼 - Range Scan
  3. 복합 인덱스의 중간 순서를 건너 뛸 경우
  4. ASC/DESC 불일치