2장. SQL 튜닝 용어

2장 SQL 튜닝 용어 정리

2.1 물리 엔진과 오브젝트 용어

2.1.1 DB 엔진 용어

스토리지 엔진

• 스토리지 엔진은 사용자가 요청한 SQL 문을 토대로 DB에 저장된 디스크나 메모리에서 필요한 데이터를 가져오는역할을 수행합니다
• 이후 가져온 데이터를 MySQL 엔진으로 보내줍니다
• 스토리지 엔진 종류
  • InnoDB(default): 온라인 상에서 트랜잭션 발생으로 데이터 처리하는 OLTP 환경
  • MyISAM: 대량의 쓰기 트랜잭션 발생하는 환경
  • Memory: 메모리 데이터를 로드하여 빠르게 읽어야 하는 환경

MySQL 엔진

• MySQL 엔진은 사용자가 요청한 SQL 문을 넘겨받은 뒤 SQL 문접 검사와 적절한 오브젝트 활용을 검사하고, SQL 문을 취소 단위로 분리하여 원하는 데이터를 빠르게 찾는 경로를 모색하는 역할을 수행합니다
• 이후 스토리지 엔진으로부터 전달받은 데이터 대상으로 불필요한 데이터는 제거하거나 가공 및 연산하는 역할을 합니다
• SQL 문의 시작 및 마무리 단계에 SQL 엔진이 관여하며, 스토리지 엔진으로부터 필요한 데이터만을 가져오는 핵심 역할을 담당합니다

2.1.2 SQL 프로세스 용어

파서(Parser)

• 파서는 MySQL 엔진에 포함되는 오브젝트로, 사용자가 요청한 SQL 문을 쪼개 최소 단위로 분리하고 트리를 만듭니다. 트리를 만들면서 문법 검사를 수행합니다.
• 트리의 최소 단위는 >, <, = 등의 기호나 SQL 키워드로 분리합니다. 만약 트리에 허용되지 않는 문법이 포함된다면 에러 발생과 동시에 실행이 종료됩니다

전처리기(Preprocessing)

• 전처리기는 MySQL 엔진에 해당하는 오브젝트로, 파서에서 생성한 트리를 토대로 SQL 문에 구조적인 문제가 없는지 파악합니다
• SQL 문에 작성된 테이블, 열, 함수, 뷰와 같은 오브젝트가 이미 생성된 오브젝트인지, 접근 권한이 부여되어 있는지 확인하는 역할을 합니다

옵티마이저(Optimizer)

• 옵티마이저는 MySQL 핵심 엔진 중 하나로, 전달된 파서 트리를 토대로 필요하지 않은 조건은 제거하거나 연산 과정을 단순화합니다
• 나아가 어떤 순서로 테이블에 접근할지, 인덱스를 사용할지, 사용한다면 어떤 인덱스를 사용할지, 정렬할 때 인덱스를 사용할지 아니면 임시 테이블(temporary table)을 사용할지와 같은 실행 계획을 수립합니다
• 단, 실행 계획으로 도출할 수 있는 경우의 수가 지나치게 많은 경우, 실행 계획을 수립하고 비용을 산정하여 최적의 실행계획을 결정하는데 시간이 오래 걸리므로 모든 실행 계획을 판단하지는 않습니다. 이는 옵티마이저가 선택한 최적의 실행 계획이 최상의 실행 계획이 아닐 수 있다는 것을 의미합니다

엔진 실행기(Engine executor)

• 엔진 실행기는 MySQL 엔진과 스토리지 엔진 영역 모두에 걸치는 오브젝트로, 옵티마이저에서 수립한 실행 계획을 참고하여 스토리지 엔진에서 데이터를 가져옵니다
• 이후 MySQL 엔진에서는 읽어온 데이터를 정렬하거나 조인하고, 불필요한 데이터는 필터링 처리하느 추가 작업을 합니다
• 따라서 MySQL 엔진의 부하를 줄이려면 스토리지 엔진에서 가져오는 데이터양을 줄이는게 중요합니다

2.1.3 DB 오브젝트 용어

(테이블, 행, 열 생략)

기본키(Primary key)

• 기본키는 특정 행을 대표하는 열을 가리키는 용어로 주 키라고도 합니다. 기본키로 설정된 값은 어떤 행과도 중복되지 않는 값입니다
• 기본키는 각 테이블에서 1개 열만으로 생성하지만 상황에 따라 2개 이상의 여려을 조합해 기본 키를 구성할 수 도 있습니다
• MySQL에서 기본 키는 클러스터형 인덱스로 작동합니다. 이는 기본 키의 구성, 열 순서를 기준으로 물리적인 스토리지에 데이터가 쌓인다는 뜻입니다. 즉, 비슷한 기본 키 값들이 근처리에 적재되므로 기본 키를 활용하여 인덱스 스캔을 수행하면 테이블 데이터에 빠르게 접근할 수 있습니다

외래키(Foreign key)

• 외래 키는 외부에 있는 테이블을 항상 참조하면서, 외부 테이블의 데이터가 변형되면 함께 영행을 받는 관계를 설정하는 키입니다

인덱스(Index)

• 인덱스는 데이터베이스에서 키값으로 실제 데이터 위치를 식별하고 데이터 접근 속도를 높이고자 생성되는, 키 기준으로 정렬된 오브젝트입니다
• 인덱스를 생성하려는 열의 속성에 따라 고유 인덱스(unique index)와 비고유 인덱스(non-unique index)로 구분할 수 있습니다
• 고유 인덱스
  • 말 그대로 인덱스를 구성하는 열들의 데이터가 유일하다는 의미입니다.
  • 중복이 없는 열들을 고유 인덱스로 생성하려 한다면 중복이 있는지 검증하는 절차를 거쳐야 하므로, 불필요한 중복 검증 과정이 추가되니 주의해야 합니다
• 비고유 인덱스
  • 고유 인덱스에서 데이터의 유일한 속성만 제외한 키입니다

2.2 논리적인 SQL 개념 용어

(서브쿼리, 관계성, 반환결과, 조인 연산방식 생략)

2.2.5 조인 알고리즘 용어

드라이빙 테이블과 드리븐 테이블

• 조인을 하는 2개의 테이블이 있을 때 먼저 접근하는 테이블을 드라이빙 테이블, 그 후에 조인 조건을 검사하는 테이블을 드리븐 테이블이라고 합니다

• 드라이빙 테이블의 데이터를 가지고 드리븐 테이블에 접근하게 되는 만큼 사실상 디라이븡 테이블을 무엇으로 선정할지는 매우 중요한 문제입니다

• 가능하면 적은 결과가 반환될 것으로 예상되는 테이블을 드라이빙 테이블로 정하고, 조인 조건절의 열이 인덱스로 설정되도록 구성해야 합니다

중첩 루프 조인 (Nested Loop Join)

• 중첩 루프 조인(NL)은 드라이빙 테이블의 데이터 1건당 드리븐 테이블을 반복해 검색하며 최종적으로는 양쪽 테이블에 공통된 데이터를 출력합니다

• 인덱스가 없는 경우 테이블 풀스캔이 발생합니다 (드라이빙, 드리븐 모두)

• 인덱스가 있는 경우

• 인덱스를 이용해 데이터를 찾아가는 과정에서 임의 접근 방식인 랜덤 엑세스(Random Access)가 발생합니다. 따라서 랜덤 엑세스를 줄일 수 있도록 인덱스를 설계하고 조건절을 작성해야 합니다

블록 중첩 루프 조인(Block Nested Loop Join)

• 중첩 루프 조인의 효율성을 높이고자 탄생한 것이 블록 중첩 루프 조인입니다
• 드라이빙 테이블에 대해 조인 버퍼(join buffer)라는 개념을 도입하여 조인 성능을 향상시킵니다
• 조인 버퍼에 드라이빙 테이블에서 검색된 데이터를 가득 채워질 때까지 적재합니다
• 이 후에 드리븐 테이블을 풀스캔을 하면서 하나의 데이터를 조인 버퍼의 값들과 비교합니다
• 드리븐 테이블의 풀 스캔을 줄이는 게 목적입니다

배치 키 액세스 조인(Batched key access join)

해시 조인(Hash join)

2.3 개념적인 튜닝 용어

2.3.1 기초용어

오브젝트 스캔 유형

테이블 풀 스캔(table full scan)

• 인덱스를 거치지 않고 테이블로 바로 직행하여 처음부터 끝까지 테이블을 훑어보는 방식입니다.
• Where 절의 조건문을 기준으로 활용할 인덱스가 없거나, 전체 데이터 대비 대량의 데이터가 필요할 때 테이블 풀 스캔을 수행할 수 있습니다.
• 테이블 풀 스캔은 인덱스 없이 사용하는 유일한 방식

인덱스 범위 스캔(index range scan)

• 말 그대로 인덱스를 범위 기준으로 스캔한 뒤 스캔 결과를 토대로 테이블의 데이터를 찾아가는 방식입니다
• SQL 문에서 Between ~ And 구문이나 <, >, LIKE 구문 등 비교 연산 및 구문에 포함될 경우 인덱스 범위 스캔을 수행합니다
• 좁은 범위를 스캔할 때는 성능적으로 매우 효율적인 방식이지만 넓은 범위를 스캔할 때는 비효율적인 방식이라고 할 수 있습니다

인덱스 풀 스캔(index full scan)

• 말 그대로 인덱스를 처음부터 끝까지 수행하는 방식입니다
• 단, 테이블에 접근하지 않고 인덱스로 구성된 열 정보만 요구하는 SQL 문에서 인덱스 풀 스캔이 수행됩니다
• 인덱스는 테이블보다 상대적으로 적은 양을 차지하므로 테이블 풀 스캔 방식보다는 인덱스 풀 스캔 방식이 성능상 유리합니다
• 그러나 인덱스라는 오브젝트의 전 영역을 모두 검색하는 방식인 만큼 검색 범위를 최대한 줄이는 방향으로 SQL 튜닝을 해야 합니다

인덱스 고유 스캔(index unique scan)

• 기본 키나 고유 인덱스로 테이블에 접근하는 방식으로, 인덱스를 사용하느 스캔 방식 중 가장 효율적인 스캔 방법입니다
• Where 절에 = 조건으로 작성하며, 해당 조인 열이 키본 키 또는 고유 인덱스의 선두 열로 설정되었을 때 활용합니다

인덱스 루스 스캔(index loose scan)

• 인덱스의 필요한 부분들만 골라 스캔하는 방식입니다
• 인덱스 범위 스캔처럼 넓은 범위를 전부 접근하지 않고, Where 절 조건문 기준으로 필요한 데이터와 필요하지 않은 데이터를 구분한 뒤 불필요한 인덱스 키는 무시합니다
• 인덱스 루스 스캔은 보통 GROUP BY 구문이나 MAX(), MIN() 함수가 포함되면 작동합니다. 이미 오름차순으로 정렬된 인덱스에서 최댓값이나 최솟값이 필요한 경우가 이에 해당합니다

인덱스 병합 스캔(index merge scan)

• 테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식입니다
• Where 문 조건절의 열들이 서로 다른 인덱슬로 존재하면 옵티마이저가 해당하는 인덱스를 가져와서 모두 활용하는 방식을 취합니다
• 통합하는 방식으로는 결합(union)과 교차(intersection) 방식이 있으며 이들 방식은 모두 실행 계획으로 출렵됩니다
• 인덱스 병합 스캔은 물리적으로 존재하는 개별 인덱스를 각각 수행하므로 인덱스에 접근하는 시간이 몇 배로 걸립니다. 따라서 별개로 생성된 인덱스들은 보통 하나의 인덱스로 통합하여 SQL 튜닝을 수행하거나, SQL 문 자체를 독립된 하나의 인덱스만 수행하도록 변경할 수 있습니다

디스크 접근 방식

시퀀셜 액세스(Sequential Access)

• 물리적으로 인접한 페이지를 차례대로 읽는 순차 접근 방식으로, 보통 테이블 풀 스캔에서 활용합니다
• 데이터를 찾고자 이동하는 디스크 헤더의 움직임을 최소화하여 작업 시간과 리소스 점유 비용을 줄일 수 있습니다
• 테이블 풀 스캔일 때 인접한 페이지를 여러 개 읽는 다중 페이지 읽기 방식으로 수행합니다

랜덤 액세스(Random Access)

• 물리적으로 떨어진 페이지들에 임의로 접근하는 임의 접근 방식으로, 페이지가 위치한 물리적인 위치를 고려하지 않고 접근합니다
• 페이지에 접근하는 디스크 헤더가 정해진 순서 없이 이동하는 만큼 디스크의 물리적인 움직임이 필요하고 다중 페이지 읽기가 불가능하기 때문에, 데이터의 접근 수행 시간이 오래 걸립니다. 따라서 최소한의 페이지에 접근할 수 있도록 접근 범위를 줄이고 효율적인 인덱스를 활용할 수 있도록 튜닝해야 합니다

조건 유형

액세스 조건(Access condition)

• 디스크에 있는 데이터에 어떻게 접근할 것인지를 다루는 액세스 조건은 SQL 튜닝에서 가장 중요한 핵심 사항입니다
• Where 절 조건문으로 필요한 데이터만 골라 가져오는 방식은 이 테이블에 직접 접근할지, 인덱스를 어떻게 활용할 것인지에 관한 문제입니다
• 그러나 SQL 조건문이 복잡하고 다양한 만큼 실제 데이터를 가져오기 위해 활용하는 SQL 문의 조건절에는 한계가 있습니다. 따라서 옵티마이저는 Where 절의 특정 조건문을 이용해 소량의 데이터를 가져오고, 인덱스를 통해 시간 낭비를 줄이는 조건절을 선택하여, 스토리지 엔진의 데이터에 접근하고 MySQL 엔진으로 데이터를 가져옵니다

필터 조건(Filter condition)

• 액세스 조건으로 MySQL 엔진으로 가져온 데이터를 기준으로, 추가로 불필요한 데이터를 제거하거나 가공하는 조건입니다
• 만약 필터 조건에 따라 필터링할 데이터가 없다면 매우 훌륭한 SQL 문이고, 필터 조거으로 필터링되어 제거된 데이터가 다수 존재한다면 상대적으로 비효율적인 SQL 문일 것입니다
• 이에 필터 조건으로 제거되는 데이터 비율을 확인하고 특정 SQL문의 튜닝이 필요한지 판단할 수 있습니다