삶은 예배다.

ORDER BY 처리(Using filesort)

정렬을 처리하기 위해서는 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법

소트 버퍼: 정렬을 수행하기 위해서 할당 받는 메모리 공간. 정렬할 레코드의 크기에 따라 가변적으로 변동.

최대 사용 가능한 소트 버퍼 공간은 sort_buffer_size 변수

소트 버퍼보다 정렬할 데이터가 많으면 메모리에서 정렬한 내용을 디스크에 저장 – IO 발생

소트 버퍼는 세션 메모리 영역이다. 커넥션이 많거나 정렬 작업이 많으면 소트 버퍼를 위한 메모리 할당 증가

운영체제의 메모리 부족 현상 발생 -> 가장 많은 메모리를 사용하는 프로세스 종료 (OOM-Killer)

정렬 알고리즘: 레코드를 정렬할 때, 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준만 소트 버퍼에 담을지에

따라 싱글 패스 알고리즘과 투 패스 알고리즘으로 나눈다.

싱글 패스 알고리즘: 정렬 기준 칼럼을 포함해 SELECT되는 칼럼 전부를 담아서 정렬을 수행하는 방법

투 패스 알고리즘: 정렬 대상 칼럼과 프라이머리 키값만을 소트 버퍼에 담아서 정렬을 수행. 정렬된 순서대로

프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 알고리즘.

정렬 처리 방식

인덱스를 사용한 정렬: 반드시 OREDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY의 순서대로

생성된 인덱스가 있어야 한다.

- 드라이빙 테이블만 정렬: 조인을 실행하기 전에, 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행.

조인에서 첫 번째 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절이 작성돼야 한다.

드라이빙 테이블은 옵티마이저가 선택.

- 조인 결과를 임시 테이블로 저장한 후, 임시 테이블에서 정렬:

GROUP BY 처리

- 인덱스 스캔을 이용하는 GROUP BY:

- 루스 인덱스 스캔을 이용하는 GROUP BY: 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것을 의미.

인덱스를 반복적으로 읽으면서 GROUP BY 키 값을 지정하고 이에 해당하는 데이터 작업을 진행

- 임시 테이블을 사용하는 GROUP BY:

DISTICT 처리

- SELECT DISTICT: GROUP BY와 거의 같은 방식으로 처리. DISTICT는 정렬을 보장하지 못함.

- 집합 함수와 함께 사용된 DISTICT:

임시 테이블

일반적으로 MySQL엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로

옮겨진다. (CREATE TEMPORARY TABLE과 다르다.)

임시 테이블이 필요한 쿼리:

임시 테이블이 디스크에 생성되는 경우:

테이블 조인

현재 MySQL의 모든 버전에서 조인 방식은 nested-loop로 알려진 중첩된 루프와 같은 형태만 지원한다.

INNER JOIN은 어느 테이블을 먼저 읽어도 결과가 달라지지 않으므로 MySQL 옵티마이저가 조인의 순서를 조절해서

최적화를 수행. OUTER JOIN은 반드시 OUTER가 되는 테이블을 먼저 읽어야 하기 때문에 조인 순서를 옵티마이저가

선택할 수 없다.

INNER JOIN

OUTER 테이블을 먼저 읽고, INNER 테이블을 나중에 읽는다. (FOR, WHILE문이 2개)

OUTER 테이블을 드라이빙 테이블이라고 하고, INNER 테이블을 드리븐 테이블이라고 한다.

OUTER JOIN

INNER 테이블은 조인의 결과에 전혀 영향을 미치지 않고, OUTER 테이블의 내용에 따라 조인의 결과가 결정되는

것이 OUTER JOIN의 특징이다.

SELECT *

FROM employee e LEFT OUTER JOIN salary s  (employee -> 드라이빙 테이블)

ON s.emp_no = e.emp_no

-> LEFT OUTER JOIN을 사용.

LEFT OUTER JOIN 키워드의 왼쪽에 employee 테이블이 사용됐고 오른쪽에 salary 테이블이 사용

employee 테이블이 아우터가 된다.

조인의 결과는 salary 테이블의 레코드 존재 여부와 관계없이 employee 테이블의 레코드에 의해 결정.

SELECT *

FROM salary s RIGHT OUTER JOIN employee e  (employee -> 드라이빙 테이블)

ON e.emp_no = s.emp_no

-> RIGHT OUTER JOIN 키워드의 오른쪽에 employee 테이블이 사용됐고 왼쪽에 salary 테이블이 사용

employee 테이블이 아우터가 된다.

조인의 결과는 salary 테이블의 레코드 존재 여부와 관계없이 employee 테이블의 레코드에 의해 결정.

카테시안 조인

FULL JOIN 또는 CROSS JOIN이라고 한다.

2개 테이블의 모든 레코드의 조합을 결과로 가져오는 조인 방식.

NATURAL JOIN

SELECT * FROM employee e NATURAL JOIN salary s;

employee 테이블과 salary 테이블에 존재하는 칼럼 중에서 서로 이름이 같은 칼럼을 모두 조인 조건으로 사용한다.

조인 버퍼를 이용한 조인 (Using Join Buffer)

조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리한다.

만약, 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서

읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다.

이때 사용되는 메모리의 캐시를 조인 버퍼(join buffer)라고 한다.

일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만 조인 버퍼가 사용되는

조인에서는 결과의 정렬 순서가 흐트러질 수 있다.

조인 실행 결과의 정렬 순서

[전략] … 주로 조인이 인덱스를 이용해 처리되는 경우에는 이러한 예측을 할 수 있다.

하지만 결과가 이 순서로 반환된 것은 옵티마이저가 여러 가지 실행계획 중에서 위의 실행 계획을 선택했기 때문

이다. 만약 옵티마이저가 다른 실행 계획을 선택했다면 이러한 결과는 보장되지 않는다.

인덱스를 이용해 검색하고 조인하는 것이 당연할 것 같은 쿼리에서도 테이블의 레코드 건수가 매우 적거나 통계

정보가 잘못돼 있을 때는 다른 실행 계획을 선택할 수 있다.