1. 랩 모드 사용
Aurora 랩 모드는 현재 Aurora 데이터베이스 버전에서 사용 가능한 Aurora 기능을 활성화하는 데 사용되지만 기본적으로 비활성화되어 있습니다. 프로덕션 DB 클러스터에서 Aurora 랩 모드 기능을 사용하지 않는 것이 좋지만 개발 및 테스트 환경에서 Aurora 랩 모드를 사용하여 DB 클러스터에 대해 이러한 기능을 활성화할 수 있습니다.
aurora_lab_mode 파라미터는 기본 파라미터 그룹에 속하는 인스턴스 수준 파라미터입니다. 기본 파라미터 그룹에서는 이 파라미터가 0(비활성)으로 설정됩니다. Aurora 랩 모드를 활성화하려면 사용자 지정 파라미터 그룹을 생성하고 사용자 지정 파라미터 그룹에서 aurora_lab_mode 파라미터를 1(활성)로 설정한 후, 사용자 지정 파라미터 그룹을 사용하도록 Aurora 클러스터에서 하나 이상의 DB 클러스터를 수정합니다. 그런 다음, 랩 모드 기능을 시도하기 위해 해당되는 인스턴스 엔드포인트에 연결합니다.
1.1. Aurora 랩 모드 기능
배치화 스캔 :
Aurora MySQL 스캔 배치화는 인 메모리 스캔 지향 쿼리의 속도를 크게 높입니다. 이 기능은 일괄 처리로 테이블 전체 스캔, 인덱스 전체 스캔 및 인덱스 범위 스캔의 성능을 향샹시킵니다.
해시 조인 :
이 기능은 동등 조인을 사용하여 많은 양의 데이터를 조인해야 하는 경우 쿼리 성능을 향상시킬 수 있습니다. Aurora MySQL 버전 1에는 랩 모드가 필요합니다. Aurora MySQL 버전 2에는 랩 모드 없이 이 기능을 사용할 수 있습니다.
빠른 DDL :
이 기능을 사용하면 ALTER TABLE tbl_name ADD COLUMN col_name column_definition 작업을 거의 동시에 실행할 수 있습니다. 이 작업은 테이블을 복사하거나 다른 DML 명령문에 영향을 거의 주지 않고 완료됩니다. 테이블 복사를 위해 임시 스토리지를 사용하지 않으므로 라지 테이블에 대해서도 DDL 문을 쉽게 만듭니다. 현재 빠른 DDL은 테이블 끝에서 기본값 없이 null이 허용된 열에 대해서만 지원됩니다.
2. 비동기식 키 미리 가져오기
Amazon Aurora은 Async Key Prefetch(AKP)를 사용하여 여러 인덱스 사이에 테이블을 조인하는 쿼리 성능을 높일 수 있습니다. 이 기능은 JOIN 쿼리에서 Batched Key Access(BKA) 조인 알고리즘과 Multi-Range Read(MRR) 최적화 기능을 사용해야 하는 쿼리를 실행하면서 필요한 행을 예측하여 성능을 높이는 효과가 있습니다.
쿼리가 AKP 기능을 이용하기 위해서는 BKA와 MRR이 모두 필요합니다. 일반적으로 JOIN 절이 보조 인덱스를 사용하지만 기본 인덱스의 열도 일부 필요할 때 이러한 쿼리가 발생합니다. 예를 들어 JOIN 절이 작은 용량의 외부 테이블과 큰 용량의 내부 테이블 사이에서 인덱스 값을 기준으로 한 등가 조인을 나타내고, 테이블 용량이 커질수록 인덱스 선택의 폭이 매우 제한적일 때 AKP를 사용할 수 있습니다. AKP는 JOIN 절을 평가하는 동안 BKA 및 MRR과 함께 보조-기본 인덱스 조회를 실행합니다. 동시에 쿼리를 실행하는 데 필요한 행까지 식별합니다. 그런 다음 쿼리를 실행하기에 앞서 백그라운드 스레드를 사용하여 식별된 행이 포함된 페이지를 메모리에 비동기식으로 로드합니다.
EXPLAIN 문 출력에서 Extra 열은 실행 계획에 추가되는 정보에 대한 설명입니다. AKP 기능이 쿼리에 사용할 테이블에 적용되는 경우 이 열에 다음 값 중 하나가 포함됩니다.
- Using Key Prefetching
- Using join buffer (Batched Key Access with Key Prefetching)
다음은 EXPLAIN 문에 EXTENDED 키워드를 사용하여 AKP를 이용할 수 있는 쿼리의 실행 계획을 확인하는 예제입니다.
mysql> explain extended select sql_no_cache
-> ps_partkey,
-> sum(ps_supplycost * ps_availqty) as value
-> from
-> partsupp,
-> supplier,
-> nation
-> where
-> ps_suppkey = s_suppkey
-> and s_nationkey = n_nationkey
-> and n_name = 'ETHIOPIA'
-> group by
-> ps_partkey having
-> sum(ps_supplycost * ps_availqty) > (
-> select
-> sum(ps_supplycost * ps_availqty) * 0.0000003333
-> from
-> partsupp,
-> supplier,
-> nation
-> where
-> ps_suppkey = s_suppkey
-> and s_nationkey = n_nationkey
-> and n_name = 'ETHIOPIA'
-> )
-> order by
-> value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| 1 | PRIMARY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3_scale_10.nation.n_nationkey | 2057 | 100.00 | Using index |
| 1 | PRIMARY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3_scale_10.supplier.s_suppkey | 42 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
| 2 | SUBQUERY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 100.00 | Using where |
| 2 | SUBQUERY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3_scale_10.nation.n_nationkey | 2057 | 100.00 | Using index |
| 2 | SUBQUERY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3_scale_10.supplier.s_suppkey | 42 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)3. 해시 조인 작업
동등 조인을 사용하여 많은 양의 데이터를 조인해야 하는 경우 해시 조인을 통해 쿼리 성능을 향상시킬 수 있습니다. Aurora MySQL의 해시 조인을 활성화할 수 있습니다.
해시 조인 열은 복잡한 표현식이 될 수 있습니다. 해시 조인 열에서 다음과 같은 방식으로 데이터 유형을 비교할 수 있습니다.
- int, bigint, numeric 및 bit 등과 같은 정확한 숫자 데이터 형식 범주의 모든 항목을 비교할 수 있습니다.
- float 및 double과 같은 대략적인 숫자 데이터 형식 범주의 모든 항목을 비교할 수 있습니다.
- 문자열 유형에 동일한 문자 세트와 콜레이션이 있는 경우 문자열 유형간에 항목을 비교할 수 있습니다.
- 유형이 동일한 경우 날짜 및 타임스탬프 데이터 형식으로 항목을 비교할 수 있습니다.
Aurora MySQL의 해시 조인에는 다음의 제한 사항이 적용됩니다.
- left/right outer 조인은 지원되지 않습니다.
- 하위 쿼리가 구체화되지 않는 한 하위 쿼리와 같은 Semijoin은 지원되지 않습니다.
- 다중 테이블의 업데이트 또는 삭제는 지원되지 않습니다.
- BLOB 및 공간 데이터 타입 열은 해시 조인의 조인 할 수 없습니다.
해시 조인이 쿼리에 사용할 테이블에 적용되는 경우 이 열에 다음과 비슷한 값이 포함됩니다.
- Using where; Using join buffer (Hash Join Outer table table1_name)
- Using where; Using join buffer (Hash Join Inner table table2_name)
다음은 EXPLAIN을 사용하여 해시 조인 쿼리의 실행 계획을 확인하는 예제입니다.
mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
-> WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| 1 | SIMPLE | hj_small | ALL | NULL | NULL | NULL | NULL | 6 | Using temporary; Using filesort |
| 1 | SIMPLE | hj_big | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (Hash Join Outer table hj_big) |
| 1 | SIMPLE | hj_big2 | ALL | NULL | NULL | NULL | NULL | 15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)출력에서 Hash Join Inner table은 해시 테이블을 작성하는 데 사용하는 테이블이며 Hash Join Outer table은 해시 테이블을 프로브하는 데 사용하는 테이블입니다.
4. Aurora MySQL 힌트
Aurora MySQL 쿼리와 함께 SQL 힌트를 사용하여 성능을 미세 조정할 수 있습니다. 또한 힌트를 사용하여 예기치 않은 조건에 따라 중요한 쿼리에 대한 실행 계획이 변경되는 것을 방지할 수 있습니다.
4.1. HASH_JOIN, NO_HASH_JOIN
쿼리에 해시 조인 최적화 방법을 사용할지 여부를 선택할 수 있는 최적화 프로그램의 기능을 설정하거나 해제합니다. HASH_JOIN을 사용하면 해당 메커니즘이 더 효율적인 경우 최적화 프로그램이 해시 조인을 사용할 수 있습니다. NO_HASH_JOIN은 최적화 프로그램이 쿼리에 해시 조인을 사용하지 못하게 합니다.
다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.
EXPLAIN SELECT/*+ HASH_JOIN(t2) */ f1, f2
FROM t1, t2 WHERE t1.f1 = t2.f1;
EXPLAIN SELECT /*+ NO_HASH_JOIN(t2) */ f1, f2
FROM t1, t2 WHERE t1.f1 = t2.f1;4.2. HASH_JOIN_PROBING, NO_HASH_JOIN_PROBING
해시 조인 쿼리에서 조인의 프로브 측에 지정된 테이블을 사용할지 여부를 지정합니다. 쿼리는 프로브 테이블의 전체 내용을 읽는 대신 빌드 테이블의 열 값이 프로브 테이블에 있는지 여부를 테스트합니다. HASH_JOIN_PROBING 및 HASH_JOIN_BUILDING을 사용하여 쿼리 텍스트 내에서 테이블을 재정렬하지 않고 해시 조인 쿼리가 처리되는 방법을 지정할 수 있습니다.
다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다. T2 테이블에 HASH_JOIN_PROBING 힌트를 지정하면 T1 테이블에 NO_HASH_JOIN_PROBING을 지정하는 것과 같은 효과가 있습니다.
EXPLAIN SELECT /*+ HASH_JOIN(t2) HASH_JOIN_PROBING(t2) */ f1, f2
FROM t1, t2 WHERE t1.f1 = t2.f1;
EXPLAIN SELECT /*+ HASH_JOIN(t2) NO_HASH_JOIN_PROBING(t1) */ f1, f2
FROM t1, t2 WHERE t1.f1 = t2.f1;
4.3. HASH_JOIN_BUILDING, NO_HASH_JOIN_BUILDING
해시 조인 쿼리에서 조인의 빌드 측에 지정된 테이블을 사용할지 여부를 지정합니다. 쿼리는 이 테이블의 모든 행을 처리하여 다른 테이블과 상호 참조할 열 값 목록을 작성합니다. HASH_JOIN_PROBING 및 HASH_JOIN_BUILDING을 사용하여 쿼리 텍스트 내에서 테이블을 재정렬하지 않고 해시 조인 쿼리가 처리되는 방법을 지정할 수 있습니다.
다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다. T2 테이블에 HASH_JOIN_BUILDING 힌트를 지정하면 T1 테이블에 NO_HASH_JOIN_BUILDING을 지정하는 것과 같은 효과가 있습니다.
EXPLAIN SELECT /*+ HASH_JOIN(t2) HASH_JOIN_BUILDING(t2) */ f1, f2
FROM t1, t2 WHERE t1.f1 = t2.f1;
EXPLAIN SELECT /*+ HASH_JOIN(t2) NO_HASH_JOIN_BUILDING(t1) */ f1, f2
FROM t1, t2 WHERE t1.f1 = t2.f1;
4.4. JOIN_FIXED_ORDER
쿼리의 테이블이 쿼리에 나열된 순서에 따라 조인되도록 지정합니다. 이는 세 개 이상의 테이블을 포함하는 쿼리에 특히 유용합니다. 이는 MySQL STRAIGHT_JOIN 힌트를 대체하기 위한 것입니다. MySQL JOIN_FIXED_ORDER 힌트에 해당합니다.
다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.
EXPLAIN SELECT /*+ JOIN_FIXED_ORDER */ f1, f2
FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);
4.5. JOIN_ORDER
쿼리의 테이블에 대한 조인 순서를 지정합니다. 이는 세 개 이상의 테이블을 포함하는 쿼리에 특히 유용합니다.
다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.
EXPLAIN SELECT /*+ JOIN_ORDER (t4, t2, t1, t3) */ f1, f2
FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);
4.6. JOIN_PREFIX
조인 순서에서 먼저 넣을 테이블을 지정합니다. 이는 세 개 이상의 테이블을 포함하는 쿼리에 특히 유용합니다.
다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.
EXPLAIN SELECT /*+ JOIN_ORDER (t4, t2) */ f1, f2
FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);
4.7. JOIN_SUFFIX
조인 순서에 마지막으로 넣을 테이블을 지정합니다. 이는 세 개 이상의 테이블을 포함하는 쿼리에 특히 유용합니다.
다음 예제에서는 이 힌트를 사용하는 방법을 보여 줍니다.
EXPLAIN SELECT /*+ JOIN_ORDER (t1, t3) */ f1, f2
FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id);'ETC DB' 카테고리의 다른 글
| prometheus 개요 (0) | 2021.07.21 |
|---|---|
| Amazon Aurora DB와 mysql 차이점 (0) | 2021.07.21 |
| Amazon Aurora 스토리지 (0) | 2021.07.21 |
| Amazon Aurora 정의 (0) | 2021.07.21 |
| Amazon Aurora 정리 (요약) (0) | 2021.07.21 |
