[데이터베이스개론·SQL] 250123

이슬비·2025년 1월 23일

Database sql

데이터베이스개론·SQL

목록 보기

8/9

인덱스

인덱스(Index)

데이터베이스 테이블의 검색 성능을 향상시키기 위해 사용되는 특별한 데이터 구조이다. 특정 데이터를 빠르게 찾아갈 수 있는 경로를 제공한다.

인덱스 사용 목적

검색 성능 향상.
쿼리 실행 시간 단축.
테이블의 특정 컬럼을 기준으로 데이터 정렬 및 관리.

인덱스 동작 원리

테이블 스캔(Table Full Scan)
- 인덱스가 없는 경우 테이블의 모든 데이터를 처음부터 끝까지 읽어야 함.
- 데이터 양이 많아질수록 성능 저하 발생.
인덱스 스캔(Index Scan)
- 인덱스를 사용하면 필요한 데이터가 저장된 위치를 빠르게 탐색 가능.
- 테이블의 일부만 읽기 때문에 성능 향상.

인덱스 구조

B-Tree 인덱스(가장 일반적인 구조).
균형 이진 트리 형태로 데이터 검색에 최적화.
데이터가 정렬된 상태로 유지되며, 검색, 삽입, 삭제 시 효율적.
루프 노드 $\rightarrow$ 중간 노드 $\rightarrow$ 리프 노드 순서로 탐색.
리프 노드에는 실제 데이터 위치 정보를 저장.

Sequential Access vs Random Access

Sequential Access
- 높은 효율.
- 데이터를 순서대로 읽거나 쓰는 방식.
- 인덱스 Leaf Block을 읽는 방식.
- Table Full Scan.
Random Access
- 낮은 효율.
- 논리적, 물리적 순서와 무관.
- 한 건을 읽기 위해 한 Block씩 접근.

테이블 스페이스 > 데이터 파일 > 세그먼트 > 익스텐트 > 블록

인덱스 장점

검색 속도 향상 : 데이터베이스에서 SELECT 쿼리를 실행할 때 효율적.
정렬된 데이터 관리 : 인덱스를 사용하면 데이터가 자동으로 정렬.
대용량 데이터 처리 : 테이블 크기가 커질수록 인덱스의 효율성이 증가.

인덱스 단점

추가 저장 공간 필요 : 테이블 외에도 별도의 공간을 차지.
쓰기 작업 성능 저하 : INSERT, UPDATE, DELETE 작업 시 인덱스도 함께 갱신되어야 함.
인덱스 과다 생성 문제 : 너무 많은 인덱스는 관리 비용 증가와 성능 저하를 초래.

인덱스 유형

인덱스 유형	설명	사용 예시
단일 컬럼 인덱스	하나의 컬럼만 기준으로 인덱스를 생성	고객 ID, 제품 코드 등 단일 검색 기준
결합 인덱스	두 개 이상의 컬럼을 조합하여 생성	이름 + 생년월일, 제품 + 카테고리 등
유니크 인덱스	중복을 허용하지 않는 인덱스	휴대폰 번호, 주민등록번호 등 고유 값

결합 인덱스는 컬럼의 순서가 중요하다.

인덱스 생성

-- 단일 컬럼 인덱스
CREATE INDEX idx_customer_name ON CUSTOMERS(name);

-- 결합 인덱스
CREATE INDEX idx_order_customer_date ON orders(customer_id, order_date);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_email ON users(email);

인덱스 확인 및 삭제

-- 인덱스 확인
SELECT index_name, table_name, uniqueness
FROM user_indexes;
-- WHERE table_name = 'CUSTOMERS';

-- 인덱스 삭제
DROP INDEX idx_customer_name;

SQL 작동 원리
SQL문 실행 $\rightarrow$ Parsing $\rightarrow$ Syntax 확인 $\rightarrow$ Semantic(권한) 체크
$\rightarrow$ Shared Pool 확인 $\rightarrow$ Execution : Soft Parsing.
$\rightarrow$ Optimization $\rightarrow$ Execution : Hard Parsing.

인덱스 관리 팁

자주 사용되는 컬럼에 생성
- WHERE 조건, JOIN, ORDER BY, GROUP BY에 자주 등장하는 컬럼.
적절한 인덱스 개수 유지
- 과도한 인덱스는 성능 저하를 초래.
쓰기와 읽기 비율 고려
- 읽기 작업이 많은 테이블에 적합.
데이터 중복도 파악
- 중복도가 낮은(Distinct가 높은) 컬럼은 B-Tree.
- 중복도가 높은 컬럼은 Bitmap.

데이터를 삽입하거나 수정, 삭제할 때 인덱스 구조를 갱신해야 하므로, 쓰기 작업에는 적절하지 않을 수 있다.

인덱스 설계 시 유의사항

인덱스 컬럼의 순서
- 결합 인덱스에서 SELECT 조건에 자주 사용되는 컬럼을 우선적으로 배치.
- = 연산자에 사용되는 컬럼을 우선적으로 배치.
- 예시 : WHERE NAME = 'John' AND AGE > 30 $\rightarrow$ (NAME, AGE) 순서로 인덱스 생성.
쓰기 작업 최적화
- 데이터 변경 작업이 빈번한 테이블은 최소 개의 인덱스 유지.
카디널리티
- 카디널리티(값의 고유도)가 높은 컬럼에 인덱스를 생성.
- 예시 : 주민등록번호 > 성별.
데이터 크기와 접근 빈도
- 대용량 데이터 테이블에서 인덱스는 필수.

"인덱스를 타지 못한다."

데이터베이스에서 "인덱스를 타지 못한다."는 것은 활용할 수 있는 인덱스가 있음에도 불구하고, SQL 쿼리가 인덱스를 활용하지 않고 테이블 전체를 스캔(Table Full Scan)하여 데이터를 조회하는 경우를 의미한다.

결과적으로는 불필요한 데이터 검색으로 인해 성능 저하가 발생한다.

인덱스를 활용하지 못하게 하는 주요 원인은 다음과 같다.

인덱스 컬럼에 함수 또는 연산 사용.

B-Tree에서 시작점을 못찾기 때문에 Table Full Scan을 함.

-- 인덱스 컬럼 'salary'에 함수 적용
SELECT *
FROM employees
WHERE ROUND(salary) = 5000;

-- 해결 방법 : 함수 사용 제거
SELECT *
FROM employees
WHERE salary BETWEEN 4999.5 AND 5000.5;

데이터 타입 불일치.

암시적 데이터 형 변환으로 인해 시작점을 못찾음.

-- 인덱스 컬럼 'employee_id'는 숫자 타입인데 문자로 비교
SELECT *
FROM employees
WHERE employee_id = '101';

-- 해결 방법 : 데이터 타입 일치
SELECT *
FROM employees
WHERE employee_id = 101;

와일드카드(%) 사용 위치.

와일드카드를 맨 앞에 사용하게 된다면, 시작점을 찾을 수 없음.

-- 인덱스가 사용되지 않음
SELECT *
FROM customers
WHERE name LIKE '%John%';

-- 해결 방법 : 와일드 카드 제거 또는 앞부분 고정
SELECT *
FROM customers
WHERE name LIKE 'John%';

NULL 값 처리.

인덱스는 NULL 값을 저장하지 않기 때문에 Table Full Scan을 함.

-- 인덱스를 사용할 수 없음
SELECT *
FROM employees
WHERE salary IS NULL;

-- 해결 방법 : NULL 값을 포함하는 별도 처리 필요
ALTER TABLE employees
MODIFY salary DEFAULT 0;

OR 조건이 포함된 경우.

각 조건을 별도 검색하므로 Table Full Scan을 초래할 수 있음.
UNION ALL을 활용함.

-- 인덱스 활용 어려움
SELECT *
FROM employees
WHERE department_id = 10 OR manager_id = 5;

-- 해결 방법 : UNION ALL로 분리
SELECT *
FROM employees
WHERE department_id = 10
UNION ALL
SELECT *
FROM employees
WHERE manager_id = 5;