MySQL(14)

이번 주 MySQL

9.3.1.12 루스 스캔, 세미조인 최적화 전략 3

세미조인 서브쿼리 최적화의 루스 스캔은 인덱스를 사용하는 GROUP BY 최적화 방법에서 본 Using index for group-by 의 루스 인덱스 스캔과 비슷한 읽기 방식을 사용한다.

SELECT * FROM departments d 
WHERE d.dept_no IN(SELECT de.dept_no FROM dept_emp de);

이때 departments 테이블의 레코드 건수는 9건이지만, dept_emp 테이블 레코드는 33만건이다.

그런데 dept_emp테이블에선 (dept_no + emp_no)칼럼의 조합으로 PK인덱스가 만들어져있다.그리고 이 PK는 전체 레코드 수는 33만건이지만, dept_no만으로 그루핑 해보면 9건밖에 없다는 것을 알 수 있다.

그렇다면, demp_emp테이블의 PK를 루스 인덱스 스캔으로 유니크한 dept_no만 읽으면 아주 효율적으로 서브쿼리 실행이 가능하다. 그것도 중복된 레코드까지 제거하면서.

서브쿼리에 사용된 dept_emp테이블이 드라이빙 테이블로 실행되며, dept_emp 테이블의 PK를 dept_no부분에서 유니크하게 한 건만 읽고있다.

루스 인덱스 스캔의 Using index for group-by 도 그림의 dept_emp 의 PK를 읽는 방식과 동일하게 작동한다.

또 실행계획을 보면 id칼럼의 값이 1인걸보면 내부적으로는 조인처럼 처리됐다는것을 말해준다.

LooseScan 최적화의 특징은 다음과 같다.

• LooseScan최적화는 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 그 다음 아우터 테이블을 드리븐으로 사용해서 조인을 수행한다. 그래서 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있는 최적화다. 루스 인덱스 스캔은 이런 형태의 서브쿼리에서 사용할 수 있다.

SELECT ... FROM ... WHERE expr IN (SELECT keypart1 FROM tab WHERE ...)
SELECT ... FROM ... WHERE expr IN (SELECT keypart2 FROM tab WHERE keypart1=constant ..)

9.3.1.13 구체화, 세미조인 최적화 전략 4 질문 2 있음-

Materialization 최적화는 세미조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화 한다는 의미다.

구체화 = 내부 임시 테이블을 생성한다는 것을 의미한다.

구체화가 의미하는것

내부 임시테이블을 생성한다는 것

SELECT *
FROM employees e
WHERE e.emp_no IN
    (SELECT de.emp_no FROM dept_emp de
     WHERE de.from_date='1995-01-01');

이 쿼리는 FM 최적화를 쓰면 employees 테이블에 대한 조건이 서브쿼리 이외에는 아무것도 없기 때문에 employees 테이블을 풀스캔 해야할 것이다. (FM에선 서브쿼리 이외에도 e.name=const 조건이 있었음) 그래서 이런 형태의 쿼리에서는 FM 최적화가 도움이 안된다.

옵티마이저는 이런 형태의 쿼리를 위해 구체화 최적화를 도입했다.

실행계획을 보면 라인이 3개인데, 2개테이블임에도 3개인걸 보면 임시테이블에 생성되었다는 것을 알 수 있다.

dept_emp 테이블을 읽는 서브쿼리가 먼저 실행되어 그 결과로 임시테이블이 만들어진다. 최종적으로 구체화된 임시테이블이 employees와 조인해서 결과를 반환한다.

구체화는 다른 서브쿼리 최적화와는 달리, 다음과 같이 서브쿼리 내에 Group BY절이 있어도 사용가능하다.

SELECT *
FROM employees e
WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de
                   WHERE de.from_date = '1995-01-01'
                   GROUP BY de.dept_no);

구체화가 사용될 수 있는 형태의 쿼리에도 제한사항과 특성이 있다.

1. IN(subquery)에서 서브쿼리는 상관 서브쿼리(correlated subquery)가 아니어야 한다.
2. 서브쿼리는 GROUP BY나 집합함수들이 사용돼도 구체화를 사용할 수 있다.
3. 구체화가 사용된 경우에는 내부임시테이블이 사용된다.

구체화 옵션은 기본적으로 on이다.

세미조인이 아닌 서브쿼리의 최적화에서도 구체화가 사용될 수 있다. 

9.3.1.14 중복 제거, 세미조인 최적화 전략 5 - 질문 3, 4 있음

Duplicate Weedout은 세미조인 서브쿼리를 일반적인 Inner Join쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다.

SELECT * FROM employeess e
WHERE e.emp_no IN (SELECT s.emp_no FROM salaries s WHERE s.salary>150000);

salaries 테이블의 PK가 (emp_no + from_date)이므로 salary가 150000 이상인 레코드를 salaries 테이블에서 조회하면 그 결과에는 중복된 emp_no가 발생할 수 있다. 그래서 이 쿼리를 재작성해서 GROUP BY를 넣어주면 위의 세미조인 서브쿼리와 동일한 결과를 얻을 수 있다.

SELECT e.*
FROM employees e, salaries s
WHERE e.epm_no=s.emp_no AND s.salary>150000
GROUP BY e.emp_no;

실제로 이 DW는 원본 쿼리를 위와같이 inner join + GROUP BY 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리를 처리한다.

질문, 원본 쿼리를 실행할때 DW로 최적화하면 어떤 쿼리와 비슷해지나?

아래쿼리.

실행계획을 보면 id가 둘 다 1이다. = 조인이 실행됐다는 의미.

DW최적화는 다음과 같은 장점, 제약사항이 있다.

1. 서브쿼리가 상관 서브쿼리(correlated subquery)여도 사용할 수 있다.
2. 서브쿼리가 GROUP BY나 집합 함수가 사용된 경우에는 사용할 수 없다.
3. DW는 서브쿼리의 테이블을 조인으로 처리하기 때문에 최적화 할수 있는 방법이 많다.

상관 서브쿼리가 무엇인가.

바깥쪽 쿼리와 서로 관련을 맺고있는 서브쿼리.서브쿼리가 메인쿼리 값을 참조하는 쿼리형태.

9.3.1.15 컨디션 팬아웃 - 질문 5.

조인할때 테이블 순서가 성능에 큰 영향을 준다.

A, B를 조인할때 A가 조건 일치가 1만건, B가 10건이라면 A-B면 B를 1만번 읽어야 한다. 그래서 옵티마이저는 여러 테이블이 조인되는 경우 레코드 건수가 적은 순서대로 조인을 실행한다.

SELECT *
FROM employees e
    INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE e.first_name="matt"
  AND e.hire_date BETWEEN '1985-11-21' AND '1986-11-21'

실행계획은 대략 다음과 같다.

실행계획에 의하면 다음과 같이 처리된다.

1. employees 테이블에서 ix_firstname인덱스를 이용하여 first_name=’matt’ 조건에 일치하는 233건의 레코드를 검색한다.

2. 233건중에서 hire_date가 between인걸 걸러내는데, filtered 칼럼이 100인것은 옵티마이저가 233건 모두 between 사이일 것으로 예측했다는 의미다.

3. employees를 읽은 결과 233건에 대해 salaries 테이블의 PK를 이용해 salaries 테이블의 레코드를 읽는다. 이때 옵티마이저는 employees 테이블의 레코드 한 건당 salaries 테이블의 레코드 10건이 일치할 것으로 예상했다.

` 여기서 중요한것은 employees 테이블의 rows칼럼의 값이 233이고, filtered 칼럼의 값이 100%라는 것이다. `

그럼 이제 컨디션 팬아웃을 활성화 하고 보면 아래와 같다.

filtered 값이 100이 아니라 23.2로 됐다. 옵티마이저는 인덱스를 사용할 수 있는 first_name 칼럼 조건 이외의 나머지 조건(hire_date 칼럼의 조건)에도 얼마나 조건을 충족할지를 고려했다는 뜻이다.

실행계획의 의미

100과 23.2의 차이.

즉, 컨디션 팬아웃이 비활성화되면, employees 테이블에서 모든 조건을 충족하는 레코드가 233건으로 예측했으나, 최적화를 켜놓으면 233*0.232 = 54건만 조건을 충족할 것으로 예측했다.

옵티마이저가 조건을 만족하는 레코드 건수를 정확하게 예측할 수 있다면 더 빠른 실행 계획을 만들 수 있다.

그럼 컨디션 팬아웃 최적화는 어떻게 filtered 칼럼 값을 예측할까.

1. WHERE 조건절에 사용된 칼럼에 인덱스가 있는 경우
2. WHERE 조건절에 사용된 칼럼에 대해 히스토그램이 있는 경우.

예제 쿼리는 실제 실행되는 경우에는 first_name=’Matt’ 조건을 위해 ix_firstname인덱스만 사용한다. 그러나. 실행계획을 수립하는 경우에는 first_name 인덱스로 조건만족이 233건이라는걸 알아내고, hire_date칼럼의 조건을 만족하는 레코드 비율이 약 23%라는걸 예측한다. employees 테이블의 hire_Date칼럼의 인덱스가 없었다면 옵티마이저는 first_name 인덱스를 이용해 hire_date의 분포값을 살펴보고 filtered 칼럼 값을 예측한다.

옵티마이저가 실행계획을 수립할때 다음의 우선순위로 이용한다.

1. 레인지 옵티마이저를 이용한 예측
2. 히스토그램을 이용한 예측
3. 인덱스 통계를 이용한 예측
4. 추측에 기반한 예측

9.3.1.16 파생 테이블 머지(derived_merge)

예전버전의 MySQL은 아래와 같이 FROM절에 사용된 서브쿼리는 먼저 실행해서 결과를 임시테이블로 만들고 외부 쿼리부분을 처리했다.

SELECT * FROM (
    SELECT * FROM employees WHERE first_name='matt'
) derived_table
WHERE derived_table.hire_date='1986-04-03'

|id|select_type|table|type|key| |–|–|–|–|–| 1|PRIMARY|<derived2>|ref|| 1|DERIVED|employees|ref|ix_firstname|

쿼리의 실행계획을 보면 first_name=’matt’ 부분을 보면 임시테이블을 만들고(메모리 저장하다가 커지면 disk), 이런 서브쿼리를 파생 테이블 Derived Table이라고도 한다.

내부적으로 임시테이블을 생성하고 임시 테이블로 insert하고 다시 읽으면서 레코드를 복사하고 읽는 오버헤드가 생긴다.

그래서 레코드가 많아진다면 임시 테이블로 레코드를 복사하고 읽는 오버헤드로 인해 쿼리의 성능은 많이 느려진다.

그러다가 5.7부터는 파생테이블로 만들어진 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입됐다. Derived_merge

이제 임시 테이블이 외부 쿼리와 병합된 경우의 실행계획을 보자.

이 실행계획에서는 DERIVED 라인이 없어지고, 서브쿼리 없이 employees 테이블을 조회하던 형태로 바뀌었다. 예전에는 이렇게 서브쿼리로 작성된 쿼리를 외부 쿼리로 병합하는 작업을 DBA가 수작업으로 처리했다. 이젠 옵티마이저가 한다.

그러나 모든 쿼리에 대해 옵티마이저가 서브쿼리를 외부 쿼리로 병합할 수 있는것은 아니다. 다음의 경우 자동으로 안되서 서브쿼리는 외부쿼리로 수동으로 병합해서 작성하는 것이 좋다.

• 집계함수와 윈도우 함수가 사용된 서브쿼리
• DISTINCT가 사용된 서브쿼리
• GROUP BY나 HAVING이 사용된 서브쿼리
• LIMIT이 사용된 서브쿼리
• UNION 또는 UNION ALL을 포함하는 서브쿼리
• SELECT절에 사용된 서브쿼리
• 값이 변경되는 사용자 변수가 사용된 서브쿼리

9.3.1.17 인비저블 인덱스(use_invisible_indexs)

8.0버전부터는 인덱스의 가용상태를 제어할 수 있다. 인덱스를 삭제하지 않고도 인덱스를 사용하지 못하게 할 수 있다.

9.3.1.18 스킵 스캔(skip_scan)

인덱스가 (A,B,C)로 있다면, WHERE에 A, AB는 쓸 수 있지만 B C는 못쓴다. 인덱스 스킵스캔은 제한적으로 이런 제약사항을 뛰어 넘을 수 있는 최적화 기법이다.

이런 인덱스가 있다고 해보자. (gender, birth_date)

SELECT * 
FROM employees
WHERE birth_date>='2025-09-30'
-- 인덱스를 못씀


SELECT * 
FROM employees
WHERE gender='M' AND birth_date>='2025-09-30'
-- 인덱스를 쓸 수 있음

그러나 8.0부터는 스킵스캔이 도입되어 후행칼럼의 조건만으로도 인덱스를 이용한 쿼리성능 개선이 가능하다. 그래서 옵티마이저는 테이블에 존재하는 모든 gender칼럼의 값을 가져와서 2번째 쿼리같이 gender칼럼의 조건이 있는것처럼 쿼리를 최적화한다.

그런데 인덱스 선행칼럼의 값이 너무 다양하다면 스킵스캔 최적화가 비효율적일 수 있다. 그래서 옵티마이저는 선행칼럼이 소수의 유니크 한 값을 가질때만 스킵스캔 최적화를 사용한다.

9.2.4.2에서 인덱스 스킵스캔도 루스인덱스 스캔과 같이, 누락된 인덱스의 선행칼럼이 유니크한 값을 많이 가질 수록 쿼리 처리 성능이 떨어진다.

9.3.1.19 해시 조인(hash_join)

8.0.18 버전부터 해시조인이 추가로 지원되었다.

많은 사용자가 해시 조인 기능을 기대하는 이유는 NLJ보다 해시조인이 빠르다고 생각하기 때문이다. 하지만 항상 옳은 얘기가 아니다.

똑같은 시점에서 시작해서 해시조인이 먼저 끝나긴 했다. A지점은 쿼리가 실행되면서 MySQL서버가 첫번째 레코드를 찾아낸 시점, B는 마지막 레코드를 찾아낸 시점이다. 마지막을 찾았다고 쿼리 종료는 아니지만, 일단 이렇게 처리했다.

해시조인은 시작이 느리지만 결과가 빠르고, NLJ는 시작이 빠르지만 결과가 느리다. 즉, 해시조인은 최고 스루풋(Best Throughput) 전략에 적합하며 NLJ는 최고 응답속도(Best Response-time) 전략에 적합하다.

일반적인 웹서비스는 OLTP서비스이므로 스루풋도 중요하지만 응답속도가 더 중요하다.

분석은 사용자 응답시간보다 전체 시간이 중요하므로 전체 스루풋이 중요하다.

MySQL은 범용 DBMS이므로 OLTP처리를 위한 DBMS라는거다. 아마도 대용량 데이터 분석을 위해 MySQL을 사용하진 않을것이고 따라서 NLJ, HJ 뭘 고를지는 명확하다.

따라서 MySQL에서의 HJ는 조인 조건 칼럼의 인덱스가 없다거나 조인 대상 테이블중 일부의 레코드 건수가 적은 경우 등에 대해서만 해시조인이 사용된다.

그러니까 HJ는 NLJ의 차선책이다. 억지로 힌트로 HJ를 강제하지 마라.

8.0.17 버전까지는 조인 조건이 좋지 않은 경우 BNLJ이 사용되었다. 조인 대상 테이블의 레코드가 조인 버퍼보다 큰 경우에는 드라이빙 테이블을 여러번 반복해서 스캔해야 하는 문제점이 있었다.

8.0.18, 19 버전에서는 동등 조인(Equi-join)을 위해서는 ㅐ시 조인이 사용됐지만, 안티 조인이나 세미 조인을 위해서는 BNLJ이 사용됐다. 하지만 20부터는 BNLJ이 아예 사라지고 HJ 세상이 됐다.

9.3.1.20 인덱스 정렬 선호(prefer_ordering_index)