[MySQL] [2]인덱스(Index)란-"R-Tree, 전문검색(n-gram),함수 기반,멀티 밸류, 클러스터링,유니크"인덱스란?

 

 

"R-Tree, 전문검색(n-gram), 함수 기반, 멀티 밸류, 클러스터링, 유니크"인덱스에 대해서 알아보자

 

 

들어가며

이전 포스팅에서는 B-Tree 인덱스에 대해서 알아보았다 .이번 포스팅에서는 MySQL에서 지원하는 나머지 인덱스에 대해서 알아보도록 하자.

 

R-Tree 인덱스

R-Tree 인덱스란?

공간인덱스 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱 하고 검색하는 목적의 인덱스이다.

세 가지 기능이 포함돼 있다.

• 공간 데이터를 저장할 수 있는 타입
• 공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
• 공간 데이터의 연산 함수(거리 또는 포함 관계의 처리)

구조 및 특성

Mysql에서 지원하는 도형 데이터 타입

• POINT : 점
• LINE : 선
• POLYGON : 도형
• GEOMETRY : POINT, LINE, POLYGON 객체를 저장할 수 있는 타입 

다음과 같이 공간 데이터를 저장하려면 MBR(Minumum Bounding Rectangle)이란 개념을 알아야 한다. MBR은 해당 도형을 감싸는 최소 크기의 사각형을 의미한다.

• 최상위 레벨(루트 노드) : 1, 2
• 차상위 레벨(브랜치 노드) :3,4,5
• 최하위 레발(리프 노드) : 6,7,8,9,10,11,12,1

R-Tree 인덱스 용도

1. 지리 정보 시스템(GIS) : 지리 정보 시스템은 지구상의 지리 공간 데이터를 저장, 관리, 분석하는 시스템
2. 위치 기반 서비스(LBS) : 위치 기반 서비스는 사용자의 위치 정보를 기반으로 주변의 서비스나 정보를 제공
3. 공간 데이터베이스 : 공간 데이터베이스에서 다양한 형태의 공간 데이터를 관리하고 쿼리 하는 데 사용
4. 검색 엔진 : 일부 검색 엔진은 지리 정보를 포함하여 다양한 유형의 데이터를 색인화하고 검색하기 위해 사용

---

 

전문검색(n-gram) 인덱스

인덱스 알고리즘

전문검색 인덱스는 문서의 키워드를 인덱싱 하는 기법에 따라 크게 단어와 어근분석과 n-gram 분석 알고리즘을 구할 수 있다.

 

어근 분석 알고리즘

• 불용어 처리(Stop Word) 처리
• 어근 분석(Stemming)

불용어 처리는 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업을 의미한다.

 

어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다. 각 나라별로 언어의 구조나 문법이 다르므로 프로그램을 사용해서 적용 가능하다.

 

 

n-gram 알고리즘

위에서 설명한 어근분석의 프로그램을 사용해서 정의한다면 매우 많은 시간이 발생하게 된다. 이런 단점을 보안하기 위해서 n-gram 알고리즘이 도입되었다. n-gram 알고리즘은 단순히 키워드를 검색해 내기 위한 인덱싱 알고리즘이라고 볼 수 있다.

 

n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 기법이다. "n"은 최소 글자수를 의미하는데 2-gram은 2 글자씩 잘라서 인덱싱 한다는 뜻이다.

 

nice to meet you

이 문장을 통해 2-gram 알고리즘을 사용해 토큰을 분리해 보자

 

현재 4개의 단어로 구분되며, 2 글자씩 중첩해 토큰으로 분리된다.

단어	2-gram 알고리즘 사용
nice	ni	ic	ce
to	to
meet	me	ee	et
you	yo	ou

 

이런 식으로 2개씩 토큰을 분리한다. 그 후에 MySQL에 정의된 불용어와 비슷하거나 불용어가 포함된 경우에 해당 문자를 걸러서 버린다.

 

기본적으로  MySQL에 포함된 불용어

 

select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;

 

쿼리를 실행하면 다음과 같은 불용어 단어 나오게 된다 현재 nice to meet you "to"가 불용어에 포함되어 있어 걸러지고 나머지 토큰들이 전문검색 인덱스에 등록되게 된다.

SET global innodb_ft_enable_stopword=off;

이 쿼리문을 실행하면 InnoDB 엔진을 사용할 경우 실행 중인 상태여도 불용어 처리를 무시할 수 있다.

 

사용자 정의 

사용자 정의 불용어를 사용하는 방법은 두 가지 정도가 있다.

 

1. 불용어 목록을 파일로 작성하고 MySQL 서버 설정 파일에서 파일경로를 지정하면 된다.

ft_stopword_file="파일 경로"

 

2. InnoDB 엔진을 사용하는 테이블의 전문 검색 엔진에서만 사용가능하, innodb_fr_server_stopword_table 시스템 변수에 불용어 테이블을 설정하면 된다.

-- 불용어를 저장할 테이블 생성
CREATE TABLE innodb_stopwords (
    word VARCHAR(50) NOT NULL,
    PRIMARY KEY (word)
) ENGINE=InnoDB;

-- 불용어 테이블에 불용어 추가
INSERT INTO innodb_stopwords (word) VALUES
('a'), ('the'), ('and'), ('to'), ('of'), ('in');

-- innodb_ft_server_stopword_table 시스템 변수 설정
SET GLOBAL innodb_ft_server_stopword_table = 'your_database_name.innodb_stopwords';

 

전문검색 인덱스를 사용하려면 두 가지 조건을 갖춰야 한다.

• 쿼리 문장이 전문 검색을 위한 문법(MATCH.... AGAINST...)을 사용
• 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유

---

 

함수 기반 인덱스

 

MySQL 서버에서 함수기반 인덱스를 구현하는 방법은 다음 두 가지로 구분가능하다.

 

가상 칼럼을 이용한 인덱스

함수를 이용한 인덱스

 

가상칼럼을 이용한 인덱스 예시

CREATE TABLE Products (
    Category varchar(50),
    Subcategory VARCHAR(50),
    ProductName VARCHAR(100),
    Price int,
    Discount int,
    discount_price INT AS(price - (price*discount /100)) VIRTUAL	//가상 컬럼을 생성한다.
);

discount_price 칼럼명으로 가상 칼럼을 생성한다. VIRTUAL이나 STORED 옵션 중 하나를 선택해서 생성한다.

 

CREATE INDEX idx_discounted_price ON Products (discount_price);

가상 칼럼을 사용한 인데스 생성

INSERT INTO Products (Category, Subcategory, ProductName, Price, Discount)
VALUES ('Electronics', 'Smartphones', 'iPhone 13', 1000000, 10);

Products에 값을 넣어고 가상 칼럼으로 생성한 인덱스로 값을 조회해서 사용할 수 있다.

SELECT * FROM Products WHERE discount_price <= 900000;

인덱스를 통해 값을 조회된 것을 볼 수 있다. 하지만 가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 변경된다는 단점이 있다.

함수를 이용한 인덱스

CREATE TABLE Products (
	id int,
    Category varchar(50),
    Subcategory VARCHAR(50),
    ProductName VARCHAR(100),
    Price int,
    Discount int,
    primary key(id)
);

Products의 테이블을 생성한다.

CREATE INDEX idx_discounted_price ON Products ((Price - (Price * Discount / 100)));

인덱스를 생성

INSERT INTO Products (id,Category, Subcategory, ProductName, Price, Discount)
VALUES (1,'Electronics', 'Smartphones', 'iPhone 13', 1000000, 10);

조회활 데이터 추가

 

select * from products where ((Price - (Price * Discount / 100)))= 900000;

where문에 인덱스 생성 시에 명시한 표현식을 그대로 사용해서 where문에 적어준다 그 후 할인된 가격 90000을 조회하면

다음과 같이 값이 나오게 된다.

 

 

가상칼럼 방식이나 함수 방식이나 동일한 성능을 발휘한다.

---

 

멀티 밸류 인덱스

멀티 밸류 인덱스란?

멀티 밸류(Multi-Value) 인덱스는 하나의 데이터 레코드가 여러 개의 키값을 가질 수 있는 형태의 인덱스이다.

최근 들어 데이터를 JSON 형태로 값을 전달해서 개발을 하지만 MySQL 이전버전에서는 JSON 형태의 값을 저장할 수가 없었다. 하지만 8.0 버전부터는 멀티 밸류인덱스를 지원해 JSON타입의 값도 저장 할 수 있게 되었다. 

 

JSON형태의 값 저장되어 있다고 가정했을 때

 

멀티밸류 인덱스를 통해서 검색을 하려면 다음과 같은 쿼리를 사용해야 한다.

 

MEMBER OF() : 특정 값이 포함되어 있는지 여부를 확인한다.

JOSN_CONTAINS() : JOSN 데이터가 특정 값을 포함하는지 여부를 확인한다.

JOSN_OVERLAPS() 두 개의 JOSN배열이 중첩되어 있는지 여부를 확인한다.

---

 

클러스트링 인덱스

클러스트링 인덱스란?

클러스트링 인덱스(Clustered Index)는 데이터베이스에서 여러 개의 열을 하나로 묶어서 저장하는 인덱스입니다. 이는 주로 InnoDB와 같은 데이터베이스 엔진에서 사용됩니다. 클러스트링 인덱스는 테이블의 레코드를 저장하는 물리적인 순서를 인덱스의 순서와 일치시킨다.
클러스트링 인덱스는 주로 프라이머리 키(primary key)에 적용된다. 이는 프라이머리 키 값에 따라 레코드가 저장되는 위치가 결정되므로, 프라이머리 키 값이 비슷한 레코드들이 물리적으로 이웃하게 된다.
이러한 구조는 프라이머리 키를 기반으로 검색 및 범위 검색 작업을 효율적으로 수행할 수 있다. 그러나 프라이머리 키를 신중하게 선택해야 한다. 왜냐하면 프라이머리 키의 선택은 클러스트링 인덱스의 성능 및 데이터베이스의 일반적인 성능에 영향을 줄 수 있기 때문이다.
따라서 클러스트링인덱스를 효과적으로 활용하려면 프라이머리 키를 신중하게 선택하고 설계해야 한다

 

프라이머리키가 없는 클러스트링 인덱스

클러스트링 인덱스의 프라이머리키 우선순위

1. 프라이머리 키가 있다면 기본적으로 프라이머리 키를 클러스트링 키로 선택

2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스트링 키로 선택

3. 둘 중 아무것도 없다면 자동으로 내부에서 증가되는 값을 설정해 그 값을 클러스트링 키로 선택

 

만약 프라이머리키나 유니크 옵션이 없는 상태에서 클러스트링 인덱스를 통해 설계를 했다면 '3'번과 형태로 클러스트링 키를 생성한다. 하지만 이렇게 생선 된 키는 내부에서 자동으로 생성하는 키여서 사용자가 직접 사용할 수 없고 어던 키로설정되었는지 확일할수 없다 즉 쿼리문장에서 명시적으로 사용할수 없다는 것이다. 그래서 항상 프라이머리키는 명시적으로 작성해줘야 한다.

클러스트링 인덱스의 장점과 단점

장점

• 프라이머리 키로 검색할 때 처리 성능이 빠르다.
• 테이블의  모든 세컨더리 인덱스가 프라이머리 키를 가지고 있어 인덱스로 만 처리되는 경우가 많다.

단점

• 모든 세컨더리 인덱스는 프라이머리 키를 가지고 있어서 인덱스의 크기가 커질 수 있다.
• 클러스트링 인덱스를 사용하면 바로 검색하는 것이 아닌 센컨더리 인덱스의 프라이머리 키를 통해 데이터를 찾아 성능이 느림
• INSERT 하면 프라이머리 키의 값의 따라 저장 위치가 결정되므로 성능이 느림
• 프라이머리 키를 변경한다면 DELETE를 하고 INSERT를 하는 쿼리문이 두 번 실행되면서 성능이 느림

클러스트링 인덱스의 주의상항

클러스트링 인덱스를 사용한다면 키의 크기를 매우 신중히 결정해야 한다. 키의 크기의 따라서 인덱스의 크기가 많이 증가할 수 있기 때문이다 프라이머리키가 각각 5바이트와 30바이트가 있다고 해보자 그리고 인덱스의 크기는 10바이트이다

인덱스의 크기는 프라이머리키 * 인덱스의 키* 데이터의 건수로 크기가 결정된다.

프라이머리키 5바이트

십만 건의 데이터가 있다고 한다면 . 5바이트일때 인덱스의 크기는 4.7MB이다.

프라이머리키 30바이트

십만건의 데이터가 있다고 한다면 , 30바이트일 때 85MB이다.

 

80MB 정도 증가한셈이다. 만약 데이터가 더 쌓인다면 인덱스의 크기는 점점 더 증가하여 성능이 느려지게 된다. 그러므로 클러스트링 인덱스를 사용할 때는 꼭 프라이머리키를 명시적으로 사용하되 프라이머리키의 크기를 잘 고려해서 설계해야 성능 향상에 도움이 된다.

---

 

유니크 인덱스

유니크 인덱스란?

데이터베이스에서 열 또는 열의 조합에 대해 중복된 값을 허용하지 않는 인덱스이다.

유니크 인덱스의 특징

1. 중복된 값을 허용하지 않음
2. 검색 및 정렬 성능 향상
3. 기본키 대체
4. NULL 값 허용
5. 여려 열에 대한 유니크 인덱스 가능

유니크 인덱스에서 쓰기는 일반 세컨더리 인덱스에 비해서 느리게 동작한다. 왜냐면 MySQL에서의 유니크 인덱스에서 중복된 값을 체크할 때 읽기 잠금을 사용하고, 쓰기를 할때 쓰기 잠금을 사용하는데 동시에 이과정을 진행한다면 데드락이 발생하기 때문이다.

 

유니크 인덱스 사용 시 주의사항

같은 열에 대해 유니크 인덱스와 일반 인덱스를 중복해서 생성할 필요는 없다. 유니크 인덱스는 해당 열에 대한 중복을 방지하고 데이터 무결성을 보장하는 역할을 하므로, 일반 인덱스와 동일한 역할을 수행하기 때문이다. 따라서 같은 열에 대해서 유니크 인덱스를 생성하면 일반 인덱스의 기능도 겸하게 되어 중복 생성할 필요가 없다.

 

 

---

참고

Real MySQL 8.0