[DB] key와 index의 차이

key와 index는 무엇인가?

key는 테이블에 있는 tuple을 유일하게 식별하는(uniquely identify) attributes 집합을 의미한다

index는 테이블에 있는 tuple을 빠르게 찾기 위해 테이블에 적용되는 추가적인 자료 구조다

(보통 candidate key를 줄여서 그냥 key라고 부른다. 즉 key = candidate key)

한 테이블에는 여러 key들이 존재할 수 있다.

그중에서 tuple을 식별할 목적으로 DB 설계자가 공식적으로 선택하여 지정한 key를 primary key라고 한다

primary key는 엄밀히 말하면 constraint다

테이블을 생성할 때 primary key를 지정하게 되면 해당 primary key에 대해서는 서로 다른 tuple이 같은 primary key 값을 가질 수 없으며, null도 값으로 가질 수 없다

이 primary key를 통해서 tuple을 찾는 일은 매우 자주 일어나는 일이기 때문에 빠른 조회를 위해 대부분의 RDBMS는 primary key에 index를 자동으로 생성해 준다

foreign key는 다른 테이블의 primary key나 key를 가리킬 목적으로 생성된 attribute(s)를 의미한다

가령, 임직원 정보를 저장하는 EMPLOYEE 테이블과 부서 정보를 저장하는 DEPARTMENT 테이블의 스키마가 아래와 같이 있다면,

EMPLOYEE (emp_id, first_name, last_name, address, phone_number, birth_date, dept_id)

DEPARTMENT (id, name, address, leader_id)

EMPLOYEE 테이블의 dept_id는 임직원이 어느 부서에 속했는지 정보를 저장하는 attribute이기 때문에 DEPARTMENT 테이블의 (primary key인) id에 있는 값을 가지게 될 것이다

이때 이 dept_id가 foreign key가 된다

primary key와 마찬가지로 foreign key도 constraint다

foreign key를 걸어주게 되면, 이 foreign key는 반드시 가리키는 (다른 테이블의) attribute(s)에 존재하는 값만 가질 수 있다

한편, (primary key와는 다르게) foreign key로 지정해줬다고 해서 자동으로 index가 걸리는 것은 아니다

이건 RDBMS마다 다른데, MySQL의 경우에는 foreign key에 자동으로 index를 걸어주지만, 다른 RDBMS들은 오히려 자동으로 index를 걸어주지 않는 경우가 더 많다