SQL Index
SQL INDEX: 데이터 조회 성능을 향상시키는 인덱스
SQL INDEX는 테이블의 데이터를 빠르게 검색하기 위해 사용되는 데이터베이스 객체입니다. 인덱스는 테이블의 특정 열이나 열의 조합에 대해 생성되며, 데이터를 효율적으로 검색할 수 있도록 지원합니다. 인덱스는 책의 색인과 비슷한 역할을 하며, 테이블에서 데이터를 조회할 때 탐색 속도를 크게 향상시킵니다.
1. INDEX의 주요 특징
- 데이터 검색 속도 향상: 인덱스를 사용하면 테이블의 데이터를 검색할 때 속도를 크게 향상시킬 수 있습니다.
- 데이터 정렬: 인덱스는 기본적으로 데이터를 정렬된 방식으로 저장하기 때문에 빠른 검색이 가능합니다.
- 추가적인 저장 공간 필요: 인덱스를 생성하면 데이터베이스는 추가적으로 저장 공간을 사용합니다.
- 데이터 수정 시 성능 저하: 인덱스는 데이터 조회에는 유리하지만, INSERT, UPDATE, DELETE와 같은 데이터 변경 작업에는 추가적인 작업이 필요하므로 성능이 저하될 수 있습니다.
2. INDEX 기본 문법
CREATE INDEX 인덱스_이름
ON 테이블_이름 (열_이름1, [열_이름2, ...]);
- 인덱스_이름: 생성할 인덱스의 이름.
- 테이블_이름: 인덱스를 생성할 테이블의 이름.
- 열_이름: 인덱스를 적용할 열의 이름. 여러 열에 대해 인덱스를 만들 수 있습니다.
3. INDEX 예제
3.1 단일 열에 인덱스 생성
예제: employees 테이블의 employee_id에 인덱스 생성
CREATE INDEX idx_employee_id
ON employees (employee_id);
이 명령은 employees 테이블의 employee_id 열에 대해 idx_employee_id라는 이름의 인덱스를 생성합니다. 이를 통해 employee_id를 기준으로 데이터를 빠르게 검색할 수 있습니다.
3.2 다중 열에 인덱스 생성 (복합 인덱스)
예제: employees 테이블의 department_id와 hire_date에 인덱스 생성
CREATE INDEX idx_dept_hire_date
ON employees (department_id, hire_date);
이 명령은 department_id와 hire_date 열을 결합한 복합 인덱스를 생성합니다. 이는 department_id와 hire_date를 동시에 조건으로 사용할 때 효율적인 검색을 제공합니다.
4. 인덱스의 유형
4.1 UNIQUE INDEX
UNIQUE INDEX는 열의 값이 중복되지 않도록 보장하는 인덱스입니다. 이는 UNIQUE 제약 조건과 유사하며, 테이블의 열 또는 열의 조합에서 중복된 값을 허용하지 않습니다.
문법
CREATE UNIQUE INDEX 인덱스_이름
ON 테이블_이름 (열_이름);
예제: employees 테이블의 email 열에 UNIQUE 인덱스 생성
CREATE UNIQUE INDEX idx_unique_email
ON employees (email);
이 명령은 email 열에 대해 UNIQUE 인덱스를 생성하여 중복된 값이 입력되지 않도록 보장합니다.
4.2 PRIMARY KEY 인덱스
테이블에 PRIMARY KEY를 설정하면, 해당 열에는 자동으로 인덱스가 생성됩니다. 기본 키는 테이블의 각 행을 고유하게 식별하므로, 자동으로 인덱스가 생성되어 빠른 검색을 지원합니다.
4.3 FULLTEXT INDEX
FULLTEXT INDEX는 문자열 열에 대해 전체 텍스트 검색을 수행할 수 있도록 지원하는 인덱스입니다. 이는 주로 긴 텍스트 데이터에서 특정 단어나 구문을 빠르게 검색하는 데 유용합니다. MySQL에서 자주 사용됩니다.
문법
CREATE FULLTEXT INDEX 인덱스_이름
ON 테이블_이름 (열_이름);
예제: articles 테이블의 content 열에 FULLTEXT 인덱스 생성
CREATE FULLTEXT INDEX idx_fulltext_content
ON articles (content);
이 명령은 articles 테이블의 content 열에서 전체 텍스트 검색을 수행할 수 있도록 FULLTEXT 인덱스를 생성합니다.
4.4 CLUSTERED INDEX (클러스터드 인덱스)
CLUSTERED INDEX는 테이블의 데이터 자체를 정렬하는 방식의 인덱스입니다. PRIMARY KEY가 있는 테이블에는 자동으로 클러스터드 인덱스가 생성되며, 데이터가 물리적으로 인덱스 순서에 맞게 저장됩니다.
4.5 NON-CLUSTERED INDEX (논클러스터드 인덱스)
NON-CLUSTERED INDEX는 데이터와 인덱스가 별도로 저장되며, 인덱스는 데이터가 저장된 위치를 참조하는 방식입니다. NON-CLUSTERED INDEX는 주로 데이터를 검색할 때 빠른 성능을 제공합니다.
5. 인덱스 제거 (DROP INDEX)
인덱스가 필요 없거나 성능을 저하시킬 경우, 인덱스를 제거할 수 있습니다.
문법
DROP INDEX 인덱스_이름
ON 테이블_이름;
예제: employees 테이블의 idx_employee_id 인덱스 제거
DROP INDEX idx_employee_id
ON employees;
이 명령은 employees 테이블에서 idx_employee_id 인덱스를 제거합니다.
6. 인덱스 사용 시 주의 사항
- 조회 속도는 빨라지지만 삽입/수정/삭제는 느려짐: 인덱스는 데이터를 검색할 때는 유리하지만, 데이터가 추가되거나 수정, 삭제될 때는 인덱스도 함께 수정되므로 성능이 저하될 수 있습니다.
- 추가 저장 공간 필요: 인덱스는 추가적인 저장 공간을 차지하므로, 테이블이 크면 인덱스도 많은 디스크 공간을 사용합니다.
- 적절한 인덱스 사용: 테이블에 너무 많은 인덱스를 생성하면, 조회 성능은 향상될 수 있지만 데이터 삽입/수정 시 성능이 저하될 수 있으므로 필요한 인덱스만 생성하는 것이 중요합니다.
7. 실전 예제
예제 1: 단일 열에 인덱스 생성
CREATE INDEX idx_employee_id
ON employees (employee_id);
이 명령은 employees 테이블에서 employee_id 열에 대한 인덱스를 생성하여, employee_id를 기준으로 데이터를 빠르게 검색할 수 있게 만듭니다.
예제 2: 복합 인덱스 생성
CREATE INDEX idx_dept_hire_date
ON employees (department_id, hire_date);
이 명령은 department_id와 hire_date 열에 대한 복합 인덱스를 생성하여 두 열을 동시에 조건으로 검색할 때 성능을 향상시킵니다.
예제 3: UNIQUE 인덱스 생성
CREATE UNIQUE INDEX idx_unique_email
ON employees (email);
이 명령은 email 열에 UNIQUE 인덱스를 생성하여 중복된 이메일 값이 입력되지 않도록 보장합니다.
8. 요약
- SQL 인덱스는 테이블의 데이터를 빠르게 검색할 수 있도록 돕는 객체입니다.
- 인덱스를 통해 검색 속도를 향상시키지만, 데이터 삽입/수정/삭제 작업에서는 성능이 저하될 수 있습니다.
- 단일 열 또는 복합 열에 인덱스를 생성할 수 있으며, UNIQUE, FULLTEXT, CLUSTERED, NON-CLUSTERED 등 다양한 유형의 인덱스가 존재합니다.
- 인덱스를 과도하게 사용하면 성능에 부정적인 영향을 미칠 수 있으므로, 적절한 인덱스 사용이 중요합니다.
SQL 인덱스는 검색 성능 최적화에 매우 중요한 역할을 하며, 데이터베이스의 크기가 커질수록 그 효과가 더욱 두드러집니다.