[MySQL] 인덱스 생성에 CREATE INDEX를 사용해야하는 이유

해당 글은 MySQL에서 인덱스 추가 시에 사용되는 대표적인 2가지 방법을 중 CREATE INDEX를 사용해야 하는 이유에 대해서 정리한 글입니다.

 

MySQL에서 인덱스 추가 방법

1. ALTER TABLE ... ADD INDEX
2. CREATE INDEX
3. 정리

---

ALTER TABLE ... ADD INDEX

• 테이블 구조를 변경하면서 인덱스를 추가하는 방법
• 고유 인덱스뿐만 아니라 비고유 인덱스, 복합 인덱스 등을 추가할 수 있습니다
• MySQL의 ALTER TABLE 명령어의 하위 명령어로서, 테이블 구조를 변경하는 데 사용됩니다.
• 테이블에 대한 잠금을 걸고 테이블 구조를 변경하기 때문에, 테이블에 대한 다른 작업이 차단될 수 있습니다.
• 테이블의 구조적 변경을 위해 테이블 전체를 잠그기 때문에 큰 테이블에서는 시간이 오래 걸릴 수 있습니다.
• 테이블에 새로운 고유 인덱스를 추가하는 것 외에도, 테이블의 다른 구조적 변경(예: 컬럼 추가, 삭제, 수정)을 함께 수행할 수 있습니다.
  
  • 따라서, 테이블에 여러 가지 변경을 한 번에 적용하는 경우에 유용합니다.

예시

ALTER TABLE 테이블명 ADD INDEX 인덱스명 (컬럼명);

 

---

CREATE (UNIQUE) INDEX

• SQL 표준의 일부로, 인덱스를 생성하는 데 사용됩니다.
• 인덱스를 추가하는 것만을 목적으로 하며, 테이블의 다른 구조적 변경을 수행하지 않습니다.
  • 따라서, 테이블의 다른 속성에 영향을 주지 않습니다.
• 인덱스만 추가하므로, 일반적으로 테이블 전체에 대한 잠금이 걸리지 않습니다.
• 테이블을 잠그지 않거나 최소한의 잠금만으로 인덱스를 추가할 수 있습니다.
• 데이터베이스에 따라서는 인덱스 생성이 테이블과 별개로 관리될 수 있으며, 이 방법은 MySQL 뿐 아니라 다른 데이터베이스 시스템에서도 사용됩니다.

 

예시

• CREATE UNIQUE INDEX: 고유 인덱스(Unique Index)를 생성
  • 고유 인덱스는 인덱스가 걸린 컬럼(또는 컬럼들)에서 중복된 값을 허용하지 않습니다. 즉, 인덱스가 적용된 컬럼에 동일한 값이 존재할 수 없습니다. 만약 중복된 값을 삽입하려고 하면, MySQL은 오류를 반환합니다.
• CREATE INDEX: 일반 인덱스(Non-Unique Index)를 생성
  • 일반 인덱스는 중복된 값을 허용합니다. 동일한 값이 여러 번 나타날 수 있으며, MySQL은 이를 허용합니다. 인덱스의 주요 목적은 검색 성능을 높이기 위해 데이터를 정렬하거나, 검색 속도를 높이는 데 있습니다.

CREATE (UNIQUE) INDEX 인덱스명 ON 테이블명 (컬럼명);

---

정리

특징	ALTER TABLE ... ADD UNIQUE INDEX	CREATE (UNIQUE) INDEX
기능	테이블의 구조를 변경하면서 고유 인덱스를 추가함.	고유 인덱스를 별도로 추가함.
사용 가능 상황	테이블에 여러 변경 사항(컬럼 추가, 삭제 등)을 동시에 적용할 때 사용.	인덱스를 추가하는 것에 집중할 때 사용.
SQL 표준 준수 여부	MySQL 고유의 확장 기능. SQL 표준 일부가 아님.	SQL 표준의 일부로, MySQL 이외의 DBMS에서도 사용 가능.
잠금(lock) 동작	테이블 전체에 잠금이 걸릴 수 있음.	보통 테이블 전체 잠금 없이 인덱스만 추가.
인덱스 생성 시점	테이블 변경과 함께 인덱스를 생성.	인덱스만 별도로 생성.
복합 작업 가능 여부	여러 작업(컬럼 추가, 삭제 등)과 함께 인덱스를 생성할 수 있음.	인덱스 생성만을 단독으로 실행.
구문 예시	ALTER TABLE 테이블명 ADD UNIQUE INDEX 인덱스명(컬럼명);	CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명);
제약 조건(Constraints) 처리	제약 조건을 인덱스와 함께 추가할 수 있음.	인덱스만 추가하며, 제약 조건은 별도로 추가해야 함.

---

결론

ALTER TABLE ... ADD UNIQUE INDEX 는 Lock이 걸려도 괜찮고 테이블에 여러 가지 변경을 한 번에 적용하는 경우에 사용하는게 좋다. 이외의 경우는 CREATE INDEX를 사용하는게 좋다.