[DB설계] 19. 색인 생성 및 삭제

표준 SQL 및 데이터베이스 입문

Mar 1. 2025

지금까지 논리적 설계에 대해 설명했습니다. 논리적으로 올바른 상태가 되어 있는 것은 매우 중요하며, 데이터베이스를 사용할 떄의 기본 전제조건입니다. 그러나, 실제 업무에서 사용하려면 성능도 중요합니다.

아무리 내용이 좋아도 속도가 느리면 쓸모가 없습니다. SQL의 성능을 향상시키는 방법은 여러가지가 있는데 그 중에서도 중요한 것은 인덱스(index)입니다.

키와 인덱스는 다른가?

관계형 데이터베이스에서는 키와 인덱스가 전혀 다른 개념입니다. 키는 데이터의 논리적 구조를 표현하는 것이고, 인덱스는 데이터 접근속도를 빠르게 하기 위해 사용되는 것입니다.

인덱스(index, 색인)는 데이터 접근을 빠르게 하기 위해 사용됩니다. 예를 들어, WHERE나 ORDER BY절에서 자주 사용되는 열에 인덱스를 생성해두면 처리속도가 향상됩니다. 참고로 PRIMARY KEY나 UNIQUE가 지정된 열에는 중복여부를 확인하기 위해 자동으로 인덱스가 생성되는 DBMS도 있어서 별도로 인덱스를 생성할 필요는 없습니다.

⑴ 색인생성

키는 아니지만 필터링이나 정렬에 자주 사용되는 열들이 있습니다. 학원의 경우, 학생을 캠퍼스별로 정렬하는 경우가 많을 수 있습니다. 그런 경우에는 인덱스를 만들어두면 좋을 수 있습니다.

표준 SQL에서는 인덱스에 대한 규정은 없지만, 인덱스 생성은 보통, 'CREATE INDEX 인덱스명 ON 테이블명(열1)'과 같이 수행됩니다. 복합키처럼 여러 열로 인덱스를 생성하고자 할 경우에는 '(열1, 열2, 열3)'와 같이 쉼표(,)로 구분하여 나열합니다. 다음은 '상품마스터' 테이블에 대해 상품명과 상품코드를 결합한 인덱스를 생성하는 예제입니다.

MySQL, MariaDB의 경우, ALTER문을 사용하여 인덱스를 생성할 수도 있습니다. 나중에 설명하겠지만, 인덱스 삭제도 ALTER문으로 수행되므로, ALTER문으로 통일하는 것도 좋습니다.

⑵ 인덱스 삭제

인덱스를 만들어 보았지만, 결국 사용하지 않은 인덱스는 삭제해도 괜찮을까? 괜찮습니다. 데이터 자체에는 영향을 주지 않기 때문입니다. 인덱스 삭제는 MySQL, MariaDB의 경우 ALTER문을 사용하여, 'ALTER TABLE 테이블명 DROP INDEX 인덱스명'으로 수행하고, PostgreSQL의 경우, 'DROP INDEX 인덱스명'으로 수행합니다.

⑶ 인덱스 및 업데이트 속도

인덱스는 몇개씩 만들 수 있을까? 극단적으로 말하면, 모든 열에 인덱스를 생성할 수도 있나?

가능은 합니다. 단 그냥 만들기만 하면 되는 것은 아니라서, 오히려 속도가 저하되는 경우도 있습니다.

인덱스가 처리속도를 향상시키는 것은 주로 쿼리(SELECT)처리에 해당됩니다. 하지만 업데이터 처리(INSERT, UPDATE, DELETE)의 경우에는 테이블 업데이트와 같이 인덱스도 업데이트해야 하기 때문에 속도가 저하됩니다. 따라서, 인덱스는 이름, 주소등과 같이 업데이트빈도가 낮으면서 검색이나 정렬에 자주 사용되는 열에 지정하는 것이 기본 원칙입니다.

인덱스의 추가나 삭제는 데이터 자체에 영향을 주지 않으므로, 대량의 데이터를 추가 및 삭제할 때나 인덱스가 설정된 열을 대량으로 변경할 때는, 한번 인덱스를 삭제한 후, 진행하는 것이 좋을 때도 있습니다. 참고로 인덱스의 유무가 업데이트 처리에 어느 정도 영향을 주는지는 DBMS와 그 동작환경, 데이터의 양이나 내용에 따라 달라집니다. 즉, 인덱스가 바로 속도 향상만을 의미하는 것은 아니라는 것입니다. 다시 이야기하지만, 어느정도 영향이 있는지는 DBMS의 성능, 동작환경, 데이터의 양이나 내용에 따라 달라지므로 여러가지 테스트를 해보기 바랍니다.