Penyelidikan tentang mengoptimumkan kecekapan SQL

Ini adalah kes yang dikongsi oleh Encik Chen Hongyi (K Lama) di Persidangan MOORACLE Shanghai pada Ogos 2016. Dengan menulis semula gabungan SQL ke plsql, kecekapan pelaksanaan telah dipertingkatkan dengan ketara. Apabila Tiger Liu melihat kes ini, dia pada mulanya tidak menyedari jumlah rekod sebenar dalam setiap jadual yang dipaparkan dalam rancangan pelaksanaan Dia tidak menyangka bahawa cara menulis semula plsql adalah lebih cekap daripada cara menulis fungsi analitik beberapa perbincangan e-mel dengan Cikgu Chen Selepas itu saya melihat dengan lebih dekat rancangan pelaksanaan.

SQL asal adalah seperti berikut:

bergabung menjadi t_customer c menggunakan

(

pilih a.cstno, a.amaun daripada t_trade a,

(pilih cstno,max(trade_date) trade_date daripada t_trade

kumpulan oleh cstno) b

di mana a.cstno = b.cstno dan a.trade_date=b.trade_date

) m

on(c.cstno = m.cstno)

apabila dipadankan kemudian

set kemas kini c.jumlah = m.jumlah;

SQL ini mengemas kini jumlah penggunaan terkini dalam jadual butiran transaksi pengguna (t_trade) kepada medan jumlah penggunaan dalam jadual maklumat pengguna (t_customer), menggunakan operasi gabungan.

Pelan pelaksanaan:

Tiger Liu Nota:

Sebelum anda menguasai kaedah penulisan fungsi analisis, bahagian merah SQL ialah cara biasa untuk menulis maklumat medan lain selepas kumpulan mengikut, yang juga merupakan punca kecekapan pelaksanaan yang lemah bagi SQL ini.

Terdapat satu lagi bahaya tersembunyi dalam SQL asal, iaitu, jika tarikh_dagang maksimum yang sepadan dengan cstno tertentu t_trade diulang, maka SQL ini akan melaporkan ralat ORA-30926 dan tidak boleh dilaksanakan.

Jika anda tidak melihat pelan pelaksanaan dengan teliti (maklumat volum data sebenar bagi kedua-dua jadual), kaedah pengoptimuman biasa untuk SQL jenis ini ialah menggunakan fungsi analitik untuk menulis semula:

Kaedah menulis semula 1:

bergabung menjadi t_customer c menggunakan

(

pilih a.cstno,a.amaun daripada

(pilih tarikh_dagang, cstno, amaun,

nombor_baris()over(partition by cstno order by trade_date desc) RNO from t_trade)a

di mana RNO=1

) m

on(c.cstno = m.cstno)

apabila dipadankan kemudian

set kemas kini c.jumlah = m.jumlah;

Kaedah penulisan semula ini akan menjadi lebih cekap daripada SQL asal, dan tidak akan ada masalah ralat berulang untuk tarikh_perdagangan maks sepadan dengan cstno tertentu.

Tetapi Cikgu Chen tidak menggunakan kaedah penulisan semula fungsi analitik Sebaliknya, berdasarkan perbezaan besar dalam volum data antara kedua-dua jadual, dia menulis semula SQL ke plsql yang lebih cekap:

. Kaedah menulis semula 2:

deklarasikan

nombor vamount;

mulakan

untuk v in (pilih * daripada t_customer )

gelung

pilih jumlah ke dalam vamount daripada

(pilih jumlah daripada t_trade di mana cstno=v.cstno pesanan mengikut trade_date desc)

di mana rownum

kemas kini jumlah set t_customer = vamount where cstno=v.cstno;

gelung hujung

komitmen;

akhir;

/

Mengikut pelan pelaksanaan SQL asal, kita tahu bahawa bilangan rekod dalam jadual t_customer adalah agak kecil, hanya lebih daripada 1,000, manakala jadual t_trade mempunyai 10 juta rekod, dengan nisbah 1:10,000 (saya tidak tahu jika ini adalah data sebenar atau data ujian, terdapat hanya lebih daripada 1,000 pengguna, dan pengguna mempunyai purata 10,000 butiran penggunaan, yang tidak kelihatan seperti data sebenar).

Dalam kes istimewa di mana data antara dua jadual agak berbeza, kaedah penulisan plsql sememangnya lebih cekap daripada kaedah penulisan fungsi analitik. Penulisan semula ini sangat bijak.

Mari kita analisa kelebihan dan kekurangan kedua-dua penulisan semula ini:

1. Kaedah penulisan semula plsql adalah sesuai apabila jadual t_customer agak kecil dan nisbah bilangan rekod dalam jadual t_customer dan t_trade adalah agak besar Kecekapan pelaksanaan akan lebih tinggi daripada penulisan semula fungsi analisis. Dalam contoh ini, jika bilangan rekod dalam jadual t_customer ialah 100,000, maka cara menulis fungsi analisis adalah berpuluh-puluh hingga ratusan kali lebih cepat daripada cara menulis plsql.

3 Prasyarat untuk penulisan semula plsql ini ialah mesti ada indeks bersama medan cstno + trade_date pada jadual t_trade. Penulisan semula fungsi analitik tidak memerlukan sebarang sokongan indeks.

4. Untuk jadual dengan berpuluh-puluh juta rekod seperti t_trade, kaedah penulisan menggunakan fungsi analisis boleh mempercepatkan dengan menghidupkan selari jika anda ingin meningkatkan kecekapan semasa menulis semula plsql, anda perlu mengumpulkan jadual t_customer dengan cstno terlebih dahulu; gunakan berbilang sesi untuk melaksanakannya secara serentak.

Mari kita lihat jika plsql Cikgu Chen boleh dilaksanakan dengan satu sql yang saya cuba Kod SQL adalah seperti berikut:

bergabung menjadi t_customer c menggunakan

(

pilih tc.cstno,

(pilih jumlah

dari t_trade td1

di mana td1.cstno=tc.cstno dan td1.trade_date = (pilih max(trade_date) daripada t_trade td2 di mana tc.cstno = td2.cstno) dan rownum=1 ) sebagai amaun

daripada t_customer tc

) m

on(c.cstno = m.cstno)

apabila dipadankan kemudian

set kemas kini c.jumlah = m.jumlah;

Pelan pelaksanaan adalah lebih kurang seperti berikut:

Kaedah penulisan ini juga memerlukan indeks bersama cstno+trade_date (IDX_T_TRADE) wujud dalam jadual t_trade, dan volum data jadual T_customer jauh lebih rendah daripada T_trade.

Mengikut pelan pelaksanaan, kecekapan pelaksanaan sql ini harus setanding dengan kecekapan menulis plsql.

Ringkasan:

SQL pengoptimuman, sebagai tambahan untuk mengelakkan penulisan SQL yang tidak cekap, terutamanya bergantung pada jumlah data dan pengedaran data jadual. Kaedah penulisan semula plsql akan menunjukkan kecekapan yang lebih tinggi dalam beberapa kes khas kecekapan mungkin tidak sebaik SQL asal. Walau bagaimanapun, idea pengoptimuman patut dipelajari.

Cara fungsi analisis ditulis semula, tidak kira bagaimana data diedarkan, akan menjadi lebih cekap dan lebih serba boleh daripada SQL asal.

Sepatutnya masih terdapat banyak pembangun dan DBA yang menggunakan SQL sebelum contoh ini ditulis semula Selepas memahami cara menggunakan fungsi analisis, cara penulisan SQL asal yang tidak cekap harus ditinggalkan sepenuhnya.

Plsql yang terakhir ditulis semula ke dalam satu SQL Logiknya kelihatan rumit dan sukar untuk difahami.

Ayat yang sama kekal, pengoptimuman tidak mempunyai formula yang pasti, pengoptimum sudah mati, dan otak manusia masih hidup Hanya dengan menguasai prinsip kecekapan pelaksanaan SQL menjadi lebih tinggi dan lebih tinggi.

Atas ialah kandungan terperinci Penyelidikan tentang mengoptimumkan kecekapan SQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!