<Real MySQL 8.0(1권)>을 읽고 개인 학습용으로 정리한 내용입니다.
MySQL 엔진 아키텍처
- mysql 서버는 크게 mysql 엔진과 스토리지 엔진으로 구분할 수 있다.
MySQL 엔진
- mysql 엔진은 클라이언트로부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 sql 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.
스토리지 엔진
- mysql 엔진은 요청된 sql 문장을 분석하거나 최적화하는 등 DBMS의 두뇌에 해당하는 처리를 수행하고, 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다.
- mysql 서버에서 mysql 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.
CREATE TABLE test_table (fd1 INT, fd2 INT) ENGINE=INNODB;
- 각 스토리지 엔진은 성능 향상을 위해 키 캐시(MyISAM 스토리지 엔진)나 InnoDB 버퍼 풀(InnoDB 스토리지 엔진)과 같은 기능을 내장하고 있다.
핸들러 API
- mysql 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는데, 이러한 요청을 핸들러 요청이라 하고, 여기서 사용되는 API를 핸들러 API라고 한다.
- mysql 엔진이 각 스토리지 엔진에게 데이터를 읽어오거나 저장하도록 명령하려면 반드시 핸들러를 통해야만한다.
-- 핸들러 API를 통해 얼마나 많은 데이터 작업이 있었는지 확인
SHOW GLOBAL STATUS LIKE 'Handler%'MySQL 스레딩 구조
- mysql 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며 크게 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.
-- 스레드 정보 확인
SELECT thread_id, name, type, processlist_user, processlist_host
FROM performance_schema.threads
ORDER BY type, thread_id포그라운드 스레드(클라이언트 스레드)
- 포그라운드 스레드는 최소한 mysql 서버에 접속된 클라이언트의 수만큼 존재하며, 주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
- 클라이언트 사용자가 작업을 마치고 커넥션을 종료하면 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 돌아간다. 이 때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 스레드의 갯수를 일정하게 유지한다.
- 포그라운드 스레드는 데이터를 mysql의 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.
백그라운드 스레드
- InnoDB의 경우 백그라운드 스레드가 처리하는 작업
- 인서트 버퍼를 병합하는 스레드
- 로그를 디스크로 기록하는 스레드
- InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
- 데이터를 버퍼로 읽어 오는 스레드
- 잠금이나 데드락을 모니터링하는 스레드
- InnoDB에서 데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없지만 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 충분히 설정하는 것이 좋다.
- 사용자 요청을 처리하는 도중 데이터의 쓰기 작업은 지연(버퍼링)되어 처리될 수 있지만 데이터의 읽기 작업은 절대 지연될 수 없다.
=> 이러한 이유로 InnoDB에서는 INSERT, UPDATE, DELETE 쿼리로 데이터가 변경되는 경우 데이터가 디스크의 데이터 파일로 완전히 저장될 때까지 기다리지 않아도 된다.
메모리 할당 및 사용 구조
- mysql에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분된다.
글로벌 메모리 영역
- 일반적으로 클라이언트 스레드의 수와 무관하게 하나의 메모리 공간만 할당된다.
- 생성된 글로벌 영역이 N개라 하더라도 모른 스레드에 의해 공유된다.
- 테이블 캐시
- InnoDB 버퍼 풀
- InnoDB 어댑티브 해시 인덱스
- InnoDB 리두 로그 버퍼
로컬 메모리 영역
- 세션 메모리 영역, 클라이언트 메모리 영역이라고도 표현하며 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.
- 로컬 메모리는 각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다.
- 정렬 버퍼
- 조인 버퍼
- 바이너리 로그 캐시
- 네트워크 버퍼
쿼리 실행 구조
쿼리 파서
- 쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(mysql이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다.
- 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달하게 된다
전처리기
- 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제가 있는지 확인한다.
- 각 토큰을 테이블 이름이나 칼럼 이름 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 들을 확인하
는 과정을 수행한다.
- 실제 존재하지 않거나 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.
옵티마이저
- 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 정하는 역할을 수행하며 DBMS의 두뇌에 해당한다고 볼 수 있다.
실행 엔진
- 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손,발 이라고 비유할 수 있다.
예를 들어 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정했다.
1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
5. 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
핸들러(스토리지 엔진)
- 핸들러는 mysql 서버의 가장 밑단에서 mysql 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 읽어오는 역할을 수행한다.
- 핸들러는 결국 스토리지 엔진을 의미한다.
InnoDB 스토리지 엔진 아키텍처
프라이머리 키에 의한 클러스터링
- InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다.
=> 즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
- 프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 매우 빨리 처리될 수 있다.
외래키 지원
- 외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능이다.
- InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래키의 존재는 주의하는 것이 좋다.
MVCC(Multi Version Concurrency Control)
- 일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이다.
- MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공한다.
- InnoDB는 언두 로그(Undo log)를 이용해 기능을 구현하는데 여기서 멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시
에 관리된다는 의미이다.
예시) 지역 칼럼이 '서울'로 되어 있는 레코드를 '경기'로 변경하는 UPDATE문이 호출되었다고 가정해보자
- UPDATE 문이 실행되면 커밋 실행 여부와 관계없이 InnoDB의 버퍼 풀은 새로운 값인 '경기'로 업데이트 된다.
- UPDATE 전의 데이터인 '서울'은 언두 로그로 복사되어 저장되어있다.
- 이 때 아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 해당 레코드를 조회하면 어떻게 될까?
=> 격리 수준이 READ_COMMITTED나 그 이상의 격리 수준의 경우 언두 로그에 존재하는 변경 전 데이터를 읽고
격리 수준이 READ_UNCOMMITTED인 경우에 InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽는다.
- 즉, 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 상황에 따라 달라지는 구조이다.
InnoDB 버퍼 풀
- InnoDB 스토리지 엔젠에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.
- 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.
트랜잭션
- 트랜잭션은 작업의 완전성을 보장해준다.
- 잠금(Lock)는 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터 정합성을 보장하기 위한 기능이다.
- 격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.
MySQL 격리수준
READ UNCOMMITTED
- dirty read 현상이 발생할 수 있다. (데이터가 나타났다 사라졌다 하는 현상)
예시)
1. 트랜잭션 A가 insert 작업을 수행했다.(아직 트랜잭션 A는 모든 작업을 마치지 않았다.)
2. 트랜잭션 B에서 A가 insert한 데이터를 읽었다 => 데이터가 존재함을 확인
3. 트랜잭션 A가 문제가 발생해서 모든 작업을 롤백했다.
=> 결과적으로 트랜잭션 B는 존재하지 않는 데이터를 존재한다고 읽고 있다.
READ COMMITTED
- dirty read 현상이 발생하지 않는다.
- NON_REPEATABLE READ라는 부정합의 문제가 존재한다.
=> 한 트랜잭션 내에서 같은 select를 여러번 수행했을 때 항상 같은 결과를 보장하지 않는다.
REPEATABLE READ
- mysql의 innoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.
- 바이너리 로그를 가진 mysql 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야한다.
- 해당 트랜잭션보다 낮은 번호의 트랜잭션에서 변경한 것만 본다.
예시)
1. 트랜잭션 A의 트랜잭션 아이디는 10, 트랜잭션 B의 트래잭션 아이디는 12
2. 트랜잭션 B가 select 수행 => 트랜잭션 A의 insert 수행 => 트랜잭션 B의 다시 한번 select 수행(본인보다 높은 트랜잭션 아이디를 가진 A의 변경사항은 읽지 않는다)
인덱스
- 데이터를 빠르게 검색하기 위해 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만들어둔다
=> 인덱스를 칼럼의 값을 미리 정렬해두어 보관한다.
- 인덱스는 정렬되어 있는 SortedList이고 데이터 파일은 저장되는 순서대로 저장하는 ArrayList라고 비유할 수 있다.
B-Tree 인덱스
- B-Tree는 칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.
- B-Tree의 구조는 최상위에 하나의 '루트 노드'와 중간의 '브랜치 노드', 가장 하위의 '리프 노드'로 구성되어 있다.
- 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아
가기 위한 주솟값을 가지고 있다.
- 인덱스의 키 값은 모두 정ㄹㄹ되어 있지만 데이터 파일의 레코드는 정렬되어 있지 않고 임의의 순서로 저장되어 있다.
- 인덱스는 테이블의 키 칼럼만 가지고 있으므로 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야한다.
- InnoDB 테이블은 클러스터링을 통해 프라이머리 키를 주소처럼 사용하기 때문에 실제 데이터가 저장되어 있는 물리적 주소가 아니라 논리적 주소를 가진다고 볼 수 있다.
=> 그래서 InnoDB 테이블에서 인덱스를 통해 레코드를 읽을 때는 인덱스에 저장되어 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장되어 있는 레코드를 읽는다.
- 즉, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색해야 한다.
MySQL이 인덱스를 이용하는 방법
인덱스 레인지 스캔
- 검색해야할 인덱스의 범위가 결정되었을 때 사용하는 방식
- 루트와 브랜치 노드를 이용해 스캔 시작 위치를 검색하고, 그 지점부터 필요한 방향(오름차순 또는 내림차순)으로 인덱스를 읽어 나간다.
예시) select * from employees where first_name between 'Ebbe' and 'Gad';
=> first_name으로 인덱스가 설정되어 있음
1. 인덱스에서 조건을 만족하는 값('Ebbe')이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색이라고 한다.
2. 1번에서 탐색된 위치부터 필요한 만큼('Gad') 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔이라고 한다.
3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최중 레코드를 읽어온다.
인덱스 풀 스캔
- 인덱스의 처음부터 끝까지 모두 읽는 방식
- 대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다.
=> 인덱스는 (A,B,C)의 순서로 만들어져 있지만 쿼리의 조건절은 B칼럼이나 C칼럼으로 검색하는 경우이다.
- 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용된다.
인덱스 스킵 스캔
- MySQL 8.0 버전에 도입된 방식으로 첫번째 인덱스가 사용되지 않고 두번째 이후 인덱스가 사용되었을 때도 효율적으로 인덱스가 사용
될 수 있도록 하는 방식
예시) select * from employees where birth_date >= '1965-02-01';
=> gender, bitrh_date 로 인덱스가 설정되어 있음
- 일반적으로 이렇게 2번째 인덱스부터 설정되어 있을 경우 인덱스 풀 스캔이 수행됨
- 인덱스 스킵 스캔은 이러한 경우에도 인덱스가 필요한 부분한 탐색할 수 있도록 도와줌
- 방식) 앞의 인덱스(gender)의 유니크한 값('m','f')들을 임의적으로 추가해 온전한 인덱스를 만들어서 탐색한다.
=> (m, 1965-02-01) or (f, 1965-02-01)
클러스터링 인덱스
- 클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용된다.
=> 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다.
클러스터링 인덱스 선정 방법
1. 프라이머리키가 있다면 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
- InnoDB 스토리지 엔진이 적절한 클러스터링 키 후보를 찾지 못한 경우 내부적으로 레코드의 일련번호 칼럼을 생성한다.
- 이렇게 자동으로 추가된 프라이머리 키는 사용자에게 노출되지 않으며, 쿼리 문장에 명시적으로 사용할 수 없다. 즉, 이것은 우리에게 아무런 혜택을 주지 않기에 가능하다면 프라이머리 키를 명시적으로 생성하자
클러스터링 인덱스의 장점과 단점
장점
- 프라이머리키(클러스터링 키)로 검색할 때 성능이 매우 빠름(특히, 프라이머리 키를 범위 검색하는 경우)
- 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(커버링 인덱스)
단점
- 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
- 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림
- INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
- 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림
프라이머리 키는 AUTO_INCREMENT보다 업무적인 칼럼으로 생성(가능한 경우)
- InnoDB의 프라이머리 키는 클러스터링 키로 사용되며, 이 값에 의해 레코드의 위치가 결정된다. 즉, 프라이머리 키로 검색하는 경우 클러스터링되지 않은 테이블에 비해 매우 빠르게 처리될 수 있다.
- 설명 칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다.
프라이머리 키는 반드시 명시할 것
- 가능하면 AUTO_INCREMENT 칼럼을 이용해서라도 프라이머리 키는 생성하는 것을 권장한다.
- InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 내부적으로 일련번호 칼럼을 추가한다.
=> 즉, 프라이머리 키를 정의하지 않는 경우와 AUTO_INCREMENT 칼럼을 생성하고 프라이머리 키로 설정하는 것이 똑같다. 그렇다면 사용자가 사용할 수 있는 값(AUTO_INCREMENT)을 프라이머리 키로 설정하는 것이 좋을 것이다.
옵티마이저와 힌트
쿼리 실행 절차
1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블로부터 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
- 첫번째 단계를 'SQL 파싱' 이라고 하며 MySQL 서버의 'SQL 파서'라는 모듈로 처리한다.
=> SQL 문장이 문법적으로 달못됐다면 이 단계에서 걸러진다. 또한 이 단계에서 'SQL 파스 트리'가 만들어진다. MySQL 서버는 SQL 문당 그 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행한다.
- 두번째 단계는 '최적화 및 실행 계획 수립' 단계이며 '옵티마이저'에서 처리한다. 두번째 단계가 완료되면 쿼리의 실행 계획이 만들어진다.
- 세번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.
=> 첫번째, 두번째 단계는 거의 MySQL 엔진에서 치리하며, 세번째 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.
ORDER BY 처리(Using filesort)
- 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 떄 'FileSort'라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.
=> 인덱스를 미리 정렬되어 있기에 오름차순또는 내림차순으로 읽으며 정렬된 값을 가져올 수 있다.
- MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 칼럼에 'Using filesort'라는 메시지로 표시된다.
소트 버퍼
- MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼라고 한다.
=> 소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.
- 만약 정렬해야하는 레코드가 아주 소량이어서 메모리에 할당된 소트 버퍼만으로 정렬할 수 있다면 아주 빠르게 정렬할 수 있을 것이다. 하지만 정렬해야할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면 정렬해야할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다. 메모리의 소트 버퍼에서 정렬을 수행하고 그 결과를 임시로 디스크에 기록해둔다. 그리고 다음 레코드를 가져와서 다시 정렬새서 반복적으로 디스크에 임시 저장한다. 이처럼 각 버퍼 크기만큼 전렬된 레코드를 다시 병합하면서 정렬을 수행한다. 이 병합 작업을 멀티 머지라고 표현한다.
=> 이 작업들이 모두 디스크의 쓰기와 읽기를 유발하여, 레코드 건수가 많을 수록 이 반복 작업의 횟수가 많아진다.
정렬 알고리즘
- 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 '싱글 패스'와 '투 패스' 2가지 정렬 모드로 나눌 수 있다.
싱글 패스
- 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식이다.
- 처음 데이터를 읽어올 때 정렬에 필요하지 않은 칼럼까지 전부 읽어서 소트 버퍼에 담고 정렬한다. 그리고 정렬이 완료되면 내용을 그대로 클라이언트로 넘겨준다.
투 패스
- 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼들을 가져오는 방식이다.
- 최신 버전에서는 일반적으로 싱글 패스 정렬방식을 사용한다.
- 투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 불합리하지만 싱글 패스 정렬 방식은 더 많은 소트 버퍼 공간이 필요하다.
- 싱글 패스 방식은 정렬 대상 레코드의 크기나 건수가 작을 경우 빠른 성능을 보이며, 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이라고 볼 수 있다.
정렬 처리 방법
- 쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 정렬이 처리된다. 일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 떨어진다.
정렬 처리 방법 실행 계획의 Extra 컬럼 내용
인덱스를 사용한 정렬 별도 표기 없음
조인에서 드라이빙 테이블만 정렬 "Using filesort" 메시지가 표시됨
조인에서 조인 결과를 임시 테이블로 저장 후 정렬 "Using temporary; Using filesort" 메시지가 표시됨
- MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 2가지 방법중 하나를 선택한다.
1. 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행
=> 일반적으로 조인이 수행되면서 레코드 건수와 레코드의 크기는 거의 배수로 불어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 방법이 효율적이다.
SELECT *
FROM employees e, salaries s
WHERE s.emp_no=elemp_no
AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY s.salary;
- employees 테이블의 프라이머리키(클러스터링키)인 emp_no로 범위 검색하는 조건이 있으므로 employees 테이블이 드라이빙 테이플, salaries 테이블이 드리븐 테이블이 된다.
- 우선 드라이빙 테이블에서 e.emp_no을 통해 조건 범위에 해당하는 값들을 조회하고 바로 정렬을 수행할 수 있다면 1번 방식(조인의
드라이빙 테이블만 정렬한 다음 조인을 수행)이 가능하겠지만 정렬 방식은 드리븐 테이블의 칼럼을 통해 수행된다. 즉, 드라이빙 테이
블에서 드리븐 테이블을 조인해서 가져온 후 정렬을 수행하게 된다.
정렬 처리 방법의 성능 비교
- 쿼리를 수행할 때 ORDER BY와 LIMIT가 함께 사용되는 경우가 있다. 일반적으로 LIMIT는 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리할 작업량을 줄이는 역할을 한다.
그러나 ORDER BY나 GROUP BY 같은 작업은 정렬이나 그루핑 작업을 실행해야만 LIMIT으로 건수를 제한할 수 있다.
쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업을 통해 느리게 작업하는 것을 '스트리밍 처리'와 '버퍼링 처리'을 작업들을 수행할 수 있다.
스트리밍 처리
- 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식
- 이 방식의 경우 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다. 그렇기에 스트리밍 방식으로 처리되는 쿼리에서 LIMIT처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줄 수 있다.
버퍼링 방식
- 버퍼링 방식으로 처리되는 쿼리는 먼저 결과를 모아서 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야한다. 그래서 버퍼링 방식으로 처리되는 쿼리는 LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로
도움이 되지 않는다.
- ORDER BY의 3가지 처리 방법 가운데 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며, 나머지는 모두 버퍼링된 후에 정렬된다.
GROUP BY 처리
- GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나다.
- GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.
인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
- ORDER BY와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다.
- GROUP BY가 인덱스를 사용해서 처리된다 하더라도 그룹 함수 등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다.
루스 인덱스 스캔을 이용하는 GROUP BY
- 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 칼럼에 "Using index for group-by" 코멘트가 표시된다.
임시 테이블을 사용하는 GROUP BY
- GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다.
- GROUP BY가 필요한 경우 내부적으로 GROUP BY 절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다. 즉, 쿼리를 처리하기 위한 임시 테이블을 생성한다. 그리고 조인의 결과를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT 또는 UPDATE를 실행한다.
CREATE TEMPORARY TABLE ... (
last_name VARCHAR(16),
salary INT,
UNIQUE INDEX ux_lastname (last_name));DISTINCT 처리
- 특정 칼럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다.
- 집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요하다. 하지만 실행 계획의 Extra 칼럼에는 "USING temporary" 메시지가 출력되지 않는다.
- DISTINCT는 SELECT하는 레코드(튜플)을 유니크하게 SELECT하는 것이지, 특정 칼럼만 유니크하게 조회하는 것이 아니다.
쿼리 실행 계획 확인(실행 시간 포함)
- EXPLAIN ANALYZE : 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인한다.
- EXPLAIN ANALYZE를 통해 확인한 쿼리 실행 계획에서 실행 순서 파악하기
- 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
- 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행
===다른말로===
1. 위에서 아래로 내려가면서 제일 먼저 읽을 라인 찾기
2. 내려가는 과정에서 같은 들여쓰기가 존재하면, 무조건 위에서 아래순으로 읽기
3. 읽고자 하는 단계보다 들여쓰기된 하위 단계가 존재한다면 가장 안쪽으로 들여쓰기된 단계를 시작으로 하여 한 단계씩 상위 단계로 읽어 나오기
- EXPLAIN ANALYZE 명령은 EXPLAIN 명령과 달리 실행 계획만 추출하는 것이 아니라 실제 쿼리를 실행하고 사용된 실행 계획과 소요된 시간을 보여주는 것이다. 그렇기 때문에 쿼리의 실행 계획이 아주 나쁜 경우라면 EXPLAIN 명령으로 먼저 실행 계획만 확인해서 어느 정도 튜닝한 후 EXPLAIN ANALYZE 명령을 실행하는 것이 좋다.
실행 계획 분석
id 칼럼
- 하나의 SELECT 문장은 다시 1개 이상의 하위(SUB) SELECT 문장을 포함할 수 있다. SELECT 키워드 단위로 구분한 것을 "단위(SELECT) 쿼리"라고 표현한다.
- 실행 계획에서 가장 왼쪽에 표시되는 id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다.
=> 조인되어 하나의 테이블이 되는 경우 등 실행 계획 레코드에는 여러줄로 출력되지만 같은 id값이 부여될 수도 있다.
select_type 칼럼
- 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.
SIMPLE
- UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리의 경우 SIMPLE로 표시된다.
- 아무리 복잡한 쿼리라해도 select_type이 SIMPLE인 단위 쿼리는 하나만 존재한다.
PRIMARY
- UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리는 select_type이 PROMARY로 표시된다.
- SIMPLE과 마찬가지로 select_type이 PRIMARY인 단위 쿼리는 하나만 존재한다.
UNION
- UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시된다.
- UNION의 첫번째 단위 SELECT는 UNION되는 쿼리 결과들을 모아서 저장하는 임시 테이블(DERIVED)이 select_type으로 표시된다.
DEPENDENT UNION
- DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다.
EXPLAIN
SELECT *
FROM employees e1 WHERE e1.emp_no IN (
SELECT e2.emp_no FROM employees e2 WHERE e2.first_name='Matt'
UNION
SELECT e3.emp_no FROM employees e3 WHERE e3.last_name='Matt'
);
UNION RESULT
- UNION RESULT는 UNION 결과를 담아두는 테이블을 의미한다.
- 8.0 이전 버전에서는 UNION ALL 쿼리까지 모두 UNION의 결과를 임시 테이블로 생성했는데, 8.0 이후 버전부터는 UNION ALL의 경우 임시 테이블을 사용하지 않도록 개선됐다.
SUBQUERY
- select_type의 SUBQUERY는 FROM 절 이외에서 사용되는 서브쿼리만을 의미한다.
- FROM 절에서 사용된 서브쿼리는 select_type이 DERIVED로 표시된다.
DEPENDENT SUBQUERY
- 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우 select_type에 DEPENDENT SUBQUERY라고 표시된다.
=> 외부 쿼리에 의해 영향을 받는 서브쿼리
type 칼럼
const
- 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며 반드시 1건을 반환하는 쿼리 방식을 const라고 한다.
- 다중 컬럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 칼럼만 조건으로 사용할 때는 const 타입의 접근 방법을 사용할 수 없다.
=> 이 경우 실제 레코드가 1건만 저장되어 있더라도 MySQL 엔진이 데이터를 읽어보지 않고서는 레코드가 1건이라는 것을 확신할 수
없기 때문이다.
eq_ref
- eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다.
- 조인에서 처음 읽은 테이블의 칼럼값을, 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때를 가리켜 eq_ref라고 한다.
- 또한 두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NOT NULL이어야 하며, 다중 칼럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 칼럼이 비교 조건에 사용되어야만 eq_ref 접근 방법이 사용될 수 있다.
=> 즉, 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 조재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.
ref
- ref 접근 방법은 eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키 등의 제약 조건도 없다.
- 인덱스의 종류와 관계없이 동등 조건으로 검색할 때는 ref 접근 방법이 사용된다.
- ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않다.
==위의 3가지 방법 모두 WHERE 조건절에서 동등 비교 연산자를 사용하기 때문에 웬만하면 성능상 문제를 일으키지 않는 접근 방법이다. 쿼리를 튜닝할 때도 이 세가지 접근 방법에 대해서는 크게 신경쓰지 않고 넘어가도 무방하다==
fulltext
- fulltext 접근 방법은 MySQL 서버의 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다.
- 전문 검색은 "MATCH(...) AGAINST(...)" 구분을 사용해서 실행하는데, 이때 반드시 해당 테이블에 전문 검색용 인덱스가 준비돼 있어야만 한다.
ref_or_null
- ref 접근 방법과 같은데, NULL 비교가 추가된 형태다.
- 잘 사용되지는 않지만 나쁘지 않은 접근 방법 정도로 기억해두면 충분하다.
unique_subquery
- WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법이다.
- unique_subquery의 의미 그대로 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용한다.
index_subquery
- IN(subquery)에서 subquery가 중복된 값을 반활할 수도 있다. 이때 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 index_subquery 접근 방법이 사용된다.
index
- 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔이다.
ALL
- 풀 테이블 스캔을 의미하는 접근 방법이다.
possible_keys 칼럼
- MySQL 옵티마이저는 쿼리를 처리하기 위해 여러 가지 처리 방법을 고려하고 그중에서 비용이 가장 낮을 것으로 예상되는 실행 계획을
선택해 쿼리를 실행한다. 그 중 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록이다.
- possible_keys 칼럼은 쿼리를 튜닝하는데 크게 도움이 되지 않기 때문에 특별한 경우를 제외하고는 그냥 무시해도 된다.
key 칼럼
- key 칼럼에 표시되는 인덱스는 최종 선택된 실행계획에서 사용하는 인덱스를 의미한다.
- 쿼리를 튜닝할 때는 key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다.
- type이 ALL일 때와 같이 인덱스를 전혀 사용하지 못하면 key 칼럼은 NULL로 표시된다.
key_len 칼럼
- key_len 칼럼의 값은 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 우리에게 알려준다. 더 >정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다.
EXPLAIN SELECT * FROM dept_emp WHERE dept_no='d005'; => key_len : 16
- 이 경우 dept_no 칼럼의 타입이 CHAR(4)이기 때문에 인덱스(프라이머리) 키에서 앞쪽 16바이트만 유효하게 사용했다는 의미이다.
이 테이블의 dept_no 칼럼은 utf8mb4 문자 집합을 사용해서 utf8mb4 문자 집합에서는 문자 하나가 차지하는 공간이 1바이트에서 4바이트까지 가변적이다.
- 하지만 MySQL 서버가 utf8mb4 문자를 위해 메모리 공간을 할당해야 할 때는 문자와 관계없이 고정적으로 4바이트로 계산한다. 그래서 위의 실행 계획에서 key_len 칼럼의 값으로 16바이트가 표시된것이다.
- 일부 NULLABLE 칼럼으로 정의되었을 경우 칼럼의 값이 NULL인지 아닌지를 저장하기 위해 1바이트를 추가로 더 사용하는 경우도 있다.
ref 칼럼
- 접근 방법이 ref면 참조 조건으로 어떤 값이 제공됐는지 보여준다.
참고1
- 중첩된 쿼리(Nested Query): SELECT되는 칼럼에 사용된 서브쿼리를 네스티드 쿼리라고 한다.
- 서브 쿼리(Subquery): WHERE 절에 사용된 경우에 일반적으로 그냥 서브쿼리라고 한다.
- 파생 테이블(Derived Table): FROM 절에 사용된 서브쿼리를 MySQL에서는 파생 테이블이라고 한다.
- 스칼라 서브쿼리(Scalar Subquery): 하나의 값만(칼럼이 단 하나인 레코드 1건만) 반환하는 쿼리
- 로우 서브쿼리(Row Subquery): 컬럼의 개수와 관계없이 하나의 레코드만 반환하는 쿼리
참고2
-쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 칼럼의 값이 DERIVED인 것이 있는지 확인해야 한다.
- 서브 쿼리를 조인으로 해결할 수 있는 경우라면 서브쿼리보다는 조인을 사용할 것을 강력히 권장한다.
참고자료
Real MySQL 8.0(1권)
댓글