Database 성능 향상을 위한 Index 최적화: btree, hash, text, rtree의 선택과 예시

Index 사용의 장단점

장점

검색 성능 향상: Index는 테이블에서 레코드를 검색하는 데에 효율적으로 사용됩니다. 특히 대용량의 데이터베이스에서 검색 속도를 향상시키는 데 도움이 됩니다.

정렬 및 그룹화 성능 향상: 정렬 또는 그룹화와 관련된 쿼리에서도 성능이 향상될 수 있습니다.

고유 제약 조건 강화: Index는 중복을 방지하며, 유일성을 강화하는데 사용될 수 있습니다.

단점

저장 공간 소모: Index는 추가적인 저장 공간을 필요로 합니다. 대용량의 테이블에서 많은 인덱스를 가지고 있으면 디스크 공간이 부담스러울 수 있습니다.

데이터 갱신 시 성능 저하: 인덱스가 많은 테이블에서 레코드를 추가, 수정 또는 삭제할 때 성능이 감소할 수 있습니다. 이는 인덱스의 업데이트와 관련이 있습니다.

관리 복잡성: Index를 효과적으로 관리하기 위해서는 추가적인 유지보수가 필요할 수 있습니다.

Index 종류와 사용 예시

B-Tree 인덱스

장점: 범위 쿼리 및 정렬된 데이터에 효과적입니다.

단점: 특정 데이터 분포에서는 성능이 감소할 수 있습니다.

예시: 주문 날짜, 사용자 아이디 등 범위 쿼리에 유용합니다.

Hash 인덱스

장점: 일치하는 값 검색에 빠르지만 범위 쿼리에는 적합하지 않습니다.

단점: 범위 쿼리 및 정렬이 필요한 경우 사용하지 않는 것이 좋습니다.

예시: 정확한 일치 검색이 필요한 경우에 사용됩니다.

Full Text 인덱스

장점: 텍스트 검색에 효과적입니다.

단점: 일부 데이터베이스에서만 지원되며, 일반적으로 기본적인 검색보다는 특정한 유형의 데이터에 적합합니다.

예시: 문서 내의 단어 또는 키워드 검색에 사용됩니다.

R-Tree 인덱스

장점: 지리 정보 시스템(GIS)에서 공간 데이터 검색에 효과적입니다.

단점: 일반적인 데이터에는 적합하지 않습니다.

예시: 지리적 위치 정보를 가진 데이터베이스에서 사용됩니다.

Query 작성에 따른 Index 적용 여부

Index 적용되는 경우

검색 조건에 인덱스된 열이 포함된 경우.

정렬 또는 그룹화에 인덱스가 사용될 수 있는 경우.

JOIN 연산에 사용되는 열이 인덱스된 경우.

Index 적용되지 않는 경우

인덱스된 열에 대해 함수나 연산이 수행되는 경우.

WHERE 절에 인덱스된 열을 변형한 경우.

데이터의 일부만 필요한 경우 (인덱스를 사용하더라도 모든 데이터를 스캔해야 할 수 있음).

예를 들어 full text의 경우

-- Full Text Index가 포함된 테이블 생성
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(255),
    user_description TEXT,
    FULLTEXT(user_name, user_description)
);

-- 샘플 데이터 삽입
INSERT INTO users (user_id, user_name, user_description) VALUES
(1, 'John Doe', '기술에 열정을 갖고 있는 소프트웨어 엔지니어.'),
(2, 'Jane Smith', '추상 회화에 특화된 예술가.'),
(3, 'Bob Johnson', '요리의 세계를 탐험하는 셰프.');

-- Full Text Index를 활용한 쿼리
SELECT * FROM users WHERE MATCH(user_name, user_description) AGAINST('기술');

-- Full Text Index가 적용되지 않을 수 있는 쿼리
SELECT * FROM users WHERE user_description LIKE '%열정%';

CREATE TABLE 문은 user_name 및 user_description 열에 FULLTEXT 인덱스를 생성합니다.

샘플 데이터가 users 테이블에 삽입됩니다.

첫 번째 쿼리는 MATCH...AGAINST 구문을 사용하여 Full Text 검색을 수행하며 인덱스를 활용합니다.

두 번째 쿼리는 LIKE 구문이 검색어의 시작에 와일드카드를 사용하므로 Full Text Index가 완전히 활용되지 않을 수 있습니다.

이 예시는 Full Text Index를 활용하여 Full Text 검색을 수행하는 시나리오를 보여줍니다. Full Text Index의 효과는 사용하는 구체적인 데이터베이스 시스템에 따라 달라질 수 있습니다.