[Real MySQL 8.0] 인덱스

디스크 읽기 방식

데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건.

HDD와 SSD

• HDD = 기계식 장치 ⇒ 데이터베이스 서버에서는 디스크 장치가 주로 병목이 된다.
• SSD = 전자식 저장 장치 ⇒ HDD보다 훨씬 빠름

순차 I/O는 SSD가 HDD보다 조금 빠르거나 비슷하다. SSD의 장점은 기존 하드 디스크 드라이브보다 랜덤 I/O가 훨씬 빠르다는 점!

랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적

💡 랜덤 I/O와 순차 I/O
공통점 : 플래터를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 후 데이터를 읽음
순차 I/O : 1번의 시스템 콜 요청 ⇒ 헤드 1번 움직임
랜덤 I/O : 3번의 시스템 콜 요청 ⇒ 헤드 3번 움직임
디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정!

 

원판이 없는 SSD는 랜덤 I/O와 순차 I/O의 차이가 없을 것 같지만, SSD 또한 랜덤 I/O는 순차 I/O보다 전체 스루풋이 떨어진다.

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용핮 ㅣ않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다.

인덱스란?

• 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만든다.
• DBMS의 인덱스는 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.
• 인덱스가 많은 테이블은 INSERT, UPDATE, DELETE 문장의 처리가 느리다.
  • SELECT 문장은 매우 빠르게 처리
• WHERE 조건절에 사용되는 칼럼이라고 전부 인덱스로 생성하면 저장 성능이 떨어지고 인덱스의 크기가 비대해져 역효과를 불러온다.
• B-Tree 알고리즘이 일반적으로 사용되는 인덱스 알고리즘이다.
• 유니크 인덱스 = 1건의 레코드만 찾으면 더 이상 찾지 않아도 됨

B-Tree 인덱스

구조 및 특성

• 인덱싱 알고리즘 가운데 가장 일반적이고 먼저 도입된 알고리즘
• 칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태를 유지
• 리프 노드에 항상 실제 데이터 레코드를 찾아가기 위한 주소값 존재
• 인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장

InnoDB에서 레코드는 클러스터되어 디스크에 저장 ⇒ 프라이머리 키 순서로 정렬되어 저장

• 인덱스는 테이블의 키 칼럼만 가지고 있으므로 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다.
• InnoDB 테이블은 프라이머리 키를 주소처럼 사용
  • InnoDB는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색해야 한다.

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

인덱스 키 추가

• 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색
  • 상대적으로 쓰기 작업에 비용이 많이 소모
• 디스크로부터 인덱스 페이지를 읽고 쓰기 하는 데에 시간 소모
• InnoDB 스토리지 엔진은 인덱스 키 추가 작업을 지연시켜 처리 가능
  • PK나 유니크 인덱스의 경우, 중복 체크로 인해 즉시 반영

인덱스 키 삭제

• 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 삭제 마킹
  • 디스크 쓰기가 필요하므로 디스크 I/O가 필요
• InnoDB 스토리지 엔진에서는 버퍼링되어 지연 처리될 수 있음

인덱스 키 변경

• 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리
• InnoDB 스토리지 엔진에서는 체인지 버퍼를 활용해 지연 처리

인덱스 키 검색

• B-Tree의 루트 노드부터 시작해 최종 리프 노드까지 이동하면서 비교 작업 수행
• 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용
• 부등호 비교 조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 사용 불가
• 인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없음
• InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현
  • UPDATE나 DELTE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠금

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

인덱스 키 값의 크기

• 인덱스는 페이지 단위로 관리
• B-Tree의 자식 노드는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정됨
• 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고 그만큼 느려진다.
• 인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스 크기가 커진다는 것을 의미
  • 인덱스를 캐시해두는 InnoDB의 버퍼 풀 크기는 제한적이기 때문에 하나의 레코드를 위한 인덱스 크기가 커질수록 메모리에 캐시해 둘 수 있는 레코드 수는 줄어듬

B-Tree 깊이

• MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결된 문제
• 인덱스 키 값의 크기가 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 줄어듬
  • 같은 레코드 건수라도 B-Tree의 깊이가 깊어져 디스크 읽기가 더 많이 필요
• 즉, 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다.

선택도

• 인덱스 키 값 가운데 유니크한 값의 수
• 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
  • 선택도가 좋지 않더라도 정렬이나 그루핑 같은 작업을 위해 인덱스를 만들 수도 있음
• 예시
  • tb_city(country, city)
    • 인덱스 : country
    • 1만 건의 레코드, 국가와 도시는 중복해서 저장되어 있지 않음
  • SELECT * FROM tb_city WHERE country = ‘KOREA’ AND city = ‘SEOUL’;
    • 케이스 A: country 컬럼의 유니크 값 10개
      • 하나의 키 값으로 검색했을 때 대략 1000건이 일치
      • 불필요하게 999건 읽음
    • 케이스 B: country 컬럼의 유니크 값 1000개
      • 하나의 키 값으로 검색했을 때 대략 10건 일치
      • 불필요하게 9건 읽음
• 인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다.

읽어야 하는 레코드의 건수

• 인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업
• 100만 건 중 50만 건을 읽어야 하는 상황
  • 전체 테이블을 읽어서 50만건을 버리는 것과, 인덱스를 통해 50만 건만 읽는 것 중 어떤 것이 효율이 좋을까?
• 일반적인 DBMS 옵티마이저에서 인덱스를 통해 레코드 1건 읽는 것이 테이블에서 직접 레코드 1건 읽는 것보다 4~5배 비용이 더 많이 드는 작업
• 즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블의 20~25%를 넘어서면 테이블을 모두 읽어서 필요한 레코드만 가려내는 방식을 처리하는 것이 효율적

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

인덱스 레인지 스캔

• 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식
• 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다.
• 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어올 때 랜덤 I/O가 한 번씩 일어난다.
  • 20~25%를 넘으면 직접 읽는게 낫다.
• 과정
  • 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다.
  • 찾은 위치부터 필요한 만큼 인덱스를 차례대로 쭉 익는다
  • 읽은 인덱스 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽는다.
    • 쿼리가 필요로 하는 데이터에 따라 3번 과정은 없을 수 있음 (커버링 인덱스)

인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는 방식
• 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우에 사용
  • 인덱스 = (A, B, C), 쿼리의 조건절은 B 컬럼이나 C 컬럼으로 검색되는 경우
• 일반적으로 인덱스의 크기가 테이블의 크기보다 작으므로 직접 테이블을 다 읽는 것보다 효율적
  • 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 사용
  • 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다.
• 인덱스 레인지 스캔보다는 빠르지 않지만, 테이블 풀 스캔보다는 효율적
• 일반적으로 인덱스를 생성하는 목적 X

루스 인덱스 스캔

• 듬성듬성 인덱스를 읽는 방식
• 인덱스 레인지 스캔과 비슷하게 작동하지만, 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어간다.
• 예시
  • SELECT dept_no, MIN(emp_no) FROM dept_emp WHERE dept_no ‘d002’ AND ‘d004’ GROUP BY dept_NO;
  • 인덱스 : (dept_no, emp_no)
  • WHERE 조건을 만족하는 범위 전체를 스캔할 필요 없음
  • dept_no 그룹별로 첫 번째 레코드의 emp_no 값만 읽는다.

인덱스 스킵 스캔

• (gender, birth_date)가 인덱스인 상황에서 gender 컬럼에 대한 비교 조건이 없어도 인덱스 검색이 가능
  • SELECT gender, birth_date FROM employees WHERE birth_date ≥ ‘1965-02-01’;
  • 옵티마이저는 gender 컬럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 gender 컬럼의 조건을 추가해 쿼리를 다시 실행하는 형태로 처리
• WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 함
• 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함 (커버링 인덱스)

다중 컬럼 인덱스

• 두 개 이상의 컬럼으로 구성된 인덱스
• 인덱스의 n 번째 컬럼은 n-1 번째 컬럼에 의존하여 정렬
  • 두 번째 컬럼의 정렬은 첫 번째 컬럼이 똑같은 레코드에서만 의미가 있음

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

• 설정 정렬 규칙에 따라 인덱스의 키 값은 오름차순이거나 내림차순으로 정렬되어 저장
• 어느 방향으로 읽을지 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정

인덱스의 정렬

• 인덱스 생성 시점에 구성하는 컬럼의 정렬을 오름차순 또는 내림차순으로 설정
• 정렬 순서가 혼합된 인덱스도 생성 가능
  • CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

인덱스 스캔 방향

• 인덱스를 최소값부터 읽으면 오름차순, 최댓값부터 읽으면 내림차순
• 쿼리가 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과

내림차순 인덱스

• 천만 건의 레코드와 오름차순 인덱스가 있는 상황
  • SELECT * FROM t1 ORDER BY tid ASC : 4.15 sec
  • SELECT * FROM t1 ORDER BY tid DESC : 5.35 sec
  • 역순 정렬 쿼리가 28.9% 시간이 더 소요
• 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느리다.
  • 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
  • 페이지 내 인덱스 레코드가 단방향으로만 연결된 구조
• 많은 쿼리가 인덱스의 앞쪽 또는 뒤쪽만 집중적으로 읽어서 특정 페이지 잠금이 병목될 것으로 예상된다면,
  • 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 병목 완화에 도움

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

비교 조건의 종류와 효율성

• 다중 컬럼 인덱스에서 컬럼의 순서와 조건에 따라 인덱스 컬럼의 활용 형태가 달라진다.
• SELECT * FROM dept_emp WHERE dept_no=’d002’ AND emp_no ≥ 10114;
  • 케이스 A : INDEX (dept_no, emp_no)
    • “dept_no=’d002’ AND emp_no ≥ 10144”인 레코드를 찾고 dept_no가 ‘d002’가 아닐 때까지 인덱스를 쭉 읽음
  • 케이스 B : INDEX (emp_no, dept_no)
    • “emp_no ≥ 10144 AND dept_no=’d002’”인 레코드를 찾고, 이후 모든 레코드에 대해 dept_no가 ‘d002’인지 비교
• 작업의 범위를 결정하는 조건 = 작업 범위 결정 조건
• 비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건 = 필터링 조건
• 작업 범위를 결정하는 조건은 많을수록 쿼리의 처리 성능을 높인다.

인덱스의 가용성

• B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬 (Left-most)
• 인덱스 키 값의 정렬 특성은 빠른 검색의 전제 조건
• SELECT * FROM employees WHERE first_name LIKE ‘%mer’;
  • 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수 없음
  • 왼쪽 부분 고정 X
• SELECT * FROM dept_emp WHERE emp_no ≥ 10144;
  • 인덱스가 (dept_no, emp_no) 컬럼 순서대로 생성돼 있다면 인덱스를 효율적으로 사용할 수 없음

R-Tree 인덱스

• 공간 인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스
• MySQL의 공간 확장
  • 공간 데이터를 저장할 수 있는 데이터 타입
  • 공간 데이터 검색을 위한 공간 인덱스
  • 공간 데이터의 연산 함수

구조 및 특성

• 여러 기하학적 도형 정보 관리 데이터 타입
  • GEOMETRY
    • POINT
    • LINE
    • POLYGON
• MBR = 도형을 감싸는 최소 크기의 사각형
  • 최하위 MBR = 각 도형 데이터의 MBR (R-Tree의 리프 노드)
  • 차상위 MBR = 중간 크기의 MBR (도형 객체 그룹, R-Tree의 브랜치 노드)
  • 최상위 MBR = R-TREE 루트 노드에 저장되는 정보

R-Tree 인덱스의 용도

• 위도, 경도 좌표 저장에 주로 사용
• ST_Contains(), ST_Within() 과 같은 포함 관계를 비교하는 함수로 검색을 수행하는 경우에만 인덱스 사용
  • ST_Distance(), ST_Distance_Sphere() 함수는 공간 인덱스를 효율적으로 사용하지 못함

전문 검색 인덱스

• 문서 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색

인덱스 알고리즘

• 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해 내고, 빠른 검색용으로 사용할 수 있게 이러한 키워드로 인덱스 구축

어근 분석 알고리즘

• 불용어 처리
  • 가치가 없는 단어를 필터링하여 제거
• 어근 분석
  • 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업

n-gram 알고리즘

• 키워드를 검색해내기 위한 인덱싱 알고리즘
• 분문을 무조건 몇 글자씩 잘라서 인덱싱

함수 기반 인덱스

• 컬럼의 값을 변형해서 만들어진 값에 대해 인덱스 구축
• 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일

가상 컬럼을 이용한 인덱스

• 가상 컬럼을 추가하고 가상 컬럼에 인덱스 생성
• 가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같은 효과
  • 실제 테이블의 구조가 변경

함수를 이용한 인덱스

• 함수를 직접 사용하는 인덱스
• 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되어야 함

멀티 밸류 인덱스

• 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스
• JSON 배열 타입의 필드에 저장된 원소들에 대한 인덱스 요건 발생
• 함수를 사용하여 검색해야 옵티마이저가 인덱스를 활용한 실행 계획 수립
  • MEMBER OF()
  • JSON_CONTAINS()
  • JSON_OVERLAPS()

클러스터링 인덱스

• 클러스터링은 테이블의 레코드를 비슷한 것들끼리 묶어서 저장하는 형태로 구현
• 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장
• 프라이머리 키 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 변경이 상대적으로 느리다.
• 클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장
• 프라이머리 키가 없는 경우 다음의 우선순위 적용
  • 프라이머리 키 가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
  • 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택
  • 자동으로 유니크 값을 가지도록 증가하는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택
• 클러스터링 인덱스는 테이블 당 하나만 가질 수 있는 엄청난 혜택

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

• InnoDB의 모든 세컨더리 인덱스는 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현
• 세컨더리 인덱스를 검색해 레코드의 프라이머리 키 값을 확인 후, 프라이머리 키 인덱스를 검색해 최종 레코드를 가져온다.
  • InnoDB 테이블에서 프라이머리 키(클러스터링 인덱스)는 더 큰 장점을 제공하기 때문에 성능 저하에 대해 걱정 X

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

• 장점
  • 빠른 읽기
• 단점
  • 느린 쓰기
• 일반적인 서비스에서 쓰기와 읽기의 비율이 2:8 또는 1:9 이기에 느린 쓰기를 감수하고 읽기를 빠르게 유지

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

클러스터링 인덱스의 크기

• 프라이머리 키 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커짐
• 인덱스가 커질수록 같은 성능을 내기 위해 메모리가 더 필요

프라이머리 키는 AUTO-INCREMENT보다는 업무적인 컬럼으로 생성

• 프라이머리 키로 검색하는 경우, 클러스터링되지 않은 테이블에 비해 매우 빠르게 처리
• 프라이머리 키는 의미만큼이나 중요한 역할을 하기에 대부분 검색에서 상당히 빈번하게 사용

프라이머리 키는 반드시 명시

• 가능하면 AUTO_INCREMENT 컬럼을 이용해서라도 프라이머리 키는 생성
• 모든 테이블이 프라이머리 키를 가져야만 하는 정상적인 복제 성능을 보장

AUTO_INCREMENT 컬럼을 인조 식별자로 사용할 경우

• 프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자(Surrogate key)라 함
• 로그 테이블 같이 조회보다는 INSERT 위주의 테이블은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움

유니크 인덱스

• 인덱스라기보다는 제약 조건에 가까움

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

인덱스 읽기

• 세컨더리 인덱스에서 한 번 더 해야하는 작업은 디스크 읽기가 아니라 CPU에서 컬럼값을 비교하는 작업이라 성능 상 영향 X
• 읽어야 할 레코드 건수가 같다면 성능상 차이는 미미

인덱스 쓰기

• 유니크 인덱스의 키 값을 쓸 때는 중복 여부를 체크하는 과정이 필요하여 쓰기 속도가 느림
• 유니크 인덱스 중복 체크는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는 데 이 과정에서 데드락이 빈번히 발생
• InnoDB 엔진은 인덱스 키 저장을 버퍼링하기 위해 체인지 버퍼를 사용하지만, 유니크 인덱스는 버퍼링 불가
• 일반 세컨더리 인덱스보다 변경 작업이 느림

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

• 불필요하게 유니크 인덱스를 생성하지 않는게 좋음
• 유니크 인덱스는 일반 다른 인덱스와 같은 역할을 하므로 중복할 필요 X
  • 유니크 + 세컨더리
  • 유니크 + 프라이머리 키
• 유일성이 꼭 보장돼야 하는 컬럼에 대해서는 유니크 인덱스 생성
• 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스 생성 고려

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

• 자식 테이블의 외래 키 컬럼의 변경은 부모 테이블의 확인이 필요
• 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다림

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

• 자식 테이블의 레코드 수정 중 부모 테이블 레코드 삭제 시도시 대기
  • 자식 테이블이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE) 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되기 때문