SQL/데이터베이스

관계형 데이터베이스, SQL, 인덱스, 정규화, Normalization, 이상현상, 카디널리티(Cardinality), 역정규화, 반정규화

sangwoo_rhie 2023. 8. 3. 20:40

 

인덱스

데이터 검색속도 향상을 위해 키값, 주소 쌍으로 구성된 별도의 데이터 객체

b- tree 구조를 이용하여 인덱스의 데이터 구조를 생성

 

카디널리티 (Cardinality)

 

테이블을 구성하는 요소 중 얼마나 중복성이 낮은지에 대한 성질

카디널리티가 높을수록 중복성이 낮다.

 

Clustered Index   vs   Non-Clustered Index

  • Clustered Index
    • 인덱스에 해당하는 물리적 데이터가 함께 정렬되는 인덱스
    • 테이블에 오로지 한개의 Clustered Index 만 존재 가능
    • 복합 인덱스는 불가능하고 단일 인덱스로만 생성 가능
    • primary key를 생성하면 해당 컬럼이 clustered Index가 됨
    • 장/단점
      • 장점: 테이블에서 카디널리티가 가장 높은 컬럼으로 선택하므로 속도가 빠르다
      • 단점: 수정/삭제/삽입이 일어날때 재구성이 일어나므로 CUD에 대해서는 부하가 발생
  • Non-Clustered Index
    • 인덱스의 키 값만 정렬될 뿐 물리적 데이터의 정렬은 없는 인덱스
    • 테이블에 여러개의 Non-Clustered Index 생성가능
    • 단일, 복합인덱스 생성 가능
    • 장/단점
      • 장점: CUD가 일어날때 Clustered Index에 비해 부하가 적다
      • 단점: Clustered Index에 비해 조회 속도가 느리다

CREATE TABLE 만들때, 인덱스를 같이 만드는 법

CREATE TABLE if not exists store (
    id int(11) unsigned auto_increment primary key,
    seller_id int(11) not null comment '매장을 소유한 판매자 ID',
    food_category_id int(11) not null comment '음식 카테고리 ID',
    name varchar(64) not null comment '매장명',
    address varchar(128) not null comment '매장 주소',
    description varchar(256) null comment '매장소개',
    created_at datetime default CURRENT_TIMESTAMP not null comment '생성시점',
    updated_at datetime default CURRENT_TIMESTAMP not null comment '최종업데이트시점',
    KEY `store_name_idx` (`name`)
) COMMENT='매장정보';

 

테이블을 만든 이후, 인덱스를 추가하는 법

#단일 인덱스
CREATE INDEX store_name_idx ON store (name);

#복합 인덱스
CREATE INDEX seller_id_store_name_idx ON store (seller_id, name);

정규화 (Normalization)

이상현상 (Anormaly)

  • 데이터의 중복으로 인해 삽입, 삭제, 갱신 시 발생하는 비 합리적인 현상
  • 발생 원인
    • 정규화 미 충족
    • 여러 특징을 하나의 릴레이션으로만 표현 (즉, 하나의 테이블에 다 때려넣을 때)
  • 종류
    • 삽입 이상 (삽입 시 불필요한 정보도 삽입)
    • 갱신 이상 (중복 값중 하나만 갱신되고 나머지는 갱신 안됨)
    • 삭제 이상 (특정 튜플 삭제할 경우 원하지 않는 정보도 삭제)

정규화의 정의

  • 데이터 중복 제거하여 이상현상 방지하고 일관성 정확성 유지 위한 무손실 분해 과정 !
  • 단계
    • 1차 정규형 (1NF)
    • 2차 정규형 (2NF)
    • 3차 정규형 (3NF)
    • 3.5차 정규형 (BCNF)
    • 4차 정규형 (4NF)
    • 5차 정규형 (5NF)

 

역정규화 / 반정규화 (Denormalization)

역정규화(반정규화) 정의

  • 정규화로 분해된 릴레이션을 통합하고 중복을 허용하여 성능을 향상시키는 기법
  • 정규화는 무조건 좋은 것일까? (No No)
  • 정규화의 강도가 세질수록 성능이 이슈가 생길 수 있음
    • 정규화가 높아질수록 무결성은 높아지나
    • 정규화가 높아질수록 성능은 떨어짐

역정규화(반정규화)의 주요 기법

  • 테이블 레벨
    • 테이블 병합: 중복을 어느정도 허용하여 Join을 줄이기 위해 테이블을 합친다
    • 테이블 추가
      • 통계 테이블: 통계성 정보의 조회가 많을 경우 통계 데이터만 따로 요약하여 제공
      • 이력 테이블: 이력성 데이터를 위해 전담 테이블 생성
  • 컬럼 레벨
    • 중복 컬럼 추가: 성능을 우선적으로 고려하기 위해 양쪽의 테이블에 같은 데이터를 유지
    • 계산 컬럼 추가: 누적계산결과를 들고 있을 컬럼을 하나 추가 한다
    • 이력 컬럼 추가: 변경 이력, 최신 변경시점 등 이력을 관리하기 위한 컬럼을 추가 
      • 관계 레벨
        • 중복 관계 추가: 외래키를 다수 추가하여 Join의 횟수를 줄이기 위한 기법

 

https://teamsparta.notion.site/230803_-623945fe13d84e5190d560daadff8b3d

 

230803_인덱스/정규화

인덱스 (Index)

teamsparta.notion.site