230329 TIL

1.  Index

 1)주의 사항

• 주의 사항 
  • 무분별한 인덱스는 용량을 차지하고, 옵티마지어의 최적화를 낮추는 결과를 얻는다
  • 특정 컬럽들을 조건으로 걸 때, 정렬된 익덱스의 순서와 맞지 않는다면 추가적인 비용 발생
• 인덱스가 필요 없는경우
  • 데이터가 적은경우
  • 조회보다 삽입, 수정, 삭제 처리가 많은 테이블인 경우
  • 조회 결과가 전체행의 15% 이상을 읽어들일 것으로 예상되는 경우
• 익덱스가 필요한 경우
  • 데이터가 많이 쌓이고, 조회 속도가 느린 경우
  • 조회 결과가 전체 데이터의 3~5% 정도되면 인덱스 스캔이 효율적이고 적은 비용으로 가능
  • Access 대상 범위가 전체 범위의 3~5% 정도면 인덱스 스캔보다 풀 스캔이 훨씬 유리

 2)Clusted Indext(클러스터형 인덱스)

• 테이블당 한 개만 생성 가능
• 행 데이터를 인덱스로 지정한 열에 맞춰 자동 정령
• primary 키 설정시 자동으로 생성되며, 컬럼은 데이터 변경시 항상 정렬을 유지
• 클러스터 인덱스 사용 시 모든 보조 인덱스가 primary키를 포함한다
• primary 키의 크기가 커질수록 보조 인덱스의 크기도 커진다

 3)Non-Clusted Indext(비클러스터형 인덱스)

• 테이블당 여러 개를 생성 가능
• 맨뒤의 찾아보기가 있는 일반 책과 같다
• 테이블의 페이즈를 정렬하지 않고 새로운 공간을 할당하므로, 클러스터 인덱스보다 많은 공간 차지
• 데이터 행과 분리된 구조를 가진다.

ㄱ. 단일 인덱스

• 데이터가 많지 않으며 조건에 걸리는 컬럼이 적은 경우에 주로 사용

CREATE INDEX 인덱스명 (컬럼명);
CREATE INDEX 인덱스명 ON 테이블명(컬럼명);
ALTER TABLE 테이블명 ADD INDEX 인덱스명(컬럼명);

ㄴ. 복합 인덱스

• 테이블 데이터를 기준으로 새로운 인덱스 공간을 생성 및 데이터를 정렬하므로 공간 사용량이 증가

CREATE INDEX 인덱스명 (컬럼명, 컬럼명);
CREATE INDEX 인덱스명 ON 테이블명(컬럼명, 컬럼명...);
ALTER TABLE 테이블명 ADD INDEX 인덱스명(컬럼명, 컬럼명...);

ㄷ. 커버드 인덱스

• 커버드 쿼리라고도 한다
• 쿼리가 인덱스 접근으로 인해 테이블 접근이 필요하지 않는 경우 사용

CREATE INDEX IDX_MULTI (C1, C2);

SELECT C1, C2 FROM IDX_MULTI WHERE C1 = 'value1' and C2 = 'value2';

 

ㄹ. Column store Index(열 저장 인덱스)

• 열 저장소 인덱스는 방대한 데이터 웨어하우징 진리표를 삭제하고 질문하는 것과 관련하여 표준 유형의 인덱스가 있는 SQL 서버의 인덱스 유형 중 하나이다.
• Column-store 인덱스는 작은 인상 안에 정보를 넣을 수있도록 하여 속도를 높이는데 도음이 된다.
• 이를 사용하면 클라이언트가 기존의 열 정렬 용량과 달리 조회 실행이 몇 배 높은 IO(input/output)를 얻을 수 있다.

ㅁ. Filterd Index(필터링된 인덱스)

• 필터링된 인덱스는 열에 값의 하위 집합에 대한 질문에 적용 가능한 숫자가 몇 개 없을 때 만들어지는 SQL Server의 인덱스 유형 중 하나입니다.
• 테이블이 이기종 데이터 행으로 구성되어 있는 경우, 적어도 한 종류의 데이터에 대해 SQL에 분리된 목록이 만들어 진다.

ㅂ. Hash Index(해시 인덱스)

• 해시 인덱스는 포인터 또는 N 버킷의 배열과 각 슬롯 또는 버킷의 행을 포함하는 슬록에 있는 SQL 서버의 인덱스 유형 중 하나입니다.
• N은 여러 버킷이고 K는 임계값인 해시 함수 F(K,N)을 사용한다.
• 용량은 해시 인덱스의 버킷과 관련된 키를 나타내며 해시 인덱스의 모든 버킷은 8비트로 구성되며 기본 섹션의 연결된 런다운 메모리 주소를 저장하는데 사용한다.

ㅅ. Unique Index(고유 인덱스)

• SQL 서버의 고유 인덱스는 인덱스 키에 복사 의존이 포함되어 있지 않는지 확인하고 보장하며 이러한 행을 따라 클라이언트가 테이블의 각 행이 어느쪽이든 예외적인지 검사할 수 있도록 한다.

2.  B-Tree와 B+Tree 차이점

 1)구조적 차이

• B-Tree는 루트로부터 리프까지의 거리가 일정하며, root와 internal, leaf에도 데이터가 담긴다.
• B+Tree는 리프 노드를 제외하고 값을 담아두지 않기 떄문에 하나의 블록에 더 많은 key들을 담아 둘 수 있다.
• 상세한 내용은 참조 2) 를 보자

 

3.  Fulltext Index

• 보통 MySQL에서 텍스트 문자열을 검색 시 like 나 instr 연산자를 통해 원하는 텍스트를 필터링하여 조회한다. 하지만 이 방법은 내용이 많아지면 성능이 떨어진다.
• 이것을 전체 텍스트 검색 기능이 해결해준다.
• 전체 텍스트 검색은 첫 글자뿐 아니라 중간의 단어나 문장으로도 인덱스를 생성해주어, 전체 텍스트 인덱스를 통해 순식간에 검색 결과를 가져올 수 있다.
• MySQL의 부가적인 기능으로 InnoDB와 MyISAM 테이블만 지원하며, char/vachar/text 타입 문자만 인덱싱 가능하다.
• 여러개의 열에 풀텍스트 인덱스 지정이 가능하다.

 1)사용방법

• 생성방법
  • CREATE FULLTEXT INDEX 인덱스이름 ON 테이블이름(열이름);
  • ALTER TABLE 테이블이름 ADD FULLTEXT (열이름);
• 삭제 방법
  • ALTER TABLE 테이블이름 DROP INDEX FULLTEXT (열이름);
  • DROP INDEX 인덱스이름 ON 테이블 이름
• 확인 방법
  • SHOW INDEX FROM 테이블이름;

 2)전체 텍스트 검색

• SELECT문의 WHERE 절에 MATCH(), AGAINST() 특수 메서드를 사용해야 검색이 된다.

 ㄱ. 자연어 검색

• 특별한 옵션을 지정하지 않거나 뒤에 in natural language mode를 붙이면 자연어 검색을 한다.
  • 자연어 검색은 단어가 정확한 단어만 검색해준다. 
  • '영화'라는 단어만 검색되며 '영화는', '영화가' .. 등 능동적인 검색 불가

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

SELECT * FROM newspaper WHERE MATCH(article) AGAINST('영화' in natural language mode);

SELECT * FROM newspaper WHERE MATCH(article) AGAINST('영화 배우'); 
-- ‘영화’ 또는 ‘배우’ 두 단어 중 하나가 포함된 기사 검색.

 ㄴ. 불린 모드 검색

• 자연어 검색 문제를 해결하기 위해 LIKE 연산자에서 %를 쓰듯이 불린모드검색을 사용한다.
• 단어나 문장이 정확히 일치하지 않는 것도 검색한다.
• 뒤에 in boolean mode 옵션을 붙여주면 된다.
• 필수인 +, 제외하기 위한-, 부분 검색을 위한 * 연산자 등 다양한 연산자를 지원한다.

검색 필수(+)
SELECT * FROM newspaper
WHERE MATCH(article) AGAINST('영화 +액션' IN BOOLEAN MODE);
> 영화를 찾되 반드시 액션이 들어가 있는 열

검색 제외(-)
SELECT * FROM newspaper
WHERE MATCH(article) AGAINST('영화 -액션' IN BOOLEAN MODE);
> 영화를 찾되 액션은 안들어가있는 열


검색 부정(~)( - 보다 부드러운 방식 )
SELECT * FROM newspaper
WHERE MATCH(article) AGAINST('영화 ~액션' IN BOOLEAN MODE);
> ‘영화’를 찾되 ‘액션’이 없는 열보다 ‘액션’이 있는 열이 아래 순위


부분 검색(*)
SELECT * FROM newspaper
WHERE MATCH(article) AGAINST('영화*' IN BOOLEAN MODE);
> ‘영화를’, ‘영화가’, ‘영화는’ 등

부분 검색 “” 안에 있는 구문과 정확히 동일한 철자의 구문
SELECT * FROM newspaper
WHERE MATCH(article) AGAINST("재밌는 영화" IN BOOLEAN MODE);
> “재밌는 영화”, “재밌는 영화가” 등
> “재밌는 한국 영화”, “재밌는 할리우드 영화” 불가

 ㄷ. 쿼리 확장 검색

• 1단계에서 자연어 검색 수행 후, 결과에 매칭된 행을 기반으로 검색 문자열을 재구성하여 두 번째 검색을 수행
• 이는 1단계 검색에서 사용한 단어와 연관성이 있는 단어가 1단계 검색에서 매칭된 결과에 나타난다는 가정을 전제로 한다

select * from tbl_full where match(content) against('내용*' WITH QUERY EXPANSION);

 ㄹ. 검색 단어 제한 수 풀기

• mysql은 기본값으로 검색 가능 단어의 숫자는 3이다.
• 즉 3글자 이상만 전체 텍스트 검색이 되고 두 글자 단어는 안된다.
• 따라서 검색 가능 단어의 숫자를 확인하고 설정에서 수정해줘야한다.
• 참조 3)을 통해 해결하자.

 ㅁ. 중지 단어(stopwords)

• 긴 문장을 인덱스로 생성할 때 무시할 만한 단어는 전체 텍스트 인덱스로 생성하지 않는 편이 좋다.
• 예를 들면 "이번 선거는 아주 중요한 행사이므로 모두 꼭 참여 바랍니다"에서
  • 이번, 아주, 모두, 꼭 같은 부사는 굳이 검색할 이유가 없으므로 제외하는 게 효율 적이다.
• mysql은 information_schema.innodb_ft_default_stopword 테이블에 36개의 중지 단어를 미리 가지고 있지만 피룡하다면 사용자가 별도의 테이블에 중지단어를 추가한뒤에 적용시킬 수 도 있다.
• 참조 3)을 통해 해결하자.

 

 

 

5.  참조

 1)인덱스 종류 : https://azderica.github.io/00-db-index/

[DB] DB의 INDEX 개념정리 - Azderica

 2)B-Tree와 B+Tree의 차이 : https://algopoolja.tistory.com/122

B-Tree 와 B+Tree 의 차이점

 3)fulltextindex : https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%ED%92%80%ED%85%8D%EC%8A%A4%ED%8A%B8-%EC%9D%B8%EB%8D%B1%EC%8A%A4Full-Text-Index-%EC%82%AC%EC%9A%A9%EB%B2%95