Portfolio

Index ???1️⃣ 인덱스란?인덱스의 종류 인덱스 생성, 삭제, 조회 2️⃣ 인덱스를 사용하는 이유(장점)조건 검색 WHERE 절의 효율성 정렬 ORDER BY 절의 효율성 MIN, MAX의 효율적인 처리가 가능 3️⃣ 인덱스를 사용하면 무조건 효율이 좋을까?(단점)인덱스는 DML에 취약하다 무조건 인덱스 스캔이 좋은 것은 아니다 속도 향상을 위해 인덱스를 많이 만드는 것은 좋지 않다.4️⃣ 인덱스의 관리 5️⃣ 인덱스 주의사항 LIKE 검색 시 NULL 관련 부정연산자 형 변환 혹은 값 변환 OR구문 왼쪽절 6️⃣ 인덱스 생성 전략 생성전략 어떤 컬럼에 인덱스를 저장할까?7️⃣ REF

Index ???

1️⃣ 인덱스란?

인덱스는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조입니다.

테이블을 만들고 저장할 때, 데이터는 내부적으로 힙영역(Heap)에 저장이 됩니다. 만약 인덱스가 없는 테이블 혹은 컬럼으로 조회를 하게 되면 테이블 전체를 뒤지는 풀 스캔을 하게됩니다.

특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장됩니다.

인덱스가 생성 되었다면 쿼리문에 인덱스 생성 컬럼을 WHERE 조건으로 거는 등의 작업을 하면 옵티마이저에서 판단하여 생성된 인덱스를 탈 수가 있습니다.

옵티마이저? 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진입니다. (컴퓨터의 CPU처럼 DMBS의 CPU는 옵티마이저라고 할 수 있습니다.)

인덱스는 insert, update, delete 등을 하게되면 index를 재갱신 하기 때문에 입력, 수정, 삭제는 최대한 적고 검색이 많은 테이블에서 최고의 성능을 뽑을 수 있습니다.

🔥

책 뒷편에 “색인"을 인덱스의 역할로 볼 수 있습니다. 색인을 통해서 원하는 키워드에 대한 페이지로 바로 이동할 수 있습니다 !

인덱스의 종류

Clustered Index (클러스터드 인덱스)

테이블 당 한개만 생성이 가능하다. 기본키(PK)를 생성하면 자동으로 클러스터 인덱스를 생성한다.
해서, 클러스터 인덱스가 있으면 해당 인덱스를 기준으로 정렬을 하게 된다.

NonClustered Index (넌클러스터드 인덱스)

한개의 테이블에 여러개의 인덱스를 선언할 수 있다. 클러스터인덱스 제외한 모든 인덱스를 말한다.

인덱스 생성, 삭제, 조회

생성


CREATE CLUSTERED / NONCLUSTERED INDEX 인덱스명 ON 테이블명 (
		정렬컬럼명 정렬기준(오름차순/내림차순)
)

# 예)
CREATE INDEX TestIndex ON TestTable(names)

삭제


DROP INDEX 테이블명.인덱스명

조회


SP_HELPINDEX "테이블명"

2️⃣ 인덱스를 사용하는 이유(장점)

인덱스의 가장 큰 특징은 데이터들이 정렬되어 있다는 점입니다. 이 특징으로 인해 조건 검색이라는 영역에서 큰 장점이 될 수 있습니다

조건 검색 WHERE 절의 효율성

테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드(row : 행)는 내부적으로 순서가 없이 뒤죽박죽 저장이 됩니다. 이렇게 된다면 WHERE절에 특정 조건에 맞는 데이터들을 찾아 낼 때도 레코드의 처음부터 끝까지 모두 읽어서 검색 조건과 맞는지 비교를 해야하는데 이것을 풀 테이블 스캔 이라고 말하며 줄여서는 풀 스캔이라고 말합니다.

하지만 인덱스 테이블 스캔 시 인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건에 맞는 데이터들을 빠르게 찾아낼 수 있다는 장점이 있습니다. 이것이 인덱스를 사용하는 가장 큰 이유입니다.

정렬 ORDER BY 절의 효율성

인덱스를 사용하면 ORDER BY에 의한 정렬(Sort) 과정을 피할 수 있습니다. ORDER BY는 굉장히 부하가 많이 걸립니다.

정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생되기 때문입니다.
디스크 I/O는 간단히 말해 우리가 데이터를 작성하고 변경할 적에 디스크 즉 HDD에 저장되는 것을 말합니다.

하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 됩니다. 그 이유는 이미 정렬이 되어 있기 때문에 가져오기만 하면 되기 때문입니다.

MIN, MAX의 효율적인 처리가 가능

데이터가 정렬되어 있기 때문에 얻을 수 있는 장점으로 MIN 값과 MAX 값을 레코드의 시작 값과 끝 값 한 건씩만 가져오면 되기 때문에 풀스캔으로 테이블을 모두 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있습니다.

3️⃣ 인덱스를 사용하면 무조건 효율이 좋을까?(단점)

인덱스가 주는 혜택이 있다면 그에 따른 부작용도 존재합니다. 인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지시켜줘야 한다는 점 입니다. 그렇기에 레코드 내에 데이터 값이 바뀌는 부분이라면 악영향을 미칩니다.

인덱스는 DML에 취약하다

INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 인덱스 테이블 내에 있는 값들을 다시 정렬을 해야합니다. 그리고 위에 예시처럼 인덱스 테이블, 원본 테이블 이렇게 두 군데의 데이터 수정 작업을 해줘야 한다는 단점도 발생합니다.

그렇기 때문에 DML이 빈번한 테이블보다 검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋습니다.

무조건 인덱스 스캔이 좋은 것은 아니다

검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋지만 무조건 검색 시에도 인덱스가 좋은 것은 아닙니다.

인덱스는 테이블 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는것이 더 낫습니다.

직관적은 예시로 1개의 데이터가 있는 테이블과 100만개의 데이터가 들어있는 테이블이 있을 때 100만 개의 데이터가 들어있는 테이블 이라면 풀 스캔보다 인덱스 스캔이 유리하겠지만, 1개의 데이터가 들어있는 테이블은 굳이 인덱스 스캔 없이 풀 스캔이 더 빠를 것입니다.

속도 향상을 위해 인덱스를 많이 만드는 것은 좋지 않다.

인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요합니다. 무턱대고 인덱스를 만들어서는 결코 안됩니다.

즉 속도 향상에 비해 단점들의 COST(비용)를 비교해서 인덱스를 만들지 말지를 정해야 합니다.

왜 인덱스를 남발하면 안되는 것일까?

데이터베이스 서버에 성능 문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성입니다.
문제가 발생할 때마다 인덱스를 생성하면서 인덱스가 쌓여가는 것은 하나의 쿼리문을 빠르게는 만들 수 있지만, 전체적인 데이터베이스 성능의 부하를 초래합니다.
조회 성능을 극대화하려 만든 객체인데 많은 인덱스가 쌓여서 INSERT, UPDATE, DELETE 시에 부하가 발생해 전체적인 데이터베이스 성능을 저하시키게 됩니다.
그렇기에 인덱스를 생성하는 것보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야 합니다. 인덱스 생성은 마지막 수단으로 강구해야할 문제입니다.

4️⃣ 인덱스의 관리

인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있습니다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 계속 정렬을 해주어야 하고 그에 따른 부하가 발생하게 됩니다.

이런 부하를 최소화하기 위해 인덱스는 “데이터 삭제"라는 개념에서 “인덱스를 사용하지 않는다"라는 작업으로 이를 대신합니다.

INSERT : 새로운 데이터에 대한 인덱스를 추가한다.

DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행한다.

UPDATE : 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가한다.

5️⃣ 인덱스 주의사항

인덱스가 분명 있는 컬럼이지만 인덱스를 타지 않는 경우가 있다. 이런 경우를 정리하여 사용할 때 주의해야 한다.

LIKE 검색 시

LIKE 검색을 할 때 %문자% 이런식으로 검색을 많이 합니다.

하지만 이름 문자% 로 검색을 하는 것이 좋다.

NULL 관련

NULL 구분으로 인덱스 컬럼을 검색하면 인덱스를 타지 않는다.

즉 IS NULL 혹은 IS NOT NULL 위 조건으로 검색을 하면 인덱스가 있어도 인덱스를 타지 않는다.

부정연산자

부정연산자는 종류가 여러개 있다 대표적인 예로 ≠, <>, NOT IN, NOT EXISTS등이 있으며 이런 경우에도 인덱스를 타지 않는다.

형 변환 혹은 값 변환

인덱스가 있는 컬럼에 형 변환 혹은 값 변환이 있으면 인덱스를 타지 않는다.

즉 NUM + 1 = 100, ISNULL(NUM,1)=100 이런 것처럼 조건을 주면 인덱스를 타지 않는다.

OR구문

인덱스가 걸린 컬럼과 OR구문으로 연동된 다른 컬럼으로 조회할 경우 테이블 전채 스캔을 하며 인덱스를 타지 않는다

왼쪽절

WHERE로 조건을 걸었을 때 왼쪽의 조건에만 인덱스를 탄다

즉 AA.NAMES = BB.NAMES 이런 조건으로 검색을 한다면 AA 테이블에 NAMES에 INDEX가 설정되어 있으면 인덱스를 타지만 AA테이블에만 인덱스가 걸려있지 않고, BB 테이블에만 인덱스가 걸려있으면 인덱스를 타지 않는다.
검색 시 왼쪽에 인덱스가 걸린 컬럼을 적어 주어야 한다.

6️⃣ 인덱스 생성 전략

생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다.

인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 인덱스 테이블이 생성됩니다.

이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋습니다. 가장 최선은 PK로 인덱스를 거는 것이라고 할 수 있습니다. 중복된 값이 없는 인덱스 테이블이 최적의 효율을 발생 시키겠고, 반대로 모든 값이 같은 컬럼의 인덱스 컬럼이 된다면 인덱스로써의 가치가 없다고 봐야합니다.