읽기 성능을 위해 OLAP 데이터베이스를 비정규 화해야합니까?
나는 항상 데이터베이스가 OLAP 데이터베이스 설계를 위해 수행되는 것처럼 읽기 성능을 위해 비정규 화되어야하며 OLTP 설계를 위해 3NF를 과장하지 않아야한다고 항상 생각했습니다.
다양한 게시물의 PerformanceDBA, 예를 들어, 시간 기반 데이터에 대한 서로 다른 aproaches의 성능에서 데이터베이스는 5NF 및 6NF (Normal Form)로 정규화하여 항상 잘 설계되어야한다는 패러다임을 방어합니다.
올바르게 이해 했습니까 (그리고 올바르게 이해 했습니까)?
OLAP 데이터베이스 (3NF 이하)의 기존 비정규 화 접근 방식 / 패러다임 설계와 3NF가 OLTP 데이터베이스의 대부분의 실제 사례에 충분하다는 조언에 무엇이 문제입니까?
예를 들면 :
비정규 화가 읽기 성능을 촉진한다는 이론을 결코 파악할 수 없었 음을 고백해야합니다. 누구든지 이것과 반대되는 신념에 대한 좋은 논리적 설명과 함께 참조를 줄 수 있습니까?
이해 관계자가 OLAP / 데이터웨어 하우징 데이터베이스를 정규화해야한다고 설득 할 때 참조 할 수있는 출처는 무엇입니까?
가시성을 높이기 위해 여기에 댓글을 복사했습니다.
"참가자가 6NF에서 실제로 보거나 참여한 데이터웨어 하우스 구현 (과학 프로젝트 포함되지 않음)을 얼마나 많이 추가 (공개) 할 수 있다면 좋을 것입니다. 일종의 빠른 풀입니다. Me = 0." – Damir Sudarevic
Wikipedia의 데이터웨어 하우스 기사 는 다음과 같이 말합니다.
"표준화 된 접근 방식 [Ralph Kimball의 차원 적 접근 방식과 비교] 은 지지자가"Inmonites " 라고 불리는 3NF 모델 (Third Normal Form)이라고도하며 데이터웨어 하우스가 ER 모델 / 정규화 된 모델을 사용하여 모델링되었습니다. "
정규화 된 데이터웨어 하우징 접근 방식 (Bill Inmon)이 3NF (?)를 초과하지 않는 것으로 인식되는 것 같습니다.
데이터웨어 하우징 / OLAP가 비정규 화의 동의어라는 신화 (또는 유비쿼터스 공리적 신념)의 기원이 무엇인지 이해하고 싶습니다.
Damir Sudarevic은 그들이 잘 포장 된 접근 방식이라고 대답했습니다. 질문으로 돌아가겠습니다. 비정규 화가 읽기를 용이하게하는 이유는 무엇입니까?
신화학
나는 항상 OLAP 데이터베이스 설계를 위해 수행되는 것처럼 데이터베이스가 읽기를 위해 비정규 화되어야한다고 생각했고, OLTP 설계를 위해 3NF를 훨씬 더 과장하지 않아야한다고 생각했습니다.
그 효과에 대한 신화가 있습니다. Relational Database 컨텍스트에서, 저는 소위 "비정규 화 된" "데이터베이스"라고하는 6 개의 매우 큰 데이터베이스를 다시 구현했습니다. 그리고 단순히 표준화하고 표준 및 엔지니어링 원칙을 적용하여 다른 사람의 문제를 수정하는 80 개 이상의 과제를 실행했습니다. 나는 신화에 대한 어떤 증거도 본 적이 없습니다. 마치 마법의기도 인 것처럼 만트라를 반복하는 사람들 만 있습니다.
정규화 vs 비정규 화
( "비정규 화"는 사용을 거부하는 사기 용어입니다.)
이것은 과학 산업입니다 (적어도 깨지지 않는 소프트웨어를 제공하는 것, 달에 사람을 두는 것, 은행 시스템을 운영하는 것 등). 그것은 마법이 아니라 물리학의 법칙에 의해 지배됩니다. 컴퓨터와 소프트웨어는 모두 물리 법칙의 적용을받는 유한하고 유형의 물리적 객체입니다. 내가받은 중등 및 고등 교육에 따르면 :
더 크고, 더 뚱뚱하고, 덜 조직 된 오브젝트가 더 작고, 더 얇고, 더 조직화 된 오브젝트보다 더 잘 수행되는 것은 불가능합니다.
정규화는 더 많은 테이블을 생성하지만 각 테이블은 훨씬 더 작습니다. 테이블이 더 많더라도 실제로는 (a) 조인 수가 적고 (b) 집합이 더 작기 때문에 조인이 더 빠릅니다. 각각의 작은 테이블에는 더 적은 인덱스가 필요하므로 전체적으로 더 적은 인덱스가 필요합니다. 정규화 된 테이블은 또한 훨씬 더 짧은 행 크기를 생성합니다.
주어진 리소스 집합에 대해 정규화 된 테이블 :
- 동일한 페이지 크기에 더 많은 행 맞추기
- 따라서 동일한 캐시 공간에 더 많은 행이 들어가므로 전체 처리량이 증가합니다.)
- 따라서 동일한 디스크 공간에 더 많은 행을 맞출 수 있으므로 I / O 수가 감소합니다. I / O가 필요할 때 각 I / O가 더 효율적입니다.
.
- 과도하게 복제 된 객체가 단일 버전의 진리로 저장된 객체보다 더 나은 성능을 발휘하는 것은 불가능합니다. 예 : 테이블 및 열 수준에서 5 배 중복을 제거하면 모든 트랜잭션의 크기가 줄어 들었습니다. 잠금이 감소했습니다. Update Anomalies가 사라졌습니다. 이로 인해 경합이 크게 줄어들고 동시 사용이 증가했습니다.
따라서 전반적인 결과는 훨씬 더 높은 성능이었습니다.
동일한 데이터베이스에서 OLTP와 OLAP를 모두 제공하는 경험상 읽기 전용 (OLAP) 쿼리의 속도를 높이기 위해 정규화 된 구조를 "비정규 화"할 필요가 없었습니다. 그것은 또한 신화입니다.
- 아니요, 다른 사람들이 요청한 "비정규 화"는 속도를 줄였고 제거되었습니다. 놀랍지는 않지만 요청자들은 놀랐습니다.
신화를 파는 사람들이 많은 책을 썼습니다. 이들은 비 기술적 인 사람들이라는 것을 인식해야합니다. 그들은 마법을 판매하고 있기 때문에 그들이 판매하는 마법은 과학적 근거가 없으며 판매 피치에서 물리학 법칙을 편리하게 피합니다.
(위의 물리 과학에 대해 이의를 제기하고자하는 사람은 단순히 만트라를 반복하는 것만으로는 효과가 없으므로 해당 만트라를 뒷받침하는 구체적인 증거를 제공하십시오.)
신화가 널리 퍼진 이유는 무엇입니까?
글쎄, 첫째, 물리학 법칙을 극복하는 방법을 찾지 않는 과학 유형 사이에서 널리 퍼지지는 않습니다.
내 경험을 통해 나는 유행에 대한 세 가지 주요 이유를 확인했습니다.
데이터를 정규화 할 수없는 사람들에게는 그렇게하지 않는 것이 편리한 이유입니다. 그들은 마법의 책을 참조 할 수 있으며 마법에 대한 증거없이 "저명한 작가가 내가 한 일을 검증하는 것을 보아라"라고 경건하게 말할 수 있습니다. 완료되지 않음, 가장 정확하게.
많은 SQL 코더는 단순한 단일 수준 SQL 만 작성할 수 있습니다. 정규화 된 구조에는 약간의 SQL 기능이 필요합니다. 그들이 가지고 있지 않다면; 임시 테이블을 사용하지 않고 SELECT를 생성 할 수없는 경우 하위 쿼리를 작성할 수없는 경우에는 처리 할 수있는 플랫 파일 ( "비정규 화 된"구조)에 심리적으로 붙어 있습니다 .
사람들 은 책을 읽고 이론을 토론하는 것을 좋아 합니다. 경험없이. 특히 마법에요. 실제 경험을 대신하는 강장제입니다. 실제로 데이터베이스를 올바르게 정규화 한 사람은 "비정규 화가 정규화보다 빠르다"고 말한 적이 없습니다. 진언을 말하는 사람에게 나는 단순히 "증거를 보여줘"라고 말하고 그들은 어떤 것도 생산하지 않았다. 따라서 현실은 사람들 이 정규화 경험없이 이러한 이유로 신화를 반복한다는 것입니다 . 우리는 가축이며, 알려지지 않은 것은 우리의 가장 큰 두려움 중 하나입니다.
이것이 제가 항상 모든 프로젝트에 "고급"SQL과 멘토링을 포함하는 이유입니다.
내 대답
이 답변은 내가 귀하의 질문의 모든 부분에 답변하거나 다른 답변 중 일부에서 잘못된 요소에 응답하면 엄청나게 길 것입니다. 예 : 위의 답변은 단 하나의 항목입니다. 따라서 특정 구성 요소를 다루지 않고 전체적으로 귀하의 질문에 답하고 다른 접근 방식을 취하겠습니다. 나는 당신의 질문과 관련된 과학, 내가 자격이 있고 경험이 풍부한 과학만을 다룰 것입니다.
관리 가능한 부분으로 과학을 소개하겠습니다. 
6 개의 대규모 전체 구현 할당의 전형적인 모델.
- 이들은 소기업에서 흔히 볼 수있는 폐쇄 된 "데이터베이스"였으며 조직은 대형 은행이었습니다.
- 1 세대 앱 실행 마인드에게는 매우 좋지만 성능, 무결성 및 품질 측면에서 완전한 실패
- 각 앱에 대해 개별적으로 설계되었습니다.
- 보고가 불가능했고 각 앱을 통해서만보고 할 수있었습니다.
- "비정규 화"는 신화이기 때문에 정확한 기술적 정의는 비정규 화되었습니다.
- "비정규 화"하려면 먼저 정규화해야합니다. 그런 다음 사람들이 "비정규 화 된"데이터 모델을 보여준 모든 경우에서 프로세스를 약간 역전시킵니다. 단순한 사실은 그들이 전혀 정규화하지 않았다는 것입니다. 그래서 "비정규 화"는 불가능했습니다. 단순히 정규화되지 않았습니다.
- 관계형 기술이나 데이터베이스의 구조 및 제어가 많지 않았지만 "데이터베이스"로 전달되었으므로 해당 단어를 따옴표로 묶었습니다.
- 비정규 화 된 구조에 대해 과학적으로 보장 된 것처럼, 그들은 여러 버전의 진실 (데이터 중복)을 겪었 기 때문에 각 버전 내에서 높은 경합과 낮은 동시성을 겪었습니다.
- 그들은 데이터 중복의 추가 문제가 있었다 가로 질러 은 "데이터베이스"
- 조직은 모든 중복 항목을 동기화 상태로 유지하려고했기 때문에 복제를 구현했습니다. 물론 추가 서버를 의미했습니다. 개발할 ETL 및 동기화 스크립트 유지; 기타
- 말할 필요도없이, 동기화는 결코 충분하지 않았고 그들은 그것을 영원히 바꾸고있었습니다.
- 모든 경합과 낮은 처리량으로 인해 각 "데이터베이스"에 대해 별도의 서버를 정당화하는 것은 전혀 문제가되지 않았습니다. 별로 도움이되지 않았습니다.
So we contemplated the laws of physics, and we applied a little science. 
We implemented the Standard concept that the data belongs to the corporation (not the departments) and the corporation wanted one version of the truth. The Database was pure Relational, Normalised to 5NF. Pure Open Architecture, so that any app or report tool could access it. All transactions in stored procs (as opposed to uncontrolled strings of SQL all over the network). The same developers for each app coded the new apps, after our "advanced" education.
분명히 과학은 효과가있었습니다. 글쎄, 그것은 나의 사적인 과학이나 마술이 아니었고, 평범한 공학과 물리학의 법칙이었습니다. 모두 하나의 데이터베이스 서버 플랫폼에서 실행되었습니다. 두 쌍의 서버 (프로덕션 및 DR)가 폐기되어 다른 부서에 제공되었습니다. 총 720GB 인 5 개의 "데이터베이스"는 총 450GB의 하나의 데이터베이스로 정규화되었습니다. 약 700 개의 테이블 (많은 중복 및 중복 된 열)이 500 개의 중복되지 않은 테이블로 정규화되었습니다. 전체적으로 10 배 더 빠르며 일부 기능에서는 100 배 이상 더 빠르게 수행되었습니다. 그것은 내 의도 였기 때문에 놀라지 않았고 과학은 그것을 예측했지만 만트라로 사람들을 놀라게했습니다.
더 많은 정규화
글쎄요, 모든 프로젝트에서 정규화로 성공하고 관련 과학에 대한 확신을 가지고 있었기 때문에 정규화를 더 많이 , 더 적게 하는 것은 자연스러운 진행 이었습니다. 예전에는 3NF가 충분히 좋았고 나중에는 NF가 아직 확인되지 않았습니다. 지난 20 년 동안 업데이트 이상이없는 데이터베이스 만 제공했기 때문에 오늘날의 NF 정의에 따르면 항상 5NF를 제공했습니다.
마찬가지로 5NF는 훌륭하지만 한계가 있습니다. 예 : 큰 테이블 (MS PIVOT Extension에 따라 작은 결과 집합이 아님)을 피벗하는 속도가 느 렸습니다. 그래서 저 (그리고 다른 사람들)는 피벗 팅이 (a) 쉽고 (b) 매우 빠르도록 정규화 된 테이블을 제공하는 방법을 개발했습니다. 이제 6NF가 정의되었으므로 해당 테이블은 6NF입니다.
동일한 데이터베이스에서 OLAP 및 OLTP를 제공하기 때문에 과학에 따라 구조가 더 많이 정규화됨을 발견했습니다.
더 빨리 수행
더 많은 방법 (예 : 피벗)으로 사용할 수 있습니다.
그래서 예, 저는 일관되고 변하지 않는 경험을 가지고 있습니다. 그것은 정규화 될뿐만 아니라 비정규 화 또는 "비정규 화"보다 훨씬 빠릅니다. more Normalized는 덜 정규화 된 것보다 훨씬 빠릅니다 .
성공의 신호 중 하나는 기능의 증가입니다 (실패의 신호는 기능의 증가없이 크기가 증가하는 것입니다). 즉, 그들은 즉시 더 많은보고 기능을 요청했고, 이는 우리가 훨씬 더 많은 정규화 를 의미 하고 이러한 특수 테이블을 더 많이 제공했습니다 (몇 년 후 6NF로 밝혀 짐).
그 주제를 진행하고 있습니다. 저는 항상 데이터웨어 하우스 전문가가 아니라 데이터베이스 전문가 였기 때문에웨어 하우스를 사용하는 처음 몇 개의 프로젝트는 완전한 구현이 아니라 실질적인 성능 조정 작업이었습니다. 그들은 내가 전문화 한 제품에 대한 내 야심에 있었다.
일반적인 경우를 살펴보고 있으므로 정확한 정규화 수준 등에 대해 걱정하지 마십시오. 우리는 OLTP 데이터베이스가 합리적으로 정규화되었지만 OLAP가 불가능했고 조직이 완전히 별도의 OLAP 플랫폼 인 하드웨어를 구입했다고 생각할 수 있습니다. 대량의 ETL 코드를 개발하고 유지하는 데 투자했습니다. 그런 다음 구현 후 생성 된 복제본을 관리하는 데 수명의 절반을 소비했습니다. 여기에서 책 작가와 공급 업체는 조직이 구매하게 하는 하드웨어 및 별도의 플랫폼 소프트웨어 라이선스 의 막대한 낭비로 인해 비난 받아야합니다 .
- 아직 관찰하지 않으 셨다면 일반적인 1 세대 "데이터베이스" 와 일반적인 데이터웨어 하우스 의 유사점을 확인하시기 바랍니다.
한편 팜 ( 위 의 5NF 데이터베이스 )으로 돌아가서 우리는 점점 더 많은 OLAP 기능을 계속 추가했습니다. 물론 앱 기능은 성장했지만, 비즈니스는 변하지 않았습니다. 그들은 더 많은 6NF를 요구할 것이고 제공하기가 쉬웠습니다 (5NF에서 6NF까지의 단계는 작은 단계입니다. 0NF는 5NF는 말할 것도없고 큰 단계입니다. 조직화 된 아키텍처는 확장하기 쉽습니다).
별도의 OLAP 플랫폼 소프트웨어 의 기본 정당화 인 OLTP와 OLAP의 주요 차이점 중 하나 는 OLTP가 행 지향적이며 트랜잭션 보안 행이 필요하며 빠르다는 것입니다. OLAP는 트랜잭션 문제에 대해 신경 쓰지 않고 열이 필요하며 빠릅니다. 이것이 모든 하이 엔드 BI 또는 OLAP 플랫폼 이 열 지향적 인 이유이며 OLAP 모델 (스타 스키마, 차원-팩트)이 열 지향적 인 이유 입니다.
그러나 6NF 테이블 :
행은없고 열만 있습니다. 우리는 동일한 눈부신 속도로 행과 열을 제공합니다.
테이블 (즉, 6NF 구조의 5NF보기)은 이미 Dimension-Facts로 구성되어 있습니다. 실제로 이들은 모두 차원 이기 때문에 OLAP 모델이 식별하는 것보다 더 많은 차원으로 구성됩니다 .
즉석에서 집계를 사용하여 전체 테이블을 피벗 (소수 파생 열의 PIVOT과 반대로)하면 (a) 쉽고 간단한 코드 및 (b) 매우 빠름
정의에 따라 우리가 수년 동안 공급해온 것은 OLTP 사용을 위해 최소 5NF, OLAP 요구 사항을 위해 6NF를 갖춘 관계형 데이터베이스입니다.
우리가 처음부터 사용한 것과 똑같은 과학임을 주목하십시오. 에서 이동하는 전형적인 않은 정규화 "데이터베이스" 에 5NF 기업의 데이터베이스 . 우리는 단순히 입증 된 과학을 더 많이 적용하고 더 높은 수준의 기능과 성능을 얻고 있습니다.
5NF 기업 데이터베이스 와 6NF 기업 데이터베이스 의 유사점에 주목하십시오.
별도의 OLAP 하드웨어, 플랫폼 소프트웨어, ETL, 관리, 유지 보수의 전체 비용이 모두 제거됩니다.
데이터의 버전은 하나 뿐이며 업데이트 이상이나 유지 관리가 없습니다. OLTP에 대해 행으로 제공되고 OLAP에 대해 열로 제공되는 동일한 데이터
우리가하지 않은 유일한 일은 새로운 프로젝트를 시작하고 처음부터 순수한 6NF를 선언하는 것입니다. 그것이 내가 다음에 줄을서는 것입니다.
여섯 번째 정규형이란 무엇입니까?
정규화에 대한 핸들이 있다고 가정하면 (여기서는 정의하지 않겠습니다)이 스레드와 관련된 비 학문적 정의는 다음과 같습니다. 이는 테이블 수준에서 적용되므로 동일한 데이터베이스에서 5NF 및 6NF 테이블을 혼합하여 사용할 수 있습니다.
- 다섯 번째 정규 형식 : 데이터베이스 전체에서 해결 된 모든 기능 종속성
- 4NF / BCNF 외에
- 모든 비 PK 열은 PK와 함께 1 :: 1입니다.
- 그리고 다른 PK에게
- 업데이트 이상 없음
.
- Sixth Normal Form : 축소 불가능한 NF, 데이터를 더 이상 축소하거나 정규화 할 수없는 지점 (7NF는 없음)
- 5NF 외에
- 행은 기본 키와 최대 하나의 키가 아닌 열로 구성됩니다.
- Null 문제 제거
6NF는 어떻게 생겼습니까?
데이터 모델은 고객의 소유이며 당사의 지적 재산은 무료로 게시 할 수 없습니다. 그러나 저는이 웹 사이트에 참석하여 질문에 대한 구체적인 답변을 제공합니다. 실제 사례가 필요하므로 내부 유틸리티 중 하나에 대한 데이터 모델을 게시하겠습니다.
이는 고객 수에 관계없이 일정 기간 동안 서버 모니터링 데이터 (엔터프라이즈 급 데이터베이스 서버 및 OS)를 수집하기위한 것입니다. 이를 사용하여 성능 문제를 원격으로 분석하고 수행하는 성능 조정을 확인합니다. 구조는 10 년 넘게 변경되지 않았으며 (기존 구조의 변경없이 추가됨), 수년 후 6NF로 식별 된 것은 전문화 된 5NF의 전형입니다. 전체 피벗을 허용합니다. 모든 차원에 그릴 차트 또는 그래프 (22 개의 피벗이 제공되지만 제한이 아님) 슬라이스와 주사위; 믹스 앤 매치. 그것들이 모두 차원 임을 주목하십시오 .
모니터링 데이터 또는 메트릭 또는 벡터는 모델에 영향을주지 않고 변경 될 수 있습니다 (서버 버전 변경; 우리는 더 많은 것을 선택하고 싶습니다). 희석되지 않은 아버지이므로 표준, 무결성 또는 관계 권한을 희생하지 않고 EAV의 모든 기능을 제공합니다. 단순히 행을 추가합니다.
▶ 통계 데이터 모델 모니터링 ◀ . (인라인에 비해 너무 큽니다. 일부 브라우저는 인라인으로로드 할 수 없습니다. 링크를 클릭하십시오.)
고객으로부터 원시 모니터링 통계 파일을받은 후 이러한 ▶ Charts Like This ◀ , 여섯 번의 키 입력 을 생성 할 수 있습니다. 믹스 앤 매치에 주목하십시오. 동일한 차트의 OS 및 서버; 다양한 피벗. (허가를 받아 사용합니다.)
관계형 데이터베이스 모델링 표준에 익숙하지 않은 독자 라면 ▶ IDEF1X Notation ◀이 도움이 될 것입니다.
6NF 데이터웨어 하우스
이는 최근 Anchor Modeling에 의해 검증되었으며 , 현재 데이터웨어 하우스를위한 "차세대"OLAP 모델로 6NF를 제시하고 있습니다. (단일 버전의 데이터에서 OLTP 및 OLAP를 제공하지 않습니다.
데이터웨어 하우스 (전용) 경험
데이터웨어 하우스 (위의 6NF OLTP-OLAP 데이터베이스가 아님)에 대한 저의 경험은 전체 구현 프로젝트와 달리 몇 가지 주요 과제였습니다. 결과는 놀랍지 않습니다.
과학과 일치하여 정규화 된 구조는 훨씬 더 빠르게 수행됩니다. 유지하기가 더 쉽습니다. 더 적은 데이터 동기화가 필요합니다. 킴볼이 아니라 인몬.
마술과 일치하게, 테이블을 정규화하고 물리학 법칙을 적용하여 성능을 크게 향상시킨 후 놀란 사람은 만트라를 가진 마술사뿐입니다.
과학적으로 생각하는 사람들은 그렇게하지 않습니다. 그들은 은탄과 마법을 믿거 나 의지하지 않습니다. 그들은 문제를 해결하기 위해 과학을 사용합니다.
유효한 데이터웨어 하우스 근거
그렇기 때문에 다른 게시물에서 별도의 데이터웨어 하우스 플랫폼, 하드웨어, ETL, 유지 관리 등에 대한 유일하게 유효한 정당성은보고를 위해 중앙웨어 하우스로 병합되는 많은 데이터베이스 또는 "데이터베이스"가있는 경우입니다. 및 OLAP.
Kimball
데이터웨어 하우스에서 "성능을 위해 비정규 화"를지지하는 Kimball에 대한 한 마디가 필요합니다. 위의 내 정의에 따르면, 그는 분명히 자신의 삶에서 정규화 한 적이없는 사람들 중 한 명입니다 . 그의 시작점은 비정규 화 ( "비정규 화"로 위장)였으며 단순히 Dimension-Fact 모델에서 구현했습니다.
물론 성능을 얻기 위해 그는 훨씬 더 "비정규 화"하고 추가 복제를 생성하고 모든 것을 정당화해야했습니다.
따라서 정신 분열증과 같은 방식으로 비정규 화 된 구조를 "비정규 화"하여보다 전문화 된 사본을 만들어 "읽기 성능을 향상"시키는 것은 사실입니다. 전체를 고려할 때는 사실이 아닙니다. 그것은 외부가 아니라 그 작은 망명 내부에서만 사실입니다.
마찬가지로 모든 "테이블"이 괴물이고 "조인은 비싸다"고 피해야 할 것이 있다는 것은 미친 방식으로 사실입니다. 그들은 더 작은 테이블과 세트를 결합한 경험이 없기 때문에 더 작은 테이블이 더 빠르다는 과학적 사실을 믿을 수 없습니다.
그들은 중복 "테이블" 을 만드는 것이 더 빠르다 는 경험이 있으므로 중복 을 제거하는 것이 그것보다 더 빠르다고 믿을 수 없습니다 .
그의 차원은 정규화되지 않은 데이터에 추가 됩니다. 데이터가 정규화되지 않았으므로 차원이 노출되지 않습니다. 정규화 된 모델에서는 차원이 데이터의 필수 부분으로 이미 노출되어 있지만 추가 할 필요가 없습니다 .
그 잘 포장 된 Kimball의 길은 더 많은 레밍이 더 빨리 죽음으로 떨어지는 절벽으로 이어집니다. Lemmings는 무리 동물입니다. 함께 길을 걷고 함께 죽는 한 그들은 행복하게 죽습니다. Lemmings는 다른 길을 찾지 않습니다.
모든 이야기, 함께 어울리고 서로를 지원하는 하나의 신화의 일부입니다.
당신의 임무
수락하기로 선택해야합니다. 저는 여러분 자신을 위해 생각하고 과학과 물리학 법칙에 모순되는 생각을 그만 두도록 요청합니다. 얼마나 흔하거나 신비 롭거나 신화 적이든 상관 없습니다. 그것을 신뢰하기 전에 증거를 찾으십시오. 과학적이며 자신에 대한 새로운 신념을 확인하십시오. "성능을 위해 비정규 화"라는 말을 반복한다고해서 데이터베이스 속도가 빨라지는 것이 아니라 기분이 나아질뿐입니다. 사이드 라인에 앉아있는 뚱뚱한 아이가 경주에있는 모든 아이들보다 더 빨리 달릴 수 있다고 스스로에게 말하는 것처럼 말입니다.
- 이를 바탕으로 "OLTP에 대한 정규화"라는 개념조차도 반대로 수행하면 "OLAP에 대한 정규화 해제"라는 개념이 모순됩니다. 물리 법칙이 한 컴퓨터에서는 어떻게 작동하지만 다른 컴퓨터에서는 반대로 작동 할 수 있습니까? 마음이 흔들린다. 모든 컴퓨터에서 똑같은 방식으로 작업하는 것은 불가능합니다.
질문?
비정규 화 및 집계는 데이터웨어 하우스에서 성능을 달성하는 데 사용되는 두 가지 주요 전략입니다. 읽기 성능이 향상되지 않는다고 제안하는 것은 어리석은 일입니다! 분명히 내가 여기서 뭔가를 이해하지 못했음에 틀림 없다?
집계 : 10 억 개의 구매를 보유한 테이블을 고려하십시오. 구매의 합계가있는 한 행이있는 테이블과 대조하십시오. 이제 어느 것이 더 빠릅니까? 10 억 행 표에서 합계 (금액)를 선택하거나 1 행 표에서 금액을 선택 하시겠습니까? 물론 어리석은 예이지만 집계의 원리를 아주 명확하게 보여줍니다. 왜 더 빠릅니까? 우리가 사용하는 마법의 모델 / 하드웨어 / 소프트웨어 / 종교에 관계없이 100 바이트를 읽는 것이 100 기가 바이트를 읽는 것보다 빠르기 때문입니다. 그렇게 간단합니다.
비정규 화 : 소매 데이터웨어 하우스의 일반적인 제품 차원에는 엄청난 양의 열이 있습니다. 일부 열은 "이름"또는 "색상"과 같은 쉬운 항목이지만 계층 구조와 같은 복잡한 항목도 있습니다. 여러 계층 구조 (제품 범위 (5 개 수준), 의도 된 구매자 (3 개 수준), 원자재 (8 개 수준), 생산 방식 (8 개 수준) 및 평균 리드 타임 (연초부터)) , weight / packaging measure etcetera etcetera. 저는 5 개의 서로 다른 소스 시스템에서 ~ 70 개의 테이블로 구성된 200 개 이상의 열이있는 제품 차원 테이블을 유지했습니다. 정규화 된 모델에 대한 쿼리가 있는지 여부에 대해 토론하는 것은 어리석은 일입니다 (아래).
select product_id
from table1
join table2 on(keys)
join (select average(..)
from one_billion_row_table
where lastyear = ...) on(keys)
join ...table70
where function_with_fuzzy_matching(table1.cola, table37.colb) > 0.7
and exists(select ... from )
and not exists(select ...)
and table20.version_id = (select max(v_id from product_ver where ...)
and average_price between 10 and 20
and product_range = 'High-Profile'
... 비정규 화 된 모델의 동등한 쿼리보다 빠릅니다.
select product_id
from product_denormalized
where average_price between 10 and 20
and product_range = 'High-Profile';
왜? 부분적으로 집계 된 시나리오와 동일한 이유입니다. 또한 쿼리가 "복잡"하기 때문입니다. 그들은 너무 역 겨울 정도로 복잡해서 옵티 마이저 (그리고 지금은 오라클 세부 사항으로갑니다)가 혼란스러워지고 실행 계획을 망치게됩니다. 쿼리가 소량의 데이터를 처리하는 경우 최적이 아닌 실행 계획은 그렇게 큰 문제가 아닐 수 있습니다. 그러나 곧 우리가 큰 테이블에 참여하기 시작으로는 중요데이터베이스가 실행 계획을 올바르게 가져옵니다. 단일 구문 키를 사용하여 하나의 테이블에서 데이터를 비정규 화 한 후 (이 진행중인 화재에 더 많은 연료를 추가하지 않는 이유) 필터는 미리 조리 된 열에서 단순한 범위 / 동등 필터가됩니다. 데이터를 새 열에 복제하면 열에 대한 통계를 수집 할 수있어 최적화 프로그램이 선택성을 추정하는 데 도움이되며 적절한 실행 계획을 제공 할 수 있습니다 (음, ...).
분명히 비정규 화 및 집계를 사용하면 스키마 변경을 수용하기가 더 어려워지고 이는 나쁜 일입니다. 반면에 그들은 읽기 성능을 제공하는데 이는 좋은 것입니다.
그렇다면 읽기 성능을 달성하기 위해 데이터베이스를 비정규 화해야합니까? 안돼! 그것은 당신의 시스템에 너무 많은 복잡성을 추가하여 당신이 배달하기 전에 얼마나 많은 방법으로 당신을 망칠 것인지 끝이 없습니다. 그만한 가치가 있습니까? 예, 때로는 특정 성능 요구 사항을 충족하기 위해이를 수행해야합니다.
업데이트 1
PerformanceDBA : 1 행이 하루에 10 억 번 업데이트됩니다.
이는 (거의) 실시간 요구 사항을 의미합니다 (이는 완전히 다른 기술 요구 사항 집합을 생성 함). 대부분은 아니지만 많은 데이터웨어 하우스에 이러한 요구 사항이 없습니다. 집계가 작동하는 이유를 명확히하기 위해 비현실적인 집계 예제를 선택했습니다. 롤업 전략도 설명하고 싶지 않았습니다. :)
또한 데이터웨어 하우스의 일반 사용자와 기본 OLTP 시스템의 일반 사용자의 요구를 대조해야합니다. 어떤 요인이 운송 비용을 유발하는지 이해하고자하는 사용자는 오늘날 데이터의 50 %가 누락되거나 10 대의 트럭이 폭발하여 운전자를 죽인 경우에도 걱정할 수 없습니다. 2 년 분량의 데이터에 대한 분석을 수행하면 그가 원하는대로 최신 정보를 얻었더라도 동일한 결론에 도달 할 수 있습니다.
이것을 그 트럭의 운전자 (생존 한 사람들)의 필요와 대조하십시오. 어리석은 집계 프로세스가 완료되어야하기 때문에 일부 환승 지점에서 5 시간을 기다릴 수 없습니다. 두 개의 개별 데이터 사본이 있으면 두 가지 요구가 모두 해결됩니다.
운영 시스템 및보고 시스템에 대해 동일한 데이터 세트를 공유하는 또 다른 주요 장애물은 릴리스주기, Q & A, 배포, SLA 및 보유하고있는 사항이 매우 다르다는 것입니다. 다시 말하지만, 두 개의 개별 복사본이 있으면이 작업을 더 쉽게 처리 할 수 있습니다.
"OLAP"이란 의사 결정 지원에 사용되는 주제 지향 관계형 / SQL 데이터베이스 인 AKA a Data Warehouse를 의미한다는 것을 이해합니다.
일반 형식 (일반적으로 5/6 일반 형식)은 일반적으로 데이터웨어 하우스에 가장 적합한 모델입니다. 데이터웨어 하우스를 정규화하는 이유는 다른 데이터베이스와 똑같습니다. 중복성을 줄이고 잠재적 인 업데이트 이상을 방지합니다. 기본 제공 편향을 방지하므로 스키마 변경 및 새로운 요구 사항을 지원하는 가장 쉬운 방법입니다. 데이터웨어 하우스에서 Normal Form을 사용하면 데이터로드 프로세스를 간단하고 일관되게 유지하는 데 도움이됩니다.
"전통적인"비정규 화 접근법은 없습니다. 좋은 데이터웨어 하우스는 항상 정규화되었습니다.
읽기 성능을 위해 데이터베이스를 비정규 화하지 않아야합니까?
좋아요, 여기에 "마일리지가 다를 수 있습니다", "다양합니다", "모든 작업에 적절한 도구 사용", "한 가지 크기가 모든 것에 맞지 않음"답변과 함께 "만약 문제를 해결하지 마십시오." Ai n't Broken "던진 :
비정규 화는 특정 상황에서 쿼리 성능을 향상시키는 한 가지 방법입니다. 다른 상황에서는 실제로 성능이 저하 될 수 있습니다 (디스크 사용 증가로 인해). 확실히 업데이트를 더 어렵게 만듭니다.
성능 문제가 발생한 경우에만 고려해야합니다 (정규화의 이점을 제공하고 복잡성을 도입하기 때문입니다).
비정규 화의 단점은 업데이트되지 않거나 배치 작업에서만 업데이트되는 데이터 (예 : OLTP 데이터가 아님)의 문제가 적다는 것입니다.
비정규 화가 해결해야하는 성능 문제를 해결하고 덜 침습적 인 기술 (인덱스, 캐시 또는 더 큰 서버 구입)으로 해결되지 않는 경우에는 그렇게해야합니다.
먼저 내 의견, 그리고 몇 가지 분석
의견
비정규 화라는 단어의 일반적인 사용에는 종종 정규 형식을 깨는 것뿐만 아니라 시스템에 삽입, 업데이트 및 삭제 종속성을 도입하는 것이 포함되기 때문에 데이터 읽기를 돕는 것으로 인식됩니다.
이것은 엄밀히 말하면 거짓입니다 .이 질문 / 답변을 참조하십시오 . 엄격한 의미에서 비정규 화는 1NF-6NF에서 정규 형식을 깨는 것을 의미하며, 기타 삽입, 업데이트 및 삭제 종속성은 Principle of Orthogonal Design으로 처리 됩니다.
따라서 사람들은 공간 대 시간 절충 원칙을 취하고 중복성이라는 용어를 기억하고 (비정규 화와 관련이 있지만 여전히 같지 않음) 이점이 있어야한다고 결론을 내립니다. 이것은 잘못된 의미이지만 잘못된 의미는 그 반대의 결론을 내릴 수 없습니다.
정상적인 형태를 깨고 수 실제로 속도를 일부 데이터 검색 (아래 분석 내용을)하지만, 동시에 또한 것이다 원칙적으로 :
- 특정 유형의 쿼리 만 선호하고 다른 모든 액세스 경로의 속도를 늦 춥니 다.
- 시스템의 복잡성 증가 (데이터베이스 자체의 유지 관리뿐만 아니라 데이터를 소비하는 애플리케이션의 복잡성 증가)
- 데이터베이스의 의미 명확성을 난독 화하고 약화시킵니다.
- 데이터베이스 시스템의 요점은 문제 공간을 나타내는 중앙 데이터가 사실을 기록하는 데 편파적이지 않기 때문에 요구 사항이 변경 될 때 실제로 독립적 인 시스템 부분 (데이터 및 애플리케이션)을 재 설계 할 필요가 없다는 것입니다. 이러한 인위적인 종속성을 최소화해야합니다. 하나의 쿼리 속도를 높이기위한 오늘날의 '중요한'요구 사항은 매우 자주 중요하지 않습니다.
분석
그래서 때때로 정상적인 형태를 깨는 것이 검색에 도움이 될 수 있다고 주장했습니다 . 몇 가지 논쟁을 할 시간
1) 1NF 깨기
6NF에 재무 기록이 있다고 가정합니다. 이러한 데이터베이스에서 매월 각 계정의 잔액에 대한 보고서를 확실히 얻을 수 있습니다.
이러한 보고서를 계산해야하는 쿼리가 n 개의 레코드 를 거쳐야한다고 가정하면 테이블을 만들 수 있습니다.
account_balances(month, report)
각 계정에 대한 XML 구조화 된 잔액을 보유합니다. 이로 인해 1NF가 중단되지만 (나중에 참고 참조) 하나의 특정 쿼리를 최소 I / O 로 실행할 수 있습니다 .
동시에, 재무 기록의 삽입, 업데이트 또는 삭제를 통해 어느 달 에든 업데이트 할 수 있다고 가정하면 시스템에서 업데이트 쿼리의 성능은 각 업데이트에 대한 n의 일부 기능에 비례하여 시간에 따라 느려질 수 있습니다 . (위의 경우는 원칙을 보여줍니다. 실제로는 더 나은 옵션이 있고 최소 I / O를 얻는 이점은 실제로 데이터를 자주 업데이트하는 현실적인 시스템의 경우 대상 쿼리에 따라 성능이 저하 될 수 있습니다. 실제 워크로드 유형. 원하는 경우 더 자세히 설명 할 수 있음)
참고 : 이것은 실제로 사소한 예이며 1NF의 정의라는 한 가지 문제가 있습니다. 위의 모델이 1NF를 중단한다는 가정은 '속성 값이 해당 도메인에서 정확히 하나의 값을 포함하는 '요구 사항에 따른 것 입니다.
이렇게하면 속성 보고서의 도메인이 가능한 모든 보고서의 집합이며 모든 보고서에서 정확히 하나의 값이 있고 1NF가 깨지지 않는다고 주장 할 수 있습니다 (단어를 저장해도 1NF가 깨지지 않는다는 주장과 유사). letters모델 어딘가에 관계가있을 수 있습니다 .)
반면에이 테이블을 모델링하는 훨씬 더 좋은 방법이 있습니다. 이는 더 광범위한 쿼리 (예 : 1 년의 모든 달에 대한 단일 계정의 잔액 검색)에 더 유용합니다. 이 경우이 필드가 1NF가 아니라고 말함으로써 개선을 정당화 할 수 있습니다.
어쨌든 사람들이 NF를 깨면 성능이 향상 될 수 있다고 주장하는 이유를 설명합니다.
2) 3NF 깨기
3NF에서 테이블 가정
CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`member_id` int(10) unsigned NOT NULL,
`status` tinyint(3) unsigned NOT NULL,
`amount` decimal(10,2) NOT NULL,
`opening` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `member_id` (`member_id`),
CONSTRAINT `t_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `m` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
CREATE TABLE `m` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
샘플 데이터 포함 (t에서 1M 행, m에서 100k)
개선하려는 일반적인 쿼리를 가정합니다.
mysql> select sql_no_cache m.name, count(*)
from t join m on t.member_id = m.id
where t.id between 100000 and 500000 group by m.name;
+-------+----------+
| name | count(*) |
+-------+----------+
| omega | 11 |
| test | 8 |
| test3 | 399982 |
+-------+----------+
3 rows in set (1.08 sec)
name3NF를 깨는 테이블 m 으로 속성을 이동하라는 제안을 찾을 수 있습니다 (FD : member_id-> 이름이 있고 member_id는 t의 키가 아님).
후
alter table t add column varchar(255);
update t inner join m on t.member_id = t.id set t.name = m.name;
달리는
mysql> select sql_no_cache name, count(*)
from t where id
between 100000 and 500000
group by name;
+-------+----------+
| name | count(*) |
+-------+----------+
| omega | 11 |
| test | 8 |
| test3 | 399982 |
+-------+----------+
3 rows in set (0.41 sec)
참고 : 위의 쿼리 실행 시간은 절반 으로 줄었지만
- 테이블은 5NF / 6NF에 없었습니다.
- the test was done with no_sql_cache so most cache mechanisms were avoided (and in real situations they play a role in system's performance)
- space consumption is increased by approx 9x size of the column name x 100k rows
- there should be triggers on t to keep the integrity of data, which would significantly slow down all updates to name and add additional checks that inserts in t would need to go through
- probably better results could be achieved by dropping surrogate keys and switching to natural keys, and/or indexing, or redesigning to higher NFs
Normalising is the proper way in the long run. But you don't always have an option to redesign company's ERP (which is for example already only mostly 3NF) - sometimes you must achieve certain task within given resources. Of course doing this is only short term 'solution'.
Bottom line
I think that the most pertinent answer to your question is that you will find the industry and education using the term 'denormalisation' in
- strict sense, for breaking NFs
- loosely, for introducing any insertion, update and deletion dependencies (original Codd's quote comments on normalisation saying: 'undesirable(!) insertion, update and deletion dependencies', see some details here)
So, under strict definition, the aggregation (summary tables) are not considered denormalisation and they can help a lot in terms of performance (as will any cache, which is not perceived as denormalisation).
The loose usage encompasses both breaking normal forms and the principle of orthogonal design, as said before.
Another thing that might shed some light is that there is a very important difference between the logical model and the physical model.
For example indexes store redundant data, but no one considers them denormalization, not even people who use the term loosely and there are two (connected) reasons for this
- they are not part of the logical model
- they are transparent and guaranteed not to break integrity of your model
If you fail to properly model your logical model you will end up with inconsistent database - wrong types of relationships between your entities (inability to represent problem space), conflicting facts (ability to loose information) and you should employ whatever methods you can to get a correct logical model, it is a foundation for all applications that will be built on top of it.
Normalisation, orthogonal and clear semantics of your predicates, well defined attributes, correctly identified functional dependencies all play a factor in avoiding pitfalls.
When it comes to physical implementation things get more relaxed in a sense that ok, materialised computed column that is dependent on non key might be breaking 3NF, but if there are mechanisms that guarantee consistency it is allowed in physical model in the same way as indexes are allowed, but you have to very carefully justify it because usually normalising will yield same or better improvements across the board and will have no or less negative impact and will keep the design clear (which reduces the application development and maintenance costs) resulting in savings that you can easily spend on upgrading hardware to improve the speed even more then what is achieved with breaking NFs.
The two most popular methodologies for building a data warehouse (DW) seem to be Bill Inmon's and Ralph Kimball's.
Inmon's methodology uses normalized approach, while Kimball's uses dimensional modelling -- de-normalized star schema.
Both are well documented down to small details and both have many successful implementations. Both present a "wide, well-paved road" to a DW destination.
I can not comment on the 6NF approach nor on Anchor Modelling because I have never seen nor participated in a DW project using that methodology. When it comes to implementations, I like to travel down well tested paths -- but, that's just me.
So, to summarize, should DW be normalized or de-normalized? Depends on the methodology you pick -- simply pick one and stick to it, at least till the end of the project.
EDIT - An Example
At the place I currently work for, we had a legacy report which has been running since ever on the production server. Not a plain report, but a collection of 30 sub-reports emailed to everybody and his ant every day.
Recently, we implemented a DW. With two report servers and bunch of reports in place, I was hoping that we can forget about the legacy thing. But not, legacy is legacy, we always had it, so we want it, need it, can't live without it, etc.
The thing is that the mess-up of a python script and SQL took eight hours (yes, e-i-g-h-t hours) to run every single day. Needless to say, the database and the application were built over years by few batches of developers -- so, not exactly your 5NF.
It was time to re-create the legacy thing from the DW. Ok, to keep it short it's done and it takes 3 minutes (t-h-r-e-e minutes) to produce it, six seconds per sub-report. And I was in the hurry to deliver, so was not even optimizing all the queries. This is factor of 8 * 60 / 3 = 160 times faster -- not to mention benefits of removing an eight hour job from a production server. I think I can still shave of a minute or so, but right now no one cares.
As a point of interest, I have used Kimball's method (dimensional modelling) for the DW and everything used in this story is open-source.
This is what all this (data-warehouse) is supposed to be about, I think. Does it even matter which methodology (normalized or de-normalized) was used?
EDIT 2
As a point of interest, Bill Inmon has a nicely written paper on his website -- A Tale of Two Architectures.
The problem with the word "denormalized" is that it doesn't specify what direction to go in. It's about like trying to get to San Francisco from Chicago by driving away from New York.
A star schema or a snowflake schema is certainly not normalized. And it certainly performs better than a normalized schema in certain usage patterns. But there are cases of denormalization where the designer wasn't following any discipline at all, but just composing tables by intuition. Sometimes those efforts don't pan out.
In short, don't just denormalize. Do follow a different design discipline if you are confident of its benefits, and even if it doesn't agree with normalized design. But don't use denormalization as an excuse for haphazard design.
The short answer is don't fix a performance problem you have not got!
As for time based tables the generally accepted pardigm is to have valid_from and valid_to dates in every row. This is still basically 3NF as it only changes the semantics from "this is the one and only verision of this entity" to "this is the one and only version of this entity at this time "
Simplification:
An OLTP database should be normalised (as far as makes sense).
OLAP 데이터웨어 하우스는 조인을 최소화하기 위해 Fact 및 Dimension 테이블로 비정규 화되어야합니다.
'Programing' 카테고리의 다른 글
| 파이썬에서 나누기 연산자를 사용할 때 십진수 값을 어떻게 얻습니까? (0) | 2020.12.03 |
|---|---|
| iPhone 용 Xcode에서 Core Data 모델을 생성 한 후 클래스를 생성하는 방법 (0) | 2020.12.03 |
| ApplicationContext 자체를 주입하는 방법 (0) | 2020.12.03 |
| 배열에 요소가 있는지 확인하십시오. (0) | 2020.12.03 |
| asyncio는 실제로 어떻게 작동합니까? (0) | 2020.12.03 |