효율적인 Nested Loop Join을 위한 INDEX 관련 튜닝

Nested Loop Join에서 가장 중요한 요소 중 하나는 적절한 인덱스를 생성하는 것입니다. 적절한 인덱스가 없다면 Nested Loop Join이 비효율적으로 동작할 수 있습니다. 하지만 적절한 인덱스를 생성했더라도 해당 인덱스가 사용되지 않는 경우가 있을 수 있습니다. 이런 경우에는 몇 가지 추가적인 점검이 필요합니다.

 

1. 인덱스 힌트 사용

인덱스 힌트는 SQL 쿼리에서 특정 인덱스를 사용하도록 강제로 지시하는 방법입니다. 때때로 옵티마이저가 최적의 실행 계획을 선택하지 못할 수 있으며, 이런 경우에는 개발자가 직접 인덱스 힌트를 사용하여 쿼리의 성능을 향상시킬 수 있습니다. 하지만 인덱스 힌트는 주의해서 사용해야 하며, 잘못된 사용은 오히려 성능을 저하시킬 수도 있습니다.

다음은 인덱스 힌트를 사용하는 예제입니다.

 

예제 테이블과 인덱스 생성:

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    department VARCHAR2(50),
    salary NUMBER
);

CREATE INDEX idx_employees_department ON employees(department);

 

옵티마이저가 인덱스를 선택하지 않는 경우:

-- 옵티마이저가 인덱스를 선택하지 않는 쿼리
SELECT *
FROM employees
WHERE department = 'IT';

 

위의 쿼리는 부서(department) 열을 조회하는데, 해당 열에는 인덱스가 생성되어 있지만 옵티마이저가 인덱스를 선택하지 않을 수 있습니다. 이런 경우에는 인덱스 힌트를 사용하여 인덱스를 강제로 선택할 수 있습니다.

 

인덱스 힌트 사용:

-- 인덱스 힌트를 사용하여 인덱스를 강제로 선택
SELECT /*+ INDEX(e idx_employees_department) */ *
FROM employees e
WHERE department = 'IT';

 

위의 쿼리에서는 /*+ INDEX(e idx_employees_department) */ 인덱스 힌트를 사용하여 idx_employees_department 인덱스를 강제로 선택하도록 지시하고 있습니다. 이렇게 하면 옵티마이저가 해당 인덱스를 사용하여 쿼리를 수행하게 됩니다.

 

하지만 인덱스 힌트를 사용할 때는 주의해야 합니다. 인덱스 힌트를 남용하면 옵티마이저의 최적화 능력을 제한하고 유지보수가 어려워질 수 있습니다. 인덱스 힌트를 사용하기 전에 먼저 옵티마이저가 왜 해당 인덱스를 선택하지 않았는지를 분석하고, 그 이유를 이해한 후에 인덱스 힌트를 사용하는 것이 좋습니다. 필요한 경우에만 신중하게 인덱스 힌트를 적용하여 쿼리의 성능을 개선하는 것이 좋습니다.

 

💡 Tip!

인덱스 힌트의 첫 번째 항목에는 해당 테이블에 대한 별칭(Alias)을 입력해야 합니다. 별칭이 없는 경우에는 테이블명을 입력해도 쿼리 옵티마이저가 이를 이해하여 힌트를 적용할 수 있지만, 동일한 테이블이 여러 번 사용될 경우 별칭을 사용하는 것이 좋습니다.

 

2. 통계 정보 확인

데이터베이스에서는 쿼리 옵티마이저가 쿼리 실행 계획을 수립할 때 테이블의 통계 정보를 활용합니다. 따라서 정확하고 최신의 통계 정보가 있어야 옵티마이저가 최적의 실행 계획을 선택할 수 있습니다.

 

필요한 인덱스를 생성했다고 해도, 데이터베이스 옵티마이저가 최적의 실행 계획을 선택하기 위해서는 테이블의 통계 정보가 필요합니다. 통계 정보가 없을 경우, 데이터베이스는 테이블의 크기, 컬럼의 분포, 인덱스의 유용성 등을 파악할 수 없기 때문에 인덱스를 인지하지 못하게 됩니다.

 

테이블의 통계 정보를 수집하는 방법으로는 ANALYZE 명령뿐만 아니라 DBMS_STATS 패키지를 사용하는 방법이 있습니다. DBMS_STATS 패키지를 사용하면 통계 정보를 더욱 세밀하게 수집할 수 있고, 자동으로 스케줄링하여 통계 정보를 주기적으로 업데이트하는 것도 가능합니다. 데이터베이스의 특성과 요구사항에 맞게 적절한 방법을 선택하여 통계 정보를 유지보수하는 것이 좋습니다. ANALYZE 및 DBMS_STATS 모두 데이터베이스 관리자 또는 통계 수집에 필요한 권한이 있는 사용자의 권한이 필요합니다.

 

최근에 특정 테이블이 언제 통계 정보를 수집했는지 확인하는 방법은 데이터베이스 시스템에 따라 다를 수 있습니다. 가장 일반적인 방법은 데이터베이스의 시스템 카탈로그 뷰를 이용하는 것입니다.

예를 들어, Oracle 데이터베이스에서는 다음과 같이 DBA_TAB_STATISTICS 뷰를 조회하여 특정 테이블의 통계 정보 수집 시간을 확인할 수 있습니다:

SELECT table_name, last_analyzed
FROM dba_tab_statistics
WHERE table_name = 'your_table_name';

 

위 쿼리에서 'your_table_name' 부분에 원하는 테이블의 이름을 넣어주면 해당 테이블의 통계 정보를 마지막으로 수집한 시간을 확인할 수 있습니다.

정리하면, 테이블의 통계 정보를 확인하고 최신으로 유지하는 것은 쿼리 옵티마이저가 최적의 실행 계획을 선택하는 데 매우 중요합니다. 따라서 적절한 시간 간격으로 ANALYZE 명령을 사용하여 통계 정보를 수집하고, 필요할 때마다 통계 정보를 확인하여 최적의 성능을 유지하는 것이 좋습니다.

 

💡 Tip!

인덱스를 생성했는데도 해당 인덱스를 활용하지 않는 경우, 인덱스 생성 이후로 통계 정보가 업데이트되지 않았을 수 있습니다. 따라서 인덱스 생성 시점과 통계 정보 생성 시점을 비교해서 통계 정보가 오래되었다면, 새로운 통계 정보를 생성하는 것이 좋습니다.

 

3. 조건절 검토

조건절에 사용되는 조건과 연산자의 타입이 인덱스와 일치하지 않으면 데이터베이스 옵티마이저는 해당 인덱스를 활용할 수 없게 됩니다. 따라서 쿼리를 실행할 때 옵티마이저는 다른 실행 계획을 선택하게 되며, 이는 쿼리의 성능에 영향을 미칩니다. 예를 들어, 문자열 값을 숫자형 인덱스에 사용하거나, NULL을 포함한 조건을 인덱스가 지원하지 않는 경우에는 인덱스를 활용할 수 없습니다.

 

예제 테이블과 인덱스 생성:

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100),
    department_id NUMBER
);

CREATE INDEX idx_employees_department_id ON employees (department_id);

 

예제 데이터 삽입:

INSERT INTO employees VALUES (1, 'John Doe', 101);
INSERT INTO employees VALUES (2, 'Jane Smith', 102);
INSERT INTO employees VALUES (3, 'Michael Johnson', 101);

 

예제 쿼리1:

SELECT * FROM employees WHERE department_id = '101';

 

예제 쿼리1은 department_id 컬럼에 문자열 값을 사용하여 조회하는 쿼리입니다. 하지만 department_id는 숫자형 인덱스로 생성되었으며, 문자열 값을 조회하는 것은 인덱스를 활용할 수 없는 조건입니다. 따라서 옵티마이저는 인덱스를 사용하지 않고 테이블의 전체 데이터를 스캔하는 풀 테이블 스캔을 선택할 수 있습니다.

 

예제 쿼리1을 다음과 같이 수정할 수 있습니다:

SELECT * FROM employees WHERE department_id = 101;

 

예제 쿼리2:

SELECT * FROM employees WHERE department_id > NULL;

 

예제 쿼리2는 department_id 컬럼과 NULL을 비교하는 쿼리입니다. 하지만 대부분의 인덱스는 NULL 값을 지원하지 않습니다. 따라서 NULL 값을 포함하는 조건은 인덱스를 활용할 수 없는 조건이며, 옵티마이저는 인덱스를 사용하지 않고 풀 테이블 스캔을 선택할 수 있습니다.

 

조건절 검토를 통해 올바른 타입의 조건과 연산자를 사용하는 것이 중요합니다. 올바른 인덱스 타입과 조건을 사용하여 옵티마이저가 최적의 실행 계획을 수립하도록 해야합니다. 만약 인덱스가 효과적으로 사용되지 않는다면, 쿼리를 재작성하여 인덱스를 활용할 수 있도록 해야 합니다.

 

 

4. 인덱스 컬럼 순서

인덱스를 생성할 때 인덱스 컬럼의 순서도 중요합니다. 자주 사용되는 조건으로 먼저 오도록 순서를 설정하면 효율적인 인덱스를 구성할 수 있습니다.

 

먼저 쿼리를 살펴보면 다음과 같습니다.

SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id = 101;

 

여기서 WHERE 절에 사용된 조건은 e.department_id = 101 입니다. 즉, employees 테이블의 department_id 컬럼을 조건으로 사용하고 있습니다.

 

1) 인덱스 idx_emp_dept_empid: (employee_id, department_id) 순서로 생성된 결합인덱스

idx_emp_dept_empid는 (employee_id, department_id) 순서로 생성되었기 때문에 첫 번째 컬럼인 employee_id를 이용한 조회에 효과적입니다. 그러나 department_id 컬럼이 인덱스의 두 번째에 위치하기 때문에 이 쿼리에서 이 인덱스를 사용하는 것은 적합하지 않습니다. 만약 employee 테이블에 이 인덱스 밖에 없다면, 테이블을 Full Scan해야 할 수도 있습니다.

인덱스의 첫번째 컬럼에 대한 조건 없이도 해당 인덱스를 사용할 수 있는 INDEX_SS 힌트를 사용할 수 있지만, 이 경우에도 INDEX Full Scan이 발생할 수 있으므로 권장하지 않습니다. 최적의 인덱스를 선택하기 위해서는 쿼리의 조건과 인덱스의 컬럼 순서를 잘 고려해야 합니다. 쿼리 옵티마이저가 인덱스를 활용하도록 하려면 인덱스의 컬럼 순서를 쿼리의 조건과 일치시키는 것이 중요합니다.

 

2) 인덱스 idx_emp_dept_deptid: (department_id, employee_id) 순서로 생성된 결합인덱스

idx_emp_dept_deptid는 (department_id, employee_id) 순서로 생성되었습니다. 따라서 위 쿼리에서 이 인덱스를 사용하여 조회할 수 있습니다. 인덱스의 첫 번째 컬럼은 department_id이며, 이것이 쿼리의 조건인 e.department_id = 101과 일치합니다. 이에 따라 쿼리 옵티마이저는 이 인덱스를 효과적으로 활용하여 데이터를 조회할 수 있습니다.

 

 

따라서 적절한 인덱스를 선택하고, 인덱스의 컬럼 순서를 쿼리에 맞게 조정하는 것이 쿼리의 성능 향상에 도움이 됩니다. 올바른 인덱스를 활용하여 데이터베이스의 성능을 최적화하는 것이 중요하며, 테스트와 모니터링을 통해 쿼리의 실행 계획을 확인하고 최적의 인덱스를 찾는 것이 좋습니다.

 

 

5. 인덱스 선택도

인덱스 선택도는 인덱스가 얼마나 유일한 데이터를 가지고 있는지를 나타내는 지표입니다. 선택도는 인덱스의 카디널리티(cardinality)를 기반으로 계산됩니다. 카디널리티란 인덱스의 모든 값이 서로 다른 개수를 말합니다. 선택도는 일반적으로 0과 1 사이의 값을 가지며, 1에 가까울수록 선택도가 높고, 0에 가까울수록 선택도가 낮습니다.

인덱스 선택도가 낮을수록 인덱스의 유용성이 높아지는 이유는 다음과 같습니다. 선택도가 낮은 인덱스는 데이터베이스 테이블의 다양한 레코드들이 같은 인덱스 값으로 매핑되는 경우가 많습니다. 이런 경우 인덱스를 이용한 데이터 검색이 적합하지 않으며, 테이블을 Full Scan하는 것이 효과적일 수 있습니다. 반면에 선택도가 높은 인덱스는 각 인덱스 값에 대응되는 레코드들이 서로 다른 경우가 많습니다. 이 경우 인덱스를 이용한 데이터 검색이 더욱 효과적입니다.

예를 들어, employees 테이블에는 department_id라는 컬럼이 있고, 이 컬럼에 인덱스가 생성되어 있다고 가정해보겠습니다. 이 department_id 컬럼에는 여러 직원들이 속한 부서를 나타내는 값들이 있을텐데, 이 중에서도 각 부서에 속한 직원들의 수가 비슷하게 분포되어 있다고 가정합니다. 이 경우 department_id 인덱스의 선택도는 낮을 것입니다. 예를 들어, 전체 직원 수가 10000명이라면 department_id 값들은 1부터 100까지 여러번 반복되게 될 수 있으며, 인덱스 선택도는 1/100 즉, 0.01이 될 것입니다. 이런 경우 department_id 인덱스를 이용한 검색보다는 테이블을 Full Scan하는 것이 더 효과적일 수 있습니다.

하지만 department_id가 아닌 다른 컬럼인 salary를 기준으로 한 인덱스를 생각해보겠습니다. 직원들의 급여는 각각의 직원마다 다양한 값들을 가질 것입니다. 따라서 salary 인덱스의 선택도는 높을 것입니다. 예를 들어, salary 값들은 1000부터 100000까지 다양한 값을 가지며, 인덱스 선택도는 1/99000 즉, 약 0.00001이 될 것입니다. 이런 경우 salary 인덱스를 이용한 검색이 테이블을 Full Scan하는 것보다 더 효과적일 것입니다.

인덱스 선택도는 쿼리의 성능에 영향을 미치는 중요한 요소 중 하나입니다. 따라서 인덱스를 선택하고 생성할 때에는 데이터의 분포와 카디널리티를 고려하여 인덱스 선택도를 최대한 높이도록 하는 것이 중요합니다. 데이터베이스 시스템은 인덱스 선택도를 기반으로 쿼리 옵티마이저가 실행 계획을 결정하므로, 적절한 인덱스 선택과 설계는 데이터베이스의 성능을 향상시키는 데 큰 도움이 됩니다.

 

💡 Tip!

인덱스 선택도는 데이터의 변별력을 나타내는 지표입니다.

선택도 = (특정 데이터의 건수) / (전체 데이터의 건수)

선택도는 특정 데이터를 인덱스로 조회하는데에 해당 인덱스가 얼마나 유용한지를 나타내는 비율입니다. 선택도는 일반적으로 0과 1 사이의 값을 가지며, 0에 가까울수록 인덱스가 매우 유용하다는 의미입니다. 선택도를 계산할 때, 특정 데이터의 건수를 전체 데이터의 건수로 나누기만 하면 됩니다.

따라서, 선택도가 10% 이하 또는 15% 이하인 경우 인덱스의 선택도가 낮다고 볼 수 있으며, 인덱스를 사용했을 때 성능 향상을 기대할 수 있습니다.

 

---

 

Nested Loop Join의 성능을 최적화하려면 위의 요소들을 고려하여 인덱스를 적절하게 설계하고, 실행 계획을 분석하여 인덱스가 효과적으로 사용되는지 확인해야 합니다. 특히 데이터베이스의 통계 정보를 주기적으로 갱신하는 것이 중요하며, 인덱스의 선택도와 조건절의 타입을 검토하는 것이 필요합니다.

 

 

목차