[MSSQL] 인덱스(INDEX) #1 + 페이지(Page)

 

 안녕하세요 코북입니다. SQL을 공부할 때 INDEX에 대해서 많이 공부해보지 않았고 실제로 프로젝트 참여하면서 INDEX를 사용해 본 경험이 없었습니다. 하지만 실제 현업에서 대량의 데이터를 처리할 때 필수적으로 사용되는 게 바로 INDEX인 것 같습니다. 편식하며 공부했던 것을 반성하며 오늘은 INDEX에 대해 공부해보려고 합니다.

 

 Index에 대해 공부하기 전에, 페이지라는 용어에 대해 알고 있어야 합니다.

---

페이지(Page)?

 

 책이 페이지로 구성되어 있는 것처럼 SQL 서버도 여러 Page로 구성되어 있으며 크기는 8KB입니다. SQL 서버의 가장 기본적인 단위로 실제로 SQL 서버에 데이터의 접근 시에는 Page 단위로 접근합니다. Page는 대부분 Data로 구성되어 있지만 일부 페이지는 인덱스(데이터 위치), 텍스트/이미지 등등으로도 구성되어 있습니다. 각 페이지는 96바이트의 헤더로 구성되어 있으며, 헤더는 페이지에 대한 시스템 정보를 저장합니다. 추가로 정리해보면 다음과 같습니다.

 

• 데이터 파일을 구성하는 논리 단위
• SQL Server의 기본 데이터 저장 단위 ( 8KB )
• 데이터를 쓸 때 행이 페이지에 기록됨
• 데이터를 읽을 때 페이지 내의 모든 행이 읽어짐
• 페이지 내의 행이 많을 수록 I/O 효율 증가
• 페이지 번호는 0~n 사이의 순차적인 번호가 존재

 

데이터를 Insert하게 된다면 페이지에 들어가게 되고, 테이블에 쿼리를 날려 조회하는 것이 아니라 페이지에서 SELECT하는 것으로 생각하면 됩니다. 

 

---

인덱스(Index)?

 

인덱스(Index)란 데이터를 빠르게 조회할 수 있도록 하는 역할을 합니다. 쉽게 설명하면 책의 목차같은 기능이라고 생각하면 됩니다. 만약 인덱스가 설정되어 있지 않다면 테이블의 모든 영역을 스캔하게 되고 속도와 효율이 떨어지게 됩니다. 그러나 인덱스가 존재한다고 무조건 속도가 빨라지는 것은 아닙니다. 조회(SELECT)할 때는 빠르지만 INSERT, UPDATE 등을 수행할 때는 느려지기 때문에 조회가 많은 테이블을 기준으로 설정해야 합니다. 이외에 장단점을 적어보면 다음과 같습니다.

 

 

인덱스(Index)는 왜 사용하고 장점은 무엇인가?

 

• 빠른 데이터를 검색하기 위해서 입니다. 찾는 데이터를 가지고 있다면 직접 주거나, 없다면 어디 있는지 알려 줍니다.
• 데이터의 중복을 방지할 수 있습니다. (Primary Key, Unique)
• 잠금을 최소화 시켜줍니다. (==동시성을 높여줍니다)

 

 

인덱스(Index)의 단점은 무엇인가?

 

• 물리적인 공간을 차지하게 됩니다. (인덱스도 테이블처럼 데이터를 가지므로 물리적인 공간을 차지하게 됩니다.)
• 페이지를 가지고 있는 존재는 데이터와 인덱스 두 가지입니다. (프러시저/뷰는 사이즈가 없습니다.)
• 인덱스에 대한 유지관리 부담이 존재합니다.
• 데이터가 극히 적다면 얻는 효과보다 유지관리 부담이 더 클 수 있습니다.

 

 

인덱스(Index)의 종류

 

1. 클러스터 인덱스 (Cluster index)

테이블당 한 개만 생성이 가능하며 기본키(Primary Key)를 생성하면 자동으로 생성됩니다.

테이블은 클러스터 인덱스를 기준으로 정렬되어 테이블에 저장됩니다.

 

2. 넌클러스터 인덱스 (Noncluster index)

테이블에 여러개를 생성할 수 있습니다.

클러스터 인덱스를 제외한 모든 인덱스를 말하며, 클러스터 인덱스보다 효율은 조금 떨어지지만 인덱스가 있는 것과 없는 것은 큰 차이가 있기 때문에 필요시 생성하면 됩니다.

 

 

인덱스 생성, 삭제, 조회

 

1-1. Index 생성 (중복 허용)

인덱스는 기본적으로 Noncluster로 생성됩니다.

Cluster로 생성하고 싶다면 명시해주면 됩니다.

CREATE CLUSTER/NONCLUSTER INDEX [인덱스명] 
ON [테이블명]([정렬컬럼명] [정렬기준]) 

CREATE INDEX [인덱스명] 
ON [테이블명]([정렬컬럼명] [정렬기준]) -- NONCLUSTER로 생성

CREATE CLUSTER INDEX I_TEST 
ON CO_TABLE(NAME ASC)

 

1-2. UNIQUE Index 생성 (중복 비허용)

INDEX와 UNIQUE INDEX는 둘 다 조회를 효과적으로 하는 역할을 하지만 UNIQUE Index가 설정한 컬럼은 중복 데이터가 들어갈 수 없고, Index가 설정된 컬럼은 중복 데이터가 들어가도 되는 차이점이 있습니다.

CREATE UNIQUE CLUSTER/NONCLUSTER INDEX [인덱스명] 
ON [테이블명]([정렬컬럼명] [정렬기준]) 

CREATE UNIQUE INDEX [인덱스명] 
ON [테이블명]([정렬컬럼명] [정렬기준]) -- NONCLUSTER로 생성

CREATE UNIQUE CLUSTER INDEX I_TEST 
ON CO_TABLE(NAME ASC)

 

2. Index 삭제

DROP INDEX[테이블명, 인덱스명]
DROP INDEX[CO_TABLE, I_TEST]

 

3. Index 조회

MSSQL 내부 명령어를 이용해서 인덱스 정보를 조회할 수 있습니다.

SH_HELPINDEX '테이블명'
SH_HELPINDEX 'CO_TABLE'

 

 

인덱스 최적화

 

1. SELECT 쿼리만 하는 테이블에서 성능이 가장 좋습니다. INSERT, UPDATE, DELETE 등의 작업은 인덱스를 새로 갱신하는 작업을 하기 때문에 성능이 저하됩니다. 

 

2. 조건절(WHERE)에서 많이 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다. 또한 동일한 데이터가 적은 컬럼이나 조인(JOIN)을 할 때 사용되는 컬럼을 인덱스로 생성해주는 게 좋습니다.

 

3. 인덱스 컬럼에는 NULL 값을 넣지 않습니다. 추가적으로 NULL 값으로 인덱스 검색 시 (IS NULL, IS NOT NULL) 인덱스가 존재해도 인덱스를 타지 않습니다.

 

4. 조건절(WHERE)에서 인덱스가 걸린 컬럼을 검색할 때 같은 형식으로 검색합니다. 예를 들어 인덱스가 VARCHAR 타입의 컬럼이면 NUM = '111', 인덱스가 INT 타입의 컬럼이면 NUM = 111 이런 식으로 검색을 해야 합니다.

 

5. 다중(복합)컬럼 인덱스를 사용하는 경우에는 많이 사용하는 컬럼 순서대로 생성합니다.

 

 

인덱스 타지 않는 경우

 

1. 조건문(WHERE)로 조건을 걸면 왼쪽의 조건에서만 인덱스를 탑니다. 왼쪽의 조건 컬럼이 인덱스가 없다면 오른쪽 조건 컬럼에 인덱스가 있어도 인덱스를 타지 않습니다. 예를 들어 AA.NAMES = BB.NAMES 이런 조건으로 검색을 한다면 AA테이블의 NAMES에 인덱스가 설정되어 있으면 인덱스를 타지만 AA테이블에는 인덱스가 걸려있지 않고 BB테이블에만 인덱스가 걸려있으면 인덱스를 타지 않습니다. 즉, 검색 시 왼쪽에 인덱스가 걸린 컬럼을 적어야 합니다.

 

2. 위에서 말한 것처럼 NULL 구분으로 인덱스 컬럼을 검색하면 인덱스를 타지 않습니다. 즉 IS NULL 혹은 IS NOT NULL 위 조건으로 검색을 하면 인덱스가 있어도 인덱스를 타지 않습니다.

 

3. LIKE 문 사용 시 '%문자열%'로 사용하면 인덱스를 타지 않으니 '문자열%'로 사용해야 합니다.

 

4. 부정 연산자를 사용하는 경우 인덱스를 타지 않습니다. (NOT IN, NOT EXISTS, <>, !=)

 

5. 인덱스가 걸린 컬럼과 OR 구문으로 연동된 다른 컬럼으로 조회할 경우 테이블 풀 스캔을 진행하기 때문에 인덱스를 타지 않습니다.

 

6. 인덱스가 있는 컬럼에 형 변환 혹은 값 변환이 있으면 인덱스를 타지 않습니다. 예를 들어 NUM + 1 = 100, ISNULL(NUM, 1) = 100 처럼 조건을 주면 인덱스를 타지 않습니다.

 

 

 

오늘은 여기까지만 하고

인덱스에 대한 더 자세한 내용은 #2에서 다루도록 하겠습니다.

 

 

 

 

본 글은 아래 링크의 내용을 참고하여 학습한 내용을 나름대로 정리한 글임을 밝힙니다.

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=diceworld&logNo=220180322020

https://bebeya.tistory.com/entry/MSSQL-%EC%9D%B8%EB%8D%B1%EC%8A%A4index-%EC%83%9D%EC%84%B1-%EC%82%AD%EC%A0%9C-%EC%A1%B0%ED%9A%8C-%EC%B5%9C%EC%A0%81%ED%99%94

https://im-first-rate.tistory.com/1

https://jungwoong.tistory.com/32