[MySQL] 인덱스 생성, 삭제, 변경, 조회 방법

이 글에서는 MySQL에서 인덱스를 생성, 삭제, 조회하는 방법과 인덱스를 관리할 때 주의해야 할 사항에 대해 알아보겠습니다.

 

인덱스란 무엇인가?

인덱스는 테이블의 데이터를 보다 빠르게 검색할 수 있도록 도와주는 자료 구조입니다. 인덱스를 추가하면, MySQL은 데이터를 조회할 때 전체 테이블을 스캔하는 대신 인덱스를 먼저 참조하여 효율적으로 데이터를 찾습니다. 마치 책의 마지막에 있는 색인(index)과 비슷한 개념입니다.

하지만, 인덱스는 검색 성능을 크게 향상시키지만, 항상 긍정적인 영향만 미치는 것은 아닙니다. 인덱스를 너무 많이 추가하면 삽입(insert), 업데이트(update), 삭제(delete) 성능에 영향을 미칠 수 있으며, 인덱스를 관리하는 데 추가적인 스토리지도 필요합니다.

 

 

인덱스 생성하는 방법 (CREATE INDEX)

기본 사용법

MySQL에서 인덱스를 생성하려면 CREATE INDEX 문을 사용합니다. 일반적으로 인덱스는 테이블의 한 개 이상의 컬럼에 대해 생성할 수 있으며, 가장 많이 조회되는 컬럼이나 자주 검색되는 조건에 따라 인덱스를 추가하면 좋습니다.

 

CREATE INDEX [인덱스명] ON [테이블이름] (컬럼명)

 

EX)

CREATE INDEX idx_customer_name ON customers (name);

위 명령어는 customers 테이블의 name 컬럼에 대해 idx_customer_name이라는 이름의 인덱스를 생성합니다. 이제 MySQL은 name 컬럼에서 값을 검색할 때, 인덱스를 사용하여 더 빠르게 조회할 수 있습니다.

 

 

다중 컬럼 인덱스

CREATE INDEX [인덱스명] ON [테이블이름] (컬럼명, 컬럼명)

인덱스는 하나 이상의 컬럼에 걸쳐 생성될 수 있으며, 이를 복합 인덱스(Composite Index)라고 합니다. 복합 인덱스는 여러 컬럼을 동시에 조건으로 검색할 때 성능 향상에 유리합니다.

EX)

CREATE INDEX idx_customer_name_email ON customers (name, email);

이 예시는 name과 email 컬럼에 대해 복합 인덱스를 생성하는 방법입니다.

 

 

유니크 인덱스

유니크 인덱스는 인덱스 내의 값이 중복되지 않도록 보장합니다. 이 경우 중복된 데이터를 허용하지 않으며, 유니크 제약 조건을 적용할 수 있습니다.

CREATE UNIQUE INDEX [인덱스명] ON [테이블이름] (컬럼명)

EX)

CREATE UNIQUE INDEX idx_unique_email ON customers (email);

이 명령어는 customers 테이블의 email 컬럼에 대해 중복을 허용하지 않는 유니크 인덱스를 생성합니다.

 

인덱스 삭제하는 방법 (DROP INDEX)

더 이상 필요하지 않거나 성능에 부정적인 영향을 미치는 인덱스는 DROP INDEX 문을 사용해 삭제할 수 있습니다.

 

DROP INDEX [인덱스명] ON [테이블이름]

EX)

DROP INDEX idx_customer_name ON customers;

이 명령어는 customers 테이블의 idx_customer_name이라는 인덱스를 삭제합니다. 

 

 

테이블에서 인덱스 확인하는 방법 (SHOW INDEX)

테이블에 존재하는 인덱스를 확인하려면 SHOW INDEX 명령어를 사용합니다. 이 명령어는 해당 테이블에 적용된 모든 인덱스를 보여줍니다.

 

SHOW INDEX FROM [테이블이름]

EX)

SHOW INDEX FROM customers;

 

이 명령어는 customers 테이블의 모든 인덱스 정보를 출력합니다. 결과에는 인덱스의 이름, 컬럼 이름, 인덱스 종류 등이 포함됩니다.

 

 

인덱스를 변경하는 방법

MySQL에서는 인덱스를 직접적으로 수정하는 명령어는 제공되지 않습니다. 따라서, 인덱스를 수정하려면 기존 인덱스를 삭제한 후, 새로 생성하는 방법을 사용해야 합니다.

인덱스 수정하는 단계

1. 기존 인덱스 삭제: 먼저 수정하고자 하는 인덱스를 삭제합니다.
2. 새 인덱스 생성: 필요한 변경 사항을 반영하여 새로운 인덱스를 생성합니다.

이 방법을 통해 인덱스를 사실상 "수정"할 수 있습니다.

 

기존에 name 컬럼만 인덱스로 사용 중이었고, 이를 name과 phone_number 컬럼을 포함한 복합 인덱스로 수정하고자 한다면 다음과 같은 절차를 따릅니다.

 

기존 인덱스 삭제

DROP INDEX idx_customer_name ON customers;

새로운 인덱스 생성

CREATE INDEX idx_customer_name_phone ON customers (name, phone_number);

 

 

인덱스를 관리할 때의 주의점

인덱스 남발을 피하세요

너무 많은 인덱스는 검색 성능을 향상시키지만, 데이터 삽입, 삭제, 업데이트 시 성능 저하를 일으킬 수 있습니다. 필요하지 않은 인덱스는 오히려 성능에 부정적인 영향을 미칠 수 있습니다.

자주 사용하는 컬럼에만 인덱스를 추가하세요

인덱스는 주로 자주 검색되거나 조건으로 사용되는 컬럼에만 추가하는 것이 좋습니다. 사용 빈도가 적은 컬럼에 인덱스를 추가하면 오히려 성능 저하가 발생할 수 있습니다.

복합 인덱스의 순서를 신경 쓰세요

복합 인덱스는 포함된 컬럼의 순서에 따라 성능에 큰 차이가 발생할 수 있습니다. 자주 조회되는 컬럼을 먼저 배치하는 것이 좋습니다.

주기적인 인덱스 관리가 필요합니다

데이터가 점차 증가하면서 인덱스 성능이 저하될 수 있습니다. 정기적으로 SHOW INDEX 명령어로 인덱스를 확인하고, 필요 없는 인덱스는 삭제하거나 관리하는 것이 좋습니다.