8장. 인덱스

[im-gnar]

[im-gnar]

https://www.notion.so/8-26c42b507bf7424798fe38a93f45eeed

[SooKim1110]

인덱스 8.5~8.10

전문 검색 인덱스

B-Tree 인덱스 알고리즘은 크지 않은 데이터에 대한 인덱싱 알고리즘 (MySQL InnoDB는 3072바이트까지 잘라서 인덱스 키로 사용)

전체 일치 혹은 좌측 일부 일치같은 검색만 가능하다

문서 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하기 위해선 전문 검색(Full Text Search) 인덱스 사용

인덱스 알고리즘

문서 본문에서 사용자가 검색할 키워드를 분석해내고, 빠른 검색용으로 사용할 수 있게 키워드로 인덱스를 구축

어근 분석 알고리즘

1. 불용어 처리: 가치 없는 단어 제거
2. 어근 분석: 검색어로 선정된 단어의 원형을 찾음. 형태소 분석기 사용

n-gram 알고리즘

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

본문을 몇 글자씩 잘라서 인덱싱

n은 인덱싱할 키워드의 최소 글자 수를 의미한다

1. 토근 생성: 띄어쓰기 기준으로 단어를 구분하고, n글자씩 중첩해서 토큰으로 분리
2. 불용어 제거: 불용어와 동일하거나 불용어를 포함하는 경우 제거. information_schema.innodb_ft_default_stopword 에 불용어 내장
3. 토근을 B-Tree 인덱스에 저장

불용어 변경 및 삭제

1. MySQL 서버의 모든 전문 검색 인덱스에 대해

시스템 변수에 빈 문자열 설정 (ft_stopword_file = '' ) 하거나 사용자 정의 불용어 적용

2. InnoDB 사용하는 테이블의 전문 검색 인덱스에 대해

시스템 변수 off (innodb_ft_enable_stopword=OFF )

불용어의 목록을 테이블로 저장(innodb_ft_server_stopword_table, innodb_ft_user_stopword_table)

전문 검색 인덱스의 가용성

전문 검색 인덱스를 사용하기 위한 조건 2개

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

CREATE TAGLE tb_test (
	doc_id INT,
	doc_body TEXT,
	PRIMARY KEY (doc_id),
	FULLTEXT KEY fx_docbody (doc_body) WITH PARSER ngram
) ENGINE=InnoDB;

SELECT * FROM tb_test WHERE doc_body LIKE '%애플%';
SELECT * FROM tb_test WHERE MATCH(doc_body) AGAINST('애플' IN BOOLEAN MODE);

+) Search Modifier

1. IN NATURAL LANGUAGE MODE

입력된 검색어의 키워드가 얼마나 많이 포함되어 있는지에 따라 매치 스코어 결정

2. IN BOOLEAN MODE

각 키워드의 포함/불포함 비교를 수행하고, 결과를 True,False로 연산하여 최종 일치 여부를 판단 (+ AND, - NOT, 없음 OR)

3. WITH QUERY EXPANSION

2번 검색. 처음에는 기존 검색 문구로 검색하고, 이 결과를 바탕으로 매칭된 행에 포함된 다른 단어들을 포함하여 2번째 검색.

(노이즈를 증가시키기 때문에 검색하려는 문구가 짧을 때만 사용)

https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

https://hoing.io/archives/16853#6-1_IN_NATURAL_LANGUAGE_MODE

+) ElasticSearch

차이점은?

언제 ElasticSearch를 사용해야하나?

https://stackoverflow.com/questions/41892179/elastic-search-full-text-vs-mysql-full-text

함수 기반 인덱스

칼럼의 값을 변형해서 인덱스를 구축해야할 때 함수 기반의 인덱스 활용

인덱스의 내부 구조 및 유지 관리 방법은 B-Tree 인덱스와 동일

가상 칼럼을 이용한 인덱스

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

full_name 가상 칼럼을 생성하고, 인덱스를 생성.

SELECT * FROM user WHERE full_name="Matt Lee";

Full_name 칼럼에 대한 검색은 ix_fullname 인덱스를 사용해 실행 계획이 만들어진다.

가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내서 실제 테이블 구조가 변경된다는 단점이 있다

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

https://stricky.tistory.com/519

Virtual vs Stored

stored를 사용하는 이유는 데이터를 테이블에 저장할 필요는 없는데, 그 값을 이용해서 무언가는 할때?

함수를 이용한 인덱스

테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스 생성

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))
)

조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되어야 한다.

<주의> 가상 칼럼과 함수를 이용한 인덱스는 내부적으로 동일한 구현 방법을 사용한다. 그러므로, 둘의 성능차이는 발생하지 않는다.

멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 값을 가진다

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

일반적으로는 정규화에 위배되는 형태이지만, JSON 타입을 지원하기 시작하면서 JSON 배열 타입 필드에 저장된 원소들에 대한 인덱스 요건이 발생

CREATE TABLE user (
	user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
	credit_info JSON,
	INDEX mx_creditscores (CAST(credit_info -> '$.credit_scores' AS UNSIGNED ARRAY))
)

SELECT * FROM user WHERE 360 MEMBER OF (credit_info->'$.credit_scores')

MEMBER OF()

JSON_CONTAINS()

JSON_OVERLAPS()

클러스터링 인덱스

클러스터링은 테이블의 레코드를 프라이머리 키를 기준으로 비슷한 것끼리 묶어서 저장하는 형태로 구현 (테이블 당 클러스터링 인덱스 하나)

프라이머리 키 값에 의해 레코드의 저장 위치가 결정되어서, 인덱스 알고리즘이라기 보다 테이블 레코드의 저장 방식이라고 볼 수 있다

InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르며, 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다

B-Tree의 리프노드와 달리 레코드의 모든 칼럼이 같이 저장되어 있다.

[클러스터링 테이블 구성 기준]

1. 프라이머리 키가 있으면 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스 중에서 첫번째 인덱스를 선택
3. 자동으로 유니크한 값을 가지도록 증가하는 칼럼을 내부적으로 추가한 후 선택

세컨더리 인덱스에 미치는 영향

프라이머리 키 값에 의해 레코드 저장 위치가 결정되므로 InnoDB에서 세컨더리 인덱스는 실제 저장된 주소가 아닌(실제 주소라면 클러스터링 키 값이 변경될 때마다 모든 인덱스의 주솟값을 변경해야한다), 프라이머리 키 값을 저장하도록 구현되어 있다.

MyISAM: 인덱스를 검색해서 레코드 주소를 확인 후, 주소를 이용해 최종 레코드를 가져온다

InnoDB: 인덱스를 검색해 레코드의 프라이머리 키 값을 확인한 후, 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져온다

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

빠른 읽기, 느린 쓰기 (웹 서비스에 적합)

장점

1. 프라이머리 키로 검색할 때 처리 성능이 빠름 (특히, 범위 검색하는 경우)
2. 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있어, 인덱스만으로 처리될 수 있는 경우가 많음(커버링 인덱스 - 쿼리를 충족하는데 필요한 모든 데이터를 갖는 인덱스)

단점

1. 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 가져서, 클러스터링 키 값이 클 경우 전체적인 인덱스 크기가 커짐
2. 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 검색해야하므로 처리 성능 느림
3. INSERT할 때 프라이머리 키에 의해 레코드 저장 위치가 결정되므로 처리 성능 느림
4. 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요해서 처리 성능이 느림

클러스터링 테이블 사용 시 주의사항

• 모든 세컨더리 인덱스가 프라이머리키 값을 포함하므로, 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다. 저장 공간이 늘어날 뿐 아니라, 인덱스가 커질수록 같은 성능을 내기 위해 메모리가 더 필요해지므로 프라이머리 키를 신중히 선택해야한다
• 프라이머리 키는 AUTO-INCREMENT 보다는 업무적인 칼럼으로 생성. 검색 성능이 좋으므로 칼럼 크기가 크더라도 해당 레코드를 대표할 수 있는 칼럼을 프라이머리 키로 설정하는 것이 좋다
• 프라이머리 키는 반드시 명시할 것. 자동으로 내부적으로 일련번호 칼럼이 추가되긴 하지만 사용자가 접근할 수 없으므로 사용자가 사용할 수 있는 값을 설정
• AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우. 세컨더리 인덱스가 필요하고, 프라이머리 키의 크기가 긴 경우 AUTO_INCREMENT 칼럼을 추가하고 이를 프라이머리 키로 설정. 로그 테이블같은 INSERT 위주 테이블은 이러한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다(왜???)

+) Clustered Index VS Non-clustered Index

https://teraphonia.tistory.com/681

유니크 인덱스

테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는 제약 조건

MySQL에서는 인덱스 없이 유니크 제약만 설정할 수 없다

유니크 인덱스와 일반 세컨더리 인덱스의 비교

둘은 인덱스 구조상 차이점이 없다

• 읽기: 읽어야할 레코드 건수가 같다면 성능상 차이가 적다
• 쓰기: 유니크 인덱스의 키 값을 쓸 때 중복 체크를 해야하므로 일반 세컨더리 인덱스보다 쓰기가 느리다. MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때 읽기 잠금을 사용하고, 쓰기를 할 때 쓰기 잠금을 사용하는데, 이 과정에서 데드락이 빈번히 발생한다. 유니크 인덱스는 중복 체크를 해야 하므로 인덱스 키 저장을 버퍼링하는 체인지 버퍼를 사용하지 못한다. 그러므로, 유니크 인덱스는 변경 작업이 일반 세컨더리 인덱스보다 느리다

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

• 일반 다른 세컨드 인덱스나 프라이머리 키와 같은 역할을 하므로 중복해서 생성할 필요는 없다
• 유일성이 꼭 보장돼야 하는 칼럼에 대해서는 유니크 인덱스를 생성하자. 필요하지 않다면, 유니크하지 않은 세컨더리 인덱스를 생성하자

외래키

외래키는 InnoDB에서만 생성할 수 있으며, 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다

[외래키 관리 특징]

• 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다
• 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)를 발생시키지 않는다

=> 데이터베이스에서 외래 키를 물리적으로 생성하려면 이러한 잠금 경합까지 고려해 모델링을 진행하는 것이 좋다. 참조키가 부모 테이블에 있는지 확인하기 위해 연관 테이블에 읽기 잠금을 걸어야하고, 그만큼 쿼리의 동시처리에 영향을 미친다

[ruthetum]

https://github.com/ruthetum/study/blob/main/db/real-mysql/ch08-index.md

그 외에 같이 읽어보면 좋은 내용

• 전문검색(Full Text Search) 인덱스를 많이 사용하는가?
• DB만 있으면 되는데, 왜 굳이 검색엔진?

[ruthetum]

클러스터링 인덱스

세컨더리 인덱스가 필요하면서 프라이머리 키의 크기가 긴 경우 AUTO_INCREMENT 칼럼을 추가하고 이를 프라이머리 키로 설정. 로그 테이블같은 INSERT 위주 테이블은 이러한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다(왜???)

• pk 크기가 크면 전체적으로 인덱스 크기가 커짐

  • pk 뿐만 아니라 다른 인덱스에도 영향
  • 결국 그만큼 메모리가 더 필요함

• 또한 인덱스 크기가 커진다는 것은 조회 시 페이지에 담을 수 있는 레코드의 수가 줄어드는 것

  • 그만큼 더 많이 조회해야 함
  • 물론 insert 위주면 문제가 덜함

내용을 정리하면 결국 단순 insert하는 테이블인데 세컨더리 인덱스가 필요하면서, pk가 길다면 인덱스의 크기가 비대해져서 오히려 성능 저하를 유발