|
1. 시작
일반적으로 하나의 테이블에는 하나의 클러스터 인덱스만 존재 할 수 있으며, 힙 영역에 클러스터 인덱스를 만들게 되면 해당 데이터 영역은 클러스터 영역으로 변하게 됩니다.
서비스에 따라 데이터 갱신에 대한 비용보다 많은 조회에 의한 비용이 많이 들 수 있으며 이럴 경우 두 개 이상의 클러스터 인덱스가 필요 할 수 있을 것입니다.
기존 SQL Server 2000에서는 인덱싱 된 뷰를 생성 하여 두 개 이상의 클러스터 인덱스가 있는 것처럼 활용이 가능 했으며 2005에서는 인덱싱 된 뷰 와 새롭게 추가된 포괄 열 인덱스로써 구현이 가능 합니다.
인덱싱 된 뷰, 포괄 열 인덱스에 대한 활용방안, 두 가지의 차이점 및 유사점에 대해서 시작 하도록 하겠습니다.
2. 인덱스란?
인덱스를 우리말로 번역을 하면 색인 이란 단어로 번역 될 수 있습니다. 색인을 국어사전에서 찾아보면 이와 같이 정의를 하고 있습니다. “책 속의 내용 중에서 중요한 단어나 항목, 인명 따위를 쉽게 찾아볼 수 있도록 일정한 순서에 따라 별도로 배열하여 놓은 목록”
SQL Server에서 불리는 색인 즉 인덱스도 위 단어처럼 책 속이 아닌 테이블에 있는 내용을 보다 쉽게 찾기 위해 따로 일정한 순서로 정리해둔 목록 이라고 볼 수 있습니다.
SQL Server또는 다른 DBMS의 경우에도 인덱스가 RDBMS에서 얼마나 중요한 것인지 누구나 알고 있을 것 입니다. 보다 빨리 데이터를 조회하고 갱신하기 위해서는 인덱스라는 것이 필요하고 인덱스가 존재 함으로써 DBMS의 성능의 차이도 발생 할 수 있으며, 잘 사용한다면 성능을 극대화 시킬 수 있지만 잘못 사용한다면 오히려 성능 문제를 일으킬 수 있습니다.
SQL Server는 이전부터 클러스터 인덱스와 넌 클러스터 인덱스 두 가지 종류의 인덱스를 제공 하고 있습니다. 두 가지의 가장 큰 차이점은 소스 데이터를 리프레벨에 포함하는 여부 입니다. 다른 일반적인 구조는 두 가지 모두 비슷합니다.
클러스터 인덱스는 소스 데이터를 포함 하고 있기에 기본적으로는 하나밖에 생성을 할 수 없으며, 넌 클러스터 인덱스는 리프 레벨에 소스 데이터가 아닌 키로 정의된 열에 대한 데이터 만을 리프레벨에 가지고 있기에 두 개 이상 생성이 가능 합니다. “그렇다면 소스데이터가 여러 개가 있다면 클러스터 인덱스도 여러 개 만들 수 있지 않을까요?” 이 물음에 대해서는 아래에서 보다 자세히 살펴보도록 하겠습니다.
아래는 BOL에 설명하는 클러스터 인덱스와 넌 클러스터 인덱스의 내부 구조를 보여 주고 있습니다.
두 개 모두 인덱스 이기에 모두 루트와 데이터 페이지가 존재 하며, 클러스터 인덱스의 데이터 페이지는 실질적인 데이터를 가르키며, 넌 클러스터 인덱스의 데이터 페이지(리프레벨)는 인덱스 페이지라고 부르는 것이 더욱 쉽게 이해할 수 있을 것입니다. 여기서는 이 두 개의 구조적 차이와 자세한 내용을 언급하는 것이 아니지만 보다 쉽게 이해하기 위해서 선수조건이 되면 좋을 것 같습니다. 이 부분에 대해 더 자세한 내용은 온라인 설명서 및 SQL관련 서적을 참조하시길 바랍니다.
[클러스터형 인덱스 구조]
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/26b28045-c3c2-465a-b564-bf2189e93fdc.htm
[비클러스터형 인덱스 구조]
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/1efeba1f-f848-4861-9af3-594e5ab3b597.htm 
3. 인덱싱 된 뷰, 포괄 열 인덱스 란
이제 위에서 말한 하나의 테이블에 두 개 이상의 클러스터 인덱스를 생성 할 수 있는 방법(?)에 대해서 살펴보도록 하겠습니다.
인덱싱 된 뷰는 SQL Server 2005이전부터 활용된 기능으로써 인덱싱 된 뷰를 추가적으로 생성하여 조회 쿼리에 대해서 많은 이점을 주었습니다.
기존 테이블에 스키마 바인딩 뷰를 생성 후 해당 뷰에 고유한 클러스터 인덱스를 추가 하게 되면 논리적인 뷰가 아닌 물리적으로 저장이 되는 인덱싱 된 뷰가 생성 되게 됩니다.
물론 하나의 테이블 만이 아닌 조인을 이용하여 여러 개의 테이블을 참조하는 뷰에 대해서도 인덱싱 된 뷰는 활용이 가능하기에 많은 조인, 정열 및 집계 등에 많은 비용이 든다면 충분히 고려해 볼 수 있는 기능 입니다. 그러나 데이터를 중복으로 가짐으로써 성능 상 문제가 될 수 도 있습니다. 그러기에 모든 환경에서의 최적의 솔루션이 아닌, 자기 환경에 정확한 이해를 바탕으로 도입 하는 것이 무엇 보다 중요합니다.
주로 데이터의 갱신작업 이 많이 일어나는 OLTP환경보다, OLAP환경과 같은 DW에 보다 유리하며, OLTP환경도 서비스 마다 틀리겠지만 일반적인 환경에서는 데이터 갱신 보다 데이터 조회의 비용이 더 많이 발생되는 것이 일반 적입니다.
SQL Server 2005에서 새롭게 추가된 넌 클러스터 인덱스에 INCLUDE라는 포괄 열 인덱스라는 것이 추가 되었습니다.
포괄 열 인덱스라는 INCLUDE절은 기존 넌클러스터 인덱스에 키와 인덱스 리프레벨에 포함되는 데이터를 따로 설정 할 수 있으며 인덱스 키만이 비리프레벨에 존재 하고, 나머지 INCLUDE절에 포함된 열에 대해서는 리프레벨에만 존재 하게 됩니다. 열 개수 및 데이터 형 제한에 대해서 기존 넌클러스터 인덱스보다 유연하여 인덱싱 된 뷰의 기능을 과 유사하게 사용 될 수 있습니다.
하지만, 만약 소스 테이블의 열이 16개 이하이고 LOB 데이터형도 없는 경우, 넌 클러스터 인덱스로 비슷한 기능을 구현은 가능 합니다. 그러나 비리프레벨에 모든 인덱스 키의 값이 존재 하기에 비리프레벨 페이지에 많은 하위 페이지에 대한 포인터 정보를 넣을 수 없어 인덱스 깊이가 증가 될 것 입니다. 이것으로 충분한 성능의 문제를 초래 할 수 도 있습니다.
포괄 열 인덱스가 포용할 수 있는 기존 넌클러스터 인덱스의 제한
- 인덱스의 열 개수의 제한
- 인덱스 크기(900바이트)의 제한
- 인덱스의 데이터 타입의 제한(LOB형)
- 저장 될 모든 열을 인덱스 키로 설정 함으로서 비 리프레벨에도 모든 열의 값이 존재함.
4. 인덱싱 된 뷰, 포괄 열 인덱스와의 차이점
위에서 알아보았듯이 인덱싱 된 뷰는 기존에도 많은 조회용 쿼리를 사용하는 환경에서 사용되었습니다.
정열 된 순서대로 많은 데이터를 가져와야 하고 클러스터 인덱스는 다른 열에 있어 클러스터 인덱스를 활용할 수 없고 covered Index로 처리도 어려운 경우에 인덱싱 된 뷰를 사용하여 보다 빠른 결과를 가져올 수 있었습니다. 또는 집계 데이터 빈번한 조인이 사용하는 쿼리 등등 사용 예를 볼 수 있습니다.
여기서 설명 하지 못한 내용도 충분히 많을 것 이며, 복합 넌 클러스터 인덱스로 불가능한 부분에 대해서 사용하였습니다. 그러나 SQL Server 2005에서는 포괄 열 인덱스를 지원함으로써 단일 테이블, 집계가 없을 경우에는 포괄 열 인덱스를 사용이 가능하기에 보다 쉽게 구현이 가능 하며 관리상의 이슈도 적어집니다. 아래는 간단히 인덱싱 된 뷰와 포괄 열 인덱스에 대한 차이를 정리하였습니다.
[표 1] 포괄 열 인덱스와 인덱싱 된 뷰 비교
|
|
인덱싱 된 뷰 |
포괄 열 인덱스 |
|
인덱스 키가 아닌 데이터가 비리프레벨에 존재 여부 |
아니오 |
아니오 |
|
LOB형 지원(TEXT등 제외) 여부 |
예 |
예 |
|
열 개수 제한 |
아니오 |
아니오(1023 개) |
|
집계 값 포함 여부 |
예 |
아니오 |
|
크기 제한 |
아니오 |
아니오 |
|
데이터 변경 시 worktable사용 여부 |
예 |
아니오 |
|
소스 테이블에 대한 키 존재 여부 |
아니오 |
예(RID 또는 클러스터키값) |
인덱싱 된 뷰는 기존의 넌 클러스터 인덱스 또는 포괄 열 인덱스와는 다르게 원본 테이블에 대해 연결된 키가 존재 하지 않습니다. 스키마 바인딩 뷰 생성 후 만들게 되는 고유한 클러스터 인덱스 키를 이용하여 소스 테이블의 변경된 내역을 반영하게 됩니다. 이러한 소스테이블에 종속된 키가 존재 하지 않아 데이터 갱신 작업 시 wokrtable을 이용하여 실 테이블을 갱신 작업에 대해 저장 후 이 내용을 바탕으로 인덱싱 된 뷰에서 처리하게 됩니다.
아래는 인덱싱 된 뷰가 존재하는 테이블에 대해서 특정 값을 업데이트 쿼리의 실행 계획이며, Table Spool연산자가 추가 및 출력목록에 인덱싱 된 뷰의 클러스터 키와 변경된 컬럼을 확인 할 수 있습니다. 
5. 인덱싱 된 뷰 생성시 필요 조건 및 주의 사항.
인덱싱 된 뷰를 만들기 위해서는 몇 가지 주의 사항이 있습니다. 주의 사항 및 지켜줘야 하는 부분에 대해서 잠시 살펴 보도록 하겠습니다.
주의 사항
- CREATE VIEW 문이 실행될 때는 ANSI_NULLS 및 QUOTED_IDENTIFIER 옵션이 ON
- 뷰를 생성시 참조 되는 원본 테이블은 생성시 ANSI_NULLS옵션이 ON
- 테이블이 아닌 다른 뷰를 참조해서는 안됨.
- 뷰 생성시 SCHEMABINDING옵션으로 생성
- 테이블 및 사용자 함수에 대해서는 스키마명.객체명 사용.
- 뷰의 식에서 참조되는 모든 함수는 결정적 함수.
- GROUP BY를 사용하는 경우 SELECT List절 에 COUNT가 아닌 COUNT_BIG을 사용(테스트1 참조)
- Select List절에 * 같이 사용할 수 없고 컬럼 명을 명시적으로 사용
보다 자세한 내용은 BOL을 참조(인덱싱 된 뷰 만들기)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/f86dd29f-52dd-44a9-91ac-1eb305c1ca8d.htm
뷰 생성 후 해당 옵션으로 생성된 것인지 확인 하기 위해서 OBJECTPROPERTY함수를 사용하여 확인
|
SELECT
OBJECTPROPERTY ( OBJECT_ID ( 'Sales.vOrders' , 'view' ) , 'ExecIsAnsiNullsOn' )
,OBJECTPROPERTY ( OBJECT_ID ( 'Sales.vOrders' , 'view' ) , 'ExecIsQuotedIdentOn' )
1 = True
0 = False |
[테스트 1 - GROUP BY절을 사용하고 COUNT_BIG함수를 사용하지 않을 경우 오류 메시지]
|
SET NUMERIC_ROUNDABORT OFF ;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT ,
QUOTED_IDENTIFIER, ANSI_NULLS ON ;
GO
--스키마 바인딩 뷰 생성
IF OBJECT_ID ('Sales.vOrders', 'view' ) IS NOT NULL
DROP VIEW Sales .vOrders ;
GO
CREATE VIEW Sales .vOrders
WITH SCHEMABINDING
AS
SELECT SUM (UnitPrice *OrderQty *(1.00 -UnitPriceDiscount )) AS Revenue ,
OrderDate , ProductID , COUNT(*) AS COUNT
FROM Sales .SalesOrderDetail AS od ,Sales .SalesOrderHeader AS o
WHERE od .SalesOrderID = o .SalesOrderID
GROUP BY OrderDate , ProductID ;
GO
--뷰에 고유한 클러스터 인덱스 생성
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales .vOrders (OrderDate , ProductID );
GO
' 메시지 10136, 수준16, 상태1, 줄1
뷰 "AdventureWorks.Sales.vOrders"이 (가) 집계 COUNT 를사용하므로이뷰에인덱스을 (를) 만들수없습니다.
대신 COUNT_BIG 을사용하십시오 .' |
6. 하나의 테이블에 클러스터 와 힙 두 가지 모두 만들 수 없을까?
그렇다면 두 가지를 모두 가져 서비스에 좋은 영향을 줄 수 있을까?
아시다시피 SQL Server에는 두 가지 데이터 영역이 존재 합니다. 클러스터 인덱스가 있는 테이블은 클러스터 데이터 영역이라고 부르겠으며, 클러스터 인덱스가 존재 하지 않는 테이블은 힙 데이터 영역이라고 부르겠습니다.
클러스터 데이터 영역은 클러스터 키로 논리적으로 정열이 된 데이터를 말하며 힙 영역은 특정 키로 정열 되지 않은 영역으로 말할 수 있습니다.
SQL Server에는 어떠한 영역이 유리하다고 보기는 매우 어려우며, 두 개 모두 각각의 환경에서 주는 이점이 있습니다.
클러스터 영역이라면 클러스터 키로 소스데이터가 정열 되었기에 집계 및 정열 등에 좋은 성능을 가질 수 있습니다. 그러나 다른 넌클러스터 인덱스는 소스데이터를 접근 하기 위한 포인터로서 클러스터 키를 가지고 있어, 넌클러스터 영역에서 소스데이터 영역을 접근 하기 위해서는 클러스터 인덱스 루트 레벨부터 데이터를 찾아가야 합니다. 단순히 한 두 개의 행을 = 비교로 가지고 와야 한다면 넌 클러스터 인덱스에서 클러스터 영역으로 데이터를 찾는 방식이 성능상 불리 할 수 도 있습니다.
하지만 이런 점은 SQL Server가 발전되면서 변화된 모습이며 이전의 SQL Server에서는 클러스터 인덱스가 생성된 테이블이라고 하더라도 넌클러스터 인덱스가 소스데이터를 참조하기 위해서 RID를 가지고 있었다고 합니다. 소스데이터의 이동이 발생 하면 RID가 변경 되기에 속하는 넌클러스터 인덱스에 대한 RID값도 변해야 합니다. 페이지 분할 등의 작업이 이루어 진다면 넌클러스터 인덱스의 RID를 변경 하는 작업도 SQLServer에는 많은 영향을 주었을 것 이며, 이러한 문제를 해결 하기 위해 RID대신 클러스터 키 값으로 변경 된 것으로 생각 됩니다. 이러한 변화로 인해 많은 장점을 주고 있지만, 모든 면에서 장점을 주는 것은 아이며, 변화로 인해 클러스터 영역인 경우 넌클러스터에서 접근 시 꼭 클러스터인덱스 루트레벨부터 다시 찾아야 하는 단점이 생겼습니다.
이런 단점을 보완 할 수 있는 방법은 없을까요? “클러스터 영역과 힙 영역을 모두 가지고 있으면 단점이 보완 될 수 있지 않을까?” 라는 생각을 해보았습니다. 물론 데이터의 중복으로 인해 데이터 갱신작업에는 기존보다 오버헤드가 있어 성능에 많은 영향을 줄 수 있습니다. 그러나 특정 서비스인 경우에는 클러스터 영역과 힙 영역을 모두 가짐으로써 좋은 영향을 줄 수 있다고 생각합니다. 하지만 많은 서비스는 이점을 줄 수 없을 것입니다. 이러한 방법이 서비스에 어떠한 영향을 준다 보다는 “이렇게도 가능 하지 않을까” 라는 생각을 해보며 작성 하였습니다.
구현을 위해서 먼저 힙 테이블을 만듭니다. 그리고 힙 테이블에 인덱싱 된 뷰를 생성합니다. 인덱싱 된 뷰를 만들기 위해서는 스키마 바인딩 뷰에 고유한 클러스터 인덱스를 처음에 만들어야 합니다. 그러기에 인덱싱 된 뷰 는 클러스터 인덱스를 가지는 영역이 되며, 소스 테이블에는 힙 상태를 그대로 유지하게 됩니다. 소스테이블에 넌 클러스터 인덱스를 생성 하게 되면 RID를 가지는 넌클러스터 인덱스, 인덱싱 된 뷰에 넌클러스터 인덱스를 만들면 클러스터 키를 포인터로 가지는 넌클러스터 인덱스가 됩니다.
개인적인 생각으로는 특정 환경에서는 충분히 활용 가치가 있을 수 있다고 생각됩니다. 위에서 설명한 하나의 테이블에 두 개의 영역으로 나눈 후 각각의 영역에 동일한 컬럼을 가지고 넌 클러스터 인덱스를 만들어서 테스트를 해보았습니다.
아래는 SQL Server 2005에 있는 샘플데이터베이스인 AdventureWorks에 있는 테이블을 가지고 간단히 구현을 하였습니다. 마지막에 두 가지 쿼리는 몇 개 되지 않는 행을 반환 받는 쿼리입니다.
|
USE AdventureWorks
GO
-- 샘플 테이블을 생성
SELECT * INTO DBO .SalesOrderDetail FROM Sales .SalesOrderDetail;
--스키마 바인딩 뷰를 생성
IF OBJECT_ID ('DBO.VSalesOrderDetail', 'view') IS NOT NULL
DROP VIEW DBO .VSalesOrderDetail ;
GO
CREATE VIEW DBO . VSalesOrderDetail
WITH SCHEMABINDING
AS
SELECT
SalesOrderID ,SalesOrderDetailID ,CarrierTrackingNumber
,OrderQty ,ProductID ,SpecialOfferID ,UnitPrice ,UnitPriceDiscount
,LineTotal ,rowguid ,ModifiedDate
FROM DBO .SalesOrderDetail
--뷰에 고유한 클러스터 인덱스를 생성하여 인덱싱 된 뷰로 생성
Create Unique Clustered Index cl_VSalesOrderDetail on VSalesOrderDetail (SalesOrderDetailID );
--인덱싱 된 뷰, 힙 테이블에 SalesOrderID를 넌 클러스터 인덱스로 만들자
CreateIndex IX_VSalesOrderDetail on VSalesOrderDetail (SalesOrderID );
CreateIndex IX_SalesOrderDetail on SalesOrderDetail (SalesOrderID );
--IO양을 확인하기 위해서.
SET STATISTICS IO ON
select * from SalesOrderDetail where SalesOrderID = 55277;
테이블 'VSalesOrderDetail'. 검색수1 , 논리적읽기수86 , 물리적읽기수0 , 미리읽기수0 , LOB 논리적읽기수0 , LOB 물리적읽기수0 , LOB 미리읽기수0.
select*from SalesOrderDetail with(index(2 ))where SalesOrderID = 55277;
테이블'SalesOrderDetail'. 검색수1 , 논리적읽기수30 , 물리적읽기수0 , 미리읽기수0 , LOB 논리적읽기수0 , LOB 물리적읽기수0 , LOB 미리읽기수0.
SET STATISTICS IO OFF |
7. 마무리
너무 단편적인 예를 들어 설명한 것은 아닌지 걱정이 앞섭니다. 항상 강조하지만 서비스에 대한 정확한 이해를 바탕으로 한 고려가 필요합니다. 잘못 사용되면 성능적 많은 문제가 발생 할 수 있기 때문입니다.
굳이 제가 하고 싶은 말을 두 가지로 정리해 본다면 아래와 같습니다.
“다른 것처럼 보이는 기능이지만 비슷한 기능으로 구현 할 수 없을까?”
”인덱스에 대해 조금 더 생각을 해보자!”
이전 아티클에서 다음 아티클은 괜찮은 아티클로 뵙겠다고 하였는데, 이번에도 그 약속을 지키지 못한 것 같습니다. 다음에는 그 약속을 꼭 지키도록 하겠습니다.^^;;
아참!! 그리고 마지막으로 질문 하나를 하겠습니다.
“인덱싱 된 뷰를 이용하여 넌클러스터 인덱스 기능을 대처 할 수 있지 않을까요?”
감사합니다.
혹시 이 글에 대한 궁금증 및 잘못된 내용이 있다면 위에 있는 제 메일로 보내주시길 바랍니다. | 