데이터베이스에서 인덱스(Index)는 방대한 양의 데이터 중에서 필요한 정보를 신속하게 찾아낼 수 있도록 돕는 정렬된 데이터 구조입니다. 책의 맨 뒤에 있는 '찾아보기'와 유사한 역할을 하며, 특정 알고리즘에 따라 데이터를 참조하는 구조를 통해 검색 성능을 비약적으로 향상시킵니다.

일반적으로 인덱스 파일은 크기가 크기 때문에 메모리에 모두 상주할 수 없으며, 디스크에 파일 형태로 저장됩니다. 인덱스는 성능 최적화를 위한 가장 강력한 도구 중 하나입니다.

1. 인덱스의 장점과 단점

1.1 장점

• 데이터 검색 속도가 빨라져 전체적인 I/O 비용이 감소합니다.
• 인덱스 컬럼을 기준으로 데이터가 이미 정렬되어 있으므로, ORDER BY나 GROUP BY 연산 시 CPU 소모를 줄일 수 있습니다.

1.2 단점

• 인덱스를 유지하기 위한 별도의 저장 공간이 필요합니다.
• INSERT, UPDATE, DELETE와 같은 쓰기 작업(DML) 시 데이터뿐만 아니라 인덱스 정보도 갱신해야 하므로 수정 속도가 저하될 수 있습니다.

2. 스토리지 엔진별 인덱스 구조

MySQL의 인덱스는 서버 계층이 아닌 스토리지 엔진 계층에서 구현됩니다. 따라서 엔진 종류에 따라 지원하는 인덱스 타입이 다를 수 있습니다.

3. B-Tree와 B+Tree 구조

MySQL에서 가장 널리 사용되는 구조는 B+Tree입니다. 이는 B-Tree의 변형으로, 데이터 탐색과 범위 스캔에 최적화되어 있습니다.

3.1 B+Tree의 특징

• 모든 실제 데이터(또는 데이터의 주소)는 리프 노드(Leaf Node)에만 저장됩니다.
• 리프 노드들은 서로 연결 리스트(Linked List) 형태로 이어져 있어, 범위 검색(Range Scan) 시 매우 효율적입니다.
• 비단말 노드(Non-leaf Node)는 데이터 탐색을 위한 가이드 역할인 키 값만 가집니다.

이러한 구조 덕분에 WHERE price > 5000과 같은 범위 쿼리 시, 첫 번째 데이터를 찾은 후 연결된 포인터를 따라가기만 하면 되므로 성능이 뛰어납니다.

4. 인덱스 관리 구문

인덱스를 생성, 조회, 삭제하는 기본적인 SQL 문법입니다. 예제 코드는 사용자 정보를 담는 테이블을 기준으로 작성되었습니다.

-- 테스트 환경 구축
CREATE DATABASE service_db;
USE service_db;

CREATE TABLE account_info (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(50) NOT NULL,
    email_address VARCHAR(100),
    department_code INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 1. 인덱스 생성
-- 형식: CREATE [UNIQUE|FULLTEXT] INDEX 인덱스명 ON 테이블명(컬럼명);
CREATE INDEX idx_user_name ON account_info(user_name);

-- 2. 인덱스 조회
SHOW INDEX FROM account_info;

-- 3. 인덱스 삭제
DROP INDEX idx_user_name ON account_info;

-- 4. ALTER 문을 활용한 인덱스 추가
-- 유니크 인덱스 추가 (중복 방지)
ALTER TABLE account_info ADD UNIQUE INDEX idx_unique_email(email_address);

-- 일반 인덱스 추가
ALTER TABLE account_info ADD INDEX idx_dept_code(department_code);

5. 효율적인 인덱스 설계 원칙

무분별한 인덱스 생성은 오히려 성능을 저하시킬 수 있습니다. 다음 원칙을 고려하여 설계해야 합니다.

• 데이터 중복도가 낮은 컬럼: 값의 고유성(Cardinality)이 높을수록 인덱스 효율이 극대화됩니다. 성별보다는 이메일이나 주민번호 같은 컬럼이 적합합니다.
• 빈번한 조회 조건: WHERE 절에서 자주 사용되는 컬럼을 인덱스로 지정합니다.
• 최좌측 접두사(Leftmost Prefix) 법칙: 복합 인덱스를 구성할 때, 인덱스의 순서가 (A, B, C)라면 A가 조건절에 포함되어야 인덱스가 작동합니다.
• 과도한 인덱스 지양: 인덱스가 너무 많으면 쓰기 성능이 떨어지고 MySQL 옵티마이저가 최적의 실행 계획을 선택하는 데 혼란을 줄 수 있습니다.
• 짧은 인덱스: 컬럼의 길이가 짧을수록 한 페이지에 더 많은 인덱스 정보를 담을 수 있어 I/O 효율이 좋아집니다.

-- 복합 인덱스 예시
CREATE INDEX idx_name_email_dept ON account_info(user_name, email_address, department_code);

-- 다음의 경우 인덱스 활용 가능:
-- 1. user_name 단독 조회
-- 2. user_name + email_address 조회
-- 3. user_name + email_address + department_code 전체 조회

-- 하지만 email_address 단독 조회 시에는 위 복합 인덱스를 효율적으로 사용하지 못할 수 있습니다.