MySQL(16)

오늘 배운 것

7주차 학습내용 (9.3.1.9 세미조인 ~ 9.3.1.20 인덱스 정렬 선호 )

9.3 고급최적화 내용.

9.3.1.9 세미조인

다른 테이블과 실제 조인하지 태의 쿼리. 않고, 조건에 일치하는 레코드가 있는지만 체크하는 형태의 쿼리

5.7 서버는 이 부분이 취약했다.

세미조인 최적화 도입 이전에 어떻게 처리했는지 보자.

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

일반적으로 서브쿼리가 실행되고, 그 다음 employees가 돌면서 일치하는 레코드만 검색할 것으로 기대된다.

그러나 employeess 테이블 풀스캔을 하면서 한건한건 서브쿼리 조건과 비교했다.

이 부분에서 상식적으로는 서브쿼리가 한번만 돌고, emp_no로 인덱스 서칭하면서 하면 57(employees)+57(employees) = 114번 정도로 됐을건데,

최적화가 잘 안되서 300363(employees 레코드 개수)를 읽으면서 하나하나 서브쿼리를 실행하고 그것과 비교한다는 말.

세미조인 형태의 쿼리, 안티 세미조인 쿼리는 최적화 방법이 차이가 있다. =(subQUery)와 IN(subquery) 형태의 세미 조인에 대해 3가지 최적화 방법이 있다.

• 세미 조인 최적화
• IN-to-EXISTS 최적화
• MATERIALIZATION 최적화

그리고 <>(subquery) 형태와 NOT IN (subquery) 형태의 안티 세미 조인 쿼리는 2가지 최적화 방법이 있다.

• IN-to-EXISTS 최적화
• MATERIALIZATION 최적화

여기선 세미 조인 최적화만 보겠다. 아래 5가지 전략들을 모아서 세미 조인 최적화 라고 한다.

1. Table Pull-out
2. Duplicate Weed-out
3. First Match
4. Loose Scan
5. Materialization

쿼리에 사용되는 테이블과 조인 특성에 따라 옵티마이저는 전략을 선별적으로 사용한다.

Table pull out은 사용 가능하면 항상 세미조인보다 좋은 성능이므로 별도 옵티마이저 옵션이 없다.

9.3.1.10 테이블 풀-아웃, 세미조인 최적화 전략 1, 질문 1

세미조인의 서브쿼리를 아우터 쿼리로 끄집어내고 조인쿼리로 재작성 하는 최적화.

서브쿼리 최적화 도입 이전에 수동으로 튜닝하던 대표적인 방법

SELECT * FROM employees e
WHERE e.emp_no IN (SELECT de.emp_no 
                    FROM dept_emp de
                    WHERE de.dept_no='d009')

8.0에서 이거 실행계획을 보면 ID가 모두 1이다. 동일한 값을 가진다는 것은 두 테이블의 서브쿼리가 아니라 조인으로 처리했음을 의미한다.

이게 모든 형태의 서브쿼리에서 사용될 수 있는것은 아니다.

1. TPO는 세미조인 서브쿼리에서만 사용가능하다.

2. TPO는 서브쿼리가 UNIQUE인덱스나 PK룩업 결과가 1건인 경우에만 사용가능하다.

3. TPO가 된다 하더라도 기존 쿼리의 최적화 방법이 사용 불가능인건 아니므로 MySQL은 가능하면 TPO를 최대한 적용한다.

4. TPO는 서브쿼리 테이블을 아우터로 가져와서 Join으로 풀어쓰는 최적화를 하는데, 만약 서브쿼리 모든 테이블이 아우터로 끄집어 낼 수 있다면, 서브쿼리 자체는 없어진다.

5. 최대한 서브쿼리를 조인으로 풀어서 사용해라 라는 튜닝가이드가 많은데, tPO는 그냥 이 가이드를 실행하는거다.

테이블 풀 아웃이 적용된 쿼리

SELECT *
FROM employees e join dept_emp de on e.emp_no=de.emp_no
WHERE de.emp_no='d009'

질문 1 : TPO를 적용한 쿼리가 뭔가.

9.3.1.11 퍼스트 매치, 세미조인 최적화 전략 2

IN형태의 세미조인을 EXISTS 형태로 튜닝한 것과 비슷한 방법으로 실행된다.

SELECT *
FROM employees e 
WHERE e.first_name='Matt'
    AND e.emp_no IN (
        SELECT t.emp_no
        FROM titles t
        WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
    )

이 쿼리 실행계획도 마찬가지로 ID가 1이다.

둘 다 1로 나온걸 보면 이것도 서브쿼리가 아니라 조인으로 실행됐다고 알 수 있다.

firstMatch = employees 테이블 레코드에 대해 title에서 일치하는걸 찾으면 더이상 title 검색을 안한다는걸 의미한다. 의미론적으론 EXISTS(subquery)와 동일하다.

하지만 FirstMatch는 서브쿼리가 아니라 조인으로 풀어서 실행하면서 일치하는 첫번째 레코드만 검색하는 최적화를 실행하는 것이다.

5.5의 IN-to-EXISTS변환과 비슷하지만, firstMatch는 이런 장점이 있다.

1. 여러 테이블이 조인되는 경우 원래 쿼리에는 없던 동등 조건을 옵티마이저가 추가하는 형태의 최적화가 실행되기도 한다. 기존 IN-to-EXISTS 최적화는 이런 동등조건전파가 서브쿼리 내에서만 가능했지만, FM은 조인형태라서 서브쿼리 뿐만아니라 아우터쿼리 테이블까지 전파될 수 있다. 그래서 FM으로 하면 더 많은 조건이 주어지므로 더 나은 실행계획 수립이 가능하다.

2. FM에선 서브쿼리 모든 테이블에 대해 FM최적화를 수행할지 일부 테이블에 대해서만 수행할지 취사선택할 수 있다.

FM의 제한사항과 특성을 보자.

1. FM은 서브쿼리에서 하나의 레코드만 검색되면 검색을 멈추는 단축실행경로(Short-cut path)이기 때문에 FM최적화에서 서브쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행된다.

2. FM이 사용되면 Extra에 FM문구가 나온다.
3. FM은 상관서브쿼리(Correlated subquery)에서도 사용될 수 있다.
4. FM은 GB나 집합함수가 사용된 서브쿼리 최적화엔 못쓴다.

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) 질문 6

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 세상이 됐다.

SELECT * 
FROM employees e IGNORE INDEX(PRIMARY, ix_hiredate)
  INNER JOIN dept_emp de IGNORE INDEX(ix_empno_fromdate, ix_fromdate)
    ON de.emp_no=e.emp_no AND de.from_date=hire_date

HJ는 빌드단계와 프로브 단계로 나뉜다.

• 빌드단계 : 조인 대상 테이블 중에서 레코드 건수가 적어서 해시 테이블로 만들기에 요잉한 테이블을 골라서 메모리에 해시 테이블을 빌드 하는 작업을 수행한다. 빌드 단계에서 해시테이블을 만들때 사용되는 원본 테이블을 빌드 테이블 이라고도 한다.

• 프로브단계 : 나머지 테이블의 레코드를 읽어서 해시테이블의 일치 레코드를 찾는 과정. 읽는 나머지 테이블을 프로브 테이블이라고도 한다.

근데 일반적인 EXPLAIN 실행계획으론 어느 테이블이 빌드고 프로브 테이블인지 알기 어렵다. EXPLAIN FORMAT=TREE 또는 EXPLAIN ANALYZE 명령어로 쉽게 구분 할 수 있다.

하여튼 실행계획에서 보면 최하단의 가장 들여쓰기 된 안쪽의 dept_emp 테이블이 빌드 테이블로 선정되었다.

옵티마이저는 빌드 테이블인 dept_emp를 읽어서 메모리 해시테이블을 만들고, employees프로브 테이블을 스캔하면서 메모리에 생성된 해시테이블에서 결과를 찾아서 반환한다.

해시테이블을 메모리에 저장할때 조인버퍼를 사용하는데, 조인버퍼 기본크기가 256KB다. 그래서 메모리 해시테이블이 이거보다 커지면,

빌드와 프로브 테이블을 적당한 크기(청크 하나가 조인버퍼보다 작도록)의 청크로 분리한 다음, 청크별로 위와 동일 방식으로 해시 조인을 처리한다.

해시조인에서 메모리 해시 테이블이 조인버퍼보다 커지면 빌드와 프로브 테이블을 적당한 크기의 청크로 분리하는데, 이때 적당한 크기가 몇이고 왜 그럴까

적당한 크기란 조인버퍼보다는 작은 크기. 동일방식으로 처리하기위해서.

9.18은 조인 버퍼보다 해시 테이블이 큰 경우 해시 조인이 실행되는 방법을 보여준다.

이 그림에선 해시조인 처리가 조금 복잡해졌는데, 만들어질 해시테이블이 설정된 메모리 크기(join_buffer_size)보다 큰 지 알 수 없기 때문이다.

MySQL서버는 dept_emp테이블을 읽으면서 메모리의 해시테이블을 준비하다가. 지정된 메모리 크기(join_buffer_size)를 넘어서면 dept_emp테이블의 나머지 레코드를 디스크에 청크로 구분해서 저장한다.(9.18의 1,2 과정) 그리고 employees테이블의 emp_no값을 이용해 메모리의 해시 테이블을 검색해서 1차 조인 결과를 생성한다. 그리고 동시에 employees 테이블에서 읽은 레코드를 디스크에 청크로 구분해서 저장한다.

그래서 9.18에서 디스크에 2개의 그룹으로 구분된 청크 목록이 표현된 거시다. 빌드테이블 청크는 dept_emp테이블의 레코드들을 저장해 둔 공간. 프로브 테이블 청크는 employees 테이블의 레코드들을 저장해 둔 공간이다.

1차 조인이 완료되면 9.19의 1번 화살표와 같이 “빌드 테이블 청크”에서 첫번째 청크를 읽어서 다시 “메모리 해시 테이블”을 구축한다. 그리고 2번화살표와 같이 프로브 테이블 청크에서 첫 번째 청크를 읽어서 메모리 해시 테이블과 조인해서 2차 조인을 진행한다. 이런게 n번 진행된다.

9.3.1.20 인덱스 정렬 선호(prefer_ordering_index)

옵티마이저는 ORDER BY 또는 GROUP BY 를 인덱스를 사용해 처리 가능한 경우, 실행계획에서 인덱스 가중치를 높이 설정해서 실행된다.

SELECT *
FROM employees
WHERE hire_date BETWEEB `1985-01-01` AND`1985-02-01`
ORDER BY emp_no;

그리고 인덱스는 PK만

이 쿼리는 대표적으로 2가지 실행계획이 있다.

1. ix_hiredate롤 레인지스캔으로 레코드 찾은다음 Emp_no로 정렬해서 결과 반환
2. PK 인덱스 풀스캔 하면서 조건 일치 비교 후 결과 반환

상황에따라 효율적인걸 고르긴 하는데,

일반적으로는 hire_date칼럼 조건에 부합하는 레코드가 많지 않다면 1번이 효율적이다. 그럼에도 2번계획을 선택하는 경우가 있을 수도 있다.

8.0.20까지는 ORDER BY를 위한 인덱스를 사용하지 못하도록 IGNORE INDEX 힌트를 사용하거나 했다. 8.0.21 버전부터는 옵티마이저가 ORDER BY인덱스에 너무 많은 가중치를 부여하지 않도록 옵티마이저 옵션이 추가됐다.