Penyetelan performa dengan tampilan materialisasi

Artikel
06/01/2023

Tampilan materialisasi untuk kumpulan SQL di Azure Synapse memberikan metode dengan pemeliharaan yang rendah untuk kueri analitik yang kompleks guna mendapatkan performa yang cepat tanpa perubahan kueri. Artikel ini membahas panduan umum tentang penggunaan tampilan materialisasi.

Tampilan materialisasi vs. tampilan standar

Kumpulan SQL khusus di Azure Synapse mendukung tampilan standar dan materialisasi. Keduanya adalah tabel virtual yang dibuat dengan ekspresi SELECT dan disajikan ke kueri sebagai tabel logika. Tampilan merangkum kompleksitas komputasi data umum dan menambahkan lapisan abstraksi ke perubahan komputasi sehingga tidak perlu menulis ulang kueri.

Tampilan standar melakukan komputasi datanya setiap kali tampilan digunakan. Tidak ada data yang tersimpan pada disk. Pengguna biasanya menggunakan tampilan standar sebagai alat yang membantu menata objek dan kueri logika dalam sebuah kumpulan SQL khusus. Untuk menggunakan tampilan standar, kueri perlu membuat referensi langsung ke tampilan tersebut.

Tampilan materialisasi melakukan komputasi, menyimpan, dan mempertahankan datanya terlebih dahulu di kumpulan SQL khusus seperti tabel. Komputasi ulang tidak diperlukan setiap kali tampilan materialisasi digunakan. Itu sebabnya kueri yang menggunakan semua atau satu subset data dalam tampilan materialisasi dapat memperoleh performa yang lebih cepat. Lebih baik lagi, kueri dapat menggunakan tampilan materialisasi tanpa membuat referensi langsung, jadi tidak perlu mengubah kode aplikasi.

Sebagian besar persyaratan pada tampilan standar masih berlaku untuk tampilan materialisasi. Untuk detail tentang sintaks tampilan materialisasi dan persyaratan lainnya, lihat CREATE MATERIALIZED VIEW AS SELECT

Perbandingan	Tampilan	Tampilan Materialisasi
Lihat definisi	Disimpan di kumpulan SQL khusus.	Disimpan di kumpulan SQL khusus.
Tampilan konten	Dihasilkan setiap kali tampilan digunakan.	Diproses dan disimpan terlebih dahulu dalam kumpulan SQL khusus selama pembuatan tampilan. Diperbarui saat data ditambahkan ke tabel yang mendasarinya.
Refresh data	Selalu diperbarui	Selalu diperbarui
Kecepatan untuk mengambil data tampilan dari kueri kompleks	Lambat	Cepat
Penyimpanan ekstra	Tidak	Ya
Sintaks	BUAT TAMPILAN	CREATE MATERIALIZED VIEW AS SELECT

Manfaat menggunakan tampilan materialisasi

Tampilan materialisasi yang didesain dengan benar memberikan keuntungan berikut:

Kurangi waktu eksekusi untuk kueri kompleks dengan JOIN dan fungsi agregat. Makin kompleks kueri, makin tinggi potensi penghematan waktu eksekusi. Keuntungan terbanyak diperoleh saat biaya komputasi kueri tinggi dan himpunan data yang dihasilkan kecil.
Pengoptimal kueri di kumpulan SQL khusus dapat secara otomatis menggunakan tampilan materialisasi yang disebarkan untuk meningkatkan rencana eksekusi kueri. Proses ini bersifat transparan bagi pengguna yang memberikan performa kueri yang lebih cepat dan tidak memerlukan kueri untuk membuat referensi langsung ke tampilan materialisasi.
Perlu pemeliharaan rendah pada tampilan. Semua perubahan data bertambah bertahap dari tabel dasar secara otomatis ditambahkan ke tampilan yang terwujud secara sinkron, yang berarti tabel dasar dan tampilan yang terwujud telah diperbarui dalam transaksi yang sama. Desain ini memungkinkan kueri tampilan terwujud untuk mengembalikan data yang sama seperti kueri tabel dasar secara langsung.
Data dalam tampilan terwujud dapat didistribusikan secara berbeda dari tabel dasar.
Data dalam tampilan materialisasi mendapatkan keuntungan ketersediaan dan ketahanan tinggi yang sama dengan data dalam tabel reguler.

Tampilan materialisasi yang diterapkan di kumpulan SQL khusus juga memberikan manfaat berikut:

Dibandingkan dengan penyedia gudang data lainnya, tampilan materialisasi yang diterapkan dalam kumpulan SQL khusus juga memberikan keuntungan tambahan berikut:

Dukungan fungsi agregat yang luas. Lihat MEMBUAT TAMPILAN TERWUJUD SEBAGAI PILIH (TRANSACT-SQL).
Dukungan untuk rekomendasi tampilan material khusus kueri. Lihat MENJELASKAN (Transact-SQL).
Refresh data otomatis dan sinkron dengan perubahan data di tabel dasar. Tidak diperlukan tindakan pengguna.

Skenario umum

Tampilan materialisasi biasanya digunakan dalam skenario berikut:

Diperlukannya peningkatan performa kueri analitik yang kompleks terhadap ukuran data yang besar

Kueri analitik yang kompleks biasanya menggunakan lebih banyak fungsi agregasi dan gabungan tabel, yang menyebabkan lebih banyak operasi komputasi yang berat seperti pengacakan dan penggabungan dalam menjalankan kueri. Itu sebabnya kueri analitik yang kompleks membutuhkan waktu lebih lama untuk diselesaikan, terutama pada tabel besar.

Pengguna dapat membuat tampilan materialisasi untuk data yang ditampilkan dari komputasi umum kueri, sehingga tidak ada komputasi ulang yang diperlukan saat data ini diperlukan oleh kueri, yang memungkinkan biaya komputasi yang lebih rendah dan respons kueri yang lebih cepat.

Diperlukannya performa yang lebih cepat dengan perubahan kueri minimum atau tanpa perubahan

Perubahan skema dan kueri di kumpulan SQL khusus biasanya dijaga seminimal mungkin untuk mendukung operasi dan pelaporan ETL reguler. Pengguna dapat menggunakan tampilan materialisasi untuk penyetelan performa kueri jika biaya yang dikeluarkan oleh tampilan dapat diimbangi dengan perolehan yang didapatkan dalam performa kueri.

Dibandingkan dengan opsi penyetelan lainnya, seperti penskalaan dan manajemen statistik, tampilan materialisasi ini adalah perubahan produksi yang dampaknya jauh lebih sedikit untuk menciptakan dan mempertahankan tampilan materialisasi serta potensi perolehan performanya yang juga lebih tinggi.

Membuat atau mempertahankan tampilan materialisasi tidak memengaruhi kueri yang berjalan terhadap tabel dasar.
Pengoptimal kueri dapat menggunakan tampilan materialisasi yang disebarkan secara otomatis tanpa referensi tampilan langsung dalam sebuah kueri. Kemampuan ini mengurangi kebutuhan akan perubahan kueri dalam penyetelan performa.

Diperlukannya strategi distribusi data yang berbeda untuk performa kueri yang lebih cepat

Kumpulan SQL khusus adalah sistem pemrosesan kueri terdistribusi. Data dalam tabel SQL didistribusikan hingga 60 node menggunakan salah satu dari tiga strategi distribusi (hash, round_robin, atau direplikasi).

Distribusi data ditentukan pada waktu pembuatan tabel dan tetap tidak berubah hingga tabel dihilangkan. Tampilan materialisasi yang merupakan tabel virtual pada disk mendukung distribusi data hash dan round_robin. Pengguna dapat memilih distribusi data yang berbeda dari tabel dasar tetapi optimal untuk performa kueri yang sering menggunakan tampilan.

Panduan desain

Berikut adalah panduan umum untuk menggunakan tampilan materialisasi guna meningkatkan performa kueri:

Desain untuk beban kerja Anda

Sebelum Anda mulai membuat tampilan materialisasi, penting bagi Anda untuk memiliki pemahaman mendalam tentang beban kerja dalam hal pola kueri, kepentingan, frekuensi, dan ukuran data yang dihasilkan.

Pengguna dapat menjalankan EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> tampilan materialisasi yang direkomendasikan oleh pengoptimal kueri. Karena rekomendasi ini bersifat spesifik pada kueri, tampilan materialisasi yang menguntungkan satu kueri saja mungkin tidak optimal untuk kueri lain dalam beban kerja yang sama.

Evaluasi rekomendasi ini dengan tetap mempertimbangkan kebutuhan beban kerja Anda. Tampilan materialisasi yang ideal adalah yang menguntungkan performa beban kerja.

Perhatikan konsekuensi yang dapat terjadi antara kueri yang lebih cepat dan biayanya

Untuk setiap tampilan materialisasi, ada biaya penyimpanan data dan biaya untuk mempertahankan tampilannya. Saat data berubah dalam tabel dasar, ukuran tampilan materialisasi meningkat dan struktur fisiknya juga berubah. Untuk menghindari penurunan kinerja kueri, setiap tampilan materialisasi dipertahankan secara terpisah oleh mesin SQL.

Beban kerja pemeliharaan semakin tinggi saat jumlah tampilan materialisasi dan perubahan tabel dasar meningkat. Pengguna harus memeriksa apakah biaya yang dikeluarkan dari semua tampilan materialisasi dapat diimbangi dengan peningkatan performa kueri.

Anda dapat menjalankan kueri ini untuk menghasilkan daftar tampilan materialisasi dalam kumpulan SQL khusus:

SELECT V.name as materialized_view, V.object_id
FROM sys.views V
JOIN sys.indexes I ON V.object_id= I.object_id AND I.index_id < 2;

Opsi untuk mengurangi jumlah tampilan materialisasi:

Identifikasi himpunan data umum yang sering digunakan oleh kueri yang kompleks dalam beban kerja Anda. Buat tampilan materialisasi untuk menyimpan himpunan data tersebut sehingga pengoptimal dapat menggunakannya sebagai blok penyusun saat membuat rencana eksekusi.
Hilangkan tampilan materialisasi yang jarang digunakan atau tidak lagi diperlukan. Tampilan materialisasi yang dinonaktifkan tidak dipertahankan tetapi masih dikenakan biaya penyimpanan.
Gabungkan tampilan materialisasi yang dibuat pada tabel dasar yang sama atau serupa meskipun datanya tidak tumpang tindih. Menggabungkan tampilan materialisasi dapat menghasilkan tampilan dengan ukuran yang lebih besar daripada jumlah tampilan yang terpisah, namun biaya pemeliharaan tampilan harusnya berkurang. Contohnya:


-- Query 1 would benefit from having a materialized view created with this SELECT statement

SELECT A, SUM(B)
FROM T
GROUP BY A

-- Query 2 would benefit from having a materialized view created with this SELECT statement

SELECT C, SUM(D)
FROM T
GROUP BY C

-- You could create a single mateiralized view of this form

SELECT A, C, SUM(B), SUM(D)
FROM T
GROUP BY A, C

Tidak semua penyesuaian performa memerlukan perubahan kueri

Pengoptimal kueri SQL dapat secara otomatis menggunakan tampilan materialisasi yang disebarkan untuk meningkatkan performa kueri. Dukungan ini diterapkan secara transparan ke kueri yang tidak mereferensikan tampilan dan kueri yang menggunakan agregat yang tidak didukung dalam pembuatan tampilan materialisasi. Perubahan kueri tidak diperlukan. Anda dapat memeriksa estimasi rencana eksekusi kueri untuk mengonfirmasi apakah tampilan materialisasi digunakan.

Memantau tampilan materialisasi

Tampilan materialisasi disimpan di kumpulan SQL khusus, sama seperti tabel dengan indeks penyimpan kolom (CCI) dalam kluster. Membaca data dari tampilan materialisasi termasuk memindai segmen indeks CCI dan menerapkan perubahan inkremental dari tabel dasar. Saat jumlah perubahan inkremental terlalu tinggi, menyelesaikan kueri dari tampilan materialisasi dapat memakan waktu lebih lama daripada langsung mengkueri tabel dasar.

Untuk menghindari penurunan kinerja kueri, sebaiknya jalankan DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD untuk memantau overhead_ratio tampilan (total_rows / max(1, base_view_row)). Pengguna harus MEMBANGUN KEMBALI tampilan materialisasi jika overhead_ratio terlalu tinggi.

Penembolokan tampilan materialisasi dan tataan hasil

Kedua fitur ini digunakan untuk penyetelan kinerja kueri dalam kumpulan SQL khusus. Penembolokan tataan hasil digunakan untuk mendapatkan konkurensi tinggi dan respons cepat dari kueri berulang terhadap data statis.

Untuk menggunakan hasil yang telah di-cache, formulir kueri permintaan cache harus cocok dengan kueri yang menghasilkan cache. Selain itu, hasil cache harus berlaku untuk seluruh kueri.

Tampilan materialisasi memungkinkan perubahan data dalam tabel dasar. Data dalam tampilan materialisasi dapat diterapkan ke suatu bagian sebuah kueri. Dukungan ini memungkinkan tampilan materialisasi yang sama untuk digunakan oleh kueri yang berbeda, yang berbagi beberapa komputasi untuk performa yang lebih cepat.

Contoh

Contoh ini menggunakan kueri seperti TPCDS yang menemukan pelanggan yang menghabiskan lebih banyak uang melalui katalog daripada di toko, mengidentifikasi pelanggan pilihan dan negara/wilayah asal mereka. Kuerinya melibatkan pemilihan 100 rekaman TERATAS dari UNION dari tiga pernyataan sub-SELECT yang melibatkan SUM() dan GROUP BY.

WITH year_total AS (
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
       ,'s' sale_type
FROM customer
     ,store_sales
     ,date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
       ,'c' sale_type
FROM customer
     ,catalog_sales
     ,date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
       ,'w' sale_type
FROM customer
     ,web_sales
     ,date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
  SELECT TOP 100
                  t_s_secyear.customer_id
                 ,t_s_secyear.customer_first_name
                 ,t_s_secyear.customer_last_name
                 ,t_s_secyear.customer_birth_country
FROM year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_c_firstyear
     ,year_total t_c_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_c_secyear.customer_id
   AND t_s_firstyear.customer_id = t_c_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_secyear.customer_id
   AND t_s_firstyear.sale_type = 's'
   AND t_c_firstyear.sale_type = 'c'
   AND t_w_firstyear.sale_type = 'w'
   AND t_s_secyear.sale_type = 's'
   AND t_c_secyear.sale_type = 'c'
   AND t_w_secyear.sale_type = 'w'
   AND t_s_firstyear.dyear+0 =  1999
   AND t_s_secyear.dyear+0 = 1999+1
   AND t_c_firstyear.dyear+0 =  1999
   AND t_c_secyear.dyear+0 =  1999+1
   AND t_w_firstyear.dyear+0 = 1999
   AND t_w_secyear.dyear+0 = 1999+1
   AND t_s_firstyear.year_total > 0
   AND t_c_firstyear.year_total > 0
   AND t_w_firstyear.year_total > 0
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE NULL END
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE NULL END
ORDER BY t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_birth_country
OPTION ( LABEL = 'Query04-af359846-253-3');

Periksa perkiraan rencana eksekusi kueri. Terdapat 18 pengacakan dan 17 operasi penggabungan, yang membutuhkan lebih banyak waktu untuk dijalankan. Sekarang mari kita buat satu tampilan materialisasi untuk masing-masing dari tiga pernyataan sub-SELECT.

CREATE materialized view nbViewSS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.store_sales
     ,dbo.date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
GO
CREATE materialized view nbViewCS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
          , count_big(*) as cb
FROM dbo.customer
     ,dbo.catalog_sales
     ,dbo.date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

GO
CREATE materialized view nbViewWS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.web_sales
     ,dbo.date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

Periksa kembali rencana eksekusi kueri asli. Sekarang jumlah gabungan berubah dari 17 menjadi 5 dan tidak ada pengacakan lagi. Pilih ikon operasi Filter dalam paket, Daftar Output-nya memperlihatkan bahwa data dibaca dari tampilan materialisasi, bukan tabel dasar.

Plan_Output_List_with_Materialized_Views

Dengan tampilan materialisasi, kueri yang sama berjalan jauh lebih cepat tanpa ada perubahan kode.

Langkah berikutnya

Untuk tips pengembangan selengkapnya, lihat Ringkasan pengembangan kumpulan SQL khusus.