Penyetelan performa dengan tampilan materialisasi

Tip

Microsoft Fabric Data Warehouse adalah gudang relasional skala perusahaan pada fondasi data lake, dengan arsitektur siap masa depan, AI bawaan, dan fitur baru. Jika Anda baru menggunakan pergudangan data, mulailah dengan Fabric Data Warehouse. Beban kerja kumpulan SQL terdedikasi yang ada dapat ditingkatkan ke Fabric untuk mengakses kemampuan baru di seluruh ilmu data, analitik waktu nyata, dan pelaporan.

Tampilan materialisasi untuk kumpulan SQL khusus di Azure Synapse menyediakan metode pemeliharaan rendah untuk kueri analitik yang kompleks untuk mendapatkan performa cepat tanpa perubahan kueri apa pun. Artikel ini membahas panduan umum tentang menggunakan tampilan materialisasi.

Tampilan materialisasi vs. tampilan standar

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

Tampilan standar menghitung datanya setiap kali tampilan digunakan. Tidak ada data yang disimpan pada disk. Orang biasanya menggunakan tampilan standar sebagai alat yang membantu mengatur objek dan kueri logis di kumpulan SQL khusus. Untuk menggunakan tampilan standar, kueri perlu membuat referensi langsung ke tampilan tersebut.

Tampilan materialisasi menghitung terlebih dahulu, menyimpan, dan memelihara datanya dalam kumpulan SQL khusus, sama seperti tabel. Tidak diperlukan penghitungan ulang setiap kali tampilan terwujud digunakan. Itulah sebabnya kueri yang menggunakan semua atau subset data dalam tampilan materialisasi bisa mendapatkan performa yang lebih cepat. Lebih baik lagi, kueri dapat menggunakan tampilan materialisasi tanpa membuat referensi langsung ke sana, sehingga 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	View	Tampilan Materialisasi
Lihat definisi	Disimpan di kumpulan SQL khusus.	Disimpan di kumpulan SQL khusus.
Menampilkan konten	Dihasilkan setiap kali tampilan digunakan.	Pra-proses dan disimpan di kumpulan SQL khusus selama pembuatan tampilan. Diperbarui saat data ditambahkan ke tabel dasar.
Pembaruan data	Selalu diperbarui	Selalu diperbarui
Kecepatan untuk mengambil data tampilan dari kueri kompleks	Slow	Cepat
Penyimpanan tambahan	Tidak.	Ya
Sintaksis	MEMBUAT TAMPILAN	MEMBUAT TAMPILAN MATERIALISASI SEBAGAI PILIH

Manfaat menggunakan tampilan materialisasi

Tampilan termaterialisasi yang dirancang 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 terbesar diperoleh saat biaya komputasi kueri tinggi dan himpunan data yang dihasilkan kecil.
Pengoptimal di kumpulan SQL khusus dapat secara otomatis menggunakan tampilan materialisasi yang disebarkan untuk meningkatkan rencana eksekusi kueri. Proses ini transparan bagi pengguna, memberikan kinerja kueri yang lebih cepat dan tidak memerlukan kueri untuk merujuk langsung ke tampilan terwujud.
Memerlukan pemeliharaan rendah pada tampilan. Semua perubahan data bertahap dari tabel dasar secara otomatis ditambahkan ke tampilan materialisasi secara sinkron, yang berarti tabel dasar dan tampilan materialisasi diperbarui dalam transaksi yang sama. Desain ini memungkinkan pengkuerian tampilan terwujud untuk mengembalikan data yang sama seperti mengkueri tabel dasar secara langsung.
Data dalam tampilan terwujud dapat didistribusikan secara berbeda dari tabel dasar.
Data dalam tampilan terwujud mendapatkan manfaat ketersediaan dan ketahanan tinggi yang sama dengan data dalam tabel reguler.

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

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

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

Skenario umum

Tampilan terwujud biasanya digunakan dalam skenario berikut:

Perlu meningkatkan performa kueri analitik kompleks terhadap ukuran data besar

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

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

Butuh performa lebih cepat dengan sedikit atau tanpa perubahan kueri

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

Dibandingkan dengan opsi penyetelan lain seperti manajemen penskalakan dan statistik, ini adalah perubahan produksi yang kurang berdampak untuk membuat dan mempertahankan tampilan terwujud dan potensi perolehan performanya juga lebih tinggi.

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

Perlu 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 simpul 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 yang termaterialisasi, menjadi tabel virtual pada cakram, mendukung hash dan distribusi data secara round_robin. Pengguna dapat memilih distribusi data yang berbeda dari tabel dasar tetapi optimal untuk performa kueri yang menggunakan tampilan.

Panduan desain

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

Mendesain untuk beban kerja Anda

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

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

Evaluasi rekomendasi ini dengan pertimbangkan kebutuhan beban kerja Anda. Tampilan materialisasi yang ideal adalah tampilan yang meningkatkan performa beban kerja.

Perhatikan kompromi antara kueri yang lebih cepat dan biaya

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

Beban kerja pemeliharaan menjadi lebih tinggi ketika 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 di 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 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 memiliki penggunaan rendah 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 yang telah dimaterialisasi dapat menghasilkan tampilan yang lebih besar ukurannya dibandingkan dengan jumlah tampilan-tampilan terpisah, namun biaya untuk memelihara tampilan tersebut seharusnya 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 materialized view of this form

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

Tidak semua pengoptimalan performa mengharuskan perubahan kueri

Pengoptimal kueri SQL dapat secara otomatis menggunakan tampilan materialisasi yang disebarkan untuk meningkatkan performa kueri. Dukungan ini diterapkan secara transparan pada kueri yang tidak mereferensikan tampilan, serta pada kueri yang menggunakan agregat yang tidak didukung dalam pembuatan tampilan materialisasi. Tidak diperlukan perubahan pencarian. Anda dapat memeriksa perkiraan rencana eksekusi kueri untuk mengonfirmasi apakah tampilan materialisasi digunakan.

Memantau tampilan materialisasi

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

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

Tampilan materialisasi dan cache set hasil

Kedua fitur ini dalam kumpulan SQL khusus digunakan untuk penyetelan performa kueri. Penggunaan caching kumpulan hasil bertujuan untuk mendapatkan kapasitas serentak yang tinggi dan respons cepat dari kueri berulang terhadap data statis.

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

Tampilan materialisasi memungkinkan perubahan data dalam tabel dasar. Data dalam tampilan materialisasi dapat digunakan untuk sebagian kueri. Dukungan ini memungkinkan tampilan materialisasi yang sama digunakan oleh kueri 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. Kueri 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. Ada 18 pengacakan dan 17 operasi gabungan, 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 rencana eksekusi kueri asli lagi. Sekarang jumlah join berubah dari 17 menjadi 5 dan tidak ada proses shuffle. Pilih ikon Operasi Filter dalam rencana, daftar hasil keluaran-nya memperlihatkan data dibaca dari tampilan yang dimaterialisasi alih-alih tabel dasar.

Plan_Output_List_with_Materialized_Views

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

Langkah berikutnya

Untuk tips pengembangan lainnya, lihat Gambaran umum pengembangan kumpulan SQL Khusus.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-03-26