Aurora MySQL의 병렬 쿼리를 위한 SQL 구성 - Amazon Aurora
EXPLAIN 문WHERE 절데이터 정의 언어(DDL)열 데이터 유형분할된 테이블집계 함수, GROUP BY 절 및 HAVING 절WHERE 절의 함수 호출LIMIT 절비교 연산자조인하위 쿼리UNION보기데이터 조작 언어(DML) 문트랜잭션 및 잠금B-트리 인덱스전체 텍스트 검색(FTS) 인덱스가상 열내장된 캐싱 메커니즘최적화 프로그램 힌트MyISAM 임시 테이블

Aurora MySQL의 병렬 쿼리를 위한 SQL 구성

다음 단원에서는 특정 SQL 문이 병렬 쿼리를 사용하거나 사용하지 않는 이유와 Aurora MySQL 기능이 병렬 쿼리와 상호 작용하는 방법에 대한 자세한 내용을 확인할 수 있습니다. 이러한 정보는 병렬 쿼리를 사용하는 클러스터의 성능 문제를 진단하고 병렬 쿼리가 특정 워크로드에 적용되는 방법을 이해하는 데 도움이 될 수 있습니다.

병렬 쿼리를 사용하려는 결정은 문이 실행되는 시점에 발생하는 여러 요인에 의존합니다. 따라서 병렬 쿼리는 특정 쿼리를 위해 항상 사용되거나 절대 사용되지 않거나 특정 조건에서만 사용될 수 있습니다.

작은 정보

HTML에서 이러한 예제를 볼 때 각 코드 목록의 오른쪽 위에 있는 복사 위젯을 사용해 SQL 코드를 복사하여 직접 시도할 수 있습니다. 복사 위젯을 사용하면 mysql> 프롬프트 및 -> 연속 줄 주위에 추가 문자가 복사되지 않습니다.

EXPLAIN 문

이 섹션의 곳곳에 나오는 예제에서 보듯이, EXPLAIN 문은 쿼리의 각 단계가 병렬 쿼리를 위해 현재 적격한지 여부를 나타냅니다. 또한 쿼리의 어떤 부분이 스토리지 계층으로 푸시 다운될 수 있는지를 나타냅니다. 다음은 쿼리 계획에서 가장 중요한 항목입니다.

  • NULL 열의 key 외에 다른 값은 쿼리가 인덱스 조회를 사용하여 효율적으로 수행될 수 있어서 병렬 쿼리가 사용될 가능성이 낮음을 암시합니다.

  • rows 열의 작은 값(즉, 값이 수백만이 아님)은 쿼리가 병렬 쿼리가 가치 있을 만큼 충분한 데이터에 액세스하고 있지 않음을 암시합니다. 즉, 병렬 쿼리가 발생할 가능성이 낮습니다.

  • Extra 열은 병렬 쿼리가 사용될 것으로 예상되는 경우를 표시합니다. 이 출력은 다음 예제와 비슷합니다.

    Using parallel query (A columns, B filters, C exprs; D extra)

    columns 수는 쿼리 블록에서 조회되는 열의 수를 나타냅니다.

    filters 수는 상수에 대한 열 값의 단순 비교를 나타내는 WHERE 조건자의 수를 나타냅니다. 등식, 부등식 또는 범위에 대해 비교할 수 있습니다. Aurora는 이러한 종류의 조건자를 가장 효율적으로 병렬화할 수 있습니다.

    exprs 수는 함수 호출, 연산자, 또는 필터 조건만큼 효율적이지는 않지만 병렬화될 수 있는 다른 표현식 등과 같은 표현식의 수를 나타냅니다.

    extra 수는 푸시 다운될 수 없고 헤드 노드에 의해 수행되는 표현식의 수를 나타냅니다.

예를 들어, 다음 EXPLAIN 출력을 고려해 보십시오.

mysql> explain select p_name, p_mfgr from part
    -> where p_brand is not null
    -> and upper(p_type) is not null
    -> and round(p_retailprice) is not null;
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+
| id | select_type | table |...| rows     | Extra                                                                      |
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | part  |...| 20427936 | Using where; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) |
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+

Extra 열의 정보는 쿼리 조건을 평가하고 결과 집합을 구성하기 위해 5개의 열이 각 행으로부터 추출되는 것을 보여줍니다. 한 개의 WHERE 조건자는 필터(즉, WHERE 절에서 직접 테스트되는 열)를 포함합니다. 두 WHERE 절은 더 복잡한 표현식(이 경우에는 함수 호출 포함)의 평가가 필요합니다. 0 extra 필드는 WHERE 절의 모든 작업이 병렬 쿼리 처리의 부분으로 스토리지 계층으로 푸시 다운됨을 확인합니다.

병렬 쿼리가 선택되지 않은 경우에는, 일반적으로 EXPLAIN 출력의 다른 열에서 이유를 추론할 수 있습니다. 예를 들어, rows 값이 너무 작을 수 있거나 possible_keys 열은 쿼리가 데이터 집약적인 스캔 대신 인덱스 조회를 사용할 수 있음을 나타낼 수 있습니다. 다음 예제에서는 쿼리가 적은 수의 행만 스캔할 것이라고 최적화 프로그램에서 추정할 수 있는 쿼리를 보여줍니다. 이는 기본 키의 특성을 기반으로 합니다. 이 경우에는 병렬 쿼리가 필요하지 않습니다.

mysql> explain select count(*) from part where p_partkey between 1 and 100;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | part  | range | PRIMARY       | PRIMARY | 4       | NULL |   99 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

병렬 쿼리의 사용 여부를 보여주는 출력은 EXPLAIN 문이 실행되는 시점에 사용할 수 있는 모든 요인을 고려합니다. 그 동안에 상황이 변경된 경우, 옵티마이저는 쿼리가 실제로 실행될 때 다른 선택을 할 수도 있습니다. 예를 들어, EXPLAIN은 문이 병렬 쿼리를 사용할 것이라고 보고할 수 있습니다. 그러나 나중에 쿼리가 실제로 실행될 때에는, 그 당시 조건을 기반으로 병렬 쿼리를 사용하지 않을 수도 있습니다. 그러한 조건에는 여러 다른 병렬 쿼리가 동시에 실행 중, 행이 테이블에서 삭제되는 중, 새 인덱스가 생성되는 중, 열린 트랜잭션 내에서 너무 많은 시간 경과 등이 포함될 수 있습니다.

WHERE 절

쿼리가 병렬 쿼리 최적화를 사용하기 위해서는 반드시 WHERE 절을 포함해야 합니다.

병렬 쿼리 최적화는 WHERE 절에 사용되는 다양한 유형의 표현식 속도를 높입니다.

  • 상수에 대한 열 값의 단순 비교(필터라고 알려짐). 이러한 비교는 스토리지 계층으로 푸시 다운되는 경우에 가장 많은 이점이 있습니다. 쿼리에 있는 필터 표현식의 수가 EXPLAIN 출력에 보고됩니다.

  • WHERE 절에 있는 다른 유형의 표현식 또한 가능한 경우 스토리지 계층으로 푸시 다운됩니다. 쿼리에 있는 그러한 표현식의 수가 EXPLAIN 출력에 보고됩니다. 이러한 표현식은 함수 호출, LIKE 연산자, CASE 표현식 등이 해당될 수 있습니다.

  • 특정 함수 및 연산자는 현재 병렬 쿼리에 의해 푸시 다운되지 않습니다. 쿼리에 있는 그러한 표현식의 수는 extra 출력에서 EXPLAIN 카운터로 보고됩니다. 나머지 쿼리도 여전히 병렬 쿼리를 사용할 수 있습니다.

  • 선택 목록의 표현식이 푸시 다운되지 않은 동안, 그러한 함수를 포함한 쿼리는 병렬 쿼리의 중간 결과를 위한 네트워크 트래픽 감소로부터 여전히 이점을 얻을 수 있습니다. 예를 들어, 선택 목록의 집계 함수를 호출하는 쿼리는 집계 함수가 푸시 다운되지 않는 경우에도 병렬 쿼리로부터 이점을 얻을 수 있습니다.

예를 들어, 다음 쿼리는 전체 테이블 스캔을 수행하고 P_BRAND 열의 모든 값을 처리합니다. 하지만, 쿼리가 WHERE 절을 하나도 포함하고 있지 않기 때문에 병렬 쿼리를 사용하지 않습니다.

mysql> explain select count(*), p_brand from part group by p_brand;
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
|  1 | SIMPLE      | part  | ALL  | NULL          | NULL | NULL    | NULL | 20427936 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+

반면에, 다음 쿼리는 결과를 필터링하는 WHERE 조건자를 포함하고 있으므로 병렬 쿼리가 적용될 수 있습니다.

mysql> explain select count(*), p_brand from part where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000
    -> group by p_brand;
+----+...+----------+-------------------------------------------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                                                       |
+----+...+----------+-------------------------------------------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using temporary; Using filesort; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) |
+----+...+----------+-------------------------------------------------------------------------------------------------------------+

옵티마이저가 쿼리 블록에 대해 반환되는 행의 수가 적다고 예상하는 경우, 병렬 쿼리가 해당 쿼리 블록에 사용되지 않습니다. 다음은 기본 키 열의 "~보다 큼" 연산자가 수백만의 행에 적용되고, 그로 인해 병렬 쿼리가 사용되는 사례를 보여주는 예제입니다. 정반대의 "~보다 작음" 테스트는 소수의 행에만 적용될 것으로 예상되므로 병렬 쿼리를 사용하지 않습니다.

mysql> explain select count(*) from part where p_partkey > 10;
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

mysql> explain select count(*) from part where p_partkey < 10;
+----+...+------+--------------------------+
| id |...| rows | Extra                    |
+----+...+------+--------------------------+
|  1 |...|    9 | Using where; Using index |
+----+...+------+--------------------------+

데이터 정의 언어(DDL)

Aurora MySQL 버전 2에서 병렬 쿼리는 대기 중인 빠른 데이터 정의 언어(DDL) 작업이 없는 테이블에서만 사용할 수 있습니다. Aurora MySQL 버전 3에서는 인스턴트 DDL 작업과 동시에 테이블의 병렬 쿼리를 사용할 수 있습니다.

Aurora MySQL 버전 3의 인스턴트 DDL은 Aurora MySQL 버전 2의 빠른 DDL 기능을 대체합니다. 인스턴트 DDL에 대한 자세한 내용은 인스턴트 DDL(Aurora MySQL 버전 3) 섹션을 참조하세요.

열 데이터 유형

Aurora MySQL 버전 3에서 병렬 쿼리는 TEXT, BLOB, JSON 및 GEOMETRY 데이터 유형이 있는 열을 포함하는 테이블에서 작업할 수 있습니다. 또한 선언된 최대 길이가 768바이트보다 긴 VARCHAR 및 CHAR 열에서 작업할 수 있습니다. 쿼리가 이처럼 대형 객체 유형을 포함하는 모든 열을 참조하는 경우 추가로 검색 작업을 수행하면 쿼리 처리에 일부 오버헤드가 추가됩니다. 이 경우 쿼리가 해당 열에 대한 참조를 생략할 수 있는지 확인합니다. 그렇지 않은 경우 벤치마크를 실행하여 병렬 쿼리가 설정되거나 해제된 상태에서 이러한 쿼리가 더 빠르게 수행되는지 확인합니다.

Aurora MySQL 버전 2에는 대형 객체 유형에 대해 병렬 쿼리에 다음과 같은 제한이 있습니다.

  • TEXT, BLOB, JSON 및 GEOMETRY 데이터 유형은 병렬 쿼리에서 지원되지 않습니다. 이러한 유형의 열을 참조하는 쿼리는 병렬 쿼리를 사용할 수 없습니다.

  • 가변 길이 열(VARCHAR 및 CHAR 데이터 유형)은 768바이트의 최대 선언된 길이까지 병렬 쿼리와 호환됩니다. 더 긴 최대 길이로 선언된 유형의 열을 참조하는 쿼리는 병렬 쿼리를 사용할 수 없습니다. 멀티바이트 문자 집합을 사용하는 열의 경우, 바이트 제한이 문자 집합의 최대 바이트 수를 고려합니다. 예를 들어, utf8mb4 문자 집합(최대 문자 길이 = 4바이트)의 경우 VARCHAR(192) 열은 병렬 쿼리와 호환되지만 VARCHAR(193) 열은 호환되지 않습니다.

분할된 테이블

Aurora MySQL 버전 3에서 병렬 쿼리에서 파티션된 테이블을 사용할 수 있습니다. 파티션된 테이블은 내부적으로 여러 개의 작은 테이블로 표시되므로 파티션되지 않은 테이블에서 병렬 쿼리를 사용하는 쿼리는 동일하게 파티션된 테이블에서 병렬 쿼리를 사용할 수 없습니다. Aurora MySQL은 전체 테이블의 크기를 평가하는 대신 각 파티션이 병렬 쿼리 최적화에 적합할 만큼 충분히 큰지 여부를 고려합니다. 파티션된 테이블의 쿼리가 예상될 때 병렬 쿼리를 사용하지 않는 경우 Aurora_pq_request_not_chosen_small_table 상태 변수가 증가하는지 여부를 확인합니다.

예를 들어 한 테이블을 PARTITION BY HASH (column) PARTITIONS 2로 파티션하고 다른 테이블을 PARTITION BY HASH (column) PARTITIONS 10으로 파티션하는 것을 고려합니다. 두 개의 파티션이 있는 테이블의 파티션은 10개의 파티션이 있는 테이블보다 5배 더 큽니다. 따라서 병렬 쿼리는 파티션이 적은 테이블에 대한 쿼리에 사용될 가능성이 높습니다. 다음 예에서는 테이블 PART_BIG_PARTITIONS에는 두 개의 파티션이 있으며 PART_SMALL_PARTITIONS는 10개의 파티션이 있습니다. 동일한 데이터를 사용하면 병렬 쿼리는 큰 파티션이 적은 테이블에 사용될 가능성이 높습니다.


mysql> explain select count(*), p_brand from part_big_partitions where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand;
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+
| id | select_type | table               | partitions | Extra                                                                                                             |
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | part_big_partitions | p0,p1      | Using where; Using temporary; Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra; 1 group-bys, 1 aggrs) |
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+

mysql> explain select count(*), p_brand from part_small_partitions where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand;
+----+-------------+-----------------------+-------------------------------+------------------------------+
| id | select_type | table                 | partitions                    | Extra                        |
+----+-------------+-----------------------+-------------------------------+------------------------------+
|  1 | SIMPLE      | part_small_partitions | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | Using where; Using temporary |
+----+-------------+-----------------------+-------------------------------+------------------------------+

집계 함수, GROUP BY 절 및 HAVING 절

집계 함수를 포함하는 쿼리는 대용량 테이블 내에 많은 수의 행 스캔이 필요하기 때문에, 대개 병렬 쿼리를 위한 좋은 후보가 됩니다.

Aurora MySQL 3에서 병렬 쿼리는 선택 목록 및 HAVING 절에서 집계 함수 호출을 최적화할 수 있습니다.

Aurora MySQL 3 이전에서 선택 목록 또는 HAVING 절에 있는 집계 함수 호출은 스토리지 계층으로 푸시 다운되지 않습니다. 하지만, 병렬 쿼리는 집계 함수에서도 그러한 쿼리의 성능을 여전히 향상시킬 수 있습니다. 그렇게 하기 위해서 먼저 스토리지 계층에서 병렬적으로 원시 데이터 페이지로부터 열 값을 추출합니다. 그런 다음 이러한 값을 전체 데이터 페이지가 아니라 간소화된 튜플 형식으로 다시 헤드 노드로 전송합니다. 항상 그렇듯이, 쿼리는 병렬 쿼리를 위해 최소 하나 이상의 WHERE조건자가 활성화되어야 합니다.

다음은 병렬 쿼리로부터 이점을 얻을 수 있는 집계 쿼리의 유형을 보여주는 간단한 예제입니다. 중간 결과를 간소화된 형식으로 헤드 노드에 반환하거나, 중간 결과에서 일치하지 않는 행을 필터링하거나, 혹은 둘 다를 사용함으로써 이점을 얻습니다.

mysql> explain select sql_no_cache count(distinct p_brand) from part where p_mfgr = 'Manufacturer#5';
+----+...+----------------------------------------------------------------------------+
| id |...| Extra                                                                      |
+----+...+----------------------------------------------------------------------------+
|  1 |...| Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------------------------------------------------------------------------+

mysql> explain select sql_no_cache p_mfgr from part where p_retailprice > 1000 group by p_mfgr having count(*) > 100;
+----+...+-------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                       |
+----+...+-------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using filesort; Using parallel query (3 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+-------------------------------------------------------------------------------------------------------------+

WHERE 절의 함수 호출

Aurora는 병렬 쿼리 최적화를 WHERE 절의 대부분 내장 함수에 대한 호출에 적용할 수 있습니다. 이러한 함수 호출의 병렬화는 헤드 노드에서 일부 CPU 작업을 오프로드합니다. 가장 빠른 쿼리 단계 동안 조건자 함수를 병렬적으로 평가하면 Aurora가 전송되어 이후 단계에서 처리되는 데이터의 양을 최소화하는데 도움이 됩니다.

현재, 병렬화는 선택 목록의 함수 호출에 적용되지 않습니다. 이러한 함수는 동일한 함수 호출이 WHERE 절에 나타날지라도 헤드 노드에 의해 평가됩니다. 관련 열의 원래 값은 스토리지 노드에서 다시 헤드 노드로 전송되는 튜플에 포함되어 있습니다. 헤드 노드는 결과 집합의 최종 값을 생성하기 위해 UPPER, CONCATENATE 등과 같은 변환을 수행합니다.

다음 예제에서는, LOWER에 대한 호출이 WHERE 절에 나타나기 때문에 병렬 쿼리가 이 호출을 병렬화합니다. SUBSTR 및 UPPER에 대한 호출이 선택 목록에 나타나기 때문에 병렬 쿼리가 이러한 호출에 영향을 주지 않습니다.

mysql> explain select sql_no_cache distinct substr(upper(p_name),1,5) from part
    -> where lower(p_name) like '%cornflower%' or lower(p_name) like '%goldenrod%';
+----+...+---------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                       |
+----+...+---------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+---------------------------------------------------------------------------------------------+

동일한 고려 사항이 CASE 표현식 또는 LIKE 연산자와 같은 다른 표현식에 적용됩니다. 예를 들어, 다음 예제에서는 병렬 쿼리가 CASE 절의 LIKE 표현식 및 WHERE 연산자를 평가하는 것을 보여줍니다.

mysql> explain select p_mfgr, p_retailprice from part
    -> where p_retailprice > case p_mfgr
    ->   when 'Manufacturer#1' then 1000
    ->   when 'Manufacturer#2' then 1200
    ->   else 950
    -> end
    -> and p_name like '%vanilla%'
    -> group by p_retailprice;
+----+...+-------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                       |
+----+...+-------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using filesort; Using parallel query (4 columns, 0 filters, 2 exprs; 0 extra) |
+----+...+-------------------------------------------------------------------------------------------------------------+

LIMIT 절

현재, 병렬 쿼리는 LIMIT 절을 포함하는 쿼리 블록에는 사용되지 않습니다. 병렬 쿼리는 GROUP by, ORDER BY 또는 조인이 포함된 이전 쿼리 단계에 여전히 사용될 수 있습니다.

비교 연산자

옵티마이저는 비교 연산자를 평가하기 위해 스캔해야 하는 행의 수를 추산하고, 그 추산을 기반으로 병렬 쿼리를 사용할지 여부를 결정합니다.

아래에 첫 번째 예제는 기본 키 열에 대한 "같음" 비교가 병렬 쿼리 없이도 효율적으로 수행될 수 있음을 보여줍니다. 아래에 두 번째 예제는 인덱싱되지 않은 열에 대한 "더 작음" 비교에 수백만 개 행의 스캔이 필요하며 따라서 병렬 쿼리로부터 이점을 얻을 수 있음을 보여줍니다.

mysql> explain select * from part where p_partkey = 10;
+----+...+------+-------+
| id |...| rows | Extra |
+----+...+------+-------+
|  1 |...|    1 | NULL  |
+----+...+------+-------+

mysql> explain select * from part where p_type = 'LARGE BRUSHED BRASS';
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

동일한 고려 사항이 "같지 않음" 테스트에는 적용되지 않고 ~보다 작음/~보다 큼/같음 또는 BETWEEN과 같은 범위 비교에 적용됩니다. 옵티마이저는 스캔할 행의 수를 추산하고 I/O의 전체 볼륨을 기반으로 병렬 쿼리가 가치 있는지 여부를 결정합니다.

조인

대용량 테이블에서의 조인 쿼리는 일반적으로 병렬 쿼리 최적화로부터 이점을 얻는 데이터 집약적인 작업을 포함합니다. 여러 테이블 간에 열 값의 비교는(즉, 조인 조건자 자체) 현재 병렬화되지 않습니다. 하지만, 병렬 쿼리는 해시 조인 동안 Bloom 필터 생성과 같은 다른 조인 단계를 위한 내부 처리의 일부를 푸시 다운할 수 있습니다. 병렬 쿼리는 WHERE 절 없이도 조인 쿼리에 적용될 수 있습니다. 그러므로, 조인 쿼리는 병렬 쿼리를 사용하기 위해 WHERE 절이 필요한 규칙에는 예외입니다.

조인 처리의 각 단계는 병렬 쿼리에 적격한지 여부를 확인하기 위해 평가됩니다. 둘 이상의 단계에서 병렬 쿼리를 사용할 수 있는 경우 이러한 단계는 순차적으로 수행됩니다. 따라서, 각 조인 쿼리는 동시성 한도 면에서 단일 병렬 쿼리세션으로 계산됩니다.

예를 들어, 조인 쿼리에 조인된 테이블 중 한 테이블에서 행을 필터링하기 위한 WHERE 조건자가 포함된 경우, 해당 필터링 옵션은 병렬 쿼리를 사용할 수 있습니다. 다른 예로서 예를 들어, 큰 테이블을 작은 테이블과 조인하기 위해 조인 쿼리가 해시 조인 메커니즘을 사용한다고 가정해 봅시다. 이 사례에서는, Bloom 필터 데이터 구조를 생성하기 위한 테이블 스캔이 병렬 쿼리를 사용할 수도 있습니다.

참고

병렬 쿼리는 해시 조인 최적화에서 이익을 얻는 리소스 집약적인 쿼리 유형에 일반적으로 사용됩니다. 해시 조인 최적화를 설정하는 방법은 Aurora MySQL 버전에 따라 다릅니다. 각 버전에 대한 자세한 내용은 병렬 쿼리 클러스터에 대한 해시 조인 설정 섹션을 참조하세요. 해시 조인을 효과적으로 사용하는 방법에 대한 자세한 내용은 해시 조인을 사용하여 대규모 Aurora MySQL 조인 쿼리 최적화 섹션을 참조하세요.

mysql> explain select count(*) from orders join customer where o_custkey = c_custkey;
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+
| id |...| table    | type  | possible_keys | key         |...| rows      | Extra                                                                                                           |
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+
|  1 |...| customer | index | PRIMARY       | c_nationkey |...|  15051972 | Using index                                                                                                     |
|  1 |...| orders   | ALL   | o_custkey     | NULL        |...| 154545408 | Using join buffer (Hash Join Outer table orders); Using parallel query (1 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+

중첩 루프 메커니즘을 사용하는 조인 쿼리의 경우, 가장 바깥쪽 중첩 루프 블록은 병렬 쿼리를 사용할 수도 있습니다. 병렬 쿼리의 사용은 평상시처럼 동일한 요인(예: WHERE 절에 추가 필터 조건의 유무)에 의존합니다.

mysql> -- Nested loop join with extra filter conditions can use parallel query.
mysql> explain select count(*) from part, partsupp where p_partkey != ps_partkey and p_name is not null and ps_availqty > 0;
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+
| id | select_type | table    |...| rows     | Extra                                                                      |
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | part     |...| 20427936 | Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) |
|  1 | SIMPLE      | partsupp |...| 78164450 | Using where; Using join buffer (Block Nested Loop)                         |
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+

하위 쿼리

외부 쿼리 블록 및 내부 하위 쿼리 블록은 각각 병렬 쿼리를 사용하거나 사용하지 않을 수 있습니다. 이는 각 블록에 대한 테이블의 일반적인 특성, WHERE 절 등을 기반으로 합니다. 예를 들어, 다음 쿼리는 하위 쿼리 블록에 대해서는 병렬 쿼리를 사용하지만 외부 블록에 대해서는 사용하지 않습니다.

mysql> explain select count(*) from part where
   --> p_partkey < (select max(p_partkey) from part where p_name like '%vanilla%');
+----+-------------+...+----------+----------------------------------------------------------------------------+
| id | select_type |...| rows     | Extra                                                                      |
+----+-------------+...+----------+----------------------------------------------------------------------------+
|  1 | PRIMARY     |...|     NULL | Impossible WHERE noticed after reading const tables                        |
|  2 | SUBQUERY    |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
+----+-------------+...+----------+----------------------------------------------------------------------------+

현재, 상관관계가 있는 하위 쿼리는 병렬 쿼리 최적화를 사용할 수 없습니다.

UNION

UNION 쿼리의 각 쿼리 블록은 WHERE의 각 부분에 대하여 테이블의 일반적인 특성, UNION 절 등을 기반으로 병렬 쿼리를 사용할 수 있거나 사용할 수 없습니다.

mysql> explain select p_partkey from part where p_name like '%choco_ate%'
    -> union select p_partkey from part where p_name like '%vanil_a%';
+----+----------------+...+----------+----------------------------------------------------------------------------+
| id | select_type    |...| rows     | Extra                                                                      |
+----+----------------+...+----------+----------------------------------------------------------------------------+
|  1 | PRIMARY        |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
|  2 | UNION          |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
| NULL | UNION RESULT | <union1,2> |...|     NULL | Using temporary                                           |
+----+--------------+...+----------+----------------------------------------------------------------------------+
참고

쿼리 내의 각 UNION 절은 순차적으로 실행됩니다. 쿼리가 모두 병렬 쿼리를 사용하는 여러 단계를 포함한 경우에도, 한 번에 하나의 병렬 쿼리만 실행합니다. 그러므로, 복잡한 다단계 쿼리조차도 동시 병렬 쿼리의 한도에 1로만 가산됩니다.

보기

옵티마이저는 기본 테이블을 사용하는 더 긴 쿼리로 보기를 사용하는 쿼리를 재작성합니다. 따라서, 병렬 쿼리는 테이블 참조가 보기이든 실제 테이블이든 관계 없이 동일하게 작동합니다. 쿼리에 병렬 쿼리를 사용할지 여부 및 어떤 부분을 푸시 다운할지에 대해 모두 동일한 고려 사항이 최종적으로 재작성된 쿼리에 적용됩니다.

예를 들어, 다음 쿼리 계획은 일반적으로 병렬 쿼리를 사용하지 않는 보기 정의를 보여줍니다. 보기가 추가 WHERE 절로 쿼리되는 경우 Aurora MySQL은 병렬 쿼리를 사용합니다.

mysql> create view part_view as select * from part;
mysql> explain select count(*) from part_view where p_partkey is not null;
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (1 columns, 0 filters, 0 exprs; 1 extra) |
+----+...+----------+----------------------------------------------------------------------------+

데이터 조작 언어(DML) 문

INSERT 부분이 병렬 쿼리를 위한 다른 조건에 부합하는 경우, SELECT 문이 처리의 SELECT 단계에 병렬 쿼리를 사용할 수 있습니다.

mysql> create table part_subset like part;
mysql> explain insert into part_subset select * from part where p_mfgr = 'Manufacturer#1';
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+
참고

일반적으로, INSERT 문 뒤에 새로 삽입된 행을 위한 데이터는 버퍼 풀에 있습니다. 그러므로, 테이블이 많은 수의 행을 삽입한 직후에는 병렬 쿼리에 적격하지 않을 수도 있습니다. 나중에 데이터가 정상 작동 중에 버퍼 풀에서 제거된 후, 테이블에 대한 쿼리가 다시 병렬 쿼리를 사용하기 시작할 수 있습니다.

문의 CREATE TABLE AS SELECT 부분이 다른 식으로 병렬 쿼리에 적격할 수 있는 경우에도 SELECT 문은 병렬 쿼리를 사용하지 않습니다. 이 문의 DDL 부분으로 인하여 병렬 쿼리 처리와 호환되지 않게 됩니다. 반면, INSERT ... SELECT 문에서는 SELECT 부분이 병렬 쿼리를 사용할 수 있습니다.

테이블의 크기 및 DELETE 절의 조건자에 관계 없이 UPDATE 또는 WHERE 문에는 병렬 쿼리가 사용되지 않습니다.

mysql> explain delete from part where p_name is not null;
+----+-------------+...+----------+-------------+
| id | select_type |...| rows     | Extra       |
+----+-------------+...+----------+-------------+
|  1 | SIMPLE      |...| 20427936 | Using where |
+----+-------------+...+----------+-------------+

트랜잭션 및 잠금

Aurora 기본 인스턴스에서 모든 격리 수준을 사용할 수 있습니다.

Aurora 리더 DB 인스턴스의 경우 REPEATABLE READ 격리 수준에서 수행되는 문에 병렬 쿼리가 적용됩니다. 또한 Aurora MySQL 버전2.09 이상에서는 리더 DB 인스턴스에서 READ COMMITTED 격리 수준을 사용할 수 있습니다. REPEATABLE READ는 Aurora 리더 DB 인스턴스에 대한 기본 격리 수준입니다. 읽기 전용 DB 인스턴스에서 READ COMMITTED 격리 수준을 사용하려면 세션 수준에서 aurora_read_replica_read_committed 구성 옵션을 설정해야 합니다. 리더 인스턴스의 READ COMMITTED 격리 수준은 SQL 표준 동작을 준수합니다. 그러나 리더 인스턴스의 격리는 쿼리가 작성자 인스턴스에서 READ COMMITTED 격리를 사용하는 경우보다 덜 엄격합니다.

Aurora 격리 수준, 특히 작성자 인스턴스와 리더 인스턴스 간 READ COMMITTED 차이점에 대한 자세한 내용은 Aurora MySQL 격리 수준 섹션을 참조하세요.

대형 트랜잭션이 완료된 후에는 테이블 통계가 기한 경과될 수 있습니다. Aurora가 행의 수를 정확하게 추산할 수 있으려면 먼저 그러한 기한 경과 통계에 ANALYZE TABLE 문이 필요할 수 있습니다. 대규모 DML 문 또한 테이블 데이터의 상당 부분을 버퍼 풀로 가져올 수 있습니다. 이러한 데이터가 버퍼 풀에 있으면 데이터가 풀에서 제거될 때까지 해당 테이블에 대해 병렬 쿼리가 덜 자주 선택될 수 있습니다.

세션이 장기 실행 트랙잭션(기본적으로 10분) 내부에 있는 경우, 해당 세션 내부의 추가 쿼리는 병렬 쿼리를 사용하지 않습니다. 또한 단일 장기 실행 쿼리 동안 시간 초과가 발생할 수 있습니다. 병렬 쿼리 처리가 시작되기 전에 쿼리가 최대 간격(현재 10분)보다 더 오래 실행되는 경우 이러한 유형의 시간 초과가 발생할 수 있습니다.

임시(1회) 쿼리를 수행하는 autocommit=1 세션에 mysql을 설정하여 실수로 장기 실행 트랜잭션을 시작할 가능성을 줄일 수 있습니다. 읽기 보기를 만들면 테이블에 대한 SELECT 문도 트랜잭션을 시작합니다. 읽기 보기는 트랜잭션이 커밋될 때까지 남아있는 후속 쿼리를 위한 일관된 데이터 집합입니다. Aurora에서 JDBC 또는 ODBC 애플리케이션을 사용할 때도 이러한 제한에 유의하십시오. 이러한 애플리케이션은 autocommit 설정이 꺼진 상태에서도 실행될 수 있기 때문입니다.

다음은 autocommit 설정이 꺼진 상태에서, 테이블에 대한 쿼리 실행이 트랜잭션을 암시적으로 시작하는 읽기 보기를 생성하는 방법을 보여주는 예제입니다. 그 후에 짧게 실행되는 쿼리는 여전히 병렬 쿼리를 사용할 수 있습니다. 하지만, 몇 분간 일시 정지 후에는 쿼리가 병렬 쿼리에 더 이상 적격하지 않습니다. COMMIT 또는 ROLLBACK으로 트랜잭션을 종료하면 병렬 쿼리 자격을 복원합니다.

mysql> set autocommit=0;

mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+----+...+---------+----------------------------------------------------------------------------+
| id |...| rows    | Extra                                                                      |
+----+...+---------+----------------------------------------------------------------------------+
|  1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+---------+----------------------------------------------------------------------------+

mysql> select sleep(720); explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+------------+
| sleep(720) |
+------------+
|          0 |
+------------+
1 row in set (12 min 0.00 sec)

+----+...+---------+-------------+
| id |...| rows    | Extra       |
+----+...+---------+-------------+
|  1 |...| 2976129 | Using where |
+----+...+---------+-------------+

mysql> commit;

mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+----+...+---------+----------------------------------------------------------------------------+
| id |...| rows    | Extra                                                                      |
+----+...+---------+----------------------------------------------------------------------------+
|  1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+---------+----------------------------------------------------------------------------+

쿼리가 장기 실행 트랜잭션 내부에 있었기 때문에 병렬 쿼리에 적격하지 않은 횟수를 확인하려면 상태 변수 Aurora_pq_request_not_chosen_long_trx를 확인합니다.

mysql> show global status like '%pq%trx%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Aurora_pq_request_not_chosen_long_trx | 4     |
+-------------------------------+-------+

SELECT 또는 SELECT FOR UPDATE 구문과 같은 잠금을 획득하는 SELECT LOCK IN SHARE MODE 문은 병렬 쿼리를 사용할 수 없습니다.

병렬 쿼리는 LOCK TABLES 문에 의해 잠겨진 테이블을 위해 작동할 수 있습니다.

mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055';
+----+...+-----------+----------------------------------------------------------------------------+
| id |...| rows      | Extra                                                                      |
+----+...+-----------+----------------------------------------------------------------------------+
|  1 |...| 154545408 | Using where; Using parallel query (3 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+-----------+----------------------------------------------------------------------------+

mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055' for update;
+----+...+-----------+-------------+
| id |...| rows      | Extra       |
+----+...+-----------+-------------+
|  1 |...| 154545408 | Using where |
+----+...+-----------+-------------+

B-트리 인덱스

ANALYZE TABLE 문에 의해 수집된 통계는 옵티마이저가 각 열에 대한 데이터의 특성을 기반으로 병렬 쿼리 또는 인덱스 조회를 사용하는 경우를 결정하도록 도와줍니다. 테이블 내의 데이터에 상당한 변경을 적용하는 DML 작업 후에 ANALYZE TABLE을 실행하여 통계를 현재 상태로 유지하십시오.

인덱스 조회가 데이터 집약적인 스캔 없이도 효율적으로 쿼리를 수행할 수 있는 경우 Aurora은 인덱스 조회를 사용할 수도 있습니다. 그렇게 하면 병렬 쿼리 처리의 오버헤드를 피할 수 있습니다. 또한 Aurora DB 클러스터에서 동시에 실행될 수 있는 병렬 쿼리의 수에 대한 동시성 제한도 있습니다. 가장 빈번하게 가장 많이 사용되는 동시 쿼리가 인덱스 조회를 사용하도록, 테이블 인덱싱을 위한 모범 사례를 사용하십시오.

전체 텍스트 검색(FTS) 인덱스

현재, 병렬 쿼리는 전체 텍스트 검색 인덱스를 포함한 테이블에 사용되지 않습니다(쿼리가 그러한 인덱싱된 열을 참조하든 MATCH 연산자를 사용하든 관계 없음).

가상 열

현재 쿼리가 가상 열을 참조하는지 여부에 관계없이 가상 열이 포함된 테이블에는 병렬 쿼리가 사용되지 않습니다.

내장된 캐싱 메커니즘

Aurora에는 내장된 캐싱 메커니즘(즉, 버퍼 풀 및 쿼리 캐시)이 포함되어 있습니다. Aurora 옵티마이저는 어떤 것이 특정 쿼리에 가장 효과적인지에 따라서 이러한 캐싱 메커니즘과 병렬 쿼리 중에서 선택합니다.

병렬 쿼리가 행을 필터링하고 열 값을 변환 및 추출할 때, 데이터는 데이터 페이지가 아니라 튜플로 다시 헤드 노드에 전송됩니다. 그러므로, 병렬 쿼리를 실행해도 버퍼 풀에 페이지가 추가되거나 버퍼 풀에 이미 존재하는 페이지가 제거되지 않습니다.

Aurora는 버퍼 풀에 있는 테이블 데이터의 페이지 수와 해당 숫자가 나타내는 테이블 데이터의 비율을 확인합니다. Aurora는 이 정보를 사용하여 병렬 쿼리(및 버퍼 풀의 데이터 우회)를 사용하는 것이 더 효율적인지 여부를 결정합니다. 또는 Aurora은 버퍼 풀에 캐시된 데이터를 사용하는 비병렬 쿼리 처리 경로를 사용할 수도 있습니다. 어떤 페이지가 캐시되고 데이터 집약적인 쿼리가 캐싱 및 제거에 어떠한 영향을 미치는지는 버퍼 풀에 관련된 구성 설정에 따라 다릅니다. 따라서 선택은 버퍼 풀 내에서 끊임없이 변하는 데이터에 달려 있기 때문에, 특정 쿼리가 병렬 쿼리를 사용할지 여부를 예측하기는 어려울 수 있습니다.

또한, Aurora은 병렬 쿼리에 동시성 한도를 적용합니다. 모든 쿼리가 병렬 쿼리를 사용하는 것은 아니기 때문에, 여러 쿼리에 의해 동시에 액세스되는 테이블은 일반적으로 버퍼 풀에 데이터의 상당 부분이 있습니다. 따라서, Aurora은 대개 병렬 쿼리를 위해 이러한 테이블은 선택하지 않습니다.

동일한 테이블에서 비병렬 쿼리의 시퀀스를 실행하는 경우, 첫 번째 쿼리는 데이터가 버퍼 풀에 없어서 오래 걸릴 수 있습니다. 그런 다음 두 번째 및 이후 쿼리는 버퍼 풀이 이제 "워밍업"되어서 훨씬 더 빨라집니다. 병렬 쿼리는 일반적으로 테이블에 대한 맨 첫 번째 쿼리부터 일관된 성능을 보여줍니다. 성능 테스트를 수행할 때는 콜드(cold) 버퍼 풀과 웜(warm) 버퍼 풀에서 모두 비병렬 쿼리를 벤치마크합니다. 일부 경우, 웜 버퍼 풀에서의 결과는 병렬 쿼리 시간과 잘 비교될 수 있습니다. 이러한 경우 해당 테이블에 대한 쿼리 빈도와 같은 요소를 고려합니다. 또한 버퍼 풀에 해당 테이블의 데이터를 유지하는 것이 가치가 있는지 여부를 고려해야 합니다.

동일한 쿼리가 제출되고 기본 테이블 데이터가 변경되지 않은 경우 쿼리 캐시는 쿼리 재실행을 방지합니다. 병렬 쿼리 기능에 의해 최적화된 쿼리는 쿼리 캐시로 이동할 수 있어, 쿼리가 다시 실행될 때 즉각적으로 실행되므로 효율적입니다.

참고

성능 비교를 수행할 때, 쿼리 캐시가 인위적으로 낮은 시간 지정 숫자를 생성할 수 있습니다. 따라서, 벤치마크 같은 상황에서는 sql_no_cache 힌트를 사용할 수 있습니다. 이 힌트는 이전에 동일한 쿼리가 실행된 경우에도, 결과가 쿼리 캐시로부터 제공되지 않도록 합니다. 힌트는 쿼리의 SELECT 문 바로 뒤에 옵니다. 병렬 쿼리가 설정 및 해제된 쿼리의 버전들 간에 쿼리 시간이 비교 가능해지도록 이 주제의 많은 병렬 쿼리 예에 이 힌트가 포함되어 있습니다.

병렬 쿼리의 프로덕션용으로 이동할 때는 원본에서 이 힌트를 제거해야 합니다.

최적화 프로그램 힌트

최적화 프로그램을 제어하는 또 다른 방법은 개별 문 내에서 지정할 수 있는 최적화 프로그램 힌트를 사용하는 것입니다. 예를 들어, 문 안에 있는 한 테이블에 대해 최적화를 설정한 다음 다른 테이블에 대한 최적화를 끌 수 있습니다. 이러한 힌트에 대한 자세한 내용은 MySQL 참조 설명서의 최적화 프로그램 힌트를 참조하세요.

Aurora MySQL 쿼리와 함께 SQL 힌트를 사용하여 성능을 미세 조정할 수 있습니다. 또한 힌트를 사용하여 예기치 않은 조건으로 인해 중요한 쿼리에 대한 실행 계획이 변경되는 것을 방지할 수 있습니다.

쿼리 계획에 대한 최적화 프로그램 선택 항목을 제어할 수 있도록 SQL 힌트 기능을 확장했습니다. 이러한 힌트는 병렬 쿼리 최적화를 사용하는 쿼리에 적용됩니다. 자세한 내용은 Aurora MySQL 힌트 단원을 참조하십시오.

MyISAM 임시 테이블

병렬 쿼리 최적화는 InnoDB 테이블에만 적용됩니다. Aurora MySQL은 임시 테이블을 위해 배후에서 MyISAM을 사용하기 때문에, 임시 테이블을 포함하는 내부 쿼리 단계는 병렬 쿼리를 사용합니다. 이러한 쿼리 단계는 Using temporary 출력에서 EXPLAIN로 표시됩니다.