데이터베이스 성능 최적화에서 가장 중요한 요소 중 하나는 인덱스 설계입니다. 올바르게 설계된 인덱스는 쿼리 성능을 극대화하고, 불필요한 테이블 액세스를 최소화하여 빠른 데이터 검색을 가능하게 합니다. 반대로, 비효율적인 인덱스 설계는 쿼리 실행 속도를 저하시킬 뿐 아니라 시스템 자원을 낭비하고, 전체 성능에 악영향을 미칠 수 있습니다. 이 포스팅에서는 인덱스 설계가 왜 중요한지, 설계 시 고려해야 할 핵심 요소들, 그리고 최적의 인덱스 설계를 위한 전략들을 자세히 설명하겠습니다.
3.4 인덱스 설계
3.4 인덱스 설계는 데이터베이스의 성능을 최적화하기 위한 필수적인 단계입니다. 잘못된 인덱스 설계는 성능 저하를 일으키고, 쿼리 실행을 비효율적으로 만듭니다. 이번 섹션에서는 인덱스 설계의 어려움부터 최적화 전략까지 자세히 설명합니다.
3.4.1 인덱스 설계가 어려운 이유
인덱스를 설계하는 과정에서 여러 가지 문제가 발생할 수 있습니다. 인덱스가 많아지면 DML 성능이 저하되고, 이는 TPS(Transaction Per Second) 감소로 이어질 수 있습니다. 또 다른 문제로는 데이터베이스 사이즈의 증가로 인한 디스크 공간 낭비와 관리 비용 상승이 있습니다.
컴퓨터 공학적 관점:
인덱스는 데이터 삽입 및 삭제 시마다 업데이트되며, 이는 정렬 유지를 위한 추가 연산을 요구합니다. 인덱스가 많아질수록 I/O 비용이 증가하고, 불필요한 인덱스 분할(Index Split)로 성능이 저하될 수 있습니다. 이러한 이유로 적절한 인덱스 수를 유지하는 것이 중요합니다.
예시 코드:
-- 인덱스가 많을 경우 DML 성능이 저하됨
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (101, 1001, SYSDATE);
위 코드에서 데이터가 삽입될 때, 다수의 인덱스가 존재하면 각각의 인덱스가 업데이트되어 성능에 부정적인 영향을 미칩니다.
3.4.2 가장 중요한 두 가지 선택 기준
인덱스를 설계할 때 가장 중요한 두 가지 기준은 다음과 같습니다:
- 조건절에 항상 사용하거나 자주 사용하는 컬럼을 선정한다: 자주 사용되는 조건절을 기준으로 인덱스를 만들어야 테이블 액세스를 최소화할 수 있습니다.
- ‘=’ 조건으로 자주 조회하는 컬럼을 앞에 둔다: 동등 조건(
=)을 사용하는 컬럼을 인덱스의 선두에 배치하는 것이 효율적입니다.
컴퓨터 공학적 관점:
WHERE 조건에서 ‘=’ 조건을 먼저 처리하는 것은 해시 함수와 비슷한 방식으로 빠르게 값을 찾을 수 있게 합니다. B-Tree 구조에서 ‘=’ 조건은 트리의 분기를 빠르게 결정하므로 쿼리 성능이 향상됩니다.
예시 코드:
-- ‘=’ 조건이 자주 사용되는 컬럼을 앞에 둔 인덱스
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- ‘=’ 조건을 사용하는 쿼리
SELECT order_id, customer_id, order_date
FROM orders
WHERE customer_id = 1001;
위 예시에서는 customer_id를 인덱스의 첫 번째 컬럼으로 둬서 동등 조건 조회 시 성능을 최적화합니다.
3.4.3 스캔 효율성 이외의 판단 기준
인덱스 설계 시 단순한 스캔 효율성 외에도 수행 빈도, 업무상 중요도, 클러스터링 팩터, 데이터량, DML 부하 등의 다양한 요소를 고려해야 합니다. 특히 NL 조인(Nested Loop Join)에서 Outer와 Inner 쪽 인덱스 선택은 중요한 역할을 합니다.
컴퓨터 공학적 관점:
Nested Loop 조인에서 Inner 테이블의 인덱스는 반복적으로 참조되므로, 성능 저하를 방지하기 위해 효율적인 설계가 필요합니다. 특히, Inner 테이블의 인덱스는 동등 조건을 우선으로 설정하여 테이블 액세스를 최소화해야 합니다.
예시 코드:
-- NL 조인을 사용하는 쿼리
SELECT b.product_code, b.product_name, a.customer_id, a.order_date
FROM orders a, products b
WHERE a.order_code = 'AC'
AND a.order_date BETWEEN '2023-01-01' AND '2023-01-31'
AND b.product_id = a.product_id;
위 예시에서는 Inner 테이블(products)의 인덱스 설계를 통해 조인 성능을 최적화할 수 있습니다.
3.4.4 공식을 초월한 전략적 설계
때로는 단순한 공식만으로는 최적의 인덱스를 설계할 수 없습니다. 예를 들어, 테이블에서 BETWEEN 조건과 = 조건을 동시에 사용하는 경우, 데이터 조회량과 범위를 고려해 전략적으로 인덱스를 설계해야 합니다.
컴퓨터 공학적 관점:
BETWEEN 연산자를 사용한 경우, 범위 검색이 발생하므로 이 때는 데이터 분포와 조회 빈도를 고려하여 인덱스를 설계해야 합니다. 이 과정에서 최적화된 인덱스를 사용하여 순차적 I/O를 극대화하고, 랜덤 I/O를 최소화하는 것이 중요합니다.
예시 코드:
-- BETWEEN 연산자를 활용한 인덱스 설계
CREATE INDEX idx_orders_dates ON orders (order_date, customer_id);
-- BETWEEN 조건을 사용하는 쿼리
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
AND customer_id = 1001;
3.4.5 소트 연산을 생략하기 위한 컬럼 추가
인덱스는 항상 정렬된 상태를 유지하기 때문에 ORDER BY나 GROUP BY에서의 소트 연산을 생략할 수 있습니다. 이를 통해 쿼리 성능을 극대화할 수 있습니다.
컴퓨터 공학적 관점:
소트 연산은 메모리와 CPU 자원을 소모하므로, 인덱스를 통해 이를 생략하면 성능이 크게 향상됩니다. 이를 위해 인덱스를 설계할 때, ORDER BY 절에 사용된 컬럼을 포함시키는 것이 중요합니다.
예시 코드:
-- 소트 연산 생략을 위한 인덱스 설계
CREATE INDEX idx_orders_sort ON orders (customer_id, order_date);
-- ORDER BY 절을 포함한 쿼리
SELECT order_id, customer_id, order_date
FROM orders
WHERE customer_id = 1001
ORDER BY order_date;
위 예시에서는 customer_id와 order_date를 인덱스에 포함하여, 추가적인 소트 연산을 생략했습니다.
3.4.6 결합 인덱스 선택도
인덱스 설계 시, 결합 인덱스의 선택도가 중요한 역할을 합니다. 결합 인덱스의 순서를 결정할 때, 선택도가 낮은(변별력이 높은) 컬럼을 앞에 두는 것이 유리합니다.
컴퓨터 공학적 관점:
선택도(Selectivity)는 전체 데이터 중 조건절에 의해 선택되는 레코드의 비율을 나타냅니다. 선택도가 높은 인덱스는 불필요한 테이블 액세스를 줄이고, 더 빠른 쿼리 처리가 가능합니다.
예시 코드:
-- 선택도가 낮은 컬럼을 앞에 둔 결합 인덱스
CREATE INDEX idx_customer_order ON orders (customer_id, order_date);
-- 결합 인덱스를 사용하는 쿼리
SELECT order_id, customer_id, order_date
FROM orders
WHERE customer_id = 1001
AND order_date BETWEEN '2023-01-01' AND '2023-01-31';
3.4.7 중복 인덱스 제거
중복된 인덱스는 불필요한 자원을 소비하고, 데이터베이스 성능에 악영향을 미칠 수 있습니다. 따라서 중복 인덱스를 제거하거나 통합하여 관리하는 것이 필요합니다.
컴퓨터 공학적 관점:
중복 인덱스는 불필요한 저장 공간을 차지하며, DML 성능을 저하시킵니다. 특히 동일한 선두 컬럼을 가진 인덱스는 하나로 통합하는 것이 바람직합니다.
예시 코드:
-- 중복 인덱스 제거
DROP INDEX idx_orders_old;
-- 통합된 인덱스 생성
CREATE INDEX idx_orders_new ON orders (customer_id, order_date, product_id);
3.4.8 인덱스 설계도 작성
효율적인 인덱스 설계를 위해서는 쿼리 실행 계획을 분석하고, 이를 기반으로 인덱스 설계도를 작성해야 합니다. 쿼리 실행 계획을 통해 쿼리 성능에 미치는 영향을 전략적으로 분석할 수 있습니다.
컴퓨터 공학적 관점:
데이터베이스는 쿼리 실행 계획을 통해 최적의 경로를 결정합니다. 이때 인덱스가 제대로 설계되어 있으면, 쿼리 최적화기가 선택하는 경로가 더 효율적이 됩니다. 실행 계획을 통해 예상되는 I/O 비용, CPU 비용, 그리고 메모리 사용량을 평가하여 인덱스를 최적화할 수 있습니다.
예시 코드:
-- 쿼리 실행 계획 분석
EXPLAIN PLAN FOR
SELECT order_id, customer_id, order_date
FROM orders
WHERE customer_id = 1001;
-- 실행 계획 결과 조회
SELECT * FROM table(DBMS_XPLAN.DISPLAY());
위 코드에서는 EXPLAIN PLAN을 사용하여 실행 계획을 분석하고, 쿼리 성능에 인덱스가 미치는 영향을 평가할 수 있습니다.
결론
효율적인 인덱스 설계는 데이터베이스 성능 최적화의 핵심입니다. 인덱스 설계를 통해 쿼리의 실행 속도를 높이고, 자원 사용을 최소화함으로써 보다 효율적인 데이터베이스 운영이 가능합니다. 중요한 것은 각 상황에 맞는 맞춤형 인덱스를 설계하는 것입니다. 단순한 공식만 따르는 것보다는, 데이터의 특성과 쿼리 패턴을 깊이 이해하고 최적의 경로를 찾는 것이 필요합니다. 이러한 전략적 접근을 통해 불필요한 인덱스를 줄이고, 필요한 인덱스만을 적재적소에 배치함으로써 데이터베이스의 성능을 크게 향상시킬 수 있습니다.