Study 5화 - INDEX

yeolyeol·2024년 8월 13일

til

목록 보기

13/27

INDEX

데이터베이스에서 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조

만약 우리가 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는것은 오랜 시간이 걸린다. 그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, 데이터베이스의 index는 책의 색인과 같다.
데이터베이스에서도 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있다.

인덱스의 관리

index는 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다.
그래서 인덱스가 적용된 컬럼에 INSERT나 UPDATE, DELETE가 수행되면 각각 추가적인 연산을 수행해 줘야한다. 이때, 그에 따른 오버헤드가 발생한다.

INSERT : 새로운 데이터에 대한 인덱스를 추가
DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업 진행
UPDATE : 기존의 인덱스를 사용하지 않게 바꾸고 갱신된 데이터에 대해 인덱스 추가

장단점

장점

테이블을 조회, 삭제, 갱신하는 속도와 그에 따른 성능을 향상시킴.
전반적인 시스템의 부하를 줄일 수 있음.

단점

인덱스를 관리하기 위한 저장공간이 필요함
인덱스를 최신 상태로 유지하기 위한 추가 작업이 필요함.
1)인덱스를 잘못 사용하면 성능이 저하되는 역효과가 발생함.

성능의 역효과

DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면, 인덱스의 크기가 비대해진다. 이는 곧 성능의 저하로 이어진다. 왜냐하면, DELETE와 UPDATE는 데이터가 삭제되거나 변경되면 관련된 인덱스를 삭제하는 것이 아닌 사용 안함처리를 해준다.
즉 데이터의 개수가 약 10만건인 테이블에서 DELETE나 UPDATE가 빈번하게 처리 되었다면, 인덱스는 10만보다 훨씬 더 많이 존재하게 된다. 결국 SQL문 처리 시 테이블 보다 인덱스가 더 커지게 되어 오히려 성능이 떨어지게 된다.

INDEX, 언제 사용하는 게 좋을까?

인덱스를 효율적으로 사용하기 위해서는 결과적으로 인덱스의 크기를 키우지 않는 것이다. 그렇다면 읽기 중심의 데이터베이스에 매우 큰 효율은 보인다.
1. 읽기 중심의 데이터베이스
2. 자주 조회되는 컬럼
특정 컬럼에 대해 자주 검색, 필터링, 정렬이 이루어지는 경우 인덱스를 생성하면 성능을 크게 향상시킴.
예를 들어, WHERE 절에서 자주 사용되는 컬럼이나 ORDER BY 절에서 자주 정렬되는 컬럼에 인덱스를 추가
3. JOIN 조건
여러 테이블을 조인할 때 사용되는 컬럼에 인덱스를 추가하면 조인 성능이 개선됨.
4. 고유한 값이 많은 컬럼 (카디널리티가 높은 컬럼)
고유한 값이 많은 컬럼(예: 사용자 ID, 이메일 주소 등)에 인덱스를 생성하면 검색 성능이 향상됨.
반면, 값의 종류가 적고 반복되는 경우(예: 성별) 인덱스의 효과가 제한적일 수 있음.
5. 범위 검색
BETWEEN, >, <, >=, <=와 같은 범위 검색을 수행하는 쿼리에서 인덱스를 사용하면 성능이 개선됨.

INDEX 사용 예시

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

위와 같은 테이블이 있다고 가정할 때,

-- username 컬럼에 대한 인덱스 생성
CREATE INDEX idx_username ON users(username);

-- email 컬럼에 대한 인덱스 생성
CREATE INDEX idx_email ON users(email);

하나의 컬럼에 인덱스를 생성할 수 있고

-- username과 email 컬럼에 대한 복합 인덱스 생성
CREATE INDEX idx_username_email ON users(username, email);

두개 이상의 컬럼에 대한 복합 인덱스를 생성할 수 있다.

종류

Hash Table

해시 테이블은 Key값을 이용해 고유한 index를 생성하여 그 index에 저장된 값을 꺼내오는 구조.
시간복잡도 O(1)이라는 매우 빠른 검색을 지원.

단, 해시 특성상 등호(=) 연산에만 특화되어있다. 왜냐하면 Key값이 1이라도 달라지면 전혀 다른 해시 값을 생성하게 되어 부등호 연산(>, <)이 자주 사용되는 컬럼에는 적합하지 않다.

B-Tree

구조
B-트리는 다차원 트리 구조로, 각 노드는 여러 개의 키와 자식 포인터를 가질 수 있다.
노드의 키는 오름차순으로 정렬되며, 각 키는 해당 키보다 작은 값과 큰 값의 자식 노드를 가리킨다.
특징
각 노드는 최소한의 자식 노드 수를 유지해야 하며, 노드가 가득 차면 분할되어 새로운 노드가 생성.
검색, 삽입, 삭제 모두 O(log n)의 시간 복잡도를 갖는다.
데이터 저장
데이터가 노드에 직접 저장. 즉, 내부 노드에서도 데이터에 접근할 수 있다.

B+Tree

구조
B+트리는 B-트리의 변형으로, 모든 데이터는 리프 노드에만 저장되고, 내부 노드는 데이터에 대한 포인터 역할만 한다.
리프 노드는 서로 연결되어 있어 범위 검색이 용이하다.
특징
검색, 삽입, 삭제 모두 O(log n)의 시간 복잡도를 갖는다.
리프 노드에서만 실제 데이터를 저장하므로, 내부 노드의 크기가 작아져 메모리 사용 효율이 높다.
데이터 저장
데이터가 리프 노드에만 존재하므로, 내부 노드는 키 값만 갖는다.
이를 통해 검색 시 내부 노드를 빠르게 지나갈 수 있다.