안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 7-8. 분할 뷰(Partitioned View)를 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번에 진행할 강좌는 분할 뷰입니다.
TL;DR
구조가 같은 테이블 여러 개가 UNION ALL로 정의된, 분할 뷰에 대해 다룹니다. 분할 뷰는 SQL Server 인스턴스 간 분산되어 존재할 수도 있으며, 분할 뷰 장점, 단점, 주의사항, 성능 향상 방안을 설명합니다.
뷰의 거의 마지막 강좌입니다. 분할 뷰, 느낌으로도 뭔가 분산처리나 성능과 연관이 있을 것 같은 느낌입니다.
분할 뷰란?
분할 뷰는 구조가 같은 테이블 여러 개가 UNION ALL로 정의된 뷰를 의미합니다. 분할 뷰는 하나 또는 여러 개의 SQL Server 인스턴스에 분산되어 존재할 수 있습니다.
분할 뷰 예제 실행
SQL 쿼리 예제를 살펴보겠습니다. 쿼리가 길지만 긴장하지 마세요. 차근차근 진행하겠습니다.
--분할 뷰에 사용할 테이블을 생성 - 체크 제약 잘 살펴보세요.
CREATE TABLE dbo.SUPPLY1 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 25000),
supplier CHAR(50)
);
CREATE TABLE dbo.SUPPLY2 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 25001 and 50000),
supplier CHAR(50)
);
CREATE TABLE dbo.SUPPLY3 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 50001 and 75000),
supplier CHAR(50)
);
CREATE TABLE dbo.SUPPLY4 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 75001 and 100000),
supplier CHAR(50)
);
GO
--모든 테이블을 UNION으로 조합해 분할 뷰를 생성
CREATE VIEW dbo.all_supplier_view
WITH SCHEMABINDING -- 스키마 바인딩 옵션
AS
SELECT supplyID, supplier
FROM dbo.SUPPLY1
UNION ALL
SELECT supplyID, supplier
FROM dbo.SUPPLY2
UNION ALL
SELECT supplyID, supplier
FROM dbo.SUPPLY3
UNION ALL
SELECT supplyID, supplier
FROM dbo.SUPPLY4;
GO
-- 10만 건의 데이터를 WHILE 반복문을 이용해 분할 뷰로 삽입
SET NOCOUNT ON
DECLARE @Counter INT
DECLARE @Guid CHAR(30)
SET @Counter=1
SET @Guid = convert(CHAR(50), NEWID())
WHILE (@Counter <= 100000)
BEGIN
INSERT INTO all_supplier_view(supplyID, supplier) VALUES(@Counter,@Guid)
SET @Counter = @Counter + 1
END;
GO
-- 약 20초 소요. 개발 머신 사양에 따라 다름.
-- 10만 건 전체 조회 할 경우 실행계획 (실제 실행계획 켜고 수행)
SELECT * FROM all_supplier_view;
-- 1개 값만 검색할 경우 실행계획
SELECT * FROM all_supplier_view WHERE supplyID = 100;
우선 4개의 테이블을 생성하고, 10만 건의 데이터를 4개의 테이블에 “분할”해 저장하고 싶습니다. 하지만, 이 예제에서는 다른 방식을 사용합니다. 4개의 테이블로 1개의 뷰 - “all_supplier_view”를 만들고, 이 뷰에 10만 건을 때려 넣습니다.
이전 강좌 7-7. 뷰에서 데이터 수정 강좌에서 이미 뷰를 이용해 데이터를 INSERT가 가능한 것을 확인했습니다. 여기서 한 단계 더 나가가 체크 제약을 활용해 자동으로 테이블에 분할되어 들어가도록 설정할 수 있습니다.
분할 뷰를 생성하고 쿼리를 해보면 여러 장점을 확인할 수 있습니다. - “컨트롤+M”을 눌러 실행계획을 켜고 결과를 보면 이렇게 자동으로 클러스터드 인덱스를 검색(Seek)해서 빠르게 결과를 출력합니다.
이미지 - 분할 뷰 실행계획
최상의 성능을 얻기 위해 분할 뷰는 설정해야 할 내용이 많습니다. 이후 SQLER의 강좌에서 배울 Index(색인) 강좌에서 좀 더 깊이 있게 실행계획을 살펴보는 방법과 성능을 높이는 방법에 대해 논의하니, 조금만 기다려 주세요.
분산 분할 뷰(Distributed partitioned view)
분할 뷰의 한 종류로 여러 서버에 걸쳐 분산된 분할 뷰를 생성할 수 있습니다. 여러 SQL Server 인스턴스를 연결된 서버(Linked Server)로 만들고 이 연결된 서버를 아래처럼 SQL 쿼리로 분산 분할 뷰를 생성합니다.
--Server1에 분할 뷰를 생성 CREATE VIEW Customers AS -- 로컬 CompanyData 데이터베이스의 테이블 SELECT * FROM CompanyData.dbo.Customers_33 UNION ALL -- Server2의 CompanyData DB의 Customers_66 테이블 SELECT * FROM Server2.CompanyData.dbo.Customers_66 UNION ALL -- Server3의 CompanyData DB의 Customers_99 테이블 SELECT * FROM Server3.CompanyData.dbo.Customers_99;
이렇게 Customers 뷰를 생성해 분산 분할 뷰로 만들고 쿼리 할 수 있습니다.
주의사항
분할 뷰와 분산 분할 뷰에서 원하는 성능을 내기 위해서는 고려할 사항과 제약이 많습니다. 차근차근 SQL Server 공식 가이드를 참고해 테이블과 쿼리 패턴을 설계하고 다양한 테스트를 통해 실제 데이터와 유사한 부하테스트와 퍼포먼스 테스트를 수행하고 도입하세요.
더 자세한 분할 뷰에 대한 내용은 이후 SQLER에 따로 포스트를 통해 풀어보겠습니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
