인덱스(Index)

• 4/9/2023
• 9 minutes to read

1. 인덱스(Index)란?

인덱스는 SQL Server의 성능을 향상시키는 데 도움이 되는 객체로, 테이블의 검색 속도를 향상시키기 위해 데이터 정보를 저장합니다. 하나의 테이블에 한 개 이상의 인덱스를 만들 수 있으며, SELECT, INSERT, UPDATE 문을 사용할 때 WHERE 절을 사용하여 조건에 따른 결과 값을 얻고자 할 때 인덱스를 사용하면 더 빨리 처리됩니다. 하지만 인덱스는 물리적으로 저장되어 있기 때문에 생성에 시간이 걸리고 공간도 필요합니다. 따라서 인덱스를 무턱대고 만드는 것보다는 필요한 곳에 적절하게 사용하면 좋은 성능을 유지할 수 있습니다.

인덱스를 책의 목차 또는 찾아보기와 비교할 수 있습니다. 목차 또는 찾아보기를 만드는 이유는 원하는 정보를 빠르게 찾아가기 위한 도움의 역할을 하는 것입니다. SQL Server에서의 인덱스도 이와 동일한 역할을 합니다. 인덱스를 만들면 정보 검색을 효율적으로 빠르게 할 수 있지만, 그만큼의 공간과 시간이 필요합니다.

2. 인덱스의 특징

• 데이터베이스는 대량의 데이터를 보조기억장치에 저장해 두고 관리하기 때문에 데이터들을 보다 효율적으로 관리하고 빠르게 검색할 수 있도록 데이터의 위치정보와 관련된 추가적인 정보 저장이 필요합니다.
• 검색의 수를 줄이기 위하여 특정 속성이나 필드에 지정하는 기능으로 갱신 속도는 떨어질 수 있습니다.

3. 인덱스의 종류

1. 클러스터드 인덱스 : 물리적인 필드의 순서와 인덱스의 순서가 동일한 인덱스 구조. 책의 차례
2. 넌클러스터드 인덱스 : 인덱스에는 실제 데이터의 위치 정보를 가지는 인덱스 구조. 책의 찾아보기
3. 유니크 인덱스 : 인덱스된 필드값이 중복되지 않는 인덱스 구조
4. 복합 인덱스 : 여러 개의 필드를 바탕으로 생성된 인덱스 구조

4. 클러스터드 인덱스와 넌클러스터드 인덱스

인덱스는 클러스터드 인덱스와 넌클러스터드 인덱스와 넌클러스터드 인덱스로 구분됩니다. 인덱스를 만드는 목적이 테이블의 검색 속도를 높이기 때문에 이 두 가지 인덱스가 필요합니다. 클러스터드 인덱스는 책의 앞부분에 나오는 차례와 비슷하고, 넌클러스터드 인덱스는 책의 뒷부분에 나오는 찾아보기와 같은 개념입니다. 좀 더 자세히 살펴보면 클러스터 인덱스는 테이블의 물리적인 행 순서와 인덱스의 순서가 동일하고, INSERT/UPDATE/DELETE문을 실행할 때 레코드를 다시 정렬시킵니다. 넌클러스터 인덱스는 실제 데이터가 저장되어 있는 테이블 외에 인덱스 테이블을 새로 생성합니다. 그래서 INSERT/UPDATE/DELETE문을 실행할 때 매번 새로 정렬시키는 것이 아니라 데이터 테이블에 값을 넣어놓고 인덱스 테이블에는 그 위치값을 기록하는 방식을 사용합니다. 일반적으로 넌클러스터 인덱스가 클러스터 인덱스에 비해 검색 속도가 느리며 저장 공간도 많이 필요합니다. 그렇지만 위 2개의 인덱스 방식을 적절히 사용하면 최상의 서버 성능을 낼 수 있습니다.

5. 클러스터 인덱스와 넌클러스터 인덱스가 필요한 곳과 필요하지 않는 곳

1. 클러스터 인덱스가 주로 필요한 필드
   • SELECT문의 WHERE 절에서 범위 검색의 대상이 되는 필드
   • ORDER BY 절에 의해 자주 사용되는 필드
2. 넌클러스터 인덱스가 주로 필요한 필드
   • SELECT문의 WHERE 절에서 포인트 검색(특정값 검색)의 대상이 되는 필드
   • PRIMARY KEY 및 FOREIGN KEY 등의 제약 조건이 걸리는 필드
   • JOIN을 자주 사용하는 필드
3. 인덱스가 필요하지 않는 필드
   • 검색에 사용되지 않는 필드
   • 같은 값들이 많이 들어오는 필드 (예: 성별)

6. 인덱스 만들기 문법

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX 인덱스이름
     ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) 
[ WITH < index_option > [ ,...n] ] 
[ ON filegroup ]

온라인 설명서에 나오는 인덱스 만드는 구문을 옮겨 적어봤습니다. 유니크 인덱스를 만들 때는 UNIQUE, 클러스터드 인덱스를 만들 때는 CLUSTERED, 넌클리스터드 인덱스를 만들 때는 NONCLUSTERED를 지정하고 인덱스를 만들면 됩니다. 단, 하나의 테이블에는 하나의 클러스터 인덱스만이 생성될 수 있고 넌클러스터드 인덱스는 여러 개(249개) 생성될 수 있습니다.

7. 인덱스 삭제하기 문법

DROP INDEX 인덱스이름

예제1. 클러스터 인덱스와 넌클러스터 인덱스 만들기(index.sql)

--[0] 테스트용 임시테이블 작성
USE master
go
CREATE DATABASE TestDB  -- DROP DATABASE TestDB
go
use TestDB
go
--DROP TABLE dbo.FrontBoard
CREATE TABLE dbo.FrontBoard  -- 웹사이트 전광판 기능 구현할 테이블
(
    ItemID int IDENTITY(1,1),--PRIMARY KEY 및 NOT NULL
    Title varchar(150) NULL,
    Url varchar(50) NULL,
    Target varchar(10) NULL,
    CreatedDate datetime default(getdate())
)
go
--[1] FrontBoard의 ItemID필드에 idxFrontBoard란 넌클러스터 인덱스 생성
CREATE INDEX idxFrontBoard
ON FrontBoard(ItemID)
go
--[2] sp_helpindex 시스템 저장 프로시저를 이용해서 인덱스 정보 확인
EXEC sp_helpindex FrontBoard
--[3] 인덱스 지우기
DROP INDEX FrontBoard.idxFrontBoard
go
--[4] Title필드에 유니크한 클러스터 인덱스 생성
CREATE UNIQUE CLUSTERED INDEX idxFrontBoard
ON FrontBoard(Title)
go
--[5] sp_helpindex 시스템 저장 프로시저를 이용해서 인덱스 정보 확인
EXEC sp_helpindex FrontBoard
--[6] 인덱스 지우기
DROP INDEX FrontBoard.idxFrontBoard  --인덱스 지우기
go

[1]번 항목을 보면 인덱스를 생성할 때 기본값인 CREATE INDEX로 생성하는데, PRIMARY KEY를 지정하게 되면 기본값으로 클러스터 인덱스가 만들어집니다. [2]번은 sp_helpindex라는 시스템 저장프로시저를 사용해서 FrontBoard라는 테이블에 있는 인덱스의 정보를 볼 수 있습니다. 인덱스는 수정이 불가능합니다. 그래서 인덱스는 삭제하고 새로 만들어야 합니다. 인덱스를 삭제할 경우에는 DROP INDEX문을 사용하면 됩니다. [4]번은 UNIQUE와 CLUSTERED라는 키워드를 앞에 붙여서 유니크하고 클러스터된 인덱스를 만들 수 있습니다. 하지만 하나의 데이터베이스에 클러스터 인덱스는 하나만 만들 수 있습니다.