Panduan Terbaik - Bagaimana Menulis Pertanyaan SQL yang Lebih Baik?

Pertanyaan berdasarkan kaedah pengumpulan dan program

Tersirat dalam model terbalik ialah hakikat bahawa terdapat perbezaan antara pendekatan berasaskan pengumpulan dan prosedur untuk membina pertanyaan.

• Pendekatan prosedur untuk pertanyaan adalah pendekatan yang hampir sama dengan pengaturcaraan: anda memberitahu sistem apa yang perlu dilakukan dan cara melakukannya. Sebagai contoh, seperti dalam contoh dalam artikel sebelumnya, tanya pangkalan data dengan melaksanakan satu fungsi dan kemudian memanggil fungsi lain, atau gunakan pendekatan logik yang melibatkan gelung, keadaan dan fungsi takrif pengguna (UDF) untuk mendapatkan hasil pertanyaan akhir. Anda akan mendapati bahawa dengan cara ini, anda sentiasa meminta subset lapisan data demi lapisan. Pendekatan ini juga sering dirujuk sebagai pertanyaan langkah demi langkah atau baris demi baris.
• Yang lain ialah pendekatan berasaskan koleksi, di mana anda hanya perlu menentukan operasi yang perlu dilakukan. Apa yang anda perlu lakukan dengan kaedah ini ialah nyatakan syarat dan keperluan untuk hasil yang ingin anda peroleh melalui pertanyaan. Apabila mendapatkan semula data, anda tidak perlu memberi perhatian kepada mekanisme dalaman yang melaksanakan pertanyaan: enjin pangkalan data menentukan algoritma dan logik terbaik untuk melaksanakan pertanyaan.

Memandangkan SQL berasaskan set, pendekatan ini lebih cekap daripada pendekatan prosedur, yang menerangkan sebab dalam sesetengah kes, SQL boleh berfungsi lebih pantas daripada kod.

Kaedah pertanyaan berasaskan set juga merupakan kemahiran yang industri analisis perlombongan data memerlukan anda kuasai! Kerana anda perlu mahir bertukar antara dua kaedah ini. Jika anda mendapati bahawa anda mempunyai pertanyaan prosedur dalam pertanyaan anda, anda harus mempertimbangkan sama ada bahagian ini perlu ditulis semula.

Dari pertanyaan kepada pelan pelaksanaan

Mod songsang tidak statik. Semasa anda maju ke arah menjadi pembangun SQL, mengelakkan model terbalik pertanyaan dan menulis semula pertanyaan boleh menjadi tugas yang sukar. Oleh itu, anda sering perlu menggunakan alat untuk mengoptimumkan pertanyaan anda dengan cara yang lebih berstruktur.

Memikirkan prestasi memerlukan bukan sahaja pendekatan yang lebih tersusun, tetapi juga pendekatan yang lebih mendalam.

Walau bagaimanapun, pendekatan berstruktur dan mendalam ini adalah berdasarkan pelan pertanyaan. Pelan pertanyaan terlebih dahulu dihuraikan menjadi "pokok parse" dan mentakrifkan dengan tepat algoritma yang digunakan untuk setiap operasi dan cara operasi diselaraskan.

Pengoptimuman Pertanyaan

Apabila mengoptimumkan pertanyaan, kemungkinan besar anda perlu memeriksa pelan yang dijana oleh pengoptimum secara manual. Dalam kes ini, anda perlu menganalisis pertanyaan anda sekali lagi dengan melihat pelan pertanyaan.

Untuk menguasai pelan pertanyaan sedemikian, anda perlu menggunakan beberapa alatan yang disediakan oleh sistem pengurusan pangkalan data. Berikut adalah beberapa alat yang boleh anda gunakan:

• Sesetengah pakej perisian menampilkan alatan yang boleh menjana perwakilan grafik pelan pertanyaan.
• Alat lain boleh memberi anda penerangan teks tentang rancangan pertanyaan.

Perhatikan bahawa jika anda menggunakan PostgreSQL, anda boleh membezakan antara EXPLAIN yang berbeza, anda hanya mendapat penerangan tentang cara perancang melaksanakan pertanyaan tanpa menjalankan rancangan. Pada masa yang sama, EXPLAIN ANALYZE akan melaksanakan pertanyaan dan mengembalikan kepada anda laporan analisis yang menilai pelan pertanyaan dan rancangan pertanyaan sebenar. Secara umumnya, pelan pelaksanaan sebenar sebenarnya akan melaksanakan rancangan itu, dan rancangan pelaksanaan penilaian boleh menyelesaikan masalah ini tanpa melaksanakan pertanyaan. Secara logiknya, pelan pelaksanaan sebenar adalah lebih berguna kerana ia mengandungi butiran tambahan dan statistik tentang perkara yang sebenarnya berlaku apabila pertanyaan dilaksanakan.

Seterusnya anda akan mengetahui lebih lanjut tentang XPLAIN dan ANALYZE, dan cara menggunakan kedua-dua arahan ini untuk memahami lebih lanjut rancangan pertanyaan dan prestasi pertanyaan anda. Untuk melakukan ini, anda perlu mula melakukan beberapa contoh menggunakan dua jadual: one_million dan half_million.

Anda boleh mendapatkan semula maklumat semasa jadual one_million dengan bantuan EXPLAIN: pastikan anda meletakkannya di tempat pertama semasa menjalankan pertanyaan, dan selepas larian selesai, ia akan dikembalikan ke pelan pertanyaan:

EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
Seq Scan on one_million
(cost=0.00..18584.82 rows=1025082 width=36)
(1 row)

Dalam contoh di atas, kita melihat bahawa kos pertanyaan ialah 0.00..18584.82, bilangan baris ialah 1025082, dan lebar lajur ialah 36.

Pada masa yang sama, anda juga boleh menggunakan ANALYZE untuk mengemas kini maklumat statistik.

ANALYZE one_million;
EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
Seq Scan on one_million
(cost=0.00..18334.00 rows=1000000 width=37)
(1 row)

Selain EXPLAIN dan ANALYZE, anda juga boleh mendapatkan semula masa pelaksanaan sebenar dengan bantuan EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
Seq Scan on one_million
(cost=0.00..18334.00 rows=1000000 width=37)
(actual time=0.015..1207.019 rows=1000000 loops=1)
Total runtime: 2320.146 ms
(2 rows)

Kelemahan menggunakan EXPLAIN ANALYZE ialah anda perlu benar-benar melaksanakan pertanyaan, yang patut diberi perhatian!

Semua algoritma yang kami lihat setakat ini ialah imbasan berurutan atau imbasan jadual penuh: ini ialah kaedah pengimbasan pada pangkalan data, di mana setiap baris jadual yang diimbas dibaca dalam susunan berurutan (bersiri), setiap lajur akan disemak ke lihat jika ia memenuhi kriteria. Dari segi prestasi, imbasan berurutan bukanlah pelan pelaksanaan terbaik kerana keseluruhan jadual perlu diimbas. Tetapi jika anda menggunakan cakera perlahan, bacaan berurutan juga akan menjadi cepat.

Terdapat beberapa contoh algoritma lain:

EXPLAIN ANALYZE
SELECT *
FROM one<span class="hljs-emphasis">_million JOIN half_</span>million
ON (one<span class="hljs-emphasis">_million.counter=half_</span>million.counter);
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
Hash Join (cost=15417.00..68831.00 rows=500000 width=42)
(actual time=1241.471..5912.553 rows=500000 loops=1)
Hash Cond: (one<span class="hljs-emphasis">_million.counter = half_</span>million.counter)
<span class="hljs-code">    -> Seq Scan on one_million</span>
<span class="hljs-code">    (cost=0.00..18334.00 rows=1000000 width=37)</span>
<span class="hljs-code">    (actual time=0.007..1254.027 rows=1000000 loops=1)</span>
<span class="hljs-code">    -> Hash (cost=7213.00..7213.00 rows=500000 width=5)</span>
<span class="hljs-code">    (actual time=1241.251..1241.251 rows=500000 loops=1)</span>
<span class="hljs-code">    Buckets: 4096 Batches: 16 Memory Usage: 770kB</span>
<span class="hljs-code">    -> Seq Scan on half_million</span>
<span class="hljs-code">    (cost=0.00..7213.00 rows=500000 width=5)</span>
(actual time=0.008..601.128 rows=500000 loops=1)
Total runtime: 6468.337 ms

Kami dapat melihat bahawa pengoptimum pertanyaan memilih Hash Join. Ingat operasi ini kerana kita perlu menggunakan ini untuk menilai kerumitan masa pertanyaan. Kami mendapati bahawa tiada indeks setengah_juta.kaunter dalam contoh di atas, kami boleh menambah indeks dalam contoh di bawah:

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> <span class="hljs-keyword">ON</span> half_million(counter);
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">ANALYZE</span>
<span class="hljs-keyword">SELECT</span> *
<span class="hljs-keyword">FROM</span> one_million <span class="hljs-keyword">JOIN</span> half_million
<span class="hljs-keyword">ON</span> (one_million.counter=half_million.counter);
QUERY PLAN
______________________________________________________________
<span class="hljs-keyword">Merge</span> <span class="hljs-keyword">Join</span> (<span class="hljs-keyword">cost</span>=<span class="hljs-number">4.12</span>.<span class="hljs-number">.37650</span><span class="hljs-number">.65</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">42</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.033</span>.<span class="hljs-number">.3272</span><span class="hljs-number">.940</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
<span class="hljs-keyword">Merge</span> Cond: (one_million.counter = half_million.counter)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> one_million_counter_idx <span class="hljs-keyword">on</span> one_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.32129</span><span class="hljs-number">.34</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">1000000</span> width=<span class="hljs-number">37</span>)
    (actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.011</span>.<span class="hljs-number">.694</span><span class="hljs-number">.466</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500001</span> loops=<span class="hljs-number">1</span>)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> half_million_counter_idx <span class="hljs-keyword">on</span> half_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.14120</span><span class="hljs-number">.29</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">5</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.010</span>.<span class="hljs-number">.683</span><span class="hljs-number">.674</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
Total runtime: <span class="hljs-number">3833.310</span> ms
(<span class="hljs-number">5</span> <span class="hljs-keyword">rows</span>)

Dengan mencipta indeks, pengoptimum pertanyaan telah memutuskan cara mencari gabungan Gabung apabila indeks diimbas.

Sila ambil perhatian perbezaan antara imbasan indeks dan imbasan jadual penuh (imbasan berurutan): yang terakhir (juga dipanggil "imbasan jadual") mencari hasil yang sesuai dengan mengimbas semua data atau mengindeks semua halaman, manakala yang pertama hanya mengimbas setiap baris dalam meja itu.

Bahagian kedua tutorial diperkenalkan di sini. Artikel akhir dalam siri "Cara Menulis Pertanyaan SQL yang Lebih Baik" akan menyusul, jadi nantikan.

Sila nyatakan sumber cetakan semula: Grape City Control

Atas ialah kandungan terperinci Panduan Terbaik - Bagaimana Menulis Pertanyaan SQL yang Lebih Baik?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!