Foreign Key 인덱스 자동 생성

MySQL은 5.5 버전부터 기본 스토리지 엔진이 MyISAM에서 트랜잭션 지원, 외래 키 제약 조건, 높은 데이터 무결성 유지, 충돌 복구 기능 등이 더 뛰어난 InnoDB로 변경되었다.

SpringBoot JPA를 사용할 때, 엔티티의 연관관계를 설정해서 FK를 생성할 때 MySQL에서는 FK 인덱스가 생성되었고 Postgres는 생성되지 않았다. ORM이 DataSource마다 다르게 설정해주나 싶었는데 MySQL이 담당하는 것이었다.

MySQL에서는 테이블을 생성할 때 외래 키(Foreign Key)에 대한 인덱스가 자동으로 생성된다.

CREATE TABLE category (
    category_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE box (
    box_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT,
    FOREIGN KEY (category_id) REFERENCES category(category_id)
);

mysql> show index from box;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| box   |          0 | PRIMARY     |            1 | box         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| box   |          1 | category_id |            1 | category_id | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

1. 외래 키를 생성할 때 해당 열에 대한 인덱스를 자동으로 생성한다. 이는 InnoDB 스토리지 엔진의 기본 동작이다.

2. 위 쿼리의 경우, box table의 category_id 열에 대해 자동으로 인덱스가 생성된다.

InnoDB는 일반적으로 B-Tree Index를 사용하여 외래 키 인덱스를 생성한다. B-Tree Index는 데이터 범위 검색과 정확한 값 검색에 효율적이며, 조인 작업에 유리하다고 한다.

이렇게 외래 키 제약을 효율적으로 관리하는 "외래 키 열에 대한 자동 인덱스 생성" 관데이터 무결성을 보장하고 작업 성능을 향상시키는 데 도움이 된다고 한다.

만약 자동 인덱스 생성을 원하지 않는다면, 외래 키를 생성할 때 WITHOUT INDEX 옵션을 사용하면 된다.

MySQL InnoDB의 자동 인덱스 생성 기능은 대부분의 경우 유용하다고 하며, 데이터베이스의 성능을 향상시키는데 도움을 준다고 한다.

하지만 특정 상황에서는 오히려 성능 저하를 초래할 수 있다는 생각도 들기 때문에 상황에 맞게 활용하고 DB는 설계에 따라서 필요한 인덱스만 생성하도록 하는것이 좋다고 생각한다.

• 데이터 양이 많거나 복잡한 조인이 포함된 경우, 불필요한 인덱스는 오히려 I/O 작업을 증가시켜서 성능 저하가 발생할 수 있다.

Summary

B-Tree Index는 JOIN 작업에 매우 유리하다.