2장. 인덱스와 인덱싱

Study/MySQL을 더 빠르게, 성능최적화 선택과 집중

2장. 인덱스와 인덱싱

주지민 2024. 8. 27. 22:20

본 포스트는 사내 스터디를 진행하며 정리한 포스트입니다

장을 본격적으로 들어가기전

인덱스는 최고이자 최상의 영향력을 미치기에 대량의 데이터에서는 인덱스가 필수적이다
MySQL 성능에는 적절한 인덱스와 인덱싱이 필요하며, 이번 장에서는 두 가지에 대해 자세히 설명한다
하드웨어와 MySQL 튜닝이 MySQL 성능을 향상하기 위한 효율적인 해결책이 아니다(일정 수준이 지나면 비용대비 성능향상이 안되서 그런듯..)
어떻게 인덱스를 생성해야 최대의 효과를 발휘할 수 있는지
인덱스가 효과를 발휘하지 못하는 이유
효과적인 테이블 조인 알고리즘 설명(적절한 인덱스 설명이될듯)

스케일업을 고민해야하는 상황

일반적으로 MySQL 성능이 만족스럽지 않다고해서 스케일업, 하드웨어 업그레이드부터 생각하지 마라
하드웨어 성능 부족으로 명백하게 느린 거라면 적절한 성능의 하드웨어로 스케일업해야한다
어플리케이션의 사용량이 급증하고 있으며, 하드웨어 스케일 업이 어플리케이션 실행에 있어 안정성을 담보하기 위한 임시 방편이라면 적용해봐도 좋다 (서비스 안정성이 급선무이므로)
레드헤링: 목표로부터 주의를 딴 데로 돌리는 상황(딴짓)

앞서 소개한 최적화에 목숨을 걸어야하는 이유

튜닝
- MySQL 시스템 변수를 조정하는 행위
- 구체적인 목표와 기준이 있는 실험실 수준의 작업
구성
- 시스템 변수를 하드웨어와 환경에 적합한 값으로 설정하는 행위
- MySQL 인스턴스가 프로비저닝되거나 하드웨어가 변경될 때 수행
최적화
- MySQL 튜닝은 통제된 실험실에서 이루어지는 실험처럼 수행되지 않을 때가 많아 결과가 의심되고, 이미 고도로 최적화되었으므로 튜닝의 결과는 기대보다 성능에 큰 영향을 미치지 않는다 (성능향상을 꾀하기 정말 어렵다)
- 고로 튜닝보다는 최적화에 목숨을 걸어야한다

본격 인덱스 소개

앞으로 소개될 내용은 InnoDB 테이블의 표준인덱스, PRIMARY KEY와 [UNIQUE] INDEX에만 적용됩니다
다른 특별한 인덱스 유형도 지원하지만 책에서 다루진 않습니다

InnoDB 테이블은 인덱스다

InnoDB 테이블의 실제 구조는 PRIMARY_KEY로 구성된 B-트리 인덱스입니다
참고) PRIMARY_KEY가 없다면 InnoDB는 내부적으로 자동으로 Primary Key 역할을 할 수 있는 숨겨진 클러스터링 키를 생성한다

Root, Internal, Leaf의 첫번째 노드가 PK(클러스터링 키)
... 으로 표기된 부분은 로우락, 트랜잭션 격리등에 사용되는 메타데이터
위와 같은 구조덕에 아래 2가지 효과를 얻을 수 있습니다
- 프라이머리 키(PK) 조회(lookup)는 매우 빠르고 효율적이다
- PK는 MySQL 성능에서 핵심적인 역할을 한다
InnoDB PK 키는 클러스터 인덱스라고도 부릅니다

세컨더리 인덱스(논 클러스터링 인덱스)도 B-트리 구조의 인덱스 형태이며, 리프 노드에 PK값을 저장한다
ex) SELECT * FROM elem WHERE a='Au' AND b='Be' 동작 순서
- 1. 세컨더리 인덱스 Ag, B..Au, Be 조회(Root)
- 2. 세컨더리 인덱스 Ar, Br..Au, Be 조회(Internal)
- 3. 세컨더리 인덱스 Au, Be 조회(Leaf)
- 4. 3번에서 조회된 Leaf 노드의 PK값(2) 조회
- 5. PK 인덱스 1..4 조회(Root)
- 6. PK 인덱스 1..2 조회(Internal)
- 7. PK 인덱스 2 조회후 value 리턴(Leaf)
테이블은 PK를 오직 하나만 가질 수 있고, 다른 인덱스는 모두 세컨더리 인덱스이다

테이블 접근 방법

인덱스 조회
- 위에서 봤던 인덱스의 정렬된 구조와 접근 알고리즘을 활용하여 특정 행이나 행 범위를 찾습니다
- 가장 빠르면서 효과적인 접근 방법
- 성능을 제대로 발휘하려면 실질적으로 모든 쿼리에서 모든 테이블을 대상으로 인덱스 조회를 사용해야한다
인덱스 스캔
- 인덱스 조회가 불가능할 때 MySQL은 전체 데이터 순차 찾기 같은 억지 기법으로 행을 찾는다, 즉 모든 행을 읽고 일치하지 않는 행을 필터링
- MySQL은 PK로 모든 행을 읽기전에 세컨더리 인덱스로 행 읽기를 시도하는데, 이를 인덱스 스캔이라고 한다
- 유형
  - 풀 인덱스 스캔
    - 인덱스 순서대로 모든 행을 읽습니다
    - 모든 행을 읽는 것은 일반적으로 성능에 매우 불리하지만 인덱스 순서가 ORDER BY 쿼리와 일치할 때 행 정렬을 피할 수 있습니다
    - 세컨더리 인덱스를 먼저 순서대로 스캔하기 때문에 세컨더리 입장에서는 순차 읽기이지만 PK 조회는 거의 무작위 읽기입니다(세컨더리 Leaf에 저장된 PK가 무엇인지 모르기때문에)
    - 그냥 궁금해서 찾아본 PK 순서가 순차적일때 효과
      - 삽입 시의 디스크 I/O를 줄일 수 있다
        
        코드를 삽입할 때 새로운 레코드가 마지막 위치에만 추가되므로, 중간에 데이터를 삽입할 때 발생할 수 있는 페이지 분할이 줄어듭니다. 페이지 분할이 발생하면 디스크 I/O가 증가하고 성능이 저하될 수 있다
        
        데이터가 디스크에 연속적으로 저장되므로 디스크의 물리적 읽기/쓰기가 효율적으로 수행
      - B-Tree 인덱스 구조를 최적화하여 성능을 향상을 꾀할 수 있음
        
        B-Tree 구조가 보다 균형 잡힌 상태를 유지
        
        순차적 키는 인덱스 페이지의 캐시 효율성을 높여, 메모리 사용량을 줄이고 성능을 향상
      - 보안 이슈나 특수한 성능 요구사항이 있는 경우에는 주의를 기울여야 한다
        
        다만 ID 예측 가능성이 존재
        
        매우 높은 삽입율을 가진 시스템에서는 순차적 Primary Key의 경우, 오토 인크리먼트의 동기화로 인해 병목이 발생할 수 있습니다
  - 인덱스 전용 스캔
    - MySQL은 인덱스에서 열값(전체 행이 아님)을 읽는다
    - 이를 위해 커버링 인덱스가 필요
    - 전체 행을 읽기 위해서 프라이머리 키 조회를 해야하는 상황이 아니므로 풀 인덱스 스캔보다 빠르게 동작
- 유일한 대안이 풀 테이블 스캔이 아닌 이상 인덱스 스캔으로 최적화를 시도하지마라
테이블 스캔
- 풀 테이블 스캔은 PK 순서로 모든 행을 읽습니다
- 인덱스 조회나 인덱스 스캔을 수행할 수 없을 때 선택할 수 있는 유일한 옵션이다
- 일반적으로 성능면에서 매우 불리하다 ( 그러나 쉽게 수정할 수 있다 )
- 테이블 스캔을 허용하거나 더 나은 경우
  - 테이블이 작고 접근 빈도가 낮을 때
  - 테이블 선택도가 매우 낮을 때

맨 왼쪽 접두사(leftmost prefix) 요구사항

인덱스를 사용하려면 쿼리는 인덱스의 맨 왼쪽 접두사 즉 인덱스 정의에서 지정한 맨 왼쪽 인덱스 열로 시작하는 하나 이상의 인덱스 열을 반드시 사용해야한다
인덱스 구조가 인덱스 열 순서에 따라 정렬되므로 맨 왼쪽 접두사가 필요하며 그 순서로만 탐색할 수 있다
복합 인덱스의 순서를 고민해야한다
인덱스(a,b,c)가 순서를 가진다면 반드시 a(맨 왼쪽 접두사)부터 검색조건이 시작되어야한다. b부터 시작될 경우 해당 인덱스를 검색조건으로 사용할 수 없다
인덱스(a,b)와 인덱스(b,a)는 다른 인덱스이다
인덱스(a)와 인덱스(a,b)는 중복되어 인덱스(a)는 삭제될 수 있다
모든 세컨더리 인덱스의 끝에는 PK가 숨겨져 있다(MySQL은 세컨더리 인덱스에 추가된 PK를 표시하지 않는다)
인덱스가 클수록 더 많은 메모리가 필요하고, 이는 인덱스가 작을수록 메모리를 더 적게 사용한다라는 의미이다
- PK의 크기를 작게 유지하고 세컨더리 인덱스 수를 적절하게 유지해야한다

EXPLAIN: 쿼리 실행 계획

EXPLAIN 명령은 MySQL이 쿼리를 실행하는 방법을 설명하는 쿼리 실행계획 또는 EXPLAIN 계획을 보여준다
EXPLAIN 실행 계획 출력 형식

MySQL :: MySQL 8.4 Reference Manual :: 10.8.2 EXPLAIN Output Format

10.8.2 EXPLAIN Output Format The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. EXPLAIN returns a row of information for each table used in the SELECT

dev.mysql.com

실행 계획 출력 형식은 계속해서 진보하고 있지만 기본형식 자체는 수십년간 유지되고 있다

필드 설명
- table
  - 테이블 이름이나 참조된 서브 쿼리
  - 테이블은 쿼리에 보여지는 순서가 아니라 MySQL이 결정한 조인 순서로 나열된다
  - 맨위가 첫번째 테이블이고 맨 아래가 마지막 테이블
- partitions
  - 쿼리에서 레코드가 매치되는 파티션
  - 파티션 테이블이 아닌경우 null
- type
  - 테이블 접근 방법이나 인덱스 조회의 접근 유형
  - ALL: 풀 테이블 스캔
  - index:
    - 인덱스 스캔
    - 테이블에 대한 모든 인덱스의 값을 스캔하는 접근 방법
    - 테이블 전체 데이터를 읽지않고, 인덱스의 순서대로 값을 가져오기 때문에 풀 테이블 스캔보다 성능이 좋다
    - 모든 인덱스의 레코드를 읽고, 필요하다면 그에 해당하는 실제 테이블의 데이터를 가져옵니다. 일반적으로 커버링 인덱스를 사용할 때, 즉 쿼리가 인덱스만으로도 필요한 데이터를 모두 가져올 수 있을 때 발생한다
    - 테이블 전체를 스캔하는 것보다는 효율적이지만, 여전히 인덱스의 모든 레코드를 순차적으로 읽어야 하기 때문에, 데이터가 많으면 성능에 영향을 미칠 수 있다
  - const, ref, range: 인덱스 조회의 유형들
    - ref
      - 인덱스의 맨 왼쪽 접두사에 대한 동등(= 또는 <=>) 조회이다
      - 다른 인덱스 조회와 마찬가지로 ref 접근은 조회할 예상 행의 수가 적절하다면 매우 빠르다
    - range
      - 인덱스를 사용하여 특정 범위의 레코드만을 검색하는 방식입니다.
      - 범위 조건이 있는 경우에 사용
      - 검색 범위를 좁힐 수 있고, 필요한 레코드들만 조회하기 때문에 읽어야 할 데이터 양이 줄어든다
- possible_keys
  - MySQL이 사용할 수 있는 인덱스를 나열
  - 사용할 수 있는 인덱스가 없으면 null
- key
  - MySQL이 사용할 인덱스의 이름(possible_keys중에 선택된 인덱스)
  - 사용할 수 있는 인덱스가 없으면 null
  - MySQL은 많은 요소를 기반으로 최상의 인덱스를 선택하며, 그중 일부는 Extra 필드에 표시된다
- ref
  - 인덱스에서 행을 조회하는 데 사용되는 값의 소스를 나열
  - 단일 테이블 쿼리나 조인의 첫 번째 테이블에서 ref는 종종 하나 이상의 인덱스 열에 대한 상수 조건을 나타내는 const이다
  - 여러 테이블을 조인하는 쿼리에서 ref는 조인 순서상 이전 테이블의 열 참조이다
- rows
  - MySQL이 일치하는 행을 찾기 위해 조회할 예상 행의 수
  - 인덱스 통계를 사용하여 행을 추정하므로 실제 값과는 근사하지만 같지는 않다
- Extra
  - 쿼리 실행 계획에 대한 부가 정보를 제공(MySQL이 적용할 수 있는 쿼리 최적화를 나타낸다)
  - MySQL이 인덱스만 사용하여 일치하는 행을 찾을 수 있다면 NULL이 나온다
  - ex) Using Index condition; Using where
  - 참고문서

MySQL :: MySQL 8.4 Reference Manual :: 10.8.2 EXPLAIN Output Format

dev.mysql.com

WHERE

MySQL은 인덱스를 사용하여 WHERE절의 테이블 조건과 일치하는 행을 찾을 수 있습니다
테이블 조건은 열과 해당 열값으로 이루어지며, 이 조건과 일치하는 행을 찾거나, 행을 그룹화하고, 집계, 정렬하기 위해 사용한다
테이블 조건이 인덱스 열의 맨 왼쪽 접두사라면 해당 인덱스를 사용할 수 있습니다

예제 설명
- table: elem: 조회할 테이블이 elem
- partitions: NULL: 조회할 파티션 테이블이 없음(파티션 테이블이 아님)
- type: range: 범위 스캔, 인덱스를 사용하여 값 범위 사이의 행을 읽는다
- possible_keys: PRIMARY: 사용할 수 있는 키는 PK
- key: PRIMARY: 사용할 키는 PK
- ref: NULL
  - id열의 조건이 상수가 아니므로 NULL(조회 열 조건 값이 한개일때 const)
  - 단일 테이블 쿼리이므로 참조할 선행 테이블이 없다
- Extra: Using where
  - MySQL이 WHERE 조건을 사용하여 일치하는 행을 찾는다는 의미
  - MySQL이 범위의 행을 일치시키는데 사용할 것은 실제로 c열의 조건뿐(id 열의 조건이 범위를 정의)

GROUP BY

MySQL은 값이 인덱스 순서에 따라 암묵적으로 그룹화되므로 GROUP BY를 최적화하기 위해 인덱스를 사용할 수 있습니다

type: index:
- 인덱스 스캔
- 행을 필터링하는 WHERE절이 없으므로 MySQL은 모든 행을 읽는다
key: idx_a_b: 인덱스 idx_a_b를 통해 GROUP BY를 최적화하는 것을 알 수 있다
Extra: Using index: MySQL이 인덱스에서 오직 a열의 값만 읽고 PK에서 전체 행을 읽지 않음을 나타낸다

key: idx_a_b
- GROUP BY b, 가지고있는 인덱스의 맨 왼쪽 접두사가 없는대도 인덱스를 사용하는 것을 확인할 수 있다
- type: index, 인덱스 스캔을 하고있어서 충족되는 것인데 해당 인덱스에는 b열의 값이 있기때문이다
- GROUP BY c, 인덱스가 없는 값에는 type: ALL로 풀 테이블 스캔을 하게된다

ORDER BY

MySQL은 ORDER BY를 최적화하기 위해 정렬된 인덱스를 사용할 수 있다
최적화하면 순서대로 행에 접근하기 떄문에 시간이 조금 더 걸리는 행 정렬을 피할 수 있다
만약 최적화가 되어있지 않다면, MySQL은 일치하는 모든 행을 읽고, 정렬(파일 소팅)한 다음 정렬된 결과 세트를 반환한다
정렬(파일 소팅)은 Extra: Using filesort로 나타난다

type: ref
- WHERE a = 'Ar' ORDER BY b 에서 처음 조건 a = 'Ar' 조건이 인덱스 (a, b)의 상수이므로 해당 인덱스를 사용할 수 있다
- MySQL은 a = 'Ar'로 이동하고 거기에서 b열값을 순서대로 읽는다
Extra: Using idex
- 위에서 소개했던 Extra: Using filesort는 사용하지 않았다
- 당연하게도 ORDER BY 조건으로 사용하는 b 컬럼은 이미 인덱스 (a,b)에서 순서대로 정렬되어있으므로 추가 파일정렬을 할 필요가 없다

EXPLAIN SELECT * FROM elem WHERE a = 'Al' AND b = 'B' ORDER BY id\G

위 쿼리는 idx_a_b 인덱스를 이용하고 파일정렬은 일어나지 않는다
모든 세컨더리 인덱스 끝에는 PK가 숨겨져 있기 때문에 파일정렬을 피할 수 있다
인덱스 컨디션 푸시다운
- Extra: Using index condition;
- 스토리지 엔진이 인덱스를 사용하여 WHERE 조건과 일치하는 행을 찾는다는 의미
- 일반적으로 스토리지 엔진은 행을 읽고 쓰기만 하며, MySQL은 일치하는 행을 찾는 로직을 처리한다
- 인덱스 컨디션 푸시다운 최적화 방법

MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.6 Index Condition Pushdown Optimization

10.2.1.6 Index Condition Pushdown Optimization Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and

dev.mysql.com

인덱스 정렬은 기본적으로 오름차순
ORDER BY 최적화는 ASC(오름차순), DESC(내림차순)든지 모든 열에 대해 한 방향으로만 작동

파일 정렬의 실시간 패널티

MySQL 8.0.18 이전에는 측정되거나 보고되지 않았다
EXPLAIN ANALYZE 명령어를 통해 측정하여 보고

EXPLAIN ANALYZE SELECT c FROM sbtest1 WHERE k < 450000 ORDER BY id\G

*************************** 1. row ***************************
1 -> Sort: sbtest1.id  (cost=83975.47 rows=133168)
2    (actual time=1221.170..1229.306 rows=68439 loops=1)
3    -> Index range scan on sbtest1 using k_1, with index condition: (k<450000)
4       (cost=83975.47 rows=133168) (actual time=40.916..1174.981 rows=68439)

EXPLAIN ANALYZE 결과 4번째줄의 actual time은 인덱스 범위 스캔(type: range)에 소요된 시간을 의미(40.916에 시작되어 1174.981에 종료)
EXPLAIN ANALYZE 결과 2번째줄의 actual time은 파일정렬에 걸린 시간을 의미
파일정렬 자체는 느리지않다(걸리는 시간이 미미하다)
- 단 디스크의 임시파일을 사용하게될 경우는 확인해봐야한다
- MySQL은 데이터 정렬이 sort_buffer_size를 초과할 때 디스크의 임시 파일을 사용하게 된다
- 이는 메모리보다 수십배 느리다(SSD를 사용한다면 일반적으로 이경우도 빠를 수 있다)
- 궁금증 sort_buffer_size VS innodb_sort_buffer_size
  - 적용 대상:
    - sort_buffer_size: 모든 스토리지 엔진에서 정렬 작업에 사용됩니다.
    - innodb_sort_buffer_size: InnoDB 엔진에서 인덱스 생성 작업에 사용됩니다.
  - 사용 상황:
    - sort_buffer_size: 쿼리에서 ORDER BY, GROUP BY, DISTINCT 등의 정렬 작업에 적용됩니다.
    - innodb_sort_buffer_size: InnoDB 테이블에서 ALTER TABLE 명령으로 인덱스를 생성하거나 재생성할 때 적용됩니다.
EXPLAIN ANALYZE 명령어는 쿼리를 실행한다. 원본 테이블에 적용할 경우 주의해서 사용해야한다

커버링 인덱스

커버링 인덱스에는 쿼리가 참조하는 모든 열이 포함되는걸 의미

위 사진처럼 WHERE 조건은 사용하려는 인덱스 열을 가르키고, 해당 인덱스 열이 SELET 절의 해당 열을 다시 가르키기도 하여 인덱스만으로 이러한 열값을 읽었음을 나타낸다
일반적으로 MySQL은 PK에서 전체 행을 읽지만, 커버링 인덱스를 사용하면 인덱스에서 열값만 읽을 수 있다
이로써 PK 조회를 피할 수 있어 세컨더리 인덱스에 가장 유용하다
MySQL은 자동으로 커버링 인덱스 최적화를 사용하며 EXPLAIN은 이를 Extra 필드에 Using index로 보고한다
단점
- 단 커버링 인덱스를 만드려고 너무 많은 시간을 낭비하지 말라
- 실제 현업에서 인덱스 하나가 너무 많은 열과 조건을 맡기에는 힘들다

테이블 조인

MySQL은 테이블 조인에 인덱스를 사용하며, 사용법은 다른 것에 인덱스를 사용하는 것과 기본적으로 같다

기존 인덱스 사용과 거의 동일하다
조인되는 테이블의 인덱스 사용에서의 차이점
- WHERE절은 JOIN..ON절을 재작성한 것
- 매칭되는 symbol값은 선행테이블에서 가져온다

EXPLAIN SELECT name
        FROM elem JOIN elem_names ON (elem.a = elem_names.symbol)
        WHERE a IN ('Ag', 'Au', 'At')\G

설명에 따라 위와같은 EXPLAIN 계획을 보여준다
type: eq_ref: PK나 유니크 not-null 세컨더리 인덱스를 사용하는 단일 행 조회를 의미
ref: test.elem.a
- 참조열 elem.a로 읽는다
- test는 데이터베이스 이름
테이블별로 조인이 인덱스 사용법을 변경하지않는다
주요 차이는 조인 조건의 값이 선행 테이블에서 온다는 것
MySQL은 어떤 접근 방법이든 사용하여 테이블을 조인할 수 있지만 eq_ref 접근 유형을 사용하는 인덱스 조회가 가장 성능이 좋다 (한 행만 일치하기 때문에)
eq_ref 조건
- PK나 유니크(not-null) 세컨더리 인덱스
- 모든 인덱스 열의 동일 조건
쿼리를 조금만 변경해도 테이블 조인 순서나 쿼리 실행계획이 크게 달라질 수 있으므로 테이블 조인 순서를 추측하거나 추정해서는 안된다

풀 조인

MySQL은 인덱스없이 테이블 조인을 할 수 있다
쿼리가 수행할 수 있는 최악의 작업
조인된 테이블에 대한 테이블 스캔은 한 번 발생하는 것이 아니라 선행 테이블에 일치하는 모든 행에 대해 발생하므로 풀 조인이 단일 테이블 풀 테이블스캔보다 훨씬 나쁘다

EXPLAIN SELECT name
        FROM elem STRAIGHT_JOIN elem_names IGNORE INDEX (PRIMARY)
          ON (elem.a = elem_names.symbol)\G

첫 번째 테이블에 대한 인덱스 전용 스캔은 10개의 행을 모두 가져온다(10개의 a값만 가져온다)
각 행에 대해 MySQL은 일치하는 행을 찾기 위해 풀 테이블 스캔(type: ALL)을 수행하여 두 번째 테이블을 조인한다
풀 조인은 선행 테이블의 각 행에 대해 조회가 발생하므로 쿼리가 수행할 수 있는 최악의 단일 작업이다
Extra: Using join buffer
- MySQL 8.0.18에 새롭게 도입된 해시 조인 알고리즘
- 해시 조인은 메모리 내 해시값 테이블을 만들고 반복되는 테이블 스캔을 수행하는 대신 이 해시 테이블을 사용해 행을 조회하는 것
- 해시 조인으로 인해 성능이 비약적으로 향상되었지만 풀 조인은 피하는게 최선이다
갑자기 카테시안 곱이랑 헷갈려서 적어두는 쓰레드
- From절에 2개 이상의 Table이 있을때 두 Table 사이에 유효 join 조건을 적지 않았을때 해당 테이블에 대한 모든 데이터를 전부 결합하여 Table에 존재하는 행 갯수를 곱한 만큼의 결과값이 반환되는 것
- 카테시안 곱은 join 쿼리 중에 WHERE 절에 기술하는 join 조건이 잘못 기술되었거나 아예 없을 경우 발생하는 현상

인덱싱: MySQL처럼 생각하는 방법

단순히 모든 열을 인덱싱해서는 탁월한 성능을 발휘할 수 없다
쿼리를 실행할 때 MySQL이 가장 적은 수의 행에 접근할 수 있도록 열을 인덱싱하는 것이 최선

1. 쿼리 알기

최적화하려는 쿼리의 기본 정보를 파악하자
테이블 생성 정보: 테이블 DDL이 출력
테이블 상태 정보: 테이블이 사용하는 Engine, Rows, Data_length, collation등이 출력
인덱스 정보: 인덱스 정보
다음 질문에 답을 찾으면서 설계하자
쿼리
- 쿼리는 몇 개의 행에 접근해야하나?
- 쿼리는 몇 개의 행을 반환해야하나?
- 어떤 열이 선택되나?
- GROUP BY, ORDER BY, LIMIT 절은 무엇인가?
- 서브쿼리가 있나? (있다면 각쿼리에 대해 위 질문을 반복)
테이블 접근
- 테이블 조건은?
- 어떤 인덱스를 사용할건지?
- 선택가능한 다른 인덱스는?
- 각 인덱스의 카디널리티는?
- 테이블의 크기는 얼마인가?

2. EXPLAIN으로 이해하기

EXPLAIN에서 보고한 현재 쿼리 실행 계획을 이해하자
항상 습관처럼 실행계획 보고를 확인하자
더 깊이 분석하고 싶다면 아래 방법을 고려해보자
- MySQL 8.0.16에서 EXPLAIN FORMAT=TREE로 변경가능하다, 트리 형태로 더욱 정확하고 서술적으로 쿼리 실행계획을 보고해준다
- 옵티마이저 트레이싱을 사용하여 비용, 고려사항 이유등 상세한 쿼리 실행 계획을 보고하도록 하자
- DBA에게 문의

3. 쿼리 최적화

직접 쿼리 최적화로 쿼리, 인덱스 또는 둘 모두를 변경하자
개발이나 준비 환경에 프로덕션을 대표하는 데이터가 있는지 확인하자(인덱스 선택하는 방식에 차이를 줄이기위해서)
준비 단계에서 변경 사항을 완전히 확인할 때까지 프로덕션 단계에서 인덱스를 수정하지 마세요

4. 배포와 검증

마지막으로 변경 사항을 배포하고 응답 시간이 개선되는지 검증한다
더 이상 쿼리를 최적화할 수 없다고 확신한다면 간접 쿼리 최적화를 고려해보자

좋은 인덱스였는데...

아무것도 변경되지 않는다면 좋은 인덱스는 영원히 좋은 인덱스로 남는다
하지만 현실적으로 불가능하다...
성능 저하의 일반적인 원인을 알아보자

쿼리변경

쿼리가 변경될 떄 맨 왼쪽 접두사에 대한 요구사항이 손실될 수 있다
사용할 수 있는 다른 인덱스가 없어서 풀 테이블 스캔이 실행될 수 도 있다
쿼리분석과 EXPLAIN 계획은 이러한 경우 신속하게 확인이 가능하게해준다
따라서 쿼리변경이 필요할 경우 새로 변경된 쿼리에 대해 인덱스가 적절한지 검증해라

과도하고 중복되며 사용되지 않음

필요이상으로 인덱스가 있는 경우이다
인덱스 크기 증가
- 인덱스가 많아지면 더 많은 메모리를 사용하므로 아이러니하게도 각 인덱스에 사용할 수 있는 메모리가 줄어든다
쓰기 성능 저하
- MySQL이 데이터를 작성할 때 모든 인덱스를 확인하고 갱신하고, 잠재적으로 재구성(내부 B-트리 구조)해야 하므로 쓰기 성능이 떨어진다
중복인덱스를 찾으려면 pt-duplicate-key-checker 사용해보는것을 추천
사용되지않는 인덱스를 찾으려면 아래 쿼리를 사용해봐라
- 성능 스키마가 활성화되어있어야한다
- MySQL이 시작된 이후 사용되지 않은 인덱스를 확인한다

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('performance_schema');

인덱스 삭제시에는 주의해야한다
- 바로 삭제말고 인비저블 인덱스를 사용하여 비활성화를 먼저해보자(MySQL 8.0이상 가능)
- 인덱스 삭제전에 인덱스가 사용되지 않거나 필요하지 않은지 확인할 수 있다
- 실수가 있을때 다시 인덱스를 보이게하는것은 거의 즉각적이다

최고의 선택도

카디널리티는 인덱스의 고윳값 수이다
값 a,a,b,b에 대한 인덱스의 경우 카디널리티는 2이다
인덱스 카디널리티를 보려면 SHOW INDEX를 사용해라

SHOW INDEX FROM {tableName}

선택도가 매우 낮은 인덱스는 각 고윳값이 많은 수의 행과 일치할 수 있으므로 거의 영향력이 없다
선택도가 매우 높은 세컨더리 인덱스가 많은 경우, 다른 기준이나 차원으로 전체 테이블을 보거나 검색하는 접근패턴을 나타낼 가능성이 높다(이 경우도 PK를 사용할 수 없는지 등의 고려가 필요하다)

MySQL이 다른 인덱스를 선택할 때

매우 드물지만 MySQL이 인덱스를 잘못 선택하기도 한다
가장 마지막에 의심해도 될정도로 드문 케이스
일반적인 이유는 많은 수의 행을 갱신할 때 갱신된 행 수가 인덱스 통계의 자동 갱신을 유발하기에는 조금 모자라기 때문
인덱스 통계는 MySQL이 어떤 인덱스를 선택할지에 영향을 미치는 많은 요소중 하나이므로 실제와 크게 다른 인덱스 통계로 인해 잘못된 인덱스를 선택할 수 있다
인덱스 통계 갱신 주기
- 테이블이 처음으로 열릴 때
- ANALYZE TABLE 명령이 실행되었을 때(테이블에 접근하는 모든 쿼리를 차단할 수 있는 플러시 잠금이 필요하기에 사용량이 많은 서버에서는 주의해야한다, 실행속도는 빠르다)
- 마지막 업데이트 이후 테이블의 1/16이 수정되었을 때
- innodb_stats_on_metadata가 활성화되고 다음 중 한 경우일 때
  - SHOW INDEX 명령어가 실행될때
  - SHOW TABLE STATUS 명령어가 실행될때
  - INFOMATION_SCHEMA.TABLES가 조회될때
  - INFOMATION_SCHEMA.STATISTICS가 조회될때

테이블 조인 알고리즘

기본 테이블 조인 알고리즘은 중첩 반복 조인이라고하며 foreach 반복문처럼 작동한다
가장 안쪽 테이블에 상당히 자주 접근하고 풀 조인을 사용하면 접근 속도가 매우 느려진다는 단점이 있다

이 예제에서 t3에는 t1의 일치하는 모든 행에 t2의 모든 일치하는 행을 곱한 횟수로 접근한다
즉 t1, t2에 모두 10개행이 일치하면 t3에는 100번 접근한다
이를 해결하기위해 블록 중첩 반복 조인 알고리즘이 나타남
t1과 t2에서 일치하는 행의 조인 열값은 조인 버퍼에 저장(join_buffer_size), 조인 버퍼가 가득차면 MySQL은 t3을 스캔하고 조인 버퍼의 조인 열갑과 일치하는 각 t3행을 조인
조인 버퍼에 여러번 접근하지만 메모리에 있어서 상당히 빠르다
MySQL 8.0.20부터는 해시 조인 알고리즘이 블록 중첩 반복 조인 알고리즘을 대체한다
해시 조인은 t과 같은 조인 테이블을 메모리 내에 해시 테이블로 생성
이 해시테이블을 사용하여 조인 테이블의 행을 조회하는데, 해시 테이블 조회는 상수 시간 연산이기에 매우 빠르다

요점 정리

인덱스는 MySQL 성능에 최고이자 최상의 영향력을 미친다
다른 옵션을 모두 사용할 때까지 MySQL 성능을 향상하기 위해 하드웨어를 확장하지마라
합리적인 구성으로 MySQL 성능을 향상하기 위해 튜닝할 필요는 없다
InnoDB 테이블은 PK로 구성된 B-트리 인덱스이다
MySQL은 인덱스 조회, 인덱스 스캔, 풀 테이블 스캔을 통해 테이블에 접근한다(이중 인덱스 조회가 짱이다)
인덱스를 사용하려면 쿼리는 인덱스의 맨 왼쪽 접두사를 사용해야한다
MySQL은 인덱스를 사용하여 WHERE와 일치하는 행을 찾고, GROUP BY에 대해 행을 그룹화하고 ORDER BY에 대해 행을 정렬하고, 커버링 인덱스를 사용하여 행 읽기를 피하고, 테이블을 조인한다(;;)
EXPLAIN은 MySQL이 쿼리를 실행하는 방법을 자세히 설명하는 쿼리 실행 계획을 출력한다
인덱싱에서는 쿼리 실행 계획을 이해하기 위해 MySQL과 같은 사고방식이 필요하다
좋은 인덱스는 다양한 이유로 효율성을 읽을 수 있다
MySQL은 중첩 반복 조인, 블록 중첩 반복 조인, 해시 조인의 세 가지 알고리즘을 사용하여 테이블을 조인한다

실습: 중복 인덱스를 찾아보자

pt-duplicate-key-checker 다운로드

728x90

저작자표시