Aurora PostgreSQL 無限資料庫中的單一碎片查詢 - Amazon Aurora
單一碎片查詢範例單一碎片查詢的限制完整 （明確） 聯結設定作用中碎片索引鍵

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

Aurora PostgreSQL 無限資料庫中的單一碎片查詢

單一碎片查詢是可以直接在碎片上執行的查詢，同時維持 SQL ACID 語意。當路由器上的查詢規劃器遇到這類查詢時，規劃器會偵測到該查詢並繼續將整個 SQL 查詢下推至對應的碎片。

此最佳化可減少從路由器到碎片的網路往返次數，進而改善效能。目前針對 INSERT、SELECT、 UPDATE和 DELETE查詢執行此最佳化。

單一碎片查詢範例

在下列範例中，我們有碎片資料表 customers、碎片索引鍵 customer_id和參考資料表 zipcodes。

SELECT
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM customers WHERE customer_id = 100;

                       QUERY PLAN                        
---------------------------------------------------------
 Foreign Scan
   Output: customer_id, other_id, customer_name, balance
   Remote SQL:  SELECT customer_id,
     other_id,
     customer_name,
     balance
    FROM public.customers
   WHERE (customer_id = 100)
 Single Shard Optimized
(9 rows)
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM orders
    LEFT JOIN zipcodes ON orders.zipcode_id = zipcodes.zipcode_id
    WHERE customer_id = 11;

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: customer_id, order_id, zipcode_id, customer_name, balance, zipcodes.zipcode_id, zipcodes.city
   Remote SQL:  SELECT orders.customer_id,
     orders.order_id,
     orders.zipcode_id,
     orders.customer_name,
     orders.balance,
     zipcodes.zipcode_id,
     zipcodes.city
    FROM (public.orders
      LEFT JOIN public.zipcodes ON ((orders.zipcode_id = zipcodes.zipcode_id)))
   WHERE (orders.customer_id = 11)
 Single Shard Optimized
(13 rows)
INSERT
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO customers
    (customer_id, other_id, customer_name, balance)
    VALUES (1, 10, 'saikiran', 1000);

                      QUERY PLAN                       
-------------------------------------------------------
 Insert on public.customers
   ->  Result
         Output: 1, 10, 'saikiran'::text, '1000'::real
 Single Shard Optimized
(4 rows)
UPDATE
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) UPDATE orders SET balance = balance + 100
    WHERE customer_id = 100;

                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Update on public.orders
   Foreign Update on public.orders_fs00002 orders_1
   ->  Foreign Update
         Remote SQL:  UPDATE public.orders SET balance = (balance + (100)::double precision)
   WHERE (customer_id = 100)
 Single Shard Optimized
(6 rows)
DELETE
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM orders
    WHERE customer_id = 100 and balance = 0;

                             QUERY PLAN                              
---------------------------------------------------------------------
 Delete on public.orders
   Foreign Delete on public.orders_fs00002 orders_1
   ->  Foreign Delete
         Remote SQL:  DELETE FROM public.orders
   WHERE ((customer_id = 100) AND (balance = (0)::double precision))
 Single Shard Optimized
(6 rows)

單一碎片查詢的限制

單一碎片查詢有下列限制：

函數

如果單一碎片查詢包含 函數，則只有在下列其中一個條件適用時，查詢才符合單一碎片最佳化的資格：

  • 函數不可變。如需詳細資訊，請參閱函數波動。

  • 函數是可變的，但已在rds_aurora.limitless_distributed_functions檢視中註冊。如需詳細資訊，請參閱函數分佈。

檢視

如果查詢包含一或多個檢視，且具有下列其中一個條件，則會停用查詢的單一碎片最佳化：

  • 任何檢視都有 security_barrier 屬性。

  • 查詢中使用的物件需要多個使用者權限。例如，查詢包含兩個檢視，而檢視是在兩個不同的使用者下執行。

CREATE VIEW v1 AS SELECT customer_name FROM customers c WHERE c.customer_id =  1;
CREATE VIEW v2 WITH (security_barrier) AS SELECT customer_name FROM customers c WHERE c.customer_id =  1;

postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v1;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0)
   Output: customer_name
   Remote Plans from Shard postgres_s3:
         Seq Scan on public.customers_ts00001 c  (cost=0.00..24.12 rows=6 width=32)
           Output: c.customer_name
           Filter: (c.customer_id = 1)
         Query Identifier: -6005737533846718506
   Remote SQL:  SELECT customer_name
    FROM ( SELECT c.customer_name
            FROM public.customers c
           WHERE (c.customer_id = 1)) v1
 Query Identifier: -5754424854414896228
(12 rows)


postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v2;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Foreign Scan on public.customers_fs00001 c  (cost=100.00..128.41 rows=7 width=32)
   Output: c.customer_name
   Remote Plans from Shard postgres_s3:
         Seq Scan on public.customers_ts00001 customers  (cost=0.00..24.12 rows=6 width=32)
           Output: customers.customer_name
           Filter: (customers.customer_id = 1)
         Query Identifier: 4136563775490008117
   Remote SQL: SELECT customer_name FROM public.customers WHERE ((customer_id = 1))
 Query Identifier: 5056054318010163757
(9 rows)
PREPARE 和 EXECUTE 陳述式

Aurora PostgreSQL 無限制資料庫支援預備 SELECT、 UPDATE和 DELETE陳述式的單一碎片最佳化。

不過，如果您使用 PREPARE和 的預備陳述式EXECUTE，並將 plan_cache_mode設為 'force_generic_plan'，查詢規劃器會拒絕該查詢的單一碎片最佳化。

PL/pgSQL

具有 PL/pgSQL 變數的查詢會以隱含預備陳述式執行。如果查詢包含任何 PL/pgSQL 變數，查詢規劃器會拒絕單一碎片最佳化。

如果陳述式不包含任何 PL/pgSQL 變數，PL/pgSQL 區塊中支援最佳化。

完整 （明確） 聯結

單一碎片最佳化是以分割區消除為基礎。PostgreSQL 最佳化工具會根據固定條件來消除分割區。如果 Aurora PostgreSQL 無限制資料庫發現所有剩餘的分割區和資料表都位於相同的碎片上，則會標記符合單一碎片最佳化資格的查詢。所有篩選條件都必須明確，才能消除分割區。Aurora PostgreSQL 無限制資料庫無法在陳述式中每個碎片資料表的碎片索引鍵上消除沒有一或多個聯結述詞或篩選述詞的分割區。

假設我們已根據資料customer_id欄分割 orders、 customers和 order_details資料表。在此結構描述中，應用程式會嘗試將客戶的所有資料保留在單一碎片上。

請考處下列查詢：

SELECT * FROM 
    customers c, orders o, order_details od 
WHERE c.customer_id = o.customer_id
    AND od.order_id = o.order_id
    AND c.customer_id = 1;

此查詢會擷取客戶 () 的所有資料c.customer_id = 1。此客戶的資料位於單一碎片上，但 Aurora PostgreSQL 無限制資料庫不會將此查詢視為單一碎片查詢。查詢的最佳化工具程序如下：

  1. 根據orders下列條件，最佳化工具可以消除 customers和 的分割區：

    c.customer_id = 1
c.customer_id = o.customer_id
o.customer_id =  1 (transitive implicit condition)

  2. 最佳化工具無法消除 的任何分割區order_details，因為資料表上沒有任何常數條件。

  3. 最佳化工具的結論是，它已從 讀取所有分割區order_details。因此，查詢無法符合單一碎片最佳化的資格。

為了將此設為單一碎片查詢，我們新增了下列明確聯結條件：

o.customer_id = od.customer_id

變更的查詢如下所示：

SELECT * FROM 
    customers c, orders o,  order_details od 
WHERE c.customer_id = o.customer_id
     AND o.customer_id = od.customer_id
     AND od. order_id = o. order_id
 AND c.customer_id =  1;

現在最佳化工具可以消除 的分割區order_details。新查詢會成為單一碎片查詢，並符合最佳化的資格。

設定作用中碎片索引鍵

此功能可讓您在查詢資料庫時設定單一碎片金鑰，導致所有 SELECT和 DML 查詢都附加在碎片金鑰做為常數述詞。如果您已遷移至 Aurora PostgreSQL 無限制資料庫，並將碎片索引鍵新增至資料表以取消標準化結構描述，此功能非常有用。

您可以自動將碎片金鑰述詞附加至現有的 SQL 邏輯，而無需變更查詢的語意。附加作用中碎片金鑰述詞只會針對相容的資料表完成。

作用中碎片金鑰功能使用 rds_aurora.limitless_active_shard_key變數，其具有下列語法：

SET [session | local] rds_aurora.limitless_active_shard_key = '{"col1_value", "col2_value", ...}';

作用中碎片索引鍵和外部索引鍵的一些考量：

  • 如果父資料表和子資料表共置，且外部索引鍵是碎片索引鍵的超集合，則碎片資料表可能會有外部索引鍵限制。

  • 碎片資料表對參考資料表可能有外部索引鍵限制。

  • 參考資料表可以對另一個參考資料表具有外部索引鍵限制。

假設我們在customer_id資料欄上有碎片的customers資料表。

BEGIN;
SET local rds_aurora.limitless_create_table_mode='sharded';
SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}';
CREATE TABLE customers(customer_id int PRIMARY KEY, name text , email text);
COMMIT;

使用作用中碎片金鑰集時，查詢具有下列轉換。

SELECT
SET rds_aurora.limitless_active_shard_key = '{"123"}';
SELECT * FROM customers;

-- This statement is changed to:
SELECT * FROM customers WHERE customer_id = '123'::int;
INSERT
SET rds_aurora.limitless_active_shard_key = '{"123"}';
INSERT INTO customers(name, email) VALUES('Alex', 'alex@example.com');

-- This statement is changed to:
INSERT INTO customers(customer_id, name, email) VALUES('123'::int, 'Alex', 'alex@example.com');
UPDATE
SET rds_aurora.limitless_active_shard_key = '{"123"}';
UPDATE customers SET email = 'alex_new_email@example.com';

-- This statement is changed to:
UPDATE customers SET email = 'alex_new_email@example.com' WHERE customer_id = '123'::int;
DELETE
SET rds_aurora.limitless_active_shard_key = '{"123"}';
DELETE FROM customers;

-- This statement is changed to:
DELETE FROM customers WHERE customer_id = '123'::int;
聯結

在具有作用中碎片索引鍵的資料表上執行聯結操作時，碎片索引鍵述詞會自動新增至聯結中涉及的所有資料表。只有在查詢中的所有資料表都屬於相同的共置群組時，才會自動新增碎片金鑰述詞。如果查詢涉及來自不同共置群組的資料表，則會改為引發錯誤。

假設我們也有與order_details資料表共置的 orders和 customers資料表。

SET local rds_aurora.limitless_create_table_mode='sharded';
SET local rds_aurora.limitless_create_table_collocate_with='customers';
SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}';
CREATE TABLE orders (id int , customer_id int, total_amount int, date date);
CREATE TABLE order_details (id int , order_id int, customer_id int, product_name VARCHAR(100), price int);
COMMIT;

擷取客戶 ID 為 10 的客戶最後 10 張訂單發票。

SET rds_aurora.limitless_active_shard_key = '{"10"}';
SELECT * FROM customers, orders, order_details WHERE
    orders.customer_id = customers.customer_id AND
    order_details.order_id = orders.order_id AND
    customers.customer_id = 10
    order by order_date limit 10;

此查詢會轉換為下列項目：

SELECT * FROM customers, orders, order_details WHERE
    orders.customer_id = customers.customer_id AND
    orders.order_id = order_details.order_id AND
    customers.customer_id = 10 AND
    order_details.customer_id = 10 AND
    orders.customer_id = 10 AND
    ORDER BY "order_date" LIMIT 10;
作用中碎片索引鍵相容資料表

碎片金鑰述詞只會新增至與作用中碎片金鑰相容的資料表。如果資料表的碎片索引鍵中有與 rds_aurora.limitless_active_shard_key變數中指定的相同數量的資料欄，則該資料表會被視為相容。如果查詢涉及與作用中碎片索引鍵不相容的資料表，系統會引發錯誤，而不是繼續進行查詢。

例如：

-- Compatible table
SET rds_aurora.limitless_active_shard_key = '{"10"}';

-- The following query works because the customers table is sharded on one column.
SELECT * FROM customers;
  
-- Incompatible table
SET rds_aurora.limitless_active_shard_key = '{"10","20"}';

-- The following query raises a error because the customers table isn't sharded on two columns.
 SELECT * FROM customers;