인덱스(index)
인덱스란?
데이터베이스의 테이블에 대한 검색 속도를 향상시켜주는 자료구조
테이블의 특정 컬럼에 인덱스를 생성한다는 것은, 해당 컬럼의 데이터를 정렬한 후 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장한다는 것을 의미한다. 컬럼의 값과 물리적 주소를 (key, value)의 한 쌍으로 저장한는 것이다.
인덱스는 목차, 색인의 역할을 한다!
데이터베이스 인덱스의 기본 개념
- 데이터베이스 인덱스(Database Index)
- 데이터베이스 관리 시스템(DBMS)에서 데이터 검색 속도를 높이기 위해 사용하는 데이터 구조
- 인덱스가 테이블의 한 열(또는 여러 열)에 대한 포인터를 포함하고 있어, 특정 값의 위치를 빠르게 찾을 수 있게 도와준다.
- 풀 테이블 스캔 (Full Table Scan)
- 테이블의 모든 행을 순차적으로 검색하는 것
- 인덱스를 사용하지 않는 경우, 풀 테이블 스캔을 시행해야해 많은 시간이 소요된다.
- 따라서 인덱스를 사용하면 검색 성능이 크게 향상된다.
- 데이터 구조(
B-Tree또는Hash Table등의)를 통한 인덱스 구현B-Tree인덱스: 범위 검색에 유리Hash Table: 특정 값의 검색에 유리
- 추가적인 저장 공간의 소요
- 인덱스 생성 및 유지에는 추가적인 저장공간이 필요하며, 데이터 삽입, 수정, 삭제 작업 시 성능 저하가 발생할 수 있다. 따라서 신중한 사용이 필요하다.
📑 결론
데이터베이스 인덱스는 데이터 검색 성능을 향상시키는 강력한 도구이지만, 그 사용에 있어서는 성능과 저장 공간 사이의 균형을 고려해야 한다.
데이터베이스 인덱스의 원리
- 데이터베이스 인덱스의 핵심 원리
- '키(key)'와 '포인터(pointer)'를 사용하여 데이터의 물리적 위치를 빠르게 찾는 것
- 인덱스는 키를 기준으로 정렬
- 각 키는 테이블의 특정 행을 가리키는 포인터를 가짐.
- '키(key)'와 '포인터(pointer)'를 사용하여 데이터의 물리적 위치를 빠르게 찾는 것
- 인덱스를 사용하면 DB는 키 값을 기준으로 이진 검색(Binary Search) 또는 트리 검색(Tree Search)를 수행할 수 있다.
- 따라서, 대규모 데이터 집합에서도 빠른 검색 기능을 제공할 수 있다.
- 예를 들어, B-트리 인덱스는 균형 잡힌 트리 구조를 가지고 있어, 데이터의 삽입, 삭제, 검색 작업을 로그 시간 복잡도(
O(Log n))로 수행할 수 있다.
- 인덱스의 키 선택지: 기본키(Primary Key), 외래키(Foreign Key), 일반 열(Column)
- PK의 경우 인덱스가 자동으로 생성됨.
- FK나 일반열의 경우 개발자가 명시적으로 생성해야 한다.
- 인덱스의 효율적 사용
- 쿼리의 특성과 데이터의 분포를 고려하여 적절한 인덱스를 선택하고, 불필요한 인덱스는 제거해야 한다.
데이터베이스 인덱스 최적화 전략
- 쿼리 분석을 기반으로 어떤 인덱스가 필요한지 결정
- 자주 사용되는 쿼리와 그 쿼리에서 검색되는 열을 파악하여, 해당 열에 인덱스 생성
- 인덱스가 존재하지 않으면 풀 테이블 스캔을 수행해야 해 성능저하가 나타날 수 있음.
- 인덱스 생성 후에는 주기적으로 사용 상태와 성능을 모니터링해 사용하지 않는 인덱스는 제거하고 필요에 따라 인덱스를 재구성한다.
- 인덱스 분할(Partitioning)
- 대용량 테이블의 인덱스를 여러 개의 작은 파티션으로 나누어 관리하여 검색 성능 향상
- 인덱스 커버링(Index Covering)
- 쿼리가 인덱스에서 필요한 모든 데이터를 얻을 수 있도록 하여, 테이블에 대한 접근을 줄이는 기법.
Key의 종류 (in MySQL)
Key(Index)
- Key는 DB의 Index와 동의어이다.
- Database는 데이터의 검색을 위해 Index를 색인으로 사용하므로 중요한 기능을 한다.
- 중복,
null이 허용된다.- 그러나
null이 색인에 있어 비약적인 성능 저하를 가져오므로, 일반적으로 Nullable한 데이터의 경우 Indexing을 하지 않는다.
- 그러나
- 단순히 Key, 즉 Index로만 지정할 경우에는 별도의 제약조건을 가지지 않는다.
- 따라서 테이블 내에 데이터에 대해 엄격한 정합성이 요구될 경우 적합하지 않다.
- null이 색인에 있어 어떤 성능 저하를 가져온다는 건지 궁금해서 좀 찾아봤다. 아래의 게시글을 참조하면 좋을 것 같다.
Primary Key
- 일반적으로 DB를 설계할 때 Key라고 하면 보통 PK를 의미한다.
- NOT NULL & UNIQUE 옵션이 포함된다.
- 테이블 당 단 하나의 정의만 가질 수 있다.
- 즉, PK로 단 하나의 컬럼이 지정되어 있다면 해당 컬럼의 데이터는 Table 내에서 유일성이 보장되며, 여러 개가 PK로 지정되어 있다면 해당 KEY들의 조합에 대해 유일성이 보장된다.
- 따라서 PK는 같은 PK를 갖는 행을 테이블 내에서 고유하게 만든다.
- RDB의 특징적인 데이터 정합성의 보장과 Key 값의 성질을 갖기 때문에, 일반적인 설계에서도 가장 선호되는 Key 타입이다.
- 기본적으로 Index 성질 역시 보장되기 때문에 검색 시 색인의 Key가 된다.
- 동시에 Constraint를 갖기 때문에 다른 테이블과 JOIN을 할 때 기준값으로 사용된다.
Unique Key
- Unique Key는 Uniqueness를 지닌 Index를 말하며, Unique Index라 부르기도 한다.
- PK와 마찬가지로 중복성이 허용되지 않지만 null에 대한 허용이 가능하다.
- 테이블 당 여러개를 가질 수 있다.
Foreign Key
- JOIN등으로 다른 DB와의 Relation을 맺는 경우, 다른 테이블의 PK를 참조하는 Column을 Foreign Key(FK)라고 한다.
- 여기서 Foreign Key Relation을 맺는다는 의미는 논리적 뿐만 아니라 물리적으로 다른 테이블과의 연결까지 맺는 경우를 말하며, 이 때 FK는 제약조건(Constraint)으로의 역할을 한다.
- 아래와 같은 옵션들이 있다.
- RESTRICT: FK 관계를 맺고 있는 데이터 ROW의 변경(UPDATE) 또는 삭제(DELETE)를 막는다.
- CASCADE: FK 관계를 맺을 때 가장 흔하게 접할 수 있는 옵션으로, FK와 관계를 맺은 상대 PK를 직접 연결해서 DELETE 또는 UPDATE 시, 상대 Key 값도 삭제 또는 갱신시킨다.
- 이때에는 Trigger가 발생하지 않으니 주의하자.
- SET NULL: 논리적 관계상 부모의 테이블, 즉 참조되는 테이블의 값이 변경 또는 삭제될 때 자식 테이블의 NULL로 만든다. UPDATE쿼리로 인해 SET NULL이 허용된 경우에만 동작한다.
- NO ACTION: RESTRICT 옵션과 동작이 같지만, 체크를 뒤로 미룬다.
- SET DEFAULT: 변경 또는 삭제 시에 값을 DEFAULT 값으로 세팅한다.
정리
주로 PK가 설계 시 많이 이용되지만, 정합성이 중요하지 않은 경우 Index만 이용해서 테이블을 설계하기도 한다. UNIQUE Index는 주로 복잡한 테이블에서 부분적인 정합성을 살리기 위해 많이 이용된다. 그리고 FK의 물리적 연결은 서버의 안정성을 위해 지양하는 편이 많다.
🤔 FK를 지양하는 이유
"FK의 물리적 연결은 서버의 안정성을 위해 지양하는 편"이라는 표현도 근거가 있나 싶어서 찾아봤다.
- FK와 성능저하
- FK를 생성하면 데이터를 입력/수정/삭제할 때 제약사항을 내부적으로 체크해야 하기 때문에 FK가 없을 때 보단 성능이 저하될 수 있다.
- 성능저하는 FK를 생성한 컬럼에 인덱스를 생성하지 않았기 때문이므로, 반드시 인덱스를 생성하도록 한다.
- FK와 데이터베이스 관리
- FK를 생성하면 데이터베이스 관리가 어렵다. 테이블 간에 상호연관성이 존재하기 때문에 데이터베이스 테이블을 DROP하거나 생성할 때 항상 순서를 고려해야 하고, 데이터를 대량으로 전환할 때에도 이러한 규칙을 준수해야 한다.
경험상 FK가 너무 귀찮았던 기억이 나서 안쓰는게 이해는 간다. 근데 그래도 실제 실무에서 FK를 안쓰고 아무 데이터나 들어가는게.. 정말 괜찮을까...?
Reference
Good Luck!