[MYSQL] Real MySQL 9장 - 옵티마이저

MySQL 서버로 요청된 쿼리는 결과는 동일하지만, 내부적으로 그 결과를 만들어내는 방법은 매우 다양하다.

이런 다양한 방법 중에서 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야한다.

 

MySQL에서도 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다.

MySQL 서버를 포함한 대부분의 DBMS에서는 옵티마이저가 이러한 기능을 담당한다.

 

MySQL에서는 EXPLAIN이라는 명령으로 쿼리의 실행 계획을 확인할 수 있는데,

EXPLAIN 명령의 결과에는 상당히 많은 정보가 출력된다, 이러한 내용들을 제대로 이해하려면 옵티마이저에 대해 지식을 갖추고 있어야 한다.

 

---

스트리밍 방식

- 서버쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때 마다 바로바로 클라이언트로 전송해 주는 방식을 의미한다

- 이 방식으로 쿼리를 처리할 경우 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번쨰 레코드를 전달 받는다

- 물론 가장 마지막 레코드는 언제 받을지 알 수 없지만, 이는 그다지 중요하지 않다. (어차피 실시간으로 받는게 중요하기 때문에?)

- 쿼리가 스트리밍 방식으로 처리될 수 있다면 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있다.

- 웹서비스 같은 환경에서는 쿼리의 요청에서부터 첫 번째 레코드를 전달받게 되기까지의 응답시간이 중요하다.

- 스트리밍 방식으로 처리되는 쿼리는 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장해준다.

* 스트리밍 방식으로 처리되는 쿼리에서 LIMIT 처럼 결과 건수를 제한하는 조건들은 쿼리으 ㅣ전체 실행 시간을 상당히 줄여줄 수 있다

* 서버에서 쿼리가 아직 실행되고 있는 도중이라도 풀 테이블 스캔의 ㄱ ㅕㄹ과가 아무런 버퍼링 처리나 필터링 과정 없이 바로 클라이언트로 스트리밍 되기 때문에 첫번째 레코드를 아주 빨리 가져올 수 있다

 

버퍼링 방식

- ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다

- WHERE 조건에 일치하는 모든 레코드를 사져온 후 , 정렬하거나 그루핑해서 차례대로 보내야 하기 때문이다

- MySQL서버에서는 코든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답속도가 느려진다.

- 그래서 이 방식을 스트리밍의 반대표현으로 버퍼링이라고 표현한다.

- 버퍼링 방식으로 처리되는 쿼리는 먼저 결과를 모아서 MySQL서버에서 일괄 가공해야 하므로, 모든 결과를  스토리지 엔진으로 부터 가져올 때 까지 기다려야 한다.

- 그래서 버퍼일 방식으로 처리되는 쿼리는 LIMIT 처럼 결과 건수를 제한 하는 조건이 있어도 성능 향상에 별로 도움이 되지 X, 레코드 건수만 줄일 뿐 MySQL서버가 해야하는 작업량에는 그다지 변화가 없다

 

- 어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만, 어떤 정렬 방식으로 처리되는 지는 더 큰 성능 차이를 만든다. 따라서 가능하다면 인덱스를 사용한 정렬로 유도하고, 그렇지 못하다면 최소한 드리이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝 방법이라고 할 수 있다

 

정렬 관련 상태 변수

- MySQL 서버는 처리하는 주요 작업에 대해서는 해당 작업의 실행횟수를 상태변수로 저장한다.

- 해당 값들을 이용해 지금까지 MySQL 서버가 처리한 정렬 작업의 내용을 어느정도 이해할 수 있다

1) Sort_merge_passes : 멀티 머지 처리 횟수

2) Sort_range : 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수다

3) Sort_scan : 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수다.

4) Sort_rows : 지금까지 정렬한 전체 레코드 건수를 의미한다

GROUP BY 처리

- Group by 또한 Order By과 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나다.

- Group by에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.

- Group by 작업

1) 인덱스를 차례대로 읽는 인덱스 스캔 방법

2) 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔 방법

3) 인덱스를 사용하지 못하는 쿼리에서 Group by 작업은 임시 테이블을 사용한다.

 

인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)

- ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 grouping 할 때, GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다.

- GROUP BY가 인덱스를 사용해서 처리된다 하더라도, 그룹 함수 등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때 도 있음

- GROUP BY가 인덱스를 통해 처리되는 쿼리는 이미 정렬된 인덱스를 읽는 것 이므로 쿼리 실행 시점에 추가적인 정렬작업이나 내부 임시 테이블은 필요하지 않다.

- 이러한 경우 Extra 칼럼에 별도로 Group by 관련 코멘트나 임시 테이블 사용 또는 정렬 관련 코멘트가 표시되지 않는다.

루스 인덱스 스캔을 이용하는 GROUP BY

- 루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져온느 것을 의미하는데, 옵티마이저가 루스 인덱스 스캔을 사용할 때는 실행계획의 Extra 칼럼에 "Using index for group-by" 코멘트가 표시된다. 

1. (emp_no,from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫번째 유일한 값 "10001"을 찾아낸다

2. (emp_no,from_date) 인덱스에서 emp_no가 '10001' 인 것 중에서 from_date 값이 '1985-03-01'인 레코드만 가져온다.

=> 이 검색 방법은 1번 단계에서 알아낸 '10001' 값과 쿼리의 WHERE 절에 사용된 from_date='1985-03-01' 조건을 합쳐서 emp_no = 10001 AND from_date='1985-03-01' 조건으로 (emp_no,from_date) 인덱스를 검색하는 것과 거의 흡사하다

3. (emp_no, from_date) 인덱스에서 emp_no의 그 다음 유니크한 값을 가져온다.

4. 3번 단계에서 결과가 더 없으면 처리를 종료하고, 결과가 있다면 2번 과정으로 돌아가서 반복 수행한다.

 

- MySQL 루스 인덱스 스캔방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다.

- 프리픽스 인덱스(칼럼의 앞쪽 일부만으로 생성된 인덱스)는 루스 인덱스 스캔을 사용할 수 없다

- 인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면, 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다. 즉, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어낸다. (띄엄띄엄 찾아서하니까?)

- 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않다

 

- 루스 인덱스 스캔이 사용될 수 있을지 없을지 판단하는 것은 WHERE 절의 조건이나 ORDER BY 절이 인덱스를 사용할 수 있을지 없을지 판단하는것보다 더어렵기 때문에 여러 패턴의 쿼리를 살펴보고, 루스 인덱스 스캔을 사용할 수 있는지 없는지 판별하는 연습을 해야한다.

 

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

- GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다. 

- MySQL 8.0 에서는 GROUP BY가 필요한 경우 내부적으로 GROUP BY 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다.

 

DISTINCT 처리

- 특정 칼럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다.

- 집함 함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요하다. 하지만 실행 계획의 Extra 칼럼에는 "Using temporary" 메시지가 출력되지 않는다.

 

SELECT DISTINCT

- 단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다.

- 이 경우에는 GROUP BY와 동일한 방식으로 처리된다.

- 특히 MySQL 8.0 버전부터는 GROUP BY를 수행하는 쿼리에 ORDER BY절이 없으면 정렬을 사용하지 않기 때문에 내부적으로 같은 작업을 수행한다고 볼 수 있다.

- *중요* DISTINCT는 SELECT하는 레코드(튜플)을 유니크하게 SELECT하는 것이지 ,특정 칼럼만 유니크하게 조회하는 것이 아니다.

SELECT DISTINCT(first_name), last_name FROM employees; 라는 쿼리는 얼핏보면

first_name을 유니크하게 조회하고 last_name은 그냥 동일하게 뽑아오는 것 처럼 보이지만,

MySQL서버는 DISTINCT 뒤의 괄호를 그냥 의미 없이 사용된 괄호로 해석하고 제거해버린다.

* SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미친다. BUT!! 집함 함수와 함께 사용된 DISTINCT의 경우는 조금 다르다

 

집함 함수와 함께 사용된 DISTINCT

- COUNT() 또는 MIN(), MAX() 같은 집함 함수 내에서 DISTINCT 키워드가 사용될 수 있는데,

이 경우에는 일반적으로 SELECT DISTINCT와 다른 형태로 해석된다.

- 집함 함수가 없는 SELECT 쿼리에서 DISTINCT는 조회하는 모든 컬럼의 조합이 유니크한 것들만 가져온다.

 

내부 임시 테이블 활용

- MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나, 그루핑할 때는 내부적인 임시 테이블을 사용한다.

- 내부적이라는 단어가 포함된 이유는 여기서 이야기하는 임시 테이블은 CREATE TEMPORARY TABLE 명령으로 만든 임시테이블과는 다르기 때문이다.

- 일반적으로 MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다. (물론 특정 예외 케이스에는 메모리를 거치지 않고 바로 디스크에 임시 테이블이 만들어지기도 한다)

 

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

- MySQL 8.0 이전 버전까지는 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용한다. 

하지만 MySQL 8.0 버전부터는 메모리는 TempTable 이라는 스토리지 엔진을 사용하고, 디스크에 저장되는 임시테이블은 InnoDB 스토리지 엔진을 사용하도록 개선됐다.

- 기존 Memory 스토리지 엔진은 VARMINARY나 VARCHAR 같은 가변 길이 타입을 지원하지 못하기 때문에 임시 에티블이 메모리에 만들어지면 가변 길이 타입의 경우 최대 길이만큼 메모리를 할당해서 사용했다.

=> 이는 메모리 낭비가 심해지는 문제점을 안고 있었다. + 디스크에 임시 테이블이 만들어질 때 사용되는 MyISAM 스토리지 엔진은 트랜잭션을 지원하지 못한다는 문제점을 안고 있었다.

그래서 MySQL 8.0 버전부터는 MEMORY 스토리지 엔진 대신 가변 길이 타입을 지원하는 TempTable 스토리지 엔진이 도입됐으며, MyISAM 스토리지 엔진을 대신해서 트랜잭션 지원 간으한 InnoDB 스토리지 엔진이 사용되도록 개선된 것이다.

 

임시 테이블이 필요한 쿼리

- 다음과 같은 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 하므로, 대표적으로 내부 임시 테이블을 생성하는 케이스다. 물론, 이 밖에도 인덱스를 사용하지 못할 때는 내부 임시 테이블을 생성해야 할 때가 많다.

- 어떤 쿼리의 실행 계획에서 임시 테이블을 사용하는지는 Extra 칼럼에 "Using temorary" 라는 메시지가 표시되는지 확인하면 된다. 하지만 "Using temporaray"가 표시되지 않을 때도 임시 테이블을 사용할 수 있는데, 위의 예에서 마지막 3개 패턴이 그러한 예다.

- 1~4번은 유니크 인덱스를 가지는 내부 임시 테이블이 만들어지고, 마지막 쿼리 패턴은 유니크 인덱스가 없는 내부 임시 테이블이 생성된다.

* 일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 그렇지 않은 쿼리보다 처리 성능이 상당히 느리다.

 

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

- 내부 임시 테이블은 기본적으로는 메모리상에 만들어지지만, 다믕과 같은 조건을 만족하면 메모리 임시 테이블을 사용할 수 없게 된다. 그래서 이 경우에는 디스크 기반의 임시 테이블을 사용한다.

=> UNION 이나 UNION ALL 에서 SELECT 되는 칼럼 중에서 길이가 512 바이트 이상인 크기의 칼럼이 있는 경우 등등,,

(317p)

 

임시 테이블 관련 상태 변수 

- 실행 계획 상에서 "Using temporary"가 표시되면 임시테이블을 사용했다는 ㅅ ㅏ실을 알 수 있다.

- 하지만 임시 테이블이 메모리에서 처리됐는지 디스크에서 처리됐는지는 알 수 없으며, 몇 개의 임시테이블이 사용됐는지도 알 수 없다. ("Using temporary"가 한번 표시됐다고해서 임시테이블을 하나만 사용했다는 것을 의미하지는 않는다.)

- 임시 테이블이 디스크에 생성됐는지 메모리에 생성됐는지 확인하려면, MySQL 서버의 상태변수를 확인해 보면 된다.

 

고급 최적화

- MySQL 서버의 옵티마이저가 실행 계획을 수립할 때 통계정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립하게 된다.

옵티마이저 옵션은 크게 조인 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분할 수 있다.

 

옵티마이저 스위치 옵션

- optimizer_match시스템 변수에 설정할 수 있는 옵션은,,~ (책에 설명 318p) => 이미지 사진 확인

 

---

인덱스 머지 - 합집합

- 인덱스 머지의 'Usiing union'은 Where 절에 사용된 2개이상의 조건이 각각 인덱스를 사용하되, OR 연산자로 연결된 경우에 사용되는 최적화다.

- Union 알고리즘의 숨은 비밀은, first_name='Matt' 이면서 hire_date='1987-03-31'인 사원이 있었다면, 그 사원의 정보는 ix_firstname인덱스를 검색한 결과에도 포함 돼 있을 것이며, ix_hiredate 인덱스를 검색한 결과에도 포함되어 있을 것이다.

그러나 이 쿼리의 결과에서는 그 사원의 정보가 두번 출력되지는 않는다.

- 그렇다면 MySQL 서버는 두 결과 집합을 정렬해서 중복 레코드를 제거했다는 뜻인데, 두 결과 집합에서 중복을 제거하기 위해서는 정렬작업이 필요했을 것이다. 하지만 실제 실행계획에는 정렬했다는 표시가 없는데,, 왜 그런거냐면

인덱스 머지 최적화의 'Union' 알고리즘의 작동 방식을 그림으로 표현한 것인데, fist_name 컬럼검색 결과와 hire_date 칼럼검색 결과는 모두 프라이머리 키로 이미 각각 정렬돼 있다는 것을 알고있어서 MySQL 서버는 두 집합에서 하나씩 가져와서 서로 비교하면서 프라이머리 키인 emp_no 칼럼의 값이 중복된 레코드들을 정렬없이 걸러낼 수 있다.

=> 이렇게 정렬된 두 집합의 결과를 하나씩 가져와 중복 제거를 수행할 때 사용된 알고리즘이 우선순위 큐라고 한다.

 

인덱스 머지 - 정렬 후 합집합

- 만약 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 MySQL 서버는 인덱스 머지 최적화의 'Sort Union' 알고리즘을 사용한다.

- 인덱스 머지 최적화에서 중복제거를 위해 강제로 정렬을 수행해야하는 경우, 실행 계획의 Extra 칼럼에 "Using sort_union" 문구가 표시된다.

 

세미 조인

- 다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미조인이라고 한다.

- MySQL 5.7 서버는 전통적으로 세미조인형태의 쿼리를 최적화하는 부분이 상당히 취약했으나, 

---

370P

쿼리 힌트

- MySQL 서버는 우리가 서비스하는 비즈니스를 100% 이해하지는 못하기 때문에 서비스 개발자나 DBA보다 MySQL 서버가 부족한 실행 계혹을 수립할 때가 있을 수 있는데, 이때에는 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법이 필요하다.

- 일반적인 RDBMS에서는 이런 목적으로 힌트가 제고되며, MySQL에서도 다양한 옵티마이저 힌트를 제공한다.

 

=> MySQL 서버에서 사용가능한 쿼리 힌트로는 1)인덱스 힌트 2) 옵티마이저 힌트 2가지로 구분할 수 있다

 

인덱스 힌트

-  USE_INDEX나 STRAIGHT_JOIN 등을 포함한 인덱스 힌트들은 이전 버전의 MySQL서버에서 옵티마이저 힌트가 도입되기 전에 사용되던 기능들이다

- 이들은 모두 SQL 문법에 맞게 사용해야 하기 때문에 사용하게 되면 ANSI_-SQL  표준 문법을 준수하지 못하게 되는 단점이 있다

- MySQL 5.6부터 생긴 옵티마이저 힌트들은 모두 MySQL 서버를 제외한 다른 RDBMS에서는 주석으로 해석하기 때문에 ANSI-SQL 표준을 준수한다고 볼 수 있다. 그래서 가능하다면 인덱스 힌트보다는 옵티마이저 힌트를 사용하는 것이 좋다

* 또한 인덱스 힌트는 SELECT 명령과 UPDATE 명령에서만 쓸 수 있다

 

STRAIGHT_JOIN

- STRAIGHT_JOIN은 옵티마이저 힌트인 동시에 조인 키워드 이기도 하다

- STRAIGHT_JOIN은 SELECT,UPDATE,DELETE 쿼리에서 여러 개의 테이블이 조인ㄷ ㅚ는 경우 조인 순서를 고정하는 역할을 한다.

- 옵티마이저가 그때그때 각 테이블의 통계정보와 쿼리의 조건을 기반으로 가장 최적이라고 판단되는 순서로 조인한다.

- 이 쿼리의 실행계호기을 확인해보면 departments 테이블을 드라이빙 테이블로 선택했고, 두번째로 dept_em 테이블을 읽은 뒤에 employees 테이블이 마지막으로 읽었음을 확인할 수 있다.

- 일반적으로 조인을 하기 위한 칼럼들의 인덱스 여부로 조인의 순서가 결정되며, 조인 칼럼의 인덱스에 아무런 문제가 없는 경우에는 레코드가 적은 테이블을 드라이빙으로 선택한다.

- 이 쿼리의 경우에는 departments 테이블이 레코드 건수가 가장 적어서 드라이빙으로 선택됐을 것으로 보인다.

- 하지만 이 쿼리의 조인 순서를 변경하려는 경우에는 STRAIGHT_JOIN 힌트를 사용할 수 있다!

- 두 쿼리는 형태만 조금 다를 뿐 같은 쿼리다. (두 예제 모두 STRAIHT_JOIN 키워드가 SELECT 키워드 바로 뒤에 사용됐다는 것에 주의하자)

-  STRAGIHT_JOIN 힌트는 옵티마이저가 FROM 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도한다.

 

주로 다음 기준에 맞게 조인 순서가 결정되지 않는 경우에만 STRAIGHT_JOIN 힌트로 조인 순서를 조정하는 것이 좋다.

1) 임시테이블과 일반 테이블의 조인 - 이 경우는 거의 일반적으로 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋다, 일반 테이블의 조인 칼럼에 인덱스가 없는 경우에는 레코드 건수ㅏ 작은 쪽을 먼저 읽도로 드라이빙으로 선택하는 것이 좋은데, 대부분 옵티마이저가 적절한 조인 순서를 선택하기 때문에 쿼리를 작성할 때 부터 힌트를 사용할 필요는 없다. 옵티마이저가 실행계획을 제대로 수립하지 못해서 심각한 성능 저하가 있는 경우에는 힌트를 사용하면 된다.

2) 임시 테이블끼리 조인 - 임시테이블은 항상 인덱스가 없기 때문에 어느 테이블을 먼저 드라이빙으로 읽어도 무관하므로 크기가 작은 테이블을 드라이빙으로 선택해주는 것이 좋다.

3) 일반 테이블끼리 조인 - 양쪽 테이블 모두 조인 칼럼에 인덱스가 있거나 양쪽 테이블 모두 조인 칼럼에 인덱스가 없는 경우에는 레코드 건수가 적은 테이블을 드라이빙으로 선택해주는 ㄴ것이 좋으며, 그 이외의 경우에는 조인 칼럼에 인덱스가 없는 테이블을 드라이빙으로 선택하는 것이 좋다.

 

392p