MySQL 인덱스 유형 분석 및 적용 사례

인덱스의 개념 및 특성

인덱스는 데이터베이스 테이블 내의 행을 효율적으로 찾기 위해 구성되는 특별한 자료구조입니다. InnoDB 스토리지 엔진의 경우, 인덱스 파일은 실제 데이터가 저장된 테이블스페이스 내에 통합되어 관리됩니다. 이러한 메커니즘은 쿼리 수행 시 전체 테이블 스캔을 피하고 원하는 데이터를 빠르게 추출하도록 돕습니다.

인덱스 도입 시 고려해야 할 장단점은 다음과 같습니다:

  • 장점: 검색 속도의 비약적인 향상으로 인한 쿼리 응답 시간 감소.
  • 단점: 추가적인 디스크 사용량 증가. 또한 INSERT, UPDATE, DELETE 연산 시 기존 데이터 변경과 함께 인덱스 트리 재구성 과정이 필요하므로 쓰기 성능 저하가 발생할 수 있습니다.
  • 일관성 유지: 테이블 데이터가 변경될 때마다 동기화된 인덱스 업데이트가 필수적이므로 과도한 인덱스는 유지보수 비용을 높입니다.

기본 인덱스 (Normal Index)

가장 단순한 형태의 인덱스로, 특정 열의 중복 허용 여부를 제한하지 않으며 오직 조회 속도만 개선하는 용도로 사용됩니다. 키 열에 'MUL' 표시가 나타나는 것이 특징입니다.

생성 예시

-- 기본 인덱스를 포함한 테이블 정의
CREATE TABLE member_profile (
    user_code INT,
    nickname VARCHAR(50),
    INDEX idx_nickname (nickname)
);

-- 확인
DESC member_profile; 
-- 결과: nickname 열의 Key 컬럼에 MUL 표시됨

지정된 이름 없이 인덱스를 생성하면 해당 필드명이 자동으로 인덱스명으로 사용되기도 합니다.

-- 명시적 이름 부여 방식
CREATE TABLE staff_list (
    emp_id INT,
    emp_name VARCHAR(50),
    KEY staff_name_key (emp_name)
);

기존 테이블에 추가

이미 생성된 테이블에도 ALTER 문을 통해 인덱스를 달 수 있습니다.

ALTER TABLE member_profile ADD INDEX idx_user_code (user_code);
ALTER TABLE staff_list ADD KEY name_index (emp_name);

삭제 방법

불필요해진 인덱스는 인덱스 명칭을 지정하여 제거합니다.

-- 인덱스 삭제 후 상태 확인
ALTER TABLE member_profile DROP INDEX idx_nickname;
ALTER TABLE member_profile DROP KEY idx_user_code;

참고로, 인덱스 타입을 정확히 확인하려면 SHOW INDEX FROM [테이블명] 명령을 사용하여 Index_type 이 'BTREE'인지 확인할 수 있습니다.

유일 인덱스 (Unique Index)

해당 열의 모든 값이 테이블 내에서 고유해야 함을 보장합니다. NULL 값은 중복해서 허용할 수 있으나 데이터 값 자체는 유일해야 합니다. 데이터가 이미 채워진 상태에서 추가 시 충돌 오류가 발생할 수 있으므로 주의해야 합니다. DESC 결과에서 Key 에 'UNI' 가 표시됩니다.

정의 및 조작

-- 테이블 생성 시 UNIQUE 제약 설정
CREATE TABLE unique_account (
    acc_no INT,
    email_addr VARCHAR(100),
    UNIQUE (acc_no)
);

-- 기존 테이블에 추가 (중복 데이터 존재 시 실패 가능)
ALTER TABLE unique_account ADD UNIQUE uq_email (email_addr);

-- 삭제 시에는 일반 인덱스와 동일한 문법 사용
ALTER TABLE unique_account DROP KEY email_addr;

기본 키 인덱스 (Primary Key)

테이블 내에서 행을 구분 짓는 가장 강력한 식별자로, 하나의 테이블당 하나만 존재하며 NULL 을 허용하지 않습니다. 유일한 값을 갖는다는 점에서 유일 인덱스와 유사하지만, NOT NULL 제약이 항상 적용됩니다. DESC 출력에서 Key 열이 'PRI' 로 표시됩니다.

사용 예시

-- CREATE 문 내에서 직접 지정
CREATE TABLE product_catalog (
    sku_code INT,
    product_desc VARCHAR(200),
    PRIMARY KEY (sku_code)
);

-- 단축 표기 방식 (열 수준 지정)
CREATE TABLE order_main (
    order_id INT PRIMARY KEY,
    total_price DECIMAL(10, 2)
);

-- 사후 추가 (데이터 정합성 확인 필요)
ALTER TABLE order_main ADD PRIMARY KEY (order_id);
-- 제거 시 인덱스명은 생략
ALTER TABLE order_main DROP PRIMARY KEY;

복합 인덱스 (Composite Index)

두 개 이상의 열을 조합하여 하나의 인덱스를 만드는 방식을 말합니다. 인덱스 생성 순서가 중요한데, 보통 자주 검색되는 순서대로 열을 나열합니다. 생성 및 관리 문법은 단일 인덱스와 동일하나 컬럼 목록을 쉼표로 구분하여 작성합니다.

-- 복합 기본키 생성
CREATE TABLE session_logs (
    log_time DATETIME,
    session_hash CHAR(32),
    user_agent VARCHAR(200),
    PRIMARY KEY (log_time, session_hash)
);

-- ALTER 를 통한 복합 인덱스 추가
CREATE TABLE audit_records (rec_id INT, action_type VARCHAR(20), target_id INT);
ALTER TABLE audit_records ADD INDEX idx_audit_compound (action_type, target_id);

-- 삭제 처리
ALTER TABLE session_logs DROP PRIMARY KEY;

전체 텍스트 인덱스 (Full-text Index)

자연어 처리를 위한 검색 기능으로 활용되며, 텍스트 내 키워드 빈도와 중요도를 분석하여 결과를 리턴합니다. MyISAM 에서 주로 사용되다가 InnoDB 에서도 5.6 버전 이후 지원되기 시작했습니다. 다만 문자열 타입 (CHAR, VARCHAR, TEXT) 에만 적용 가능합니다. 한글 지원은 5.7.6 버전부터 ngram 파서를 통해 공식적으로 구현되었습니다.

-- FULLTEXT 인덱스 설정
CREATE TABLE blog_articles (
    post_id INT AUTO_INCREMENT,
    title VARCHAR(255),
    content LONGTEXT,
    FULLTEXT INDEX ft_content (content)
);

-- ALTER 문 활용
ALTER TABLE blog_articles ADD FULLTEXT KEY idx_title_search (title);

-- 삭제
ALTER TABLE blog_articles DROP KEY title;

-- 확인 시 SHOW INDEX WHERE index_type='FULLTEXT' 로 필터링 추천

DESC 결과에서는 일반적인 인덱스처럼 MUL 로 보일 수 있지만, 실제 SHOW INDEX 명령어를 사용하면 Type 이 FULLTEXT 로 정확히 노출됩니다.

인덱스 설계 가이드라인

무조건 많은 인덱스를 생성한다고 성능이 좋아지는 것은 아닙니다. 효율적인 설계를 위해 다음 사항을 준수해야 합니다.

  1. 소량의 데이터나 분포도가 낮은 열 (예: 성별, 국가 코드 등) 은 인덱스 효과 미미함.
  2. SELECT 문의 WHERE 절, ORDER BY, GROUP BY 등에 자주 사용되는 열에 우선 배치.
  3. 고유성 제어가 필요한 열에는 유니크 또는 프라이머리 키 활용.
  4. 쓰기 작업이 빈번한 테이블에서는 인덱스 개수를 최소화하여 트랜잭션 지연 방지.

태그: MySQL SQL Database Optimization Index InnoDB

6월 22일 20:08에 게시됨

인기 태그

©2026 괴물 클럽