Back-End/DB

Real My Sql 8.0 [인덱스]

Meluu_ 2025. 6. 20. 15:20

1. 디스크의 읽기 방식

디스크 같은 기계식 장치의 성능은 상당히 제한적로 발전

데이터베이스의 성능 튜닝 == 디스크 I/O를 어떻게 줄일지 관건

 

HDD vs SSD

CPU, 메모리 같은 주요 장치는 대부분 전자식 장치

HDD는 기계식 장치이기에 데이터베이스 서버에서 항상 디스크 장치가 병목 발생

 

전자식 저장 매체인 SSD가 많이 출시되고 있으며 HDD와 같은 인터페이스 지원, 내장 디스크나 DAS, SAN에 그대로 사용 가능

한마디로 DB서버로 사용하기 최적이며 HDD보다 훨씬 빠르다. (랜덤 I/O, 순차I/O) 둘다 

 

 

 

 

1.2 랜덤 I/O 와 순차 I/O

랜덤 I/O : HDD의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동 시킨 다음 데이터를 읽는 것

순차 I/O : 디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 것

 

사실 디스크 헤더를 원하는 데이터 위치로 이동해야하는건 랜덤, 순차 동일하다

하지만 얼마나 디스크 헤더를 움직이냐가 두 읽기 방식의 차이다.

 

생각을 해보자 

어떤 데이터를 순차적으로 읽는다면 그 데이터의 시작점으로 이동한 다음 쭉 읽으면 그만이지만

사방에 펼쳐져있다면, 원하는 데이터를 읽으려 헤더를 계속해서 움직여야한다. 

 

디스크의 성능 :  디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정

데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기에 

MySQL 서버에는 그룹 커밋, 바이너리 로그 버퍼, InnoDB 로그 버퍼 등의 기능이 내장

 

SSD라고 해서 랜덤과 순차가 비등하지 않고 처리량을 봤을때 랜덤 < 순차 다.

 

일반적으로 쿼리 튜닝은  랜덤 I/O 자체를 줄이는 것이 목적

 

인덱스 레인지 스캔은 데이터를 읽기위해 주로 랜덤 I/O, 풀 테이블 스캔은 순차 I/O 사용 (테이블의 대부분을 읽는다면 순차I/O가 더 적합할 수 있음)

 

 

2. 인덱스란

색인, 찾아보기에 비유된다.

 

DBMS에서 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸린다.

그래서 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만들어 둔다.

 

책의 색인과 DBMS 인덱스의 공통점 가운데 중요한 것은 정렬이다.

책의 찾아보기도 내용이 많으면 최대한 빠르게 찾아갈 수 있게 사전순으로 정렬되어있는데

DBMS의 인덱스도 마찬가지로 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다. 

 

sortedList는 DBMS 인덱스와 같은 자료구조

ArrayList는 데이터 파일과 같은 자료구조

 

sortedList

  • 정렬되어있지만 데이터 저장시 매번 정렬, 과정이 복잡하고 느림
  • 정렬 상태이기에 아주 빨리 탐색 가능

 

ArrayList

  • 저장된 순서대로 정렬 없이 그대로 저장

 

DBMS에서 인덱스는 데이터의 저장(Insert, Update, Delete)성능을 희생, 읽기 속도를 높이는 기능

그렇다고 전부 인덱스로 생성하면 역효과이다.

 

인덱스는 데이터 관리 방식(알고리즘), 중복 값 허용 여부 등에 따라 여러가지로 분류

  • Primary Key
  • secondary Key
    • (Primary Key를 제외한 모든 인덱스, 대체 인덱스라고도 함)

 

데이터 저장 방식

  • B-Tree
    • 가장 일반적인 인덱스 알고리즘
    • 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘
  • R-Tree
    • 위치 기반 검색을 지원하기 위한 목적
    • B-Tree의 응용 알고리즘
  • Hash 인덱스 알고리즘
    • 칼럼의 값으로 해시값 계산해서 인덱싱하는 알고리즘
    • 매우 빠름
    • 일부 검색 등 범위 검색은 불가 (Prefix, 일부만 검색)
    • 메모리 기반 데이터베이스에서 많이 사용 (Redis)Hash 인덱스 알고리즘
  • 아래는 MySQL 서버의 범위는 아님
    • Fractal-Tree
    • Merge-Tree (로그 기반)

 

데이터 중복 허용 여부로 분류

유니크 인덱스

유니크하지 않는 인덱스

전문 검색 인덱스 (FullText Search)
텍스트 데이터에 대해 단어 단위로 검색할 수 있도록 구성된 특수한 인덱스
일반적인 B-Tree나 Hash 인덱스와 달리, 문서 전체에서 단어의 출현 위치나 빈도 등을 기반으로 구성
공간 검색 인덱스 (spatial index)
위치나 형태가 있는 데이터(예: 좌표, 도형, 영역 등)에 대해 효율적으로 질의할 수 있게 만든 인덱스

 

 

3. B-Tree 인덱스

변형 형태의 알고리즘

B+-Tree || B*-Tree가 사용

B는 Balanced 를 의미

 

칼럼의 원래 값을 변형시키지 않고(실제론 값의 앞부분만 잘라서 관리) 인덱스 구조체 내에서 항상 정렬된 상태로 유지

전문 검색과 같은 특수한 요건이 아닐 경우 대부분의 인덱스는 B-Tree 사용

 

3.1 구조 및 특성

실제 데이터, 인덱스 따로 관리

 

루트 노드 - 브랜치 노드 - 리프 노드 - 데이터 파일 순 

 

InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장됨, 기본적으로 Primary Key 순서로 정렬되어 저장됨

클러스터링 : 비슷한 값을 최대한 모아서 저장하는 방식

 

MyISAM 스토리지 엔진에서는 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙 공간처럼 활용된다. 즉, Insert 되는 순서대로 저장된다. 그리고 MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지며

프라이머리키 와 세컨더리 인덱스는 모두 ROWID값을 가리킨다.

세컨더리 인덱스가 물리적인 주소를 가짐

 

InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기에 논리적인 주소를 가짐

때문에 세컨더리 인덱스에 저장돼 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후 프라이머리 키 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽는다. 

즉, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색해야한다.

 

클러스터형 인덱스인 프라이머리 키 인덱스는 리프노드에 실제 레코드 전체가 들어있다.

 

3.2 인덱스 키 추가 및 삭제

인덱스 키 추가

새로운 키 값이 B-Tree에 저장될때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 저장될 수도 아닐 수도 있다.

 

과정

  1. 저장될 키값을 이용해 B-Tree상 적절한 위치 검색
  2. 위치 결정시 레코드 키값과 레코드의 주소 정보를 B-Tree의 리프노드에 저장
  3. 꽉찼다면 리프노드가 분리돼야하는데 이는 상위 브랜치 노드까지 처리의 범위가 넓어짐
  4. 생각보다 키 추가 비용이 있음

 

MyISAM, MEMORY 스토리지 엔진을 사용하는 테이블

INSERT 문장 실행시 즉시 새로운 키 값을 B-Tree 인덱스에 변경

 

InnoDB 스토리지 엔진

프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기에 즉시 처리

그외에는 지연 처리 가능

 

인덱스 업데이트 작업은랜덤 디스크 읽기 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모

InnoDB는 변경해야할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행

디스크로부터 읽어와서 업데이트 해야한다면 즉시 실행하지 않고 임시공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킴

 

당연하게도 중복 체크해야하는 유니크나 프라이머리 키인덱스는 체인지 버퍼 사용 불가

이때 사용하는 임시 메모리 공간을 체이지 버퍼라 한다.

 

인덱스 키 삭제

단순히 해당 키 값이 저장된 리프 노드를 찾아서 삭제 마크만 하면 작업이 완료

InnoDB에서는 이 또한 지연 처리가 가능

 

인덱스 키 변경

인덱스 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결졍되므로 단순히 인덱스상의 키 값만 변경하는 것은 불가능

 

 

과정

  1. 삭제할 인덱스의 키 값을 삭제
  2. 새로운 키 값을 추가

InnoDB 스토리지 엔진 사용 테이블은 체인지 버퍼를 통해 지연 처리 가능

 

인덱스 키 검색

이러한 비용을감당하면서 인덱스를 구축하는 이유는 바로 빠른 검색을 위해서이다.

 

B-Tree의 트리 탐색을 통해 루트 노드부터~리프노드까지 이동하면서 비교작업을 수행

인덱스 트리 탐색은 SELECT뿐 아니라  UPDATE, DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야할 경우에도 사용

B-Tree 인덱스 검색은 100%일치, 값의 앞부분 일치하는 경우에도 사용 가능

부등호 비교 조건에서도 가능

 

인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스 사용 불가

인덱스 키 값에 변형이 가해진 후 비교되는 경우 절대 B-Tree의 빠른 검색 기능 사용 불가

변형시 인덱스에 존재하는 값이 아니기에 함수나 연산을 수행한 결과로 정렬, 검색하는 작업은 탐색 불가 

 where  member.id * 1.1  < 100

 

 

InnoDB 스토리지 엔진에서 인덱스는 특별한 의미가 존재

InnoDB 테이블에서 지원하는 레코드 잠금, 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현됨

SELECT * FROM Member Where id = 5 FOR UPDATE; //id는 프라이머리 키로 인덱스를 가짐, 레코드 락

-> 넥스트 키락, 레코드 락은 인덱스 기반으로만 작동한다. 애초에 MYSQL의 모든 락이 인덱스락임 

    인덱스가 없으면 범위 자체를 정의할 수 없기에 전체 스캔 + 광범위 잠금을 걸게 된다. 

 

따라서 UPDATE, DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.

심지어 테이블의 모든 레코드를 잠글 수 있음

 

때문에 InnoDB엔진에서는 인덱스 설계가 중요하고 많은 영향을 미침

 

UniQue, Primary key 로 정확한 고유키 조건 검색일시 id = 5, 해당 레코드락만 걸고 넥스트 키락은 걸지 않는다. 

 

 

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

B-Tree 인덱스는 아래의 요소들에 의해 검색이나 변경 작업의 성능이 영향을 받는다. 

  • 칼럼 크기
  • 레코드 건수
  • 유니크한 인덱스 키 값의 개수

 

3.3.1 인덱스 키 값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 기본 단위를 페이지 or 블록 단위라 함

디스크의 모든 읽기 및 쓰기 작업의 최소 단위, 버퍼 풀에서 데이터 버퍼링하는 기본 단위

 

페이지 크기를 innodb_page_size 시스템 변수를 이용해 4KB~64KB 사이의 값 선택 가능

 

인덱스 키 값의 크기가 클 수록 한 페이지에 저장할 수 있는 키가 줄어든다. 

 

때문에 SELECT로 500개의 레코드를 읽는다 했을때 

인덱스 페이지가 커서 한 페이지에 300개의 레코드만 있다면 최소 2번 이상 디스크로부터 읽어야하지만

인덱스 키 값이 작은 페이지는 그보다 많은 레코드를 가지므로 1번만 디스크를 읽어도 될 수 있다.

 

또한 키 값이 길어진 다는 것은 전체적인 인덱스 크기가 커진다는 것을 의미 -> 메모리 효율 떨어짐

16KKB 인덱스 페이지
인덱스 키값 16B, 자식노드 주소 12B
16*1024 / (16+12) = 585개 인덱스 저장 가능

인덱스 값이 커지면? 분모가 커지므로 작아질 수 밖에 없다.

 

B-Tree 깊이

인덱스 키 값의 크기는 깊이와도 연관이 있다. 

인덱스 키 값 크기(길이)가 클수록 한 페이지 내 담을 수 있는 키값의 양이 줄어드므로 

깊이에 따른 키 값이 줄어 같은 레코드 건수라도 B-Tree의 깊이가 깊어져서 디스크 읽기(랜덤I/O)가 더 많이 필요하게 됨

 

하지만 실제로 아무리 대용량 DB라도 깊이가 5단계 이상 깊어지는 경우는 흔치 않다. 

단지 인덱스 키 값의 크기는 가능한 작게 만드는게 좋다는 의미

 

 

선택도(기수성)

인덱스에서 선택도 = 기수성으로 거의 같은 의미를 가짐

모든 인덱스 키 값 가운데 유니크한 값의 수를 의미

 

선택도(기수성) = 전체 인덱스 키 값 / 유니크한 값의 수

중복된 값이 많아질 수록 기수성은 낮아지며 동시에 선택도도 떨어진다.

인덱스는 선택도가 높을 수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리됨

 

country 전체 인덱스 10000개
A. country 칼럼 유니크한 값이 10개
B. country 칼럼 유니크한 값이 1000개

SELECT city FROM tb_city WHERE country = 'KOREA' AND city = 'SEOUL';

A의 경우 검색시 1000 건이 일치하게되고 원하는 값을 읽을때 최악의 경우 999건을 불필요하게 읽어야한다.
B의 경우 검색시 10건이 일치하게되고 원하는 값을 읽을때 최악의 경우 9건만 불필요하게 읽게 된다.

 

 

읽어야하는 레코드 건수 (비율)

인덱스를 이용한 읽기의 손익 분기점에 따라 테이블 풀 스캔이 나을 때도 있다.

그 이유는 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건 읽는 것이 테이블에서 직접 레코드 1건 읽는 것보다  4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다고 한다. 

 

 

인덱스를 통해 읽어야할 레코드의 건수(옵티마이저 판단 기준)가 전체 테이블 레코드의 20~25%를 넘어가면 직접 테이블 읽어서 필터링 방식으로 처리하는 것이 효율적이다.

 

 

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

어떤 경우에 인덱스를 사용하게 유도할지, 또는 사용하지 못하게 할지 판단하려면 MYSQL(스토리지 엔진)이 어떻게 인덱스를 이용해서 실제 레코드를 읽는지 알아야한다. 


인덱스 레인지 스캔

가장 대표적인 접근 방식, 빠름

 

검색해야 할 인덱스의 범위가 결정됐을때 사용하는 방식

검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현

 

인덱스 특성상 오름차순, 또는 내림차순으로 정렬되어있다. 읽는 방향만 정하면 된다.

이렇게 레인지 스캔으로 읽고 각 레코드에 해당하는 주소로 랜덤 읽기한다.

 

이런 이유때문에 비용이 비싸 20~25% 이상이면 테이블 풀 스캔이 더 낫다는 것이다. 

 

 

처리과정

  1. 인덱스에서 조건을 만족하는 값이 저장된 위치 찾음 (인덱스 탐색)
  2. 1번에서 탐색된 위치부터 필요한 인덱스를 차례대로 쭉 읽는다. (인덱스 스캔)
  3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어옴

 

물론 커버링 인덱스로 처리가능한 경우 3번은 일어나지 않는다. 떄문에 매우 빠르다.

 

 

아래는 mysql 서버에서 1번과 2번 단계의 작업이 얼마나 수행됐는지에 대한 상태값이다.

SHOW STATUS LIKE 'Handler_%';

Handler_read_fist : MIN(), MAX()와 같이 최대, 최솟값만 읽는 경우 증가, 읽은 레코드 건수를 의미, 처리과정 1,2,3번 구분 안함
Handler_read_last 

Handler_read_key : 1번 실행 횟수
Hander_read_next : ASC로 읽은 레코드 건 수 
Handler_read_prev : DESC로 읽은 레코드 건 수

위 값들은 서버 실행후 누적된 값들임

 

인덱스 풀 스캔

 

말그대로 인덱스를 풀 스캔한다.

인덱스 리프 노드 처음 or 끝으로 이동해서 링크드 리스트를 따라서 첨부터 끝까지 스캔

대표적으로 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 사용

 

인덱스 뿐 아니라 데이터 레코드까지 모두 읽어야한다면 절대 이방식으로 처리되지 않는다.

인덱스 풀 스캔은 효율적인 방식이 아니며 일반적으로 인덱스를 생성하는 목적이 아니다.

인덱스 풀 스캔을 사용하는 경우 인덱스를 사용하지 못한다. 또는 인덱스를 효율적으로 사용하지 못한다라는 표현을 사용했다.

 

루스 인덱스 스캔 

인덱스 레인지 스캔과 비슷하게 동작하지만, 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어감

일반적으로 GROUP BY, MAX,MIN 함수에 대해 최적화를 하는 경우 사용

 

인덱스는 정렬되어있기때문에 가능

// category, price 인덱스가 있어야함
SELECT MIN(price) FROM products GROUP BY category;

 

위의 경우 각 그룹에서 가장 위에 있는 값을 선택하면 됨 

그외는 무시하고 다음 그룹으로 넘어감

 

 

 

인덱스 스킵 스캔

루스 인덱스 스캔의 확장판

WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 훨씬 넓어짐

 

인덱스는 구성 칼럼의 순서가 중요하다.

IDEX ix_gender_birthdate(gender, birthdate)

위와 같은 인덱스가 있고 birthdate만 where 조건절에 사용해서 검색쿼리를 날린다하자 

이 경우 인덱스 풀 스캔을 한다. 

 

하지만 MySQL 8.0부터 인덱스 스킵 스캔이 도입되었다. 

 

스킵 스캔 처리 과정

  • MySQL 옵티마이저는 우선 선행 칼럼의 유니크한 값을 모두 조회
  • 주어진 쿼리에서 선행 칼럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리

 

인덱스 스킵 스캔 조건(단점)

  • WHERE 조건 절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야함
    • 옵티마이저가 인덱스에서 스캔해야할 시작 지점을 검색하는 작업이 많아지기때문
  • 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야함 (커버링 인덱스)

 

 

 

3.5 다중 칼럼 인덱스

복합 인덱스라고도 함

인덱스 내 각 칼럼의 위치(순서)가 상당히 중요

첫 번째 칼럼 - 두번째 - 세번재.. N번째 칼럼 순으로 정렬됨


컬럼마다 정렬 방식을 다르게 할 수 있음 

CREATE INDEX idx_t_name_userscore ON (team_name ASC, user_score DESC);

 

 

정렬은 인덱스 생성 시점에 오름차순 또는 내림차순으로 결정된다.

 

읽는 방향에 따라서 오름차순, 내림차순으로 결정됨 (실행 계획)

그저 읽는 방향만 바꾸면 따로 정렬하지 않아도 된다.

 

 

내림차순 인덱스

 

내림차순이 오름차순보다 불리하다. 

 

페이지 구조

페이지 잠금이 인덱스 정순 스캔에 적합한 구조

페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

 

 

즉, 페이지끼리는 양방향

페이지 내에서는 단방향

 

 

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

쿼리의 WHERE 조건이나 GROUP BY, ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있는지, 어떤 방식으로 사용할 수 있는지에 대해 알아본다.

 

비교 조건의 종류와 효율성

다중 칼럼 인덱스에서 각 칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교('='), 크다('>'), 작다('<') 같은 범위 조건인지에 따라

각 인덱스 칼럼의 활용 형태와 효율이 달라진다.

SELECT * FROM dept_emp 
WHERE dept_no='d002' AND emp_no >= 10114;

 

이 쿼리를 위해 dept_emp 테이블에 각각 칼럼의 순서만 다른 두가지 케이스로 인덱스 생성

  • 케이스 A : INDEX(dept_no, emp_no)
  • 케이스 B : INDEX(emp_no , dept_no)

케이스 A의 경우

dept_no = 'd002' && emp_no>= 10114인 레코드를 찾고 거기서부터 dept_no가 'd002'가 아닐때까지 읽으면 된다. 

 

케이스 B의 경우 (비효율)

emp_no >= 10114인 레코드를 찾고 그 이후의 모든 레코드를 읽으면서 dept_no = d002인지 비교(필터링)하는 과정을 거친다.

 

 

필터링(체크 조건) : 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업

작업 범위 결정 조건 : 작업의 범위를 결정하는 조건

 

작업 범위 결정 조건이 많을 수록 쿼리 처리 성능이 높아짐

체크 조건은 많을 수록 느려짐

 

 

인덱스의 가용성 

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬되어 있다.

이는 인덱스 값에 대해서, 다중 칼럼 인덱스에 대해서도 함께 적용된다.

 

하나의 칼럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능

다중 칼럼 인덱스에서도 왼쪽 칼럼의 값을 모르면 인덱스 레인지 스캔 불가능

 

 

가용성과 효율성 판단

기본적으로 B-Tree 인덱스의 특성상 다음 조건에서는 작업 범위 결정 조건으로 사용 불가,

경우에 따라서는 체크 조건으로 인덱스를 사용할 수는 있음

 

// NOT-EQUAL로 비교된 경우 
WHERE column <> 'N'
WHERE column NOT IN (1,2,3)
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

 

MySQL 에서는 NULL 값도 인덱스에 저장됨

따라서 아래와 같은 WHERE 조건도 작업 범위 결정 조건으로 인덱스를 사용

WHERE column IS NULL

 

 

다중 칼럼 인덱스에서 사용 가능 조건

INDEX ix_test( column_1, column_2,.. column_N)
  • 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
    • column_1 칼럼에 대한 조건이 없는 경우
    • column_1 칼럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
  • 작업 범위 조건으로 인덱스를 사용하는 경우 (i는 2보다 크고 n보다 작은 임의의 값을 의미)
    • column_1 ~ column_(i-1) 칼럼까지 동등 비교 형태( '=' or 'IN'  )로 비교
    • column_i 칼럼에 대해 다음 연산자 중 하나로 비교
      • 동등 비교 ( '=' or 'IN' )
      • 크다 작다 형태
      • LIKE로 좌측 일치 패턴

위 두가지 조건을 모두 만족하는 쿼리는 column_1부터 column_i까지 작업 범위 결정 조건으로 사용되고

column(i+1)부터 column_N까지의 조건은 체크 조건으로 사용된다. 

 

예제는 252p 참고

 

4. R-Tree

공간 인덱스로 2차원 데이터를 인덱싱하고 검색하는 목적의 인덱스

위치 기반 서비스를 MYSQL의 공간 확장 기능을 이용해 구현

 

  • 공간 데이터를 저장할 수 있는 데이터 타입
  • 공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
  • 공간 데이터 연산 함수 (거리 or 포함 관계의 처리)

 

지원하는 데이터 타입

Point, Line, Polygon, Gemometry(나머지 3개의 슈퍼 타입)

 

MBR(minimum bounding rectangle) : 해당 도형을 감싸는 최소 크기의 사각형

 

이 사각형들의 포함 관계를 B-Tree 형태로 구현한 인덱스가 R-Tree 인덱스

 

R로 시작하는 것들이 MBR

 

자세한건 253p부터 참조

 

 

 

 

5. 전문 검색 인덱스

B-Tree 인덱스는 실제 칼럼의 값의 3072Byte (InnoDB)까지만 잘라서 인덱스 키로 사용

 

문서 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문 검색에는 B-Tree 인덱스 사용 불가

 

5.1 인덱스 알고리즘

문서의 키워드를 인덱싱 하는 기법에 따라 크게 2가지로 분류

  • 단어의 어근 분석
    • 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업
    • 한국어의 경우 형태소 분석이 더 중요
    • 언어 학습 과정 필요 (오래걸림)
  • n-gram 분석 알고리즘
    • 키워드 검색 인덱싱 알고리즘
    • n은 인덱싱할 키워드 최소 글자 
    • 본문을 무조건 n글자씩 잘라서 인덱싱하는 기법
      • 각 글자를 중첩해서 토큰으로 구분

2-gram의 경우 

question 을 토큰으로 분리하면

qu, ue, es, st, ti, io, on 으로 분리됨

 

 

MySQL 서버는 생성된 토큰들에 대해 불용어를 걸러내는 작업을 수행하여 불용어를 포함하거나 그 자체일 경우 걸러서 버림

// MySQL 서버에 내장된 불용어 조회
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

 

MySQL 서버에 내장된 불용어보단 사용자가 직접 불용어를 등록해서 사용하는게 낫다.

 

불용어 처리를 무시하는 방법

1. 스토리지 엔진에 관계없이 MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거

MySQL 서버 설정 파일(my.cnf)의 ft_stopword_file 시스템 변수에 빈 문자열 설정

ft_stopword_file=''

 

 

2. InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 인덱스에 대해서만 불용어 처리를 무시

innodb_ft_enable_stopword 시스템 변수를 OFF로 설정

이는 동적인 시스템 변수이므로 서버 실행중에도 변경 가능

SET GLOBAL innodb_ft_enable_stopword=OFF;

 

 

사용자 정의 불용어 사용

1. 불용어 목록 파일을 파일로 저장하고 ft_stopword_file 설정에 등록

ft_stopword_file='/data/my_custom_stopword.txt';

 

2. 불용어 목록을 테이블로 저장 (InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 엔진에서만 사용가능)

// 테이블 생성 및 불용어 저장
CREATE TABLE my_stopwrord(value VARCHAR(30)) ENGINE = INNODB;
INSERT INTO my_stopword(value) VALUES ('MySQL');

// 불용어 지정
SET GLOBAL innodb_ft_server_stopword_table='mydb/my_stopword';
ALTER TABLE tb_bi_gram ADD FULLTEXT INDEX fx_title_body(title, body) WITH PARSER ngram;

 

 

5.2 전문 검색 인덱스의 가용성

전문 검색 인덱스 사용 조건

  • 쿼리 문장이 전문 검색을 위한 문법(MATCH , AGAINST)을 사용
  • 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유
CREATE TABLE tb_test (
    doc_id INT,
    doc_body TEXT,
    PRIMARY KEY (doc_id),
    FULLTEXT KEY fx_docbody (doc_bdy) WITH PARSER ngram
) ENGINE=InnoDB;

테이블 및 전문 검색 인덱스 생성

 

SELECT * FROM tb_test WHERE doc_body LIKE '$애플$';

해당 쿼리는 풀 테이블 스캔으로 쿼리를 처리한다. 

 

SELECT * FROM tb_test
WHERE MATCH(doc_body) AGAINST('애플' IN BOOLEAN MODE);

전문 검색 인덱스를 사용하려면 반드시 MATCH, AGAINST 구문으로 검색 쿼리를 작성

전문 검색 인덱스를 구성하는 칼럼들은 MATCH 절의 괄호 안에 모두 명시

 

6. 함수 기반 인덱스

칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축

  • 가상 칼럼을 이용한 인덱스
  • 함수를 이용한 인덱스
MySQL 서버의 함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있고,
실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일

 

 

가상 칼럼 인덱스

가상 칼럼을 만들고 AS로 별칭에 함수를 넣어 필요할때마다 계산하게 한다. 

ALTER TABLE user
ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
ADD INDEX ix_fullname (full_name);

VIRUAL, STORED 옵션 중 아무거나 해도 가상 칼럼에 인덱스 생성 가능

 

가상 칼럼은 테이블에 새로운 칼럼 추가하는 것과 같은 효과

→ 때문에 실제 테이블의 구조가 변경된다. 

 

함수를 이용한 인덱스

함수를 직접 사용하는 인덱스는 테이블의 구조를 변경 ❌

계산된 결괏값의 검색을 빠르게 만들어줌

INDEX ix_full_name ((CONCAT(first_name,' ', last_name))

주의점은 조건절에 반드시 함수 기반 인덱스에 명시된표현식이 그대로 사용되어야한다.

 

동일하게 했는데 인덱스 적용이 안된다면

시스템 변수의 값을 동일 콜레이션으로 일치 시킨후 테스트 해볼 것

  • collation_connection
  • collation_database
  • collation_server

 

7. 멀티 밸류 인덱스

하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스

JSON 데이터 타입에 대한 인덱스

 

CREATE TABLE test_json (
    info JSON,
    INDEX mx_info ((CAST(info-> '$.number' AS UNSIGNED ARRAY)))
)

INSERT INTO test_json VALUES ('{"info" : [1, 2, 3]}');

 

멀티 밸류 인덱스는 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행계획을 수립

  • MEMBER OF()
  • JSON_CONTAINS()
  • JSON_OVERLAPS()
SELECT info FROM test_json WHERE 1 MEMBER OF(info->'$.number');

DECIMAL, INTERGER, DATETIME 타입에 대해 멀티 밸류 인덱스를 지원

 

CHAR, VARCHAR는 현재 8.0.41버전에서도 미지원 

 

 

 

 

 

8. 클러스터링 인덱스

클러스터링 : 여러 개를 하나로 묶는다.

 

프라이머리 키를 기준으로 비슷한 값들을 동시에 죄회하는 경우가 많기에 

비슷한 키들끼리 묶는 인덱스

 

클러스터링 인덱스는 프라이머리 키에만 적용되며

키 값에 의해 레코드의 저장 위치가 결정된다.

키 값이 변경되면 레코드 저장 위치도 변경된다. (강한 의존)

실제 테이블에서의 레코드 위치가 변경되는 것은 아님

 

클러스터링의 구조 및 특징

클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장됨

 

프라이머리 키가 없는 InnoDB 테이블이 클러스터링 테이블로 구성하는 방법

  1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
  2. NOT NULL 옵션의 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택
  3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후 선택
    1. 이 경우 사용자에게 노출되지 않으며 쿼리 문장에 명시적으로 사용 불가, 의미없는 숫자 값이 클러스터링

가능하다면 프라이머리 키를 명시적으로 하는게 좋음

 

 

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

MyISAM, MEMORY 테이블 같은 클러스터링 되지 않은 테이블은 INSERT 될 때 처음 저장된 공간에서 절대 이동 X

프라이머리 키나 세컨더리 인덱스의 각 키는 레코드의 주소 (ROWID) 를 이용해 실제 데이터 레코드를 찾음

InnoDB는 세컨더리 인덱스는 리프노드에서 프라이머리 키를 갖기에 총 2번 접근해야함

 

복잡하지만 이에 대한 이점이 있다.

 

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

장점 - 프라이머리 키(클러스터링 키)로 검색시 처리 성능 매우 빠름 (특히, 프라이머리 키 범위 검색)
- 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기에 커버링 인덱스 가능
단점 - 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기에 키 값의 크기가 크면 전체 인덱스 크기가 커짐
- 세컨더리 인덱스 검색시 2번 탐색 
- INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정 (처리 성능이 느림)
- 프라이머리 키 변경 시 레코드를 DELETE 후 INSERT 하는 과정 필요 (처리 성능 느림)

 

일반적인 웹 서비스와 같은 온라인 트랜잭션 환경 (OLTP)에서는 쓰기와 읽기 비율이 1:9 or 2: 8 이기에

쓰기 속도를 낮추고 읽기 속도를 높인다.

 

  • 프라이머리 키는 AUTO-INCREAMENT 보다는 업무적인 칼럼으로 생성 (가능한 경우)
  • 프라이 머리 키는 반드시 명시
    • AUTO-INCREAMENT 칼럼을 이용해서라도 프라이머리 키 생성 권장
    • 미 지정시 내부적으로 일련번호를 칼럼에 추가 (이는 사용자에게 보이지 않아 접근 불가)
    • ROW 기반의 복제나 Innodb Cluster 에서 모든 테이블의 프라이머리 키를 가져야만 하는 정상적인 복제 성능을 보장
  • 복합 프라이머리 키의 경우 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 불필요시 그대로 프라이머리 키 사용
    • 세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO-INCREMENT 칼럼을 추가하고 이를 프라이머리 키로 사용 (인조 식별자)

 

 

9. 유니크 인덱스

제약 조건에 가까우며 인덱스나 테이블에 같은 값이 2개 이상 저장 불가

NULL은 특정 값이 아니여서 2개 이상 저장 가능

 

프라이머리 키도 NOT-NULL에 유니크 제약조건이므로 유니크 인덱스로 불 수 있지만

클러스터링 키 역할은 한다는 것이 차별점이다.

 

인덱스 읽기

유니크 인덱스 설정을 해도 성능의 차이는 미미하며

레코드를 여러개의 읽느냐의 차이만 존재하다.

 

인덱스 쓰기 

중복 값 체크 때문에 유니크 하지 않은 세던더리 인덱스보다 쓰기가 느림

 

중복 값 체크시 읽기 잠금

쓰기를 할 때는 쓰기 잠금

여기서 데드락이 아주 빈번히 발생

체인지 버퍼 불가 (중복 값 체크해야함)

 

결론

꼭 필요한 경우 사용, 성능을 향상을 목적으로 불필요하게 유니크 인덱스 생성은 자제

불필요한 인덱스 생성은 하지말자

 

10. 외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성 가능

외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성

외래키가 제거되지 않은 상태에서 자동으로 생성된 인덱스 삭제 불가

 

InnoDB의 외래키 관리에는 중요한 두가지 특징이 존재

  • 테이블 변경(쓰기 잠금)이 발생하는 경우 잠금 경합(잠금 대기)이 발생
  • 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생하지 않음

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

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

두가지  케이스가 있으며 자세한건  280p 참조 

 

 

 

참조

https://en.wikipedia.org/wiki/R-tree

저작자표시 비영리 변경금지 (새창열림)