인덱스.. 그래서 어떻게 해야 효율적이야? (필터조건, 액세스 조건)

인덱스를 설정하는 것은 정말 운영환경에서 중요한 것으로 효율적으로 설정하기 위해서는 반드시 "필터 조건"과 "액세스 조건"을 이해해야 한다. 

 

액세스 조건 - 인덱스 스캔 범위를 결정 

필터 조건 -- 인덱스를 설정했는데 원하는 테이블 결곽 나오지 않았을 경우 이제 테이블로 가기 위한 조건이다 

 

\

인덱스의 액세스 조건(Access Predicate)과 필터 조건(Filter Predicate)은 데이터베이스에서 인덱스가 사용될 때, 쿼리의 효율성을 결정하는 중요한 요소들입니다. 이 두 가지는 실행 계획에서 인덱스가 어떻게 사용되는지를 설명하며, 옵티마이저가 인덱스 스캔을 통해 데이터를 검색하는 방식과 조건을 나누어 분석할 수 있게 해줍니다.

1. 인덱스의 액세스 조건 (Access Predicate)

액세스 조건은 인덱스 키 컬럼에 직접적으로 적용되는 조건입니다. 즉, 인덱스를 사용할 때 해당 조건에 맞는 범위나 값만을 대상으로 인덱스를 탐색하는 데 사용됩니다.

• 인덱스 키에 대한 조건이기 때문에, 인덱스 스캔을 시작할 위치를 결정하는 데 매우 중요한 역할을 합니다.
• 주로 WHERE 절에서 인덱스의 키 컬럼에 대한 동등 조건(=), 범위 조건(>, <, BETWEEN) 등을 포함합니다.
• INDEX RANGE SCAN 또는 INDEX UNIQUE SCAN 등에서 주로 사용됩니다.

예시

SELECT * FROM employees WHERE department_id = 10;

• department_id가 인덱스 키로 설정되어 있는 경우, department_id = 10은 액세스 조건이 됩니다.
• 이 조건을 통해 옵티마이저는 department_id 값이 10인 부분만을 빠르게 찾을 수 있게 됩니다.

액세스 조건은 쿼리의 효율성에 매우 중요한 요소로, 인덱스가 얼마나 효과적으로 사용되는지를 결정합니다. 액세스 조건이 잘 설정되면 불필요한 테이블의 데이터를 스캔하지 않고, 해당 범위의 인덱스만 빠르게 조회할 수 있습니다.

실행 계획에서의 액세스 조건 예시

실행 계획에서 액세스 조건은 다음과 같은 형태로 나타날 수 있습니다:

 

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES    |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | EMP_IDX      |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPARTMENT_ID"=10)

2. 인덱스의 필터 조건 (Filter Predicate)

필터 조건은 인덱스 스캔 후에 데이터가 추가적으로 필터링되는 조건입니다. 즉, 인덱스를 사용하여 데이터를 찾아낸 후, 추가적으로 필터링해야 할 조건이 있을 때 사용됩니다.

• 필터 조건은 인덱스가 적용되지 않으며, 데이터 블록을 스캔한 후 행 수준에서 필터링이 이루어집니다.
• 필터 조건은 일반적으로 인덱스에 없는 다른 열에 대한 조건이거나, 인덱스 키에 적용되지 않는 복잡한 조건입니다.
• 필터링을 하기 위해 테이블 데이터를 읽고 다시 필터를 적용하기 때문에, 비교적 성능에 영향을 미칠 수 있습니다.

예시

SELECT * FROM employees WHERE department_id = 10 AND salary > 5000;

• department_id = 10은 인덱스의 액세스 조건이 될 수 있지만, salary > 5000은 인덱스 키가 아닌 열이므로 필터 조건으로 처리됩니다.
• 인덱스는 department_id = 10인 데이터를 먼저 찾은 후, salary > 5000이라는 조건을 추가로 적용하여 데이터를 필터링합니다.

실행 계획에서의 필터 조건 예시

실행 계획에서 필터 조건은 다음과 같이 나타날 수 있습니다:

 

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    27 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES    |     1 |    27 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | EMP_IDX      |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPARTMENT_ID"=10)
   1 - filter("SALARY" > 5000)

• access("DEPARTMENT_ID"=10): 인덱스를 사용하여 DEPARTMENT_ID = 10 조건을 만족하는 데이터를 찾습니다.
• filter("SALARY" > 5000): 그 다음 단계에서 SALARY > 5000 조건을 만족하는 데이터를 필터링합니다.
•  

3. 액세스 조건과 필터 조건의 차이점 요약

항목액세스 조건(Access Predicate)필터 조건(Filter Predicate)

적용 위치	인덱스 스캔 시 적용 (인덱스의 키 컬럼에 적용)	인덱스 스캔 후 테이블의 데이터 블록에서 추가로 필터링
효율성	인덱스를 사용하여 빠르게 데이터를 찾을 수 있음 (성능에 큰 영향)	성능에 상대적으로 덜 효율적 (추가 필터링 필요)
사용 가능 조건	인덱스 키 컬럼에 대한 동등 조건(=), 범위 조건(>, <, BETWEEN 등)	인덱스에 없는 열 또는 추가적인 조건 (복잡한 연산)
실행 계획에서의 표현	access(...)	filter(...)

4. 최적화 전략

• 인덱스 키에 맞게 액세스 조건을 최대한 활용하여, 데이터를 빠르게 찾고 불필요한 테이블 스캔을 줄이는 것이 중요합니다.
  • 예를 들어, 자주 쿼리되는 열을 인덱스 키로 설정하고, WHERE 조건에 동등 또는 범위 조건을 사용하면 액세스 조건을 효과적으로 활용할 수 있습니다.
• 필터 조건을 줄이기 위해 추가 인덱스를 설정하거나, 필요한 열을 인덱스 키에 포함시키는 것도 성능을 최적화하는 방법입니다.
  • 필터 조건이 자주 사용되면 해당 조건에 맞는 인덱스를 새로 생성하거나, 컴포지트 인덱스(다중 열 인덱스)를 사용하는 것이 좋습니다.
  • 이렇게 컴포지트 인덱스를 설정하게 될 경우, 

결론

• 액세스 조건은 인덱스 키에 적용되며 인덱스를 통해 데이터를 찾는 데 매우 중요한 역할을 합니다. 이를 잘 활용하면 쿼리 성능을 크게 개선할 수 있습니다.
• 필터 조건은 인덱스 스캔 후 추가적으로 적용되는 조건으로, 이 조건을 줄이기 위해 인덱스 설계를 신중히 해야 합니다. 필요한 경우 추가 인덱스를 생성하거나 컴포지트 인덱스를 통해 필터링 단계를 줄일 수 있습니다.

인덱스 액세스 조건과 필터 조건을 이해하고 적절히 활용하면 데이터베이스의 쿼리 성능을 크게 최적화할 수 있다는 점 기억하세요!!