인덱스(Index)란?

정의

데이터베이스의 데이터 검색 속도를 높이기 위한 자료 구조

 

 

인덱스를 왜 써야 할까?

인덱스를 사용하지 않은 칼럼을 조회해야 하는 상황이라면? 테이블 전체를 읽어야 하는 Full Scan을 수행해야 하며, Full Scan은 테이블 전체 내용을 하나하나 비교하여 탐색하기 때문에 처리 시간이 오래 걸립니다. 따라서 내가 원하는 자료를  빠르게 검색하기 위해 도와주는 인덱스 사용을 고려해보면 좋을 거 같습니다.

 

 

종류

MariaDB의 인덱스 종류는 크게 기본, 고유, 일반, 전체 텍스트 인덱스 총 4가지가 있습니다.

 

 

기본 인덱스 (null이 아닌 고유)

• 기본 인덱스는 고유하며 절대 null 일 수 없습니다.
• 항상 하나의 레코드만을 식별하며, 각 레코드를 표시해야 합니다.
• 각 테이블에는 기본 키가 하나만 있을 수 있습니다.
• InnoDB 테이블에서 모든 인덱스는 기본 키를 접미사로 포함합니다. 따라서 이 저장 엔진을 사용할 때 기본 키를 최대한 작게 유지하는 것이 특히 중요합니다.
• 기본 키가 없고 고유 인덱스가 없는 경우 InnoDB는 사용자에게 보이지 않는 6바이트 클러스터링 인덱스를 생성합니다.
• AUTO_INCREMENT 특성은 새 행에 대한 고유한 ID를 생성하는 데 사용할 수 있으며, 일반적으로 기본 키와 함께 사용된다.
• 기본 키는 일반적으로 CREATE TABLE 문을 사용하여 테이블을 생성할 때 추가됩니다.
• 다음은 ID 필드에 기본 키를 생성하는 예시입니다. ID 필드는 NOT NULL로 정의되어야 하며, 그렇지 않으면 색인을 작성할 수 없다는 점에 유의하십시오.

CREATE TABLE `Employees` (  
 `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,  
 `First_Name` VARCHAR(25) NOT NULL,  
 `Last_Name` VARCHAR(25) NOT NULL,  
 `Position` VARCHAR(25) NOT NULL,  
 `Home_Address` VARCHAR(50) NOT NULL,  
 `Home_Phone` VARCHAR(12) NOT NULL,  
 PRIMARY KEY (`ID`) ) ENGINE=Aria;

 

 

고유 인덱스(unique and null일 수 있음)

• 고유 지수는 고유해야 하지만 null일 수 있습니다. 따라서 각 키 값은 하나의 레코드만 식별하지만 각 레코드를 나타낼 필요는 없습니다.
• 인덱스는 둘 이상의 열을 포함할 수 있습니다. MariaDB는 전체 인덱스를 사용할 수 없는 경우 인덱스의 맨 왼쪽 부분에 하나 이상의 열을 사용할 수 있습니다.
• 고유 인덱스가 후행 패드 문자가 벗겨지거나 무시되는 열로 구성된 경우 후행 패드 문자 수에 따라 값이 다른 열에 삽입하면 중복 키 오류가 발생합니다.
• 예를 들어 기본 키뿐만 아니라 직원_코드 필드에 고유한 키를 만들려면 다음과 같이 사용해야 합니다.

CREATE TABLE `Employees` (  
 `ID` TINYINT(3) UNSIGNED NOT NULL,  
 `First_Name` VARCHAR(25) NOT NULL,  
 `Last_Name` VARCHAR(25) NOT NULL,  
 `Position` VARCHAR(25) NOT NULL,  
 `Home_Address` VARCHAR(50) NOT NULL,  
 `Home_Phone` VARCHAR(12) NOT NULL,  
 `Employee_Code` VARCHAR(25) NOT NULL,  
 PRIMARY KEY (`ID`),  
 UNIQUE KEY (`Employee_Code`) ) ENGINE=Aria;

 

 

일반 인덱스

• 인덱스가 반드시 고유할 필요는 없습니다.
• 예를 들면 다음과 같습니다.

 

 

전체 텍스트 인덱스

• MariaDB는 전체 텍스트 색인화 및 검색을 지원합니다.
• FULLTEXT 유형의 인덱스며, 필드에서 텍스트 부분을 검색할 때 더 많은 옵션을 허용합니다.
• InnoDB 테이블이 있는 MariaDB 10.0.5부터 Mroonga 테이블이 있는 MariaDB 10.0.15까지 MyISAM 및 Aria 테이블에서만 사용할 수 있으며, CHAR, VARCAR 또는 TEXT 열에 대해서만 생성할 수 있습니다.
• 분할된 테이블은 스토리지 엔진이 전체 텍스트 색인을 지원하더라도 전체 텍스트 인덱스를 포함할 수 없습니다.
• 대용량 데이터 세트의 경우 기존 FULLTEXT 인덱스가 있는 테이블에 데이터를 로드하는 것보다 FULLTEXT 인덱스가 없는 테이블에 데이터를 로드한 후 인덱스를 생성하는 것이 훨씬 빠릅니다.
• 전체 텍스트 검색은 MATCH() ... AGAINST 구문을 사용하여 수행됩니다.
• MATCH()는 검색할 열의 이름을 지정하는 쉼표로 구분된 목록을 취합니다. 
• ANTER는 검색할 문자열과 수행할 검색 유형을 나타내는 선택적 수정자를 사용합니다.
• 검색 문자열은 변수나 열 이름이 아닌 리터럴 문자열이어야 합니다.

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

 

• 제외된 결과
  • 일부 단어는 제외됩니다.
  • 길이가 4자(3자 이하) 미만인 단어는 전체 텍스트 색인에 저장되지 않는다.
  • 이 값은 ft_min_word_length 시스템 변수(또는 InnoDB의 경우 innodb_ft_min_token_size)를 변경하여 조정할 수 있다.
  • 길이가 84자 이상인 단어도 전체 텍스트 색인에 저장되지 않는다.
  • 이 값은 ft_max_word_length 시스템 변수(또는 InnoDB의 경우 innodb_ft_max_token_size)를 변경하여 조정할 수 있다.
  • 스톱 워드는 IN BOOLN MODE를 사용하지 않는 한 검색 결과에 반영되지 않는 "Once" 또는 "then"과 같은 일반적인 단어의 목록이다. 기본 목록을 변경하는 방법에 대한 자세한 내용은 스톱 워드를 참조하십시오.
  • MyISAM/Aria 테이블과 InnoDB 테이블의 스톱 워드 목록은 다를 수 있다.
  • MyISAM/Aria 전체 텍스트 인덱스에 대해서만 행의 절반 이상에 단어가 나타나면 전체 텍스트 검색 결과에서도 제외된다.
  • InnoDB 인덱스의 경우 커밋된 행만 표시되며, 현재 트랜잭션의 수정 내용은 적용되지 않는다.

 

전체 텍스트 검색 유형

• IMT-2000 3GPP-자연어 모드
  • IN NUAL LUGNER MODE는 전체 텍스트 검색의 기본 유형이며 키워드를 생략할 수 있다. 특별한 연산자는 없으며, 검색은 하나 이상의 쉼표로 구분된 키워드로 구성된다.
  • 검색은 관련성의 내림차순으로 반환된다.

 

• 부울 모드
  • 부울 검색은 다음과 같은 다수의 특수 연산자를 사용할 수 있다.
  • 검색은 관련 순서에 따라 반환되지 않으며, 50% 제한도 적용되지 않습니다.
  • 스톱 워드와 단어 최소 및 최대 길이는 여전히 평소와 같이 적용됩니다.

 

 

• 쿼리 확장 모드
  • 쿼리 확장 검색은 자연어 검색의 수정입니다. 
  • 검색 문자열은 일반 자연어 검색을 수행하는 데 사용됩니다.
  • 검색에 의해 반환된 가장 관련성이 높은 행의 단어가 검색 문자열에 추가되고 검색이 다시 수행되며, 쿼리는 두 번째 검색에서 행을 반환합니다.
  • 쿼리 확장 또는 쿼리 확장 한정자가 있는 IN NUAL Language MODE는 쿼리 확장 검색을 지정합니다.
  • 예를 들어 마리아 DB가 데이터베이스라는 데이터 내의 묵시적 지식 등에 의존할 때 유용할 수 있습니다.

 

 

 

마무리

오늘은 cs 기본 지식 중 하나인 인덱스에 대해서 공부해봤습니다.

공부를 해보니 생각보다 양이 방대해서 좀 당황스러웠네요. 예상했던 부분인 클러스터링, B-Tree 인덱스 이런 내용이 추가될 줄 알았는데, 공식 문서를 보니 책 또는 블로그와는 다른 디데일 한 부분들이 있어서, 공식문서 기준으로 정리해봤습니다.  다른 내용들은 다른 포스팅으로 찾아뵙겠습니다.

아직 부족하거나 틀린 부분이 있을 수도 있으니 주의하시면 좋을 거 같습니다.

이번 포스팅은 마무리하면서 다음 포스팅에서 뵙겠습니다.