Search

9장-1

•
MySQL 서버에서 옵티마이저는 각 테이블에 있는 데이터가 어떻게 분포되어있는지 등의 통계정보를 가지고 최적의 실행계획을 수립한다.

9.1 개요

9.1.1 쿼리 실행 절차

쿼리 실행 절차는 크게 세 단계로 나눌 수 있다.
1.
SQL 문장을 분석하고 파스 트리를 만든다.
•
SQL 문장의 문법적인 오류를 발견한다.
2.
만들어진 파스트리를 확인해서 테이블과 인덱스를 어떤 식으로 읽을지를 선택한다. (최적화 및 실행 계획 수립)
•
복잡한 연산을 단순화한다.
•
조인이 있을 경우 읽는 순서를 정한다.
•
인덱스 통계정보를 이용해 사용할 인덱스를 결정한다.
•
가져온 레코드를 임시테이블에 넣고 다시 가공할지를 결정한다.
3.
수립된 실행계획에 따라 스토리지 엔진에서 읽어오도록 요청한다.
•
MySQL 엔진에서 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.

9.1.2 옵티마이저의 종류

옵티마이저는 크게 두 가지로 나눌 수 있다.
•
규칙기반 최적화(RBO)
•
비용기반 최적화(CBO) : 통계정보 등을 사용하여 최소 비용을 산출
◦
요즘은 대부분의 RDBMS는 비용기반 최적화를 사용하고 있다.

9.2 기본 데이터 처리

•
RDBMS 마다 데이터를 가공하는 방법은 천차만별이다.
•
MySQL에서 데이터를 정렬, Grouping 등을 처리하는 방법을 설명한다.

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

•
풀 테이블 스캔 : 인덱스를 사용하지 않고 테이블을 처음부터 끝까지 읽어서 처리한다.
◦
다음의 상황에서 풀 테이블 스캔이 발생한다.
▪
테이블 레코드 건수가 너무 적어서 인덱스를 통해 읽는 것보다 테이블을 직접 읽는 것이 빠를 때
▪
인덱스를 사용할 적절한 Where, On 절 조건이 없을 때
▪
일치하는 데이터 건수가 너무 많을 때(통계 기준)
◦
풀 테이블 스캔 시 페이지 하나씩 읽는 것이 아니라 앞으로 필요할 것 같은 페이지를 미리 버퍼 풀에 읽어들인다. (리드 어헤드)
•
풀 인덱스 스캔 : 인덱스를 처음부터 끝까지 읽어서 처리한다.
select count(*)from employees;
SQL
복사
•
위의 쿼리는 풀 테이블 스캔을 할 것처럼 보이지만 레코드 건수만 필요로 한다면 용량이 작고 빠른 인덱스를 선택한다.
•
그러나 다음과 같이 인덱스에 없는 테이블 레코드가 필요한 쿼리는 풀 테이블 스캔을 하게된다.
select *from employees;
C#
복사

9.2.2 병렬 처리

•
MySQL 8.0 이후부터 하나의 쿼리에 대해 여러 스레드가 병렬 처리를 할 수 있게 되었다.
•
innodb_parallel_read_threads 라는 변수로 하나의 쿼리를 최대 몇개의 스레드가 처리할지 설정할 수 있다.

9.2.3 ORDER BY 처리(Using filesort)

•
정렬 처리 방법의 두 가지
1.
인덱스를 이용한 정렬
•
장점 : 인덱스는 이미 정렬되어 있어서 조회가 매우 빠르다
•
단점 : INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가, 삭제 작업이 필요해서 느리다.
2.
Filesort를 이용한 정렬
•
장점 : 인덱스를 생성하지 않아도 되므로 INSERT, UPDATE,DELETE가 상대적으로 빠르다.
•
단점 : 정렬 작업이 쿼리를 실행할 때 처리되어 느리다.
•
인덱스를 사용하지 못하는 경우
◦
정렬 기준이 너무 많아서 모두 인덱스를 생성할 수 없을 때
◦
GROUP BY의 결과 또는 DISTINCT 같은 처리 결과를 정렬할 때
◦
UNION의 결과 같이 임시 테이블의 결과를 다시 정렬할 때
◦
랜덤하게 결과 레코드를 가져올 때
•
실행계획을 살펴보면 Extra 컬럼에 Using filesort 메시지가 포함되면 Filesrot를 사용한지 알 수 있다.

9.2.3.1 소트버퍼

•
MySQL에서 정렬을 수행하기 위해 만들어둔 메모리 공간이다.
•
최대 사용 가능한 소트 버퍼의 크기는 sort_buffer_size 변수로 설정할 수 있다.
•
만약 정렬해야 할 레코드 수가 소트버퍼 크기보다 크다면 소트버퍼에서 부분 정렬을 수행하고 디스크에 쓰고 다시 해당 버퍼만큼 읽어서 정렬을 수행하고 병합하고 하는 과정을 반복해야 한다.
•
소트 버퍼의 크기가 너무 작으면 디스크 읽기 쓰기가 빈번해 질 것이다.
•
소트 버퍼의 크기가 너무 크면 동시 접속이 잦은 환경에서 운영체제는 메모리 부족 현상을 겪을 수 있다.
◦
운영체제의 OOM-Killer는 메모리가 부족하면 메모리를 가장 많이 사용하는 프로세스를 강제로 종료할 것이다.

9.2.3.2 정렬 알고리즘

•
레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담아서 정렬할지, 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 싱글 패스와 투 패스 정렬로 나눌 수 있다.
•
쿼리가 어떤 정렬 모드를 수행하는지는 다음과 같이 옵티마이저 트레이스 기능으로 확인할 수 있다.
select *from information_schema.optimizer_trace ... "sort_mode": "<fixed_sort_key, packed_additional_fields>"
C#
복사
•
정렬 모드는 3가지로 나뉘어진다.
◦
<sort_key, row_id> : 정렬 키와 레코드의 로우 아이디만 가져와서 정렬한다.
◦
<sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬한다. 레코드 칼럼은 고정 사이즈로 메모리에 저장한다.
◦
<sort_key, packed_additional_fields> : 정렬 키와 레코드 전체를 가져와서 정려한다. 레코드 칼럼은 가변 사이즈로 메모리에 저장한다.
•
첫 번째 방식은 투 패스 정렬방식이고 나머지 두 방식은 싱글 패스 방식이다.

9.2.3.2.1 싱글 패스 정렬방식

•
정렬에 필요하지 않은 칼럼까지 소트 버퍼에 담아서 정렬을 수행한다.

9.2.3.2.2 투 패스 정렬방식

•
정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행한다.
•
정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져온다.
•
차이점
◦
투패스 정렬방식은 테이블을 두 번 읽어야 하므로 상당히 비용이 많이 든다.
◦
싱글 패스는 한 번만 읽어도 된다. 하지만 소트 버퍼의 공간을 많이 사용한다.
•
최근에는 일반적으로 싱글 패스를 많이 쓰지만 다음의 경우 투 패스 정렬방식을 사용한다.
◦
레코드의 크기가 max_length_for_sort_data 값보다 클 때
◦
BLOB, TEXT 타입의 칼럼이 SELECT 대상에 포함 될 때

9.2.3.3 정렬 처리 방법

•
쿼리에 ORDER BY 문이 포함되면 다음의 세 가지 방법 중 하나로 처리된다.
◦
인덱스를 이용한 정렬 : 실행 계획의 Extra 컬럼 => 별도 표기 없음
◦
조인의 드라이빙 테이블만 정렬 => Using filesort
◦
조인 결과를 임시 테이블에 저장 후 정렬 => Using temporary; Using filesort
옵티마이저는 먼저 인덱스를 이용해서 정렬을 할 수 있을지 여부를 체크한다. 인덱스를 사용할 수 있으면 인덱스를 사용하고 그렇지 않으면 레코드를 정렬버퍼에 넣고 정렬을 수행한다. 이 때 MySQL 서버는 정렬 대상 레코드를 최소화하기 위해 다음 두 가지 방법 중 하나를 수행한다.
•
조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
•
조인이 끝나고 결과 레코드 모두를 가져와서 정렬을 수행

9.2.3.3.1 인덱스를 이용한 정렬

•
조건
◦
인덱스를 이용하려면 먼저 ORDER BY 에 명시된 컬럼이 제일 먼저 읽는 테이블(조인을 한다면)에 속해야 한다.
◦
또한, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
•
인덱스를 이용한 정렬은 이미 인덱스 자체가 정렬돼 있기 때문에 순서대로 읽기만 하면 된다. (추가 작업이 없음)
•
조인을 하더라도 네스티드 루프 방식으로 실행되기 때문에 조인으로 인해 순서가 흐트러지지 않는다.

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

•
조건
◦
인덱스를 이용하려면 먼저 ORDER BY 에 명시된 컬럼이 제일 먼저 읽는 테이블(조인을 한다면)에 속해야 한다.
•
일반적으로 조인이 수행되고 나면 레코드 수가 꽤나 증가한다. 따라서 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이다.
select * from employees e, salaries s where s.emp_no=e.emp_no and e.emp_nobetween 100002and 100010 orderby e.last_name;
SQL
복사

9.2.3.3.3 임시 테이블을 이용한 정렬

•
하나의 테이블만을 정렬하거나 9.2.3.3.2와 같이 2개 이상의 테이블을 조인하더라도 드라이빙 테이블만 정렬하여 처리할 수 있는 경우는 임시 테이블이 필요하지 않다.
•
하지만 그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고 그 결과를 다시 정렬하는 과정을 거친다.
◦
이 방법은 가장 느린 정렬 방법이다.
select * from employees e, salaries s where s.emp_no=e.emp_no and e.emp_nobetween 100002and 100010 orderby s.salary;
SQL
복사
•
쿼리의 실행 계획을 보면 Extra 칼럼에 Using temporary; Using filesort라는 코멘트가 남는다.
◦
이는 임시 테이블에 조인 결과를 저장하고 그 결과를 정렬했다는 의미이다.

9.2.3.3.4 정렬 처리 방법의 성능 비교

일반적으로 limit은 처리하는 양을 줄일 수 있기 때문에 서버가 처리할 작업량을 줄일 수 있다고 생각한다. 하지만 데이터를 처리하는 방식에 따라 limit으로 처리량이 줄 수도있고 줄지 않을 수도 있다. 다음 두 가지 방식을 살펴보자.
•
스트리밍 방식 : 서버 쪽에서 레코드가 검색될 때마다 바로바로 클라이언트로 결과를 전송해주는 방식이다. 이 방식에서는 limit 제한을 걸면 처리량을 줄이고 마지막 레코드를 가져오는 시간을 줄일 수 있다.
•
버퍼링 방식 : 데이터를 스캔할 때 order by나 group by를 걸면 스트리밍이 불가능하다. where 조건에 만족하는 모든 레코드를 가져와서 정렬하거나 그루핑해서 차례로 응답을 보내야한다. 즉 결과를 모아서 일괄 가공해야 하므로 limit과 같은 결과 건수를 제한하는 조건은 성능 향상에 도움이 되지 않는다.
•
order by 처리방식 중에서 인덱스를 이용한 정렬 방식만 스트리밍 형태의 처리이고 나머지는 모두 버퍼링 방식이다.

9.2.4 GROUP BY 처리

•
group by는 order by와 비슷하게 스트리밍 방식의 데이터 처리를 사용할 수 없게 만든다.
•
group by 작업은 다음과 같이 나눌 수 있다.
•
인덱스 이용
◦
인덱스 스캔 : 인덱스를 차례로 읽는 방식
◦
루스 인덱스 스캔 : 인덱스를 건너뛰면서 읽는 방식
•
인덱스를 이용 x : 임시 테이블 사용

9.2.4.1 인덱스 스캔을 이용하는 Group By(타이트 인덱스 스캔)

•
조건
◦
조인의 드라이빙 테이블에 속한 칼럼만을 이용해 그루핑
◦
group by의 순서로 인덱스가 있어야 함

9.2.4.2 루스 인덱스 스캔을 이용하는 Group by

•
인덱스 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 방식
•
루스 인덱스 스캔이 되는 지 조건을 판별하기가 어렵다.
•
다음의 예로 연습해보자. (col1, col2, col3의 순으로 인덱스)
select col1, col2from tb_testgroupby col1, col2; select distinct col1, col2from tb_test; select col1, min(col2)from tb_testgroupby col1; select col1, col2from tb_testwhere col1 <constgroupby col1, col2; selectmax(col3),min(col3), col1, col2from tb_testwhere col2 >constgroupby col1, col2; select col2from tb_testwhere col1 <constgroupby col1, col2; select col1, col2from tb_testwhere col3 =constgroupby col1, col2;
C#
복사

9.2.4.3 임시 테이블을 사용하는 Group By

•
인덱스를 전혀 사용하지 못할 때에는 임시테이블을 만든다.
•
group by 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 합수 연산을 수행한다.

9.2.5 DISTINCT 처리

•
distinct 처리는 크게 두 가지로 나뉠 수 있다.
•
집합함수(Min, Max, count 등)과 함께 사용되는 경우와 그렇지 않은 경우이다.

9.2.5.1 SELECT DISTINCT...

•
이 경우 group by와 동일한 방식으로 처리된다.
selectdistinct emp_nofrom salaries; select emp_nofrom salariesgroupby emp_no;
VB.Net
복사
•
distinct 키워드로 가장 흔하게 실수하는 것 중 하나는 distinct는 select 하는 모든 대상 컬럼에 대해 유니크하게 조회하는 것이다. 특정 칼럼만 유니크하게 조회하는 것이 아니다.
◦
괄호를 쳐도 의미없다!!!
selectdistinct(first_name), last_namefrom employees;
C#
복사

9.2.5.2 집합 함수와 함께 사용된 DISTINCT

•
하지만 집합함수와 함께 사용하는 distinct는 단일 칼럼의 유니크 값을 가져온다.
•
집합함수와 함께 사용할 때에는 항상 임시 테이블을 생성한다.
select count(distinct s.salary) from employees e, salaries s where e.emp_no = s.emp_no and e.emp_nobetween 10001and 100100;
SQL
복사
•
위의 쿼리를 실행했을 때 조인한 결과에서 salary를 저장하기 위한 임시테이블을 만든다.
•
이 테이블에서는 salary 칼럼은 유니크 인덱스가 생성된다.

9.2.6 내부 임시 테이블 활용

•
MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블(Internal temporary table)을 사용한다.
•
내부적인(internal)이라 표현하는 이유는 create temporary table 명령어로 만든 임시 테이블과는 다르기 때문이다.
•
내부적인 임시 테이블은 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다. 또한 다른 세션이나 쿼리에서는 볼 수 없고 쿼리가 완료되면 자동으로 삭제된다.

9.2.6.1 메모리 임시 테이블과 디스크 임시 테이블

•
메모리는 TempTable이라는 스토리지 엔진을 사용한다. (MySQL 8.0 이후부터)
◦
최대 사용 가능한 메모리 크기는 1GB이다. 1GB가 초과하는 경우 디스크로 기록하게 된다.
◦
이 때 두 가지 방식으로 저장할 수 있다.
1.
MMAP 파일로 기록 (디폴트, InnoDB 테이블로 전환하는 것 보다 오버헤드가 적음)
2.
InnoDB 테이블로 기록
•
디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용한다. (MySQL 8.0 이후부터)

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

•
다음과 같은 쿼리는 MySQL 엔진에서 별도의 데이터 가공이 필요하므로 내부 임시 테이블을 생성하는 대표적인 케이스이다.
◦
Order by와 group by에 명시된 칼럼이 다른 쿼리
◦
Order by와 group by에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
◦
distinct와 order by가 동시에 쿼리에 존재하는 경우 또는 distinct가 인덱스로 처리되지 못하는 쿼리
◦
union이나 union distinct가 사용된 쿼리(select_type이 union result인 경우)
◦
쿼리의 실행 계획에서 select_type이 derived인 쿼리
•
임시 테이블을 사용하는 지 여부는 실행 계획의 Extra 칼럼에 "Using temporary" 메시지가 표시되는지 확인하면 알 수 있다.

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

•
내부 임시 테이블은 기본적으로 메모리에 만들어진다.
•
하지만 다음의 조건을 만족하면 디스크 기반의 임시 테이블을 사용한다.
◦
Union이나 union all에서 select되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
◦
group by나 distinct 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
◦
메모리 임시 테이블의 크기가 temp_table_max_ram 시스템 변수 값보다 큰 경우

9.2.6.4 임시 테이블 관련 상태 변수

•
일반적으로 실행 계획에서 "Using temporary"가 표시된 것으로 임시 테이블이 사용되었다는 것을 알 수 있다.
•
하지만 임시 테이블을 하나 사용했는지 여러개 사용했는지, 메모리에 생성됐는지 디스크에 생성됐는지는 다음의 상태 변수로 확인할 수 있다.
•
show session status like 'Created_tmp%';
◦
Created_tmp_tables : 내부 임시 테이블의 개수를 누적하는 상태 값
◦
Created_tmp_disk_tables : 디스크에 내부 임시 테이블이 만들어진 개수를 누적하는 상태 값

9.3 고급 최적화

MySQL 옵티마이저는 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립한다. 여기서 옵티마이저 옵션은 크게 조인과 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분할 수 있다.

9.3.1 옵티마이저 스위치 옵션

•
옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어한다.
•
9.3.1절 하위의 옵션들을 on, off, default로 설정하여 사용할 수 있다.
•
9.3.1.1을 제외한 모든 설정 옵션은 기본값이 on이다.

9.3.1.1 MRR과 배치 키 액세스(mrr & batched_key_access)

•
MRR은 Multi Range Read를 줄여서 부르는 말이다. MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링한다. 즉, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링하는 것이다. 그리고 조인 버퍼에 레코드가 가득 차면 그때 한 번에 스토리지 엔진으로 요청한다. 이렇게 하면 읽어야 할 레코드 들을 데이터 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화 할 수 있다. 이러한 읽기 방식을 MRR이라 하고 이를 응용해서 실행하는 조인 방식을 BKA라 한다.

9.3.1.2 블록 네스티드 루프 조인(block_nedsted_loop)

•
MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인이다.
•
프로그래밍 언어에서 마치 중첩된 반복 명령을 사용하는 것처럼 작동한다고 해서 Nested Loop Join이다.
◦
블록 네스티드 루프 조인은 조인 버퍼가 사용되는 것과 드라이빙 테이블, 드리븐 테이블의 처리 순서에 따라서 구분된다.
•
MySQL 8.0.18 버전부터는 해시 조인 알고리즘이 도입되어 이 방식은 사용되지 않는다.

9.3.1.3 인덱스 컨디션 푸시다운(index_condition_pushdown)

Altertable employeesadd index ix_lastname_firstname (last_name, first_name); select *from employeeswhere last_name='Acton'and first_namelike '%sal';
SQL
복사
•
위의 Select 쿼리에서 index_condition_pushdown을 off 로 설정하면 last_name을 기준으로 인덱스를 타고 first_name like '%sal' 조건은 레코드에 직접 접근해서 검사를 한다.
•
그러나 index_condition_pushdown을 on으로 설정하면 index 내에서 비교를 수행하고 비교한 대상 레코드에만 접근해서 데이터를 가져올 수 있다.

9.3.1.4 인덱스 확장(use_index_extensions)

•
세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션이다.
•
InnoDB 스토리지 엔진에서 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키를 포함한다.
•
프라이머리 키가 a 이고 세컨더리 인덱스가 b 로 되어있으면 실제로 세컨더리 인덱스는 (b, a)로 작동한다.

9.3.1.5 인덱스 머지(index_merge)

•
인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리한다.
•
하나의 인덱스를 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 하나의 인덱스만 활용하는 것이 효율적이다. 하지만 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 것으로 예상 될 때 MySQL 서버는 인덱스 머지 실행 계획을 선택한다.
•
인덱스 머지는 다음의 3개의 세부 실행 계획으로 나누어 볼 수 있다.
◦
index_merge_intersection
◦
index_merge_union
◦
index_merge_sort_union

9.3.1.6 인덱스 머지 - 교집합(index_merge_intersection)

•
이 실행 계획은 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합을 반환한다.
•
실행계획의 Extra 칼럼에 Using intersect로 표시됨을 확인할 수 있다.
select *from employeeswhere first_name='George'and emp_nobetween 10000and 20000;
SQL
복사
•
위의 쿼리에서 두 조건에 대한 인덱스가 모두 존재하고 모두 상대적으로 많은 레코드를 가져와서 처리해야 한다면 인덱스를 각각 검색해서 교집합 하는 방식이 더 효율적일 수 있다. (물론 아닐 수도 있다.)

9.3.1.7 인덱스 머지 - 합집합(index_merge_union)

•
이 실행 계획은 여러 개의 인덱스를 각각 검색해서 그 결과의 합집합을 반환한다.
•
실행계획의 Extra 칼럼에 Using union로 표시됨을 확인할 수 있다.
select *from employeeswhere first_name='Matt'or hire_date='2022-07-13';
C#
복사
•
이 경우 인덱스 머지 방식을 사용할 때 중복을 제거해주어야 한다.
◦
각각의 인덱스로 조회 시에 emp_no(primary key)를 기준으로 정렬이 되어있으므로 인덱스 머지를 수행할 때 각 집합에서 하나씩 가져와 비교하며 죽복된 레코드를 걸러낼 수 있다.(우선순위 큐 알고리즘)

9.3.1.8 인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)

•
위의 Union 알고리즘에서 정렬된 결과로 중복제거를 하는데 정렬이 이미 되어있으므로 필요하지 않았다.
•
하지만 도중에 정렬이 필요한 경우에는 Sort union 알고리즘을 사용한다.

9.3.1.9 세미 조인

select * from employees e where e.emp_noin (select de.emp_nofrom dept_empwhere de.from_date= '1995-01-01' );
C#
복사
•
다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미 조인이라 한다.
◦
Table Pull-out
◦
Duplicate Weed-out
◦
First Match
◦
Loose Scan
◦
Materialization

9.3.1.10 테이블 풀-아웃(Table Pull-out)

•
세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화다.
◦
IN(subquery) 형태의 세미 조인이 가장 빈번하게 사용되는 형태의 쿼리
•
실행계획의 Extra 칼럼에 특별한 문구가 출력되지 않는다. 따라서, table pullout 최적화가 사용됐는지 확인하려면 Show warings 명령으로 MySQL 옵티마이저가 재작성한 쿼리를 살펴보면 된다.
◦
이때, IN(subquery) 형태는 사라지고 join으로 재작성된 것을 확인할 수 있다.

9.3.1.11 퍼스트 매치(firstmatch)

•
IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행된다.
•
extra 칼럼에 firstmatch() 문구가 출력된다.

9.3.1.12 루스 스캔(loosescan)

•
Loose Index Scan과 비슷한 읽기 방식을 사용한다.

9.3.1.13 구체화(Materialization)

•
Mataerialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다.
◦
여기서 구체화(Materialization)는 쉽게 말해 내부 임시 테이블을 만드는 것을 의미한다.
•
optimizer_switch 시스템 변수에서 semijoin, materialization 옵션이 모두 on인 경우에 활성화 된다.(defualt)

9.3.1.14 중복 제거(Duplicated Weed-out)

•
세미 조인 서브쿼리를 일반적인 Inner Join 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다.
select *from employees e where e.emp_noin (select s.emp_nofrom salaries swhere s.salary > 150000);
C#
복사
•
위의 쿼리는 다음과같이 바꿔서 실행된다.
select e.* from employees e, salaries s where e.emp_no = s.emp_noand s.salary>150000 groupby e.emp_no;
C#
복사

9.3.1.15 컨디션 팬아웃(condition_fanout_filter)

•
조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미친다. MySQL 옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.
•
실행계획에는 쿼리 실행시 읽게 될 rows의 갯수와 실행 결과 rows의 비율인 filtered 칼럼이 있다.
◦
rows * filtered / 100 이 쿼리 실행 결과 나오게 될 rows 수이다.
•
옵티마이저는 condition_fanout_filter 최적화 기능을 활성화하여 보다 정교한 계산을 할 수 있다.
◦
WHERE 조건절에 사용된 칼럼에 인덱스가 있는 경우
◦
WHERE 조건절에 사용된 칼럼에 히스토그램이 존재하는 경우

9.3.1.16 파생 테이블 머지(derived_merge)

•
MySQL 5.7버전부터는 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입됐다.
•
derived_merge 최적화 옵션을 통해 이러한 최적화를 활성화할지 여부를 결정한다.
select *from ( select *from employeeswhere first_name='Matt' ) dervied_table where derived_table.hire_date='2022-07-01'
C#
복사
•
위의 쿼리 실행 계획을 보면 from 절에 사용된 서브쿼리를 파생 테이블이라 부른다. 이러한 임시 테이블이 외부 쿼리로 병합된 경우 show warnings 명령으로 옵티마이저가 작성한 쿼리를 보자.
select *from employees where employees.hiredate = '2022-07-01'and employees.first_name = 'Matt'
C#
복사

9.3.1.17 인비저블 인덱스(use_invisible_indexes)

•
MySQL 8.0 버전부터는 인덱스의 가용 상태를 제어할 수 있는 기능이 추가됐다.
◦
이를 통해 인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어할 수 있다.
ALTERTABLE ...ALTER INDEX ... [ VISIBLE | INVISIBILE ]
SQL
복사

9.3.1.18 스킵 스캔(skip_scan)

•
인덱스의 핵심은 값이 정렬돼 있다는 것이며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요하다.
•
(A, B, C)로 구성된 인덱스가 있을 때 B와 C 칼럼에 대한 조건을 가지고 있다면 인덱스를 활용할 수 없다.
◦
인덱스 스킵 스캔은 제한적이긴 하지만 인덱스의 이러한 제약을 해소하는 최적화 기법이다.
ALTERTABLE EMPLOYEESADD INDEX IX_GENDER_BIRTHDATE (GENDER, BIRTH_DATE);
SQL
복사
위의 인덱스에서 이 경우 select * from employees where birth_date >= '1992-01-11'; 쿼리를 실행할 때 인덱스를 사용할 수 있을까?
•
MySQL 8.0 버전 부터는 인덱스 스킵 스캔 최적화가 도입되어 후행 칼럼만으로 인덱스를 이용한 쿼리 성능 개선이 가능하다.
◦
단, 선행 칼럼이 소수의 유니크한 값을 가질 때

9.3.1.19 해시 조인(hash_join)

•
많은 사용자들이 해시 조인 기능을 기대하는 이유가 기존의 네스티드 루프 조인보다 해시 조인이 빠르다고 생각해서이다. 하지만 항상 그렇지는 않다.
•
해시 조인 쿼리는 최고 스루풋 전략(첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데까지는 많이 걸리지 않는다.)에 적합하고, 네스티드 루프 조인(첫 번째 레코드를 찾는 것은 빠르지만 마지막 레코드를 찾는 데에 시간이 많이 걸린다.)은 최고 응답 속도 전략에 적합하다. 해시 조인 절차
•
빌드 단계 : 조인 대상 테이블 중에서 레코드 건수가 적은 테이블을 골라서 해시 테이블을 생성하는 작업 수행
•
프로브 단계 : 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정

9.3.1.20 인덱스 정렬 선호(prefer_ordering_index)

•
MySQL 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스를 사용해 처리 가능한 경우 쿼리의 실행 계획에서 이 인덱스의 가중치를 높이 설정해서 실행한다.
◦
옵티마이저의 이런 가중치 부여하지 않게 하기위해 prefer_ordering_index 옵션이 추가되었다. 이를 off 로 설정하면 된다.

9.3.2 조인 최적화 알고리즘

•
MySQL의 조인 쿼리의 실행 계획 최적화를 위한 알고리즘은 다음 두 가지이다.
•
아래의 예제쿼리에 대해 각 알고리즘이 어떻게 접근하는지 살펴보자
select * from t1, t2, t3, t4 where ...
Plain Text
복사

9.3.2.1 Exhaustive 검색 알고리즘

•
MySQL 5.0 이전에 사용되던 조인 최적화 기법이다.
•
FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법
•
테이블이 N개라면 N! 개의 조인 조합이 가능하다.

9.3.2.2 Greedy 검색 알고리즘

•
MySQL 5.0부터 도입된 조인 최적화 기법
•
절차
1.
전체 N개의테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
2.
1번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
3.
2번에서 선정된 실행 계획의 첫 테이블을 부분 실행 계획의 첫 번째 테이블로 선정
4.
전체 N-1개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
5.
4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 부분 실행 계획에 대입해 실행 비용을 계산
6.
5번의 비용 계산 결과 최적의 실행 계획에서 두 번째 테이블을 3번에서 새성된 부분 실행 계획의 두 번째 테이블로 선정
7.
남은 테이블이 모두 없어질 때까지 4~6번까지의 과정을 반복
8.
최종적으로 부분 실행 계획이 테이블의 조인 순서로 결정됨
•
시스템 변수
◦
optimizer_search_depth : Greedy 검색 알고리즘과 Exhaustive 검색 알고리즘 중에서 어떤 알고리즘을 사용할지 결정
◦
optimizer_prune_level : Heuristic 검색이 작동하는 방식 제어