MS sql에 인덱스 개념
인덱스 정의
쿼리 성능을 가장 획기적으로 높일 수 있는 방법은 데이터 IO를 줄이는 방법중에서 디스크 IO를 줄이는 것이다.
이러한 디스크 IO 와 논리적 읽기수를 줄이는 방법은 인덱스 활용이다.
인덱스를 쓰면 전체 테이블 스캔을 하지 않고 데이터를 찾을수 있다.
인덱스에 기본적은 개념은 책에 있는 인덱스와 비슷하다.
ㄱㄴㄷ 순으로 정렬 되어있고 책 이라는 키워드를 검색하면 ㅊ 인덱스 페이지로가서 그부분만 스캔후 찾아서 정보를 준다 그러면 책 전체를 스캔 할 필요 없다.
그러나 인덱스 페이지 정렬을 안한경우 키워드를 찾기 위해서 인덱스 페이지를 처음부터 페이지 번호를 찾아야하기 때문에 테이블 스캔하는것처럼
찾아야한다.
기본적으로는 필요 없지만 데이터 양이 많아지고 빠른 검색이 필요 할경우에 필요하다.
인덱스 구조
Rootlevel 하나만 존재 인덱스 검색시 여기 통해서 검색
Intermediate level 인덱스 크기에 따라서 있을수도 있고 없을수도있음
Leaf level 데이터들의 위치 정보 있는곳 인덱스 구조에서 가장 중요
인덱스 종류
대표적인 데이터 저장 구조와 데이터 엑세스 방법
힙 테이블, 클러스터형 인덱스, 비클러스터형 인덱스 3가지로 나뉜다.
SQL 서버는 인덱스를 B-Tree 구조로 관리하며 크게 클러스터형 인덱스와 비클러스터형 인덱스로 구분한다.
클러스터형 인덱스
클러스터형 인덱스는 테이블 자체가 클러스터형 인덱스 키를 기준으로 데이터를 B-Tree형태로 관리하며 Leaf Level이 데이터 페이지로 데이터 가 정렬해 저장된다.
이로 인해 클러스터형 인덱스는 테이블 당 한개만 생성할 수 있고 데이터 자체가 물리적으로 정렬돼 있기 때문에 디스크 포인터 점프 할필요 없고
대량 범위 처리시 (64kb 이상) 순차적으로 IO작업 처리 하기 때문에 대량 범위 데이터 검색할때 유리하다.
힙 테이블
반대로 클러스터형 인덱스가 없는 테이블을 힙 테이블이라 하며, 데이터가 들어오는 순서대로 쌓여있는 형태이다.
인덱스가 없으면 모든 데이터 페이지를 순차적으로 엑세스 하는 테이블 Scan이 발생하기 때문에 데이터가 많아지면 성능 저하가 발생한다.
비클러스터형 인덱스
비클러스터형 인덱스는 인덱스의 leaf node에 로케이터 값 = RID가 저장돼 있다.
이값이 데이터 페이지와의 연결고리가 된다.
테이블에 클러스터형 인덱스가 있으면 데이터 페이지를 찾아가기위한 행 로케이터로 클러스터형 인덱스의 키값을 사용 하지만
클러스터형 인덱스가 없으면 필드id, page id, slot id등을 포함한 바이너리 값의 RID를 사용한다.
비클러스터형 인덱스는 각 행에 대한 디스트 포인터 점프가 필요하며 비순차적 IO가 발생해서 선택도가 높은 소소의 데이터를 검색하는데 유리하고 각 테이블에 최대 999개까지 만들수 있다.
비 클러스터 형 인덱스 정렬안됨 => 데이터 변경 용이
클러스터 형 인덱스 정렬됨 => Select 속도 증가
데이터 엑세스 하는 종류
종류
Scan - Table Scan, Clustered Index Scan, NonClustered Index Scan
Seek - Clustered Index Seek, NonClustered Index Seek
Bookmark Lookup - KEY Lookup, RID Lookup
옵티 마이저는 이 연산들의 비용을 계산해서 가장 적은 연산으로 실행 계획을 생성한다.
Scan
Scan 연산은 데이터 찾을때까지 데이터 페이지 또는 인덱스 Leaf level을 처음부터 읽는 연산으로 디스크 IO 부하가 크다.
반해 Seek 연산은 한번에 원하는 위치로 데이터를 찾아가기 때문에 가장 디스크 IO 및 CPU 부하가 적다.
인덱스 설계할 때는 index seek를 목표로 가야한다.
Bookmark Lookup
Bookmark Lookup은 비클러스터형 인덱스를 참조해 조건에 맞는 row은 찾았지만 인덱스 키값이 아닌 데이터가 추가로 필요 할 경우 물리적 위치를 찾아가 데이터를 읽는 연산이다.
클러스터형 인덱스가 있는 테이블은 클러스터형 인텍스 키를 통해 찾아가므로 KEY Lookup
클러스터형 인덱스가 없는 힙 테이블은 RID 를 통해 데이터 페이지를 찾아가므로 RID Lookup이다
Bookmark Lookup의 경우 전체 테이블의 Scan하는 것에 비해 디스크 IO는 줄일수있지만 CUP부하가 많이 발생하면 데이터 하나 당 두배 page read가 발생한다.
클러스터 형 인덱스가 있는 테이블은 leaf level에 클러스터 인덱스 키값을 가지고있기 때문에 인덱스 컬럼 외 필요한 데이터가
클러스터형 인덱스 키값일경우에는 Lookup이 발생하지 않는다. 하지만 힙 테이블에서는 비 클러스터형 인덱스의 Leaf Level에 RID 값이 저장되기 때문에
비클러스터형 키에 포함되지 않는 컬럼 데이터에 대해서는 무조건 RID Lookup이 발생한다.
인덱스 오버헤드
이렇게 보면 인덱스가 장점만 있는거같은데 단점도 존재한다.
Select 쿼리에 인덱스가 있을 경우에 Index Seek 가 발생해서 빠르다 하지만 데이터 insert 결우 클러스터형 인덱스 보다 힙 테이블 또는 비클러스터형 인덱스가 없을때 데이터 IO도 더 적게 발생하고 응답시간도 빠르다.
특정 열에 대한 변경( DELETE, UPEATE )하는 경우에는 힙 테이블은 조건을 찾기위해서 테이블 전체를 Scan 해야해서 성능이 별로다.
또한 클러스터형 인덱스와 비 클러스터형 인덱스의 키 컬럼 데이터가 변경될 때마다 클러스터형 인덱스 재정렬 뿐만 아니라
Leaf Level에 클러스터형 인덱스 키 값을 갖고 있는 비클러스터형 인덱스의 재정렬도 발생한다.
인덱스 관리에는 추가적인 리소스가 사용되고 처리시간 증가해 블로킹 이슈로 이어질수있고
테이블에 인덱스를 많이 만들면 추가적인 인덱스 페이지로 인한 디스크와 메모리 공간이 추가로 필요하다.
인덱스 오버헤드 최소화 및 최적화된 인덱스를 생성
그리면 이런 인덱스 오버헤드를 초소화 시키는 방법을 생각해야한다.
컬럼 데이터 선택 고려
WHERE 이나 JOIN 절에 있는 컬럼 이다로 성별처럼 선택할수있는 범위가 적은 인덱스는 도움이 되지 않는다.
따라서 인덱스를 만들때 선택도가 높은 컬럼을 키 컬럼으로 선택 한다.
컬럼 데이터 타입을 고려
만약 인덱스 키가 INT, BIGINT, SMALLINT, TINYINT 정수타입 이라면 탐색 속도가 빠르다 int 타입 데이터는 사이즈가 작고 산술적 관리가 쉽기 때문이다.
반대로 string 타입인 char nchar, varchar, nvarchar들은 seting match 연산이 들어가서 integer match 연산보다 일반적으로 비용이 높다.
따라서 인덱스 컬럼으로 고려하는 키가 int 데이터 타입과 char(4)가 있다면 int 데이터 타입을 우선 선정한다.
인덱스 타입을 고려
클러스터형 인덱스는 최대한 키값이 작아야한다. 비클러스트형 Leaf Level에 키값이 들어가므로 키값이 클경우 모든 인덱스의 사이즈가 커진다.
클러스터형 인덱스는 가능한 UNIQUE 인덱스로 설정한다. 고유하지 않을 경우 물리적 데이터를 절렬해야 하므로 고유값으로 만들기위해 숨겨진 4byte 바이너리 코드가 추가된다.
키값 변경은 거이 일어나지 않아야한다.
키 값이 변경이 잦을 경우 클러스터형 인덱스, 비클러스터형인덱스에 대한 정렬로 인한 오버헤드가 커진다.
Primary key 가 반드시 클러스터형 인덱스의 키값일 필요는 없다.
하지만 없다. Primary Key를 주로 클러스터형 인덱스 키로 지정 하나
WHERE 절과 JOIN 절에 자주 참조되지 않고 쿼리상에 거의 등장하지 않는 Primary Key를 인덱스 키로 지정하는 것은 불필요하다.
클러스터형 인덱스 키는 비클러스터형 인덱스 커버링을 고려한다.
클러스터형 인덱스의 키가 비클러스터형 인덱스의 Leaf Level에 포함되므로 Key Lookup 없이 쿼리와 비클러스터형 인덱스 단에서 조회가 끝나도록 한다.
사용자 접속 로그등 순차적 데이터가 대량으로 Insert되는 Log 성격의 테이블에는 클러스터형 인덱스를 생성하지 않는다.
인덱스 재정렬과 트랜잭션 로그 기록등의 오버헤드 발생
page split이 계속 발생해 인덱스 성능에도 좋지 않기 때문이다.
이런테이블은 힙테이블로 두는게 좋다.
