Child pages
  • Optimizing Performance Based on Query Execution Plans

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Imported Korean translation
Sv translation
languageen

A query can have multiple valid execution plans. For example, there might be several indexes to choose from when reading rows out of a table. The ClustrixDB query optimizer examines these options to determine the most efficient execution plan.

The optimizer transforms an SQL query into a set of building blocks called operators . Each operator performs a basic operation such as scanning an index or filtering rows based on a predicate. The operators are arranged into a query execution plan in the form of a tree, where the root of the tree represents any rows possibly returned by the query.

To display the execution plan that was chosen for a query, issue the EXPLAIN command. 

Code Block
languagesql
titleExplain command example
mysql> CREATE TABLE foo (a int, b int);
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO foo VALUES (5,10);
Query OK, 1 row affected (0.04 sec)

mysql> EXPLAIN SELECT * FROM foo WHERE a = 5;
+------------------------------------+-----------+-----------+
| Operation                          | Est. Cost | Est. Rows |
+------------------------------------+-----------+-----------+
| stream_combine                     |     31.72 |      0.91 | 
|   filter (1.a = param(0))          |     10.21 |      0.30 | 
|     index_scan 1 := foo.__base_foo |     10.20 |      0.34 | 
+------------------------------------+-----------+-----------+
3 rows in set (0.00 sec)


The Operation column lists two operators: filter and index_scan. The index_scan entry is indented to indicate that it is a child of the filter, which means that the rows created by the index scan are passed through the filter. The line "index_scan 1 := foo._base_foo" means that the index base_foo is read from the table foo, and the unique ID of 1 is assigned to this operation. This operator returns all rows found in base_foo. Because no primary key was specified when foo was created, the ClustrixDB created a hidden primary key for foo's primary index, _base_foo.
The filter returns only rows that pass the predicate "a = 5" .

The ClustrixDB query optimizer converts the value of 5 into a parameter, enabling it to use same execution plan for similar queries without recompiling the query (for example, 5 can be replaced with 7 or 100 without requiring recompilation). The predicate in the filter is represented as (1.a eq param((0 0))), and 1 is the unique ID assigned to _base_foo, so 1.a represents the column a from index _base_foo. The last part, param((0 0)), represents parameter 0 (plus a second argument for internal use). The plan returns rows where column foo.a equals parameter 0.

To improve query efficiency, add an index to column a and examine its effect:

Code Block
mysql> CREATE INDEX idx_a ON foo (a);
Query OK, 0 rows affected (0.73 sec)


mysql> EXPLAIN SELECT a FROM foo WHERE a = 5;
+-----------------------------------------+-----------+-----------+
| Operation                               | Est. Cost | Est. Rows |
+-----------------------------------------+-----------+-----------+
| index_scan 1 := foo.idx_a, a = param(0) |     10.61 |      1.01 | 
+-----------------------------------------+-----------+-----------+
1 row in set (0.01 sec)


The query now reads from the new index, so the optimizer pushed the predicate down from the filter into the index scan. The filter is still displayed but no longer performs any processing.
The cost and rows columns in the EXPLAIN output are estimates for the associated operators in the execution plan. Est. Cost is a relative cost, and therefore is not displayed in units such as milliseconds or CPU cycles. Est. Rows is an estimate of the number of rows returned by the operator. These estimates are based on the properties of the operator and the number of rows that are returned by any children of that operator.

For example, consider the above plan for SELECT * FROM foo WHERE a = 5.

The estimate for rows returned from the index scan is based on statistics that ClustrixDB maintains for the table foo and the index __base_foo. These statistics are automatically refreshed periodically. (You cannot manually force ClustrixDB to refresh statistics.)


Here's a more complex example:

Code Block
languagesql
mysql> CREATE TABLE bar (x int PRIMARY KEY, y int);
Query OK, 0 rows affected (0.15 sec)


mysql> INSERT INTO bar VALUES (1,10), (2,20), (3,20), (4,20);
Query OK, 4 rows affected (0.02 sec)


mysql> CREATE TABLE baz (r int);
Query OK, 0 rows affected (0.14 sec)
 
mysql> INSERT INTO baz VALUES (1), (1), (2), (9);
Query OK, 4 rows affected (0.02 sec)


mysql> EXPLAIN SELECT x, SUM(y) FROM bar JOIN baz ON x = r GROUP BY x;
+-----------------------------------------------------------------------+-----------+-----------+
| Operation                                                             | Est. Cost | Est. Rows |
+-----------------------------------------------------------------------+-----------+-----------+
| hash_aggregate_combine GROUPBY((1 . "x")) expr0 := sum((0 . "expr0")) |     81.80 |      4.00 | 
|   hash_aggregate_partial GROUPBY((1 . "x")) expr0 := sum((1 . "y"))   |     76.20 |      4.00 | 
|     nljoin                                                            |     76.20 |      4.00 | 
|       stream_combine                                                  |     33.80 |      4.00 | 
|         index_scan 2 := baz.__base_baz                                |     10.80 |      1.33 | 
|       index_scan 1 := bar.__idx_bar__PRIMARY, x = 2.r                 |     10.60 |      1.00 | 
+-----------------------------------------------------------------------+-----------+-----------+
6 rows in set (0.02 sec)


The operator nljoin takes each row from its first input and joins it with each row from its second input. dual is an internal operator that enforces good join ordering. The inner nljoin is equivalent to the index scan on baz._base_baz. The outer nljoin reads from baz.base_baz first, and then joins with bar.idx_bar_PRIMARY. The presence of x = 2.r in the latter's index scan indicates a seek operation, meaning a full table scan is avoided.

Sv translation
languageko

쿼리는 여러 유효한 실행 플랜을 가질 수 있습니다. 예를 들어, 테이블에서 행을 읽을 때 여러개의 색인을 선택해야 할 경우가 있습니다. ClustrixDB 쿼리 옵티마이저는(optimizer) 이러한 옵션들을 검토하여 가장 효율적인 실행 플랜을 결정합니다.

옵티마이저는 SQL 쿼리를 오퍼레이터라고(operator) 불리는 빌딩 블록 세트로 변환합니다. 각 오퍼레이터는 인덱스 스캔과 조건문에(predicate) 기초한 행 필터링 등의 기본 작업을 수행합니다. 오퍼레이터는 쿼리 실행 플랜에 트리 형태로 배치되고 트리의 루트는 쿼리에서 반환 될 수 있는 행을 나타냅니다.

주어진 쿼리의 실행 플랜을 표시하려면 EXPLAIN 명령을 실행합니다.

Code Block
languagesql
titleExplain 구문 예
mysql> CREATE TABLE foo (a int, b int);
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO foo VALUES (5,10);
Query OK, 1 row affected (0.04 sec)

mysql> EXPLAIN SELECT * FROM foo WHERE a = 5;
+------------------------------------+-----------+-----------+
| Operation                          | Est. Cost | Est. Rows |
+------------------------------------+-----------+-----------+
| stream_combine                     |     31.72 |      0.91 | 
|   filter (1.a = param(0))          |     10.21 |      0.30 | 
|     index_scan 1 := foo.__base_foo |     10.20 |      0.34 | 
+------------------------------------+-----------+-----------+
3 rows in set (0.00 sec)

Operation 열에는 filterindex_scan 두 개의 오퍼레이터가 나열되어 있습니다. index_scan 항목은 들여쓰기를 통해서 filter의 하위 항목임을 나타냅니다. 즉, index_scan에 의해 생성 된 행은 filter를 통과합니다. "index_scan 1 : = foo._base_foo"줄은 인덱스 base_foo가 테이블 foo에서 읽혔고 고유 ID 1이 읽기 작업에 할당 된 것을 의미합니다. 이 오퍼레이터는 base_foo에서 찾은 모든 행을 리턴합니다. foo를 만들 때 기본키가 지정되어 있지 않았기 때문에 ClustrixDB는 foo의 기본 색인인 _base_foo를 위해서 내부용 기본 키를 만들었습니다.
Filter는 조건문 "a = 5"를 충족한 행만 반환합니다.

ClustrixDB 쿼리 옵티마이저는 5를 매개 변수로 변환하기 때문에 동일한 쿼리에 대해서 다시 컴파일하지 않고 같은 실행 플랜을 사용할 수 있도록합니다 (예를 들어, 매개 변수 값 5는 재 컴파일 없이 7 또는 100으로 대체 할 수 있습니다). Filter의 조건은 (1.a = param (0))로 표시되며, 1은 _base_foo 에 할당 된 고유 ID입니다. 즉, 1.a는 인덱스 _base_foo 의 열 a를 나타냅니다. 마지막 부분 param(0)는 매개 변수 0을 나타냅니다. 플랜은 foo.a 열의 값이 매개 변수 0과 동일한 행만 반환합니다

쿼리의 효율성을 향상시키기 위해 열 a에 인덱스를 추가하고 효과를 확인합니다.

Code Block
mysql> CREATE INDEX idx_a ON foo (a);
Query OK, 0 rows affected (0.73 sec)


mysql> EXPLAIN SELECT a FROM foo WHERE a = 5;
+-----------------------------------------+-----------+-----------+
| Operation                               | Est. Cost | Est. Rows |
+-----------------------------------------+-----------+-----------+
| index_scan 1 := foo.idx_a, a = param(0) |     10.61 |      1.01 | 
+-----------------------------------------+-----------+-----------+
1 row in set (0.01 sec)


이제부터는 쿼리가 새로운 색인에서 읽기 때문에 옵티마이저는 조건문을 filter에서 index_scan으로 내렸습니다. Fileter는 아직 표시는 되지만 아무런 실행을 하지 않습니다.
EXPLAIN의 가격과 행 열은 실행 플랜에 관련된 오퍼레터의(operator) 비용 견적입니다. Est. Cost는 상대적 비용이기 때문에 밀리 세컨드나 CPU 사이클 등의 단위로 표시되지 않습니다. Est. Rows는 오퍼레이터에 의해 리턴되는 행 수의 견적입니다. 이러한 비용 견적은 오퍼레이터의 속성 및 하위의 종속 오퍼레이터 의해 반환되는 행 수를 기준으로합니다.

예를 들어, 위의 SELECT * FROM foo WHERE a = 5의 플랜을 살펴봅시다.
인덱스 스캔에서 리턴 할 행에 대한 견적은 ClustrixDB가 테이블 foo와 인덱스 __base_foo에 유지하는 통계자료를 기반으로합니다. 이러한 통계자료는 정기적으로 자동 업데이트됩니다. (수동으로 ClustrixDB에 통계 정보를 업데이트 하도록 강제할 수 없습니다).

더 복잡한 예제가 있습니다:

Code Block
languagesql
mysql> CREATE TABLE bar (x int PRIMARY KEY, y int);
Query OK, 0 rows affected (0.15 sec)


mysql> INSERT INTO bar VALUES (1,10), (2,20), (3,20), (4,20);
Query OK, 4 rows affected (0.02 sec)


mysql> CREATE TABLE baz (r int);
Query OK, 0 rows affected (0.14 sec)
 
mysql> INSERT INTO baz VALUES (1), (1), (2), (9);
Query OK, 4 rows affected (0.02 sec)


mysql> EXPLAIN SELECT x, SUM(y) FROM bar JOIN baz ON x = r GROUP BY x;
+-----------------------------------------------------------------------+-----------+-----------+
| Operation                                                             | Est. Cost | Est. Rows |
+-----------------------------------------------------------------------+-----------+-----------+
| hash_aggregate_combine GROUPBY((1 . "x")) expr0 := sum((0 . "expr0")) |     81.80 |      4.00 | 
|   hash_aggregate_partial GROUPBY((1 . "x")) expr0 := sum((1 . "y"))   |     76.20 |      4.00 | 
|     nljoin                                                            |     76.20 |      4.00 | 
|       stream_combine                                                  |     33.80 |      4.00 | 
|         index_scan 2 := baz.__base_baz                                |     10.80 |      1.33 | 
|       index_scan 1 := bar.__idx_bar__PRIMARY, x = 2.r                 |     10.60 |      1.00 | 
+-----------------------------------------------------------------------+-----------+-----------+
6 rows in set (0.02 sec)

오퍼레이터 nljoin은 첫 번째 입력의 각 행을 두 번째 입력의 각 행과 결합합니다. 내부 nljoinbaz._base_baz 인덱스 스캔에 해당합니다. 외부 nljoin은 먼저 baz. base_baz 을 읽고 bar. idx_bar_PRIMARY에 결합합니다. 나중의 인덱스 스캔에서 x = 2r이 존재하는 것은 검색 작업이(seek operation) 있음을 나타냅니다. 즉, 풀 테이블 스캔이 발생하지 않습니다.