MySQL(18)

주차 학습내용 (9.3.2 조인 최적화 알고리즘 ~ 9장 마무리 )

9.4.2 옵티마이저 힌트

다양하므로 영향 범위별로 구분해서 보고, 자주 사용되는 것들 위주로 예제와 함께 보자.

9.4.2.1 옵티마이저 힌트 종류

크게 4그룹으로 나뉜다.

  1. 인덱스 : 특정 인덱스의 이름을 사용할 수 있는 옵힌
  2. 테이블 : 특정 테이블의 이름을 사용할 수 있는 옵힌
  3. 쿼리 블록 : 특정 쿼리블록에 사용할 수 있는 옵힌, 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵힌
  4. 글로벌(쿼리 전체) : 전체 쿼리에 대해서 영향을 미치는 힌트

하지만 이 구분으로 사용 위치가 달라지지는 않는다. 힌트에 인덱스 이름이 명시될 수 있으면 인덱스 수준의 힌트, 테이블 이름까지만이면 테이블 수준의 힌트, 테이블, 인덱스 모두 명시할 수도 있지만, 테이블 이름만 명시할 수 잇는데 이 경우 인덱스와 테이블 수준의 힌트가 된다.

모든 인덱스 수준의 힌트는 반드시 테이블명이 선행되야 한다.

하나의 SQL문장에서 SELECT 키워드는 여러번 사용될 수 있다. 이때 각 SELECT 키워드로 시작하는 서브쿼리 영역을 쿼리 블록 이라고 한다., 특정 쿼리블록에 영향을 미치는 옵티마이저 힌트는 그 쿼리 블록 내에서 사용될 수도 있지만 외부 쿼리 블록에서 사용할 수도 있다. 이처럼 특정 쿼리블록을 외부 쿼리 블록에서 사용하려면 “QB_NAME()” 힌트를 이용해 해당 쿼리블록에 이름을 부여해야 한다.

9.4.2.2 MAX_EXECUTION_TIME

유일하게 쿼리 실행계획에 영향을 미치지 않는 힌트. 단순히 쿼리의 최대 실행시간을 설정하는 힌트다. 타임아웃.

9.4.2.3 SET_VAR

옵힌 뿐만 아니라 MySQL서버의 시스템 변수들 또한 쿼리의 실행계획에 상당한 영향을 미친다.

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

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로 변경할 수는 없다.

9.4.2.4 SEMIJOIN & NO_SEMIJOIN 질문 3

세미조인 최적화는 여러 세부전략이 있다는 것을 살펴봤다. 어떤 세부전략을 사용할지를 제어하는데 사용할 수 있다.

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

왜 Table PUll-out 전략은 힌트를 사용할 수 없나?

이걸 사용할 수 있다면, 항상 더 나은 성능을 보장하기 때문이다. 하지만 다른 최적화 전략은 상황에 따라 다른 최적화 전략으로 우회할 수도 있다.

9.4.2.5 SUBQUERY

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

  1. IN-to-EXISTS
  2. Materialization

세미조인 최적화는 주로 IN(subquery) 형태의 쿼리에 사용될 수 있지만 안티 세미 조인 최적화에는 사용될 수 없다. 그래서 주로 안티 세미조인 최적화에는 위의 2가지 최적화가 사용된다.

서브쿼리 최적화 전략은 사용할 기회가 많지 않다.

9.4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN 질문 4

8.0.19버전까지는 BNLJ를 사용했지만, 8.0.18에 도입된 해시조인이 8.0.20부터는 BNLJ을 대체하도록 개선됐다. 그러나 재밌게도 20버전 이후부터는 BNL힌트를 사용하면 해시조인을 사용하도록 유도하는 힌트로 용도가 변경되고, HASHJOIN힌트는 18버전에서만 유효하고 이후버전에서는 효력이 없다.

BNLJ를 사용하기 위해 힌트에 BNL를 썼는데, 해시조인으로 동작했다. 왜 그렇게 동작했을지 설명하시오

20버전 이후라서.

9.4.2.7 JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

조인 순서를 결정하기위해 전통적으로 인덱스 힌트 STRAIGHT_JOIN을 사용했다. 하지만 FROM절에 사용된 테이블 순서를 조인 순서에 맞게 변경해야 한다는 단점이 있었다. 그리고 전부 고정되므로, 일부만 고정하고 나머지는 옵티마이저에게 맡기는게 불가능했다.

이걸 보완하기위해 STRAIGHT_JOIN과 동일한 힌트를 포함해서 4개의 힌트를 제공한다.

  1. JOIN_FIXED_ORDER: STRAIGHT_JOIN과 동일하게 FROM절 순서대로 조인을 실행하게 한다.
  2. JOIN_ORDER : 힌트에 명시된 순서대로 조인 실행
  3. JOIN_PREFIX: 조인에서 드라이빙 테이블만 강제하는 힌트
  4. JOIN_SUFFIX: 조인에서 드리븐 테이블(가장 마지막에 조인돼야 할 테이블들)만 강제하는 힌트

9.4.2.8 MERGE & NO_MERGE

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

개인적 궁금증

네, 알겠습니다. 두 시나리오에 대한 구체적인 쿼리 예시를 통해 설명해 드릴게요.

## 1. 서브쿼리 병합이 더 유리한 경우

결론적으로, 외부 쿼리의 WHERE 조건이 매우 선택적일 때(결과를 크게 줄일 때) 병합이 유리합니다.

예시 쿼리

과거에 $150,000 이상의 연봉을 받은 모든 직원 중, 사원 번호가 10001인 직원의 정보를 찾는다고 가정해 봅시다.

SELECT *
FROM (
    -- 이 서브쿼리는 잠재적으로 수만 명의 직원을 반환할 수 있음
    SELECT emp_no, first_name, last_name
    FROM employees e
    WHERE e.emp_no IN (SELECT s.emp_no FROM salaries s WHERE s.salary > 150000)
) AS high_paid_employees
WHERE high_paid_employees.emp_no = 10001; -- 매우 선택적인 조건

분석

  • 병합하지 않을 때 (구 버전 방식) 🐢:

    1. MySQL은 FROM 절의 서브쿼리를 먼저 실행합니다. salaries 테이블을 스캔하여 연봉이 $150,000 이상인 모든 직원의 emp_no를 찾고, 이를 바탕으로 employees 테이블에서 수만 건의 데이터를 포함하는 거대한 임시 테이블(high_paid_employees)을 만듭니다.
    2. 그 후에야 이 거대한 임시 테이블에 WHERE emp_no = 10001 조건을 적용하여 최종 한 건을 찾습니다. 불필요한 중간 과정이 너무 큽니다.

  • 병합할 때 (현대 옵티마이저) ⚡️:

    1. 옵티마이저는 서브쿼리와 외부 쿼리를 병합하여 아래와 유사한 단일 쿼리로 재작성합니다. 
      SELECT e.emp_no, e.first_name, e.last_name
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.salary > 150000 AND e.emp_no = 10001;
    2. 옵티마이저는 e.emp_no = 10001 (Primary Key 조건)이 결과를 단 한 건으로 줄이는 가장 강력한 조건임을 즉시 알아챕니다.
    3. employees 테이블에서 emp_no가 10001인 직원 단 한 명을 먼저 찾고, 그 후에 salaries 테이블을 조인하여 연봉이 $150,000를 넘었는지 확인합니다.
    4. 조건절 푸시다운(Predicate Pushdown) 덕분에 작업량이 극적으로 줄어들어 매우 효율적입니다.

## 2. 내부 임시 테이블 생성이 더 유리한 경우 (구체화, Materialization)

결론적으로, 서브쿼리의 결과가 매우 작고, 이 작은 결과를 외부의 거대한 테이블과 조인할 때 임시 테이블 생성이 유리합니다.

예시 쿼리

현재 재직 중인 모든 부서 매니저들의 전체 연봉 이력을 찾는다고 가정해 봅시다.

-- salaries 테이블은 수백만 건의 데이터를 가짐
SELECT *
FROM salaries s
WHERE s.emp_no IN (
    -- 이 서브쿼리는 현재 매니저 9명만 반환함 (매우 작은 결과)
    SELECT dm.emp_no
    FROM dept_manager dm
    WHERE dm.to_date = '9999-01-01'
);

분석

  • 병합하려고 시도할 때 (잠재적으로 비효율적): 만약 쿼리를 병합하면, 수백만 건의 salaries 레코드 각각에 대해 “이 직원이 현재 매니저인가?”를 dept_manager 테이블과 조인하여 확인해야 합니다. 이는 반복적인 조인 비용을 유발할 수 있습니다.

  • 임시 테이블 생성 (옵티마이저의 전략적 선택) ⚡️:

    1. 옵티마이저는 서브쿼리(SELECT dm.emp_no FROM dept_manager ...)의 결과가 9건으로 매우 작다는 것을 인지합니다.
    2. 먼저 이 서브쿼리를 실행하여 현재 매니저 9명의 사원 번호를 담은 작은 내부 임시 테이블을 만듭니다. MySQL은 이 임시 테이블에 자동으로 인덱스(해시 인덱스 등)를 생성하여 조인 성능을 최적화합니다.
    3. 그 후에 수백만 건의 salaries 테이블을 스캔하면서, 각 레코드의 emp_no가 이 작고 빠른 임시 테이블에 존재하는지 확인합니다.
    4. 작업을 분리하고 최적화된 중간 결과물(임시 테이블)을 활용하는 이 ‘구체화(Materialization)’ 전략이 전체적인 비용을 크게 줄여줍니다.

개인적 궁금증 종료

9.4.2.9 INDEX_MERGE & NO_INDEX_MERGE

가능하다면 테이블당 하나의 인덱스만을 이용해 쿼리를 처리하려고 한다. 그러나 충분히 좁힐 수 없다면 옵티마이저는 다른 인덱스를 사용하기도 한다.

여러 인덱스를 통해 검색된 레코드로부터 교집합 또는 합집합만을 구해서 그 결과를 반환한다. 이처럼 하나의 테이블에서 여러 인덱스를 동시에 사용하는 것을 인덱스 머지 라고 한다.

9.4.2.10 NO_ICP

인덱스 컨디션 푸시다운 최적화는 사용가능하다면 항상 성능향상에 도움이 된다. 그러나 이로인해 실행계획 비용 계산이 잘못된다면, 결과적으로 잘못될 수 있다.

예를들어, A 인덱스와 B 인덱스 둘 중 하나를 골라야 하는데, A에서 ICP가 가능하다고 해서 A인덱스만 사용하다간. 실제 상황에서는 B인덱스가 더 효율적일 수 있다.

9.4.2.11 SKIP_SCAN & NO_SKIP_SCAN

인덱스 스킵스캔은, 인덱스의 선행칼럼에 대한 조건이 없어도 인덱스를 사용할 수 있도록 하는 훌륭한 최적화 기능이다. 하지만, 조건이 누락된 선행칼럼이 가지는 유니크한 칼럼의 수가 많아진다면, 인덱스 스킵 스캔의 성능은 오히려 떨어진다.

9.4.2.12 INDEX & NO_INDEX

예전 인덱스 힌트를 대체하는 용도로 제공된다.인덱스 힌트는 특정 테이블 뒤에 사용했기 때문에 별도로 힌트 내에 테이블명 없이 인덱스 이름만 나열했다. 하지만 옵티마이저 힌트에는 테이블명과 인덱스 이름을 함께 명시해야 한다.

results matching ""

    No results matching ""