[ SQL 레벨업 ] 10장. 인덱스 사용

1. 인덱스와 B-tree

RDB에서 사용하는 인덱스 3가지

B-tree 인덱스
비트맵 인덱스
해시 인덱스

1.1 만능형 : B-tree

데이터를 트리 구조로 저장하는 형태의 인덱스이다.

가장 많이 사용되며, 데이터베이스에서 '인덱스'라 말하면 대부분 B-tree를 지칭하는 것이다.

실제 수식을 붙이지 않 채 CREATE INDEX 구문을 실행하면 모든 DBMS에서 암묵적으로 B-tree 인덱스가 만들어진다.

트리 구조

계층적으로 데이터를 저장하고 관리하는 비선형 데이터 구조이다.

1️⃣ 각 계층

루트 노드
중간 노드
리프 노드

2️⃣ 용어

- 노드(Node) : 데이터를 저장하는 각 지점

- 루트(Root) : 트리의 최상위 노드

- 부모 노드 : 다른 노드를 직접 연결한 노드

- 자식 노드 : 다른 노드에 연결된 노드

- 리프 노드 : 더 이상 자식이 없는 맨 끝 노드

3️⃣ 특징

계층적 구조
1. 데이터가 위에서 아래로 분기
2. 명확한 부모-자식 관계
효율적인 검색
1. 이진 트리의 경우 O(log n) 시간 복잡도
2. 데이터 정렬과 검색에 최적화
유연한 구조
1. 삽입, 삭제가 비교적 용이
2. 다양한 형태로 변형 가능

4️⃣ B-tree 인덱스와의 연관성

각 노드가 여러 개의 자식을 가질 수 있음
데이터베이스 검색에 최적화
balanced(균형) 상태 유지

B-tree의 가장 중요한 특징을 2개 뽑아본다면

1) 트리 구조

2) 균형성

일 것이다.

균형성을 짧게 살펴보자면

✔️ 모든 리프 노드는 같은 깊이에 위치하며

✔️ 데이터 삽입/삭제 시 트리의 균형을 자동 유지한다.

1️⃣ B-tree 작동 원리 예시

간단하게 코드로 작동 원리를 살펴보자.

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

-- customer_id에 B-Tree 인덱스 생성
CREATE INDEX idx_customer_id ON Customers(customer_id);

특정 customer_id 로 검색을 요청한다.
B-tree 루트 노드부터 탐색을 시작한다.
각 노드에서 범위를 비교한다.
최종 리프 노드에서 데이터 위치를 확정한다.
해당 데이터에 직접 접근한다.

2️⃣ 실제 구조 시각화

               [50]
        /                 \
   [25, 35]           [75, 85]
  /    |    \       /    |    \
[10][20][30] [40] [60][70][80] [90]

3️⃣ 검색 시나리오

SELECT * FROM Customers WHERE customer_id = 75;

B-tree 루트 노드부터 시작
75를 찾아 직접 포인터로 데이터 접근
시간 복잡도 : O(log n)

4️⃣ 성능 분석

[ 장점 ]

빠른 검색 속도
범위 검색에 효율적
정렬된 데이터 유지

[ 단점 ]

삽입/삭제 시 재구조화 필요
추가 저장 공간 필요

[ 실행계획 ]

🪶 예상 쿼리 비용 : 1.00

🪶 단일 테이블 스캔

🪶 Customers 테이블에서 단 하나의 행만 조회하면 되므로 비용도 낮고 결과도 빠르게 반환될 것

🪶 Single Row (constant) 표시는 이 쿼리에서 고유 키 또는 기본 키 컬럼(customer_id)으로 필터링하고 있다는 것을 의미함

-> 이를 통해 db 엔진이 신속히 해당 행을 조회할 수 있음

사실 대부분의 데이터베이스에서는 B+tree를 채택한다.

B+tree가 무엇일까?

트리의 리프 노드에만 키값을 저장하는, B-tree의 수정 버전이다.

이는 B-tree에 비해 검색을 보다 효율적으로 만든 알고리즘으로, 파일 시스템 등에서도 사용된다.

B+tree는 루트와 리프의 거리를 가능한 일정하게 유지하려 한다.
- 이렇게 되면 균형이 잘 잡히므로 검색 성능이 안정적이게 된다.
트리의 깊이도 3-4 depth 정도의 수준으로 일정하고, 데이터가 정렬 상태를 유지한다.
- 이분 탐색을 통해 검색 비용을 크게 줄일 수 있다.
데이터가 정렬 상태를 유지한다는 특성을 잘 활용하면 집약 함수 등에서 요구되는 정렬을 하지 않고 넘어갈 수도 있다.

1.2 기타 인덱스

인덱스 종류	설명
비트맵 인덱스	데이터를 비트 플래그로 변환해서 저장하는 형태. 카디널리티가 낮은 필드에 대해 효과를 발휘함. 갱신할 때 오버헤드가 매우 큼. => 빈번한 갱신이 일어나지 않는 BI/DWH 용도로 사용됨.
해시 인덱스	키를 해시 분산해서 등가 검색을 고속으로 실행하는 형태. 등가 검색 외에는 효과가 거의 없고 범위 검색이 불가능함. => 거의 사용되지 않음.

2. 인덱스를 잘 활용하려면

인덱스를 사용한다고 문제가 바로 해결되지는 않는다.

인덱스를 잘 활용하는 방법을 살펴보자.

2.1 카디널리티와 선택률

2.1.1 카디널리티

모든 레코드에 같은 값이 들어가 있다 = 카디널리티가 낮은 필드이다

모든 레코드에 다른 값이 들어가 있다 = 카디널리티가 높은 유일 키 필드이다

2.1.2 선택률

특정 필드값 지정 시 전체 테이블에서 몇 개의 레코드가 선택되는지에 대한 비율이다.

ex. 100개의 레코드를 가진 테이블에서 유일키로 'pkey=3'처럼 지정 시 1개의 레코드가 선택될 때,

1/100 = 0.01로 선택률은 1%이다.

🟢 클러스터링 팩터 (clustering factor)

저장소에 같은 값이 어느 정도 물리적으로 뭉쳐 존재하는지를 나타내는 지표이다.

높을수록 분산되어 있고, 낮을수록 뭉쳐있다는 의미이다.

클러스터링 팩터가 낮을수록 접근할 데이터양이 적어져 좋다.

❓클러스터링 팩터는 어떻게 확인할까?

Oracle : DBA_INDEXES 뷰의 CLUSTERING_FACTOR

DB2 : SYSCAT.INDEXES 뷰의 CLUSTERRATIO 필드 또는 CLUSTERFACTOR 필드

2.2 인덱스를 사용하는 것이 좋은지 판단하려면

☑️ 카디널리티가 높은지 (값이 평균치에서 많이 흩어져 있다)

☑️ 선택률이 낮은지 (한 번의 선택으로 적은 레코드가 선택된다)

이 두 가지 지표를 확인해서 두 가지 모두 충족한다면 인덱스를 활용하기 좋은 조건임을 알 수 있다.

✍️ 저자의 의견

최근 DBMS 에서는 대체로 5~10% 이하가 기준이다.

5% 미만 : 인덱스 작성할 가치가 있다 (또는 있을 수도 있다)

10% 초과 : 테이블 풀 스캔이 더 빠를 가능성이 있다.

3. 인덱스로 성능 향상이 어려운 경우

데이터의 규모가 커질수록 성능 확보를 위해 인덱스 설계가 굉장히 중요해진다.

SQL의 검색 조건과 결합 조건을 바탕으로 효율적인 압축 조건을 찾아야 한다.

그러기 위해선 SQL 구문과 검색 키 필드의 카디널리티를 아는 것이 필요조건이다.

그런데 만약 압축 조건이 존재하지 않는다면 어떻게 해야 할까?

극단적인 사례를 통해 살펴보자.

아래는 Orders 테이블이다. (1억 개의 데이터가 들어있다고 가정)

3.1 압축 조건이 존재하지 않는 경우

SELECT order_id, receive_date
FROM Orders;

주문 테이블에서 모든 데이터를 검색하는 매우 간단한 select 구문이다.

실행계획을 보지 않아도 테이블 풀 스캔을 탄다는 것을 알 수 있다.

레코드를 압축하는 where 절이 없으므로 인덱스로 작성할 만한 필드도 존재하지 않는다.

이런 극단적인 경우는 실무에서는 거의 없다고 보면 된다.

3.2 레코드를 제대로 압축하지 못하는 경우

1번보다 자주 일어나고 번거로운 경우이다.

압축 조건이 있지만 제대로 압축하지 못하는 sql 구문이다.

SELECT order_id, receive_date
FROM Orders
WHERE process_flg = '5';

현 테이블의 process_flg 의 분포는 다음과 같다고 가정한다.

1(주문단계) : 200만 건
2(주문완료) : 500만 건
3(재고확인중) : 500만 건
4(배송준비중) : 500만 건
5(배송완료) : 8,300만 건

process_flg = '5' 라는 조건으로는 전체 데이터의 절반 이상이 선택된다.

선택률은 85%로 굉장히 높은 수치이므로 이 상태에서 process_flg 필드에 인덱스 생성 시, 풀스캔보다 느려질 수 있다.

인덱스가 제대로 작동하기 위해서는 레코드를 크게 압축할 수 있는 검색 조건이 필요하다.

즉, 이 예제에서는 인덱스가 의미가 없다.

제대로 압축하지 못하는 패턴에는 다양한 변종이 있다.

그 중 2가지만 살펴보자.

1️⃣ 입력 매개변수에 따라 선택률이 변동하는 경우 1

예를 들어 기간의 범위 검색의 경우를 살펴보자.

SELECT order_id
FROM Orders
WHERE receive_date BETWEEN :start_date AND :end_date;

:start_date와 :end_date는 외부에서 매개변수로 받는 값이다.

이 기간의 범위가 크면 클수록, 즉 입력에 따라 선택률이 높아지거나 낮아진다.

2️⃣ 입력 매개변수에 따라 선택률이 변동하는 경우 2

주문받은 점포(shop_id)를 검색 기준으로 입력하는 경우를 살펴보자.

SELECT COUNT(*)
FROM Orders
WHERE shop_id = :sid;

shop_id에 주어지는 매개변수 :sid의 규모(대규모 or 소규모)에 따라 따라오는 주문 건수가 상이하다.

예를 들어 대규모 점포라면 1,000만 건이 선택되고, 소규모 점포라면 10만 건이 선택된다 가정해보자.

전자의 선택률은 10%, 후자의 선택률은 0.01%이다.

전자의 경우에는 인덱스 스캔보다 풀스캔이, 후자의 경우에는 인덱스 스캔이 더 낫다.

❓왜 데이터 건수가 더 많은 경우에 테이블 풀스캔이 더 좋은 방법일까?

✔️ 1,000만 건에 해당되는 데이터를 검색할 때, 인덱스를 타게 되면 실제로 검색해야 하는 데이터 양이 너무 많다. 이 때에는 인덱스의 이점을 활용하기가 어렵다.

✔️ 1,000만 건의 데이터 중 많은 데이터가 필요하다면 테이블 풀 스캔을 사용해 전체 데이터를 한 번에 읽는 것이 더 유리할 수 있다.

✔️ 데이터가 많을수록 인덱스를 통한 랜덤 접근(랜덤 I/O)이 비효율적이다.

여기서 문제는 shop_id 필드에 인덱스가 존재하고, 전자의 경우에 인덱스를 타게 되면 성능이 악화된다는 것이다.

인덱스의 장점을 전혀 활용할 수 없게 된다.

3.3. 인덱스를 사용할 수 없는 (사용하지 않는) 검색 조건

압축할 검색 조건이 있으면서도 인덱스를 사용할 수 없는 타입

3.3.1 중간 일치, 후방 일치의 LIKE 연산자

SELECT order_id
FROM Orders
WHERE shop_name LIKE '%대공원%';

🙂 LIKE 연산자를 사용하는 경우 인덱스는 전방 일치('대공원%')에만 적용할 수 있다.

☹️ 중간 일치('%대공원%') 혹은 후방 일치('%대공원')는 인덱스를 사용할 수 없다.

3.3.2 색인 필드로 연산하는 경우

SELECT *
FROM SomeTable
WHERE col_1 * 1.1 > 100;

☹️ 색인 필드로 연산하는 경우에는 인덱스를 사용할 수 없다.

🙂 대신 검색조건의 우변에 식을 사용하면 인덱스를 사용할 수 있다.

아래와 같은 조건을 쓰면 된다.

WHERE col_1 > 100 / 1.1

3.3.3 IS NULL을 사용하는 경우

SELECT *
FROM SomeTable
WHERE col_1 IS NULL;

☹️ IS NULL을 사용하는 경우 인덱스를 사용할 수 없다.

(null 과 관련된 검색 조건에서 인덱스가 사용되지 않는 건 일반적으로 색인 필드의 데이터에 null이 존재하지 않기 때문)

SELECT *
FROM SomeTable
WHERE LENGTH(col_1) = 10;

☹️ 색인 필드에 함수를 사용하는 경우에도 인덱스를 사용할 수 없다.

색인 필드 자체에 연산을 하는 경우와 같기 때문이다.

인덱스 내부에 존재하는 값은 col_1 이지 LENGTH(col_1)이 아니기 때문이다.

3.3.4 부정형을 사용하는 경우

SELECT *
FROM SomeTable
WHERE col_1 <> 100;

☹️ 부정형(<>, !=, NOT IN)은 인덱스를 사용할 수 없다.

4. 인덱스를 사용할 수 없는 경우 대처법

인덱스를 사용하지 못하거나 오히려 성능이 떨어지는 경우 SQL 구문을 어떻게 튜닝해야 할까?

✔️ 어플리케이션에서의 설정으로 처리

✔️ 인덱스 온리 스캔

하나씩 상세하게 살펴보자.

4.1 외부 설정으로 처리

[ UI 설계로 처리 ]

가장 간단한 해결 방법이다.

처음부터 쿼리가 실행되지 않게 어플리케이션 단에서 제한하면 된다. (ex. 입력 제한 두기)

Oracle 테이블의 쿼리를 위의 웹 화면에서의 입력으로 생성한다고 할 때,
어플리케이션이 어떤 쿼리를 조합해 어떤 검색 조건을 만들지는 어플리케이션의 기능과 UI 설정에 크게 의존한다.

4.2 데이터 마트로 대처

데이터 마트 : 특정한 쿼리(군)에서 필요한 데이터만을 저장하는, 상대적으로 작은 크기의 테이블 (원래 테이블의 부분집합 또는 서브셋)

데이터 마트는 접근 대상 테이블의 크기를 작게 해 I/O 양을 줄이는 것이 목표이다.

데이터 마트 사용의 이점

성능 최적화
- 원본 테이블의 전체 데이터를 매번 스캔하는 대신, 미리 선별되고 가공된 데이터를 포함합니다.
- 필요한 컬럼만 미리 추출하고 인덱싱하여 쿼리 실행 속도를 크게 개선할 수 있습니다.
I/O 감소
- 대규모 데이터베이스에서 전체 테이블을 스캔하는 것은 많은 시간과 자원이 소요됩니다.
- 데이터 마트는 필요한 데이터만 미리 저장해 디스크 I/O 작업을 최소화합니다.
분석 최적화
- 특정 비즈니스 영역이나 부서에 최적화된 데이터 구조를 제공합니다.
- 복잡한 조인이나 집계 연산을 미리 수행하여 최종 사용자의 분석 효율성을 높입니다.

-- 압축 조건이 존재하지 않는 매우 간단한 SELECT 구문
SELECT order_id, receive_date
FROM Orders;

이 쿼리에서 필요한 데이터는 order_id 와 receive_date 두 개의 필드뿐이다.

이 때 데이터 마트가 있다면

CREATE TABLE OrderMart
(order_id       CHAR(4) NOT NULL,
receive_date    DATE NOT NULL);

아래와 같은 쿼리를 만들 수 있다.

SELECT order_id, reveive_date
FROM OrderMart;

그냥 보면 쿼리가 크게 달라지지 않은 것 같지만 성능적인 관점에서 보면 훨씬 빨라졌다고 볼 수 있다.

4.3 데이터 마트를 채택할 때의 주의사항 ⚠️

데이터 신선도

데이터 마트는 원래 테이블의 부분적인 복사본이므로 특정 시점마다 원본 테이블에서 데이터를 동기화해야 한다.

동기 사이클이 짧을수록 데이터의 신선도는 높으며, 원본 테이블과 가까운 모습을 가지게 된다.

대신 빈번한 갱신 처리가 실행되면 성능적인 문제가 발생할 가능성이 있다.

전통적으로 동기처리는 야간 배치로 실행하는 경우가 많은데, 하루에 한 번 정도 갱신되므로 신선도가 굉장히 낮아진다.

데이터 마트 크기

데이터 마트를 만드는 목적을 생각해보면 원 테이블의 크기를 작게 해 I/O 양을 줄이는 것이다.

하지만 원 테이블의 크기를 줄일 수 없다면 데이터 마트를 만들 필요가 없다. 성능상 이점이 없기 때문이다.

예를 들어 SELECT * 의 경우나, 검색 조건의 선택률이 높아 레코드를 제대로 압축하지 못하는 경우 데이터 마트를 만드는 것이 성능상 이점이 없다.

GROUP BY 절은 다르다. 이를 미리 사용해서 집계를 마친 후 데이터 마트를 만들면, 필드수와 레코드수를 줄일 수 있고, GROUP BY 에 필요한 정렬이나 해시 처리 또한 사전에 끝낼 수 있기 때문에 성능적인 개선이 가능하다.

데이터 마트 수

데이터 마트가 성능 개선에 유용하다고 판단되더라도 무자정 데이터 마트를 대량으로 만들어내면 안된다.

어떤 테이블이 어떤 처리에 사용되는지 혼동되면서 더 이상 사용되지 않는데도 쓸데없이 동기화가 일어나는 '좀비 마트'가 생기거나, 관리가 불가능해지기도 한다.

또한 데이터 마트 수가 늘어나면 그만큼 저장소 용량을 차지하게 되고, 백업이나 스냅샷에 소요되는 시간도 많아진다.

결론 : 데이터 마트에 지나치게 의존하지 말자!

배치 윈도우

데이터 마트는 어느 정도 규모의 갱신이 발생할 때 통계 정보 또한 다시 수집해야 한다.

이를 위해 배치 윈도우와 Job Net 을 고려해야 한다.

배치 윈도우란?

대규모 데이터 처리 작업을 수행하기 위해 할당된 특정 시간 범위이다.

1. 목적

대량의 데이터를 안정적으로 갱신
시스템 성능에 최소한의 영향을 미치면서 데이터 마트 새로고침
운영 시스템의 성능 저하 방지

2. 일반적인 배치 윈도우 시간

보통 시스템 사용량이 가장 적은 야간 시간대
ex : 오후 11시 - 새벽 3시
업무 특성에 따라 주말이나 특정 휴무 시간 활용

Jot Net과의 연계

Job Net은 배치 윈도우 내에서 수행되는 일련의 작업들을 조율하고 관리하는 시스템이다.

작업 순서를 제어하고
의존성을 관리하고
오류 발생 시 대응 전략 (오류에 대응하기 위한 시스템적 접근 방식)이며
리소스 할당을 최적화한다.

4.4 인덱스 온리 스캔으로 대처

외부 설계에 영향을 받지 않고 튜닝하는 방법 첫 번째가 데이터 마트였다면,

두 번째는 인덱스 온리 스캔이다.

SQL 구문이 접근하려는 대상의 I/O 감소가 목적이라는 점에는 데이터 마트와 같고, 특히 데이터 마트에서 문제가 되는 데이터 동기 문제를 해결할 수 있다.

이 방법은 기존 인덱스와는 사용 방법이 다르다.

이전의 압축할 수 있는 조건이 없었던 쿼리를 다시 살펴보자.

SELECT order_id, receive_date
FROM Orders;

위에서 인덱스는 WHERE 구문을 사용하지 않으면 원칙적으로 풀 스캔이 무조건 발생했었다.

하지만 이 쿼리에서 풀 스캔을 할 때 검사 대상 테이블을 Orders 테이블이 아닌 인덱스로 바꾼다면?

사전 작업으로 특정 필드를 커버할 인덱스를 작성해보자.

CREATE INDEX CoveringIndex ON Orders (order_id, receive_date);

기존 쿼리에서 order_id, receive_date 필드는 SELECT 구문에 포함되어 있었기 때문에 일반적으로는 인덱스 필드 후보가 되지 않는다.

하지만 이 2개의 필드를 커버하는 인덱스가 존재한다면?

테이블이 아닌 인덱스만이 검사(검색) 대상이 된다.

(이러한 인덱스를 Covering Index 라고 부른다)

인덱스 온리 스캔은 SQL 구문에서 필요한 필드를 인덱스만으로 커버할 수 있는 경우에 테이블 접근을 생략하는 기술이다.

인덱스는 테이블 필드의 부분 집합만 저장하므로 원 테이블에 비해 크기가 굉장히 작고, 데이터 마트를 사용할 때와 마찬가지로 I/O 비용을 줄일 수 있다.

실행계획을 살펴보면

INDEX FAST FULL SCAN | COVERINGINDEX 라는 생소한 계획을 볼 수 있는데 이것이 바로 인덱스를 사용한 풀 스캔을 의미한다.

여기서 주의깊게 봐야 하는 부분은 'Orders'라는 테이블 이름이 등장하지 않는다는 것이다.

왜냐? 테이블에 접근하지 않으니까!!

이전의 사례들을 활용해서 더 살펴보자.

[ 1 ]

SELECT order_id, receive_date
FROM Orders
WHERE process_Flg = '5';

필드를 커버하는 인덱스를 만들어보자.

CREATE INDEX CoveringIndex_1 ON Orders (process_flg, order_id, receive_date);

[ 2 ]

SELECT order_id, receive_date
FROM Orders
WHERE shop_name LIKE '%대공원%';

필드를 커버하는 인덱스를 만들어보자.

CREATE INDEX CoveringIndex_2 ON Orders (shop_name, order_id, receive_date);

이는 말하자면 로우(레코드) 지향 저장소의 DBMS에 유사적으로 컬럼(필드) 기반 저장소를 실현하는 것으로 봐도 좋다.

🔵 인덱스 온리 스캔과 컬럼 지향 데이터베이스

인덱스 온리 스캔은 특정 상황에서 검색 성능을 극단적으로 높일 수 있는 기능이다.

이는 컬럼 지향 데이터베이스를 로우 지향 데이터베이스에서 의사적으로 구현한 방법이라 할 수 있다.

무슨 말일까?

현재 존재하는 RDB는 대부분 로우(레코드) 지향 데이터베이스이다.

레코드 단위로 데이터를 저장한다.

이는 성능 관점으로 바라보면 비효율적이라 할 수 있다.

SELECT col_1
FROM SomeTable;

이 쿼리가 접근해야 하는 데이터는 col_1 뿐이다.

따라서 필드를 하나만 검색하는 것이 효율적인데 로우 지향 데이터베이스에서는 I/O가 레코드 단위로 이루어지므로 불필요한 다른 필드도 모두 읽어야 한다.

입출력적으로 큰 낭비가 발생하는 것이다.

컬럼 지향 데이터베이스는 실제로 SQL 구문에서 사용하는 필드는 굉장히 한정적이다 라는 사고를 바탕으로 만들어진 데이터베이스이므로, 말 그대로 데이터 저장 단위를 레코드에서 필드로 바꿔서 불필요한 필드를 읽지 않도록 만든 방법이다.

앞서 살펴본 SELECT 구문에서 일부 필드만 살펴보므로 데이터 입출력을 크게 감소시킬 수 있다.

하지만 이와 같은 방법이 단점으로 적용하는 경우도 있다.

극단적이긴 하지만 확실하게 확인할 수 있는 쿼리를 살펴보자.

SELECT *
FROM SomeTable
WHERE col_1 = 'A';

WHERE 절에 조건이 있기는 하지만 결국 모든 필드에 접근해야 하는 쿼리이므로 로우 지향 데이터베이스보다 성능이 떨어진다.

이처럼 많은 필드를 사용하는 SQL 구문에는 컬럼 지향 데이터베이스가 어울리지 않는다.

☑️ 인덱스 온리 스캔의 구문에서 사용하는 필드를 커버하는 인덱스를 만들어 접근한다는 관점에서 컬럼 지향 데이터베이스와 유사하다는 것을 알 수 있다.

4.5 인덱스 온리 스캔의 주의사항 ⚠️

✔️ DBMS에 따라 사용할 수 없는 경우가 있다.

✔️ 한 개의 인덱스에 포함할 수 있는 필드 수에 제한이 있다.

구현 의존적인 주의사항이다.

인덱스의 크기는 무제한이 아니며, 포함할 수 있는 필드 수 또는 크기에 제한이 있다.

✔️ 갱신 오버 헤드가 커진다.

인덱스는 테이블의 갱신 부하를 올린다.

인덱스 온리 스캔을 위한 커버링 인덱스는 성질상 필연적으로 필드 수가 많기에 크기가 큰 인덱스가 된다.

검색을 고속으로 만드는 대신, 갱신 성능이 떨어지는 트레이드 오프가 발생할 수 있다.

✔️ 정기적인 인덱스 리빌드가 필요하다.

인덱스에만 접근한다. = 검색 성능 자체가 인덱스의 크기에 의존한다.

ORACLE의 INDEX FAST FULL SCAN 등은 인덱스로 풀 스캔을 수행하므로 검색 성능이 인덱스 크기에 거의 비례하며, 일반적인 인덱스보다 크기에 성능이 민감하게 반응한다.

이 때문에, 커버링 인덱스의 정기적인 크기 모니터링과 리빌드를 운용에 포함시켜야 한다.

✔️ SQL 구문에 새로운 필드가 추가된다면 사용할 수 없다.

당연한 얘기지만, 새로운 필드가 추가도니다면 인덱스 온리 스캔을 사용할 수 없다.

커버링 인덱스는 SQL 구문에서 사용하는 필드를 모두 커버할 수 없게 된 시점에서, 더 이상 커버링 인덱스가 아니다.

즉, 인덱스 온리 스캔은 일반적인 인덱스에 비해 유지 보수에 약한 타입의 튜닝임을 알 수 있다.

5. 정리하며

B+tree 인덱스는 편하지만, 카티널리티와 선택률에 따라 성능이 결정된다.
선택률 제어를 위해 UI 설계까지 변경할 필요가 있다.
선택률이 높은 경우 인덱스 온리 스캔을 활용하는 것이 효율적이다.
인덱스를 사용한 성능 개선 또한 I/O 비용을 줄이기 위한 노력임을 잊지 말자.

'DATABASE > 📖 SQL 레벨업' 카테고리의 다른 글

[ SQL 레벨업 ] 7장. 서브쿼리 (0)	2024.12.12
[ SQL 레벨업 ] 4장. 집약과 자르기 (0)	2024.12.03

1. 인덱스와 B-tree

1.1 만능형 : B-tree

1.2 기타 인덱스

2. 인덱스를 잘 활용하려면

2.1 카디널리티와 선택률

2.1.1 카디널리티

2.1.2 선택률

2.2 인덱스를 사용하는 것이 좋은지 판단하려면

3. 인덱스로 성능 향상이 어려운 경우

3.1 압축 조건이 존재하지 않는 경우

3.2 레코드를 제대로 압축하지 못하는 경우

3.3. 인덱스를 사용할 수 없는 (사용하지 않는) 검색 조건

3.3.1 중간 일치, 후방 일치의 LIKE 연산자

3.3.2 색인 필드로 연산하는 경우

3.3.3 IS NULL을 사용하는 경우

3.3.4 부정형을 사용하는 경우

4. 인덱스를 사용할 수 없는 경우 대처법

4.1 외부 설정으로 처리

4.2 데이터 마트로 대처

4.3 데이터 마트를 채택할 때의 주의사항 ⚠️

4.4 인덱스 온리 스캔으로 대처

4.5 인덱스 온리 스캔의 주의사항 ⚠️

5. 정리하며

'DATABASE > 📖 SQL 레벨업' 카테고리의 다른 글

티스토리툴바