[MySQL] 옵티마이저 고급 최적화

기존의 Real MySQL (5.0, 5.1 버전) 책을 너무 유익하게 봤는데, Real MySQL 8.0이 전면 개정판이 나와서... 설레는 마음에 보면서 개인적인 공부용으로 정리하고 있습니다.
역시 믿고 보는 Real MySQL... 👍 한번 사서 보시는 것을 강력 추천드립니다!

고급 최적화

---

MRR과 배치 키 액세스

MRR은 Multi-Range-Read로, Disk Sweep Multi-Range-Read라고도 한다.

네스티드 루프 조인

• MySQL에서 지금까지 지원하던 조인 방식은 네스티드 루프 조인으로, 드라이빙 테이블의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 조인을 수행하는 것이다.
• MySQL 서버의 내부 구조상 조인 처리는 MySQL 엔진이 처리하지만, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당한다. 이때 드라이빙 테이블의 레코드 건별로 드리븐 테이블의 레코드를 찾으면 레코드를 찾고 있는 스토리지 엔진에서는 아무런 최적화를 수행할 수 없다.

위와 같은 단점을 보완하기 위해 MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링 한다.

• 즉 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링 한다.
• 조인 버퍼에 레코드가 가득 차면 비로소 MySQL 엔진의 버퍼링 된 레코드를 스토리지 엔진으로 한 번에 요청한다.

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

• 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없었다면 드리븐 테이블에서 연결되는 레코드를 찾기 위해 1,000번의 풀 테이블 스캔을 해야 한다.
• 어떤 방식으로도 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시 한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다. 이때 사용하는 메모리 캐시를 조인 버퍼라고 한다.
• join_buffer_size 시스템 변수로 크기를 제한할 수 있다.
• Extra 칼럼에 Using Join buffer로 표기된다.

블록 네스티드 루프 조인 주의사항

• 조인 버퍼가 사용되는 쿼리에서는 조인의 순서가 거꾸로인 것처럼 실행된다.
• 실제 드라이빙 테이블의 결과는 조인 버퍼에 담아두고, 드리븐 테이블을 먼저 읽고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다.
• 일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만, 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있음을 주의하자.

MySQL 8.0.18 버전부터는 해시 조인 알고리즘이 도입되어서, 블록 네스티드 루프 조인은 더 이상 사용되지 않고 해시 조인 알고리즘이 대체되어 사용된다.

hash_join (해시 조인 알고리즘)

해시 조인은 네스티드 조인에 비해 첫 번째 레코드를 찾는 데는 시간이 많이 걸리 짐 나 최종 레코드를 찾는 데 까지는 시간이 많이 걸리지 않는다. (네스티드 루프 조인은 반대)

• 해시 조인 쿼리는 Best Throughput에 적합
• 네스티드 루프 조인은 Best Response-time에 적합

일반적인 웹 서비스는 온라인 트랜잭션 서비스여서, 스루풋도 중요하지만 응답 속도가 더 중요하다.

• MySQL 서버는 주로 조인 조건의 칼럼이 인덱스가 없다거나 조인 대상 테이블 중 일부의 레코드 건수가 매우 적은 경우 등에 대해서만 해시 조인 알고리즘을 사용하도록 설게돼 있다.
• 네스티드 루프 조인이 사용되기에 적합하지 않은 경우 기존의 Block Nested Loop 대신에 사용한다고 생각하면 된다.

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

MySQL 5.6 버전부터 도입.

INDEX (last_name, first_name);

SELECT * FROM employees WHERE last_name='seungho' AND first_name LIKE '%sal';

• MySQL 5.6 이전 버전에서는 Using where 실행 계획이 사용되었다.
  • 인덱스를 범위 제한 조건으로 사용하지 못하는 first_name조건은 MySQL 엔진이 스토리지 엔진으로 아예 전달해주지 않았다.
  • 그래서 스토리지 엔진에서는 불필요한 레코드에 대한 읽기가 유발될 수 있다.
• MySQL 5.6 버전부터는 인덱스를 범위 제한 조건으로 사용하지 못한다고 하더라도 인덱스에 포함된 칼럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달할 수 있게 핸들러 API가 개선되었다.
  • 이로 인해, 인덱스를 이용해 최대한 필터링까지 완료해서 꼭 필요한 레코드에 대해서만 테이블 읽기를 수행할 수 있게 최적화되었다.
• Extra 칼럼에 using index condition 메시지가 출력된다.

인덱스 확장 (use_index_extensions)

• use_index_extensions 옵티마이저 옵션은 InnoDB 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션.
• 모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가진다.
  • 예전 MySQL 버전에서는 세컨더리 인덱스의 마지막에 자동으로 추가되는 프라이머리 키를 제대로 활용하지 못했지만, MySQL 서버가 업그레이드되면서 옵티마이저는 id_fromdate 인덱스의 마지막에 (dept_no, emp_no) 칼럼이 숨어있다는 것을 인지하고 실행 계획을 수립하도록 개선됐다.
  • INDEX(from_date) PK(dept_no, emp_no) SELECT COUNT(*) FROM from_date='1998-02-12' AND dept_no='d001';

인덱스 머지(index_merge)

• 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리한다.
• 쿼리에 사용된 각각의 조건에 따라 인덱스 머지 실행 계획을 선택한다.
  • index_merge_intersection (교집합)
    • Extra 칼럼에 Using intersect 표시
    • 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합만 반환함을 의미 (두 조건 모두 상대적으로 많은 레코드를 가져와야 하는 경우)
    • SELET * FROM member WHERE first_name='will' AND id BETWEEN 10 AND 100;
  • index_merge_union (합집합)
    • Extra에 Using union 표시
    • 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우 사용되는 최적화
    • SELECT * FROM memer WHERE first_name='will' OR date = '1998-02-12';
  • index_merge_sort_union (정렬 후 합집합)
    • 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 사용된다.
    • Extra 칼럼에 Using sort_union 표시

use_invisible_indexes

• MySQL 8.0 버전부터는 인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어하는 기능을 제공한다.
• ALTER TABLE ... ALTER INDEX ... [VISIBLE, INVISIBLE]

skip_scan

• 인덱스의 핵심은 값이 정렬돼 있다는 것으로, 인덱스를 구성하는 칼럼의 순서가 매우 중요하다.
• 따라서 (A, B, C) 칼럼으로 구성된 인덱스가 있을 때, B, C 칼럼에 대한 조건을 가지고 있다면 해당 쿼리는 인덱스를 활용할 수 없다.
• MySQL 8.0부터는 인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼의 조건만으로도 인덱스를 이용한 쿼리 성능 개선이 가능하다.
  • 옵티마이저는 테이블에 존재하는 모든 A 칼럼의 값을 가져와 마치 A 칼럼의 조건이 있는 것처럼 쿼리를 최적화한다. (이런 이유로 선행 칼럼이 매우 다양한 값을 가지는 경우 오히려 비효율적이다)
  • 이러한 이유로 소수한 유니크한 값을 가질 때만 인덱스 스킵 스캔 최적화를 사용한다.