데이터베이스의 테이블 비정규화

8.1 왜 비정규화가 필요한가요?

큰 테이블 사이에서 계산 비용이 가장 많이 드는 작업은 조인입니다. 따라서 하나의 쿼리에서 수백만 개의 행으로 구성된 여러 테이블을 "환기"해야 하는 경우 DBMS는 이러한 처리에 많은 시간을 할애합니다.

이때 사용자는 커피를 마시기 위해 자리를 비울 수 있습니다. 처리의 상호작용성은 실질적으로 사라지고 배치 처리에 접근합니다. 설상가상으로 배치 모드에서는 사용자가 전날 아침에 요청한 모든 데이터를 수신하고 침착하게 작업하여 저녁에 새로운 요청을 준비합니다.

과도한 조인 상황을 피하기 위해 테이블이 비정규화됩니다. 그러나 어쨌든 아닙니다. 트랜잭션 방식으로 비정규화된 테이블을 데이터 웨어하우스용 테이블 구성 규칙에 따라 "정규화된" 것으로 간주할 수 있는 몇 가지 규칙이 있습니다.

분석 처리에서 "정상"으로 간주되는 두 가지 주요 체계는 "눈송이"와 "별"입니다. 이름은 본질을 잘 반영하고 관련 테이블의 그림에서 직접 따릅니다.

두 경우 모두 소위 팩트 테이블은 스키마의 중심 요소이며 이벤트, 트랜잭션, 문서 및 기타 분석가의 관심 대상을 포함합니다. 그러나 트랜잭션 데이터베이스에서 하나의 문서가 여러 테이블(적어도 두 개: 헤더 및 콘텐츠 행)에 "번짐"되면 팩트 테이블에서 하나의 문서, 더 정확하게는 각 행 또는 그룹화된 행 집합이 해당합니다. 하나의 레코드로.

이는 위의 두 테이블을 비정규화하여 수행할 수 있습니다.

8.2 비정규화 예시

이제 DBMS가 예를 들어 해당 기간 동안 Pirozhki LLC 및 Vatrushki CJSC의 고객에 대한 밀가루 판매량을 결정하는 유형의 쿼리를 실행하는 것이 얼마나 쉬운지 평가할 수 있습니다.

정규화된 트랜잭션 데이터베이스에서:

SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

분석 데이터베이스에서:

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

두 개의 문서 테이블과 수백만 행의 구성 사이에 무거운 조인 대신 DBMS는 팩트 테이블과 직접 작업하고 작은 보조 테이블과 가벼운 조인을 수행합니다. 이 작업은 식별자를 몰라도 수행할 수 있습니다.

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

"별"과 "눈송이" 체계로 돌아가 봅시다. 첫 번째 사진의 무대 뒤에는 고객, 그룹, 상점, 판매자 및 실제로 상품의 테이블이 있습니다. 비정규화되면 차원이라고 하는 이러한 테이블도 팩트 테이블에 조인됩니다. 팩트 테이블이 다른 차원(두 번째 수준 이상의 차원)에 대한 링크가 있는 차원 테이블을 참조하는 경우 이러한 스키마를 "눈송이"라고 합니다.

보시다시피 클라이언트 그룹별 필터링을 포함하는 쿼리의 경우 추가 연결을 만들어야 합니다.

SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

이 경우 비정규화가 계속되어 두 번째 차원을 첫 번째 차원으로 떨어뜨려 팩트 테이블을 더 쉽게 쿼리할 수 있습니다.

팩트 테이블이 두 번째 수준이 없는 차원만 참조하는 스키마를 스타 스키마라고 합니다. 측정 테이블의 수는 별의 "광선" 수에 해당합니다.

스타 스키마는 차원의 계층 구조와 단일 쿼리에서 해당 테이블을 조인할 필요성을 완전히 제거합니다.

SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

비정규화의 단점은 항상 중복성으로 인해 트랜잭션 및 분석 애플리케이션 모두에서 데이터베이스 크기가 증가합니다. "눈송이"를 "별"로 변환하는 위의 예에서 대략적인 델타를 계산해 봅시다.

Oracle과 같은 일부 DBMS에는 데이터베이스 스키마 정의 수준에 특별한 정수 유형이 없으므로 일반 부울 유형을 사용해야 합니다. numeric(N)여기서 N은 저장된 비트 수입니다. 이러한 숫자의 저장 크기는 물리적 데이터 저장에 대한 문서에 제공된 특수 공식을 사용하여 계산되며 일반적으로 "16비트 정수"와 같은 저수준 유형의 크기를 1-3바이트 초과합니다.

sales 테이블이 데이터 압축을 사용하지 않고 약 5억개의 행을 포함하고 고객 그룹의 수가 약 1000개라고 가정합니다. 이 경우 식별자 유형으로 id_customer_group2바이트를 차지하는 짧은 정수(shortint, smallint)를 사용할 수 있습니다.

DBMS가 2바이트 정수 유형(예: PostgreSQL, SQL Server, Sybase 등)을 지원한다고 가정합니다. 그런 다음 판매 테이블에 해당 열을 추가하면 id_customer_group크기가 최소한 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 비정규화는 언제 필요합니까?

비정규화가 유용할 수 있는 몇 가지 일반적인 상황을 살펴보겠습니다.

많은 수의 테이블 조인

완전히 정규화된 데이터베이스에 대한 쿼리에서 종종 최대 12개 이상의 테이블을 조인해야 합니다. 그리고 각 연결은 매우 리소스 집약적인 작업입니다. 결과적으로 이러한 요청은 서버 리소스를 소비하고 느리게 수행됩니다.

이러한 상황에서 도움이 될 수 있습니다.

• 테이블 수를 줄여서 비정규화. 거의 변경되지 않은 (종종 조건부 상수 또는 참조) 정보와 의미와 밀접하게 관련된 정보를 포함하는 작은 크기의 여러 테이블로 결합하는 것이 좋습니다.
• 일반적으로 많은 수의 쿼리에서 5~6개 이상의 테이블을 조인해야 하는 경우 데이터베이스 비정규화를 고려해야 합니다.
• 테이블 중 하나에 추가 필드를 추가하여 비정규화. 이 경우 데이터 중복성이 나타나고 데이터베이스 무결성을 유지하기 위해 추가 조치가 필요합니다.

예상 값

종종 쿼리는 느리고 많은 리소스를 소비하며 특히 그룹화 및 집계 함수(Sum, Max 등)를 사용할 때 일부 복잡한 계산이 수행됩니다. 자주 사용되는(그리고 계산하기 어려운) 계산된 데이터를 포함하는 테이블에 1-2개의 추가 열을 추가하는 것이 이치에 맞는 경우가 있습니다.

각 주문의 총 비용을 결정한다고 가정합니다. 이렇게하려면 먼저 각 제품의 비용을 결정해야합니다 ( "제품 단위 수"* "제품 단가"-할인 공식에 따라). 그런 다음 비용을 주문별로 그룹화해야 합니다.

이 쿼리를 실행하는 것은 매우 복잡하며 데이터베이스가 많은 수의 주문에 대한 정보를 저장하는 경우 시간이 오래 걸릴 수 있습니다. 이러한 쿼리를 실행하는 대신 주문 단계에서 비용을 결정하고 주문 테이블의 별도 열에 저장할 수 있습니다. 이 경우 원하는 결과를 얻으려면 이 열에서 미리 계산된 값을 추출하면 됩니다.

미리 계산된 값을 포함하는 열을 생성하면 쿼리를 실행할 때 많은 시간을 절약할 수 있지만 적시에 해당 열의 데이터를 업데이트해야 합니다.

긴 챙

데이터베이스에 긴 필드(Blob, Long 등)가 포함된 큰 테이블이 있는 경우 긴 필드를 별도의 테이블로 이동하면 해당 테이블에 대한 쿼리 실행 속도를 크게 높일 수 있습니다. 예를 들어 사진 자체를 blob 필드(전문적인 품질, 고해상도 및 적절한 크기)에 저장하는 것을 포함하여 데이터베이스에 사진 카탈로그를 만들고 싶습니다. 정규화의 관점에서 다음 테이블 구조는 절대적으로 정확합니다.

• 사진이 부착 된 신분증
• 저자 ID
• 카메라 모델 ID
• 사진 자체(블롭 필드)

이제 작성자가 찍은 사진의 수를 세면서 쿼리가 실행되는 시간을 상상해 봅시다.

이러한 상황에서 올바른 솔루션(정규화 원칙을 위반하더라도)은 사진 ID와 사진 자체가 있는 blob 필드의 두 필드로만 구성된 다른 테이블을 만드는 것입니다. 그런 다음 메인 테이블(더 이상 큰 블롭 필드가 없는)에서 선택하면 즉시 이동하지만 사진 자체를 보고 싶을 때 기다리겠습니다.

비정규화가 정당화되는 시기를 어떻게 결정합니까?

8.4 비정규화의 장단점

특정 단계가 정당한지 여부를 결정하는 한 가지 방법은 비용 및 가능한 이점 측면에서 분석을 수행하는 것입니다. 비정규화된 데이터 모델의 비용은 얼마입니까?

요구 사항 결정(달성하고자 하는 것) → 데이터 요구 사항 결정(따라야 할 것) → 이러한 요구 사항을 충족하는 최소 단계 찾기 → 구현 비용 계산 → 구현

비용에는 디스크 공간, 이 구조를 관리하는 데 필요한 리소스, 이 프로세스 유지와 관련된 시간 지연으로 인한 기회 손실과 같은 물리적 측면이 포함됩니다. 비정규화 비용을 지불해야 합니다. 비정규화된 데이터베이스는 데이터 중복성을 증가시켜 성능을 향상시킬 수 있지만 관련 데이터를 제어하는 ​​데 더 많은 노력이 필요합니다. 데이터가 반복되고 추적하기 어려워지므로 애플리케이션을 만드는 프로세스가 더욱 어려워집니다. 또한 참조 무결성 구현이 쉽지 않습니다. 관련 데이터가 서로 다른 테이블로 나뉩니다.

이점에는 더 빠른 쿼리 성능과 더 빠른 응답을 얻을 수 있는 기능이 포함됩니다. 처리량 증가, 고객 만족도, 생산성 향상, 외부 개발자 도구의 보다 효율적인 사용 등 다른 이점도 얻을 수 있습니다.

요청 속도 및 성능 일관성

예를 들어 기업에서 매일 생성되는 1,000개의 쿼리 중 72%는 드릴다운 쿼리가 아닌 요약 수준 쿼리입니다. 요약 테이블을 사용하면 쿼리가 4분이 아니라 약 6초 만에 실행되므로 처리 시간이 3,000분 단축됩니다. 매주 피벗 테이블을 유지 관리하는 데 소요되는 100분을 조정한 후에도 주당 2,500분을 절약할 수 있으므로 피벗 테이블 생성이 정당화됩니다. 시간이 지남에 따라 대부분의 쿼리가 요약 데이터가 아니라 세부 데이터로 지정될 수 있습니다. 요약 테이블을 사용하는 쿼리가 적을수록 다른 프로세스에 영향을 주지 않고 쉽게 삭제할 수 있습니다.

그리고…

위에 나열된 기준은 최적화의 다음 단계를 수행할지 여부를 결정할 때 고려해야 할 유일한 기준이 아닙니다. 비즈니스 우선 순위 및 최종 사용자 요구 사항을 포함하여 다른 요소를 고려해야 합니다. 사용자는 기술적인 관점에서 모든 요청이 몇 초 안에 완료되기를 원하는 사용자의 요구 사항이 시스템 아키텍처에 어떤 영향을 미치는지 이해해야 합니다. 이러한 이해를 달성하는 가장 쉬운 방법은 이러한 테이블을 만들고 관리하는 것과 관련된 비용을 설명하는 것입니다.

8.5 비정규화를 능숙하게 구현하는 방법.

상세 표 저장

비즈니스에 중요한 데이터베이스의 기능을 제한하지 않으려면 대체가 아닌 공존의 전략을 채택해야 합니다. 예를 들어 적중 카운터입니다. 비즈니스의 경우 웹 페이지 방문 횟수를 알아야 합니다. 그러나 분석(기간별, 국가별...)을 위해서는 각 방문에 대한 정보가 포함된 테이블과 같은 자세한 데이터가 필요할 가능성이 큽니다.

트리거 사용

integrity중복 데이터의 무결성을 유지하기 위해 데이터베이스 트리거를 사용하여 데이터베이스 구조를 비정규화하고 정규화의 이점을 계속 누릴 수 있습니다 .

예를 들어 계산된 필드를 추가할 때 계산된 필드가 의존하는 각 열은 필요한 데이터를 계산된 필드에 쓰는 단일 저장 프로시저(중요!)를 호출하는 트리거와 연결됩니다. 계산된 필드가 종속된 열을 건너뛰지 않아도 됩니다.

소프트웨어 지원

기본 제공 트리거 및 저장 프로시저를 사용하지 않는 경우 애플리케이션 개발자는 비정규화된 데이터베이스에서 데이터의 일관성을 보장해야 합니다.

트리거와 유사하게 변경되는 필드에 의존하는 모든 필드를 업데이트하는 하나의 함수가 있어야 합니다.

결론

비정규화할 때 데이터베이스 속도 증가와 일관성 없는 데이터의 위험 증가 사이, 프로그래머의 작성 편의성 사이에서 균형을 유지하고 Select-s데이터베이스 채우기 및 데이터 업데이트를 제공하는 사람들의 작업을 복잡하게 만드는 것이 중요합니다. 따라서 꼭 필요한 경우에만 데이터베이스를 매우 신중하게, 매우 선택적으로 비정규화해야 합니다.

비정규화의 장단점을 미리 계산하는 것이 불가능하다면 초기에는 정규화된 테이블로 모델을 구현한 다음 문제 쿼리를 최적화하기 위해 비정규화를 수행해야 한다.

서로 다른 테이블에서 관련 데이터를 반복해서 가져오는 경우에만 점진적으로 비정규화를 도입하는 것이 중요합니다. 데이터를 복제하면 레코드 수는 늘어나지만 읽기 수는 줄어듭니다. 불필요한 집계 선택을 피하기 위해 계산된 데이터를 열에 저장하는 것도 편리합니다.