Contoh kueri pecahan tunggal Pembatasan untuk kueri pecahan tunggal Bergabung sepenuhnya memenuhi syarat (eksplisit)Menyetel kunci pecahan aktif

Kueri pecahan tunggal di Aurora PostgreSQL Limitless Database

Kueri shard tunggal adalah kueri yang dapat dijalankan langsung pada pecahan sambil mempertahankan semantik SQL ACID. Ketika kueri seperti itu ditemui oleh perencana kueri pada router, perencana mendeteksinya dan melanjutkan untuk menekan seluruh kueri SQL ke pecahan yang sesuai.

Optimalisasi ini mengurangi jumlah perjalanan pulang pergi jaringan dari router ke pecahan, meningkatkan kinerja. Saat ini optimasi ini dilakukan untukINSERT,SELECT,UPDATE, dan DELETE kueri.

Topik

Contoh kueri pecahan tunggal
Pembatasan untuk kueri pecahan tunggal
Bergabung sepenuhnya memenuhi syarat (eksplisit)
Menyetel kunci pecahan aktif

Contoh kueri pecahan tunggal

Dalam contoh berikut, kita memiliki tabel shardedcustomers, dengan kunci shardcustomer_id, dan tabel referensi. 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)

Pembatasan untuk kueri pecahan tunggal

Kueri single-shard memiliki batasan berikut:

Fungsi

Jika kueri pecahan tunggal berisi fungsi, kueri memenuhi syarat untuk pengoptimalan pecahan tunggal hanya jika salah satu dari kondisi berikut berlaku:

Fungsinya tidak dapat diubah. Untuk informasi selengkapnya, lihat Volatilitas fungsi.
Fungsi ini bisa berubah, tetapi terdaftar dalam rds_aurora.limitless_distributed_functions tampilan. Untuk informasi selengkapnya, lihat Distribusi fungsi.

Tampilan

Jika kueri berisi satu atau beberapa tampilan, optimasi shard tunggal dinonaktifkan untuk kueri jika memiliki salah satu kondisi berikut:

Setiap tampilan memiliki security_barrier atribut.
Objek yang digunakan dalam kueri memerlukan beberapa hak istimewa pengguna. Misalnya, kueri berisi dua tampilan, dan tampilan dijalankan di bawah dua pengguna yang berbeda.


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)

MEMPERSIAPKAN dan MENGEKSEKUSI pernyataan

Aurora PostgreSQL Limitless Database mendukung optimasi single-shard untuk disiapkan,, dan pernyataan. SELECT UPDATE DELETE

Namun, jika Anda menggunakan pernyataan yang disiapkan untuk PREPARE dan EXECUTE dengan plan_cache_mode disetel ke'force_generic_plan', perencana kueri menolak pengoptimalan pecahan tunggal untuk kueri tersebut.

PL/PGSQL

Kueri dengan PL/pgSQL variables are run as implicitly prepared statements. If a query contains any PL/pgSQL variabel, perencana kueri menolak optimasi shard tunggal.

Optimasi didukung dalam PL/pgSQL block if the statement doesn't contain any PL/pgSQL variabel.

Bergabung sepenuhnya memenuhi syarat (eksplisit)

Optimasi single-shard didasarkan pada eliminasi partisi. Pengoptimal PostgreSQL menghilangkan partisi berdasarkan kondisi konstan. Jika Aurora PostgreSQL Limitless Database menemukan bahwa semua partisi dan tabel yang tersisa berada pada pecahan yang sama, ini menandai kueri yang memenuhi syarat untuk optimasi shard tunggal. Semua kondisi filter harus eksplisit agar eliminasi partisi berfungsi. Aurora PostgreSQL Limitless Database tidak dapat menghilangkan partisi tanpa satu atau lebih predikat gabungan atau memfilter predikat pada kunci pecahan setiap tabel sharded dalam pernyataan.

Asumsikan bahwa kita telah mempartisicustomers,orders, dan order_details tabel berdasarkan kolom. customer_id Dalam skema ini, aplikasi mencoba menyimpan semua data untuk pelanggan pada satu pecahan.

Pertimbangkan kueri berikut:


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;

Kueri ini mengambil semua data untuk pelanggan (c.customer_id = 1). Data untuk pelanggan ini ada pada satu pecahan, tetapi Aurora PostgreSQL Limitless Database tidak memenuhi syarat kueri ini sebagai kueri pecahan tunggal. Proses pengoptimal untuk kueri adalah sebagai berikut:

Pengoptimal dapat menghilangkan partisi untuk customers dan orders berdasarkan kondisi berikut:


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

Pengoptimal tidak dapat menghilangkan partisi apa punorder_details, karena tidak ada kondisi konstan di atas meja.
Optimizer menyimpulkan bahwa ia telah membaca semua partisi dari. order_details Oleh karena itu, kueri tidak dapat memenuhi syarat untuk pengoptimalan pecahan tunggal.

Untuk menjadikan ini kueri pecahan tunggal, kami menambahkan kondisi gabungan eksplisit berikut:


o.customer_id = od.customer_id

Kueri yang diubah terlihat seperti ini:


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;

Sekarang pengoptimal dapat menghilangkan partisi untuk. order_details Kueri baru menjadi kueri pecahan tunggal dan memenuhi syarat untuk pengoptimalan.

Menyetel kunci pecahan aktif

Fitur ini memungkinkan Anda untuk mengatur kunci shard tunggal saat menanyakan database, menyebabkan semua SELECT dan kueri DHTML ditambahkan dengan kunci shard sebagai predikat konstan. Fitur ini berguna jika Anda telah bermigrasi ke Aurora PostgreSQL Limitless Database dan telah mendenormalisasi skema dengan menambahkan kunci shard ke tabel.

Anda dapat menambahkan predikat kunci shard secara otomatis ke logika SQL yang ada, tanpa mengubah semantik kueri. Menambahkan predikat kunci shard aktif dilakukan hanya untuk tabel yang kompatibel.

Fitur kunci pecahan aktif menggunakan rds_aurora.limitless_active_shard_key variabel, yang memiliki sintaks berikut:


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

Beberapa pertimbangan tentang kunci pecahan aktif dan kunci asing:

Sebuah tabel sharded dapat memiliki kendala kunci asing jika tabel induk dan anak ditempatkan dan kunci asing adalah superset dari kunci shard.
Tabel sharded dapat memiliki kendala kunci asing ke tabel referensi.
Tabel referensi dapat memiliki kendala kunci asing ke tabel referensi lain.

Asumsikan bahwa kita memiliki customers tabel yang di-sharded pada customer_id kolom.


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;

Dengan set kunci pecahan aktif, kueri memiliki transformasi berikut.

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;

Gabungan

Saat melakukan operasi gabungan pada tabel dengan kunci pecahan aktif, predikat kunci shard secara otomatis ditambahkan ke semua tabel yang terlibat dalam gabungan. Penambahan otomatis predikat kunci shard ini hanya terjadi ketika semua tabel dalam kueri milik grup kolokasi yang sama. Jika kueri melibatkan tabel dari grup kolokasi yang berbeda, kesalahan akan muncul sebagai gantinya.

Asumsikan bahwa kita juga memiliki orders dan order_details tabel yang ditempatkan dengan customers tabel.


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;

Ambil 10 faktur pesanan terakhir untuk pelanggan yang ID pelanggannya 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;

Kueri ini diubah menjadi sebagai berikut:


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;

Tabel yang kompatibel dengan kunci pecahan aktif

Predikat kunci shard ditambahkan hanya ke tabel yang kompatibel dengan kunci pecahan aktif. Sebuah tabel dianggap kompatibel jika memiliki jumlah kolom yang sama dalam kunci shard seperti yang ditentukan dalam rds_aurora.limitless_active_shard_key variabel. Jika kueri melibatkan tabel yang tidak kompatibel dengan kunci pecahan aktif, sistem akan memunculkan kesalahan alih-alih melanjutkan kueri.

Misalnya:


-- 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;

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Meminta Database Tanpa Batas

Kueri terdistribusi