8. 인덱스

디스크 읽기 방식

디스크 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 하드보다 조금 빠르거나 거의 비슷한 성능을 보이기도 한다. 하지만 SSD의 장점은 랜덤 I/O에서 훨씬 빠르다는 것이다.

랜덤I/O는 순차 I/O에 비해 성능이 배로 차이가 남으로, 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다. SSD도 랜덤 I/O가 순차 I/O보다 전체 Throughput이 떨어진다.

인덱스란?

찾고 싶은 레코드를 빠르게 찾을 수 있다. 인덱스는 많으면 데이터 크기보다 비대해져 역효과가 생길 수 있고 저장 성능을 주고 읽기 성능을 얻는 것 이다.

인덱스 역할로 구분 한다면 : PK Key, Secondary Key
저장 방식(알고리즘) 구분 한다면 : B-TREE Index, Hash Index

B-Tree 인덱스

B(Balanced)-Tree는 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.

최상위 노드	중간 노드	최하위 노드
루트 노드(Root node)	브랜치 노드(Branch node)	리프 노드(Leaf node)

리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소값을 가지고 있다.
인덱스의 키 값은 정렬되어 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장돼 있다.
└ 삭제 없이 Insert만 된 테이블인 경우 맞을 수도 있다. 하지만 삭제가 되고 빈 공간이 생기면 공간을 재활용하도록 DBMS가 설계되기 때문에 순서로 저장되는 것은 아니다.

InnoDB는 리프 노드는 실제 주소 값이 아닌 PK 값을 가지고 있어, PK 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽는다.
└ 클러스터링 인덱스로 저장하기 때문에, 자세한 내용은 이후 설명

B-Tree를 통한 인덱스 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있다. 또한 인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다. 이미 변경된 값은 인덱스에 존재하는 값이 아니다. InnoDB에서 레코드 잠금이나 넥스트 키락이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현돼 있다. 따라서 UPDATE or DELETE 문장이 실행될 때 적절한 인덱스가 없으면 불필요한 많은 레코드를 잠근다.

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

• 인덱스 키 추가
  저장될 위치가 결정되면 리프 노드에 저장한다. 리프 노드가 꽉 차서 저장할 수 없을 때는 분리 돼야 하는데 이는 상위 브랜치 노드까지 처리의 범위가 넓어 진다. 이러한 작업으로 인해 새로운 키를 추가하는 비용이 많이 드는 것으로 알려졌다.
  InnoDB인 경우 PK or Unique Index인 경우는 중복 체크가 필요하기 때문에 즉시 추가하거나 삭제한다 그 외 Secondary Index에 대해서는 체인지 버퍼를 활용한다.

• 인덱스 키 삭제
  해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 삭제 마크만 하면 작업이 완료된다. 해당 처리는 버퍼링되어 지연 처리될 수도 있다.

• 인덱스 키 변경
  인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정됨으로 단순히 변경이 되는 작업이 아닌, 기존 키 값을 삭제하고 변경된 값으로 추가한다. 순서는 설명한 절차대로 진행된다.

B-Tree 인덱스 사용의 영향

인덱스 키 값의 크기

InnoDB의 가장 기본 단위를 페이지 or 블록 이라고 하며, 모든 읽기 쓰기 작업의 최소 작업 단위가 된다.
페이지 크기를 innodb_page_size 를 통해 4KB~64KB 선택할 수 있지만 기본 값은 16KB이다.
인덱스 키 값이 커질 수록 한 페이지에 저장되는 레코드 량이 적어짐으로 많은 데이터를 읽을 때 디스크로 읽어야 하는 횟수가 늘어나게 된다.

B-Tree 깊이

키 값이 16byte이고 B-Tree 깊이가 3인 경우 최대 2억개 정도의 키 값을 담을 수 있다. 아무리 대용량 DB라고 해도 깊이가 5단계 이상까지 깊어지는 경우는 흔치 않다.

선택도(기수성)

모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다.
인덱스 키 값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아지고 동시에 선택도 또한 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.

읽어야 하는 레코드의 건수

테이블 레코드가 100만 건이 저장돼 있는데, 그중에서 50만 건을 읽어야 하는 쿼리가 있다고 가정해 보자. 인덱스를 통해서 50만 건을 읽을지 혹은 테이블 풀 스캔이후 필터를 할지 어느 것이 효율적일지 판단해야 한다.

일반적은 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다.
인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 방식으로 처리하는 것이 효율적이다.

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

인덱스 레인지 스캔

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.
루트 노드에서 부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 레코드의 시작 지점을 찾을 수 있다. 그때부터는 레코드 순서대로 읽으면서 멈춰야 할 위치에 다다르면 반환하고 쿼리를 끝낸다.

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다.
└ 데이터 레코드까지 모두 읽어야 한다면 이 방식으로 처리되지 않는다.

루스 인덱스 스캔

루스 인덱스 스캔은 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리된다. 일반적으로 GROUP BY or MAX() or MIN() 집합 함수에 대해 최적화를 하는 경우에 사용된다.

인덱스 스킵 스캔

ix_temp_1(os, buy_date)

SELECT * FROM temp WHERE buy_date > '2025-01-01';
SELECT * FROM temp WHERE os = 'window' AND buy_date > '2025-01-01';

첫번째 SELECT 조회는 인덱스 풀 스캔을 하면서 원하는 값을 찾게된다.
두번째 SELECT 조회는 ix_temp_1 사용하여 레인지 스캔을 사용한다.

여기서 인덱스 스킵 스캔을 사용하게 된다면

SELECT * FROM temp WHERE os = 'window' AND buy_date > '2025-01-01';
SELECT * FROM temp WHERE os = 'mac' AND buy_date > '2025-01-01';

os의 유니크한 값을 넣어 index를 활용할 수 있게 된다.
하지만 유니크한 값이 많다면 처리 성능이 오히려 더 느려질 수도 있다.

다중 컬럼 인덱스

두 개 이상의 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스라고 한다.
인덱스 내에서 칼럼 위치에 따라 인덱스내의 정렬이 결정됨으로 순서가 상당히 중요하다.

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

인덱스를 생성할 때 설정한 규칙에 따라서 오름차순 or 내림차순으로 저장된다. 오름차순으로 생성됐다고 해서 오름차순으로만 읽을 수 있는 것은 아니고 거꾸로 읽으면서 내림차순으로 읽을 수 있다.

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

WHERE dept_no='d002' AND emp_no >= 10144 인 경우 인덱스 순서에 따라 조회되는 범위가 다르게 된다.
그림 좌측 인덱스인 경우 모두 작업 범위 결정 조건에 해당 하지만 우측은 emp_no만 작업 범위 결정 조건에 해당되고 dept_no는 필터링 조건에 해당된다. 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만 체크 조건은 많다고 해서 쿼리의 처리 성능을 높이지는 못한다. 오히려 더 느리게 만들 때가 많다

• 가용성과 효율성 판단
  기본적으로 B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없다.
  • NOT-EQUALS로 비교된 경우 (<>, NOT IN, NOT BETWEEN, IS NOT NULL)
    • col <> 'a'
    • col NOT IN ('a')
    • col IS NOT NULL
  • LIKE '%text' 인 경우 (앞부분 일치가 아닌 경우)
    • col LIKE '%a';
    • col LIKE '_a';
  • 인덱스 컬림이 변형된 후 비교된 경우
    • SUBSTRING(col,1,1) = 'a'
    • DAYOFMONTH(col) = 1
  • NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
    • col = deterministic_funtion()
  • 데이터 타입이 서로 다른 비교
    • char_col = 10
  • 문자열 데이터 타입의 콜레이션이 다른 경우
    • utf8_col = euckr_col

R-Tree 인덱스

R(Rectangle)-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스다.
GIS, GPS 기반의 서비스는 MySQL의 공간 확장을 이용하면 간단하게 이러한 기능을 구현할 수 있다.

구조 및 특성

MySQL은 공간 정보의 저장 및 검색을 위해 여러 가지 기하학적 도형 정보를 관리할 수 있는 데이터 타입을 제공한다.
각 도형을 MBR(Minimum Bounding Rectangle)로 보여주는데 여기서 MBR은 각 도형을 감싸는 최소 크기의 사각형을 의미한다.

R-Tree 인덱스의 용도

일반적으로는 GPS 기준의 위도, 경도 좌표 저장에 주로 사용된다. 또는 회로 디자인 등과 같이 좌표 시스템에 기반을 둔 정보에 대해서는 모두 적용할 수 있다.
ST_Contains() or ST_Within() 등과 같은 포함 관계를 비교하는 함수로 검색을 수행하는 경우에만 인덱스를 이용할 수 있다.
대표적으로는 '현재 위치로 반경 5KM 이내의 음식점 검색' 등과 같은 검색에 사용될 수 있다.

전문 검색 인덱스

문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다. 이러한 검색을 위한 전문 검색 인덱스라고 한다.

인덱스 알고리즘

• 어근 분석 알고리즘
  • 불용어 처리 : 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업
  • 어근 분석 : 검색어로 선정된 단어의 뿌리린 원형을 찾는 작업

    MySQL 서버에서는 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원한다.

• n-gram 알고리즘
  n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다.
  └ 만들어진 인덱스의 크기는 상당히 큰 편이다.
  일반적으로 2글자 단위로 인덱싱하는 2-gram 방식이 많이 사용된다. 이후 불용어 처리 이후 최종 인덱스로 등록된다.

전문 검색 인덱스를 사용하려면 반드시 MATCH(col) AGAINST('text' IN BOOLEAN MODE);로 검색해야 한다.

함수 기반 인덱스

때로는 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데 이러한 경우 함수 기반의 인덱스를 활용하면 된다.
함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있을 뿐, 내부적인 구조는 B-Tree와 동일하다.

가상 칼럼을 이용한 인덱스

VAIRUAL or STORED을 통해 가상 컬럼에 인덱스를 생성할 수 있다. 하지만 가상 칼럼은 실제 테이블의 구조가 변경된다는 단점이 있다.

함수를 이용한 인덱스

INDEX ix_fullname ((CONCAT(first_name, ' ' , last_name)) 으로 인덱스에 함수를 직접 사용하여 생성할 수 있다.
테이블의 구조는 변경하지 않고 사용할 수 있다. 함수 기반의 인덱스는 명시된 표현식 그대로 사용해야지만 인덱스를 사용할 수 있다.

멀티 벨류 인덱스

하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스이다.
JSON 타입의 인덱스 생성이 가능하다

INDEX mx_index ((CAST(col -> '$.credit_scores' AS UNSIGNED ARRAY))

INSERT INTO temp VALUES('{"credit_info":[360, 353, 351]}');

SELECT * FROM temp WHERE 360 MEMBER OF(col -> '$.credit_scores');

클러스터링 인덱스

클러스터링 인덱스는 테이블의 PK에 대해서만 적용되는 내용이다. 비슷한 PK 레코드까리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다. PK 값에 의해 레코드의 저장 위치가 달라진다는 것이다.
InnoDB와 같이 클러스터링 인덱스로 저장되는 PK 기반의 검색이 매우 빠르지만 저장이나 PK 변경이 상대적으로 느리다.

클러스터링의 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장돼 있음을 알 수 있다. 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.

PK 키가 없는 경우에는 다음 우선순위로 결정된다.

• 1. NOT NULL 옵션의 UNIQUE 인덱스 중에서 첫 번째 인덱스를 선택
• 2. 자동으로 Unique한 값을 가지도록 내부 칼럼을 추가 후 선택

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

MyISAM or MEMORY 테이블 같은 클러스터링되지 않은 테이블은 INSERT될 때 처음 저장된 공간에서 절대 이동하지 않는다. 데이터 레코드가 저장된 주소는 내부적인 ROWID를 이용해 실제 데이터 레코드를 찾아온다. 그러므로 PK or 세컨더리의 구조적인 차이점이 없다.
하지만 InnoDB의 클러스터링은 물리적 위치가 변경 시 세컨더리의 리프 노드 값을 수정해야 함으로 PK 값을 가지게 되었다.

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

• 장점
  • PK 검색 시 성능이 매우 빠름
  • 모든 세컨더리 인덱스가 PK 값을 가지고 있기에 인덱스만으로 처리될 수 있는 경우가 많음
• 단점
  • 모든 세컨더리가 PK 값을 가지고 있음으로 PK 값이 큰 경우 인덱스의 크기가 커짐
  • 세컨더리 인덱스를 통해 값을 찾아도 한 번더 PK 인덱스로 검색해야 한다.
  • INSERT시 PK에 의한 레코드 저장 위치가 결정됨으로 처리 성능이 느림
  • PK 변경 시 DELETE TO INSERT로 진행하기에 성능이 느림

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

• PK 키의 크기
  테이블에 세컨더리 인덱스가 5 개인 경우

PK	레코드당 증가하는 인덱스 크기	100만 건 레코드 저장 시 증가하는 인덱스 크기
10byte	10 byte * 5 = 50 byte	47MB
50 byte	250 byte	238MB

• PK는 AUTO-INCREMENT 보다는 업무적인 칼럼으로 생성
  PK 조회 속도가 빠름으로 중요한 역할을 하기에 의미있는 값으로 설정하는 것이 좋다.
• PK는 반드시 명시할 것

유니크 인덱스

인덱스의 키 값이 중복이 있으면 안됨으로, 새로운 인덱스가 추가될 때 중복된 값을 확인하는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용한다. 이 과정에서 데드락이 아주 빈번히 발생한다. 또한 중복 체크를 해야함으로 버퍼를 사용할 수 없음으로 세컨더리 인덱스 보다 변경 작업이 더 느리게 작동된다.

꼭 필요한 경우라면 유니크 인덱스를 생성한느 것은 당연하다. 하지만 더 성능이 좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하는 것은 좋지 않다. 유니크 인덱스와 세컨더리 인덱스는 중복을 제외하고선 동일하게 동작함으로 중복으로 만들어 줄 필요는 없다.

외래키

외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스 까지 생성한다. 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.