INDEX

Ref.
[MS-SQL]인덱스에 대한 정리

정의

색인과 같은 것.

수많은 데이터에서 조회하기 위해서는 많은 시간이 소요된다.

인덱스를 설정하여 그 시간을 줄일 수 있다.

“데이터를 좀 더 빠르게 찾을 수 있게 해주는 도구”

데이터의 중복값이 없음을 보장해준다.

기준없이 저장한 데이터베이스에서 찾는 것보다 특정기준으로 정렬된 데이터베이스에서 찾는 것이 더 빠르다.

종류

• Clustered Index : 테이블 당 한개만 생성이 가능합니다. 기본키(PK, Primary Key)를 생성하면 자동으로 클러스터 인덱스를 생성합니다. 클러스터 인덱스가 있으면 해당 인덱스를 기준으로 정렬을 하게 됩니다.
• NonClustered Index : 한개의 테이블에 여러개의 인덱스를 선언할 수 있습니다. 클러스터인덱스를 제외한 모든 인덱스를 말합니다.

특징

1. 항상 최신의 정렬상태를 유지한다.
2. 인덱스를 저장해 놓을 저장공간이 필요하다.

적용 시점

1. 중복도가 낮은 컬럼.
2. where, join ,order by 에 자주 사용되는 컬럼.
3. insert, update, delete가 자주 발생하지 않는 컬럼.
4. 규모가 작지않은 테이블
   • 인덱스를 생성할때 추가 저장공간을 필요로 하기 때문에 규모가 작지않은 테이블은 추가 저장공간 사용대비 성능(속도) 효율을 뽑아낼 수 없기 때문이다.
5. WHERE절 뒤에 자주 사용되는 컬럼
6. LIKE '%A'는 TABLE SCAN을 탄다.
7. NOT연산자는 긍정문으로 바꿔서 쓴다.
8. 삽입과 삭제가 빈번한 컬럼은 인덱스로 좋지않다. (인덱스를 만드는데 사용되는 공간과, 정렬하는데 걸리는 시간등이 추가적으로 필요하다.)

인덱스의 생성, 삭제, 조회

1. 생성

CREATE CLUSTERED/NONCLUSTERED INDEX 인덱스명 ON 테이블명 (
     정렬컬럼명 정렬기준(오름차순/내림차순)
)
// CREATE INDEX TestIndex ON TestTable(names)
// Clust여부는 생략가능

2. 삭제

DROP INDEX 테이블명.인덱스명

3. 조회

SP_HELPINDEX '테이블명'

//-------------------------------//

SELECT
      i.name AS 인덱스_이름,
      i.type_desc AS 인덱스_종류,
      c.name AS 컬럼_이름,
      iif(ic.key_ordinal = 1, 'O','X') AS 인덱스컬럼,
      iif(ic.is_descending_key = 0, 'desc', 'asc') AS 정렬
	FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    	ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c 
		ON ic.object_id = c.object_id AND ic.column_id = c.column_id
	WHERE i.object_id = OBJECT_ID('테이블명')
          and i.name = '인덱스명'

고려사항

1. 인덱스는 SELECT에서 최고의 성능을 나타냅니다. 하지만, 위에 말한 것과 같이입력, 수정, 삭제를 하게 되면인덱스를 새로 갱신하여야 하기 때문에 성능이 저하됩니다. 또한, 컬럼도 중요합니다.조건절(WHERE)에서 많이 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다. 또한, 동일한 데이터가 적은 컬럼혹은 JOIN을 할 때 사용되는 컬럼을 INDEX로 선언하는 것이 좋습니다.
2. 인덱스 컬럼에서는 NULL값을 사용하지 않습니다.
3. 조건절(WHERE)에서인덱스가 걸린 컬럼을 검색할 때 같은 형식으로 검색을 합니다. 즉, 인덱스가 INT 타입의 컬럼이면 NUM = 1000, 인덱스가 VARCHAR 컬럼이면 NUM = '100'이런 식으로 검색을 해야 합니다.
4. 복합 컬럼을 인덱스로 설정할 때는 자주 사용되는 컬럼 순으로 인덱스를 생성하는 것이 좋습니다.

주의사항

인덱스가 분명 있는 컬럼이지만 인덱스를 타지 않는 경우가 있습니다.

1. LIKE 검색 시 LIKE 검색을 할 때 '%문자%' 이렇게 많이 검색을 합니다. 이를 '문자%' 로 검색을 하는 것이 좋습니다.
2. NULL 구분으로 인덱스 컬럼을 검색하면 인덱스를 타지 않습니다. 즉, IS NULL 혹은 IS NOT NULL 조건으로 검색을 하면 인덱스가 있어도 인덱스를 타지 않습니다
3. 부정연산자는 종류가 여러개 있습니다. 대표적인 예로 !=, <> NOT IN, NOT EXISTS 등이 있습니다. 이런 경우에도 인덱스를 타지 않습니다.
4. 인덱스가 있는 컬럼에 형 변환 혹은 값 변환이 있으면 인덱스를 타지 않습니다. 즉, NUM +1 = 100 ISNULL(NUM, 1) = 100 이런 것처럼 조건을 주면 인덱스를 타지 않습니다.
5. OR구문인덱스가 걸린 컬럼과 OR구문으로 연동된 다른 컬럼으로 조회할 경우 테이블 전체 스캔을 하며 인덱스를 타지 않습니다.
6. 왼쪽절 WHERE로 조건을 걸었을 때 왼쪽의 조건에만 인덱스를 탑니다. 즉, AA.NAMES = BB.NAMES 이런 조건으로 검색을 한다면 AA테이블의 NAMES에 INDEX가 설정되어 있으면 인덱스를 타지만 AA테이블에는 인덱스가 걸려있지 않고, BB테이블에만 인덱스가 걸려있으면 인덱스를 타지 않습니다. 즉, 검색 시 왼쪽에 인덱스가 걸린 컬럼을 적어 주어야 한다.

결합인덱스

인덱스를 결합하는 것

create nonclustered index [index명] on [테이블명] (index컬럼) include (추가index)

이와 같이 결합인덱스를 사용하게 되면 쿼리성능의 개선효과가 나타나지만, 인덱스가 많아질수록 INSERT, UPDATE, DELETE 등 DML 작업에 의한 부하가 증가하게 된다.

Leaf노드에 컬럼을 추가하는 행위이다.
포괄열은 정렬을 하지 않는다.