설계 고려 사항

데이터베이스 시스템에 적절한 인덱스 세트를 파악하는 것은 복잡한 작업일 수 있습니다. 일반 인덱스를 설계할 때도 수 많은 가능성을 고려해야 하지만 스키마에 인덱싱된 뷰를 추가하면 설계가 훨씬 복잡해지며 가능한 결과의 수도 증가합니다. 예를 들어 인덱싱된 뷰는 다음에서 사용 될 수 있습니다.

• 쿼리에서 참조된 테이블의 모든 하위 집합
• 해당되는 테이블 하위 집합에 대한 쿼리 조건의 모든 하위 집합
• 그룹화 열
• 집계 함수(예: SUM)

각 구조에서 최상의 결과를 도출하기 위해서는 테이블의 인덱스와 인덱싱된 뷰를 동시에 설계해야 합니다. 인덱스와 인덱싱된 뷰 모두 주어진 쿼리에 유용할 수 있기 때문에 이들을 각각 따로 설계하면 중복된 권장 사항으로 높은 스토리지 및 유지 관리 오버헤드를 야기할 수 있습니다. 데이터베이스의 물리적 설계를 조정하는 동안 다양한 쿼리 세트의 성능 요구 사항과 데이터베이스 시스템이 지원해야 하는 업데이트 간에 상충이 발생합니다. 따라서 인덱싱된 뷰에 대한 적절한 물리적 설계를 파악하는 것이 무엇보다 중요한 과제이기 때문에 가능한 Database Tuning Advisor를 사용하도록 합니다.

쿼리 최적화 프로그램이 특정 쿼리에 대한 많은 인덱싱된 뷰를 고려할 수 있기 때문에 쿼리 최적화 비용이 크게 상승할 수 있습니다. 쿼리 최적화 프로그램은 쿼리의 모든 테이블 하위 집합에 정의되어 있는 모든 인덱싱된 뷰를 고려할 수 있습니다. 거부되기 전에 각 뷰의 대체 가능성에 대해 조사해야 합니다. 주어진 쿼리에 대한 수백 개의 뷰가 존재할 경우 같은 시간이 소요될 수 있습니다.

뷰에 클러스터된 고유 인덱스를 생성하기 위해서는 여러 요구 사항을 충족해야 하며 설계 단계에서 다음과 같은 요구 사항을 고려해야 합니다.

• 뷰와 뷰에서 참조된 모든 테이블은 동일한 데이터베이스 내에 있어야 하며 소유자가 같아야 합니다.
• 인덱싱된 뷰는 최적화 프로그램이 사용하게 될 쿼리에서 참조된 모든 테이블을 포함할 필요가 없습니다.
• 뷰에서 기타 다른 인덱스를 만드려면 먼저 클러스터된 고유 인덱스를 만들어야 합니다.
• 기본 테이블, 뷰 및 인덱스가 생성될 때, 그리고 기본 테이블의 데이터와 뷰가 수정될 때마다 특정 SET 옵션(뒤에서 논의)을 올바르게 설정해야 합니다. 또한 이들 SET 옵션이 정확하지 않은 경우에는 쿼리 최적화 프로그램이 인덱싱된 뷰를 고려하지 않을 것입니다.
• 스키마 바인딩을 사용해서 뷰를 생성해야 하며 SCHEMABINDING 옵션을 이용하여 뷰에서 참조되는 모든 사용자 정의 함수를 생성해야 합니다.
• 인덱싱된 뷰에서 정의된 데이터를 보관하기 위한 추가 디스크 공간이 필요할 것입니다.

설계 지침

인덱싱된 뷰를 설계할 때 다음과 같은 지침을 고려하십시오.

• 여러 쿼리 또는 여러 작업에서 사용할 수 있는 인덱싱된 뷰를 설계하십시오.
• 예를 들어, 열에 대한 SUM과 COUNT_BIG을 포함하고 있는 인덱싱된 뷰는 SUM, COUNT, COUNT_BIG, AVG 함수를 포함하고 있는 쿼리에 의해 사용될 수 있습니다. 기본 테이블에서 전체 행을 검색하지 않고 뷰에서 소수의 행만을 검색할 수 있으며, AVG 함수를 실행하는 데 필요한 일부 계산이 이미 완료되었기 때문에 훨씬 신속하게 쿼리가 수행됩니다.
• 인덱스 키의 크기를 작게 유지하십시오.
• 인덱스 키에서 최소한의 열과 바이트를 사용하면 인덱싱된 뷰 행을 좁혀 인덱싱된 뷰의 행에 보다 효율적으로 액세스할 수 있으며 보다 광범위한 키를 이용할 때보다 키 비교를 신속하게 수행할 수 있습니다. 뿐만 아니라 클러스터된 인덱스 키는 인덱싱된 뷰에 정의된 모든 클러스터되지 않은 인덱스에서 행 로케이터로 사용됩니다. 인덱스 키가 커지면 뷰의 클러스터되지 않은 인덱스 수에 비례해 비용이 상승합니다.
• 만들어진 인덱싱된 뷰의 크기를 고려하십시오.
• 순수 집계의 경우 인덱싱된 뷰의 크기가 원본 테이블의 크기와 비슷하면 큰 폭의 성능 향상이 이루어지지 않을 수도 있습니다.
• 프로세스의 일부를 가속화하는 여러 개의 소형 인덱싱된 뷰를 설계하십시오.
• 전체 쿼리를 지원하는 인덱싱된 뷰를 설계할 수 없을 수도 있습니다. 이 경우 각 쿼리의 일부를 수행하는 인덱싱된 뷰를 여러 개 생성하는 방 법을 고려해 보십시오.

다음 예제를 검토해 보십시오.

• 자주 실행되는 쿼리는 한 데이터베이스에서 데이터를 집계하고 다른 데이터베이스에서 데이터를 집계한 다음, 그 결과를 조인합니다. 인덱싱된 뷰는 1개 이상의 데이터베이스에서 테이블을 참조할 수 없기 때문에 전체 프로세스를 수행하는 단일 뷰를 설계할 수 없습니다. 하지만 각 데이터베이스에 인덱싱된 뷰를 만들어 해당 데이터베이스에 대한 집계를 수행할 수 있습니다.
• 최적화 프로그램이 기존 쿼리에 대해 인덱싱된 뷰를 일치시킬 수 있다면 기존 쿼리를 재코딩하지 않고도 최소한 집계 처리를 보다 신속하게 수행할 수 있습니다. 조인 처리 작업이 보다 신속하게 실행되지 않더라도 인덱싱된 뷰에 저장된 집계를 사용하기 때문에 전체 쿼리 속도가 빨라집니다.
• 자주 실행되는 쿼리는 여러 테이블에서 데이터를 집계하고 UNION을 사용해 그 결과를 종합합니다. 인덱싱된 뷰에서는 UNION이 지원되지 않습니다. 개별 집계 작업을 수행하도록 각각의 뷰를 설계할 수 있습니다. 따라서 최적화 프로그램은 인덱싱된 뷰를 선택하여 쿼리 재코딩 작업 없이 쿼리 속도를 높일 수 있습니다. UNION 처리는 향상되지 않지만 개별 집계 프로세스는 향상됩니다.

인덱싱된 뷰의 선택을 지원하는 도구

DTA3(Database Tuning Advisor)는 데이터베이스 관리자가 물리적 데이터베이스 설계를 조정할 수 있도록 돕는 SQL Server 2005 기능입니다. DTA는 기본 테이블의 인덱스와 테이블 및 인덱스 파티셔닝 전략은 물론, 인덱싱된 뷰를 권장합니다. DTA를 사용하면 데이터베이스에 대해 실행된 일반적인 쿼리들의 성능을 최적화하하는 인덱스, 인덱싱된 뷰 및 파티셔닝 전략 등을 결정하는 관리자의 능력을 한층 강화할 수 있습니다. DTA는 매우 다양한 인덱싱된 뷰를 권장할 수 있습니다. 여기에는“SQL Server 2005의 인덱싱된 뷰가 제공하는 새로운 기능”에서 설명된 SQL Server 2005의 인덱싱된 뷰를 위한 새로운 기능을 활용하는 뷰도 포함됩니다. DTA를 사용한다고 해서 데이터베이스 관리자가 물리적 스토리지 구조를 설계할 때 올바른 판단을 내리기 위해 노력할 필요가 없다는 것을 의미하는 것은 아니며 물리적 데이터베이스 설계 프로세스를 간소화할 수 있습니다. DTA는 가상의 인덱스, 인덱싱된 뷰, 파티션 구조를 제안하여 비용 기반의 쿼리 최적화 프로그램과 연동할 수 있으며 최적화 프로그램을 사용하여 파티션 구조를 채택한 경우와 그렇지 않은 경우의 워크로드 비용을 예측하고 전체 비용이 낮은 구조를 권장합니다. DTA는 필요한 모든 SET 옵션을 적용하기 때문에(올바른 결과 세트 보장을 위해) 인덱싱된 뷰를 성공적으로 작성할 수 있습니다. 그러나 옵션이 요구에 맞게 설정되지 않은 경우, 응용 프로그램이 뷰를 제대로 활용할 수 없습니다. SET 옵션이 제대로 설정되지 않으면 인덱싱된 뷰 정의에 포함된 테이블에서 삽입, 업데이트, 삭제 작title>데이터 업데이트가 인덱싱된 뷰에 미치는 영향

SQL Server는 기본 테이블 데이터의 변경 시 다른 인덱스와 유사한 방식으로 인덱싱된 뷰를 자동 유지 관리합니다. 일반 인덱스의 경우, 단일 테이블에 각각의 인덱스가 직접 연결됩니다. 원본으로 사용하는 테이블에서 각각의 INSERT, UPDATE 또는 DELETE 작업이 수행되고 이에 따라 인덱스가 업데이트되기 때문에 인덱스에 저장된 값을 테이블과 항상 일치시킬 수 있습니다.

인덱싱된 뷰도 이와 유사하게 유지 관리되지만, 뷰가 여러 테이블을 참조할 경우 테이블 업데이트를 위해서는 인덱싱된 뷰가 필요할 수 있습니다. 일반 인덱스와 달리 인덱싱된 뷰에서는 참가 테이블에 단일 행을 삽입하면 여러 행이 변경될 수 있습니다. 단일 행이 다른 테이블의 여러 행을 조인할 수 있기 때문입니다. 업데이트나 삭제 작업에서도 마찬가지입니다. 결론적으로 인덱싱된 뷰의 유지 관리는 테이블 인덱스 유지 관리 보다 훨씬 많은 비용이 듭니다. 반대로 뷰가 참조하는 기본 테이블에 대한 삽입, 삭제 및 업데이트 작업은 대부분 뷰에 영향을 미치지 않기 때문에 고도의 선택 상황에서는 인덱싱된 뷰의 유지 관리 비용이 테이블 인덱스 유지 관리 비용 보다 훨씬 적습니다. 다른 데이터베이스 데이터를 액세스하지 않고도 인덱싱된 뷰와 관련해 이러한 작업을 제거할 수 있습니다.

SQL Server에서는 일부 뷰를 업데이트할 수 있습니다. 뷰 업데이트가 가능하면 INSERT, UPDATE 및 DELETE 명령문을 사용하는 뷰를 통해 원본으로 사용하는 기본 테이블이 직접 수정할 수 있습니다. 뷰에서 인덱스를 생성해도 뷰 업데이트를 막을 수는 없습니다. 인덱싱된 뷰를 업데이트하면 실제로 뷰의 원본으로 사용하는 기본 테이블이 업데이트됩니다. 이러한 업데이트는 인덱싱된 뷰 유지 관리의 일환으로서 인덱싱된 뷰까지 자동으로 전달됩니다. 업데이트 가능 뷰에 대한 자세한 내용은 SQL Server 2005를 위한 SQL Server Books Online의“뷰를 통한 데이터 수정”을 참조하십시오.

유지 관리 비용 고려 사항

인덱싱된 뷰를 설계할 때 다음과 같은 사항을 고려해야 합니다.

• 인덱싱된 뷰의 경우 데이터베이스에 추가 스토리지가 필요합니다. 일반적인 테이블 스토리지와 마찬가지로 인덱싱된 뷰의 결과 세트는 물리적으로 데이터베이스에 저장됩니다.
• SQL Server는 뷰를 자동 유지 관리하기 때문에 뷰가 정의되어 있는 기본 테이블이 변경되면 뷰 인덱스에서도 1개 이상이 변경 작업이 수행됩니다. 따라서, 유지 관리 오버헤드가 추가로 발생됩니다.

뷰가 제공하는 총 쿼리 실행 절감 효과 및 뷰 저장/유지 관리 비용에서의 차이가 뷰를 통해 달성할 수 있는 순수 성능 개선이라 할 수 있습니다. 뷰에서 필요한 스토리지를 손쉽게 예측할 수 있습니다. SQL Server Management Studio 도구인 Display Estimated Execution Plan을 통해 뷰 정의를 통해 캡슐화된 SELECT 명령문을 평가하십시오. 이 도구를 통해 쿼리가 반환하는 행의 수와 행 크기를 예측할 수 있습니다. 이 두 값을 곱하면 뷰의 크기를 예측할 수 있습니다. 하지만, 이것은 어디까지나 근사치입니다. 뷰 정의에서 쿼리를 실행하거나 뷰에서 인덱스를 생성해야만 뷰의 실제 인덱스 크기를 정확하게 알 수 있습니다.

SQL Server가 수행하는 자동화된 유tion Plan 기능은 이러한 오버헤드의 영향을 파악할 수 있도록 지원합니다. SQL Server Management Studio를 통해 뷰를 수정한 명령문(뷰에서의 UPDATE, 기본 테이블로의 INSERT)을 평가하면 해당 명령문에 대해 표시된 실행 계획에 유지 관리 작업이 포함됩니다. 프로덕션 환경에서 이 작업이 수행되는 회수와 비용을 고려하면 뷰 유지 관리 비용을 예측할 수 있습니다.

일반 권장사항으로 뷰나 원본으로 사용하는 기본 테이블에 대한 모든 수정 또는 업데이트는 가능한 단독 방식이 아닌 배치 방식으로 수행되어야 합니다. 따라서, 뷰 유지 관리에 있어 오버헤드를 줄일 수 있습니다.