[MySql] 인덱싱 개념 정리

DB Indexing 기초 개념

1. 인덱스의 개념

인덱스는 테이블의 특정 컬럼 값을 빠르게 찾을 수 있도록 돕는 자료구조다.
전체 테이블을 순회하는 풀 테이블 스캔 대신, B-Tree나 해시 테이블 구조를 통해 원하는 행(row)에 효율적으로 접근할 수 있다.

2. 인덱스의 특징

• 조회 성능 향상
  • WHERE, JOIN, ORDER BY, GROUP BY 등에 걸린 조건을 만족하는 레코드를 빠르게 찾는다.
• 쓰기 비용 증가
  • INSERT/UPDATE/DELETE 시 인덱스도 함께 갱신해야 하므로 쓰기 성능이 떨어질 수 있다.
• 추가 디스크 공간 사용
  • 인덱스는 추가적인 저장 공간을 필요로 하므로, 과도하게 생성하면 디스크 자원을 낭비한다.
• 통계 기반 최적화
  • MySQL 옵티마이저는 테이블 및 인덱스 통계 정보를 활용해 최적의 실행 계획을 선택한다.

3. 다중 컬럼 인덱스

• Composite index
  • 두 개 이상의 컬럼을 묶어서 만든 인덱스.
  • 예: (col1, col2, col3)
• Left-most 원칙
  • 인덱스의 가장 왼쪽 컬럼부터 순차적으로 사용해야 인덱스를 활용할 수 있다.
  • (col1, col2, col3) 인덱스는 WHERE col1 = … AND col2 = … 또는 WHERE col1 = … 쿼리에만 적용된다.
• 순서 중요성
  • 필터링 우선순위가 높은 컬럼을 앞쪽에 배치해야 효율이 높다.
• Covering index
  • 인덱스에 쿼리 대상 컬럼을 전부 포함하면 디스크 접근 없이 인덱스만으로 결과를 반환할 수 있다.

4. 설계 방법

1. 자주 사용하는 컬럼에만 인덱스 추가
   • 조회 조건, 정렬, 조인에 자주 쓰이는 컬럼 위주로.
2. 컬럼 선택도(selectivity) 고려
   • 고유 값이 많거나 중복값이 적은 컬럼이 성능 개선 효과가 크다.
3. Composite index 활용
   • 여러 조건이 동시에 자주 쓰이면 composite index를 만든다.
4. 카디널리티 확인
   • SHOW INDEX, ANALYZE TABLE로 통계 정보를 점검한다.
5. 쿼리 패턴 중심 설계
   • 운영 환경의 쿼리 로그를 분석해 빈도가 높은 쿼리를 우선 최적화한다.

5. 문법

• 인덱스 생성

  CREATE INDEX idx_users_email
    ON users (email);

• Composite index 생성

  CREATE INDEX idx_orders_user_date
    ON orders (user_id, order_date);

• ALTER TABLE로 추가

  ALTER TABLE posts
    ADD INDEX idx_posts_title (title);

• 인덱스 확인

  SHOW INDEX FROM table_name;

• 쿼리 실행 계획 확인

  EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

6. 주의 사항

• 인덱스 과다 생성 금지
  • 지나치게 많은 인덱스는 쓰기 성능 저하 및 디스크 낭비를 초래한다.
• 함수나 연산 적용 금지
  • WHERE UPPER(name) = 'JOHN'처럼 컬럼에 함수를 적용하면 인덱스를 활용하지 못한다.
• NULL 처리 주의
  • NULL 비율이 높은 컬럼은 인덱스 효율이 낮다.
• 통계 최신화
  • 데이터가 크게 변동된 후에는 ANALYZE TABLE로 통계를 갱신해야 한다.
• 인덱스 스킵 문제
  • Composite index의 중간 컬럼만 사용하는 쿼리는 인덱스를 활용하지 못할 수 있다.

7. 정리

인덱스는 MySQL 성능 최적화의 핵심 도구로, 조회 속도를 크게 향상시킨다.
하지만 무분별한 인덱스 생성은 쓰기 성능과 저장 공간에 악영향을 미친다.
실제 쿼리 패턴을 기반으로 필요한 컬럼에만 적절히 인덱스를 설계하고, 정기적으로 실행 계획과 통계를 점검하는 습관을 들이면 데이터베이스 운영의 효율을 극대화할 수 있다.