728x90
인덱스 최적화 팁
저자: Alexander Chigrik
일반적인 개념
본 글에서는 적절한 인덱스를 사용하여 쿼리의 속도를 향상시키는 방법과 SQL Server가 지원하는 인덱스의 종류 및 특정한 상황에서 인덱스를 사용할 때의 장점과 단점 등에 대해서 얘기하고자 한다.
인덱스는 크게 나누어서 클러스터드 인덱스(Clustered Index)와 넌클러스터드 인덱스(Nonclustered Index)가 있다. 클러스터드 인덱스는 특별한 형태의 인덱스로 테이블에 저장되어 있는 레코드의 물리적인 순서를 재구성한 것이다. 따라서 하나의 테이블은 단 하나의 클러스터드 인덱스만을 가질 수 있다. 클러스터드 인덱스의 최종 노드(Leaf Node)는 데이터 페이지를 가지게 된다.
넌클러스터드 인덱스는 인덱스의 논리적인 순서가 디스크상에 존재하는 레코드의 물리적인 저장 순서와 일치하지 않는 인덱스이다. 넌클러스터드 인덱스의 최종 노드는 데이터 페이지 대신 인덱스 열을 가지고 있다.
클러스터드 인덱스나 넌클러스터드 인덱스를 만들 때에 fill factor 옵션을 사용하면 SQL Server가 각각의 인덱스 페이지를 최대 얼마만큼 사용할 것인지를 지정할 수 있다. 인덱스 페이지에 새로운 열을 추가할 만한 공간이 남아있지 않으면 SQL Server는 새로운 인덱스 페이지를 생성하여 기존의 페이지에 있는 열의 일부를 새로운 페이지로 옮기게 되는데 이 과정을 일컬어 페이지 스플릿(Page Split)이라고 한다. 인덱스의 각 페이지에 빈 공간을 확보하기 위해서 fill factor를 적절히 설정함으로써 페이지 스플릿이 발생하는 회수를 줄일 수 있다. fill factor는 1에서100 사이의 값을 가지며 빈 공간으로 남겨두어야 할 인덱스 페이지의 비율을 나타낸다.
fill factor의 기본값은 0이다. fill factor 0은 fill factor 100과 거의 동일한 의미를 갖는데 한가지 차이라고 한다면 fill factor 0의 경우에는 인덱스 트리의 상위 레벨에 어느 정도의 공간을 남겨둔다는 점이 다르다.
커버링 인덱스(Convering Index)는 쿼리에서 참조하는 컬럼을 모두 포함하는 인덱스를 말한다. 따라서 인덱스에 찾고자 하는 데이터가 모두 포함되어 있기 때문에 SQL Server는 굳이 테이블을 참조할 필요가 없다.
데이터 갱신 작업을 많이 한다면(삽입, 갱신, 삭제) 테이블의 단편화가 심하게 발생하게 된다. 단편화의 정도를 측정하고 싶다면 DBCC SHOWCONTIG 명령을 사용하면 된다.
최적화 팁
모든 인덱스는 삽입, 갱신, 삭제 명령이 수행되는 시간을 증가시키기 때문에 인덱스의 개수가 너무 많아서는 안 된다. 하나의 테이블에 최대 4~5개 이내의 인덱스를 사용하도록 한다. 만약 읽기 전용의 테이블을 사용하는 경우라면 인덱스의 숫자가 많아도 상관없다.
복합 인덱스가 포함하는 컬럼 개수는 가능한 적게 유지해야 한다. 이렇게 하면 인덱스의 크기를 줄일 수 있기 때문에 인덱스를 읽는 데 필요한 읽기 작업의 회수가 감소하게 된다.
문자열 컬럼보다는 숫자 컬럼에 인덱스를 생성하는 편이 좋다.
복합 인덱스(멀티 컬럼 인덱스)를 만드는 경우에는 인덱스 키의 순서가 매우 중요하다. 가장 선택도가 높은 컬럼 일수록 키의 왼쪽 편에 위치하게 하여 선택도를 향상시키는 방향으로 컬럼 순서를 구성해야 한다.
여러 개의 테이블을 조인하고 싶으면 정수 키를 대신 만들어서 이 컬럼에 인덱스를 생성하여 사용하도록 한다.
테이블에 삽입 작업이 많지 않다면 정수 타입의 프라이머리 키를 만들어서 대신 사용하도록 한다.
일정한 범위의 값을 조회하거나 GROUP BY, ORDER BY를 이용하여 결과를 정렬할 필요가 있다면 넌클러스터드 인덱스보다는 클러스터드 인덱스를 사용하는 편이 좋다.
동일한 테이블에 대해서 같은 쿼리를 반복적으로 실행해야 하는 경우에는 커버링 인덱스를 만드는 것을 고려해 보기 바란다.
데이터베이스 내의 어떤 테이블이 인덱스를 필요로 하는지 확인하기 위해서는 SQL Server 프로파일러의 트레이스 마법사(Trace Wizard)를 통해서 “Identify Scans of Large Tables” 트레이스를 이용 하면 된다. 이 트레이스는 어떤 테이블이 인덱스를 사용하지 않고 조회 중인지를 보여 준다.
데이터베이스의 모든 인덱스를 재구성 하려면 아직 문서화되지는 않았지만 sp_Msforeachtable 프로시저를 사용하면 된다. 이 프로시저는 CPU를 사용하지 않거나 작업이 뜸한 시간에 실행되도록 스케줄링을 하도록 한다.
sp_Msforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”
저자: Alexander Chigrik
일반적인 개념
본 글에서는 적절한 인덱스를 사용하여 쿼리의 속도를 향상시키는 방법과 SQL Server가 지원하는 인덱스의 종류 및 특정한 상황에서 인덱스를 사용할 때의 장점과 단점 등에 대해서 얘기하고자 한다.
인덱스는 크게 나누어서 클러스터드 인덱스(Clustered Index)와 넌클러스터드 인덱스(Nonclustered Index)가 있다. 클러스터드 인덱스는 특별한 형태의 인덱스로 테이블에 저장되어 있는 레코드의 물리적인 순서를 재구성한 것이다. 따라서 하나의 테이블은 단 하나의 클러스터드 인덱스만을 가질 수 있다. 클러스터드 인덱스의 최종 노드(Leaf Node)는 데이터 페이지를 가지게 된다.
넌클러스터드 인덱스는 인덱스의 논리적인 순서가 디스크상에 존재하는 레코드의 물리적인 저장 순서와 일치하지 않는 인덱스이다. 넌클러스터드 인덱스의 최종 노드는 데이터 페이지 대신 인덱스 열을 가지고 있다.
클러스터드 인덱스나 넌클러스터드 인덱스를 만들 때에 fill factor 옵션을 사용하면 SQL Server가 각각의 인덱스 페이지를 최대 얼마만큼 사용할 것인지를 지정할 수 있다. 인덱스 페이지에 새로운 열을 추가할 만한 공간이 남아있지 않으면 SQL Server는 새로운 인덱스 페이지를 생성하여 기존의 페이지에 있는 열의 일부를 새로운 페이지로 옮기게 되는데 이 과정을 일컬어 페이지 스플릿(Page Split)이라고 한다. 인덱스의 각 페이지에 빈 공간을 확보하기 위해서 fill factor를 적절히 설정함으로써 페이지 스플릿이 발생하는 회수를 줄일 수 있다. fill factor는 1에서100 사이의 값을 가지며 빈 공간으로 남겨두어야 할 인덱스 페이지의 비율을 나타낸다.
fill factor의 기본값은 0이다. fill factor 0은 fill factor 100과 거의 동일한 의미를 갖는데 한가지 차이라고 한다면 fill factor 0의 경우에는 인덱스 트리의 상위 레벨에 어느 정도의 공간을 남겨둔다는 점이 다르다.
커버링 인덱스(Convering Index)는 쿼리에서 참조하는 컬럼을 모두 포함하는 인덱스를 말한다. 따라서 인덱스에 찾고자 하는 데이터가 모두 포함되어 있기 때문에 SQL Server는 굳이 테이블을 참조할 필요가 없다.
데이터 갱신 작업을 많이 한다면(삽입, 갱신, 삭제) 테이블의 단편화가 심하게 발생하게 된다. 단편화의 정도를 측정하고 싶다면 DBCC SHOWCONTIG 명령을 사용하면 된다.
최적화 팁
모든 인덱스는 삽입, 갱신, 삭제 명령이 수행되는 시간을 증가시키기 때문에 인덱스의 개수가 너무 많아서는 안 된다. 하나의 테이블에 최대 4~5개 이내의 인덱스를 사용하도록 한다. 만약 읽기 전용의 테이블을 사용하는 경우라면 인덱스의 숫자가 많아도 상관없다.
복합 인덱스가 포함하는 컬럼 개수는 가능한 적게 유지해야 한다. 이렇게 하면 인덱스의 크기를 줄일 수 있기 때문에 인덱스를 읽는 데 필요한 읽기 작업의 회수가 감소하게 된다.
문자열 컬럼보다는 숫자 컬럼에 인덱스를 생성하는 편이 좋다.
복합 인덱스(멀티 컬럼 인덱스)를 만드는 경우에는 인덱스 키의 순서가 매우 중요하다. 가장 선택도가 높은 컬럼 일수록 키의 왼쪽 편에 위치하게 하여 선택도를 향상시키는 방향으로 컬럼 순서를 구성해야 한다.
여러 개의 테이블을 조인하고 싶으면 정수 키를 대신 만들어서 이 컬럼에 인덱스를 생성하여 사용하도록 한다.
테이블에 삽입 작업이 많지 않다면 정수 타입의 프라이머리 키를 만들어서 대신 사용하도록 한다.
일정한 범위의 값을 조회하거나 GROUP BY, ORDER BY를 이용하여 결과를 정렬할 필요가 있다면 넌클러스터드 인덱스보다는 클러스터드 인덱스를 사용하는 편이 좋다.
동일한 테이블에 대해서 같은 쿼리를 반복적으로 실행해야 하는 경우에는 커버링 인덱스를 만드는 것을 고려해 보기 바란다.
데이터베이스 내의 어떤 테이블이 인덱스를 필요로 하는지 확인하기 위해서는 SQL Server 프로파일러의 트레이스 마법사(Trace Wizard)를 통해서 “Identify Scans of Large Tables” 트레이스를 이용 하면 된다. 이 트레이스는 어떤 테이블이 인덱스를 사용하지 않고 조회 중인지를 보여 준다.
데이터베이스의 모든 인덱스를 재구성 하려면 아직 문서화되지는 않았지만 sp_Msforeachtable 프로시저를 사용하면 된다. 이 프로시저는 CPU를 사용하지 않거나 작업이 뜸한 시간에 실행되도록 스케줄링을 하도록 한다.
sp_Msforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”
[출처] 인덱스 최적화 팁 http://www.howtobank.net|작성자 윈마스터
'데이터베이스 > SQL Server' 카테고리의 다른 글
| Where 절 CASE문 사용 (0) | 2008.04.29 |
|---|---|
| Order By 부분 정렬 구분인자에 따라 정렬 처리 Case 문 사용 (0) | 2008.04.29 |
| 올바른 순위 정렬 결과를 얻으려면 (0) | 2008.04.29 |
| SQL Server 데이터베이스의 트랜잭션 로그가 예기치 않게 커지는 것을 막는 방법 (0) | 2008.04.29 |
| SQL Server 2005 성능 문제 해결가이드 2 (0) | 2008.04.29 |
위로 가기