효율적인 Nested Loop Join을 위한 디스크 I/O 관련 튜닝

Nested Loop Join은 데이터베이스에서 두 개의 테이블을 조인할 때 사용되는 기법 중 하나로, 작은 테이블과 큰 테이블 사이에서 효과적으로 조인을 수행하는데 도움이 됩니다. 하지만 Nested Loop Join을 효율적으로 수행하기 위해서는 디스크 I/O를 최적화하는 것이 중요합니다.

 

이 글에서는 Nested Loop Join에서 디스크 I/O를 관리하는 다양한 튜닝 방법을 소개하고, 데이터베이스 성능을 최적화하는데에 도움이 될 수 있는 지침들을 제공하겠습니다. 특히, 인덱스의 활용, 데이터의 물리적 저장 방식, 데이터베이스 설정 관련 튜닝 등을 다룰 예정입니다. 디스크 I/O를 효율적으로 관리함으로써 Nested Loop Join의 성능을 향상시키는데에 도움이 되는 정보들을 제공하겠습니다.

 

1. Select 절의 불필요한 항목 제거

Nested Loop Join에서는 Join 조건을 만족하는 데이터만을 가져와야 합니다. 따라서 Select 절에서 불필요한 항목을 조회하지 않도록 유의하여 디스크 I/O를 최소화할 수 있습니다.

 

예를 들어, employees 테이블과 departments 테이블이 있고, 이 두 테이블을 department_id를 기준으로 Nested Loop Join으로 조인하려고 합니다. 다음과 같은 쿼리를 고려해봅시다.

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id = 101;

 

위 쿼리에서는 employees 테이블과 departments 테이블을 department_id를 기준으로 조인하고, employees 테이블의 일부 컬럼과 departments 테이블의 department_name 컬럼을 조회하고자 합니다. 하지만 여기서 주목할 점은 employees 테이블의 일부 컬럼만이 필요하다는 것입니다.

만약 employees 테이블에 많은 컬럼이 있고 해당 쿼리에서 사용하지 않는 불필요한 컬럼들이 있다면, 해당 불필요한 컬럼들을 조회하는 것은 디스크 I/O를 발생시키게 됩니다. 이는 불필요한 블록을 읽고, 메모리 공간을 차지하며, 쿼리의 성능을 저하시킬 수 있습니다.

따라서, 불필요한 항목을 조회하지 않도록 Select 절을 최소화하는 것이 중요합니다. 위 예제에서는 employees 테이블의 department_id와 departments 테이블의 department_name만 필요하므로, 다음과 같이 수정하여 불필요한 항목을 제거할 수 있습니다.

 

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

 

위 쿼리는 필요한 데이터만을 조회하기 때문에, 불필요한 컬럼들에 대한 디스크 I/O를 줄일 수 있습니다. 이렇게 불필요한 항목을 제거하여 조회하는 블록의 수를 최소화하면, 쿼리의 성능을 향상시킬 수 있습니다.

또한, 1건의 데이터가 1개의 블록에 저장되는 것이 아니라 여러 블록에 걸쳐서 저장될 수 있는데, 불필요한 항목을 조회하지 않음으로써 조회하는 블록의 수를 줄이는 것은 디스크 I/O를 더욱 효율적으로 사용하는데 도움이 됩니다. 이렇게 디스크 I/O를 최소화하는 것은 쿼리 성능 향상에 중요한 요소입니다.

 

 

2. 인덱스의 활용

적절한 인덱스를 사용하여 인덱스에서만 데이터를 조회할 수 있는 경우를 찾아내고, 인덱스를 활용하여 Join을 수행함으로써 디스크 I/O를 줄일 수 있습니다.

 

예를 들어, employees 테이블과 departments 테이블이 있고, 이 두 테이블을 department_id를 기준으로 Nested Loop Join으로 조인하려고 합니다. 이때, department_id 컬럼에 인덱스를 생성하여 인덱스를 활용하여 Join을 수행할 수 있습니다.

먼저, employees 테이블의 department_id 컬럼에 인덱스를 생성합니다.

CREATE INDEX idx_employees_department_id ON employees(department_id);

 

이제 다음과 같은 쿼리를 고려해봅시다.

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id = 101;

 

위 쿼리에서는 employees 테이블과 departments 테이블을 department_id를 기준으로 조인하고, employees 테이블의 일부 컬럼과 departments 테이블의 department_name 컬럼을 조회하고자 합니다.

이제 인덱스를 활용하여 Join을 수행할 수 있습니다. 인덱스를 사용하면 employees 테이블에서 department_id가 101인 데이터만을 인덱스에서 바로 조회할 수 있습니다. 그리고 해당 department_id에 대응하는 departments 테이블의 데이터를 Join하여 조인 결과를 반환합니다. 이때, 인덱스를 활용하여 데이터를 조회하므로 디스크 I/O가 줄어들게 됩니다.

즉, 인덱스를 활용하면 필요한 데이터만을 인덱스에서 바로 가져와서 Join을 수행하므로, 불필요한 테이블 스캔을 줄이고 디스크 I/O를 최소화할 수 있습니다. 이렇게 인덱스를 적절하게 활용하여 Join을 수행하면 쿼리의 성능을 향상시킬 수 있습니다.

 

조인으로 인해 테이블 스캔은 줄였지만, select 절에 department_name 항목이 포함되어 있기 때문에 해당 항목을 조회하기 위해 departments 테이블을 디스크에서 읽어와야 합니다. 이때, departments 테이블은 인덱스로 조회할 수 없는 컬럼인 department_name을 조회해야 하므로 디스크 I/O가 발생하게 됩니다.

인덱스는 주로 조인이나 검색에 사용되는 컬럼을 기준으로 생성하므로, 인덱스는 select 절에 사용되는 모든 컬럼을 대상으로 조회할 수 있는 것은 아닙니다. 따라서 인덱스만으로 해결할 수 없는 select 절의 항목이 있을 경우, 해당 항목은 디스크 I/O가 발생할 수밖에 없습니다.

 

 

3. 데이터의 물리적 저장 방식

테이블의 데이터를 디스크에 보관할 때 적절한 리오그(리전 인덱스 오버로딩)를 통해 같이 조회될 가능성이 높은 데이터를 같은 블록에 저장합니다. 이렇게 함으로써 데이터 조회 시에 많은 블록을 메모리에 올리지 않아도 되므로 성능이 향상될 수 있습니다.

 

가정:
우리는 주문 정보를 담고 있는 orders 테이블이 있습니다. 해당 테이블은 다음과 같은 구조를 갖습니다:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    product_id INT,
    quantity INT,
    total_amount DECIMAL
);

 

이제 orders 테이블에 대한 리오그를 생성합니다. 리오그는 데이터베이스 엔진에서 데이터를 물리적으로 저장하는 방식을 지정하는 것으로, 데이터의 조회 패턴에 맞추어 데이터를 블록에 효율적으로 저장할 수 있도록 도와줍니다.

우리는 customer_id 컬럼이 자주 조회되는 패턴을 갖는다고 가정해봅시다. customer_id 컬럼은 주문을 조회할 때 자주 사용되는 값이지만, 주문을 등록하는 빈도는 그리 높지 않을 수 있습니다.

따라서 우리는 customer_id 컬럼을 기준으로 리오그를 생성하여 같은 customer_id 값을 갖는 주문들을 가능한 한 같은 블록에 저장하도록 합니다.

CREATE CLUSTER orders_cluster
   (customer_id NUMBER)
   SINGLE TABLE HASHKEYS 1000;

 

위 예제에서 CLUSTER 키워드를 사용하여 orders 테이블에 대한 리오그를 생성하였습니다. CLUSTER 키워드를 사용하면 주문을 customer_id 컬럼 값을 기준으로 클러스터링하도록 지정한 것입니다. 그리고 HASHKEYS 키워드를 사용하여 같은 customer_id 값을 갖는 주문들을 1000개의 해시 버킷에 저장하도록 하였습니다.

이제 테이블에 데이터를 삽입하면, 같은 customer_id 값을 갖는 주문들이 같은 해시 버킷에 저장되게 됩니다. 따라서 특정 customer_id를 기준으로 주문을 조회할 때, 해당 customer_id가 속한 해시 버킷에 저장된 주문들만을 읽어오면 됩니다. 이로 인해 디스크 I/O가 줄어들고, 메모리에 캐시된 데이터를 활용하여 빠르게 조회할 수 있게 됩니다.

 

리오그를 사용한 클러스터(Cluster)는 인덱스의 키 값을 기준으로 데이터를 묶어서 저장하는 방법입니다. 따라서 클러스터에 속하는 모든 행은 동일한 클러스터 키 값을 가지며, 같은 클러스터 키 값을 가지는 행들은 물리적으로 연속된 블록에 저장됩니다. 이러한 방식으로 인해 같은 클러스터 키 값을 가지는 여러 항목도 동일한 블록에 저장될 수 있습니다.

그러나 주의해야 할 점은, 클러스터에 저장되는 데이터는 인덱스의 키 값과 관련된 항목만 저장되는 것이 아닙니다. 클러스터에는 해당 테이블의 모든 컬럼 값들이 함께 저장됩니다. 즉, 클러스터에 포함된 행들은 모든 컬럼 값들이 동일한 클러스터 키 값을 가지며, 이에 따라 물리적으로 연속된 블록에 저장됩니다.

예를 들어, 'customer_id'를 기준으로 클러스터를 생성했다면, 해당 클러스터에 속하는 모든 행은 'customer_id'가 동일한 값을 가지게 됩니다. 그리고 이 행들은 클러스터 키 값과 함께 모든 컬럼의 데이터가 물리적으로 연속된 블록에 저장됩니다.

따라서 리오그를 사용한 클러스터는 테이블의 모든 컬럼 값들을 함께 저장하며, 같은 클러스터 키 값을 가지는 행들이 물리적으로 연속된 블록에 저장되므로, 특정 클러스터 키 값으로 조회할 때 디스크 I/O가 줄어들어 성능 향상을 기대할 수 있습니다.

 

💡 Tip!

자주 변경되지 않는 코드 데이터의 경우 일반적으로 새로운 코드가 추가되는 경우가 많고, 기존 코드는 변경되는 경우가 적기 때문에 새로운 코드를 추가할 때 ORDER BY를 이용하여 특정 순서로 삽입하는 방식이 유용할 수 있습니다. 예를 들어, 우편번호를 관리하는 테이블에서 새로운 우편번호를 추가할 때는 해당 우편번호를 알파벳 순서로 삽입한다면 같은 블록에 연속적으로 저장되어 인덱스의 효율성을 높일 수 있습니다.

그러나 이 방법에는 주의할 점이 있습니다. 새로운 데이터를 삽입할 때마다 ORDER BY를 수행하면 해당 테이블의 모든 데이터를 정렬해야 하므로 성능에 영향을 미칠 수 있습니다. 따라서 데이터 양이 많거나 빈번한 데이터 추가가 예상되는 경우에는 이러한 방법을 신중하게 선택해야 합니다.

또 다른 방법은 테이블의 데이터를 초기에 정해진 순서대로 저장하고, 그 이후로는 새로운 데이터의 추가나 변경이 없는 경우에는 순서를 유지하는 방법입니다. 이렇게 하면 ORDER BY가 필요 없으며, 일반적으로 조회되는 순서를 유지하면서 성능을 유지할 수 있습니다.

데이터의 변경 빈도와 조회 빈도, 데이터의 양 등을 고려하여 적절한 방법을 선택해야 합니다. 데이터베이스의 성능과 유지보수 측면에서 적절한 데이터 관리 방법을 선택하는 것이 중요합니다.

 

---

 

데이터베이스 시스템에서는 각 테이블의 데이터가 어떤 블록에 저장되었는지 확인할 수 있는 기능을 제공합니다. 보통 이러한 정보를 확인하기 위해서는 DBMS에서 제공하는 패키지 또는 뷰를 사용합니다.

대표적인 패키지로는 Oracle 데이터베이스에서 제공하는 DBMS_ROWID 패키지가 있습니다. 이 패키지를 사용하면 ROWID를 통해 해당 데이터가 저장된 블록 번호를 확인할 수 있습니다.

예를 들어, 다음과 같이 SELECT 문을 실행하여 특정 테이블의 ROWID를 조회하고, DBMS_ROWID 패키지의 함수를 사용하여 해당 ROWID에 대한 블록 번호를 확인할 수 있습니다:

-- 테이블의 ROWID 조회
SELECT ROWID
FROM your_table_name
WHERE your_condition;

-- ROWID로 블록 번호 조회
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS block_number
FROM your_table_name
WHERE your_condition;

 

위의 예제에서 your_table_name은 원하는 테이블의 이름, your_condition은 원하는 조회 조건을 나타냅니다. 먼저 SELECT 문을 통해 특정 테이블의 ROWID를 조회한 후, DBMS_ROWID 패키지의 ROWID_BLOCK_NUMBER 함수를 사용하여 해당 ROWID에 대한 블록 번호를 확인하고 출력합니다.

다른 데이터베이스 시스템에서도 마찬가지로 해당 기능을 제공하는 패키지 또는 뷰가 있을 수 있으므로, 사용하는 데이터베이스 시스템의 문서를 참조하여 확인하시기 바랍니다.

 

 

4. 데이터베이스 설정 관련 튜닝

데이터베이스 설정을 조정하여 디스크 I/O와 메모리 사용을 최적화하는 방법을 소개합니다. 버퍼 캐시 사이즈, I/O 파라미터 등을 적절히 설정함으로써 성능을 향상시킬 수 있습니다.

 

1) 버퍼 캐시 사이즈 설정

버퍼 캐시는 데이터베이스가 메모리 상에서 데이터를 읽고 쓸 때 사용하는 메모리 공간입니다. 쿼리 실행 시 자주 사용되는 데이터를 메모리에 캐시하면 디스크 I/O를 줄여서 성능을 향상시킬 수 있습니다. 따라서 적절한 버퍼 캐시 사이즈를 설정하는 것이 중요합니다.

 

예를 들어, Oracle 데이터베이스에서는 SGA (System Global Area)의 DB_CACHE_SIZE 파라미터를 설정하여 버퍼 캐시의 크기를 조정할 수 있습니다. 다음은 DB_CACHE_SIZE를 2GB로 설정하는 예제입니다.

ALTER SYSTEM SET DB_CACHE_SIZE = 2G;

 

2) I/O 파라미터 설정

I/O 파라미터는 데이터베이스의 디스크 I/O 동작을 조정하는 파라미터들입니다. 이를 적절히 설정함으로써 디스크 I/O를 최적화할 수 있습니다.

 

예를 들어, MySQL 데이터베이스에서는 innodb_io_capacity 파라미터를 설정하여 I/O 처리량을 조정할 수 있습니다. 다음은 innodb_io_capacity를 1000으로 설정하는 예제입니다.

SET GLOBAL innodb_io_capacity = 1000;

 

이렇게 설정된 I/O 파라미터는 데이터베이스의 디스크 I/O 작업에 영향을 미치며, 데이터베이스 시스템의 성능을 조정하는 데 도움이 됩니다.

주의할 점은 데이터베이스 설정을 변경할 때에는 해당 설정이 데이터베이스 시스템의 성능에 어떤 영향을 미치는지를 신중히 고려해야 합니다. 또한 설정을 변경하기 전에 데이터베이스의 백업을 수행하고, 변경된 설정이 예상대로 동작하는지 테스트하는 것이 좋습니다. 디스크 I/O와 메모리 사용을 최적화하는 데에는 데이터베이스의 특성과 하드웨어 환경을 고려하여 적절한 설정을 찾는 것이 중요합니다.

 

5. 인덱스 컬럼 순서

인덱스를 생성할 때 인덱스 컬럼의 순서를 중요하게 고려하는 것은 데이터베이스 성능을 향상시키는데 큰 영향을 미칩니다. 특히 결합 인덱스(Composite Index)의 경우 인덱스 컬럼의 순서를 올바르게 설정함으로써 효율적인 인덱스를 구성할 수 있습니다.

다음은 예제를 통해 인덱스 컬럼 순서의 중요성을 설명하겠습니다.

 

예제 테이블:

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  status VARCHAR(10)
);

 

예제 1: 잘못된 인덱스 컬럼 순서

CREATE INDEX idx_orders_wrong ON orders(customer_id, order_date);

 

위 예제에서는 "customer_id"를 첫 번째 컬럼으로 하고 "order_date"를 두 번째 컬럼으로 하는 인덱스를 생성하였습니다. 그러나 주문을 조회할 때 주로 "order_date"를 기준으로 조회하는 경우가 많다고 가정해봅시다.

 

예제 쿼리:

SELECT * FROM orders WHERE order_date = '2023-07-25';

 

위 쿼리에서 "order_date"를 기준으로 조회하는데, 인덱스의 첫 번째 컬럼인 "customer_id"를 이용한 조회는 효과적이지 않습니다. 인덱스의 첫 번째 컬럼을 사용하는 경우, 불필요한 블록을 읽게 될 가능성이 높아져서 성능 저하를 초래할 수 있습니다.

 

예제 2: 올바른 인덱스 컬럼 순서

CREATE INDEX idx_orders_right ON orders(order_date, customer_id);

 

위 예제에서는 "order_date"를 첫 번째 컬럼으로 하고 "customer_id"를 두 번째 컬럼으로 하는 인덱스를 생성하였습니다. 이제 주문을 조회할 때 "order_date"를 기준으로 먼저 조회하므로, 인덱스를 효과적으로 활용할 수 있습니다. "order_date"로 인덱스를 검색하면 해당 인덱스 블록만 읽어올 수 있습니다.

정리하자면, 인덱스를 생성할 때에는 가장 자주 사용되는 조건 순서를 고려하여 인덱스 컬럼의 순서를 결정해야 합니다. 특히 결합 인덱스의 경우 첫 번째 컬럼은 가장 빈번하게 사용되는 조건으로 선택하고, 그 다음으로 자주 사용되는 조건을 순서대로 추가하는 것이 성능을 향상시키는데 도움이 됩니다. 올바른 인덱스 컬럼 순서를 설정하면 데이터베이스의 성능을 크게 향상시킬 수 있습니다.

 

 

목차