인덱스(4) : 생성, 삭제, 사용, 테스트하기 + 실행계획 보는법

목차

 

 

이전 게시물

https://lotuus.tistory.com/168

인덱스(3) : 인덱스 종류

 

 

 

 

MySQL 기준으로 알아보겠습니다.

일반적으로 인덱스는 생성과 삭제만 가능합니다. (수정 불가)

 

생성하기

-- B+Tree 인덱스 (기본 인덱스)
CREATE INDEX idx_btree ON table_name (column_name);

-- 해시 인덱스 (Memory 엔진에서만 사용)
CREATE INDEX idx_hash ON table_name (column_name) USING HASH;

-- FullText 인덱스
CREATE FULLTEXT INDEX idx_fulltext ON table_name (text_column);

-- FullText 인덱스 (한글)
CREATE FULLTEXT INDEX idx_fulltext ON table_name (text_column) with parser ngram;

 

고려사항

• 인덱스를 생성하는 동안 테이블 락이 걸립니다.
  • 데이터 추가, 수정, 삭제가 불가합니다.
  • 비 업무 시간에 진행해야합니다.
  • 운영 중인 경우엔, 테이블을 교체하는 방법으로 인덱스를 생성합니다.
    1. 임시 테이블에 인덱스 생성,
    2. 기존 테이블 > 임시테이블로 데이터 마이그레이션
    3. 기존 테이블 삭제(상황에 따라 백업 테이블로 보관)
    4. 임시 테이블 rename
• 빈번한 검색, 조인, 정렬이 일어나는 열에 인덱스를 생성하면 좋습니다.
  • 특히 정렬이 필요한 컬럼의 경우, 클러스터형 인덱스를 고려합니다.
• 데이터 종류가 적어도 인덱스를 생성하면 좋습니다.
  • Q. 인덱스를 탐색하고, 실제 데이터에 접근하는 비용을 고려하면 Full Table Scan 방식이 더 빠르지 않을까요?
  • A. 옵티마이저가 인덱스를 사용할지 Full Table Scan을 사용할지 결정할 수 있으므로, 인덱스를 설정해 두는 것이 유리합니다.
• 복합 인덱스의 경우, 컬럼 순서를 고려합니다.
• 과도한 인덱스 생성은 피해야합니다.
  • 인덱스는 데이터 변경 시 마다 업데이트 됩니다.
  • 불필요한 인덱스를 유지하면 DML 작업이 느려집니다.

 

 

 

삭제하기

ALTER TABLE table_name DROP INDEX index_name;

 

고려사항

• 인덱스를 삭제하는 동안 테이블 락이 걸립니다.
  • 데이터 추가, 수정, 삭제가 불가합니다.
  • 비 업무 시간에 진행해야합니다.
  • 운영 중인 경우엔, 테이블을 교체하는 방법으로 인덱스를 생성합니다.
    1. 임시 테이블에 인덱스 생성,
    2. 기존 테이블 > 임시테이블로 데이터 마이그레이션
    3. 기존 테이블 삭제(상황에 따라 백업 테이블로 보관)
    4. 임시 테이블 rename

 

 

 

사용하기

인덱스를 생성했다고 해서 반드시 그 인덱스가 모든 쿼리에서 자동으로 사용되는 것은 아닙니다!!!!

인덱스가 쿼리에서 사용될 수 있는지 여부는 쿼리의 조건과 데이터베이스 엔진의 최적화 기준(옵티마이저)에 따라 달라집니다.

따라서 인덱스를 올바르게 설계하고 쿼리를 최적화하는 것이 중요합니다.

먼저, 작성한 쿼리가 인덱스를 활용하는지 확인해봅시다.

 

 

쿼리가 인덱스를 활용하는지 확인하기 (실행계획 확인하기)

EXPLAIN, ANALYZE 명령어를 이용하면 쿼리가 인덱스를 활용하는지 확인할 수 있습니다.

 

EXPLAIN : 통계정보 테이블을 기반으로 유추하여 결과를 반환합니다.

• type : 테이블 액세스 방법
  • ALL : Full Table Scan 으로 모든 레코드를 읽는다.
  • index, range, ref, eq_ref, const : 인덱스를 활용하여 레코드를 읽는다.
• key : 실제로 사용되는 인덱스의 이름
• possible_keys : 사용 가능한 인덱스 목록
• extra : 추가 실행정보
  • Using where : where 조건에 인덱스가 설정된 컬럼을 사용할 경우 표시됩니다.
  • Using index : 커버링 인덱스를 사용할 경우 표시됩니다.

 

ANALYZE : 실제 쿼리를 실행하여 결과를 반환합니다.

• cost : 해당 쿼리의 총 비용
• rows : 예상되는 결과 행 수
• actual time : 각 단계별 처리 시간
  • 일반적으로 각 단계의 actual time 중 가장 큰 값이 쿼리의 실행 시간과 유사하다.
  • 단위는 밀리초이다. (1초 = 1000밀리초)

 

 

B+Tree 인덱스를 사용하도록 쿼리 작성하기

• 대소문자 구별하기
  • 인덱스에 저장된 Key 값이 ‘a’ 일때, ‘A’ 를 쿼리하면 서로 불일치 하기 때문에 인덱스를 활용할 수 없습니다.
• 복합인덱스 사용 시, 인덱스에 정의된 컬럼의 순서와 쿼리 조건의 컬럼 순서를 일치시키기
• LIKE문 검색에서 와일드카드(%)를 처음 부분에 입력하지 않기
  • 인덱스에 저장된 Key 값이 ‘a’ 일때, ‘%a’ 를 쿼리하면 첫 문자도 불일치하기 때문에 인덱스를 활용 할 수 없습니다.
  • LIKE 'A%' : 인덱스 활용 가능, LIKE ‘%A’ : 인덱스 활용 불가
  • 다시말해, 문자열로 시작하는 단어는 인덱스 활용 가능하지만, 문자열을 포함하거나 문자열로 끝나는 단어는 인덱스 활용이 불가합니다.
  • 더 좋은 방법인 Full-text 인덱스로 변경하기!!!!
• OR절을 사용하는 경우
  • OR절의 각 조건을 따로 검색하고, 결과를 결합해야 하기 때문
  • OR 대신 UNION ALL 방법을 사용하는 것이 더 효율적일 수 있습니다.
• NULL 값을 비교하는 경우
  • 인덱스 Key에 NULL값이 들어갈 수 없기 때문에 인덱스를 활용할 수 없습니다.
• IN 절 결과값이 많은 경우
  • N개의 인덱스 스캔을 수행하는 것이 Full Scan을 사용하는 것보다 더 많은 비용이 들어가기 때문

 

 

 

Full-Text 인덱스를 사용하도록 쿼리 작성하기

• MATCH(검색컬럼), AGAINST('키워드' mode) 메서드를 사용해야 해당 인덱스를 사용할 수 있습니다.
• mode 종류
  • in nature lantuage mode
    • 키워드가 하나라도 포함되는 데이터를 찾습니다.
  • in boolean mode
    • 키워드에 다양한 규칙을 지정해서 데이터를 찾을 수 있습니다.
      

      operator	설명	예시	결과
      +	반드시 포함	'+IT +개발'	‘IT’와 ‘개발’이 반드시 포함된 데이터
      -	반드시 제외	'+IT -개발'	‘IT’는 반드시 포함되고 ’개발’은 반드시 제외된 데이터
      *	와일드카드	'마케팅'	‘마케팅’이 포함된 데이터

 

 

 

테스트

특정 문자열을 포함하는 데이터 조회

• 인덱스 없음, LIKE 이용하여 문자열 검색 시
  

  

  • 데이터 탐색방식 : Full Table Scan
    • 외부테이블(item)을 Full Table Scan하여 name = ‘%슈거%’ and config_id is not null 인 행을 필터링합니다.
    • 필터링된 외부 테이블과 내부테이블(item_config)을 기본키 인덱스를 사용하여 빠르게 일치하는 행을 찾아 조인을 수행합니다. (2중 for문 생각하면 쉽다)
  • 데이터 탐색 소요시간 : 10초~13초

 

 

• Full-Text 인덱스 생성

  

• 인덱스 생성 60초 소요

 

 

• Full-Text 인덱스 존재, MATCH, AGAINST 이용하여 문자열 검색 시
  

  

  • 데이터 탐색방식 : Full-Text Search
    1. 외부테이블(item)을 Full-text index를 이용하여 ‘슈거’가 포함되고, config_id is not null 인 행을 필터링합니다.
    2. 필터링된 외부 테이블과 내부테이블(item_config)을 기본키 인덱스를 사용하여 빠르게 일치하는 행을 찾아 조인을 수행합니다. (2중 for문 생각하면 쉽다)
  • 데이터 탐색 소요시간 : 0.002초 ~ 0.002초

 

 

 

특정 범위 검색

• 인덱스 없음, 날짜 검색 및 정렬 시

  

  • 데이터 탐색방식 : Full Table Scan
    • 외부테이블(item)을 Full Table Scan하여 created_at ≥ ‘2024-04-01 00:00:00’ and config_id is not null 인 행을 필터링합니다.
    • 외부테이블(item)을 created_at 기준으로 오름차순 정렬합니다.
    • 필터링된 외부 테이블과 내부테이블(item_config)을 기본키 인덱스를 사용하여 빠르게 일치하는 행을 찾아 조인을 수행합니다. (2중 for문 생각하면 쉽다)
  • 데이터 탐색 소요시간 : 2초 ~ 2초

 

 

 

• B+Tree 인덱스 생성

  

  • 인덱스 생성 60초 소요

 

 

 

• B+Tree 인덱스 존재, 날짜 검색 및 정렬 시

  

  • 데이터 탐색방식 : Index Range Scan
    • 외부테이블(item)을 Index Range Scan을 이용하여 created_at ≥ ‘2024-04-01 00:00:00’ and config_id is not null 인 행을 필터링합니다.
    • 인덱스가 이미 정렬되어 있으므로 정렬 작업은 필요없습니다.
    • 필터링된 외부 테이블과 내부테이블(item_config)을 기본키 인덱스를 사용하여 빠르게 일치하는 행을 찾아 조인을 수행합니다. (2중 for문 생각하면 쉽다)
  • 데이터 탐색 소요시간 : 0.1초 ~ 0.2초