[F-lab 챌린지 75일차 TIL]

성수데브리·2023년 9월 10일

f-lab_java

목록 보기

63/73

쿼리 처리 과정

https://stackoverflow.com/questions/10379956/parsing-sql-like-syntax-design-pattern

SQL Parser가 쿼리를 트리 구조로 파싱한다.
옵티마이저가 트리를 참고해 어떤 테이블부터, 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
1. 복잡한 조건 제거, 복잡한 연산 단순화
2. 조인있는 경우 어떤 순서로 테이블 읽을지 결정
3. 각 테이블에 사용된 조건과 통계 정보를 이용해 사용할 인덱스 결정
4. 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야하는지 결정
→ 실행 계획 만들어진다.
실행 계획대로 스토리지 엔진에서 데이터를 가져온다.
1. 실행 계획대로 스토리지 엔진에서 데이터를 가져오면 조인하거나 정렬하는 작업 수행

옵티마이저의 종류

비용 기반 최적화, Cost-based optimizer
규칙 기반 최적화, Rule-based optimizer

💡 비용기반 최적화가 왜 나왔는지

옵티마이저의 종류에는 비용 기반 최적화와 규칙 기반 최적화가 있다.

예전에는 규칙 기반 최적화가 많이 사용되었다. 규칙 기반 최적화는 옵티마이저의 우선순위에 따라 쿼리를 처리하기 때문에 같은 쿼리는 항상 같은 실행 계획에 의해 처리된다.

데이터의 종류가 다양해지면서 데이터 양과 분포도가 다양해지면서 상황에 맞는 최적화 방법이 필요해졌다. 비용 기반 최적화는 다양한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 통계 정보를 이용해 실행 계획별 비용을 산출한다. 이중에서 비용이 최소로 소요되는 처리 방식을 선택해 쿼리를 실행한다.

💡 그 비용을 어떻게 측정하는지

Chat GPT 답변

읽기 비용 (Access Cost): 테이블 또는 인덱스에서 읽어야 하는 행의 수를 고려합니다. 디스크 I/O 연산이 발생하는 경우, 디스크 읽기 비용을 고려합니다. 디스크 I/O 비용은 주로 테이블의 크기, 인덱스의 깊이 및 행의 분포와 관련이 있습니다.
정렬 비용 (Sorting Cost): 쿼리에서 ORDER BY 또는 GROUP BY 절을 사용하면 정렬 작업이 필요할 수 있습니다. 이때 정렬 비용이 계산됩니다. 정렬 비용은 정렬해야 하는 행 수와 정렬 알고리즘의 복잡성에 따라 달라집니다.
필터링 비용 (Filtering Cost): WHERE 절에서 사용되는 조건과 필터링 작업에 필요한 비용을 고려합니다. 이 비용은 조건의 복잡성과 필터링된 행 수에 영향을 받습니다.
조인 비용 (Join Cost): JOIN 연산의 경우, 다른 테이블 간에 조인할 때의 비용을 고려합니다. 이 비용은 조인 방법, 조인할 테이블의 크기, 인덱스 사용 여부 등에 따라 달라집니다.
인덱스 스캔 비용 (Index Scan Cost): 인덱스를 사용하여 데이터를 검색할 때 인덱스 스캔의 비용을 계산합니다. 이는 인덱스의 높이와 인덱스 블록에 액세스하는 데 필요한 비용을 고려합니다.
통계 정보 (Statistics): 옵티마이저는 테이블과 인덱스의 통계 정보를 사용하여 비용을 계산합니다. 통계 정보는 데이터 분포와 테이블의 구조에 대한 정보를 제공하며, 실행 계획을 결정하는 데 중요한 역할을 합니다.

기본 데이터 처리

정렬
Scan, 병렬 처리, Order by, Group by, Distinct

RDBMS에는 데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능을 가지고 있다.

풀 테이블 스캔

테이블 레코드 수가 너무 작아서 인덱스보다 풀 테이블 스캔이 빠른 경우
인덱스 레인지 스캔을 사용하더라도 선택도가 너무 낮아(=인덱스 컬럼에 중복값이 많아) 조건 일치 레코드 건수가 너무 많은 경우
WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우(?)
풀 테이블 스캔 최적화
- Read ahead : 페이지를 읽으면 다음 페이지를 예측해 미리 메모리에 로딩한다.
  
  초반에는 포그라운드 스레드가 페이지 읽기를 실행하지만 나머지 페이지는 백그라운드 스레드가 읽기를 넘겨 받고, 읽은 데이터를 버퍼 풀에 저장한다.
  포그라운드 스레드는 버퍼풀에 있는 데이터를 읽기만 하면 되므로 쿼리 처리가 빨라진다.
풀 인덱스 스캔 최적화

인덱스 테이블에서 처리
```
SELECT COUNT(*) FROM employees;
```
1,SIMPLE,employees,,index,,PRIMARY,4,,299468,100,Using index

인덱스 테이블에 없는 컬럼을 조회할때는 풀 테이블 스캔
```
SELECT * FROM employees;
```
1,SIMPLE,employees,,ALL,,,,,299468,100,

병렬 처리

여러 스레드가 하나의 쿼리를 작업을 나눠 동시에 처리하는 것
innodb_parallel_read_threads
조건 없이 전체 건수 가져오는 쿼리만 가능

Order By (Using filesort)

인덱스를 이용하는 방법
쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법
Explain : Extra 컬럼 Using filesort
인덱스 이용 장점
- 이미 정렬되어 있으므로 순서대로 읽기만 하면 된다.
인덱스 이용 단점
- INSERT, UPDATE, DELETE 비용 발생
- 인덱스 디스크 공간 필요 → 버퍼 풀을 위한 메모리도 같이 증가
filesort 장점
- 레코드 건수가 적으면 메모리에서 정렬하면 빠름
filesort 단점
- 인덱스 처럼 디스크에 미리 정렬해두는게 아니라 쿼리가 들어오면 그때 그때 정렬해야하므로 레코드 수가 많아지면 쿼리 응답 속도가 느리다.

Sort Buffer

메모리에서 정렬할 때 소트 버퍼라는 영역을 할당 받아서 처리한다.
- 세션 영역이라 클라이언트마다 독립적으로 할당되는 공간
- 많은 세션이 정렬 작업을 요청하면 OOM 발생할 수 있음. OOM killer 1순위는 DB
이 공간은 작업이 완료되면 바로 반납한다.
정렬해야할 레코드 총 크기가 소트 버퍼보다 크면?
- 나눠서 작업한다. 정렬된 일부 레코드들은 임시로 디스크에 저장하고 다음 레코드들을 불러와 작업한다. 이 작업 단위로 정렬된 레코드들을 병합하는 작업이 추가로 필요하다.
  - 디스크 쓰기와 읽기 유발한다 → 위 반복작업의 횟수가 많을수록 I/O 많아진다.
- 이것을 Multi-merge 라고 한다.
- 상태변수 sort_merge_passes 에 누적된다

정렬 알고리즘

Single-pass, Two-pass
MySQL 서버의 정렬 방식
1. <sort key, rowid>
  
  👉🏻 Two-pass : 정렬에 필요한 컬럼만 가져와 정렬하고 다시 메모리에서 나머지 컬럼들을 조회해오는 방식 / 작업에 두 단계가 필요하다.
2. <sort key, additional_fields> : 레코드 컬럼들 고정 사이즈로 메모리에 저장
3. <sort_key, packed_additional_fields> : 레코드 컬럼들 가변 사이즈로 메모리에 저장
  
  👉🏻 Single-pass / 아예 들고와서 정렬 후 바로 결과 리턴 / 한 단계만 필요하다.
  
  → Trade-off : 소트 버퍼 공간이 더 필요하다 / 적은 레코드일 경우 빠름
two-pass 정렬 사용하는 경우
- max_length_for_sort_data < 레코드 크기
- SELECT 대상에 BLOB, TEXT 타입 있는 경우

정렬 처리 방법

밑으로 갈수록 처리 속도는 떨어진다.

인덱스	-
조인에서 드라이빙 테이블만 정렬	using filesort
조인 결과를 임시 테이블로 저장 후 정렬	using temparary; using filesort

인덱스 이용한 정렬

🔺 조건

ORDER BY 에 명시된 컬럼이 드라이빙 테이블에 속할 것
ORDER BY 순서대로 생성된 인덱스가 있어야 할 것
WHERE col, ORDER BY col 두 컬럼이 같은 인덱스를 사용할 수 있어야 한다.
해시 인덱스 X, 전문 검색 인덱스 X

네스티드 루프 방식으로 처리 : 드라이빙 테이블에서 인덱스 정렬 순서대로 레코드 한 건씩 드리븐 테이블 찾아가 조인
조인 버퍼가 사용되면 순서 흐트러질 수 있기 때문에 주의

조인의 드라이빙 테이블만 정렬

첫 번째 테이블을 정렬한 다음 조인을 실행