DBMS/MySQL

MySQL 인덱스와 DDL 깊이 파보기

우 선 2025. 3. 25. 17:07

1. 인덱스의 세계: 데이터베이스 성능 최적화의 핵심

1.1 인덱스의 기본 개념

데이터베이스에서 인덱스는 마치 책의 목차와 같다. 방대한 데이터 속에서 원하는 정보를 빠르게 찾아갈 수 있게 해주는 magic key다. 인덱스가 없다면, 데이터베이스는 매번 전체 데이터를 처음부터 끝까지 스캔해야 한다.

1.2 B-Tree 인덱스: 효율적인 데이터 탐색의 기술

B-Tree 인덱스의 작동 원리

-- 기본 인덱스 생성
CREATE INDEX index_username ON users(username);

-- 사용자 이름으로 정렬된 검색
SELECT username 
FROM users 
ORDER BY username ASC;

B-Tree 인덱스의 특징:

  • 자가 균형 트리 구조
  • 대량의 데이터에서 O(log n) 검색 시간 복잡도
  • 데이터 삽입, 삭제, 검색에 최적화

성능 비교

  • 인덱스 미사용: 0.044초
  • 인덱스 사용: 0.006초 → 검색 속도가 약 7배 이상 빨라짐!

1.3 복합 인덱스: 다중 조건 검색 최적화

-- 복합 인덱스 생성
CREATE INDEX idx_username_password 
ON users(username, password_hash);

-- 복합 인덱스를 활용한 검색
SELECT username, password_hash 
FROM users 
ORDER BY username ASC, password_hash DESC;

복합 인덱스의 장점:

  • 여러 열에 대한 동시 검색 최적화
  • 단일 인덱스보다 더 효율적인 쿼리 처리
  • 메모리 사용량은 약간 증가하지만 성능 향상 효과 탁월

2. 다양한 인덱스 유형

2.1 FULLTEXT 인덱스: 텍스트 검색의 혁명

-- FULLTEXT 인덱스 생성
CREATE FULLTEXT INDEX idx_course_description 
ON courses(description);

-- 텍스트 기반 검색
SELECT title, description 
FROM courses 
WHERE MATCH(description) AGAINST("프로그래밍");

FULLTEXT 인덱스의 특징:

  • 대규모 텍스트 데이터 검색에 최적화
  • 단어 및 구문 기반 검색 지원
  • 자연어 검색 알고리즘 적용

2.2 HASH 인덱스: 초고속 정확 검색

-- HASH 인덱스 생성
CREATE INDEX idx_payment_amount 
ON payments(amount) USING HASH;

-- 빠른 동등 조건 검색
SELECT * FROM payments 
WHERE amount = 417.47;

HASH 인덱스의 특징:

  • O(1) 검색 속도
  • 정확한 값 일치 검색에 최적
  • 메모리 기반 인덱스로 빠른 접근

3. EXPLAIN: 쿼리 실행 계획 분석의 깊이

3.1 EXPLAIN 명령어의 중요 필드

EXPLAIN SELECT * FROM users;

주요 분석 필드:

  • select_type: 쿼리 유형 (단순 조회, 서브쿼리 등)
  • table: 참조 테이블
  • type: 조인 및 검색 방식
  • possible_keys: 사용 가능한 인덱스 목록
  • key: 실제 사용된 인덱스
  • rows: 예상 검색 행 수
  • Extra: 추가 실행 정보

4. DDL: 데이터베이스 구조 정의의 예술

4.1 DDL의 기본 명령어

CREATE: 구조 생성

-- 데이터베이스 생성
CREATE DATABASE learning_management;

-- 테이블 생성
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE
);

ALTER: 구조 변경

-- 테이블에 새 컬럼 추가
ALTER TABLE students 
ADD COLUMN phone_number VARCHAR(20);

-- 컬럼 타입 변경
ALTER TABLE students 
MODIFY COLUMN name VARCHAR(150);

DROP: 구조 삭제

-- 테이블 삭제
DROP TABLE students;

-- 데이터베이스 삭제
DROP DATABASE learning_management;

TRUNCATE: 데이터 초기화

-- 테이블의 모든 데이터 삭제
TRUNCATE TABLE students;

4.2 제약 조건과 DDL

 
CREATE TABLE courses (
    id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) CHECK (price >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  • PRIMARY KEY: 고유 식별자
  • NOT NULL: 필수 입력
  • UNIQUE: 중복 방지
  • CHECK: 데이터 유효성 검사
  • DEFAULT: 기본값 설정

5. 인덱스와 DDL 활용 전략

인덱스 생성 시 고려사항

  1. 자주 검색되는 컬럼 선택
  2. 카디널리티(고유값 비율) 높은 컬럼 인덱싱
  3. 과도한 인덱스는 성능 저하 유발
  4. 정기적인 인덱스 최적화 필요

DDL 설계 원칙

  1. 명확하고 일관된 명명 규칙
  2. 데이터 무결성 보장
  3. 확장성을 고려한 설계
  4. 성능 최적화

결론: 끊임없는 학습의 여정

데이터베이스는 단순한 저장소가 아니다. 그것은 살아있는 유기체와 같다. 끊임없이 변화하고, 최적화되며, 우리의 데이터를 더 스마트하게 관리할 수 있게 해준다.

인덱스와 DDL은 이 유기체의 신경계와 골격 시스템과 같다. 올바르게 설계하고 관리할 때, 우리의 데이터베이스는 놀라운 성능과 유연성을 발휘한다.

계속해서 배우고, 실험하고, 성장하자.

'DBMS > MySQL' 카테고리의 다른 글

데이터베이스 설계  (0) 2025.03.25
MySQL 트랜잭션: 데이터 무결성을 지키는 마법  (0) 2025.03.25
MySQL 고급 기능 마스터하기  (0) 2025.03.25
MySQL 기본 문법 완전 정복  (0) 2025.03.25