Real MySql3

MINJU·2023년 9월 17일
RealMySQL
0

8.4 R-Tree 인덱스 ~ 8.6 함수 기반 인덱스

4. R-Tree 인덱스

R-Tree 인덱스는 공간 인덱스에 활용된다. 이는 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스이다.

R-Tree 인덱스의 기본적인 내부 메커니즘은 B-Tree와 흡사하다. 하지만 B-Tree는 인덱스를 구성하는 칼럼의 값이 1차원의 스칼라 값인 반면, R-Tree 인덱스는 2차원의 공간 개념 값이다.

GPS나 GIS와 같은 위치 기반의 서비스를 구현하는 방법 중 하나는 MySQL 의 공간 확장을 이용하는 것이다.

공간 확장에는 아래와 같은 세 가지의 큰 기능이 포함된다.

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

(1) 구조 및 특성

MySQL은 공간 정보의 저장 및 검색을 위해 여러 가지 "기하학적 도형 정보"를 관리할 수 있는 데이터 타입을 제공한다.

마지막의 GEOMETRY 타입은 아래 세 개 타입의 슈퍼 타입이다. POINT와 LINE, POLYGON 객체를 모두 저장할 수 있기 떄문이다.

공간 정보의 검색을 위한 R-Tree 알고리즘을 이해하려면 MBR 이라는 개념을 알아야 한다.

MBR(Minimum Bounding Rectangle)이란 "해당 도형을 감싸는 최소 크기의 사각형"을 의미한다.
이 사각형들의 포함 관계를 B-Tree 형태로 구현한 인덱스가 바로 "R-Tree"이다.


아래와 같은 도형(공간 데이터)이 있다고 해보자.

해당 도형의 MBR을 아래 세 개의 레벨로 나눠서 그려본 그림은 아래와 같다.

  1. 최상위 레벨 : R1, R2
  2. 차상위 레벨 : R3, R4, R5, R6
  3. 최하위 레벨 : R7 ~ R14

최하위 레벨은 각 도형 데이터의 MBR을 의미한다.
차상위 레벨은 중간 크기의 MBR(도형 객체의 그룹)이다.

최상위 레벨은 R-Tree의 루트 노드에 저장되고 차상위 그룹 MBR은 R-Tree의 브랜치 노드에, 그리고 각 도형의 객체(최하위 레벨)은 리프 노드에 저장된다.

(2) R-Tree 인덱스의 용도

공간 인덱스라고도 불리는 R-Tree 인덱스는 일반적으로 GPS 기준의 위도, 경도 좌표 저장에 주로 사용된다.

또한 CAD/CAM 소프트웨어 or 회로 디자인 ... 등과 같이 좌표 시스템에 기반을 둔 정보에는 모두 적용할 수 있다.

R-Tree는 각 도형의 포함 관계를 이용해 만들어진 인덱스이다. 따라서 포함 관계를 비교하는 함수(ex. ST_Contains(), ST_Within()..)로 검색을 수행하는 경우에만 인덱스를 이용할 수 있다.


"현재 위치로부터 5km 이내의 음식점 검색"

위의 상황에 맞는 검색을 진행하기 위해선 "기준점으로부터 반경 거리 5km 이내의 점들을 검색"하면 된다.

이를 위해 우선 사각 점선의 상자에 포함되는 점들을 검색한다.

여기서 ST_Contains(), ST_Within()은 다각형으로만 연산할 수 있으므로, 반경 5km를 그리는 원을 포함하는 MBR으로 포함 관계 비교를 수행한다.

//  ST_Contains(포함 경계 가진 도형, 포함되는 도형)
SELECT * 
FROM tb_location
WHERE ST_Contains(사각 상자, px);
// ST_Within(포함되는 도형, 포함 경계를 가진 도형)
SELECT * 
FROM tb_location
WHERE ST_Withins(px, 사각 상자);

이때, 원엔 포함되지 않지만 MBR엔 포함되는 P6을 빼고 조회하려면 조금 더 복잡한 비교가 필요하다

SELECT * 
FROM tb_location
WHERE ST_Contains(사각상자, px) // 공간 좌표 px가 사각 상에 포함되는지 비교
AND ST_Distance_Sphere(p, px) <= 5 *1000;




5. 전문 검색 인덱스

B-Tree 인덱스는 문서 전체가 아닌 일부를 인덱스 키로 활용한다. 따라서 전체 일치 or 좌측 일부 일치와 같은 검색만 가능하다.

그렇기 때문에, 문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문 검색에는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다.

문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘을 전문 검색 인덱스라고 한다. (알고리즘의 이름을 지칭하는 것은 아니다.)

(1) 인덱스 알고리즘

전문 검색에서는 키워드로 인덱스를 구축하나.

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

<1> 어근 분석 알고리즘

MySQL 서버의 전문 검색 인덱스는 아래 두 가지 중요한 과정을 거쳐서 색인 작업이 수행된다.

  • 불용어 처리
  • 어근 분석

불용어 처리는 검색에 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업이다.
보통 불용어를 상수로 정의해서 필터링 하는 경우가 많다. (DB화하여 사용자가 추가하거나 삭제할 수도 있다.)

어근 분석은 검색어로 선정된 단어의 원형을 찾는 작업이다.
MySQL 서버에서는 MeCab을 플러그인 형태로 사용할 수 있게 지원한다. 한글이나 일본어와 같은 경우에는 단어의 변형 자체는 거의 없기 때문에 사실 어근보다는 형태소를 분석해서 명사와 조사를 구분하는 기능이 더 중요하다!

(서구권 언어 형태소 분석기는 MongoDB의 Snowball)

(MeCab도 일본어를 위한 형태소 분석기인데 한글이 그나마 일본어와 비슷해서 이를 활용한다.)

Mecab을 MySQL 서버에 적용하는 방법은 어렵지 않지만, 한글의 문장구조를 인식시키고 학습 시키는 작업은 많은 시간이 걸린다.

<2> n-gram 알고리즘

시간이 많이 걸린다는 Mecab의 단점을 보완하기 위한 방법으로 n-gram 알고리즘이 도입되었다.

형태소 분석이 "문장을 이해하는" 알고리즘이면 n-gram은 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘이라고 할 수 있다.

n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다.

형태소 분석 보다는 단순하고, 국가별 언어에 대한 이해와 준비 작업이 없어진다.
하지만 만들어진 인덱스의 크기가 상당히 큰 편이다.

n-gram에서 n은 인덱싱할 키워드의 최소 글자 수를 의미한다.

주로 2-gram이 많이 사용된다.
이 방식에서 문장 내의 각 글자는 중첩해서 2글자씩 토큰으로 구분된다. 그리고 구분된 각 토큰을 인덱스에 저장하게 된다. (이때 중복된 토큰은 하나의 인덱스 엔트리로 병합되어 저장된다.)

To be or not to be. That is the question.

이란 문장이 있다고 하면,

토큰은 위와 같이 생성된다.

그리고 이렇게 생성된 토큰들에 대해 MySQL 서버는 불용어를 걸러내는 작업을 수행한다.

<3> 불용어 변경 및 삭제

불용어 처리 자체를 완전히 무시하거나, 사용자가 직접 불용어를 등록하는 방법이 더 권장된다.

[전문 검색 인덱스의 불용어 처리 무시]

  1. 스토리지 엔진에 관계없이 MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거하는 것.

-> MySQL 서버의 설정 파일 ft_stopword_file 시스템 변수에 빈 문자열을 설정하면 된다!
-> 해당 시스템 변수를 활용해서 사용자 정의 불용어를 적용할 수도 있다.

  1. InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 인덱스에 대해서만 불용어 처리를 무시하기.

-> innodb_ft_enable_stopword 시스템 변수를 off로 설정하면 된다.



[사용자 정의 불용어 사용]

  1. 불용어 목록을 파일로 저장하고, MySQL 서버 설정 파일에서 ft_stopword_file 에 파일 경로 등록하기

  2. (InnoDB 스토리지 엔진 사용하는 테이블의 전문 검색 엔진에서만 사용할 수 있음) 불용어의 목록을 테이블로 저장하는 방식

(2) 전문 검색 인덱스의 가용성

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

  1. 쿼리 문장이 전문 검색을 위한 문법을 사용
  2. 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유
CREATE TABLE tb_test (
	doc_id INT,
    doc_body TEXT,
    PRIMARY KEY(doc_id),
    FULLTEXT KEY fx_docbody (doc_body) WITH PARSER ngram
   ) ENGINE = InnoDB;

위와 같이 doc_body 칼럼에 전문 검색 인덱스를 생성했다고 해보면

SELECT *
FROM tb_test 
WHERE doc_body LIKE "%애플%";

이와 같은 검색 쿼리로도 원하는 결과를 얻을 수 있을 것.
하지만! 풀 테이블 스캔으로 쿼리를 처리하게 된다.

그래서 전문 검색 인덱스를 사용하려면 아래와 같이

SELECT *
FROM tb_test
WHERE MATCH(doc_body) AGAINST('애플' IN BOOLEAN_MODE);

MATCH() AGAINST() 구문으로 쿼리를 작성해야하며, 전문 검색 인덱스를 구성하는 칼럼들은 MATCH 절의 괄호 안에 모두 명시되어야 한다.

6. 함수 기반 인덱스

일반적으로는 칼럼 값의 일부 or 전체를 인덱스로 활용하는데 경우에 따라 칼럼 값을 변형해서 그를 인덱스로 사용해야할 떄도 있다.

이 경우엔 함수 기반의 인덱스를 활용하면 된다.
(인덱싱할 값을 계산하는 과정의 차이만 있지, 실제 인덱스 내부 구조 및 유지 관리 방법은 B-Tree와 비슷하다)

구현하는 방법은 아래의 두 가지로 구분할 수 있다.

  1. 가상 칼럼을 이용한 인덱스
  2. 함수를 이용한 인덱스

(1) 가상 칼럼을 이용한 인덱스

아래와 같이 사용자의 정보를 저장하는 테이블이 있다고 해보자

CREATE TABLE user (
	user_id BIGINT,
    firt_name VARCHAR(10),
    last_name VARCHAR(10),
    PRIMARY KEY (user_id)
    );

이때 first_name과 last_name을 합쳐서 검색해야하는 요건이 생겼다면

예전 버전에선 full_name이라는 칼럼을 추가하고 모든 레코드에 대해 이 칼럼값을 업데이트하는 작업을 거쳐야만했다.

하지만 MySQL 8.0 버전부터는 가상 컬럼을 추가하고 거기에 인덱스를 생성할 수 있게 됐다.

ALTER TABLE user
ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
ADD INDEX ix_fullname (full_name);

근데 이러한 방식은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 떄문에 실제 테이블의 구조가 변경된다는 단점이 있다.

(2) 함수를 이용한 인덱스

MySQL 8.0 부터는 테이블의 구조를 변경하지 않고 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.

CREATE TABLE user (
	user_id BIGINT,
    first_name VARCHAR(10),
    last_name VARCHAR(10),
    PRIMARY KEY (user_id),
    INDEX ix_fullname ((CONCAT(first_name, ' ', last_name)) );

함수를 직접 사용하는 인덱스는 테이블의 구조는 변경하지 않고, 계산된 결과값의 검색을 빠르게 만들어준다.

주의할 점은 함수 기반 인덱스를 제대로 활용하려면 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되어야 한다는 것이다.

profile
MINJU
이전 포스트

Real MySQL 2

다음 포스트

Real MySQL 4

0개의 댓글

관련 채용 정보