책너두 (Real MySQL 8.0 1권) 35일차 (~392p)

요약

• 인덱스 힌트 중, SQL_CACL_FOUND_ROWS 에 대한 내용을 알게 됨.
  • LIMIT 에 명시된 수만큼의 만큼의 레코드 뿐만 아니라 끝까지 검색을 수행함
  • 이 힌트는 사용하지 않는 방향을 추천함.
• 옵티마이저 힌트에 대한 내용과 그 종류를 알게 됨.
  • 인덱스, 테이블, 쿼리 블록, 글로벌의 옵티마이저 종류가 있음.
  • MAX_EXECUTION_TIME
  • SET_VAR
  • SEMIJOIN & NO_SEMIJOIN
  • SUBQUERY
  • BNL & NO_BNL & HASHJOIN & NO_HASHJOIN
  • JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX
  • MERGE & NO_MERGE
  • INDEX_MERGE & NO_INDEX_MERGE
  • NO_ICP
  • SKIP_SCAN & NO_SKIP_SCAN
  • INDEX & NO_INDEX
    • 위 종류는 이전 옵티마이저 스위치에서 알게된 내용들에 대한 힌트들의 사용법을 설명함.

메모

SQL_CALC_FOUND_ROWS(3)

• MySQL 의 LIMIT을 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 더 많더라도, LIMIT에 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색 작업을 멈춤.
• 하지만, SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리의 경우, LIMIT 을 만족하는 수만큼의 레코드를 찾았다고 하더라도 끝까지 검색을 수행함.
  • 최종적으로 사용자에게는 LIMIT에 제한된 수만큼의 결과 레코드만 반환됨에도 불구하고도 끝까지 검색함.
• SQL_CALC_FOUND_ROWS 힌트가 사용된 쿼리가 실행된 경우, FOUND_ROWS()라는 함수를 이용해 LIMIT을 제외한 조건을 만족하는 레코드가 전체 몇 건이었는지 알아낼 수 있음.
• SQL_CALC_FOUND_ROWS 힌트를 사용하면 전체 쿼리를 다 검색하므로 디스크 I/O 작업이 발생하여 느린 작업임.
  • 따라서, SQL_CALC_FOUND_ROWS 힌트를 사용하지 않는 방향을 추천함.

옵티마이저 힌트

• MySQL 8.0 버전에서 사용 가능한 힌트 종류는 매우 다양함.
• 옵티마이저 힌트가 미치는 영향 범위도 매우 다양함.

옵티마이저 힌트 종류

• 옵티마이저 힌트는 영향 범위에 따라 다음 4개 그룹으로 나누어 볼 수 있음.
  • 인덱스 : 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
  • 테이블 : 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
  • 쿼리 블록 : 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트임.
    • 특정 쿼리 블록의 이름을 명시하는 것이 아니라, 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트임.
  • 글로벌(쿼리 전체) : 전체 쿼리에 대해서 영향을 미치는 힌트
• 위 구분으로 인해 힌트의 사용 위치가 달라지는 것은 아님.
• 그리고, 힌트에 인덱스 이름이 명시 될 수 있는 경우를 인덱스 수준의 힌트로 구분하고, 테이블 이름까지만 명시될 수 있는 경우를 테이블 수준의 힌트로 구분한다.
• 또, 특정 힌트는 테이블과 인덱스의 이름을 모두 명시할 수도 있지만, 인덱스의 이름을 명시하지 않고 테이블 이름만 명시할 수도 있는데, 이런 경우는 인덱스와 테이블 수준의 힌트가 된다.
• 하나의 SQL 문장에서 SELECT 키워드는 여러 번 사용될 수 있음.
  • 이때, 각 SELECT 키워드로 시작하는 서브쿼리 영역을 쿼리 블록 이라고 함.
  • 특정 쿼리 블록에 영향을 미치는 옵티마이저 힌트는 그 쿼리 블록 내에서 사용될 수도 있지만, 외부 쿼리 블록에서 사용할 수도 있음.
    • 이처럼 특정 쿼리 블록을 외부 쿼리 블록에서 사용하려면 “QB_NAME()” 힌트를 이용하여 해당 쿼리 블록에 이름을 부여해야 함.
    • 아래 쿼리는 특정 쿼리 블록(서브쿼리)에 대해 “subq1” 이라는 이름을 부여하고, 그 쿼리 블록을 힌트에 사용하는 예쩨임.

mysql> EXPLAIN
             SELECT /*+ JOIN_ORDER(e, s@subq1) */
             COUNT(*)
             FROM employees e
             WHERE e.first_name='Matt'
             AND e.emp_no IN (SELECT /*+ QB_NAME(subq1) */ s.emp_no
                                                FROM salaries s
                                              WHERE s.salary BETWEEN 50000 AND 50500);

• 위 예제 쿼리는 서브 쿼리에 사용된 salaries 테이블이 세미 조인 최적화를 통해 조인으로 처리될 것을 예상하고, JOIN_ORDER 힌트를 사용한 것임.
  • 조인의 순서로 외부 쿼리 블록의 employees 테이블과 서브 쿼리 블록의 salaries 테이블을 순서대로 조인하게 힌트를 사용한 것임.
  • 위 예제 쿼리와 같은 힌트 사용은 일반적이진 않지만, 쿼리 블록에 대한 이름 부여와, 그 쿼리 블록 내부의 테이블을 외부 쿼리 블록에서 사용하기 위해서는 이와 같이 사용해야 함.

MAX_EXECUTION_TIME

• 옵티마이저 힌트 중, 유일하게 쿼리의 실행 계획에 영향을 미치지 않는 힌트임.
• 단순히 쿼리의 최대 실행 시간을 설정하는 힌트임.
• MAX_EXECUTION_TIME 힌트에는 밀리초 단위의 시간을 설정함
  • 쿼리가 지정된 시간을 초과하면 쿼리는 실패하게 됨.

SET_VAR

• 옵티마이저 힌트뿐만 아니라 MySQL 서버의 시스템 변수들 또한 쿼리의 실행 계획에 상당한 영향을 미침.
  • 대표적으로 조인 버퍼의 크기를 설정하는 join_buffer_size 시스템 변수의 경우, 쿼리에 아무런 영향을 미치지 않을 것처럼 보임.
  • 하지만, MySQL 서버의 옵티마이저는 조인 버퍼의 공간이 충분하면 조인 버퍼를 활용하는 형태의 실행 계획을 선택할 수도 있음.
  • 뿐만 아니라, 옵티마이저 힌트로 부족한 경우, optimizer_switch 시스템 변수를 제어해야 할 수도있음.
    • 이런 경우, 아래와 같이 SET_VAR 힌트를 이용하면 됨.

mysql> EXPLAIN
             SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ *
             FROM employees
             WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

• SET_VAR 힌트는 실행 계획을 바꾸는 용도뿐만 아니라, 조인 버퍼나 정렬용 버퍼(소트 버퍼)의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로도 사용할 수 있음.
• 모든 시스템 변수를 SET_VAR 힌트로 조정할 수는 없음.

SEMIJOIN & NO_SEMIJOIN

• 세미 조인의 최적화는 여러 가지 세부 전략이 있다는 것을 이미 살펴봄.
  • 9.3.1.9 “세미조인(semijoin)” 참고
• SEMIJOIN 힌트는 어떤 세부 전략을 사용할지를 제어하는데 사용할 수 있음.

최적화 전략	힌트
Duplicate Weed-out	SEMIJOIN(DUPSWEEDOUT)
First Match	SEMIJOIN(FIRSTMATCH)
Loose Scan	SEMIJOIN(LOOSESCAN)
Materialization	SEMIJOIN(MATERIALIZATION)
Table Pull-out	없음

• “Table Pull-out” 최적화 전략은 별도로 힌트를 사용할 수 없음.
  • “Table Pull-out” 전략은 그 전략을 사용할 수 있다면 항상 더 나은 성능을 보장하기 때문임.
  • 하지만 다른 최적화 전략들은 상황에 따라 다른 최적화 전략으로 우회하는 것이 더 나은 성능을 낼 수도 있기 때문에 NO_SEMIJOIN 힌트도 제공되는 것임.
• 예시는 p383 ~ 384에 잘 설명되어 있음.
• 세미 조인 최적화 힌트는 외부 쿼리가 아니라 서브쿼리에 명시해야 함.
• 다른 방법으로는, 우선 서브쿼리에 쿼리 블록 이름을 정의하고, 실제 세미 조인 힌트는 외부 쿼리 블록에 명시하는 방법이 있음.
• 특정 세미 조인 최적화 전략을 사용하지 않게 하려면 NO_SEMIJOIN 힌트를 명시해서 해당 최적화 전략을 사용하지 않게 가이드함.

SUBQUERY

• 서브쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법임.
• 서브쿼리는 다음 2가지 형태로 최적화할 수 있음.

최적화 방법	힌트
IN-to-EXISTS	SUBQUERY(INTOEXISTS)
Materialization	SUBQUERY(MATERIALIZATION)

• 세미 조인 최적화는 주로 IN(subquery) 형태의 쿼리에 사용될 수 있음.
  • 안티 세미 조인(Anti Semi-Join)의 최적화에는 사용될 수 없음.
  • 주로, 안티 세미 조인 최적화에는 위 2가지 최적화가 사용됨.
• 서브쿼리 최적화 힌트는 세미 조인 최적화 힌트와 비슷한 형태로, 서브쿼리에 힌트를 사용하거나, 서브쿼리에 쿼리 블록 이름을 지정해서 외부 쿼리 블록에서 최적화 방법을 명시하면 됨.
• 서브쿼리 최적화 전략은 사용할 기회가 그렇게 많지는 않음.
  • 매뉴얼을 참조하자. (https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization.html)

BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

• MySQL 8.0.19 버전까지는 블록 네스티드 루프(Block Nested Loop) 조인 알고리즘을 사용했지만, 8.0.18 버전부터 도입된 해시 조인 알고리즘이 8.0.20 버전부터는 블록 네스티드 루프 조인까지 대체하도록 개선됨.
  • 더이상 8.0.20 버전부터 블록 네스티드 루프 조인은 MySQL 서버에 사용되지 않음.
  • 하지만 BNL 힌트와 NO_BNL힌트는 MySQL 8.0.20 과 그 이후의 버전에서도 여전히 사용 가능함.
  • 8.0.20 버전과 그 이후 버전에서는 BNL 힌트를 사용하면 해시 조인을 사용하도록 유도하는 힌트로 용도가 변경됨.
    • 대신, HASHJOIN 과 NO_HASHJOIN 힌트는 MySQL 8.0.18 버전에서만 유효하고, 그 이후 버전에서는 효력이 없음.
    • 그래서 MySQL 8.0.20과 그 이후 버전에서는 해시 조인을 유도하거나 해시 조인을 사용하지 않게 하고자 한다면 다음 예제 쿼리와 같이 BNL과 NO_BNL 힌트를 사용해야 함.

mysql> EXPLAIN
             SELECT /*+ BNL(e, de) */ *
             FROM employees e
             INNER JOIN dept_emp de ON de.emp_no=e.emp_no;

MySQL 서버에서는 조인 조건이 되는 칼럼의 인덱스가 적절히 준비되어 있다면 해시 조인은 거의 사용되지 않음. 위 예제 쿼리에서도 힌트를 사용했지만, 실제 실행 계획에서는 해시 조인을 사용하지 않고 네스티드 루프 조인을 실행하게 될 것임. 해시 조인 알고리즘이 사용되게 하려면 조인 조건이 되는 emp_no 칼럼의 인덱스를 employees 테이블과 dept_emp 테이블에서 모두 제거하거나사용하지 못하게 해야 함.

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

• MySQL 서버에서 조인의 순서를 결정하기 위해 전통적으로 STRAIGHT_JOIN 힌트를 사용해왔음.
  • 하지만 STRAIGHT_JOIN 힌트는 우선 쿼리의 FROM 절에 사용된 테이블의 순서를 조인 순서에 맞게 변경해야 하는 번거로움이 있었음.
  • 또, STRAIGHT_JOIN은 한 번 사용되면 FROM 절에 명시된 모든 테이블의 조인 순서가 결정되기 때문에 일부는 조인 순서를 강제하고, 나머지는 옵티마이저에게 순서를 결정하게 맞기는 것이 불가능 했음.
  • 이 단점을 보완하기 위해 옵티마이저 힌트는 STRAIGHT_JOIN과 동일한 힌트까지 포함해서 다음과 같이 4개의 힌트를 제공함.
    • JOIN_FIXED_ORDER : STRAIGHT_JOIN 힌트와 동일하게 FROM 절의 테이블 순서대로 조인을 실행하게 하는 힌트
    • JOIN_ORDER : FROM 절에 사용된 테이블의 순서가 아니라 힌트에 명시된 테이블의 순서대로 조인을 실행하는 힌트
    • JOIN_PREFIX : 조인에서 드라이빙 테이블만 강제하는 힌트
    • JOIN_SUFFIX : 조인에서 드리븐 테이블(가장 마지막에 조인돼야 할 테이블들)만 강제하는 힌트
• 조인 순서와 관련된 옵티마이저 힌트의 사용법은 아래와 같음.
  • 물론 쿼리 블록의 이름까지 사용하면서 복잡하게 사용할 수도 있음.

--// FROM 절에 나열된 테이블의 순서대로 조인 실행
mysql> SELECT /*+ JOIN_FIXED_ORDER() */ *
             FROM employees e
             INNER JOIN dept_emp de ON de.emp_no=e.emp_no
             INNER JOIN departments d ON d.dept_no=de.dept_no;

--// 일부 테이블에 대해서만 조인 순서를 나열
mysql> SELECT /*+ JOIN_ORDER(d, de) */ *
             FROM employees e
             INNER JOIN dept_emp de ON de.emp_no=e.emp_no
             INNER JOIN departments d ON d.dept_no=de.dept_no;

--// 조인의 드라이빙 테이블에 대해서만 조인 순서를 나열
mysql> SELECT /*+ JOIN_PREFIX(e, de) */ *
             FROM employees e
             INNER JOIN dept_emp de ON de.emp_no=e.emp_no
             INNER JOIN departments d ON d.dept_no=de.dept_no;

--// 조인의 드리븐 테이블에 대해서만 조인 순서를 나열
mysql> SELECT /*+ JOIN_SUFFIX(de, e) */ *
             FROM employees e
             INNER JOIN dept_emp de ON de.emp_no=e.emp_no
             INNER JOIN departments d ON d.dept_no=de.dept_no;

MERGE & NO_MERGE

• 이전 버전의 MySQL 서버에서는 FROM 절에 사용된 서브쿼리를 항상 내부 임시 테이블로 생성함.
  • 이렇게 생성된 내부 임시 테이블을 파생 테이블(Derived table) 이라고 함.
  • 이는 불필요한 자원 소모를 유발함.
  • MySQL 5.7과 8.0 버전에서는 가능하면 임시 테이블을 사용하지 않게 FROM 절의 서브쿼리를 외부 쿼리와 병합하는 최적화를 도입함.
  • 때로는 MySQL 옵티마이저가 내부 쿼리를 외부 쿼리와 병합하는 것이 나을 수도 있고, 때로는 내부 임시 테이블을 생성하는 것이 더 나은 선택일 수도 있음.
    • 하지만 MySQL 옵티마이저는 최적의 방법을 선택하지 못할 수도 있는데, 이때는 다음과 같이 MERGE 또는 NO_MERGE 옵티마이저 힌트를 사용하면 된다.

mysql> EXPLAIN
             SELECT /*+ MERGE(sub)*/ *
             FROM (SELECT *
                         FROM employees
                         WHERE first_name='Matt') sub LIMIT 10;

+----+-------------+-----------+------+--------------+-------------+
| id | select_type | table     | type | key          | Extra       |
+----+-------------+-----------+------+--------------+-------------+
| 1  | SIMPLE      | employees | ref  | ix_firstname | Using where |
+----+-------------+-----------+------+--------------+-------------+

mysql> EXPLAIN
             SELECT /*+ NO_MERGE(sub)*/ *
             FROM (SELECT *
                         FROM employees
                         WHERE first_name='Matt') sub LIMIT 10;

+----+-------------+------------+------+--------------+-------+
| id | select_type | table      | type | key          | Extra |
+----+-------------+------------+------+--------------+-------+
| 1  | SIMPLE      | <derived2> | ALL  | NULL         | NULL  |
| 2  | DERIVED     | employees  | ref  | ix_firstname | NULL  |
+----+-------------+------------+------+--------------+-------+

INDEX_MERGE & NO_INDEX_MERGE

• MySQL 서버는 가능하다면 테이블당 하나의 인덱스만을 이용해 쿼리를 처리하려고 함.
  • 하지만 하나의 인덱스만으로 검색 대상 범위를 충분히 좁힐 수 없다면 MySQL 옵티마이저는 사용 가능한 다른 인덱스를 이용하기도 함.
    • 여러 인덱스를 통해 검색된 레코드로부터 교집합 또는 합집합만을 구해서 그 결과를 반환함.
    • 이처럼 하나의 테이블에 대해 여러 개의 인덱스를 동시에 사용하는 것을 인덱스 머지(Index Merge) 라고 함.
    • 인덱스 머지 실행 계획은 때로는성능 향상에 도움이 되지만 항상 그렇지는 않을 수도 있음.

mysql> EXPLAIN SELECT *
             FROM employees
             WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

+-------------+----------------------+----------------------------------------------------+
| type        | key                  | Extra                                              |
+-------------+----------------------+----------------------------------------------------+
| index_merge | ix_firstname,PRIMARY | Using intersect(ix_firstname,PRIMARY); Using where |
+-------------+----------------------+----------------------------------------------------+

mysql> EXPLAIN SELECT /*+ NO_INDEX_MERGE(employees PRIMARY) */ *
             FROM employees
             WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

+-----------+-------+--------------+-----------------------+
| table     | type  | key          | Extra                 |
+-----------+-------+--------------+-----------------------+
| employees | range | ix_firstname | Using index condition |
+-----------+-------+--------------+-----------------------+

mysql> EXPLAIN SELECT /*+ INDEX_MERGE(employees ix_firstname, PRIMARY) */ *
             FROM employees
             WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

+-----------+-------------+----------------------+---------------------------------------+
| table     | type        | key                  | Extra                                 |
+-----------+-------------+----------------------+---------------------------------------+
| employees | index_merge | ix_firstname,PRIMARY | Using intersect(ix_firstname,PRIMARY) |
+-----------+-------------+----------------------+---------------------------------------+

NO_ICP

• 인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown) 최적화는 사용 가능하다면 항상 성능 향상에 도움이 되므로 MySQL 옵티마이저는 최대한 인덱스 컨디션 푸시다운 기능을 사용하는 방향으로 실행 계획을 수립함.
  • 그래서 MySQL 옵티마이저는 ICP 힌트(인덱스 컨디션 푸시다운을 사용하도록 하는 힌트)는 제공되지 않음.
  • 그런데, 인덱스 컨디션 푸시다운으로 인해 여러 실행 계획의 비용 계산이 잘못된다면 결과적으로 잘못도니 실행 계획을 수립하게 될 수도 있음.
  • p389~390에 예시가 잘 설명되어 있음.

SKIP_SCAN & NO_SKIP_SCAN

• 인덱스 스킵 스캔은 인덱스의 선행 칼럼에 대한 조건이 없어도 옵티마이저가 해당 인덱스를 사용할 수 있게 해주는 매우 훌륭한 최적화 기능임.
  • 하지만 조건이 누락된 선행 칼럼이 가지는 유니크한 값의 개수가 많아진다면 인덱스 스킵 스캔의 성능은 오히려 더 떨어짐
  • MySQL 옵티마이저가 유니크한 값의 개수를 제대로 분석하지 못하거나 잘못된 경로로 인해 비효율적인 인덱스 스킵 스캔을 선택하면 NO_SKIP_SCAN 옵티마이저 힌트를 이용해 인덱스 스킵 스캔을 사용하지 않게 할 수 있음.
  • p391~392에 예시가 잘 설명되어 있음.

INDEX & NO_INDEX

• INDEX 와 NO_INDEX 옵티마이저 힌트는 예전 MySQL 서버에서 사용되던 인덱스 힌트를 대체하는 용도로 제공됨.
• 인덱스 힌트를 대체하는 옵티마이저 힌트는 다음과 같다.

인덱스 힌트	옵티마이저 힌트
USE INDEX	INDEX
USE INDEX FOR GROUP BY	GROUP_INDEX
USE INDEX FOR ORDER BY	ORDER_INDEX
IGNORE INDEX	NO_INDEX
IGNORE INDEX FOR GROUP BY	NO_GROUP_INDEX
IGNORE INDEX FOR ORDER BY	NO_ORDER_INDEX

• 인덱스 힌트는 특정 테이블 뒤에 사용했기 때문에 별도로 힌트 내에 테이블명 없이 인덱스 이름만 나열함.
  • 하지만 옵티마이저 힌트에는 테이블명과 인덱스 이름을 함께 명시해야 함.
  • p391 ~ 392에 예시가 잘 설명되어 있음.