Real My SQL이 읽기 싫을때

고승원·2023년 10월 5일
mysql
1

DB

목록 보기
6/6

이 글은 RealMySQL 스터디를 마치고 정리한 글이다.

내가 다시 보기 위한 것도 있지만, 제목처럼 Real My SQL이 너무 두꺼워 볼 엄두가 안나는 사람이 읽으면 좋겠다 라는 생각으로 정리해봤다.

책과 구성이나 순서가 다를 수 있다.

1. 쿼리 실행 구조

MySQL에서 쿼리가 어떻게 실행되는지 아는가? 쿼리 실행 구조부터 알아보자

1.1 쿼리 파서 (MySQL 엔진)

사용자로부터 요청이 들어오면 쿼리를 토큰으로 분리해 트리 구조로 만든다.

쿼리 문장의 문법 오류는 쿼리파서가 발견한다.

1.2 전처리기 (MySQL 엔진)

쿼리 파서가 생성한 트리를 각 토큰마다 테이블 또는 컬럼, 내장함수에 객체를 매핑한다.

이때 해당하는 테이블 컬럼 내장함수 등이 존재하거나, 권한이 있는지 확인한다.

1.3 옵티마이저 (MySQL 엔진)

사용자의 요청을 어떻게 실행해야 가장 빠를지 결정한다.

1.4 실행 엔진 (MySQL 엔진)

옵티마이저가 결정한 내용을 핸들러에게 전달한다.

(ex. 임시테이블 생성, 레코드 읽기, 결과를 다른 모듈로 전달 등등)

1.5 핸들러 (스토리지 엔진)

실행 엔진의 요청대로 디스크로부터 데이터를 I/O 한다.

1.6 스레드 풀

MySQL은 엔터프라이즈 에디션에서만 지원하지만, Percona Server에서도 지원을 한다.

MySQL에서 스레드 풀의 목적은 스레드 개수를 줄여서 CPU가 제한된 스레드 처리에만 집중해 리소스 소모를 줄이는 것이다. (실제로 큰 성능 이점은 없다)

Percona Server에서 스레드 풀은 CPU 개수만큼 스레드를 생성한다. 적절한 수의 스레드가 성능의 핵심 요소이다. 스레드 풀은 선순위 후순위 큐를 이용해 성능을 향상시킨다.(잠금 경합 회피등)

  • Percona Server
    MySQL을 기반으로한 고성능, 안정성 및 확장성을 강화한 오픈 소스 데이터베이스 서버.
    MySQL를 포크해 시작되었으며, 추가적인 기능과 성능 개선을 위해 개발되었다.

주요 특징

  1. 고성능: InnoDB 스토리지 엔진을 기반으로하며, 트랜잭션 처리 및 동시성을 개선하여 데이터베이스의 처리량과 응답 시간을 최적화할 수 있다.
  2. 안정성: 데이터의 일관성과 내구성을 보장하기 위해 트랜잭션 기능과 복구 기능을 강화했다. 또한, 장애 복구/감지 기능을 제공하여 시스템의 안정성을 향상시킨다.
  3. 확장성: 대규모 데이터베이스 환경에서 수평 및 수직 확장을 지원한다. 마스터-슬레이브 복제, 샤딩(Sharding), 클러스터링 등의 기능을 활용하여 데이터베이스의 확장성을 향상시킬 수 있다.
  4. 모니터링 및 분석: 성능 모니터링 및 진단 도구인 Percona Toolkit과 통합되어, 데이터베이스의 성능 및 상태를 실시간으로 모니터링하고 분석할 수 있다. 이를 통해 성능 튜닝과 문제 해결을 용이하게 할 수 있다.

Percona Server는 MySQL과 호환성이 높으며, 기존의 MySQL에서 쉽게 마이그레이션할 수 있다.

1.7 스레딩 구조

MySQL은 스레드 기반으로 작동하며, 포그라운드와 백그라운드 스레드로 구분된다.

image

앞서 설명한 것 처럼 엔터프라이즈와 Percona Server는 스레드풀을 사용할 수 있다. 스레드 풀과 전통적인 스레드 모델의 가장 큰 차이점은 포그라운드 스레드와 커넥션 관계이다. 전통적인 스레드 모델에선 커넥션별로 포그라운드 스레드가 하나씩 생성되고 할당된다. 하지만 스레드 풀에선 하나의 스레드가 여러개의 커넥션을 전담한다.

포그라운드 스레드(클라이언트 스레드)

서버에 접속된 클라이언트 수만큼 존재하며 클라이언트가 요청하는 쿼리 문장을 처리한다.

커넥션이 종료되면 스레드는 스레드 캐시로 되돌아간다. 이때 스레드 캐시에 일정 개수 이상이 대기하고 있으면 스레드를 종료시킨다.

포그라운드 스레드는 데이터를 데이터 버퍼나 캐시로부터 가져오며, 존재하지 않는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어온다.

MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리하지만 InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고 나머지는 백그라운드 스레드가 처리한다.

백그라운드 스레드

InnoDB에서 백그라운드 스레드는 다음과 같은 작업을 수행한다.

  • Insert Buffer 병합
  • Log를 디스크로 기록
  • InnoDB 버퍼 풀의 데이터를 디스크에 기록
  • 데이터를 버퍼로 읽기
  • 잠금이나 데드락을 모니터링

MySQL 5.5 버전부터 데이터 읽기/쓰기 스레드를 2개 이상 지정할 수 있으며, 시스템 변수로 개수를 설정한다.

InnoDB에서도 읽기 작업은 주로 클라이언트 스레드에서 처리되지만, 쓰기 작업은 많은 작업을 백그라운드로 처리하기 때문에 일반적인 내장 디스크인 경우 2~4, DAS나 SAN과 같은 스토리지에선 충분히 설정하는 것이 좋다.

데이터 쓰기 작업은 지연(버퍼링)되어 처리할 수 있지만, 읽기 작업은 절대 지연될 수 없다.

InnoDB는 쓰기 작업을 버퍼링해서 일괄 처리하고, MyISAM은 사용자 스레드가 쓰기 작업까지 한번에 처리한다.

2. MySQL 서버 구조

쿼리가 실행되는 순서는 얼추 알게 되었으니 MySQL 서버가 어떻게 생겼고, 어떤 역할을 하는지 알아보자.

image

MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 나뉜다.

2.1 MySQL 엔진

클라이언트의 요청을 처리하는 커넥션 핸들러와 SQL 파서

최적의 쿼리 실행을 위한 옵티마이저가 주를 이룬다. (위에서 언급한 핸들러와 전처리기도 여기에 포함된다.)

2.2 스토리지 엔진

요청된 SQL을 분석, 최적화 하고 실제 데이터를 디스크 스토리지에 저장하거나 읽어오는 역할을 한다.

스토리지 엔진은 키 캐시, 버퍼 풀과 같은 기능을 내장하고 있다.

스토리지 엔진은 여러개가 있어서 ENGINE = 엔진 으로 지정할 수 있다.

여러개의 스토리지 엔진이 있지만, 가장 많이 사용되는 두가지만 알아보자.

2.2.1 InnoDB

InnoDB는 MySQL엔진중 거의 유일하게 레코드 기반의 잠금을 제공한다. 그렇기 때문에 동시성 처리가 가능하고, 안정성과 성능이 뛰어나다.

아키텍처

image

PK에 의한 클러스터링

InnoDB의 모든 테이블은 PK를 기준으로 클러스팅 되어 저장된다.

즉 PK순서대로 디스크에 저장되고, 다른 인덱스들은 PK의 값을 논리적인 주소로 사용한다.

PK가 클러스터링 인덱스이기 때문에 레인지 스캔은 상당히 빠르게 처리된다. 따라서 실행계획에 PK가 선택될 확률이 높다.

MyISAM은 클러스터링 키를 지원하지 않기 때문에 다른 인덱스는 구조적인 차이가 없다.

FK 지원

InnoDB에서 FK는 부모, 자식 테이블 모두 해당 칼럼에 인덱스를 생성하고, 변경시에 부모 자식 모두 체크한다. 이와 같은 특성으로 인해 여러 테이블로 전파되어 데드락을 야기한다.

이와 같은 특성으로 인해 수동으로 데이터 적재, 스키마 변경 작업등을 진행할때 실패할 수 있다. 이런 경우 foreign_key_checks 변수를 OFF로 변경하고 작업할 수 있다.(cascade무시)속성을 다시 ON 하기전에 부모, 자식 간의 일관성을 맞춰줘야 한다.

자동 데드락 감지

InnoDB는 내부적으로 Wait-for List를 관리한다. 데드락 감지 스레드가 주기적으로 Wait-for List를 검사해 교착상태의 트랜잭션중 하나를 강제 종료한다.

강제 종료의 기준은 트랜잭션의 언두 로그 양이다. (언두 로그의 양은 롤백 부하와 반비례 한다.)

데드락 감지 스레드는 잠금 상태가 변경되지 않도록 잠금 테이블에 잠금을 거는데, 동시 처리가 많아지는 경우 데드락 감지가 느려져 서비스에 악영향을 미치게 된다. innodb_lock_wait_timeout 를 적절히 사용해보자.

  • InnoDB 스토리지 엔진의 상위 레이어인 MySQL 엔진에서 관리되는 테이블 lock은 스토리지 엔진에서 볼 수 없어서 데드락 감지가 불확실할 수 있다. innodb_table_locks 시스템 변수를 활용하면 테이블 레벨의 lock을 감지할 수 있다.

버퍼 풀

InnoDB의 가장 핵심이다. 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐싱하는 공간이다. 쓰기작업을 지연해 일괄처리하는 버퍼 역할도 한다.

버퍼 풀은 128MB 청크 단위로 쪼개서 관리 되기 때문에 늘리거나 줄일때의 단위는 128MB로 사용된다. innodb_buffer_pool_instance 를 이용해 버퍼 풀을 여러개로 분리해서 관리하는데 이를 인스턴스라고 부른다.

MySQL 5.7부터 버퍼 풀의 크기를 동적으로 조절할 수 있어, 작은 값으로 시작해 조금씩 증가시키는 방법이 최적이다. innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있다. 버퍼 풀의 크기를 줄이는 작업은 시스템 영향도가 매우 크니 주의하자.

버퍼풀 구조

image

버퍼 풀을 페이지 크기로 쪼개어 저장하는데, 페이지를 관리하기 위해 크게 LRU, Flush, Free라는 자료구조를 관리한다.

  • Free : 실제 사용자 데이터로 채워지지 않은 비어있는 페이지 목록이다.
  • Flush : 디스크로 동기화되지 않은 데이터를 가진 페이지(더티 페이지)

MRU와 LRU가 섞인 모습이다. 데이터 페이지를 찾는 과정은 다음과 같다.
1. 필요한 레코드가 있는 페이지를 찾는다.
2. 디스크에서 페이지를 버퍼 풀에 적재하고 LRU 헤더에 추가한다.
3. LRU의 헤더에 적재된 페이지가 읽히면 MRU 헤더 부분으로 이동한다.
4. 버퍼풀에 상주하는 페이지는 최근에 접근할수록 낮은 Age를 가지며 나이가 많아지면 버퍼풀에서 제거된다.
5. 데이터가 자주 접근됐다면, 해당 페이지를 어댑티브 해시 인덱스에 추가한다.

리두 로그

리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다. 따라서 전체 리두 로그 파일에서 재사용 가능한 공간과 불가능한 공간을 구분해서 관리해야 하는데, 불가능한 공간을 활성 리두 로그라고 한다.

기록될 때마다 Log Sequence Number를 증가시킨다. InnoDB는 주기적으로 체크포인트 이벤트를 발생시켜 리두 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화 한다. 이때 가장 최근 체크포인트의 Log Sequence Number이 시작점이 된다.

리두 로그의 크기가 크다고 무조건 좋은 것이 아니다. 한 번에 많은 더티페이지를 동기화해 서버에 부하를 주기 때문이다.

버퍼 풀 플러시

MySQL 8.0부턴 더티 페이지 동기화 작업시 디스크 쓰기 폭증 현상은 발생하지 않는다. InnoDB는 Flush List와 LRU List를 백그라운드로 진행한다.

  • Flush List
    리두 로그 공간 활용을 위해 주기적으로 오래된 리드 로그 엔트리가 사용하는 공간을 비운다. 이때 버퍼풀의 더티 페이지가 먼저 동기화 해야된다. 이를 위해 Flush List 함수를 호출해서 오래된 순으로 동기화 하는 작업을 수행한다.
    이때 innodb_max_dirty_pages_pct_lwv 을 설정해 버퍼 풀에 더티 페이지 비율을 정할 수 있다.
  • LRU List
    사용 빈도가 낮은 데이터 페이지들을 제거하기 위해 사용된다. 더티 페이지를 동기화 후 Free 리스트로 페이지들을 옮긴다.

버퍼 풀 상태 백업 및 복구

서버를 다시 시작하면 버퍼 풀이 비어있어 쿼리 처리 성능이 낮다. 버퍼 풀에 데이터가 적재 되어있는 상태를 워밍업이라고 표현하는데, 재시작 하는 경우 강제 워밍업을 했지만, MySQL 5.6부턴 버퍼 풀 덤프 및 적재 기능이 도입되어 백업할 수 있다.

Double Write Buffer

리두 로그는 공간 낭비를 막기 위해 페이지의 변경된 내용만 기록한다. 이로 인해 플러시할 때 일부만 기록되면 문제가 발생한다. 이를 막기위해 Double Write 기법을 사용한다.

데이터를 디스크에 기록하기 전에 먼저 이중 버퍼링하는 메커니즘이다. 버퍼 풀의 데이터를 Double Write 버퍼로 복사한 다음 디스크의 Double Write 영역에 기록한다.

언두 로그

트랜잭션과 격리 수준을 보장하기 위해 DML 변경 이전의 데이터를 별도로 백업한다.

MySQL 5.5 이전 버전에선 한 번 증가한 언두 로그 공간은 줄어들지 않았다. 하지만 MySQL 8.0에선 언두 로그를 돌아가면서 사용해 디스크 공간을 줄이기도 하고, 디스크 공간을 자동으로 줄여주기도 한다.

언두 테이블스페이스 관리

언두 로그가 저장되는 공간을 언두 테이블스페이스라고 한다. 기존에는 시스템 테이블스페이스에 저장되기도 했고, 별도의 언두 로그 파일을 사용하기도 했는데 MySQL 8.0부터 외부의 별도 로그 파일에 기록되도록 개선되었다.

image

언두 테이블스페이스는 1~128개의 롤백 세그먼트를 가지며 롤백 세그먼트는 1개 이상의 언두 슬롯을 가진다.

MySQL 8.0부터 언두 테이블 스페이스는 Create, Drop, Alter를 통해 조작이 가능하다.

체인지 버퍼

InnoDB는 변경해야 할 인덱스 페이지를 디스크로부터 읽어와서 업데이트해야 한다면 즉시 실행하지 않고 체인지 버퍼에 저장 해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다. (유니크 인덱스는 체인지 버퍼를 사용할 수 없다)

체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이를 체인지 버퍼 머지 스레드 라고 한다. MySQL 5.5 까지는 INSERT, MySOL 8.0 부터 INSERT, DELETE, UPDATE 까지 버퍼링이 될 수 있게 개선됐다.

MySQL 5.5부터 innodb_change_buffering이라는 시스템 변수가 도입되어 작업의 종류별로 체인지 버퍼를 활성화할 수 있으며, 체인지 버퍼가 비효율적일 때는 체인지 버퍼를 사용하지 않게 설정할 수 있게 개선됐다.

리두 로그 및 로그 버퍼

리두 로그는 여러가지 문제로 인해 MySQL서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.
변경된 데이터를 데이터 파일에 기록 하려면 상대적으로 큰 비용이 필요하다. 이로 인한 성능 저하를 막기 위해 쓰기 비용이 낮은 자료 구조를 가진 리두 로그를 가지고 있으며, 비정상 종료가 발생하면 리두 로그의 내용을 이용해 데이터 파일을 다시 서버가 종료되기 직전의 상태로 복구한다. ACID도 중요하지만 성능도 중요하기 때문에 데이터 파일뿐만 아니라 리두 로그를 버퍼링할 수 있는 InnoDB 버퍼 풀이나 리두 로그를 버퍼링할 수 있는 로그 버퍼와 같은 자료 구조도 가지고 있다.

어댑티드 해시 인덱스

사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스. B-Tree 검색 시간을 줄여주기 위해 도입된 기능

자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다. CPU는 적은 일을 하지만 쿼리의 성능은 빨라진다.

innodb_adaptive_hash_index 변수를 이용해서 어댑티브 해시 인덱스 기능을 활성화/비활성화할 수 있다.

키값은 B-Tree 인덱스의 고유번호와 실제 키 값을 조합하고 밸류는 버퍼 풀에 로딩된 데이터 페이지의 주소로 관리된다. B-Tree의 고유번호를 담는 이유는 어댑티브 해시 인덱스는 하나의 해시 인덱스에서 관리하기 때문이다.(8.0부턴 파티션 기능 제공)

도움이 되지 않는 경우

  • 디스크 읽기가 많은 경우
  • 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
  • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

도움 되는 경우

  • 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
  • 동등 조건 검색(동등비교와 IN 연산자)이 많은 경우
  • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

2.2.2 MyISAM

InnoDB보다 훨씬 간단한 구조로 이루어져 있으며 MySQL 8.0 버전부터 사용할 일이 거의 없다.

image

키 캐시

InnoDB의 버퍼 풀과 비슷한 역할이다.

이름 그대로 인덱스를 대상으로 작동하며 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼 역할을 한다.

운영체제의 캐시 및 버퍼

MyISAM 은 데이터 캐시나 버퍼기능이 없다. 물론 OS의 디스크 I/O에 대한 캐시나 버퍼링 메커니즘이 있기 때문에 매번 디스크 I/O가 일어나진 않는다.

위와 같은 이유로 OS의 메모리 공간을 DB가 모두 사용하지 않도록 설정해야 한다. 보통 키 캐시는 물리 메모리의 40%를 넘지 않도록 설정하는 것이 좋다.

데이터 파일과 PK 구조

InnoDB 는 클러스터링 되어 저장되는 반면 MyISAM 은 힙공간처럼 사용된다.

PK값과 무관하게 INSERT 순서대로 데이터 파일에 저장된다. 그리고 각 레코드는 모두 ROWID 라는 물리적 주소값을 갖는데 PK와 세컨더리 인덱스는 ROWID 를 포인터로 가진다.

ROWID

  • 고정 길이
    자주 사용 되지 않지만, MAX_ROWS 옵션을 명시하면 4바이트 정수를 사용한다.
  • 가변 길이
    myisam_data_pointer_size 변수의 바이트 수만큼 공간을 사용할 수 있다. default 값은 7로 2~7 바이트의 ROWID를 할당한다.
    첫 값은 ROWID 의 길이, 그 뒤는 실제 ROWID 를 갖는다.

3. 로그

3.1 에러 로그 파일

에러 로그 파일은 MySQL 설정 파일에서 log_error 파라미터로 정의된 경로에 생성된다.
MySQL 설정 파일에 별도로 정의되지 않은 경우에는 데이터 디렉터리에 생성된다.

주요 메세지

  1. 시작하는 과정과 관련된 정보 및 에러 메시지
    설정 파일을 변경하거나 DB가 비정상적으로 종료된 이후 재시작 하는 경우 에러 로그 파일을 통해 설정된 변수의 이름이나 값이 의도한 대로 적용됐는지 확인해야 한다. 특정 변수가 무시(ignore)된 경우에는 파라미터가 적용되지 않음을 의미한다.
  2. 비정상적으로 종료된 경우 나타나는 InnoDB 트랜잭션 복구 메세지
    MySQL 서버를 시작할 때 트랜잭션을 재처리 하며 간단한 메세지를 출력하는데, 문제가 생겨 복구하지 못하는 경우 메세지를 출력하는데, 해결하기 어려운 문제인 경우이기 때문에 innodb_force_recovery 변수를 0부터 6까지 하나씩 올려가며 재시작해보자.
  3. 쿼리 도중 발생한 에러 메세지
    쿼리 도중 발생한 에러는 에러 로그에 기록되기 때문에 자주 로그 파일을 검토하며 DB의 숨겨진 문제점을 찾아보자.
  4. 비정상적으로 종료된 커넥션 메세지
    정상적인 접속 종료를 못하고 프로그램이 종료된 경우 기록된다. 물론 중간에 네트워크 문제가 있어 접속이 끊긴 경우에도 기록된다. 이런 메세지가 자주 기록된다면 어플리케이션 커넥션 종료 로직을 검토해보자.
  5. InnoDB의 모니터링 또는 상태 조회 명령 메세지
    테이블, 락, InnoDB 상태 모니터링은 상대적으로 큰 에러 로그를 남기기 때문에, 시스템 공간을 다 사용할 수 있기 때문에 모니터링이 끝나면 다시 비활성화 해야한다.
  6. 종료 메시지
    MySQL 서버가 종료될때 남겨지는 메시지를 뜻한다. 아무런 메시지 없이 스택 트레이스가 출력되는 경우는 MySQL 서버가 세그먼트 폴트로 비정상적으로 종료된 것으로 판단할 수 있다.

3.2 제너럴 쿼리 로그 파일

MySQL 서버에서 실행되는 쿼리로 어떤게 있는지 검토할때 사용한다.

쿼리 로그를 활성화 하면 시간 단위로 실행한 쿼리의 내용이 모두 기록된다. 슬로우 쿼리로그와는 다르게 쿼리 요청을 받자마자 바로 기록한다.

3.3 슬로우 쿼리 로그 파일

long_query_time 에 설정한 시간 이상으로 소요된 쿼리를 기록한다. 그렇기 때문에 반드시 쿼리가 정상적으로 실행이 완료되어야 기록된다.

log_output 옵션을 이용해 로그를 테이블 또는 파일로 저장할 수 있다.

# Time: 2020-07-19715:44:22.178484+09:00
# User@Host: root [root] @ localhost Id:
14
# Query_time: 1.180245 Lock_time: 0.002658 Rows_sent: 1 Rows_examined: 2844047
use employees;
SET timestamp=1595141060;
select emp_no, max(salary) from salaries;

슬로우 쿼리 로그의 일부이다.

여기서 Lock_time은 InnoDB를 제외한 MySQL 엔진에서 사용한 테이블 락에 대한 시간만 표현한다. Rows_sent는 전달한 처리결과 건수, Rows_examined는 접근한 레코드수를 뜻한다.

슬로우 쿼리 통계

Percona에서 개발한 pt-query-digest 스크립트를 사용해 빈도, 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.

분석이 완료되면 실행, 잠금등의 시간에 대해 평균/최소/최대 값을 표시한다.

실행 빈도 및 누적 실행 시간순 랭킹

pt-query-digest 명령 실행 시 —order-by 옵션으로 정렬 순서를 변경할 수 있다.

쿼리별 실행 횟수 및 누적 실행 시간 상세 정보

개별 쿼리 정보를 확인하면, 쿼리 실행 횟수, 응답 시간등 상세한 내용을 확인할 수 있다.

4. 인덱스

인덱스는 SortedList와 같다. 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리지만, 이미 정렬되어 있어서 원하는 값을 빨리 찾아올 수 있다.

따라서 인덱스는 데이터의 INSERT, UPDATE, DELETE 성능을 희생하고 SELECT 속도를 높이는 기능이다.

인덱스는 프라이머리 인덱스와 세컨더리 인덱스로 구분할 수 있는데, 프라이머리 인덱스는 레코드를 대표하는 칼럼으로 만들어진 인덱스를 뜻하며 세컨더리 인덱스는 그외 나머지를 뜻한다.

인덱스는 Hash와 B-Tree로 이루어져 있으며, 대부분 B-Tree이다.

4.1 B-Tree

Balanced-Tree를 뜻하며 원래 값을 변형시키지 않고, 항상 정렬된 상태로 관리한다.

구조 및 특성

최상위에 루트 노드, 그 하위에 자식 노드가 붙어있는 형태다. 최하위에 있는 노드를 리프 노드라고 하며, 그 사이를 브랜치 노드라고 한다. DB에서 인덱스와 실제 데이터는 따로 관리되는데, 리프 노드는 실제 데이터의 주솟값을 갖고 있다. 따라서, 인덱스 키만 가지고 나머지 컬럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 하는데 인덱스 리프노드를 통해 정보를 얻는다.

image

데이터 파일은 인덱스 순서와 상관없이 뒤죽박죽이다.

MyISAM은 인덱스에 레코드 주소를 갖는 반면 InnoDB는 클러스터링 구조로 되어있어서 프라이머리 키를 주소처럼 사용한다. 즉 InnoDB에서 세컨더리 인덱스를 사용해서 검색을 할 때 세컨더리 인덱스의 리프 노드에 있는 값을 프라이머리 인덱스에서 다시 검색해야 한다.

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

추가

B-Tree에 저장될 때 리프노드에 저장되는데, 리프노트가 꽉찬 경우 브랜치 노드까지 작업 범위가 넓어진다. 따라서 B-Tree 쓰기 작업은 비용이 크다. 보통 레코드 추가 비용이 1이면 인덱스 추가 비용은 1.5정도로 산정하는데, 인덱스가 3개인 경우 레코드 하나 삽입할 때 5.5의 비용이 드는 셈이다.

다른 스토리지 엔진과 다르게 InnoDB는 지연 처리하기도 하는데, 프라이머리 키나 유니크 인덱스인 경우 중복 체크 때문에 바로 적용한다.

삭제

B-Tree의 리프 노드를 찾아서 삭제 마크를 하면 끝이다. 마킹된 공간은 재사용된다. 이 방법도 지연처리 하기도 한다.

업데이트

인덱스 업데이트는 기존의 인덱스 값을 삭제 한 후 새로운 키 값을 추가한다.

검색

인덱스 트리 탐색은 SELECT 뿐만 아니라 UPDATE, DELETE 작업을 처리하기 위해 사용한다. 보통 인덱스의 100%를 비교하기도 하지만, 인덱스의 일부(앞부분)만 일치하는 경우에도 사용한다.

인덱스 검색을 할 때 인덱스 키 값에 변형이 가해진 경우 더이상 B-Tree에 존재하지 않기 때문에 인덱스를 통한 빠른 검색이 불가능 하다.

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

인덱스 키 값의 크기

데이터를 저장하는 가장 기본 단위인 Page의 크기와 인덱스 키 값의 크기는 중요하다.

B-Tree의 자식 노드의 수는 Page / (인덱스 키 값 + 자식 노드 주소) 이기 때문에 인덱스 키 값이 작을수록 자식 노드가 많고, 깊이가 얕아지게 된다.

B-Tree의 깊이

한 페이지의 크기가 크면 디스크 I/O 횟수가 줄어들어 효율이 늘어난다.

즉 B-Tree의 깊이가 깊어질수록 디스크 읽기 연산이 늘어난다.

카디널리티

인덱스는 카디널리티가 높을수록 검색 대상이 줄어들기 때문에 그만큼 속도가 빨라진다.

읽어야 하는 레코드 건수

100만건의 레코드가 있는 테이블에서 50만건을 읽어야 하는 쿼리가 있으면, 인덱스보다 풀테이블 스캔을 하는 것이 유리하다. 이는 Random I/O와 Sequencial I/O라서 속도 차이가 있는데, 보통은 20~ 25% 이상의 레코드를 읽을 경우 풀테이블 스캔이 더 유리해서 사용하도록 되어있다.

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

어떤 상황에서 어떻게 인덱스를 스캔하는지 알아보자.

인덱스 레인지 스캔

뒤에서 설명할 두 가지 방법보다 빠른 방법으로, 인덱스를 통해 검색해야 할 인덱스 범위가 결정되었을때 사용한다.

루트 노드부터 시작해 리프 노드를 찾아 순서대로 탐색한다. 이때 실제 인덱스만 반환하는 경우(커버링 인덱스)엔, 인덱스 스캔을 하고 끝이지만, 그렇지 않은 경우엔 데이터 파일을 읽는다. 1건마다 Random I/O 가 일어난다. 따라서 20~25%를 넘어가면 풀테이블 스캔이 더 유리하다.

인

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식.

대표적으로 쿼리 조건절에 사용된 컬럼이 인덱스의 첫 컬럼이 아닌 경우 사용한다. 리프노드의 첫 번째 페이지부터 링크드리스트로 이어져있어서 쭉 읽게 된다. 이는 레인지 스캔보다 느리지만, 풀 테이블 스캔보다 빠르다.

인덱스는 테이블보다 훨씬 작기 때문에 적은 디스크 I/O로 쿼리를 처리할 수 있다.

루스 인덱스 스캔

인덱스 레인지 스캔과 비슷하지만 리프노드의 페이지를 확인하면서, 필요하지 않은 인덱스 키 값을 스킵하고 다음으로 넘어가는 방식이다. 일반적으로 GROUP BY나 MAX, MIN 함수에서 사용된다.

인덱스 스킵 스캔

(gender, birthday) 복합 인덱스에서 WHERE절에 gender가 생략되고, birthday로만 검색을 하게 되면 인덱스를 사용하지 못한다. 이때 첫번째 컬럼의 값을 무시하고 birthday 값으로만 인덱스 검색이 가능하게 해주는 기능이다.

MySQL 8.0 부터 도입된 기능으로 실행 계획을 살펴보면 range, skip scan이라고 나온다.

image

실제 쿼리는 gender가 가지는 모든 도메인(예를 들어 M, F) + birthday에 대한 검색을 실행하는 것과 비슷하게 작동한다. 따라서 선행 컬럼의 카디널리티가 낮아야 하고, 커버링 인덱스인 경우에만 사용이 가능하다.

복합 인덱스

복합 인덱스의 경우 인덱스 내부의 컬럼 순서에 의존하여 정렬된다. 따라서 인덱스 내부의 컬럼 순서는 매우 중요하다.

5. 트랜잭션 락 MVCC

5.1 트랜잭션과 락

트랜잭션은 하나의 작업 셋이 100% 적용되거나 ROLLBACK되어야 함을 보장해주는 것이다. 트랜잭션을 지원하는 InnoDB와 지원하지 않는 MyISAM의 처리방식을 살펴보자

트랜잭션 범위

트랜잭션의 범위가 큰 경우 DBMS에 부하를 주거나 위험한 상태에 빠지게 할 수 있기 때문에 범위를 최소의 코드에만 적용하는 것이 좋다.

  • DB에 영향을 주지 않는 경우
  • 원격 서버와 통신하는 경우
  • 작업 성격이 다른 경우

5.1.1 MySQL엔진 락

스토리지 엔진을 제외한 나머지 영역의 락을 뜻한다. MySQL엔진 락은 스토리지엔진에 영향을 끼치지만, 스토리지 엔진의 락은 다른 스토리지 엔진에 영향을 끼치지 않는다.

테이블 데이터 동기화를 위한 락과 테이블 구조를 잠구는 메타데이터 락, 네임드 락이라는 기능을 제공한다.

글로벌 락

FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 가장 범위가 크다.

한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 DDL, DML 문장을 대기상태로 만든다.

여러 DB에 존재하는 MyISAM이나 MEMORY 테이블에 대해 mysqldump로 백업받을때 사용한다.

백업 락

InnoDB 엔진은 트랜잭션을 지원하기 때문에 가벼운 글로벌 락인 백업 락이 도입됐다. 글로벌 락과 비슷하지만, 일반적인 테이블의 데이터 변경이 가능하다.

백업을 실행하는 도중에 스키마 변경이 실행되면 백업이 실패하기 때문에 락을 사용한다.

  • FLUSH TABLES WITH READ LOCK 명령을 사용하면, 먼저 실행된 SQL과 트랜잭션이 완료된 후 잠금을 한다. 이때 실행된 락은 오랜 시간동안 지속되기 때문에 MySQL의 모든 테이블에 큰 영향을 미친다.

테이블 락

테이블 단위로 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

명시적은 LOCK TABLES 테이블이름 [ READ / WRITE ] 명령으로 특정 테이블의 락을 획득할 수 있다.

테이블 락은 MyISAM뿐 아니라 InnoDB 엔진을 사용하는 테이블도 동일하게 설정할 수 있다. 명시적으로 획득한 락은 UNLOCK TABLES 명령으로 잠금을 해제 할 수 있다. 명시적인 테이블 락은 글로벌 락과 동일하게 작업에 상당한 영향을 미치기 때문에 사용할 필요가 거의 없다.

묵시적은 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다. 즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료되면 자동으로 해제된다.

InnoDB에선 레코드 기반 잠금을 지원하기 때문에 데이터 변경에는 사용되지 않고, DDL에 사용된다.

네임드 락

테이블, 레코드, DB객체가 아닌 단순히 사용자가 지정한 문자열에 대해 획득 및 반납하는 락이다. GET_LOCK() 함수를 사용해 임의의 문자열에 대해 잠금을 설정한다.

복잡한 요건으로 레코드를 변경하는 트랜잭션에 유용하게 사용할 수 있다. 배치 프로그램처럼 한번에 많은 레코드를 변경하는 쿼리는 데드락의 원이이 되곤 하는데, 여러 방법이 있지만, 동일 데이터를 변경하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 간단히 해결이 가능하다.

MySQL 8.0 부터 네임드 락을 중첩해서 사용할 수 있고, 한번에 해제할 수 있다.

메타데이터 락

DB 객체(테이블, 뷰 등)의 이름이나 구조를 변경할 때 획득한다. 메타데이터 락은 명시적으로 획득/해제 할 수 없고, RENAME TABLE before TO after 처럼 이름을 변경할 때 사용한다. 이런경우 before, after 모두 잠긴다.

때로는 메타데이터 락과 InnoDB 트랜잭션을 동시에 사용해야 하는 경우도 있다. 예를들어 로그 테이블을 변경하려 할때 DDL은 싱글 스레드로 작동하기 때문에 상당히 많은 시간이 걸린다. 따라서 새로운 구조의 테이블을 생성하고 id 범위별로 나눠서 여러 개의 스레드로 빠르게 복사한다. 이렇게 하고 남은 데이터는 테이블 잠금을 통해 복사한다.

5.1.2 InnoDB 스토리지 엔진 락

InnoDB는 레코드 기반의 락 방식으로 훨씬 뛰어난 동시성 처리를 제공할 수 있다.

이원화된 락 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 락에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭다.
InnoDB의 트랜잭션과 락, 그리고 대기 중인 트랜잭션의 목록을 조회할 수 있는데, information_schema에 존재하는 INNODETRX, INNODB_LOCKS, INNODB LOCK_WAITS라는 테이블을 조인해서 조회하면 락을 소유하고 있는 트랜잭션, 점유시간을 알 수 있다.

InnoDB 스토리지 락

레코드 락이 페이지 또는 테이블 락으로 레벨업 되는 경우는 없다. 일단 DBMS와 다르게 InnoDB는 레코드 간격을 잠그는 갭 락이라는 것이 존재한다.

image

레코드 락

레코드 자체만 잠구는 것을 뜻한다. 중요한 점은 레코드 자체가 아니라 인덱스의 레코드를 잠근다. 인덱스가 하나도 없는 경우 내부적으로 생성된 클러스터 인덱스를 잠군다.

보조 인덱스를 이용한 변경 작업은 대부분 넥스트 키 락 또는 갭 락을 사용하지만 PK, UK에 의한 변경 작업에서는 갭에 대해 잠그지 않고 레코드 자체에 락을 건다.

갭 락

레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이에 새로운 레코드가 생기는 것을 제어하는 역할이다.

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 락이다.

InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.

넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

자동 증가 락

AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우 내부적으로 자동 증가 락이라는 테이블 수준의 락을 사용한다.

트랜잭션과 관계없이 INSERT나 REPLACE 문장에서 AUTO_INCREMENT값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다. 테이블에 단 하나만 존재하기 때문에 두개 이상의 INSERT 쿼리가 동시에 실행되면 하나의 쿼리는 대기하게 된다.

명시적으로 획득/해제 할 수 없으며, 아주 짧은 시간 사용되기 때문에 문제가 되지 않는다.

MySQL 5.1 부터 자동 증가 락 방법을 변경할 수 있다.

  • innodb_autoinc_lock_mode=0
    MySQL 5.0과 동일한 잠금 방식으로 모든 INSERT 문장은 자동 증가 락을 사용한다.
  • innodb_autoinc_lock_mode=1
    여러건을 INSERT 하는 경우, 서버가 INSERT 되는 레코드의 건수를 정확히 예측할 수 있을 때 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치를 이용해 처리한다.
    건수를 예측할 수 없는 경우 0번 방식을 사용한다. 대량의 INSERT가 실행될 때 여러개의 자동 증가 값을 한 번에 받아서 INSERT 하는 방법이다. 자동 증가 값이 남는 경우 폐기된다.
  • innodb_autoinc_lock_mode=2
    절대 자동 증가 락을 사용하지 않고, 경량화된 래치 방법을 사용한다. 그렇기 때문에 연속된 자동 증가 값을 보장하지 않는다. STATEMENT 포맷의 바이너리 로그를 사용하는 복제에서는 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수도 있다.

인덱스와 락

변경해야 할 레코드를 찾기 위해 검색한 인덱스를 모두 락을 걸어야 한다.

image

다음과 같이 클러스터드 인덱스와 first_name에 대한 인덱스만 있는 테이블이 있다.

first_name이 Gerogi 인 사원이 253명이 있고 변경하려는 레코드는 1개이다. 이때 first_name에 대한 조건은 253개의 레코드, last_name은 인덱스가 없으므로 253개의 레코드가 모두 잠긴다.

image

만약 테이블에 인덱스가 하나도 없다면, 테이블을 풀 스캔하면서 테이블에 있는 모든 레코드를 잠그게 된다.

레코드 락 확인/해제

레코드 락은 테이블 락보다 조금 더 복잡하다. 레코드 락은 각각의 레코드에 걸리기 때문에 문제를 발견하기 어렵다.

예전 버전의 MySQL에선 레코드 락에 대한 메타정보를 제공하지 않았지만, 5.1 버전부터 잠금, 잠금 대기에 대한 조회가 가능하다. 잠금을 해제하려면 KILL 명령어를 사용해 MySQL 서버의 프로세스를 강제종료하면 된다.

image

다음과 같은 시나리오에서 락에 대한 정보를 조회해보자.

performance_schema의 테이블을 이용해 잠금과 잠금 대기 순서를 확인하는 방법은 다음과 같다.

image

17번 스레드는 UPDATE 명령이 실행되고 커밋되지 않은 상태, 18, 19는 기다리는 잠금대기 상태이다.

SELECT * FROM performance_schema.data_locks\G 명령을 통해 상세 정보를 확인할 수 있다.

image

18, 19 스레드는 17과 18번 스레드를 기다리고 있다.

5.3 MVCC(Multi Version Concurrency Controll)

MVCC의 뜻은 하나의 레코드를 여러 버전으로 관리한다는 뜻이며, 가장 큰 목적은 lock 없이 읽관된 읽기이다. InnoDB는 언두 로그를 통해 이 기능을 제공한다.

다음은 m_area가 서울에서 경기로 바뀌는 경우에 언두 로그의 변화를 나타낸 사진이다.

image

커밋 여부와 상관없이 UPDATE 쿼리와 함께 버퍼풀은 변경되고, 기존 값은 언두 로그에 적재된다. 디스크는 보통 버퍼풀과 동일하지만 아닐 수 있다.

이때 트랜잭션의 격리 수준에 따라서 어느 영역의 데이터를 반환할지 결정된다.

커밋이 된다면 버퍼풀은 즉시 디스크에 반영되고, 트랜잭션이 사라지면 해당하는 언두 로그는 삭제한다.

잠금 없는 일관된 읽기

격리 수준이 SERIALIZABLE이 아닌 경우 SELECT 작업은 잠금을 대기하지 않고 바로 실행된다. 위의 사진과 같이 커밋되지 않은 데이터여도 언두 로그를 통해 잠금없이 SELECT 작업을 실행한다.

오랜시간 활성화된 트랜잭션으로 인해 언두 로그가 쌓여 서버가 느려지거나 문제가 발생하는경우도 있다. 따라서 트랜잭션이 시작되었다면 가능한 롤백/커밋을 해주자.

6. Disk I/O

컴퓨터에서 데이터 저장 매체는 컴퓨터에서 가장 느린 부분이다. 따라서 DB 성능 튜닝은 디스크 I/O를 줄이는게 관건일 때가 많다.

HDD, SDD

기존에 병목이 되던 HDD에서 연산속도가 약 1000배 빠른 SSD로 변경되었지만, 여전히 Random I/O에선 7배, Sequencial I/O 에선 큰 이점이 없어 병목인 것은 변함이 없다.

Random / Sequencial I/O

image

Sequencial I/O는 3개의 페이지를 디스크에 기록하기 위해 시스템 콜을 1번 요청했지만, Random I/O는 시스템 콜을 3번 요청했다. 이런 경우 시간 차이는 거의 3배 난다고 볼 수 있다. DB의 대부분의 작업은 Random I/O여서 MySQL 서버에서는 그룹 커밋이나 바이너리 로그 버퍼, InnoDB 로그 버퍼등의 기능이 있다.

쿼리 튜닝으로 Ramdom I/O 를 Sequencial I/O로 바꾸는 일은 거의 없다. 일반적으로 쿼리 튜닝은 Random I/O를 줄여 개선을 의미한다.

7. 옵티마이저, 힌트

MySQL에서 옵티마이저는 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 최적의 실행 계획을 수립하는 작업을 한다.

7.1 기본 데이터 처리

7.1.1 풀 테이블 스캔과 풀 인덱스 스캔

풀 테이블 스캔

테이블의 데이터를 처음부터 끝까지 읽어서 작업을 처리하는 것. 보통 다음 조건에서 사용된다.

  • 테이블 레코드 건수가 너무 작아서 인덱스를 읽는 것보다 더 빠른 경우(페이지 1개)
  • Where 절이나 On절에 인덱스를 사용할 수 있는 조건이 없는 경우
  • 인덱스 레인지 스캔을 하더라도 조건 일치 레코드 건수가 너무 많은 경우

InnoDB에서 특정 테이블의 연속된 페이지가 읽히면 백그라운드 스레드에 의해 Read ahead 작업이 자동으로 시작된다.

풀 인덱스 스캔

풀 테이블 스캔과 동일하게 Read ahead 작업이 사용된다.

  • Read ahead - 백그라운드 스레드가 4개 또는 8개씩 페이지를 읽어서 최대 64개까지 버퍼 풀에 저장해 둔다.

7.2 병렬 처리

MySQL 8.0 부터 where조건 없이 테이블 전체 건수를 가져오는 쿼리는 병렬 처리가 가능해졌다.

innodb_parallel_read_threads 라는 시스템 변수를 통해 최대 몇개의 스레드를 이용할지 변경할 수 있다.

병렬 처리를 하면 쿼리 처리 속도가 더 빨라지지만, 서버 CPU 개수보다 많아지면 성능이 떨어질 수 있다.

7.3 ORDER BY 처리

정렬을 하는 방법은 인덱스를 이용하는 법과 Filesort를 사용해 처리하는 방법으로 나눌 수 있다.

image

어떤 정렬 방식을 사용하는지 보려면 실행계획의 Extra 컬럼을 보자.

모든 정렬은 Filesort를 이용하지 않는다. 다음과 같은 상황에서 인덱스를 사용하지 못한다.

  • 정렬 기준이 너무 많아서 요건별로 인덱스 생성이 불가능한 경우
  • GROUP BY의 결과 또는 DISTINCT같은 처리의 결과를 정렬해야 하는 경우
  • UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
  • 랜덤하게 결과 레코드를 가져와야 하는 경우

7.3.1 소트 버퍼

정렬을 수행하기 위한 메모리 공간을 뜻한다.

소트 버퍼만으로 모두 정렬할 수 있다면 좋겠지만, 소트 버퍼 공간으로 부족한 경우에는 임시 저장을 위해 디스크를 사용한다.

소트 버퍼에서 정렬을 하고 결과를 임시 디스크에 기록해둔다. 이 방법을 반복해서 수행하고, 정렬된 레코드를 병합하며 다시 정렬한다. (멀티 머지) 수행된 멀티 머지 횟수는 상태 변수에 누적 집계된다.

멀티 머지는 많은 디스크 I/O를 유발한다. 그렇다고 소트 버퍼 크기를 크게 설정해도 큰 차이는 없다.

image

소트 버퍼는 세션 메모리여서 정렬 작업이 많고, 소트 버퍼를 많이 할당하게 되면 OS의 메모리 공간이 부족할 수 있다.

7.3.2 정렬 알고리즘

일반적으로 레코드 크기가 크거나 많은 경우 Two-pass를 유리하고, 반대 상황에서 Single-pass가 유리하다.

Single-pass - 레코드 전체를 소트 버퍼에 담는 방법

image

처음 테이블을 읽을때 모든 컬럼을 읽어서 소트 버퍼에 담고 정렬을 수행한다. 소트 버퍼 공간이 많이 필요하다는 단점이 있다.

Two-pass - 정렬 기준 컬럼만 소트 버퍼에 담는 방법

Single-pass가 도입되기 전부터 사용하던 방식이고, 여전히 사용되는 방식이다. 최신 버전에서는 Single-pass를 주로 사용한다. 다만 레코드 크기가 max_length_for_sort_data 시스템 변수보다 크거나, BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함된 경우 Two-pass를 사용한다.

image

정렬에 필요한 컬럼과 rowid만 읽어서 정렬을 수행한다. 이 방식은 테이블을 두 번 읽어야 한다는 단점이 있다.

7.3.3 정렬 처리 방법

정렬 처리 방법은 속도 순으로 세가지가 있다.

  1. 인덱스를 사용한 컬럼 - 별도 표기 없음
  2. 조인에서 드라이빙 테이블만 정렬 - Using filesort 표시됨
  3. 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 - Using temporary; Using filesort 표시됨

옵티마이저는 1,2,3 순서로 하려고 하지만 안되는 경우 정렬대상 레코드를 최소화하기 위해서 2가지 방법중 하나를 선택한다.

  • 드라이빙 테이블만 정렬한 다음 조인을 수행
  • 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

인덱스를 이용한 정렬

인덱스는 정렬되어 있기 때문에 별도의 정렬 없이 그대로 읽어오면 된다.

정렬에 인덱스를 이용하려면 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고 ORDER BY 순서대로 생성된 인덱스가 있어야 한다. 그리 B-Tree를 제외한 다른 트리는 이 방법을 사용할 수 없고, 여러 테이블이 조인되는 경우에는 NL 방식만 사용가능하다.

조인에서 드라이빙 테이블만 정렬

이 방법을 사용하면 조인에서 드라이빙 테이블의 칼럼만으로 ORDER BY절을 작성해야 한다.

임시 테이블을 이용한 정렬

2개 이상의 테이블을 조인해서 정렬해야 한다면 임시테이블이 필요할 수 있다.

정렬 처리 방법 성능 비교

LIMIT는 MySQL 서버의 작업량을 줄이는 역할을 한다. 보통 ORDER BY와 LIMIT를 함께 사용하는데, ORDER BY와 GROUP BY와 함께 사용되는 경우 LIMIT의 이점을 살릴 수 없다.

  • 스트리밍 방식 (인덱스 정렬) 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트에 전송하는 방식, MySQL 서버는 데이터 가공을 빠르게 시작할 수 있다. 따라서 레코드 수에 상관 없이 빠른 응답 시간을 보장한다.
  • 버퍼링 방식 (드라이빙 테이블 정렬, 임시 테이블 정렬) ORDER BY나 GROUP BY는 스트리밍이 정렬/그루핑 하여 차례대로 보내야 하기 때문에 불가능하다. 그동안 MySQL 서버는 아무 작업을 할 수 없기 때문에 응답 속도가 느리다.

7.4 GROUP BY

GROUP BY는 ORDER BY와 같이 쿼리가 스트리밍 처리를 할 수 없게 한다.

HAVING절에 사용되는 조건은 인덱스를 사용할 수 없으므로 HAVING절에 대한 튜닝은 필요 없다.

반면에, GROUP BY 작업에 인덱스를 사용하는 경우가 있다. (인덱스 스캔, 루스 인덱스 스캔)

7.4.1 타이트 인덱스 스캔

드라이빙 테이블에 속한 컬럼만 이용해 GROUPING을 할 때 인덱스가 있다면 그 인덱스를 차례로 읽으면서 그루핑을 수행하고, 조인한다. 이때는 추가적인 정렬이나, 임시테이블은 필요하지 않다. (그룹값을 처리할땐 임시테이블을 필요)

실행 계획에선 Extra 컬럼에 Using index for group-by 또는 Using temporary, Using filesort가 표시되지 않는다.

7.4.2 루스 인덱스 스캔

실행 계획에선 Extra 컬럼에 Using index for group-by 가 표시된다.

다음과 같이 특수한 상황에서만 사용한다.

SELECT emp_no
FROM salaries
WHERE from_date = '1985-03-01'
GROUP BY emp_no;

인덱스는 (emp_no, from_date)인 상황에서 다음과 같은 쿼리를 날리면 where 조건으로 인덱스 레인지 스캔을할 수 없다. 하지만 실행계획을 보면 Using where; Using index for group-by가 나타나 있는데 어떻게 된걸까

  1. (emp_no, from_date) 인덱스를 스캔하면서 emp_no의 첫 번째 그룹키를 찾아낸다.
  2. 1에서 찾은 그룹키 중에서 from_date값이 이리하는 레코드만 가져온다.
    따라서 1번값과 from_date값으로 찾는 것과 거의 흡사하다.
  3. 다시 1, 2번 반복하다가 없으면 종료한다.

루스 인덱스 스캔은 단일 테이블에 대해 수행되는 GROUP BY만 가능하고, prefix index의 경우는 사용할 수 없다. 인덱스의 특성과 다르게 루스 인덱스 스캔은 카디널리티가 낮을수록 성능이 좋다.

7.4.3 임시 테이블 사용

인덱스를 전혀 사용하지 못할때 사용된다.

실헹계획에선 Extra 컬럼에 Using temporary가 표시된다.

MySQL 8.0 이전에는 묵시적으로 정렬까지 함께 했었는데, 더이상 진행되지 않아 Using filesort는 표시되지 않는다. 하지만, 명시적으로 정렬을 하는 경우 여전히 Using filesort가 표시된다.

MySQL 8.0 이후로는 내부적으로 GROUP BY에 사용된 컬럼으로 Unique 인덱스를 가진 임시 테이블을 만들어 중복제거와 집합 함수 연산을 수행한다.

7.5 DISTINCT 처리

DISTINCT는 집합 함수와 함께 사용될 때와 아닐 때로 나뉜다

7.5.1 DISTINCT without 집합 함수

이 경우에는 내부적으로 GROUP BY와 동일한 방식으로 처리된다.

DISTINCT는 한 컬럼이 아니라 SELECT되는 모든 컬럼 조합을 Unique하게 한다 이 부분을 명심하자.

7.5.2 DISTINCT with 집합 함수

집합 함수 내에서 사용된 DISTINCT는 그 집합 함수 인자로 전달된 컬럼만 유니크 하도록 한다.

SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s

이 쿼리는 COUNT(DISTINCT s.salary) 를 처리하기 위해 내부적으로 임시 테이블을 사용한다. 하지만 실행 계획에는 임시 테이블을 사용한다는 메세지를 표시하지 않는다. 이는 salary 컬럼의 값만 저장하기 위한 임시 테이블을 사용하는데, salary 컬럼에 유니크 인덱스가 생성되어 느려질 수 있다.

이때는 사용되는 컬럼에 인덱스가 있다면 인덱스를 사용할 수 있다.

7.5.3 내부 임시 테이블 활용

여기서 내부라는 뜻은 CREATE TEMPORARY TABLE 로 생성된 임시 테이블과 다르기 때문이다.

다른 임시 테이블과 다르게 쿼리가 끝나면 자동으로 삭제된다.

메모리/디스크 의 임시 테이블

사용되는 스토리지 엔진을 변경해 MEMORY의 메모리 낭비 문제, MyISAM의 트랜잭션 미지원 문제를 개선했다.

MySQL 8.0이전 사용되는 스토리지 엔진 메모리 - MEMORY, 디스크 - MyISAM

MySQL 8.0이후 사용되는 스토리지 엔진 메모리 - TempTable, 디스크 -InnoDB

이보다 커진다면 디스크로 기록되는데 두가지 방법이 있는데 temptable_use_mmap 로 설정한다.

  • MMAP 파일로 기록 - InnoDB로 전환하는 것보다 오버헤드가 적다.
  • InnoDB 테이블로 기록

임시 테이블이 필요한 쿼리

  • ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
  • ORDER BY나 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째가 아닌 쿼리
  • ORDER BY나 GROUP BY가 동시에 존재하는 경우
  • DISTINCT가 인덱스를 활용할 수 없는 경우
  • UNION이나 UNION DISTINCT가 사용된 쿼리
  • 쿼리 실행 계획에서 select_type이 DERIVED인 쿼리

임시 테이블이 디스크에 사용되는 경우

  • UNION이나 UNION ALL에서 SELECT되는 컬럼 중 길이가 512 바이트를 넘는 경우
  • GROUP BY나 DISTINCT 컬럼에서 512 바이트 이상인 경우
  • 메모리 임시 테이블 크기가 tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 temptable_max_ram 보다 큰 경우

7.6 옵티마이저 힌트

옵티마이저 힌트는 항상 같은 위치에서 사용되지만, 영향 범위에 따라 4개의 그룹으로 나뉜다.

  • 인덱스 → 특정 인덱스의 이름을 사용할 수 있다.
  • 테이블 → 특정 테이블의 이름을 사용할 수 있다.
  • 쿼리 블록 → 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트이다. 특정 쿼리 블록의 이름을 명시하는 것이 아니라, 힌트가 명시된 쿼리 블록에 대해서만 영향을 끼친다.
  • 글로벌 → 전체 쿼리에 영향을 미친다.

7.6.1 힌트 위치

힌트를 어떻게 적용하든지 반드시 테이블명을 명시해야한다.

SELECT /*+ INDEX(table column) */ *
FROM table
WHERE column = 'asdf';

만일 자신의 힌트가 잘 사용되었는지 보려면, EXPLAIN 키워드를 붙여 실행해보자.

7.6.2 쿼리블록에 힌트 적용

특정 쿼리 블록에 힌트를 사용하려면 QB_NAME()을 사용해서 이름을 지정해주어야 한다.

SELECT /*+ JOIN_ORDER(e, s@subq1) */ COUNT(*)
FROM EMPLOYEES e
WHERE e.first_name = 'asdf'
and emp_no IN (
	SELECT /*+ QB_NAME(subq1) */ s.emp_no
	FROM salaries s
	WHERE s.salary = 'asdf
);

7.6.3 인덱스 힌트 종류

image

image

  • SET_VAR 쿼리를 실행할 때 시스템 변수는 실행계획에 상당한 영향을 끼친다. / 실행계획 뿐만 아니라 임시적으로 버퍼풀같은걸 늘려쓸 때 사용한다. SELECT /*+ SET_VAR(변수명=값) */ 으로 사용할 수 있다.
  • SEMIJOIN, NO_SEMIJOIN 세미조인 최적화는 세부전략이 있다. image 세부 전략은 다음과 같이 사용할 수 있으며, 세미조인 힌트는 서브쿼리에 명시해야 한다. 다른 방법으로 서브 쿼리 블록에 이름을 명시하고 외부에 힌트를 줘도 된다.
  • SUBQUERY 세미 조인 최적화를 사용하지 못할 때 사용하는 방법으로 2가지의 최적화 방법이 있다. image 사용 방법은 세미조인 힌트와 비슷하다.
  • BNL, NO_BNL, HASHJOIN, NO_HASHJOIN MySQL 8.0.20 부턴 해시조인이 BNL 대신 사용된다. 하지만 BNL 힌트를 사용하면 그 이후 버전에서도 사용이 가능하다. 반면에 HASHJOIN, NO_HASHJOIN은 8.0.18 버전에서만 유효하다.
  • JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, JOIN_SUFFIX STRAIGHT_JOIN외에도 조인 순서를 결정하는 힌트들이다. 차이점은 “일부”만 결정한다.
    • JOIN_FIXED_ORDER → FROM절 테이블의 순서대로 조인
    • JOIN_ORDER → 힌트에 명시된 테이블의 순서대로 조인
    • JOIN_PREFIX → 드라이빙 테이블만 강제
    • JOIN_SUFFIX → 가장 마지막 테이블만 강제하는 힌트
  • INDEX_MERGE, NO_INDEX_MERGE MySQL 서버는 기본적으로 한 테이블에 한 인덱스만 사용한다. 인덱스를 두개 이상 사용하면 결과 값들의 교집합 또는 합집합을 반환한다. 이를 인덱스 머지라 하는데 그 사용 유무를 따질 때 사용되는 힌트다.

8. 실행 계획 / 통계 / 히스토그램

8.1 통계 정보

MySQL 5.7 버전까지 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행계획을 수립했다. 이는 테이블 컬럼값마다 다르기 때문에 정확도가 떨어졌었다.

MySQL 8.0 버전부터 인덱스되지 않은 컬럼까지도 데이터 분포도를 수집해서 저장하는 히스토그램이라는 정보가 도입되었다.

8.1.1 테이블 및 인덱스 통계 정보

CBO에서 가장 중요한 것은 통계 정보다.

기존엔 통계 정보를 최신으로 만들기 위해 실제 테이블의 데이터를 일부 분석해서 통계정보를 보완했다.

MySQL 서버의 통계 정보

5.5 버전까지는 메모리로만 관리했기 때문에 서버가 재시작되면 지금까지 수집된 통계 정보가 모두 사라진다.

MySQL 5.6 버전부터 InnoDB 스토리지 엔진을 사용하는 테이블 통계 정보를 영구적으로 관리한다.

  • stat_name : 'n_diff_pfx%' : 인덱스가 가진 유니크한 값의 개수
  • statname : 'n leaf_pages': 인덱스의 리프 노드 페이지 개수
  • stat_name='size' : 인덱스 트리의 전체 페이지 개수
  • n_roms : 테이블의 전체 레코드 건수
  • clustered_index_size : 프라이머리 키의 크기(InnoDB 페이지 개수)
  • sum_of_other_index_sizes : 프라이머리 키를 제외한 인덱스의 크기(nnoDB 페이지 개수)

테이블 정보는 여러가지 상황에서 갱신되는데, innodb_stats_auto_recalc 시스템 변수를 OFF로 설정하면 막을 수 있다. (default는 TRUE) (OFF인 경우 ANALYZE TABLE 명령을 사용해 갱신한다.)

  • innodb_stats_transient_sample_pages : 자동으로 통계 수집할 때 확인할 페이지 개수 (default 8)
  • innodb_stats_persistent_sample_pages : ANALYZE TABLE 명령이 실행될 때 확인할 페이지 개수 (default 20)

8.2 히스토그램

MySQL 5.7 버전까지는 인덱슨된 컬럼의 유니크한 값 개수 정도만 가지고 있었다. 이는 최적의 실행 계획을 수립하기에 부족하기에 랜덤 인덱스 페이지를 참조했었다.

MySQL 8.0 버전부터 컬럼 데이터 분포도를 참조하게 되었다.

8.2.1 히스토그램 정보 수집 및 삭제

컬럼단위로 관리되며, 자동 수집은 없다.

ANALYZE TABLE … UPDATE HISTOGRAM 명령어로 수집할 수 있다. 수집된 정보는 시스템 딕셔너리에 저장되고, 서버가 시작할 때 information_schema의 column_statistics 테이블에 로드한다.

히스토그램 삭제는 ANALYZE TABLE ... DROP HISTOGRAM 을 통해 할 수 있다.

삭제를 하지 않고 옵티마이저가 사용하지 않게 하려면 SET GLOBAL optimizer_switch='condition_fanout_filter=off'; 명령어를 통해 할 수 있다.

MySQL 8.0 버전에서 2가지 히스토그램 타입이 지원된다.

  • Singleton : 컬럼값 개별로 레코드 건수를 관리한다. (Value-Based, 도수 분포라고 불림) 카디널리티가 적은 경우 사용된다. (성별같은 경우)
  • Equi-Height : 컬럼값의 범위를 균등한 개수로 구분해서 관리 (Height-Balanced, 높이 균형)

MySQL 8.0.19 전까지는 히스토그램 생성 시 서버 풀스캔을 통해 데이터 페이지를 샘플링 했다. 그 이후로는 InnoDB 스토리지 엔진 자체적으로 샘플링 알고리즘을 구현해 풀테이블 스캔을 하지 않는다.

8.2.2 히스토그램 용도

히스토그램 적용 이전의 통계 정보는 대략적인 통계 정보이기 때문에 최적의 실행계획이 아니었다.

히스토그램을 가져 훨씬 정확한 통계 정보를 갖게 되었다. 이는 실제로 잘못 만들어진 실행 계획을 줄여주어 성능에 이점이 있다. (실행 계획 예측 및 조인시 드라이빙 테이블 지정)

8.2.3 히스토그램과 인덱스

조건절에 일치하는 건수를 예측하기 위해 옵티마이저는 B-Tree의 샘플링을 살펴본다. 이를 Index Dive라고 하는데, 이는 어느정도 비용이 들어간다.

8.3 코스트 모델

MySQL 서버가 쿼리를 처리하는데 필요한 작업은 다음과 같다.

  • 디스크로부터 데이터 페이지 읽기
  • 버퍼 풀로부터 데이터 페이지 읽기
  • 인덱스 키 비교
  • 레코드 평가
  • 메모리 임시 테이블 작업
  • 디스크 임시 테이블 작업

최적의 실행 계획을 위해 계산하는 작업의 단위를 Cost Model이라고 한다. 이 작업은 하드웨어에 따라 달라질 수 있다는 단점이 있다.

MySQL 5.7 버전부터 서버의 소스코드에 상수화돼 있던 각 단위 비용을 DBMS 관리자가 조정할 수 있다.

  • server_cost : 인덱스를 찾고 레코드를 비교해 임시 테이블 처리에 대한 비용 관리
    • cost_name : 코스트 모델의 각 단위 작업
    • default_value : 각 단위 작업 비용
    • cost_value : DBMS 관리자가 설정한 값
    • last_updated : 단위 작업 비용이 변경된 시점
    • comment : 추가 설명
  • engine_cost : 레코드를 가진 데이터 페이지를 가져오는데 필요한 비용 관리
    • engine_name : 비용이 적용된 스토리지 엔진
    • device_type : 디스크 타입

image

각 단위 작업의 비용이 변경되면 쿼리 실행 계획에 영향을 끼치게 되는데, 이를 이해하고 비용 조절을 하는 것은 중요하다.

  • key_compare_cost 높이면 정렬을 수행하지 않을 가능성이 높아진다.
  • row_evaluate_cost 비용을 높이면 풀 스캔하는 쿼리들의 가능성이 높아진다. 가능하면 인덱스 레인지 스캔을 사용하는 가능성이 높아진다.
  • disk_temptable_create_cost와 disk_temptable_row_cost를 높이면 디스크 임시테이블을 생성할 가능성이 낮아진다.
  • memory_temptable_create_cost와 memory_temptable_row_cost를 높이면 메모리 임시테이블을 생성하지 않을 가능성이 높아진다.
  • io_block_read_cost 비용이 높아지면 가능하면 InnoDB 버퍼 풀에 데이터 페이지가 많이 적재된 인덱스를 사용할 가능성이 높아진다.
  • memory_block_read_cost 비용이 높아지면 InnoDB 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다해도 그 인덱스를 사용할 가능성이 높아진다.

왠만하면 건들지 않는 것을 추천한다.

8.4 실행 계획 확인

DESC 또는 EXPLAIN 명령으로 확인할 수 있다.

실행 계획 출력 포맷

MySQL 8.0에서 EXPLAIN EXTENDED 또는 EXPLAIN PARTITIONS 명령이 통합되었다.

그리고 FORMAT 옵션을 사용해 JSON 또는 TREE 형태로 볼 수 있다.

쿼리 실행 시간 확인

MySQL 8.0.18 버전부터 쿼리 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 추가되었다. (FORMAT=TREE 고정, 읽는 순서는 안쪽, 상단부터)

SHOW PROFILE 명령도 시간 소요가 많은 부분을 보여준다.

  • actual time : 앞의 숫자는 첫 번째 레코드 값을 가져오는데 걸린 평균 시간, 뒤는 마지막 레코드
  • rows : 조건에 일치하는 테이블의 평균 레코드 수
  • loops : 테이블의 인덱스 등을 이용해 테이블 레코드를 찾는 작업의 횟수

8.5 실행 계획 분석

EXPLAIN 키워드를 사용하면 각 레코드는 쿼리 문장에서 사용된 테이블(임시 포함) 개수만큼 출력한다. 실행 순서는 위에서 아래로 표시되며 위쪽일수록 쿼리의 바깥을 뜻한다.

image

모두 외울 수 없으니 필요할때 찾아보자.

8.5.1 ID

SELECT 쿼리별로 부여되는 식별자 값이다. 이는 쿼리 실행 순서와는 관련이 없다.

실제 순서는 TREE 포맷으로 보는것이 좋다

8.5.2 SELECT TYPE

어떤 타입의 쿼리인지 표시된다.

SIMPLE

UNION, SUB QUERY가 아닌 단순한 SELECT 쿼리인 경우. 아무리 복잡해도 쿼리당 단 하나만 존재한다.

PRIMARY

UNION이나 SELECT쿼리의 실행계획 가장 바깥쪽에 있는 단위 쿼리

쿼리 가장 바깥쪽의 SELECT 문이며, 쿼리당 하나만 존재한다.

UNION

UNION을 사용하는 SELECT 쿼리중 첫번째를 제외한 쿼리들을 표시한다.

첫 번째 쿼리는 DERIVED 로 표시된다.

DEPENDENT UNION

DEPENDENT UNION 또는 UNION과 같이 UNION, UNION ALL로 집합하는 쿼리에 표시된다.

DEPENDENT는 외부 쿼리의 영향을 받는다는 뜻이다.

UNION RESULT

UNION 결과를 담아두는 테이블을 의미한다. MySQL 8.0부터 UNION 시에도 임시테이블을 생성하지 않도록 개선되었다. (버퍼링 시킴)

이 테이블을 UNION RESULT라고 가리킨다. UNION RESULT는 별도의 id가 없다.

SUBQUERY

FROM절이외에서 사용되는 서브쿼리를 뜻한다.

FROM 절에서 사용되는 서브쿼리는 DERIVED로 표시한다.

DEPENDENT SUBQUERY

서브 쿼리가 바깥쪽 SELECT 쿼리에 정의된 컬럼을 사용하는 경우

DERIVED

SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.

MySQL 5.6 부터 파생 테이블에도 인덱스가 생겨 최적화 되었다.

(서브쿼리는 조인으로 풀어쓰자)

DEPENDENT DERIVED

LATERAL JOIN 같이 FROM절의 서브쿼리에도 외부 컬럼을 사용하는 경우를 의미한다.

UNCACHEABLE SUBQUERY

한 문장의 서브쿼리는 여러번 실행될 수 있는데, 이를 다시 사용할 때 캐싱을 하기도 한다.

서브쿼리에 캐시를 사용할 수 없는 경우를 뜻한다.

MATERIALIZED

MySQL 5.6 버전에 도입된 기능으로 FROM절이나 IN(subquery) 형태의 쿼리를 최적화 하기위해 사용된다.

서브 쿼리의 내용일 구체화 한 후 임시 테이블과 조인하는 형태로 최적화 한다.

8.5.3 table

실행 계획은 테이블 기준으로 표시되는데, 테이블에 별칭이 부여된 경우 표시된다.

, <union M,N> 같이 <> 안에 사용되는 경우는 임시 테이블을 뜻하고 숫자는 id이다.

8.5.4 partitions

MySQL 5.7 까지는 EXPLAIN PARTITION 명령을 통해 확인했지만, 8.0부터 실행 계획에서 확인이 가능해졌다.

파티셔닝 된 테이블에 접근할 때 WHERE 조건과 관계없는 파티션에도 접근할지 골라내는 과정을 Partition pruning이라고 하는데, 이 결과를 나타낸다.

보통 partition key 컬럼으로 확인하는데, 테이블 풀 스캔도 가능하다. (물리적으로 별도의 공간을 가지기 때문)

8.5.5 type

각 테이블의 레코드를 어떤 방식으로 읽었는지 나타낸다.

MySQL 매뉴얼에선 조인 타입으로 소개하는데 하나의 테이블로부터 레코드를 읽는 작업도 조인처럼 처리하기 때문이다.

총 12개의 값이 있는데, ALL은 풀테이블 스캔을 뜻하고, 나머지는 모두 인덱스를 사용하는 방법이다. 하나의 단위 쿼리는 단 하나의 type만 사용할 수 있다.

system

레코드가 1건만 존재하거나 한건도 존재하지 않는 테이블을 참조하는 형태

MyISAM, MEMORY 테이블에서만 사용되는 접근 방법이다.

const

PK, UK를 이용하는 WHERE 조건절이며 동시에 1 건을 반환하는 쿼리를 뜻한다.

반환하는 값을 상수화 한다.

eq_ref

여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인되는 처음 테이블의 컬럼 값을 그 다음 테이블의 PK/UK로 사용되는 경우를 뜻한다.

인덱스는 NOT NULL이어야 하며 두번째 테이블에선 항상 1개의 레코드만 반환한다.

ref

eq_ref와 달리 조인 순서와 관계 없고, PK/UK 제약 조건도 없이 equal, not equal 조건으로 검색할 때 사용된다.

fulltext

MySQL 서버의 Full-text Search 인덱스를 사용해 레코드에 접근하는 방식을 뜻한다.

이는 통계 정보가 관리되지 않으며, 전혀 다른 SQL 문법을 사용한다.

우선순위는 상당히 높아, 앞서 설명된 경우가 아니면 전문 인덱스를 사용한다.

전문검색 인덱스가 있는 경우 MATCH … AGAINST … 구문을 사용한다.

ref or null

ref와 같지만 NULL 비교가 추가된 형태이다.

unique subquery

WHERE 조건절에 IN(subquery) 로 접근하는 방식이다.

서브쿼리에 unique한 값만 반환될 때 사용된다.

index_subquery

IN(subquery)의 조건이 중복되는 값이 반환될때 인덱스를 사용해서 중복을 제거하는 방식이다.

range

인덱스 레인지 스캔을 뜻한다. <, >, IS NULL, BETWEEN, IN, LIKE 등 연산자를 이용해 인덱스를 검색할 때 사용된다.

우선순위가 굉장히 낮으며, 상당히 빠르기 때문에 이 방법만 사용해도 최적의 성능을 보장한다.

index merge

2개 이상의 인덱스를 이용해 각각의 검색 결과를 부가적인 작업을 통해 병합하는 방식이다.

일반적으로 range보다 효율이 떨어지며, 전문 검색 인덱스에선 적용되지 않는다.

index

index full scan을뜻한다. 풀테이블 스캔과 비교하는 레코드 수는 같지만, 파일의 크기가 작아 더 빠르다.

range, const, ref 같은 방법을 사용하지 못하고, 커버링 인덱스이고, 인덱스를 사용한 정렬 또는 그루핑을 하는 경우에 사용된다.

ALL

full table scan을 뜻한다. 가장 우선순위가 낮은 비효율적인 방법이다.

Read Ahead같은 최적화 기능이 있기 때문에 억지로 index를 생성했다가 더 느려질 수도 있다.

8.5.6 possible keys

옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정한 인덱스 목록을 뜻한다.

실행 계획에는 큰 영향이 없다.

8.5.7 key

쿼리 실행에 사용된 인덱스를 의미한다.

8.5.8 key_len

복합 인덱스에서 몇개의 컬럼까지 사용했는지 알려준다. (각 레코드의 몇 바이트를 사용했는지)

key_len 필드 값이 데이터 타입의 길이보다 길게 표시되는 경우가 있다. 이는 nullable한 경우 null의 여부를 판단하기 위해 1바이트 더 쓰는 경우이다.

8.5.9 ref

접근 방법이 ref인 경우 equal 비교 조건이 어떤 값인지 보여준다.

상수라면const, 컬럼이라면 table.column으로 표시된다.

함수를 참조하는 경우도 있는데 이는 좋지 않다. 쿼리를 수정해서 참조하지 않도록 하자.

8.5.10 rows

MySQL 옵티마이저는 쿼리 실행 계획이 얼마나 많은 레코드를 읽고 비교하는지 예측 후 비용을 산정한다. 이때 예측된 값이 rows 컬럼에 들어간다. 예측치이기 때문에 정확하지 않다.

실제로 읽고, 비교하는 것이기 때문에 반환되는 레코드수와 다르다.

8.5.11 filtered

rows처럼 비교되는 레코드 수도 중요하지만 WHERE절의 조건과 일치하는 레코드 수도 중요하다.

filtered 컬럼은 필터링되어 남는 레코드의 %비율을 나타낸다.

이 정보를 통해 몇건의 레코드가 반환되는지 알 수 있고, 이를 통해 드라이빙 테이블을 선정할 때 튜닝하기 좋다.

8.5.12 Extra

내부 처리 알고리즘에 대해 보여준다.

const row not found

const 접근 방법으로 테이블을 읽어지만, 1건도 반환되지 않는 경우

Deleting all rows

스토리지 엔진의 핸들러 차원에서 모든 레코드를 삭제하는 기능을 가진 스토리지 엔진 테이블인 경우 나타난다. (MyISAM) 기존은 각 레코드를 호출해서 삭제했지만, 이 방식은 한번에 삭제할 수 있다.

  • 스토리지 엔진 핸들러: MySQL 데이터베이스 시스템에서 스토리지 엔진과 상호 작용하는 인터페이스나 API를 뜻한다.

Distinct

레코드 중복 없이 결과를 반환하기 위해 사용된다.

조인을 하는 경우 필요없는 항목은 무시한다.

FirstMatch

세미조인 최적화중에서 FirstMatch 전략이 사용되는 경우 출력된다.

해당하는 조건에서 첫 번째로 일치하는 한 건만 검색한다.

Full scan on NULL key

column IN (subquery) 과 같은 조건 쿼리에서 col1이 NULL인 경우 NULL IN (subquery) 로 변경된다. 이때 서브쿼리가 1건이라도 결과를 가지면 NULL을, 가지지 않는다면 FALSE를 반환한다.

colmun이 NULL이면 서브쿼리는 풀테이블 스캔을 해야하는데, 이를 알려주기 위한 키워드이다.

이런 NULL 규칙을 무시하려면 where 조건에 column is not null 을 명시해주면 된다.

Impossible HAVING

HAVING절 조건을 만족하는 레코드가 없는경우 표시된다.

이 키워드가 뜨는 경우는 쿼리가 잘못 작성된 것이니 점검하는 것이 좋다.

Impossible WHERE

WHERE 조건이 항상 FALSE인 경우를 뜻한다.

실행계획을 만들기 위한 기초자료가 없다는 뜻

LooseScan

세미 조인 최적화 중 LooseScan 전략이 사용되는 경우를 뜻한다.

No matching min/max row

WHERE 조건에 만족하는 레코드가 한 건도 없는 경우 “Impossible WHERE ..” 문장이 표시된다.

MIN/MAX가 일치하는 결과가 없는 경우를 뜻한다.

no matching row in const table

조인에 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없는 경우를 뜻한다.

실행계획을 만들기 위한 기초자료가 없다는 뜻.

No matching rows after partition pruning

파티션 테이블에 대한 UPDATE/DELETE 쿼리에서 UPDATE/DELETE 할 대상이 없을때 표시된다.

이 메시지는 레코드가 없는게 아니라 해당하는 파티션이 없다는 뜻이다.

No tables used

FROM절이 없거나, FROM DUAL 을 사용하는 쿼리에서 출력된다.

Not exists

A 테이블에 존재하지만 B 테이블에 없는 값을 조회해야 하는 쿼리가 자주 사용된다.

이는 NOT IN(subquery) / NOT EXISTS를 사용하는데 이를 안티조인이라고 한다.

레코드수가 많을땐 아우터 조인을 사용하는게 더 빠르다.

SELECT *
FROM dept_emp de
	LEFT JOIN departments d ON de.dept_no=d.dept_no
WHERE d.dept_no IS NULL;

이런식으로 아우터 조인을 하는 경우 Not exists가 표시된다.

Plan isn’t ready yet

MySQL 8.0 버전에서 다른 커넥션에서 실행중인 쿼리의 실행 계획을 볼 수 있다.

image

이 쿼리의 실행 계획을 다른 커넥션에서 확인해도 똑같은지 확인해보자

image

동일하게 풀테이블 스캔을 하고 있다.

이와 같은 명령어를 사용할 때 Plan is not ready yet이라는 문구가 표시되기도 하는데, 이는 아직 실행계획을 수립하지 못한 경우를 뜻한다.

Range checked for each record(index map:N)

조인같이 조건을 만족하는 레코드를 찾기 위해 탐색하는 경우 첫 번째 조건에 의해 두 번째 조건의 탐색 방법이 천차만별이다. 따라서 두 번째 조건의 탐색 방법을 괄호 안의 인덱스 순번에 의해 결정하는 방법을 표시했다. 괄호 안의 값을 비트로 변환한 뒤 해당하는 인덱스의 비트가 1이면 그 인덱스를 사용한다.

Recursive

MySQL 8.0 버전은 CTE를 사용해 재귀 쿼리를 작성할 수 있다. 이를 뜻하는 키워드이다.

WITH RECURSIVE name (column) AS (
	쿼리
) 쿼리 FROM name;

Rematerialize

MySQL 8.0에 추가된 LATERAL JOIN에 사용된다. 선행 테이블의 레코드 별로 서브쿼리를 실행해서 임시테이블을 만드는데 이 과정을 뜻한다.

Select tables optimized away

MIN/MAX만 SELECT절에 사용되거나 GROUP BY로 MIN/MAX를 조회하는 경우 쿼리는 인덱스를 내림/오름차순으로 1건만 읽는 최적화를 적용한다. 이 과정을 뜻한다.

MyISAM에서는 최적화를 지원하지 않는다.

Start temporary, End temporary

불필요한 중복을 제거하기 위해 내부 임시 테이블을 사용하는 Duplicate Weed-out 최적화 전략이 사용되는 경우 사용된다.

첫 번째 임시 테이블에 Start를 조인이 끝나는 부분에 End 문구를 표시한다.

unique row not found

PK/UK/unique 컬럼으로 아우터 조인을 수행하는데 일치하는 값이 없는 경우 표시된다.

Using filesort

ORDER BY를 처리할때 적절한 인덱스가 없으면 Sort buffer에 복사해 정렬을 수행하는데 이때 표시된다.

Using index(커버링 인덱스)

데이터 파일을 읽지 않고 인덱스만으로 쿼리를 처리할 수 있을 때 표시된다.

커버링 인덱스를 사용하면 성능에 매우 좋기 때문에 사용하도록 유도하는게 좋다.

InnoDB의 경우 클러스터 인덱스가 있기 때문에 커버링 인덱스로 사용될 확률이 매우 높다.

실행 계획의 type이 eq_ref, ref, range, index_merge, index등 인덱스를 사용하는 경우 모두 표시될 수 있다.

Using index condition

옵티마이저가 인덱스 컨디션 푸시다운 최적화를 사용하면 표시된다.

Using index for group-by

GROUP BY는 고부하 작업에 속한다. 하지만 GROUP BY도 루스 인덱스 스캔을 사용하면 더 빠르다. 이는 B-Tree 인덱스를 순서대로 읽어가며 그루핑만 하면 되어 효율적이다. 이때 표시되는 방법이다.

  • AVG, SUM, COUNT 같은 함수를 쓰면 인덱스를 사용하지만, 루스 인덱스 스캔을 할 수 없다. 이때는 표시되지 않는다.
  • MIN, MAX 같은 함수는 루스 인덱스 스캔이 사용된다. 다만 몇가지 주의사항이 있다.
    • WHERE 조건이 인덱스를 사용 못하는 경우 → 타이트 인덱스 스캔
    • WHERE 조건이 인덱스를 사용하는 경우 → 하나의 쿼리에선 하나의 인덱스만 사용할 수 있어서 WHERE 조건에서 사용되는 인덱스를 통해 GROUP BY를 하는 경우에만 루스 인덱스 사용 가능

Using index for skip scan

옵티마이저가 인덱스 스킵 스캔 최적화를 사용하는 경우 표시한다.

Using join buffer(Block Nested Loop), Using join buffer(Bached KeyAccess), Using join buffer(hash join)

일반적으로 조인에서 드리븐 테이블의 컬럼은 인덱스를 생성한다. 하지만, 인덱스가 없는 경우 블록 네스티드 루프 조인이나 해시 조인을 사용한다. 이런 경우 조인 버퍼를 사용하는데 그때 표시된다.

조인 버퍼의 크기도 중요한데, 부족하거나 낭비되지 않도록 적절히 설정하는 것이 좋다.

Using MRR(Multi Range Read)

MySQL 엔진은 실행 계획을 수립하고 그 실행 계획에 맞게 스토리지 엔진의 API를 호출해서 쿼리를 처리한다. 스토리지 엔진은 이 부분을 알지 못해 최적화에 한계가 있다. (예를 들어 아무리 많은 레코드를 읽더라도 키 값을 기준으로 한건씩 반환한다. 동일 페이지더라도 레코드단위의 API 호출을 한다.)

이 단점을 보완하기 위해 MRR이 도입되었는데, 여러개의 키 값을 한 번에 스토리지 엔진에 전달해서 페이지 접근을 최소화 하는 방법이다.

MRR 최적화를 활용하는 BKA 조인도 있다.

Using sort_union(), Using union(), Using intersect()

실행 계획의 type이 index_merge인 경우 인덱스를 2개 이상 사용할 수 있다. 이때 각 인덱스를 읽은 결과를 어떻게 머지하는지 위 메세지를 통해 표시한다.

  • sort_union : union과 동일하지만 union으로 처리하지 못하는 경우(대량의 범위 조건) 사용된다. 다른점은 PK만 먼저 읽고, 정렬 후 병합해 레코드를 읽는다.
  • union : 각 인덱스의 조건이 OR인 경우 합집합을 추출한다.
  • intersect : 각 인덱스의 조건이 AND인 경우 교집합을 추출한다.

Using temporary

쿼리를 처리하는 중간에 결과를 담아두기 위해 임시 테이블을 사용한다.

임시 테이블은 메모리나 디스크에 생기는데 어디에 생기는지는 실행 계획으로 알 수 없다.

  • Using temporary가 표시되지 않아도 임시 테이블을 사용하는 경우도 있다.

image

Using where

MySQL 엔진 - 스토리지 엔진에게 받은 레코드를 가공/연산 한다.

스토리지 엔진 - 디스크나 메모리에서 필요한 레코드를 읽고 저장한다.

MySQL 엔진에서 레코드를 별도 가공하는 경우에 표시한다. 매우 자주 나오는 조건이어서 그냥 문제인지, 중요한 문제인지 잘 걸러야 한다. filtered 컬럼을 참고하면 좋다.

MySQL 엔진보다 스토리지 엔진에서 가공을 하고 MySQL 엔진의 할 일을 덜어주는 것이 좋다.

Zero limit

MySQL 서버에서 쿼리 결과값이 아닌 메타 데이터만 필요한 경우 표시된다.

쿼리 결과가 몇개의 컬럼을 가지고, 타입이 어떤건지 확인할 때 사용하는데 쿼리 마지막에 LIMIT 0을 붙이면 된다.

profile
고승원
봄은 영어로 스프링
이전 포스트

인덱스 스캔의 종류

0개의 댓글