Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space ML1 and version 5.3
Sv translation
languageen

The EXPLAIN statement is used to reveal how the Xpand Query Optimizer also known as Sierra, will execute INSERT, SELECTUPDATE, and DELETE statements. The output from EXPLAIN presents three columns:

  • Operation -  An internal operator to accomplish a task
  • Est. Cost  - The estimated cost is a measurement proportional to how much wall-clock time will be required to perform the operation
  • Est. Rows - The estimated number of rows the Sierra believes will be output by the operator

These describe the execution plan as a physical plan to implement the declarative SQL statement. In most instances, each row in the output of EXPLAIN represents a single operation collecting input or working with the input which is indented one level in following rows. In other words, most explain statements can be read as most indented is executed first and the total execution follows toward least indented.

Table of Contents

The Data

To illustrate the EXPLAIN output, we'll go through an exercise defining and using a database to track customers and products that are sold to them. This example is for illustrative purposes and is not necessarily a good way to design your data for your application -- a good data model for your application will depend on your business needs and usage patterns. This data model focuses on relations rather than a complete data consistency model. 

We will start with this basic data model. (Download the script used here.)

sql> CREATE TABLE customers (
         c_id INTEGER AUTO_INCREMENT
       , name VARCHAR(100) 
       , address VARCHAR(100)
       , city VARCHAR(50)
       , state CHAR(2)
       , zip CHAR(10)
       , PRIMARY KEY c_pk (c_id)
      ) /*$ SLICES=3 */;
sql> CREATE TABLE products (
         p_id INTEGER AUTO_INCREMENT
       , name VARCHAR(100)
       , price DECIMAL(5,2)
       , PRIMARY KEY p_pk (p_id)
      ) /*$ SLICES=3 */;
sql> CREATE TABLE orders (
         o_id INTEGER AUTO_INCREMENT
       , c_id INTEGER
       , created_on DATETIME
       , PRIMARY KEY o_pk (o_id)
       , KEY c_fk (c_id)
       , CONSTRAINT FOREIGN KEY c_fk (c_id) REFERENCES customers (c_id)
      ) /*$ SLICES=3 */;
sql> CREATE TABLE order_items (
         oi_id INTEGER AUTO_INCREMENT
       , o_id INTEGER
       , p_id INTEGER
       , PRIMARY KEY oi_pk (oi_id)
       , KEY o_fk (o_id)
       , KEY p_fk (p_id)
       , CONSTRAINT FOREIGN KEY order_fk (o_id) REFERENCES orders (o_id)
       , CONSTRAINT FOREIGN KEY product_fk (p_id) REFERENCES products (p_id)
      )  /*$ SLICES=3 */;

After populating the database, there are 1,000 customers, 100 products, 4,000 orders and around 10,000 order_items.

Getting Our Toes Wet

Let's start with a simple query which gives us all the information about all of the customers.

sql> EXPLAIN SELECT * FROM customers; 
+------------------------------------------------------+-----------+-----------+
| Operation                                            | Est. Cost | Est. Rows |
+------------------------------------------------------+-----------+-----------+
| stream_combine                                       |    712.70 |   1000.00 |
|   index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+------------------------------------------------------+-----------+-----------+

In general, the explain output can be read from innermost indentation first and working your way to lower indentation eventually winding up at the first line in the output. Reading the explain output above, the first thing that happens is the operation index_scan on the index customers.__idx_customers__PRIMARY, the primary key index, and assigns the name "1" to the results of the read. In this case, that name is not used again. Note that the estimated rows are approximately 333 even though there are 1,000 customers in the relation. This is because each index_scan is reading a subset of the data distributed in the cluster which we call a slice. With this relation's schema where there are three slices, so three index_scan operations are running in parallel to gather the customer information. The output from the three index_scan operations is passed to the stream_combine operator which will, as the name implies, combines the streams into one so that it can be delivered to the client. The stream_combine operator works by simply copying the entire contents of the first input to arrive into the single stream output and continuing on until all streams have been combined.

Let's see what happens if we add a limit to our query.

sql>  EXPLAIN SELECT * FROM customers LIMIT 10;  
+----------------------------------------------------------+-----------+-----------+
| Operation                                                | Est. Cost | Est. Rows |
+----------------------------------------------------------+-----------+-----------+
| row_limit LIMIT := param(0)                              |    615.70 |     10.00 |
|   stream_combine                                         |    615.70 |     30.00 |
|     row_limit LIMIT := param(0)                          |    203.90 |     10.00 |
|       index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+----------------------------------------------------------+-----------+-----------+

Here we have pretty much the same execution plan as before with the addition of the row_limit operator. A row_limit operator takes an incoming stream and closes the input stream once the limit (and offset) are satisfied. Since there are three parallel streams, Sierra "pushes down" a copy of the row_limit operator to each index_scan stream since there is no need to read more than 10 rows from each slice. After the streams are combined, we limit the output again so that the client gets the requested 10 rows.

Let's say we want to have an ordering for the results.

sql> EXPLAIN SELECT * FROM customers ORDER BY c_id;
+------------------------------------------------------+-----------+-----------+
| Operation                                            | Est. Cost | Est. Rows |
+------------------------------------------------------+-----------+-----------+
| stream_merge KEYS=[(1 . "c_id") ASC]                 |    816.70 |   1000.00 |
|   index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+------------------------------------------------------+-----------+-----------+

This plan is similar to the unsorted version except that this time there is a stream_merge to combine the results rather than stream_combine. The stream_merge operator works by pulling the next row from all incoming streams into the output stream based on the ordering provided. In this case, the order is on the c_id column ascending so stream_merge will pop the row which compares smallest on all streams.

In a Xpand cluster, data is typically hash distributed across the nodes and since stream_combine returns whatever arrives first, the results may look different than databases which are not distributed and always read the data in order. For example:

sql> SELECT * FROM customers LIMIT 10; 
+------+---------------------+--------------+-------------+-------+-------+
| c_id | name                | address      | city        | state | zip   |
+------+---------------------+--------------+-------------+-------+-------+
|    1 | Chanda Nordahl      | 4280 Maple   | Greenville  | WA    | 98903 |
|    2 | Dorinda Tomaselli   | 8491 Oak     | Centerville | OR    | 97520 |
|    9 | Minerva Donnell     | 4644 1st St. | Springfield | WA    | 98613 |
|   21 | Chanda Nordahl      | 5090 1st St. | Fairview    | OR    | 97520 |
|    4 | Dorinda Hougland    | 8511 Pine    | Springfield | OR    | 97477 |
|    6 | Zackary Velasco     | 6296 Oak     | Springfield | OR    | 97286 |
|   11 | Tennie Soden        | 7924 Maple   | Centerville | OR    | 97477 |
|    3 | Shawnee Soden       | 4096 Maple   | Ashland     | WA    | 98035 |
|   24 | Riley Soden         | 7470 1st St. | Greenville  | WA    | 98613 |
|   12 | Kathaleen Tomaselli | 8926 Maple   | Centerville | OR    | 97477 |
+------+---------------------+--------------+-------------+-------+-------+

Repeating this query may get different results. By adding an ORDER BY clause to the statement, we can ensure that we get consistent results. To make things more interesting, we'll also change the ordering from ascending to descending.

sql> EXPLAIN SELECT * FROM customers ORDER BY c_id DESC LIMIT 10; 
+------------------------------------------------------------------+-----------+-----------+
| Operation                                                        | Est. Cost | Est. Rows |
+------------------------------------------------------------------+-----------+-----------+
| row_limit LIMIT := param(0)                                      |    622.70 |     10.00 |
|   stream_merge KEYS=[(1 . "c_id") DSC]                           |    622.70 |     30.00 |
|     row_limit LIMIT := param(0)                                  |    203.90 |     10.00 |
|       index_scan 1 := customers.__idx_customers__PRIMARY REVERSE |    203.90 |    333.33 |
+------------------------------------------------------------------+-----------+-----------+

We can see from this execution plan, the database will first read the primary index in parallel and in reverse on all available slices with the index_scan operator, stop reading after 10 rows are found using the row_limit operator, merge those streams by selecting the greatest value for c_id from each stream with the stream_merge operator, and finally limit that to 10 rows via a repeated application of the row_limit operator.

Explaining Joins

So far, we have been looking at single relation reads. One of the Sierra's jobs is to compare the cost of different join orderings and choose the plan with the lowest cost. This query will yield the order id, product name, and price for every row in order_items.

         
1.   | sql> EXPLAIN SELECT o_id, name, price FROM orders o NATURAL JOIN order_items NATURAL JOIN products;                         
2.   +-------------------------------------------------------------------------------+-----------+-----------+
3.   | Operation                                                                     | Est. Cost | Est. Rows |
4.   +-------------------------------------------------------------------------------+-----------+-----------+
5.   | nljoin                                                                        |  95339.90 |   9882.00 |
6.   |   nljoin                                                                      |  50870.90 |   9882.00 |
7.   |     stream_combine                                                            |     82.70 |    100.00 |
8.   |       index_scan 3 := products.__idx_products__PRIMARY                        |     23.90 |     33.33 |
9.   |     nljoin                                                                    |    507.88 |     98.82 |
10. |       index_scan 2 := order_items.p_fk, p_id = 3.p_id                         |     63.19 |     98.82 |
11. |       index_scan 2 := order_items.__idx_order_items__PRIMARY, oi_id = 2.oi_id |      4.50 |      1.00 |
12. |   index_scan 1 := orders.__idx_orders__PRIMARY, o_id = 2.o_id                 |      4.50 |      1.00 |
13. +-------------------------------------------------------------------------------+-----------+-----------+

This plan is a little more complex and will require a little more explanation to see what is happening.

  1. Given the indentation, we can infer that an index_scan will happen first. In the output of the explain, we can see that the p_id found in the index_scan of the primary key of products on line 8 is used when reading the p_fk index of order_items on line 10 and the oi_id is used when reading the order_items primary key index on line 11. At essentially the same time, the products are collected with the stream_combine operator and the order_items information is collected by doing an nljoin of order_items.p_fk and the order_items primary key index.
  2. The nljoin operator is a nested loop join which implements a relational equi-join.
  3. The output of the products stream_combine and order_items nljoin are then joined in another nljoin.
  4. The order_items.o_id is used to read orders and the results are all put together in a final nljoin.

Looking at the estimated rows in the final nljoin let's us know that in this particular data set, Sierra thinks there are approximately 9882 order_items rows.

StageOperationLookup/Scan representationLookup/Scan KeyRun on Node
1Lookup and Forward__idx_products__PRIMARYnone (all nodes with slices)The node where the query begins

2.1Index Scan__idx_products__PRIMARYNone, all rowsNodes with slices of __idx_products__PRIMARY
2.2Lookup and Forwardp_fkp_id = 3.p_idsame

3.1Index Scanp_fkp_id = 3.p_idNodes with slices of p_fk
3.2Join

same
3.3Lookup and Forward__idx_order_items__PRIMARY oi_id = 2.oi_idsame

4.1Index Scan__idx_order_items__PRIMARYoi_id = 2.oi_idNodes with slices of __idx_order_items__PRIMARY
4.2 Join

same
4.3 Lookup and Forward__idx_orders__PRIMARYo_id = 2.o_idsame

5.1Index Scan__idx_orders__PRIMARYo_id = 2.o_idNodes with slices of __idx_orders__PRIMARY
5.2Join


5.3Lookup and ForwardGTMnone - single GTM node

6Return to user

The node where the query began

Locks

Xpand uses Two-phase locking (2PL) as a concurrency control to guarantee serializability. Sierra will plan locks for writes as well as reads for update in a transaction. First, we'll examine a simple update which increases the value of price by 1 when greater than 10.

sql> EXPLAIN UPDATE products SET price = price + 1 WHERE price > 10;
+-------------------------------------------------------------------------+-----------+-----------+
| Operation                                                               | Est. Cost | Est. Rows |
+-------------------------------------------------------------------------+-----------+-----------+
| table_update products                                                   |   1211.58 |     81.00 |
|   compute expr0 := (1.price + param(0))                                 |   1211.58 |     81.00 |
|     filter (1.price > param(1))                                         |   1210.50 |     81.00 |
|       nljoin                                                            |   1208.70 |     90.00 |
|         pk_lock "products" exclusive                                    |    803.70 |     90.00 |
|           stream_combine                                                |     83.70 |     90.00 |
|             filter (1.price > param(1))                                 |     24.57 |     30.00 |
|               index_scan 1 := products.__idx_products__PRIMARY          |     23.90 |     33.33 |
|         index_scan 1 := products.__idx_products__PRIMARY, p_id = 1.p_id |      4.50 |      1.00 |
+-------------------------------------------------------------------------+-----------+-----------+

In this query plan:

  1. We read the products primary key index with an index_scan and send the output to a "pushed down" filter which discards rows with a price not greater than 10 at each slice.
  2. Those outputs are then combined with a stream_combine and that stream is distributed across the cluster to acquire an exclusive primary key lock with the pk_lock operator on the rows found.
  3. We can then use the p_id found and read the primary key index with another index_scan.
  4. The filter is applied again since the row found in the first index_scan may have had a price change since reading the row and acquiring the lock.
  5. Matching rows are sent to a compute operator which calculates the new value for price and the new row is sent to the table_update operator which writes the new value.

At some point, taking individual row locks for every row to modify is more expensive than simply acquiring a single table lock and modifying all qualifying rows. The Sierra optimizer will consider using table locks instead of row locks during plan exploration and choose the plan with the lowest cost. In this example, 100 rows would normally be too small to bother with a table lock though if Sierra chose to take a table lock, the plan would look like the following explain.

sql> EXPLAIN UPDATE products SET price = price + 1; 
+------------------------------------------------------------+-----------+-----------+
| Operation                                                  | Est. Cost | Est. Rows |
+------------------------------------------------------------+-----------+-----------+
| table_locks 1                                              |   8084.03 |    100.00 |
|   table_update products                                    |     84.03 |    100.00 |
|     stream_combine                                         |     84.03 |    100.00 |
|       compute expr0 := (1.price + param(0))                |     24.34 |     33.33 |
|         table_lock "products" exclusive                    |     23.90 |     33.33 |
|           index_scan 1 := products.__idx_products__PRIMARY |     23.90 |     33.33 |
+------------------------------------------------------------+-----------+-----------+

What is interesting here is that it looks like index_scan is an input to table_lock. This not the case since the table lock will be acquired prior to any read. With this in mind, we can see the plan:

  1. Reads all rows in the relation with index_scan.
  2. Adds 1 to the price with compute.
  3. Combines those results into a single stream with stream_combine.
  4. Sends the output to table_update to write the new value.

The table_lock operator is a helper operator for Sierra which has a heuristic for balancing the relatively inexpensive single lock with the fact that other updates are blocked, and thus consuming wall-clock time, during this transaction.

Using Indexes to Improve Performance

So far we have only examined reading the primary key index to get results. We can change this by adding indexes which make sense for a given workload. For example, let's imagine we have a business process that works better if we have our customer information sorted by zip code and coalesced into small chunks. To get this information:

sql> EXPLAIN SELECT name, address, city, state, zip FROM customers ORDER BY zip LIMIT 10 OFFSET 0;
+----------------------------------------------------------+-----------+-----------+
| Operation                                                | Est. Cost | Est. Rows |
+----------------------------------------------------------+-----------+-----------+
| row_limit LIMIT := param(0)                              |   2632.70 |     10.00 |
|   sigma_sort KEYS=[(1 . "zip") ASC]                      |   2632.70 |   1000.00 |
|     stream_combine                                       |    712.70 |   1000.00 |
|       index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+----------------------------------------------------------+-----------+-----------+

This reads the primary key index, combines the results and then sends those rows to a sigma_sort operator. The sigma_sort operator builds a temporary container in memory or in storage as necessary to sort the rows found by zip code. Once all of the results are sorted, they are passed along to the row_limit operator to enforce the limit and offset.

We can significantly improve the performance here if we read the zip code in order rather than reading all the rows, sort on zip code, and then return the next batch of 10 rows. To do this, we add an index on customers.zip and see how Sierra changes the execution plan.

sql> ALTER TABLE customers ADD INDEX (zip); 
sql> EXPLAIN SELECT name, address, city, state, zip FROM customers ORDER BY zip LIMIT 10 OFFSET 0;
+---------------------------------------------------------------------+-----------+-----------+
| Operation                                                           | Est. Cost | Est. Rows |
+---------------------------------------------------------------------+-----------+-----------+
| msjoin KEYS=[(1 . "zip") ASC]                                       |    674.70 |     10.00 |
|   row_limit LIMIT := param(0)                                       |    622.70 |     10.00 |
|     stream_merge KEYS=[(1 . "zip") ASC]                             |    622.70 |     30.00 |
|       row_limit LIMIT := param(0)                                   |    203.90 |     10.00 |
|         index_scan 1 := customers.zip                               |    203.90 |    333.33 |
|   index_scan 1 := customers.__idx_customers__PRIMARY, c_id = 1.c_id |      4.50 |      1.00 |
+---------------------------------------------------------------------+-----------+-----------+              

Here, the query optimizer chooses to:

  1. Read the customers.zip index in order with the index_scan operator on all slices in parallel.
  2. Limit the results with the "pushed down" row_limit operator.
  3. Merge those results and preserve order with the stream_merge operator.
  4. Limit the merged results with another row_limit.
  5. Use the c_id found in the zip index to read the rest of the row.
  6. Use the msjoin operator to perform the equi-join.

The msjoin operator is a "merge sort nested-loop join" which is similar to nljoin, but preserves sort order during a join. Notice that in this plan, the sort order is read for the zip index and preserved all the way through the plan which eliminates the need to create a sigma container to sort the results. In other words, this plan streams all results as it goes which can be an important consideration when reading millions of rows.

Aggregates

Another common task when working with a relational database is to sift through big data to calculate SUM, AVERAGE, MINIMUM, or MAXIMUM. These queries are executed by adding a GROUP BY clause to your statement which declares how you want the data to be aggregated. Xpand also implements the MySQL extension to GROUP BY to allow inclusion of non-aggregated columns in the output columns. If there is not a one-to-one relation between the GROUP BY columns and the non-aggregated columns, the value of the non-aggregated columns will be one of the values in the row though which value is returned is not defined. Since we have a one-to-one mapping between zip and state in our data, we can generate a result set which generates that mapping for us.

sql> EXPLAIN SELECT zip, state FROM customers GROUP BY zip; 
+--------------------------------------------------------+-----------+-----------+
| Operation                                              | Est. Cost | Est. Rows |
+--------------------------------------------------------+-----------+-----------+
| sigma_distinct_combine KEYS=((1 . "zip"))              |   1303.90 |   1000.00 |
|   sigma_distinct_partial KEYS=((1 . "zip"))            |    203.90 |   1000.00 |
|     index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+--------------------------------------------------------+-----------+-----------+

This query will:

  1. First, perform an index_scan and send the output the sigma_distinct_partial operator.
  2. The sigma_distinct_partial operator which produces an output of one row per distinct value of KEYS on the same node as the read.
  3. Those distinct values are then sent to the sigma_distinct_combine operator which will do the same distinct operations on KEYS on the node where the query was initiated.

For a more realistic aggregation, let's presume we want to find how many orders each customer has placed and that customer's name.

sql> EXPLAIN SELECT c.name, COUNT(*) FROM orders o NATURAL JOIN customers c GROUP BY o.c_id; 
+-------------------------------------------------------------------------------+-----------+-----------+
| Operation                                                                     | Est. Cost | Est. Rows |
+-------------------------------------------------------------------------------+-----------+-----------+
| hash_aggregate_combine GROUPBY((1 . "c_id")) expr1 := countsum((0 . "expr1")) |  12780.38 |   4056.80 |
|   hash_aggregate_partial GROUPBY((1 . "c_id")) expr1 := count((0 . "expr0"))  |   7100.87 |   4056.80 |
|     compute expr0 := param(0)                                                 |   7100.87 |   4056.80 |
|       nljoin                                                                  |   7046.78 |   4056.80 |
|         stream_combine                                                        |    712.70 |   1000.00 |
|           index_scan 2 := customers.__idx_customers__PRIMARY                  |    203.90 |    333.33 |
|         index_scan 1 := orders.c_fk, c_id = 2.c_id                            |      6.33 |      4.06 |
+-------------------------------------------------------------------------------+-----------+-----------+

In this plan:

  1. The index_scan of the customer's primary key is first and combined with stream_combine and the c_id is used to read the orders.c_fk index with another index_scan.
  2. Those results are joined on the node where we read the orders.c_fk index with the nljoin operator and grouped and counted with the hash_aggregate_partial operator on the same node.
  3. The results are then sent to the hash_aggregate_combine operator on the originating node for a final group and count before returning rows to the user.

Summary

Hopefully, this is a sufficient introduction to the EXPLAIN output for the Xpand Sierra Query Optimizer that you use to examine your own queries. For a complete list of the operators which might show up in the EXPLAIN, consult the List of Planner Operators. For more information on how Sierra does query optimization, see Query Optimizer in Distributed Database Architecture.

Sv translation
languageko

EXPLAIN문은 Sierra라고도 하는 ClustrixDB 쿼리 옵티마이저(Query Optimizer)가 INSERT, SELECTUPDATE 및 DELETE문을 어떻게 실행하는지 보여주기 위해 사용합니다. EXPLAIN 출력은 세 개의 열을 제공합니다.

  • Operation - 작업을 수행하기 위한 내부 연산자
  • Est. Cost - 예상 비용은 작업을 수행하는 데 필요한 소요된 시간(wall-clock time)에 비례하는 수치입니다.
  • Est. Rows - Sierra가 연산자에 의해 출력될 것으로 예상하는 행 수

이것은 실행 계획을 선언적 SQL문을 구현하는 물리적 계획으로 설명합니다. 대부분의 경우, EXPLAIN 출력의 각 행은 입력을 수집하거나 다음 행에서 한 단계 들여쓰기 된 입력을 처리하는 단일 작업을 나타냅니다. 즉, 가장 들여쓰기가 많이 된 explain문이 먼저 실행되고 전체적인 실행은 가장 들여쓰기가 적은 방향으로 이어집니다.

Table of Contents

데이터

EXPLAIN 출력을 설명하기 위해 지금부터 데이터베이스를 정의하고 사용하여 고객과 판매된 제품을 추적하는 연습을 진행하려고 합니다. 이 예제는 설명을 위한 것으로 응용 프로그램의 데이터를 디자인하는 좋은 방법은 아닙니다. 응용 프로그램에 적합한 데이터 모델은 비즈니스 요구 사항과 사용 패턴에 따라 달라집니다. 이 데이터 모델은 완전한 데이터 일관성 모델이 아닌 relation에 중점을 둡니다.

스크립트로 기본 데이터 모델을 만들고 데이터를 등록합니다. (사용된 스크립트를 여기에서 다운로드하십시오.)

sql> CREATE TABLE customers (
         c_id INTEGER AUTO_INCREMENT
       , name VARCHAR(100) 
       , address VARCHAR(100)
       , city VARCHAR(50)
       , state CHAR(2)
       , zip CHAR(10)
       , PRIMARY KEY c_pk (c_id)
      ) /*$ SLICES=3 */;
sql> CREATE TABLE products (
         p_id INTEGER AUTO_INCREMENT
       , name VARCHAR(100)
       , price DECIMAL(5,2)
       , PRIMARY KEY p_pk (p_id)
      ) /*$ SLICES=3 */;
sql> CREATE TABLE orders (
         o_id INTEGER AUTO_INCREMENT
       , c_id INTEGER
       , created_on DATETIME
       , PRIMARY KEY o_pk (o_id)
       , KEY c_fk (c_id)
       , CONSTRAINT FOREIGN KEY c_fk (c_id) REFERENCES customers (c_id)
      ) /*$ SLICES=3 */;
sql> CREATE TABLE order_items (
         oi_id INTEGER AUTO_INCREMENT
       , o_id INTEGER
       , p_id INTEGER
       , PRIMARY KEY oi_pk (oi_id)
       , KEY o_fk (o_id)
       , KEY p_fk (p_id)
       , CONSTRAINT FOREIGN KEY order_fk (o_id) REFERENCES orders (o_id)
       , CONSTRAINT FOREIGN KEY product_fk (p_id) REFERENCES products (p_id)
      )  /*$ SLICES=3 */;

데이터베이스에 데이터를 등록한 후에는 1,000명의 고객, 100개의 제품, 4,000개의 주문 및 약 10,000개의 order_items가 있습니다.

시작하기

모든 고객에 대한 모든 정보를 제공하는 간단한 쿼리부터 시작해 보겠습니다.

sql> EXPLAIN SELECT * FROM customers; 
+------------------------------------------------------+-----------+-----------+
| Operation                                            | Est. Cost | Est. Rows |
+------------------------------------------------------+-----------+-----------+
| stream_combine                                       |    712.70 |   1000.00 |
|   index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+------------------------------------------------------+-----------+-----------+

일반적으로, explain 출력은 가장 안쪽의 들여쓰기로부터 먼저 읽히고 들여쓰기 많은 순으로 나아가다 출력의 첫 번째 줄에서 끝이 납니다. 위의 explain 출력을 읽어보면 customers.__idx_customers__PRIMARY(기본 키 인덱스)에 대한 index_scan 작업을 수행하고 읽기 결과에 이름 "1"을 할당하는 것입니다. 이 경우 해당 이름은 다시 사용되지 않습니다. Relation에 1,000명의 고객이 있더라도 예상 행 수는 약 333입니다. 이는 각 index_scan이 슬라이스로 불리는 클러스터에 분산된 데이터의 하위 집합을 읽기 때문입니다. 3개의 슬라이스가 있는 이 relation의 스키마를 사용하면 3개의 index_scan 작업이 병렬로 실행되어 고객 정보를 수집합니다. 그 3개의 index_scan 작업의 출력은 stream_combine 연산자로 전달되며, 이름에서 알 수 있듯이 이 연산자는 스트림을 하나로 결합하여 클라이언트에 전달할 수 있습니다. stream_combine 연산자는 첫 번째 입력의 전체 내용을 복사하여 단일 스트림 출력에 도달하고 모든 스트림이 결합할 때까지 계속 작동합니다.


쿼리에 제한을 추가하면 어떻게 되는지 봅시다.

sql>  EXPLAIN SELECT * FROM customers LIMIT 10;  
+----------------------------------------------------------+-----------+-----------+
| Operation                                                | Est. Cost | Est. Rows |
+----------------------------------------------------------+-----------+-----------+
| row_limit LIMIT := param(0)                              |    615.70 |     10.00 |
|   stream_combine                                         |    615.70 |     30.00 |
|     row_limit LIMIT := param(0)                          |    203.90 |     10.00 |
|       index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+----------------------------------------------------------+-----------+-----------+

row_limit 연산자가 추가되더라도 실행 계획은 전과 거의 동일합니다. row_limit 연산자는 입력 스트림을 가져와서 한계(및 오프셋)가 충족되면 입력 스트림을 닫습니다. 3개의 병렬 스트림이 있으므로 Sierra는 각 슬라이스에서 10개 이상의 행을 읽을 필요가 없으므로 row_limit 연산자의 복사본을 각 index_scan 스트림으로 "푸시 다운"합니다. 스트림을 결합한 후 요청된 10개의 행이 클라이언트에 반환되도록 출력을 다시 제한합니다.

결과에 대한 정렬을 원한다고 가정해 봅시다.

sql> EXPLAIN SELECT * FROM customers ORDER BY c_id;
+------------------------------------------------------+-----------+-----------+
| Operation                                            | Est. Cost | Est. Rows |
+------------------------------------------------------+-----------+-----------+
| stream_merge KEYS=[(1 . "c_id") ASC]                 |    816.70 |   1000.00 |
|   index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+------------------------------------------------------+-----------+-----------+

이 계획은 이번에는 stream_combine이 아닌 stream_merge 연산자가 결과를 결합하는 점을 제외하고는 정렬되지 않은 버전과 유사합니다. stream_merge 연산자는 제공된 순서에 따라 출력 스트림으로 모든 입력 스트림의 다음 행을 가져와서 작동합니다. 이 경우 정렬은 c_id 열에 오름차순이므로 stream_merge는 모든 스트림에서 가장 작은 행을 리턴합니다.

ClustrixDB 클러스터에서 데이터는 일반적으로 노드 전체에 해시 되어 분산되며stream_combine은 먼저 도착한 결과를 반환하기 때문에 분산되지 않고 항상 순서대로 데이터를 읽는 데이터베이스와는 결과가 다를 수 있습니다. 예:

sql> SELECT * FROM customers LIMIT 10; 
+------+---------------------+--------------+-------------+-------+-------+
| c_id | name                | address      | city        | state | zip   |
+------+---------------------+--------------+-------------+-------+-------+
|    1 | Chanda Nordahl      | 4280 Maple   | Greenville  | WA    | 98903 |
|    2 | Dorinda Tomaselli   | 8491 Oak     | Centerville | OR    | 97520 |
|    9 | Minerva Donnell     | 4644 1st St. | Springfield | WA    | 98613 |
|   21 | Chanda Nordahl      | 5090 1st St. | Fairview    | OR    | 97520 |
|    4 | Dorinda Hougland    | 8511 Pine    | Springfield | OR    | 97477 |
|    6 | Zackary Velasco     | 6296 Oak     | Springfield | OR    | 97286 |
|   11 | Tennie Soden        | 7924 Maple   | Centerville | OR    | 97477 |
|    3 | Shawnee Soden       | 4096 Maple   | Ashland     | WA    | 98035 |
|   24 | Riley Soden         | 7470 1st St. | Greenville  | WA    | 98613 |
|   12 | Kathaleen Tomaselli | 8926 Maple   | Centerville | OR    | 97477 |
+------+---------------------+--------------+-------------+-------+-------+

이 쿼리를 반복하면 다른 결과를 얻게 될 수 있습니다. 명령문에 ORDER BY 절을 추가하면 일관된 결과를 얻을 수 있습니다. 좀 더 흥미로운 결과를 위해 오름차순에서 내림차순으로 정렬을 변경합니다.

sql> EXPLAIN SELECT * FROM customers ORDER BY c_id DESC LIMIT 10; 
+------------------------------------------------------------------+-----------+-----------+
| Operation                                                        | Est. Cost | Est. Rows |
+------------------------------------------------------------------+-----------+-----------+
| row_limit LIMIT := param(0)                                      |    622.70 |     10.00 |
|   stream_merge KEYS=[(1 . "c_id") DSC]                           |    622.70 |     30.00 |
|     row_limit LIMIT := param(0)                                  |    203.90 |     10.00 |
|       index_scan 1 := customers.__idx_customers__PRIMARY REVERSE |    203.90 |    333.33 |
+------------------------------------------------------------------+-----------+-----------+

이 실행 계획에서 볼 수 있듯이 데이터베이스는 먼저 기본 인덱스(primary index)를 병렬로 읽고 index_scan 연산자를 사용하여 사용 가능한 슬라이스를 역으로 읽습니다. row_limit 연산자를 사용하여 10개의 행을 찾은 후 읽기를 중지하고 stream_merge 연산자로 각 스트림에서 c_id에 대한 최대 값을 선택하여 스트림들을 병합하고 마지막으로 row_limit 연산자를 반복 적용하여 10개의 행으로 줄입니다.

Join 설명

지금까지 단일 relation 읽기를 살펴보았습니다. Sierra의 작업 중 하나는 서로 다른 join 순서의 비용을 비교하고 비용이 가장 낮은 계획을 선택하는 것입니다. 이 쿼리는 order_items의 모든 행에 대한 주문 ID, 제품 이름가격을 산출합니다. 

         
1.   | sql> EXPLAIN SELECT o_id, name, price FROM orders o NATURAL JOIN order_items NATURAL JOIN products;                         
2.   +-------------------------------------------------------------------------------+-----------+-----------+
3.   | Operation                                                                     | Est. Cost | Est. Rows |
4.   +-------------------------------------------------------------------------------+-----------+-----------+
5.   | nljoin                                                                        |  95339.90 |   9882.00 |
6.   |   nljoin                                                                      |  50870.90 |   9882.00 |
7.   |     stream_combine                                                            |     82.70 |    100.00 |
8.   |       index_scan 3 := products.__idx_products__PRIMARY                        |     23.90 |     33.33 |
9.   |     nljoin                                                                    |    507.88 |     98.82 |
10. |       index_scan 2 := order_items.p_fk, p_id = 3.p_id                         |     63.19 |     98.82 |
11. |       index_scan 2 := order_items.__idx_order_items__PRIMARY, oi_id = 2.oi_id |      4.50 |      1.00 |
12. |   index_scan 1 := orders.__idx_orders__PRIMARY, o_id = 2.o_id                 |      4.50 |      1.00 |
13. +-------------------------------------------------------------------------------+-----------+-----------+

이 계획은 좀 더 복잡하여 조금 설명이 필요합니다.

  1. 현재 explain 출력의 들여쓰기를 보면 index_scan이 먼저 발생한다고 추론할 수 있습니다. explain의 출력에서 8번째 줄의 products의 기본 키 index_scan에 있는 p_id는 10번째 줄에 있는 order_itemsp_fk 인덱스를 읽을 때 사용되며 oi_id는 11번째 줄의 order_items 기본 키 인덱스를 읽을 때 사용됩니다. 반드시 동시에, products는 stream_combine 연산자를 사용하여 수집되며 order_items.p_fkorder_items 기본 키 인덱스를 nljoin하여 order_items 정보를 수집합니다.
  2. nljoin 연산자는 관계형 equi-join을 구현하는 중첩(nested loop) join입니다.
  3. 그런 다음 products stream_combine 및 order_items nljoin의 출력이 다른 nljoin에서 join 됩니다.
  4. order_items.o_idorders를 읽는 데 사용되며 결과는 모두 마지막 nljoin에서 합쳐집니다.

마지막 nljoin의 예상 행을 보면 이 특정 데이터 집합에서 Sierra는 약 9,882개의 order_items 행이 있다고 예측하는 것을 알 수 있습니다.

단계연산Lookup/Scan representationLookup/Scan 키노드에서 실행
1Lookup and Forward__idx_products__PRIMARYnone (all nodes with slices)The node where the query begins
 
2.1Index Scan__idx_products__PRIMARYNone, all rowsNodes with slices of __idx_products__PRIMARY
2.2Lookup and Forwardp_fkp_id = 3.p_idsame
 
3.1Index Scanp_fkp_id = 3.p_idNodes with slices of p_fk
3.2Join  same
3.3Lookup and Forward__idx_order_items__PRIMARY oi_id = 2.oi_idsame
 
4.1Index Scan__idx_order_items__PRIMARYoi_id = 2.oi_idNodes with slices of __idx_order_items__PRIMARY
4.2 Join  same
4.3 Lookup and Forward__idx_orders__PRIMARYo_id = 2.o_idsame
 
5.1Index Scan__idx_orders__PRIMARYo_id = 2.o_idNodes with slices of __idx_orders__PRIMARY
5.2Join   
5.3Lookup and ForwardGTMnone - single GTM node 
 
6Return to user  The node where the query began

잠금

ClustrixDB는 직렬성(serializability)을 보장하기 위해 동시성 제어로 2PL(Two-phase Locking)을 사용합니다. Sierra는 트랜잭션에서 업데이트를 위한 읽기 및 쓰기를 위해 잠금을 계획합니다. 먼저, 10보다 큰 값을 가질 때 가격의 값을 1씩 증가시키는 간단한 업데이트를 살펴보겠습니다.

sql> EXPLAIN UPDATE products SET price = price + 1 WHERE price > 10;
+-------------------------------------------------------------------------+-----------+-----------+
| Operation                                                               | Est. Cost | Est. Rows |
+-------------------------------------------------------------------------+-----------+-----------+
| table_update products                                                   |   1211.58 |     81.00 |
|   compute expr0 := (1.price + param(0))                                 |   1211.58 |     81.00 |
|     filter (1.price > param(1))                                         |   1210.50 |     81.00 |
|       nljoin                                                            |   1208.70 |     90.00 |
|         pk_lock "products" exclusive                                    |    803.70 |     90.00 |
|           stream_combine                                                |     83.70 |     90.00 |
|             filter (1.price > param(1))                                 |     24.57 |     30.00 |
|               index_scan 1 := products.__idx_products__PRIMARY          |     23.90 |     33.33 |
|         index_scan 1 := products.__idx_products__PRIMARY, p_id = 1.p_id |      4.50 |      1.00 |
+-------------------------------------------------------------------------+-----------+-----------+

이 쿼리 계획에서,

  1. index_scan을 사용하여 products 기본 키 인덱스를 읽고 각 슬라이스에서 10보다 크지 않은 가격을 가진 행을 버리는 "푸시 다운(pushed down) filter로 출력을 보냅니다.
  2. 그런 다음, 이러한 출력은 stream_combine과 결합하며 해당 스트림은 찾은 행에서 pk_lock 연산자로 배타적인 기본 키 잠금을 획득하기 위해 클러스터 전체에 분산됩니다.
  3. 그런 다음, 찾은 p_id를 사용하고 기본 키 인덱스를 다른 index_scan으로 읽을 수 있습니다.
  4. 행을 읽고 잠금을 획득한 이후 첫 번째 index_scan에서 찾은 행에 가격 변경이 있었기 때문에 filter가 다시 적용됩니다.
  5. 일치하는 행은 가격에 대한 새 값을 계산하는 compute 연산자로 보내지고 새 행은 새 값을 쓰는 table_update 연산자로 보내집니다.

어떤 시점에서 수정하려는 모든 행에 대한 개별 행 잠금을 거는 것은 단일 테이블 잠금을 획득해서 모든 적용 가능한 행을 수정하는 것보다 비용이 많이 듭니다. Sierra 옵티마이저는 플랜 탐색 중에 행 잠금 대신 테이블 잠금 사용을 고려하고 비용이 가장 낮은 플랜을 선택합니다. 이 예제에서 100개의 행은 일반적으로 테이블 잠금을 필요로 하기에는 너무 작지만, Sierra가 테이블 잠금을 걸기로 선택했다면 플랜은 다음의 explain과 같이 보일 것입니다.

sql> EXPLAIN UPDATE products SET price = price + 1; 
+------------------------------------------------------------+-----------+-----------+
| Operation                                                  | Est. Cost | Est. Rows |
+------------------------------------------------------------+-----------+-----------+
| table_locks 1                                              |   8084.03 |    100.00 |
|   table_update products                                    |     84.03 |    100.00 |
|     stream_combine                                         |     84.03 |    100.00 |
|       compute expr0 := (1.price + param(0))                |     24.34 |     33.33 |
|         table_lock "products" exclusive                    |     23.90 |     33.33 |
|           index_scan 1 := products.__idx_products__PRIMARY |     23.90 |     33.33 |
+------------------------------------------------------------+-----------+-----------+

여기서 흥미로운 점은 index_scantable_lock에 대한 입력인 것처럼 보인다는 것입니다. 테이블 잠금은 모든 읽기 전에 획득되기 때문에 이것은 그런 경우가 아닙니다. 이를 염두에 두고 우리는 플랜이 다음과 같음을 확인할 수 있습니다.

  1. index_scan으로 relation에 있는 모든 행을 읽습니다.
  2. compute가격에 1을 추가합니다.
  3. stream_combine을 사용하여 해당 결과를 단일 스트림으로 결합합니다. 
  4. 새 값을 쓰기 위해 table_update로 출력을 보냅니다.

table_lock 연산자는 Sierra의 도우미(helper) 연산자로서 비교적 저렴하지만, 이 트랜잭션 동안 다른 업데이트는 차단되는 단일 잠금을 밸런싱하는 발견적(heuristic) 기능을 가지고 있습니다.

인덱스를 사용하여 성능 향상시키기

지금까지 우리는 결과를 얻기 위해 기본 키 인덱스 읽기만 검토했습니다. 주어진 워크로드에 적합한 인덱스를 추가하여 이를 변경할 수 있습니다. 예를 들어, 고객 정보를 우편번호 별로 정리하고 작게 합쳐지면 더 잘 작동하는 비즈니스 프로세스가 있다고 가정해 봅시다. 이 정보를 얻으려면,

sql> EXPLAIN SELECT name, address, city, state, zip FROM customers ORDER BY zip LIMIT 10 OFFSET 0;
+----------------------------------------------------------+-----------+-----------+
| Operation                                                | Est. Cost | Est. Rows |
+----------------------------------------------------------+-----------+-----------+
| row_limit LIMIT := param(0)                              |   2632.70 |     10.00 |
|   sigma_sort KEYS=[(1 . "zip") ASC]                      |   2632.70 |   1000.00 |
|     stream_combine                                       |    712.70 |   1000.00 |
|       index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+----------------------------------------------------------+-----------+-----------+

이것은 기본 키 인덱스를 읽고 결과를 결합한 다음 해당 행을 sigma_sort 연산자로 보냅니다. sigma_sort 연산자는 우편번호로 찾은 행을 정렬하는 데 필요한 임시 컨테이너를 메모리 또는 스토리지에 만듭니다. 모든 결과가 정렬되면 row_limit 연산자로 전달되어 제한 및 오프셋을 적용합니다.

모든 행을 읽고 우편번호로 정렬한 다음 10개의 행을 반환하기보다는 순서대로 우편번호를 읽으면 성능을 크게 향상시킬 수 있습니다. 이를 위해 customers.zip에 인덱스를 추가하고 Sierra가 실행 계획을 어떻게 변경하는지 확인합니다.

sql> ALTER TABLE customers ADD INDEX (zip); 
sql> EXPLAIN SELECT name, address, city, state, zip FROM customers ORDER BY zip LIMIT 10 OFFSET 0;
+---------------------------------------------------------------------+-----------+-----------+
| Operation                                                           | Est. Cost | Est. Rows |
+---------------------------------------------------------------------+-----------+-----------+
| msjoin KEYS=[(1 . "zip") ASC]                                       |    674.70 |     10.00 |
|   row_limit LIMIT := param(0)                                       |    622.70 |     10.00 |
|     stream_merge KEYS=[(1 . "zip") ASC]                             |    622.70 |     30.00 |
|       row_limit LIMIT := param(0)                                   |    203.90 |     10.00 |
|         index_scan 1 := customers.zip                               |    203.90 |    333.33 |
|   index_scan 1 := customers.__idx_customers__PRIMARY, c_id = 1.c_id |      4.50 |      1.00 |
+---------------------------------------------------------------------+-----------+-----------+              

여기에서 쿼리 옵티마이저는 다음을 실행하기로 선택합니다.

  1. 모든 슬라이스에서 index_scan 연산자를 사용하여 병렬로 customers.zip 인덱스를 순서대로 읽습니다.
  2. "pushed down" row_limit 연산자로 결과를 제한합니다.
  3. stream_merge 연산자로 해당 결과를 병합하고 순서를 유지합니다.
  4. 다른 row_limit로 병합된 결과를 제한합니다.
  5. 우편번호 인덱스에서 찾은 c_id를 사용하여 나머지 행을 읽습니다.
  6. msjoin 연산자를 사용하여 equi-join을 수행합니다.

msjoin 연산자는 nljoin과 유사하지만, join 중에 정렬 순서를 유지하는 "merge sort nested-loop join"입니다. 이 플랜에서 정렬 순서는 우편번호 인덱스에 대해 읽혀지고 플랜에서 계속 유지됩니다. 이로써 결과를 정렬하기 위해 시그마 컨테이너를 생성할 필요가 없어집니다. 다시 말해, 진행 중에 이 플랜이 모든 결과를 스트림하므로 수백만 행을 읽을 때 중요한 고려 사항이 될 수 있습니다.

집계

관계형 데이터베이스에서 공동 태스크 중 하나는 큰 데이터를 가려내어 SUM(합계), AVERAGE(평균), MINIMUM(최소) 또는 MAXIMUM(최대)을 계산하는 것입니다. 이 쿼리는 데이터가 어떻게 집계될지 선언하는 명령문에 GROUP BY 절을 추가하여 실행됩니다. 또한, ClustrixDB는 GROUP BY에 대한 MySQL 확장을 구현하여 출력 열에 집계되지 않은 열을 포함시킵니다. GROUP BY 열과 집계되지 않은 열 사이에 일대일 관계가 없으면 반환되는 값이 정의되지 않았지만 집계되지 않은 값은 행의 값 중 하나가 됩니다. 우리는 데이터에서 zipstate 사이에 일대일 매핑을 가지고 있어서 우리를 위해 그 매핑을 생성하는 결과 집합을 생성할 수 있습니다.

sql> EXPLAIN SELECT zip, state FROM customers GROUP BY zip; 
+--------------------------------------------------------+-----------+-----------+
| Operation                                              | Est. Cost | Est. Rows |
+--------------------------------------------------------+-----------+-----------+
| sigma_distinct_combine KEYS=((1 . "zip"))              |   1303.90 |   1000.00 |
|   sigma_distinct_partial KEYS=((1 . "zip"))            |    203.90 |   1000.00 |
|     index_scan 1 := customers.__idx_customers__PRIMARY |    203.90 |    333.33 |
+--------------------------------------------------------+-----------+-----------+

이 쿼리는

  1. 먼저 index_scan을 수행하고 출력을 sigma_distinct_partial 연산자로 보냅니다.
  2. sigma_distinct_partial 연산자는 읽기와 동일한 노드에서 KEYS의 고유한 값 당 한 행의 출력을 생성합니다.
  3. 그런 다음 이 고유한 값은 sigma_distinct_combine 연산자로 보내집니다. 이 연산자는 쿼리가 시작된 노드의 KEYS에서 동일한 별개의 작업을 수행합니다.

보다 현실적인 집계를 위해 각 고객이 주문한 주문 수와 해당 고객 이름을 찾고 싶다고 가정해 봅시다.

sql> EXPLAIN SELECT c.name, COUNT(*) FROM orders o NATURAL JOIN customers c GROUP BY o.c_id; 
+-------------------------------------------------------------------------------+-----------+-----------+
| Operation                                                                     | Est. Cost | Est. Rows |
+-------------------------------------------------------------------------------+-----------+-----------+
| hash_aggregate_combine GROUPBY((1 . "c_id")) expr1 := countsum((0 . "expr1")) |  12780.38 |   4056.80 |
|   hash_aggregate_partial GROUPBY((1 . "c_id")) expr1 := count((0 . "expr0"))  |   7100.87 |   4056.80 |
|     compute expr0 := param(0)                                                 |   7100.87 |   4056.80 |
|       nljoin                                                                  |   7046.78 |   4056.80 |
|         stream_combine                                                        |    712.70 |   1000.00 |
|           index_scan 2 := customers.__idx_customers__PRIMARY                  |    203.90 |    333.33 |
|         index_scan 1 := orders.c_fk, c_id = 2.c_id                            |      6.33 |      4.06 |
+-------------------------------------------------------------------------------+-----------+-----------+

이 계획에서,

  1. Customer의 기본 키의 index_scan은 먼저 stream_combine과 결합하고 c_id는 다른 index_scan과 함께 orders.c_fk 인덱스를 읽는 데 사용됩니다.
  2. 이러한 결과는 nljoin 연산자를 사용해 orders.c_fk 인덱스를 읽는 노드에서 조인하고 동일한 노드에서 hash_aggregate_partial 연산자로 그룹화하고 카운트됩니다.
  3. 그런 다음 최종 그룹의 원본 노드에서 hash_aggregate_combine 연산자로 결과가 보내지고 행을 사용자에게 반환하기 전에 카운트됩니다.

요약

쿼리를 검사하는데 사용하는 ClustrixDB Sierra 옵티마이저의 EXPLAIN 출력에 대한 충분한 소개가 되었으면 합니다. EXPLAIN에 나타날 수 있는 연산자의 전체 목록은 List of Planner Operators를 참조하십시오. Sierra가 쿼리 최적화를 수행하는 방법에 대한 자세한 내용은 Distributed Database Architecture의 Query Optimizer을 참조하십시오.