[스터디] 14. R-Tree 인덱스

목차

8.4 R-Tree 인덱스

8.4.1 구조 및 특성

• 도형들의 MBR(Minimum Bounding Rectangle, 해당 도형을 감싸는 최소 크기의 사각형)에서 사각형들의 포함 관계를 B-Tree 형태로 구현한 인덱스

▲ 공간(R-Tree, Spatial) 인덱스 구조 [출처: 교보문고]

8.4.2 B-Tree 인덱스의 용도

• WG884(GPS) 기준의 위도, 경도 좌표 저장에 주로 사용됨.
• CAD/CAM 소프트웨어 또는 회로 디자인 등과 같이 좌표 시스템에 기반을 둔 정보에 대해 적용할 수 있음.

8.5 전문 검색 인덱스

• 문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문 검색에는 InnoDB나 MyISAM 스토리지 엔진에서 제공하는 B-Tree 인덱스를 사용할 수 없음.
• 문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘을 전문 검색(Full Text search) 인덱스라고 함.

8.5.1 인덱스 알고리즘

• 문서의 키워드를 인덱싱하는 기법에 따라 어근 분석과 n-gram 분석 알고리즘으로 구분함.

8.5.1.1 어근 분석 알고리즘

• 불용어(Stop Word) 처리: 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업
• 어근 분석(Stemming): 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업

8.5.1.2 n-gram 알고리즘

• MeCb을 위한 형태소 분석은 매우 전문적인 전문 검색 알고리즘이어서 많은 노력과 시간이 필요함.
• 이러한 단점을 보환하기 위한 방법으로 n-gram 알고리즘이 도입됨.
• n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법임.

8.6 함수 기반 인덱스

• 일반적인 인덱스는 칼럼의 값 일부(앞부분) 또는 전체에 대해서만 인덱스 생성이 허용됨.
• 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 하는 경우 함수 기반의 인덱스를 활용함.

8.6.1 가상 칼럼을 이용한 인덱스

8.6.2 함수를 이용한 인덱스

8.7 멀티 밸류 인덱스

• 인덱스 키와 데이터 레코드 키는 1:1의 관계를 가짐.
• 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스를 멀티 밸류(Multi-Value) 인덱스라고 함.

8.8 클러스터링 인덱스

• 테이블의 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것

 

▲ 클러스터링 테이블(인덱스) 구조 [출처: 교보문고]

장점	- 프라이머리 키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름   (특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름) - 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음   (커버링 인덱스라고 함)
단점	- 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐 - 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림 - INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림 - 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림

8.9 유니크 인덱스

• 유니크는 인덱스라기 보다는 제약 조건에 가까움. 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미함.

8.10 외래키

• MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있음.
  • 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생함.
  • 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않음. 
• 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성됨.
• 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없음.

요약

• B-Tree: 균형 잡힌 트리 구조를 사용하여 데이터를 정렬하고 빠르게 검색하는 인덱스

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);

-- department 컬럼에 B-Tree 인덱스 적용
CREATE INDEX idx_department ON employees(department);

-- 데이터 삽입
INSERT INTO employees (id, name, department) VALUES (1, 'Alice', 'Sales');
INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'Engineering');
INSERT INTO employees (id, name, department) VALUES (3, 'Charlie', 'Sales');

-- idx_department 인덱스를 사용하여 department를 기준으로 검색
SELECT * FROM employees WHERE department = 'Sales';

• R-Tree: 공간 데이터의 효율적인 검색을 위해 계층적 영역 구조를 사용한 인덱스

-- MySQL 8은 PostGIS와 같은 공간 데이터 인덱스를 지원하지 않음.
-- 아래 코드는 PostgreSQL에서만 작동함.
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    geom GEOMETRY(Point, 4326)
);

-- geom 컬럼에 R-Tree 인덱스 적용
CREATE INDEX idx_geom ON locations USING GIST (geom);

-- 데이터 삽입
INSERT INTO locations (name, geom) VALUES ('Location A', ST_Point(37.7749, -122.4194));
INSERT INTO locations (name, geom) VALUES ('Location B', ST_Point(34.0522, -118.2437));

-- idx_geom 인덱스를 사용하여 특정 영역 내의 위치 검색
SELECT * FROM locations
WHERE ST_Within(geom, ST_MakeEnvelope(-123.5, 37.5, -122.5, 38.5, 4326));

• 전문 검색 인덱스: 텍스트 데이터에 대해 전체 단어나 구를 빠르게 검색할 수 있는 인덱스

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT
);

-- content 컬럼에 전문 검색 인덱스 적용
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 데이터 삽입
INSERT INTO articles (id, title, content) VALUES (1, 'Database Guide', 'This is a comprehensive guide about databases.');
INSERT INTO articles (id, title, content) VALUES (2, 'SQL Tips', 'Learn useful SQL tips and tricks.');
INSERT INTO articles (id, title, content) VALUES (3, 'NoSQL vs SQL', 'Comparison between NoSQL and SQL databases.');

-- idx_content 인덱스를 사용하여 'database' 키워드가 포함된 문서 검색
SELECT * FROM articles WHERE MATCH(content) AGAINST ('databases' IN BOOLEAN MODE);

• 함수 기반 인덱스: 특정 컬럼에 대해 함수를 적용한 결과를 기준으로 인덱싱하는 방법

-- MySQL 8.0은 함수 기반 인덱스를 직접 지원하지 않음.
-- 대신 가상 컬럼(username_lower)을 사용하여 유사한 기능을 구현할 수 있음.
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    username_lower VARCHAR(50) AS (LOWER(username)) STORED
);

-- LOWER(username) 함수
CREATE INDEX idx_lower_username ON users(username_lower);

-- 데이터 삽입
INSERT INTO users (id, username) VALUES (1, 'John_Doe');
INSERT INTO users (id, username) VALUES (2, 'jane_doe');

-- idx_lower_username 인덱스를 사용하여 대소문자 무시하고 검색
SELECT * FROM users WHERE username_lower = 'john_doe';

• 멀티 밸류 인덱스: 하나의 컬럼이 여러 값을 가질 수 있는 경우 이 값들을 인덱싱하는 방법

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    tags JSON
);

-- 데이터 삽입
INSERT INTO products (id, name, tags) VALUES (1, 'Laptop', '["electronics", "computer"]');
INSERT INTO products (id, name, tags) VALUES (2, 'Headphones', '["electronics", "audio"]');

-- 특정 태그가 포함된 제품 검색 (올바른 경로 사용)
SELECT * FROM products WHERE JSON_CONTAINS(tags, '"electronics"');

• 클러스터링 인덱스: 데이터가 실제 물리적 저장 순서와 일치하도록 유지하는 인덱스

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
) ENGINE=InnoDB;

-- 데이터 삽입
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 101, '2024-01-10');
INSERT INTO orders (order_id, customer_id, order_date) VALUES (2, 102, '2024-05-15');
INSERT INTO orders (order_id, customer_id, order_date) VALUES (3, 103, '2024-09-20');

-- 클러스터링 인덱스를 사용하여 특정 범위의 주문 날짜 검색
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

• 유니크 인덱스: 컬럼 값이 고유하도록 보장하는 인덱스

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- 데이터 삽입
INSERT INTO customers (customer_id, email) VALUES (1, 'user@example.com');
INSERT INTO customers (customer_id, email) VALUES (2, 'another_user@example.com');

-- 유니크 인덱스를 사용하여 이메일의 고유성 보장
-- 중복된 이메일 삽입 시 오류 발생
INSERT INTO customers (customer_id, email) VALUES (3, 'user@example.com');

인덱스	장점	단점	사용 예시
B-Tree	균등한 삽입/삭제/검색 성능	재정렬 필요	RDBMS에서 기본 인덱스
R-Tree	공간 데이터에 최적화	고차원 데이터에서 성능 저하	GIS 데이터베이스
전문 검색 인덱스	텍스트 데이터의 빠른 검색	- 인덱스 크기가 큼 - 생성 시간 필요	문서 관리 시스템
함수 기반 인덱스	컬럼 변환 결과 검색 지원	복잡한 함수의 유지 비용	대소문자 무시 문자열 검색
멀티 밸류 인덱스	배열/리스트 데이터 효율적 검색	인덱스 크기와 관리 문제	NoSQL 배열 데이터
클러스터링 인덱스	순차적 범위 검색에 빠름	삽입/삭제 시 리소스 소모	로그 파일 테이블
유니크 인덱스	데이터 고유성 유지	중복 값 삽입 불가	사용자 ID, 이메일

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.