MySQL 인덱스(Index) & 파티션Partition & Full-Text Search

1.  인덱스

• Select시 빨리 추출 가능(수정, 변경 작업 많은 곳에 쓰면 성능 나빠질 수 있다. )
• 종류 : 클러스터형 인덱스(영어사전 같은 것), 보조 인덱스(책뒤 찾아보기 기능 같은 것)
• 클러스터형 인덱스 : 테이블 당 한 개만 생성, 보조 인덱스는 테이블 당 여러 개 생성 가능
• 클러스터형 인덱스는 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬
• primary key(클러스터형 인덱스)또는 Unique(보조 인덱스)에는 인덱스가 자동으로 생성됨( unique인데 notnull 붙이면 클러스터형 인덱스 됨, pk랑 중복 시 pk가 클러스터형 인덱스가 됨)
• 클러스터형 인덱스는 루트 페이지와 리프 페이지로 인덱스가 구성되어 있으며 인덱스 페이지의 리프 페이지는 데이터 그 자체이다.
• 보조 인덱스는 데이터 페이지를 건드리지 않고 별도의 장소에 인덱스 페이지를 생성한다.

  2.  인덱스 내부구조

• Btree -페이지 분할(insert, update, delete 작업 시)로 인해 성능 문제 생길 수 있다.
• 클러스터형 인덱스는 데이터 페이지 자체도 인덱스 페이지에 포함됨(영어사전 책 자체가 인덱스)- datafree가 줄어든다. 
• 보조형 인덱스는 데이터 자체는 건드리지 않고 별도의 공간에 인덱스가 추가 따라서 입력 시 보조형 인덱스보다 클러스터형 인덱스일 경우 성능에 주는 부하가 더 크다.
• 클러스터형 인덱스가 보조형 인덱스보다 더 빠름, 그러나 클러스터형 인덱스의 경우 입력, 수정 시 페이지 분할 일어나면 성능에 영향 미침
• 인덱스를 검색하기 위해 일차 조건은 WHERE절에 해당 인덱스를 생성한 열의 이름이 나와야 한다. (MySQL이 인덱스 필요 없다고 생각하면 실행 안 할 수도 있다. 
• Btree : 루트, 브랜치, 리프 노드

3. 인덱스 스캔 방식

• Index Range Scan : 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후 리프 블록을 필요한 범위만 스캔함. 
• Index Full Scan : 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
• Index Unique Scan : 수직적 탐색만으로 데이터를 찾는 스캔 방식으로 Unique 인덱스를 = 조건으로 탐색하는 경우에 작동  

4. 인덱스의 성능 비교

  (클러스터형 인덱스 존재하는 경우, 보조 인덱스 존재하는 경우, 인덱스 존재하지 않는 경우) 

• 인덱스는 열 단위로 생성된다. 
• WHERE절에서 사용되어야 한다. WHERE절에 사용되더라도 자주 사용되어야 한다. 
• Insert, Update, Delete 얼마나 자주 일어나는지 고려해야 

5. 파티션(Partition)

 

대량의 테이블을 물리적으로 여러 개의 테이블로 쪼개는 것을 말한다. 예를 들어 수십억 건의 테이블에 쿼리를 수행할 때, 비록 인덱스를 사용한다고 해도 테이블의 대용량으로 인해 데이터베이스에 큰 부담이 된다. 따라서 이럴 때 하나의 테이블이 10개의 파티션으로 나누어 저장되게 하면 부담이 많이 줄어든다. 

 

1) 파티션 테이블을 지정하는 문법

 우선 파티션 테이블에는 PK를 지정하면 안 된다.  파티션 테이블 지정하는 문법은 테이블 정의가 끝나는 부분에 PARTITON BY RANGE(열 이름)으로 지정한다. 

create table PartiRangeTest (
  studentno varchar(7) not null,
  enteryear smallint not null,
  studentname varchar(31) not null )
  
  Partition by RANGE(enteryear) (
    partition p1 values less than(2000),
    partition p2 values less than(2010),
    partition p3 values less than MAXVALUE
);

2) 제약 조건 

만약 PK를 지정하려면 파티션에 사용되는 열도 함께 PK로 지정해야 한다. 

 

3) 파티션을 분리, 합치기, 삭제하기 위해서는 

-- 분리
Alter table PartiRangeTest
    REORGANIZE Partition p3 INTO (
        partition p3 values less than (2020),
        partition p4 values less than MAXVALUE
    );

optimize table PartiRangeTest;

-- 합치기
alter table PartiRangeTest
    reorganize partition p1, p2 into (
        partition p1_2 values less than (2010)
    );


-- 삭제하기 
alter table PartiRangeTest drop partition p1_2;

optimize 나중에 해줘야 한다. 

6. Full-Text Search

전체 텍스트 검색이란 긴 문자로 구성된 구조화되지 않은 텍스트 데이터 등을 빠르게 검색하기 위한 것이다.

SELECT * FROM 신문기사_테이블 WHERE 신문기사내용 LIKE '%교통%'
//이렇게 검색할 경우 인덱스를 사용할 수 없게 된다. 
//따라서 Full-Text Index를 활용해서 첫 글자뿐만 아니라 중간의 단어나 문장으로도 인덱스를 생성하게 하여 검색속도 향상시킬 수 있다.

1) 특징

•  전체 텍스트 인덱스는 InnoDB를 지원한다. 
•  전체 텍스트 인덱스는 char, varchar, text의 열에만 생성이 가능하다. 
•  인덱스 힌트 사용이 일부 제한된다. 
•  여러 개 열에 FULLTEXT 인덱스를 지정할 수 있다. 
• 중지 단어 직접 추가해주어야 한다. MySQL, MariaDB 한글은 지원 안 함

2) 전체 텍스트 인덱스를 사용하기 위해서 WHERE절에 MATCH() AGAINST()를 사용하면 된다. 

 

BOOLEAN MODE의 경우

SELECT * FROM newspaper WHERE MATCH(article) AGAINST('영화*' IN BOOLEAN MODE);

//영화가 들어간 모든 결과를 검색하고 싶을 때



SELECT * FROM newspaper WHERE MATCH(article) AGINST('영화 배우 + 공포' IN BOOLEAN MODE);

//'영화배우' 단어가 들어가 있는 기사 중에 '공포'의 내용이 들어간 경우

 

 

※참고자료※

https://www.youtube.com/watch?v=rmYhahiLHnE&list=PLEOnZ6GeucBU7FR26mn9d3Mxqc8V81yHX&index=25