디스크 읽기 방식

하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)

  • 데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다.
  • 이러한 기계식 하드 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD가 많이 출시되고 있다.
  • 디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차I/O에서는 SSD가 하드 디스크 드라이브보다 조금 빠르거나 거의 비슷한 성능을 보이고 있다.
  • SSD의 장점은 기존 하드 디스크 드라이브보다 랜덤 I/IO가 훨씬 바르다.

랜덤 I/O와 순차 I/O

  • 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다.
    • 여러 번 쓰기 또는 읽기 요청하는 랜덤 I/O 작업이 순차 I/O보다 작업 부하가 훨씬 더 크다.
  • 디스크 원판을 가지지 않는 SSD도 랜덤 I/O가 여전히 순차 I/O보다 전체 throughput이 떨어진다.
  • 쿼리를 튜닝하는 것은 랜덤 I/O를 순차 I/O로 바꾸는것이 아니라, 랜덤 I/O 자체를 줄여주는 것이 목적이다.
  • 인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다.
    • 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱슬르 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다.

인덱스란?

  • 인덱스는 데이터의 저장 속도를 희생하고, 읽기 속도를 빠르게 만들어준다.
    • 따라서, SELECT 쿼리 문장의 WHERE 조건절에 사용되는 컬럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.
  • 인덱스 역할별 분류
  • 프라이머리 키: 테이블에서 해당 레코드를 식별할 수 있는 기준값이 된다. NULL 값을 허용하지 않으며 중복을 허용하지 않는 것이 특징이다.
    • 세컨더리 인덱스: 프라이머리 키를 제외한 나머지 모든 인덱스.
      • 유니크 인덱스는 프라이머리 키와 성격이 비솟하고 프라이머리 키를 대체해서 사용할 수 있다고 해서 대체 키라고도 한다.
  • 인덱스 저장 방식에는 대표 적으로 B-Tree 인덱스와 Hash 인덱스로 분류할 수 있다. 그 외에도 여러 알고리즘이 존재한다.
    • B-Tree 알고리즘: 가장 일반적으로 사용되는 인덱스 알고지름
      • 컬럼의 값을 변형하지 않고 원래의 값을 이요해 인덱싱하는 알고리즘이다.
    • Hash 인덱스 알고리즘: 컬럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다.
      • 값을 변형해서 인덱싱하므로 prefix 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다.
  • 인덱스가 유니크한지 아닌지는 DBMS 쿼리를 실행해야 하는 옵티마이저에게 상당히 중요한 문제가된다.
    • 유니크 인덱스에 대해 동등 조건으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다.
    • 그뿐만 아니라 유니크 인덱스로 인한 MySQL의 처리 방식의 변화나 차이점이 상당히 많다.

B-Tree 인덱스

  • B-Tree에는 여러 가지 변형된 알고리즘이 있는데, 일반적으로 DBMS에서는 주로 B+-Tree 또는 B*-Tree가 사용된다.
  • B-Tree의 B는 “Balanced"를 의미한다.

구조 및 특성

  • 트리 구조
    • 루트 노드: 최상위에 하나
    • 리프 노드: 가장 하위에 있는 노드
    • 브랜치 노드: 루트 노드도 아니고 리프 노드도 아닌 중간의 노드
  • 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소값을 가지고 있다.
  • 인덱스는 테이블의 키 컬럼만 가지고 있으므로 나머지 컬럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야된다.
  • 인덱스의 리프 노드에는 데이터 파일에 저장된 레코드의 주소를 가진다.
    • InnoDB 스토리지 엔진을 사용하는 테이블에서는 프라이머리 키가 레코드 주소가 된다.
  • InnoDB 테이블에서 인덱스를 통해 레코드를 읽을 때는 데이터 파일을 바로 찾아가지 못하고, 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽는다.
    • InnoDB 스토리지 엔진을 사용하는 테이블은 성능이 떨어지는 것처럼 보이지만 장단점이 존재한다. (자세한 내용은 8.8절 ‘클러스터링 인덱스’ 에서)

B-Tree 인덱스 키 추가 및 삭제

  • 인덱스 키 추가
    • InnoDB의 경우 새로운 키 값이 B-Tree에 저장될 때, 필요하다면 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있다.
    • 하지만 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제한다.
    • 인덱스에 추가하기 위해서 디스크로부터 인덱스 페이지를 읽고 쓰기를 하는 시간이 많이 걸린다.
      • B-Tree 상 적절한 위치를 검색하고, 저장될 위치가 결정되면 B-Tree의 리프 노드에 저장한다.
      • 리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리돼야 하는데, 이는 상위 브랜치 노드까지 처리 범위가 넓어진다.
  • 인덱스 키 삭제
    • 인덱스가 삭제된 경우, 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료된다.
    • 삭제 마킹 또한 디스크 쓰기가 필요하므로 작업시 디스크 I/O가 필요한 작업이다.
    • MySQL 5.5 이상 버전의 InnoDB 스토리지 엔진에서는 이 작업 또한 버퍼링되어 지연 처리될 수 있다.
  • 인덱스 키 변경
    • B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다.
    • InnoDB 스토리지 엔진을 사용하는 테이블에서는 이 작업 모두 체인지 버퍼를 활용해 지연 처리 될 수 있다.
  • 인덱스 키 검색
    • 인덱스를 검색하는 작업은 B-Tree의 루트 노드로부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데, 이 과정을 트리 탐색이라고한다.
    • 트리 탐색은 SELECT에서만 사용하는 것이 아니라 UPDATE나 DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 사용된다.
    • B-Tree 인덱스를 이용한 검색을 사용하는 경우
      • 100% 일치
      • 값의 앞부분 만 일치하는 경우
      • 부등호 비교 조건
    • B-Tree 인덱스를 이용한 검색을 사용할 수 없는 경우
      • 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도
      • 인덱스의 키값에 변형이 가해진 후 비교
    • InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현돼 있다.
      • 따라서 UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.

B-Tree 인덱스 사용에 영향을 미치는 요소

  • 인덱스 키 값의 크기
    • InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다.
    • 또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다.
    • DBMS의 B-Tree의 자식 노드의 개수는 페이지의 크기와 키 값의 크기에 따라 결정된다.
      • MySQL 5.7 버전부터는 innoDB 스토리지 엔진의 페이지 크기를 innoDB_page_size 시스템 변수를 이용해 4KB ~ 64KB 사이의 값을 선택할 수 있지만 기본값은 16KB다.
      • 인덱스의 키가 16바이트이고 자식 노드 주소와 관련된 복합적인 영역이 12바이트라고 하면, 자식 노드는 16*1024(16+12) = 585개를 가질 수 있다.
    • 인덱스 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미한다.
  • B-Tree 깊이
    • 인덱스 키 값의 크기가 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 그 때문에 같은 레코드 건수라 하더라도 B-Tree의 깊이가 깊어져 디스크 랜덤 ㅇ릭기가 더 많이 필요하게 된다는 것을 의미한다.
  • 선택도(기수성)
    • 인덱스에서 선택도(기수성)은 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다.
    • 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
    • 선택도가 좋지 않다고 하더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러 가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다.
  • 읽어야 하는 레코드의 건수
    • 인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 ㅇ릭는 것보다 높은 비용이 드는 작업이다.
    • 테이블에 레코드가 100만 건이 저장돼 있는데, 그중에서 50만 건을 읽어야 하는 쿼리가 있다고 가정해보자.
      • 이 작업은 전체 테이블을 모두 읽어서 필요 없는 50만 건을 버리는 것이 효율적일지, 인덱스를 통해 필요한 50만 건만 읽어 오는 것이 효율적일지 판단해야 한다.
    • 인덱스를 이용해 읽기의 손익 분기점이 얼마인지 판단할 필요가 있는데, 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드를 1건을 읽는 것이 테이블에서 직접 레코드 1건을 익는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다.
      • 즉, 인덱스를 통해 읽어야 할 레코드의 건수가 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코들만 가려내는 방식으로 처리하는 것이 효율적이다.

B-Tree 인덱스를 통한 데이터 읽기

  • 인덱스 레인지 스캔
    • 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식
    • 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식
    • 인덱스 레인지 스캔 과정
      1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 인덱스 탐색(index seek)라고 한다.
      2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 인덱스 스캔(index scan)이라고 한다.
      3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.
    • 해당 인덱스를 구성하는 컬럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다.
      • 인덱스 자체의 정렬 특성 때문에 자동으로 그렇게 된다.
    • 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 일어난다.
      • 따라서, 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리 방식이 된다.
    • 커버링 인덱스: 인덱스 레인지 스캔 과정에서 레코드를 가져오는 과정이 필요하지 않는 경우
      • 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다.
  • 인덱스 풀 스캔
    • 인덱스를 사용하지만 인덱스의 처음부터 끝까지 모두 읽는 방식
    • 대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다.
      • 예: 인덱스는 (A, B, C) 컬럼의 순서로 만들어져 있지만 쿼리의 조건절은 B 컬럼이나 C 컬럼으로 검색하는 경우다.
    • 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다 효율적이다.
    • 인덱스에 포함된 컬럼만 쿼리를 처리할 수 있는 경우에만 (테이블의 레코들르 읽을 필요가 없는 경우) 인덱스 풀 스캔이 발생한다.
  • 루스 인덱스 스캔
    • 느슨하게 또는 듬성듬성하게 인덱스를 읽는다.
    • 인덱스 스캔과 비슷하게 작동하지만 중간에 필요하지 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다.
    • 아래 예시에서 dept_emp 테이블은 (dept_no, emp_no) 라는 두 개의 컬럼으로 인덱스가 생성돼 있다.
      • 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.
SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;

  • 인덱스 스킵 스캔
    • 인덱스를 사용하기 위해서는 인덱스의 앞에 있는 컬럼부터 사용해야된다.
    • MySQL8.0 버전부터는 옵티마이저가 gender 컬럼을 건너뛰어서 birtbh_date 컬럼만으로도 인덱스 검색이 가능해주는 인덱스 스킵 스캔 최적화 기능이 도입되었다.
    • 루스 인덱스 스캔은 GROUP BY 작업을 처리하기 위해 인덱스를 사용하는 경우에만 적용할 수 있다.
    • 위의 예시에서 옵티마이저는 내부적으로 아래 2개의 쿼리를 실행 하는 것과 비슷한 형태의 최적화를 실행하게 해준다.
    • 인덱스 스킵 스캔의 조건
      • 선행 컬럼의 유니크한 값의 개수가 적어야 함
      • 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함(커버링 인덱스)

B-Tree 인덱스의 정렬 및 스캔 방향

  • 인덱스의 정렬
    • MySQL 5.7 버전까지는 컬럼 단위로 정렬 순서를 혼합해서 인덱스를 생성할 수 없었다.
      • 이를 해결하기 위해 숫자 컬럼의 경우 -1을 곱한 값을 저장하는 우회 방법을 사용했었다.
    • MySQL 8.0 버전부터는 다음과 같은 형태의 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됐다.
  • 인덱스 스캔 방향
    • 인덱스는 항상 오름차순으로 정렬돼 있다면, 인덱스를 최솟값부터 읽으면 오름차순으로 값을 가져올 수 있고, 최댓값부터 거꾸로 읽으면 내림차 순으로 값을 가져올 수 있다.
  • 내림차순 인덱스
    • 단일 컬럼의 인덱스의 경우 인덱스를 읽는 순서만 바꾸면 내림차순과 오름차순 관계없이 해결할 수 있다.
    • 하지만, 2개 이상의 컬럼으로 구성된 복합 인덱스인 경우에 각 컬럼이 내림차순과 오름차순이 혼합된 경우에는 인덱스의 정렬순서로만 해결할 수 있다.
    • 용어 정리
      • 오름차순 인덱스: 작은 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스
      • 내림차순 인덱스: 큰 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스
      • 인덱스 정순 스캔: 인덱스 키의 크고 작음에 관게없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔
      • 인덱스 역순 스캔: 인덱스 키의 크고 작음에 관계없이 인덱스의 리프 노드의 오른쪽 페이지부터 왼쪽으로 스캔
    • 인덱스 정순 스캔이 역순 스캔보다 빠르다. 2가지 이유가 있다.
      • 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
      • 페이지 내 인덱스 레코드가 단방향으로만 연결된 구조

B-Tree 인덱스의 가용성과 효율성

비교 조건의 종류와 효율성

SELECT * FROM dept_emp WHERE dept_no='d0002' AND emp_no >= 10114;
  • 아래 두 인덱스의 차이
    • 케이스 A: INDEX (dept_no, emp_no)
    • 케이스 B: INDEX (emp_no, dept_no)
  • 인덱스를 통해 읽는 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업을 ‘필터링’이라고 한다.
  • 케이스 A 인덱스는 2번째 컬럼인 emp_no 가 비교 작업의 범위를 좁히는데 도움을 준다.
  • 케이스 B 인덱스는 2번째 컬럼인 dept_no는 비교 작업의 범위를 좁히는 데 아무런 도움을 주지 못한다.

인덱스의 가용성

  • B-Tree 인덱스의 특징은 왼쪽 값에 기준해서(Left-most) 오른쪽 값이 정렬돼 있다는 것이다.
    • 케이스 A: INDEX (first_name)
    • 케이스 B: INDEX (dept_no, emp_no)
  • 아래 두 쿼리는 인덱스 레인지 스캔 방식으로는 인덱스를 사용할 수 없다.
    • SELECT * FROM employees WHERE first_name LIKE '%mer';
    • SELECt * FROM dept_emp WHERE emp_no>=10114;

가용성과 효율성 판단

  • 작업 범위 결정 조건으로 인덱스를 사용할 수 없는 경우
    • NOT-EQUAL로 비교된 경우
      • WHERE column <> 'N'
      • WHERE column NOT IN (10,11,12)
      • WHERE column IS NOT NULL
    • LIKE ‘%??’(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
      • WHERE column LIKE '%승환'
      • WHERE column LIKE '_승환'
      • WHERE column LIKE '%승%
    • 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
      • WHERE SUBSTRING(column,1,1) = 'X'
      • WHERE DAYOFMONTH(column) = 1
    • NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
      • WHERE column = deterministic_function()
    • 데이터 타입이 서로 다른 비교(인덱스 컬럼이ㅡ 타입을 변환해야 비교가 가능한 경우)
      • WHERE char_column = 10
    • 문자열 데이터 타입이 콜레이션이 다른 경우
      • WHERE utf8_bin_char_column = euckr_bin_char_column
  • 다른 일반적인 DBMS에서는 NULL 값이 인덱스에 저장되지 않지만 MySQL에서는 NULL 값도 인덱스에 저장된다.
INDEX ix_test ( column_1, column_2, ..., column_n )
  • 다중 컬럼으로 만들어진 인덱스가 작업 범위 결정 조건으로 사용하지 못하는 경우
    • column_1 컬럼에 대한 조건이 없는 경우
    • column_1 컬럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
  • 다중 컬럼으로 만들어진 인덱스가 작업 범위 결정 조건으로 사용하는 경우
    • column_1 ~ column_(i-1) 컬럼까지 동등 비교 형태("=” 또는 “IN”)로 비교
    • column_i 컬럼에 대해 다음 연산자 중 하나로 비교
      • 동등 비교
      • 크다 작다 형태
      • LIKE로 좌측 일치 패턴

전문 검색 인덱스

  • MySQL에서 B-Tree 인덱스는 실제 컬럼의 값이 1MB이더라도 1MB 전체의 값을 인덱스 키로 사용하는 것이 아니라 3072바이트(InnoDB 기준)까지만 잘라서 인덱스 키로 사용한다.
  • 문서의 내용 전체를 인덱스화해서 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에는 B-Tree 인덱스를 사용할 수 없다.
  • 문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘을 전문 검색 인덱스라고 한다.

인덱스 알고리즘

  • 전문 검색 인덱스는 문서의 키워드를 인덱싱하는 기법에 따라 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다.

어근 분석 알고리즘

  • MySQL 서버의 전문 검색 인덱스는 두 가지 중요한 과정을 거쳐 색인 작업이 수행된다.
    • 불용어(Stop Word) 처리
      • 검색에서 별 가치가 벗는 단어를 필터링해서 제거하는 작업.
      • 소스 코드에 정의된 상수를 사용하는 경우가 많고, 유연성을 위해 불용어 자체를 데이터베이스화해서 사용자가 추가하거나 삭제할 수 있게 구현하는 경우도 잇다.
    • 어근 분석(Stemming)
      • 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업
      • 한국어나 일본어의 경우 영어와 같이 단어의 변형 자체는 거의 없기 때문에 어근 분석보다는 문장의 형태소를 분석해서 명사와 조사를 구분하는 기능이 중요한 편이다.
      • MeCab은 일본어를 위한 형태소 분석 프로그램인데 한국어와 비슷하기 때문에 MeCab을 통해 한글 분석이 가능하다.
      • MeCab 프로그램만 가져다 설치한다고 해결되는 것이 아니라, 단어 사전이 필요하며 문장을 해체해서 각 단어의 품사를 식별할 수 있는 학습과정이 필요하다. 이 과정은 상당한 시간이 필요하다.
      • MeCab을 MySQL 서버에 적용하는 방법은 어렵지 앟지만 한글에 맞게 완성돌르 갖추는 작업은 많은 시간과 노력이 필요하다.

n-gram 알고리즘

  • MeCab을 위한 형태소 분석은 매우 전문적인 검색 알고리즘이어서 만족할 만한 결과를 내기 위해서는 많은 노력과 시간을 필요로 한다.
    • 전문적인 검색 엔진을 고려하는 것이 아니라면 범용적으로 적용하기 쉽지 않다.
    • 이런 단점을 보완하기 위한 방법으로 n-gram 알고리즘이 도입된것이다.
  • n-gram은 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다.
    • 형태소 분석보다는 알고리즘이 단순하고 국가별 언어데 대한 이해와 준비 작업이 필요 없는 반면, 만들어진 인덱스의 크기는 상당히 큰 편이다.
  • n-gram에서 n은 인덱싱할 키워드의 최소 글자 수를 의미하는데, 일반적으로 2글자 단위로 키워드를 쪼개서 인덱싱하는 2-gram 방식이 많이 사용된다.
To be or not to be. That is the question
  • 위 문장의 처리 과정
    • 띄어쓰기와 마침표를 기준으로 10개의 단어로 구분되고, 2글자씩 중첩해서 토큰으로 분리된다.
    • 생성된 토큰들에 대해 불용어를 걸러내는 작업을 수행한다.
      • 불용어와 동일하거나 불용어를 포함하는 경우 걸러서 버린다.
      • 기본적으로 MySQL 서버에 내장된 불용어는 information_schema.innodb_ft_default_stopword 테이블을 통해 확인할 수 있다.
    • 최종적으로 “출력(최종 인덱스 등록)” 컬럼에 표시된 것들만 전문 검색 인덱스에 등록한다.
    • MySQL 서버는 이렇게 구분된 토큰을 단순한 B-Tree 인덱스에 저장한다.
      • 물론 성능 향상을 위한 Merge-Tree 같은 기능도 가지고 있긴 하지만 MySQL에서 구현하는 n-gram 알고리즘의 핵심 내용은 이 정도로 이해하면 된다.

불용어 변경 및 삭제

  • MySQL 서버에 내장된 불용어는 도움이 되기보다는 더 혼란스럽게 할 수 있다.
    • 불용어 처리를 완전히 무시하거나, 직접 불용어를 등록해서 사용하는 것을 권장한다.
  • 전문 검색 인덱스의 불용어 처리 무시 방법 2가지
    • MySQL 서버의 설정 파일(my.cnf)의 ft_stopword_file 시스템 변수에 빈 문자열을 설정하면 된다.
      • 스토리지 엔진에 관계없이 MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거하는 것
      • 사용자 정의 불용어를 적용할 때도 사용할 수 있다.
      • 해당 시스템 변수는 MySQL 서버를 재시작해야 반영된다.
    • innodb_ft_enable_stopword 시스템 변수를 OFF로 설정하면 된다.
      • InnoDB 테이블의 전문 검색 인덱스의 불용어 처리를 무시하는 것
      • 동적인 시스템 변수이므로 MySQL 서버가 실행 중인 상태에서도 변경할 수 있다.
      • SET GLOBAL innodb_ft_enable_stopword=OFF;
  • 사용자 정의 불용어 사용 방법 2가지
    • ft_stopword_file 시스템 변수를 아래와 같이 파일의 경로로 등록하면 된다.
      • ft_stopword_file='/data/my_custom_stopword.txt'
    • innodb_ft_server_table 시스템 변수에 불용어 테이블을 설정하면 된다.
      • 불용어 목록을 변경한 이후의 전문 검색 인덱스가 생성돼야만 불용어가 적용된다.
      • innodb_ft_user_stopword_table 시스템 변수를 이용하는 방법도 있는데, 사용 방법은 동일하다.
        • 단, 해당 시스템 변수는 여러 전문 검색 인덱스가 서로 다른 불용어를 사용해야 하는 경우에도 사용 가능하다.

전문 검색 인덱스의 가용성

  • 전문 검색 인덱스를 사용하려면 반드시 다음 두 가지 조건을 갖춰야 한다.
    • 쿼리 문장이 전문 검색을 위한 문법(MATCH ... AGAINST ...)을 사용
    • 테이블이 전문 검색 대상 컬럼에 대해서 전문 인덱스 보유
  • 다음과 같이 doc_body 컬럼에 대해서 전문 검색 인덱스를 생성할 수 있다.
  • 아래 쿼리로도 원하는 검색 결과를 얻을 수 있지만 풀 테입르 스캔으로 쿼리를 처리한다.
    • SELECT * FROM tb_test WHRE doc_body LIKE '%애플%'
  • 전문 검색 인덱스를 사용하려면 다음과 같이 검색 쿼리를 작성해야하며, 전문 검색 인덱스를 구성하는 컬럼들은 MATCH 절의 괄호 안에 모두 명시돼야 한다.
    • SELECT * FROM tb_test WHERE MATCH(doc_body) AGAINST('애플' IN BOOLEAN MODE)

멀티 밸류 인덱스

  • 전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다.
  • 하지만 멀티 밸류(Multi-Value) 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다.
  • 최근 RDMBS들이 JSON 데이터 타입을 지원하기 시작하면서 JSON의 배열 타입의 필드에 저장된 원소들에 대한 인덱스 요건이 발생한 것이다.
CREATE TABLE user(
	user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	credit_info JSON,
	INDEX mx_creditscores ( (CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)) )
);

INSERT INTO user VALUES (1, 'Matt', 'Lee', '{"credit_scores":[360, 353, 351]}');
  • 멀티 밸류 인덱스를 활용하기 위해서는 일반적인 조건 방식을 사용하면 안 되고, 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.
    • MEMBER OF()
    • JSON_CONTAINT()
    • JSON_OVERLAPS()

클러스터링 인덱스

  • MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원하며, 나머지 스토리지 엔진에서는 지원되지 않는다.

클러스터링 인덱스

  • 클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다.
  • 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다.
  • 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.
  • 어떤 레코드의 프라이머리 키를 변경하면 레코드의 저장된 위치가 프라이머리 키에 맞게 변경된다.
  • 프라이머리 키가 없는 경우에는 InooDB 스토리지 엔진이 다음 우선순위대로 프라이머리 키를 대체할 컬럼을 선택한다.
    • 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
    • NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택
    • 자동으로 유니크한 값을 가지도록 증가하는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택
  • 프라이머리 키나 유니크 인덱스가 없는 InnoDB 테이블에서는 아무 의미가 없는 숫자 값으로 클러스터링되는 것이며, 이것은 우리에게 아무런 혜택을 주지 않는다.

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

  • InnoDB 테이블에서 세컨더리 인덱스는 실제 레코드가 저장된 주소를 가지지 않고, 프라이머리 키 값을 저장하도록 구현돼 있다.
    • 주소가 저장되어 있다면 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 할 것이다.

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

  • 장점
    • 프라이머리 키로 검색할 때 처리 성능이 매우 빠름
    • 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음
  • 단점
    • 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
    • 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림
    • INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
    • 프라이머리 키를 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요하기 때문에 처리 성능이 느림
  • 일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경에서는 쓰기와 읽기의 비율이 2:8 또는 1:9 정도이기 때문에 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것이 매우 중요하다.

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

  • 클러스터링 인덱스 키의 크기
    • 모든 세컨더리 인덱스가 프라이머리 키 값을 포함한다.
    • 그래서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크진다.
  • 프라이머리 키는 AUTO-INCREMENT보다는 업무적인 컬럼으로 생성(가능한 경우)
    • 프라이머리 키는 검색에서 빈번하게 사용하는 것으로 선택하는 것이 중요하다.
    • 그러므로 설령 그 컬럼으 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 크 컬럼을 프라이머리 키로 설정하는 것이 좋다.
  • 프라이머리 키는 반드시 명시할 것
    • 사용자가 사용할 수 있는 값을 프라이머리 키로 설정하는 것이 좋다.
    • 또한 ROW 기반 복제나 InnoDB Cluster에서 모든 테이블이 프라이머리 키를 가져야만 하는 정상적인 복제 성능을 보장하기도 하므로 프라이머리 키는 꼭 생성하자.
  • AUTO-INCREMENT 컬럼을 인조 식별자로 사용할 경우
    • 여러 개의 컬럼이 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어질 때가 있다.
    • 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요하지 않다면 그대로 프라이머리 키를 사용하는 것이 좋다.
    • 세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO_INCREMENT 컬럼을 추가하고, 이를 프라이머리 키로 설정하면 된다.
      • 이를 인조 식별자라고 한다.
    • 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것은 성능 향상에 도움이 된다.

유니크 인덱스

  • MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.
  • 유니크 인덱스에는 NULL도 저장될 수 있는데, NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다.

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

  • 많은 사람이 유니크 인덱스가 읽을 때 빠르다고 생각하지만, 이는 사실이 아니다.
    • 유니크하지 않는 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니다.
    • 유니크 인덱스와 일반 세컨더리 인덱스는 사용되는 실행 계획이 다르지만, 성능 상 큰 차이는 없다.
  • 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다.
    • 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다.
    • 일반적인 세컨더리 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼가 사용된다. 하지만, 유니크 인덱스는 중복 체크를 해야하므로 버퍼링하지 못한다.

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

  • 유일성이 꼭 보장돼야 하는 컬럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 하지 않는 세컨더리 인덱스를 생성하는 방법도 한 번씩 고려하자.
  • 또한, 유니크 인덱슬르 일반 다른 인덱스와 같은 역할을 하므로 중복해서 생성할 필요가 없다.

외래키

  • MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성된다.
  • InnoDB의 외래키에는 두 가지 특성이 있다.
    • 테이블 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
    • 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

자식 테이블의 변경이 대기하는 경우

  • 자식 테이블의 쾨래 키 컬럼의 변경(INSERT, UPDATE)은 부모 테이블의 확인이 필요한데, 이 상태에서 보무 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 쓰기 잠금이 해제될 때까지 기다린다.
  • 자식 테이블의 외래키(pid)가 아닌 컬럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않는다.

부모 테이블의 변경 작업이 대기하는 경우

  • 자식 테이블의 레코드를 변경하면 자식 테이블의 레코드에 대해 쓰기 잠금을 획득한다.
  • 이 상태에서 부모 테이블의 레코드에서 id가 1인 레코드를 삭제하는 경우 이 쿼리는 자식 테이블의 레코드에 대한 쓰기 잠금이 해제될 때 까지 기다린다.
    • 자식 테이블이 생성될 때 정의된 외래키 특성(ON DELETE CASCADE) 때문이다.
  • 외래 키를 물리적으로 생성하려면 이러한 현상으로 인한 잠금 경합까지 고려해 모델링을 진행하는 것이 좋다.
    • 물리적인 외래키의 고려 사항은 연관 테입르에 읽기 잠금을 건다.
    • 이렇게 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리의 동시 처리에 영향을 미친다.