찐빵의 IT World

[MS-SQL]

Primary Key 복합 인덱스

Primary Key 는 NULL을 허용하지 않고, 중복값을 허용하지 않는다는 것은 잘 알고 있을 것이다.

그런데 Primary Key를 2~3개 컬럼으로 잡은 경우의 동작방식에 대해 알게 되어 적어본다.

Primary Key 컬럼을 3개로 잡은 경우

Col1 , Col2 , Col3 컬럼으로 잡고 3개다 int 형식의 컬럼이라고 하면

1 , 2 , 3 의 데이터가 있다면

1 , 2 , 2 의 데이터는 허용하지만

1 , 2 , 3 의 데이터는 허용하지 않는다.

즉, 3개컬럼이 다 합쳐져서 유니크 해야 한다는 뜻이 된다.

-MSSQL

MSSQL은 NULL값도 Index를 잡는다. 그래서 만든 것이 필터된 인덱스 인데 문제가 많다.

-ORACLE

ORACLE은 NULL값은 Index를 잡지 않는다.

 OLTP에서 B-Tree 인덱스를 쓰는 이유

OLTP(Online Transaction Processing) 시스템에서 B-Tree 인덱스를 사용한다. B는 'Balaced'를 말하기도 하고 'Binary'를 말하기도 한다. 하지만 내가 본 책(새로쓴대용량데이터베이스솔루션Vol.1 이화식 지음) 에서는 Balaced에 더 중점을 두고 있다. 잎사귀에 이르는 깊이가 어떤 인덱스 로우에 대해서도 동일하기 때문이다.

테이블의 로우가 어떤 위치에 있든 동일한 처리방법과 속도로 접근할 수 있다는 것이 인덱스가 가지는 가장 큰 특징이다. Leaf수준으로 갈 수록 가지가 늘어나는 수는 제곱이 되기 때문에 아무리 규모가 커져도 가지 깊이가 약간 늘어나는 것만으로도 천문학적인 수의 Leaf수준을 관리할 수 있다는 뜻이다.

통계 - 선택도

통계 보기

DBCC SHOW_STATISTICS (테이블이름,인덱스이름) 

실행한 화면

2번째 표의 All Density : 전체 밀도

컬럼 전체의 밀도 즉 이것은  평균적인 밀도이며,  쿼리가 바인드변수 피킹이나, 파라메터 스니핑을 하지 못할 때 사용되는 밀도 입니다. 반면, 바인드변수 피킹이나 파라미터 스니핑이 가능한 상황이라면, histogram 을 이용하게 됩니다. 둘 중 어떤것을 이용 했으냐에 따라 내 쿼리의 선택도 판단은 달라 집니다. 데이터 선택도는 둘 중 무엇을 사용하고 내가 쿼리하는 데이터가 뭐냐에 따라 틀립니다.

밀도가 1에 가까워 지면 거의다 같은 데이터 이고, 밀도가 0에 가까워지면 전체 행에 같은 행이 거의 없다는 말입니다.

만약 밀도가 1이면, 모두 같은데이터이고, 밀도가 0.1 이고 전체행수가 10 이라면 같은 데이터가 (0.1 * 10) 1개 있다는 말입니다.

선택도가 0에 가까운 것이 밀도는 낮고 선택도는 좋다는 말입니다.

출처 및 더 자세한 정보 : http://www.sqler.com/558930

SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id=DB_ID()

WHERE 절을 빼면 모든 데이터베이스의 기록을 볼 수 있다.

SQL Server 데이터베이스 엔진에서는 기본 데이터에 삽입, 업데이트 또는 삭제 작업을 수행할 때마다 인덱스를 자동으로 유지 관리합니다. 이러한 수정이 거듭되면 시간이 흐름에 따라 인덱스의 정보가 조각화되어 데이터베이스 내에 흩어지게 될 수 있습니다. 조각화는 키 값을 기준으로 하는 인덱스의 논리적 페이지 순서가 데이터 파일 내의 물리적 순서와 일치하지 않을 때 나타납니다. 심하게 조각화된 인덱스는 쿼리 성능을 저하시키고 응용 프로그램의 응답을 늦출 수 있습니다.

인덱스를 다시 구성하거나 다시 작성하여 인덱스 조각화 문제를 해결할 수 있습니다.

INDEX REBUILD : 인덱스를 다시 작성하면 이 인덱스가 삭제된 다음 다시 생성됩니다. 이렇게 하면 조각화를 제거하고, 지정된 채우기 비율 또는 기존 채우기 비율 설정을 기준으로 페이지를 압축하여 디스크 공간을 회수하고, 인덱스 행을 연속된 페이지로 다시 정렬할 수 있습니다. ALL을 지정하면 테이블의 모든 인덱스가 단일 트랜잭션으로 삭제되고 다시 작성됩니다.

INDEX REORGANIZE : 인덱스를 다시 구성할 때는 최소한의 시스템 리소스가 사용됩니다. 이때는 왼쪽에서 오른쪽으로 표시되는 리프 노드의 논리적 순서에 맞도록 리프 수준 페이지를 물리적으로 다시 정렬하여 테이블 및 뷰의 클러스터형 및 비클러스터형 인덱스의 리프 수준에 대한 조각 모음을 수행합니다. 다시 구성 작업을 수행하면 인덱스 페이지도 압축됩니다. 이때 압축은 기존 채우기 비율 값을 기준으로 수행됩니다.

DBCC INDEXDEFRAG 대신 ALTER INDEX REORGANIZE를 사용하여 인덱스의 리프 수준 페이지를 논리적 순서로 다시 정렬합니다. 이 작업은 온라인 작업이므로 문이 실행 중일 때 인덱스를 사용할 수 있습니다. 또한 작업이 중단되더라도 이미 완료된 작업은 손실되지 않습니다. 이 방법의 단점은 데이터를 다시 구성하는 작업이 인덱스를 다시 작성하는 작업만큼 효과적이지 않다는 것과 통계가 업데이트되지 않는다는 것입니다.

인덱스 조각화 검색하기, 그 후에 비율에 따라서 맞는 작업을 수행

힙의 조각화 줄이기

힙의 익스텐트 조각화를 줄이려면 테이블에 클러스터형 인덱스를 만든 다음 해당 인덱스를 삭제

이렇게 하면 클러스터형 인덱스를 만드는 동안 데이터가 다시 구성되고 데이터베이스에서 사용할 수 있는 공간의 분포를 고려하여 최적화된다. 그런 다음 클러스터형 인덱스를 삭제하여 힙으로 다시 만들면 데이터가 이동하지 않고 최적의 분포를 유지

출처 : http://msdn.microsoft.com/ko-kr/library/ms189858(v=sql.120).aspx

 INDEX SCAN 과 INDEX SEEK

 SQL Server의 인덱스가 B(anlanced) Tree 구조로 처리

 Index Seek는 B-Tree 구조상의 Root 페이지부터 Leaf Level까지 검색 경로를 따라 수행되는 방법. Leaf Level을 제외한 상위 각 레벨에서 1페이지씩을 검색하게 된다.

 Index Scan은 Leaf Level의 첫번째 페이지부터 데이터 검색을 수행하는 방법. 일반적으로 얘기하는 Table Scan과 동일한 방법이지만, 그 대상이 Leaf Level의 인덱스 페이지라는 것이지요.
 
 그러나, Clustered Index의 경우 Leaf Level이 곧 Data Page이기 때문에, Index Scan이란 용어 대신 Clustered Scan이라는 용어로 표현이 됩니다.

 ONLINE = ON | OFF

인덱스 작업 중에 쿼리 및 데이터 수정에 기본 테이블과 관련 인덱스를 사용할 수 있는지 여부를 지정합니다. 기본값은 OFF입니다.

ON - 장기 테이블 잠금이 유지되지 않습니다. 따라서 기본 테이블에 대한 쿼리나 업데이트를 계속할 수 있습니다.

OFF - 테이블 잠금이 적용되어 인덱스 작업 중에 테이블을 사용할 수 없습니다.

ONLINE 옵션은 클러스터형 인덱스를 삭제할 때만 지정할 수 있습니다. 자세한 내용은 주의 섹션을 참조하십시오.

출처 : http://technet.microsoft.com/ko-kr/library/ms176118.aspx

 통계

- 검색 조건이 주어질 때 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 옵티마이저가 판단의 근거로 활용

- 통계는 데이터가 있는 컬럼에 대해 인덱스가 만들어질 때 이 컬럼 값의 히스토그램 및 관련정보를 이용하여 만들어진다.

Sysindex 테이블의 image형식의 statblog컬럼에 통계정보가 저장된다.

데이터베이스 옵션의 auto create statistics와 auto update statistics는 모든 데이터베이스에 대해 자동으로ture로 설정되며 이 옵션들이 설정되어 있으면 인덱스뿐만 아니라 인덱스가 없는 컬럼에 대해서도 필요할 경우 자동으로 통계가 만들어지고 일정 비율(전체행의 약20%)의 데이터가 업데이트 되면통계도 자동으로 업데이트 된다. 

밀도(density)는 1/(인덱스키들의 수)로 정의된다. 즉, 성별은 밀도가 0.5(남자, 여자)이지만 나의 주민등록 번호는 1/5000만 이 된다.(대한민국 인구수를 5천만으로 가정 했을 때).

이때 선택도(selectivity)는 주어진 키 값(또는 키 범위)에 해당하는 행수의 정도를 말하는데 행수가 적을수록 선택도가 높다. 그러나 전체 밀도가낮더라도 특정 키에 대한 선택도는 낮을 수도 있다.

[통계 보기]

DBCC SHOW_STATISTICS문을 사용하여 통계를 확인 할수 있다. 실습에서는 AdventureWorks의 [Person.Address]테이블을 사용 한다.

DBCC SHOW_STATISTICS([PERSON.ADDRESS], PK_ADDRESS_ADDRESSID)

Updated : 최근 통계가 업데이트된 날짜.

Rows : 전체 행 수

Rows Sampled : 샘플링된 행 수 (행 수가 많을 경우 일부를 샘플링하여 사용)

Steps : 히스토그램의 단계 수.고유 인덱스가 아닐 경우 단계수가 많아지며 최대 200단계 이다.

Average Key length : 컬럼 값의 평균 길이. Int형이므로 4가 표시 된다.

All density : 밀도. 밀도는 Steps의 값과 무관하며 일반적으로 (1/ 인덱스 키들의 수) 이다.

RANGE_HI_KEY : 각 단계(step)에서샘플링된 최대 값.

RANGE_ROWS : 각 단계에서RANGE_HI_KEY갑을 제외한 값들의 개수.

EQ_ROWS : 최대값의 개수.

DISTINCT_RANGE_ROWS : RANGE_ROWS 컬럼 값들중 고유한 값의 개수.

수동으로 통계 업데이트

UPDATE STATISTICS 테이블이름 

인덱스 정보 확인하기 

EXEC SP_HELPINDEX [PERSON.ADDRESS]

출처 : http://sqlmvp.kr/140165557766

1. 인덱스 (INDEX)

인덱스의 장점 : 검색 속도가 빠르다.

인덱스의 단점

데이터에 대한 추가/수정/삭제에 대한 연산 속도는 약간 느려진다.

데이터와 별도의 장소에 저장되어서 공간이 조금 더 필요하다. (데이터 저장 크기의 5~20%)

관리자의 수고

인덱스를 이용하게 되는 쿼리

- WHERE 절에서 인덱스 된 컬럼으로 검색할 때

- 조인 질의(Join Query)시 인덱스 된 컬럼으로 검색할 때

- Order By 절에서 인덱스 된 컬럼으로 정렬할 때

- Group By 절에서 인덱스 된 컬럼으로 집합 연산할 때

인덱스를 설정해주면 좋은 컬럼의 예 : 자주 사용되는 컬럼(검색 조건이나 정렬 조건으로 자주 이용되는 컬럼)

- 프라이머리 키(Primary Key) 컬럼

- 참조 키(Foreign Key) 컬럼

- 조인 질의(Join Query)에서 사용되는 컬럼

- 정렬 순서로 자주 사용되는 컬럼

- 자주 범위로 검색되는 컬럼