🥇Kisah satu penyiasatan SQL

Disember lalu saya menerima laporan pepijat yang menarik daripada pasukan sokongan VWO. Masa pemuatan untuk salah satu laporan analitik untuk pelanggan korporat yang besar kelihatan terlalu tinggi. Dan kerana ini adalah bidang tanggungjawab saya, saya segera menumpukan pada menyelesaikan masalah itu.

prasejarah

Untuk menjelaskan apa yang saya maksudkan, saya akan memberitahu anda sedikit tentang VWO. Ini ialah platform yang anda gunakan untuk melancarkan pelbagai kempen disasarkan di tapak web anda: menjalankan percubaan A/B, menjejaki pelawat dan penukaran, menganalisis corong jualan, memaparkan peta haba dan memainkan rakaman lawatan.

Tetapi perkara yang paling penting tentang platform adalah melaporkan. Semua fungsi di atas adalah saling berkaitan. Dan untuk pelanggan korporat, sejumlah besar maklumat akan menjadi sia-sia tanpa platform berkuasa yang membentangkannya dalam bentuk analitik.

Menggunakan platform, anda boleh membuat pertanyaan rawak pada set data yang besar. Berikut ialah contoh mudah:

Tunjukkan semua klik pada halaman "abc.com" DARI <tarikh d1> HINGGA <tarikh d2> untuk orang yang menggunakan Chrome ATAU (terletak di Eropah DAN menggunakan iPhone)

Beri perhatian kepada pengendali Boolean. Mereka tersedia untuk pelanggan dalam antara muka pertanyaan untuk membuat pertanyaan kompleks sewenang-wenangnya untuk mendapatkan sampel.

Permintaan perlahan

Pelanggan yang dipersoalkan cuba melakukan sesuatu yang secara intuitif harus berfungsi dengan cepat:

Tunjukkan semua rekod sesi untuk pengguna yang melawat mana-mana halaman dengan URL yang mengandungi "/jobs"

Tapak ini mempunyai banyak trafik dan kami menyimpan lebih sejuta URL unik hanya untuknya. Dan mereka ingin mencari templat URL yang agak mudah yang berkaitan dengan model perniagaan mereka.

Siasatan awal

Mari kita lihat apa yang berlaku dalam pangkalan data. Di bawah ialah pertanyaan SQL asal yang perlahan:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

Dan berikut adalah masanya:

Masa yang dirancang: 1.480 ms Masa pelaksanaan: 1431924.650 ms

Pertanyaan itu merangkak 150 ribu baris. Perancang pertanyaan menunjukkan beberapa butiran menarik, tetapi tiada kesesakan yang jelas.

Mari kita kaji permintaan itu dengan lebih lanjut. Seperti yang anda lihat, dia lakukan JOIN tiga meja:

sesi: untuk memaparkan maklumat sesi: penyemak imbas, ejen pengguna, negara, dan sebagainya.
data_rakaman: URL yang direkodkan, halaman, tempoh lawatan
url: Untuk mengelakkan penduaan URL yang sangat besar, kami menyimpannya dalam jadual berasingan.

Juga ambil perhatian bahawa semua jadual kami telah pun dipisahkan oleh account_id. Dengan cara ini, situasi di mana satu akaun yang sangat besar menyebabkan masalah kepada orang lain dikecualikan.

Mencari petunjuk

Setelah diperiksa lebih dekat, kami melihat bahawa ada sesuatu yang tidak kena dengan permintaan tertentu. Perlu melihat lebih dekat pada baris ini:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

Pemikiran pertama adalah mungkin kerana ILIKE pada semua URL yang panjang ini (kami mempunyai lebih 1,4 juta unik URL yang dikumpul untuk akaun ini) prestasi mungkin terjejas.

Tetapi tidak, itu bukan maksudnya!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

Permintaan carian templat itu sendiri mengambil masa 5 saat sahaja. Mencari corak dalam sejuta URL unik jelas tidak menjadi masalah.

Suspek seterusnya dalam senarai adalah beberapa orang JOIN. Mungkin penggunaan berlebihan mereka telah menyebabkan kelembapan? Biasanya JOIN's adalah calon yang paling jelas untuk masalah prestasi, tetapi saya tidak percaya kes kami adalah tipikal.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

Dan ini juga bukan kes kami. JOIN's ternyata agak pantas.

Mengecilkan bulatan suspek

Saya sudah bersedia untuk mula menukar pertanyaan untuk mencapai sebarang peningkatan prestasi yang mungkin. Pasukan saya dan saya membangunkan 2 idea utama:

Gunakan EXISTS untuk URL subquery: Kami ingin menyemak semula jika terdapat sebarang masalah dengan subkueri untuk URL. Satu cara untuk mencapai ini adalah dengan hanya menggunakan EXISTS. EXISTS boleh sangat meningkatkan prestasi kerana ia tamat serta-merta sebaik sahaja ia menemui satu-satunya rentetan yang sepadan dengan keadaan.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

Nah, ya. Subquery apabila dibungkus EXISTS, menjadikan segala-galanya sangat pantas. Soalan logik seterusnya ialah mengapa permintaan dengan JOIN-ami dan subquery itu sendiri adalah pantas secara individu, tetapi sangat perlahan bersama-sama?

Memindahkan subkueri ke CTE : Jika pertanyaan itu pantas dengan sendirinya, kita hanya boleh mengira hasil pantas dahulu dan kemudian memberikannya kepada pertanyaan utama

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Tetapi ia masih sangat perlahan.

Mencari pelakunya

Selama ini, satu perkara kecil terlintas di depan mata saya, yang sentiasa saya tolak. Tetapi kerana tiada apa-apa lagi, saya memutuskan untuk melihatnya juga. Saya bercakap tentang && pengendali. Selamat tinggal EXISTS hanya meningkatkan prestasi && merupakan satu-satunya faktor biasa yang tinggal di semua versi pertanyaan perlahan.

Melihat kepada dokumentasi, kita lihat itu && digunakan apabila anda perlu mencari elemen biasa antara dua tatasusunan.

Dalam permintaan asal ini ialah:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Ini bermakna kami melakukan carian corak pada URL kami, kemudian mencari persimpangan dengan semua URL dengan siaran biasa. Ini agak mengelirukan kerana "url" di sini tidak merujuk kepada jadual yang mengandungi semua URL, tetapi kepada lajur "url" dalam jadual recording_data.

Dengan syak wasangka yang semakin meningkat mengenai &&, saya cuba mencari pengesahan untuk mereka dalam pelan pertanyaan yang dihasilkan EXPLAIN ANALYZE (Saya sudah mempunyai rancangan yang disimpan, tetapi saya biasanya lebih selesa bereksperimen dalam SQL daripada cuba memahami kelegapan perancang pertanyaan).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

Terdapat beberapa baris penapis sahaja dari &&. Ini bermakna bahawa operasi ini bukan sahaja mahal, tetapi juga dilakukan beberapa kali.

Saya menguji ini dengan mengasingkan keadaan

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

Pertanyaan ini lambat. Kerana ia JOIN-s adalah pantas dan subqueries adalah pantas, satu-satunya perkara yang tinggal ialah && pengendali.

Ini hanyalah operasi utama. Kami sentiasa perlu mencari keseluruhan jadual asas URL untuk mencari corak dan kami sentiasa perlu mencari persimpangan. Kami tidak boleh mencari mengikut rekod URL secara langsung, kerana ini hanyalah ID yang dirujuk urls.

Dalam perjalanan ke penyelesaian

&& lambat kerana kedua-dua set adalah besar. Operasi akan menjadi agak cepat jika saya menggantikan urls pada { "http://google.com/", "http://wingify.com/" }.

Saya mula mencari cara untuk melakukan set persimpangan dalam Postgres tanpa menggunakan &&, tetapi tidak banyak kejayaan.

Pada akhirnya, kami memutuskan untuk menyelesaikan masalah secara berasingan: berikan saya segala-galanya urls baris yang URLnya sepadan dengan corak. Tanpa syarat tambahan ia akan menjadi -

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

Sebaliknya JOIN sintaks Saya baru sahaja menggunakan subquery dan berkembang recording_data.urls array supaya anda boleh terus menggunakan syarat dalam WHERE.

Perkara yang paling penting di sini ialah && digunakan untuk menyemak sama ada entri yang diberikan mengandungi URL yang sepadan. Jika anda menjeling sedikit, anda boleh melihat operasi ini bergerak melalui elemen tatasusunan (atau baris jadual) dan berhenti apabila syarat (padanan) dipenuhi. Tidak mengingatkan anda tentang apa-apa? Yeah, EXISTS.

Sejak pada recording_data.urls boleh dirujuk dari luar konteks subquery, apabila ini berlaku kita boleh jatuh kembali kepada rakan lama kita EXISTS dan bungkus subquery dengannya.

Menggabungkan segala-galanya, kami mendapat pertanyaan akhir yang dioptimumkan:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

Dan masa utama terakhir Time: 1898.717 ms Masa untuk meraikan?!?

Tidak begitu pantas! Mula-mula anda perlu menyemak ketepatannya. Saya sangat curiga tentang EXISTS pengoptimuman kerana ia mengubah logik untuk diselesaikan lebih awal. Kami perlu memastikan bahawa kami tidak menambahkan ralat yang tidak jelas pada permintaan itu.

Satu ujian mudah adalah untuk dijalankan count(*) pada kedua-dua pertanyaan perlahan dan pantas untuk sejumlah besar set data yang berbeza. Kemudian, untuk subset kecil data, saya mengesahkan secara manual bahawa semua keputusan adalah betul.

Semua ujian memberikan keputusan positif secara konsisten. Kami membetulkan segala-galanya!

Pengajaran

Banyak pengajaran yang boleh diambil dari kisah ini:

Rancangan pertanyaan tidak menceritakan keseluruhan cerita, tetapi ia boleh memberikan petunjuk
Suspek utama tidak selalu menjadi punca sebenar
Pertanyaan perlahan boleh dipecahkan untuk mengasingkan kesesakan
Tidak semua pengoptimuman bersifat reduktif
Gunakan EXIST, di mana boleh, boleh membawa kepada peningkatan dramatik dalam produktiviti

Output

Kami beralih daripada masa pertanyaan ~24 minit kepada 2 saat - peningkatan prestasi yang agak ketara! Walaupun artikel ini keluar besar, semua percubaan yang kami lakukan berlaku dalam satu hari, dan dianggarkan ia mengambil masa antara 1,5 dan 2 jam untuk pengoptimuman dan ujian.

SQL ialah bahasa yang menarik jika anda tidak takut dengannya, tetapi cuba pelajari dan gunakannya. Dengan mempunyai pemahaman yang baik tentang cara pertanyaan SQL dilaksanakan, cara pangkalan data menjana rancangan pertanyaan, cara indeks berfungsi dan hanya saiz data yang anda berurusan, anda boleh menjadi sangat berjaya dalam mengoptimumkan pertanyaan. Ia adalah sama penting, bagaimanapun, untuk terus mencuba pendekatan yang berbeza dan perlahan-lahan memecahkan masalah, mencari kesesakan.

Bahagian terbaik tentang mencapai hasil seperti ini ialah peningkatan kelajuan yang ketara dan ketara - di mana laporan yang sebelum ini tidak dimuatkan kini dimuatkan hampir serta-merta.

Terima kasih khas kepada rakan seperjuangan saya atas arahan Aditya Mishra, Aditya Gauru и Varun Malhotra untuk sumbangsaran dan Dinkar Pandir kerana mencari ralat penting dalam permintaan terakhir kami sebelum kami akhirnya mengucapkan selamat tinggal kepadanya!

Sumber: www.habr.com

Kisah satu penyiasatan SQL