인덱스(INDEX)

그zi운아이 2023. 10. 5. 14:54

인덱스

MySQL의 인덱스는 데이터베이스에서 데이터를 빠르게 검색할 수 있도록 도와주는 자료 구조입니다. 인덱스가 없다면 데이터베이스는 테이블의 모든 레코드를 순회해야 하기 때문에 검색이 느려질 수 있다.

책의 맨처음에 나오는 목차처럼 해당 컬럼이 어디에 있는지 저장하여 해당부분만 검색할 수 있게 하여 검색속도를 향상시킨다.

인덱스의 장점

테이블 전체를 스캔하는 것보다 빠른 데이터 검색이 가능하다.
WHERE,JOIN,GROUP BY,ORDER BY등의 SQL 구문에서 성능이 향상된다
인덱스를 사용하면 데이터 정렬 및 그룹화 작업을 빠르게 수행할 수 있다.
클러스터링 인덱스를 사용하면 실제 테이블에 접근하지 않고도 원하는 데이터를 추출할 수 있다

인덱스의 단점

인덱스가 있으면 데이터를 삽입,수정,삭제할 때 인덱스도 함께 갱신해야 하므로 성능이 저하될 수 있다.
인덱스는 디스크 공간을 추가로 사용한다. 매우 큰 테이블의 경우 이는 큰 단점이 될 수 있다.
인덱스 구조를 유지하고 최적화하는 작업이 추가로 필요하다.
잘못된 인덱스 구조는 오히려 성능을 저하시킬 수 있다.

인덱스 종류

클러스터링 인덱스

클러스터링 인덱스는 테이블의 레코드를 인덱스 키의 순서에 따라 물리적으로 재배열한다. 때문에 하나의 테이블에는 하나의 클러스터링 인덱스만 있을 수 있다. MYSQl의 InnoDB 스토리지 엔진에서는 기본 킥 자동으로 클러스터링 인덱스로 설정된다. 기본 키가 없을시 UNIQUE 제약조건이 있는 첫 번째 칼람을 클러스터링 인덱스로 사용한다.

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

특징

인덱스 키에 의해 레코드의 물리적 위치가 결정된다.
정렬이 빠르게 수행됩니다.
테이블에 하나의 클러스터링 인덱스만 존재할 수 있습니다.

보조 인덱스

보조 인덱스는 인덱스 데이터와 실제 테이블 데이터가 분리되어 저장된다. 인덱스에는 키와 키에 대한 레코드의 포인터가 저장된다.

-- CREATE INDEX 문을 사용한 경우
CREATE INDEX idx_last_name ON Employees(last_name);

-- ALTER TABLE 문을 사용한 경우
ALTER TABLE Employees ADD INDEX idx_first_name (first_name);

특징

인덱스 키와 실제 데이터는 별도로 저장된다.
하나의 테이블에 여러 개의 보조 인덱스를 가질 수 있다.
인덱스 검색 후, 식제 레코드를 찾기 위한 추가 작업이 필요하다.

인덱스 생성/변경/삭제

인덱스 생성

테이블 생성 시 인덱스 추가
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column1),
    INDEX index_name (column2),
    UNIQUE INDEX unique_index_name (column3)
);

기존 테이블에 인덱스 추가 
CREATE INDEX index_name ON table_name (column);

복합 인덱스 
CREATE INDEX index_name ON table_name (column1, column2);

인덱스 변경

인덱스 이름 변경: MySQL에서는 인덱스 이름을 직접 변경하는 기능이 없으므로, 인덱스를 삭제한 다음 새 이름으로 다시 생성해야 한다.

ALTER TABLE table_name DROP INDEX old_index_name;
ALTER TABLE table_name ADD INDEX new_index_name (new_column);

인덱스 삭제

ALTER TABLE table_name DROP INDEX index_name;

정리

인덱스는 열 단위에 생성된다
WHERE절에 사용되는 열에 인덱스를 만들어야 한다.
WHERE절에 자주 사용되야 가치 있다.
중요도가 높은 열은 인덱스를 만들어도 별 효과가 없다.
외래키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다
JOIN에 자주 사용되는 열에는 인덱스를 생성하는 것이 좋다
INSERT/UPDATE/DELETE 가 얼마나 자주 일어나는지 고려해야 한다
클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
클ㄹ스터형 인덱스가 테이블에 없는 것이 좋은 경우도 있다.