[MySQL] 예제로 이해하는 인덱싱(Indexing)과 정규와, 비정규화, 반정규화

인덱싱 (Indexing)

• 특정 컬럼의 값을 빠르게 찾을 수 있도록 도와주는 데이터 구조

 

인덱스 종류

인덱스 종류	설명
Primary Key	테이블의 고유 식별자. 자동으로 UNIQUE + NOT NULL 인덱스 생성
Unique Key	중복 허용 안 됨. 이메일, 주민번호 등에 사용
Index (또는 key)	일반 인덱스. 중복 가능
FullText Index	전문 검색용 인덱스 (MyISAM, InnoDB 5.6 이상에서 지원)
Spatial Index	공간 데이터(GIS) 전용 인덱스

 

 

인덱스 내부 구조

• MySQL의 기존 스토리지 엔진인 InnoDB는 B+Tree 구조로 인덱스를 저장한다.
  • Clustered Index: Primary Key 기준으로 실제 데이터도 정렬됨
  • Secondary Index: 실제 데이터가 아닌 PK를 참조 (row lookup 필요)

 

Clustered Index

• 테이블 자체의 물리적 저장 순서를 결정하는 인덱스 -> 테이블의 데이터 자체가 인덱스 구조에 따라 정렬되어 저장된다.
• 테이블에 하나만 존재 가능
• 일반적으로 Primary Key가 자동으로 Clustered Index가 됨

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

 

 

Secondary Index

• 데이터와 분리된 별도의 인덱스 구조
• 여러 개 생성 가능
• secondary 인덱스를 통해 먼저 위치를 찾고, 실제 데이터를 Clustered Index를 통해 다시 읽음

CREATE INDEX idx_age ON users(age);

// 키는 age, 값은 해당 row의 id (즉, clustered index의 주소)
[ 22 ] → id=1 (users의 id)
[ 25 ] → id=3
[ 30 ] → id=2
이런식으로 저장됨.

• 즉, Primary key를 만들면 그게 Clustered Index가 되고, 나머지는 모두 Secondary Index다.

SELECT * FROM users WHERE age = 25;

 

• 위 쿼리가 실행되면
  1. idx_age(보조 인덱스)에서 25라는 나이를 찾고, id=3을 발견
  2. id=3으로 Clustered Index를 타고 가서 실제 row 데이터를 읽음

 

인덱스 설계 전략

• 언제 인덱스를 걸까?
  • WHERE, JOIN, ORDER BY, GROUP BY에 자주 사용되는 컬럼
  • FK, 유니크 값 검색
    • fk의 경우 자식 테이블에서 부모 테이블의 값을 찾기 때문에, 참조 대상 칼럼에 인덱스가 있어야 효율적.
  • 큰 테이블의 범위 검색
    • 예: SELECT * FROM users WHERE age BETWEEN 30 AND 40;
      • age에 인덱스가 있다면, 30 이상인 위치로 바로 Jump하고, 거기서부터 40까지 선형 탐색만 함.
      • b-tree (균형 이진 트리) 구조라 이진 탐색으로 빠르게 찾음.
• 과도한 인덱싱은 오히려 성능 저하
  • 쓰기 성능(Insert, Update, Delete) 저하
  • 인덱스 저장공간 증가

 

예제 1: 블로그 시스템 DB 설계 (1:N 관계)

-- 사용자
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 게시글
CREATE TABLE posts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user_created_at (user_id, created_at)
);

-- 댓글
CREATE TABLE comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  content TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_post_created_at (post_id, created_at)
);

• user <-> posts는 1:N, posts <-> comments도 1:N 관계
• posts.user_id, comments.post_id에 인덱스를 추가해 작성자별, 게시글별 조회 속도 향상
• idx_user_created_at: 유저별 글을 시간순으로 정렬할 때 사용

SELECT * 
FROM posts
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

 

• 예를 들어, 위처럼 "user 42가 작성한 최근 글 10 개 가져오기" 요청할 때,
  • 인덱스가 없다면 mysql은 comments 테이블 전체에서 user_id = 42를 풀스캔(full table scan) 하거나, user_id만 인덱스에 있어도 filesort를 수행하게 된다. 
  • ORDER BY created_at 정렬 작업은 메모리 또는 디스크에서 수동으로 진행되기 때문에 느림.
  • 그런데 INDEX (user_id, created_at) 설정을 하게 되면, mysql은 인덱스 자체가 정렬된 자료구조(B+ Tree)이기 때문에 (user_id, created_at) 순서로 인덱스가 잡혀 있으면, 정렬 없이도 빠르게 원하는 결과를 얻을 수 있다.
  • MySQL은 복합 인덱스를 왼쪽부터 순차적으로 이용하기 때문에, 인덱스에 나열된 컬럼 순서가 쿼리 성능에 결정적인 영향을 준다.

 

 

예제 2: 팔로우 시스템 (자기참조 관계)

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE follows (
  follower_id BIGINT UNSIGNED NOT NULL,
  followee_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, followee_id),
  FOREIGN KEY (follower_id) REFERENCES users(id),
  FOREIGN KEY (followee_id) REFERENCES users(id),
  INDEX idx_followee (followee_id)
);

• user <-> user 참조하는 형태
• follower_id, followee_id를 각각 인덱싱 (팔로잉 / 팔로우 조회)
• PRIMARY KEY (follower_id, followee_id): 두 사람의 관계는 중복되지 않도록 보장 + 빠른 조회

 

정규화(Normalization), 비정규화(Denormalization), 반정규화(Semi-denormalization)

• 데이터 저장의 구조와 효율성 사이의 균형을 조정하는 개념

 

정규화(Normalization)

• 정규화는 데이터 중복을 줄이고, 데이터 무결성(정합성)을 보장하기 위해 테이블을 분해하는 과정
• 목적
  • 중복 제거
  • 데이터 무결성 유지
  • ANOMALY 방지 (삽입/삭제/갱신 시 문제 최소화)
    • 삽입 이상 (Insertion Anomaly)
      • 문제 상황: Physics 과목을 추가하려고 할 때, 수강생이 없다면 course_name과 professor만 입력해야 하는데, student_id가 Not Null 설정되어 있어서 입력 불가.
      • 문제 해결: 과목 정보를 별도 테이블로 분리 (정규화)
    • 삭제 이상 (Deletion Anomaly)
      • 문제 상황: 학생 Charlie가 수강 취소하면, English 과목 자체가 DB에서 사라진다. (과목이 학생에게 의존된 상황)
      • 문제 해결: 과목과 학생 정보를 별도 테이블로 분리해서 보존.
    • 갱신 이상 (Update Anomaly)
      • 문제 상황: 수학 교수님이 Dr. Kim -> Dr. Park으로 변경되었는데, 해당 과목을 수강 중인 모든 학생 레코드에 반영해야 함. 일부만 수정되면 데이터 정합성 깨짐 (누군가의 데이터는 과거 교수님 이름으로 기록됨)
      • 문제 해결: 교수 정보는 과목 테이블에 넣고, 참조로 관리갱신 이상 (Update Anomaly)

 

 

 

예: 학생이 여러 과목을 수강하는 데이터

 

 

 

 

 

 

 

 

1. 정규화 (Normalization)

 

 

2. 비정규화 (Denormalization)

• 정규화를 거쳐 나뉜 테이블을 성능 개선이나 실용적인 이유로 다시 합치는 과정
• 목적
  • 조인 비용 감소
  • 읽기 성능 감소
  • 쿼리 구조 단순화
• 앞서 정규화된 구조에서 (학생 이름, 과목 이름) 쌍으로 조회하는 경우가 많은 경우, 쿼리가 느릴 수 있기 때문에 아래처럼 두 테이블을 합친 비정규화 테이블을 만들 수 있다.
  • 중복이 생기지만 조회는 빨라짐.

 

 

3. 반정규화 (Semi-denormalization)

• 완전히 정규화된 구조와 완전히 비정규화된 구조 중간 정도로 타협하는 설계이다.
• 정규화는 유지하되, 자주 쓰이는 값을 중복 컬럼 또는 캐싱 컬럼으로 일부 테이블에 둠.
• 예: 게시글의 댓글 수를 coments 테이블에서 매번 COUNT 하지 않고 posts 테이블에 comment_count 컬럼을 둬서 캐싱
  • 새로운 comments 값이 생길 때마다, posts의 comment_count 값을 업데이트