[스터디] 18. 쿼리 힌트

목차

• 9.4 쿼리 힌트
  • 9.4.1 인덱스 힌트
    • 9.4.1.1 STRAIGHT_JOIN
    • 9.4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX
    • 9.4.1.3 SQL_CALC_FOUND_ROWS
  • 9.4.2 옵티마이저 힌트
    • 9.4.2.1 옵티마이저 힌트 종류
    • 9.4.2.2 MAX_EXECUTION_TIME
    • 9.4.2.3 SET_VAR
    • 9.4.2.4 SEMIJOIN & NO_SEMIJOIN
    • 9.4.2.5 SUBQUERY
    • 9.4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN
    • 9.4.2.7 JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX
    • 9.4.2.8 MERGE & NO_MERGE
    • 9.4.2.9 INDEX_MERGE & NO_INDEX_MERGE
    • 9.4.2.10 NO_ICP
    • 9.4.2.11 SKIP_SCAN & NO_SKIP_SCAN
    • 9.4.2.12 INDEX & NO_INDEX

9.4 쿼리 힌트

• MySQL 서버가 서비스의 비지니스를 완벽히 이해하지 못하므로, 서비스 개발자나 DBA가 옵티마이저에게 부족한 실행 계획을 어떻게 수립하는지 알려주기 위한 목적으로 MySQL에서는 힌트를 제공함.
• MySQL 서버에서 사용 가능한 쿼리 힌트는 다음과 같이 2가지로 구분할 수 있음.
  1. 인덱스 힌트: 예전 버전의 MySQL에서 사용된 힌트
  2. 옵티마이저 힌트: MySQL 5.6 버전부터 추가된 힌트
• 이 책에서는 옵티마이저 힌트가 아닌 것은 인덱스 힌트 절로 분류함.

9.4.1 인덱스 힌트

• STRAIGHT_JOIN과 USE INDEX 등을 포함한 인덱스 힌트는 MySQL 서버에 옵티마이저 힌트가 도입되기 전 사용된 기능으로, SQL 문법에 맞게 사용해야 하기 때문에 ANSI-SQL 표준을 준수하지 못함.
• MySQL 5.6 버전부터 추가된 옵티마이저 힌트는 MySQL 서버를 제외한 RDBMS에서는 주석으로 해석하여 ANSI-SQL 표준을 준수함.
• 인덱스 힌트는 SELECT 명령과 UPDATE 명령에서만 사용할 수 있음.

9.4.1.1 STRAIGHT_JOIN

• 옵티마이저 힌트인 동시에 조인 키워드임.
• SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 함.
• 일반적으로 조인을 하기 위한 칼럼들의 인덱스 여부로 조인 순서가 결정되며, 조인 컬럼의 인덱스에 문제가 없으면(조건을 만족하면) 레코드가 적은 테이블을 드라이빙으로 선택함. 옵티마이저가 FROM 절에 명시된 순서대로 조인을 수행하도록 유도함.
• 조인 순서가 결정되지 않은 경우 STRAIGHT_JOIN 힌트로 조인 순서를 조정하는 기준
  • 임시 테이블(인라인 뷰 또는 파생된 테이블)과 일반 테이블의 조인: 임시 테이블을 드라이빙으로 선택함.
  • 임시 테이블끼리 조인: 임시 테이블(서브쿼리로 파생된 테이블)은 항상 인덱스가 없기 때문에 크기가 작은 테이블을 드라이빙으로 선택함.
  • 일반 테이블끼리 조인: 양쪽 테이블 모두 조인 칼럼에 인덱스가 있거나 양쪽 테이블 모두 조인 칼럼에 인덱스가 없으면, 레코드 건수가 적은 테이블을 드라이빙으로 선택함. 그외에는 조인 칼럼에 인덱스가 없는 테이블을 드라이빙으로 선택함.
• 레코드 건수: WHERE 조건까지 포함해 인덱스를 사용할 수 있는 조건을 만족하는 레코드 건수를 의미함. 테이블 전체의 레코드 건수를 의미하지 않음.
• STRAIGHT_JOIN 힌트와 비슷한 옵티마이저 힌트
  • JOIN_FIXED_ORDER
  • JOIN_ORDER
  • JOIN_PREFIX
  • JOIN_SUFFIX

9.4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX

• STRAIGHT_JOIN 힌트와는 달리 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 함.
• 키워드 뒤에 사용할 인덱스의 이름을 괄호로 묶어 사용하며, 별도의 사용자가 부여한 이름이 없는 프라이머리 키는 "FRIMARY"라고 명시함.
• 인덱스 힌트의 용도
  • USE INDEX: MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트
  • FORCE INDEX: USE INDEX 보다 옵티마이저에게 미치는 영향이 더 강한 힌트
  • IGNORE INDEX: 특정 인덱스를 사용하지 못하게 하는 힌트

9.4.1.3 SQL_CALC_FOUND_ROWS

• MySQL의 LIMIT을 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 많아도 LIMIT에 명시된 수만큼 만족하는 레코드를 찾으면 검색 작업을 멈춤.
• SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리의 경우에는 LIMIT를 만족하는 수만큼 레코드를 찾아도 끝까지 검색을 수행함. FOUND_ROWS() 함수를 이용해 LIMIT을 제외한 조건을 만족하는 레코드가 몇 건인지 알 수 있음.

9.4.2 옵티마이저 힌트

9.4.2.1 옵티마이저 힌트 종류

• 영향 범위에 따라
  1. 인덱스: 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
  2. 테이블: 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
  3. 쿼리 블록: 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트로서, 특정 쿼리 블록의 이름을 명시하는 것이 아니라 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트
  4. 글로벌(쿼리 전체): 전체 쿼리에 대해서 영향을 미치는 힌트
• 힌트에 인덱스 이름이 명시될 수 있는 경우를 인덱스 수준의 힌트로 구분하고, 테이블 이름까지만 명시될 수 있는 경우를 테이블 수준의 힌트로 구분함.
• 특정 힌트는 테이블과 인덱스의 이름을 모두 명시할 수도 있지만 인덱스의 이름을 명시하지 않고 테이블 이름만 명시할 수도 있는데, 이런 경우는 인덱스와 테이블 수준의 힌트가 됨.

9.4.2.2 MAX_EXECUTION_TIME

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

9.4.2.3 SET_VAR

• MySQL 서버의 시스템 변수들 또한 쿼리의 실행 계획에 상당한 영향을 미침.
• 조인 버퍼의 크기를 설정하는 join_buffer_size 시스템 변수의 경우, 쿼리에 아무런 영향을 미치지 않을 것처럼 보임. 하지만 MySQL 서버의 옵티마이저는 조인 버퍼의 공간이 충분하면 조인 버퍼를 활용하는 형태의 실행 계획을 선택할 수도 있음.
• 옵티마이저 힌트로 부족한 경우 optimizer_switch 시스템 변수를 제어해야 할 수도 있음. 이런 경우에 SET_VAR 힌트를 이용함.
• SET_VAR 힌트는 조인 버퍼나 정렬용 버퍼(소트 버퍼)의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로도 사용할 수 있음.
• 모든 시스템 변수를 SET_VAR 힌트로 조정할 수는 없음.

9.4.2.4 SEMIJOIN & NO_SEMIJOIN

• 9.3.1.9 절을 참조하면, 세미 조인의 최적화는 여러 가지 세부 전략이 있다는 것을 알 수 있음.
• SEMIJOIN 힌트는 어떤 세부 전략을 사용할지를 제어하는 데 사용할 수 있음.

9.4.2.5 SUBQUERY

• 서브쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법임.
• 세미 조인 최적화는 주로 IN(subquery) 형태의 쿼리에 사용될 수 있지만, 안티 세미 조인(Anti Semi-Join)의 최적화에 사용될 수 없음.
• 서브쿼리 최적화 힌트는 서브쿼리에 힌트를 사용하거나, 서브쿼리에 쿼리 블록 이름을 지정해서 외부 쿼리 블록에서 최적화 방법을 명시하면 됨.

9.4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

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

9.4.2.7 JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

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

9.4.2.8 MERGE & NO_MERGE

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

9.4.2.9 INDEX_MERGE & NO_INDEX_MERGE

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

9.4.2.10 NO_ICP

• 인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown) 최적화는 사용 가능하다면 항상 성능 향상에 도움이 되므로, MySQL 옵티마이저는 최대한 인덱스 컨디션 푸시다운 기능을 사용하는 방향으로 실행 계획을 수립함.
• MySQL 옵티마이저에서는 ICP 힌트(인덱스 컨디션 푸시다운을 사용하도록 하는 힌트)는 제공되지 않음.
• 인덱스 컨디션 푸시다운으로 인해 여러 실행 계획의 비용 계산이 잘못된다면 잘못된 실행 계획을 수립하게 될 수도 있음.
• 테이블의 데이터 분포는 항상 균등한 것이 아니기 때문에, 인덱스 컨디션 푸시다운 최적화만 비활성화해서 조금 더 유연하고 정확하게 실행 계획을 선택하게 할 수 있음.

9.4.2.11 SKIP_SCAN & NO_SKIP_SCAN

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

9.4.2.12 INDEX & NO_INDEX

• INDEX와 NO_INDEX 옵티마이저 힌트는 예전 MySQL 서버에서 사용되던 인덱스 힌트를 대체하는 용도로 제공됨.
• 인덱스 힌트는 특정 테이블 뒤에 사용했기 때문에 힌트 내에 테이블명 없이 인덱스 이름만 나열했지만, 옵티마이저 힌트에는 테이블명과 인덱스 이름을 함께 명시해야 함.

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.