🥇داستان یک تحقیق SQL

دسامبر گذشته گزارش باگ جالبی از تیم پشتیبانی VWO دریافت کردم. زمان بارگذاری یکی از گزارش های تحلیلی برای یک مشتری شرکت بزرگ بسیار زیاد به نظر می رسید. و از آنجایی که این حوزه مسئولیت من است، بلافاصله روی حل مشکل تمرکز کردم.

ماقبل تاریخ

برای اینکه مشخص شود در مورد چه چیزی صحبت می کنم، کمی در مورد VWO به شما می گویم. این پلتفرمی است که با آن می‌توانید کمپین‌های هدفمند مختلفی را در وب‌سایت‌های خود راه‌اندازی کنید: آزمایش‌های A/B، ردیابی بازدیدکنندگان و تبدیل‌ها، تجزیه و تحلیل قیف فروش، نمایش نقشه‌های حرارتی و پخش ضبط‌های بازدید.

اما مهمترین چیز در مورد پلتفرم گزارش است. همه توابع فوق به هم مرتبط هستند. و برای مشتریان شرکتی، حجم عظیمی از اطلاعات بدون یک پلتفرم قدرتمند که آن را به شکل تحلیلی ارائه می کند، به سادگی بی فایده خواهد بود.

با استفاده از پلتفرم، می توانید یک پرس و جو تصادفی در یک مجموعه داده بزرگ ایجاد کنید. در اینجا یک مثال ساده آورده شده است:

نمایش همه کلیک‌ها در صفحه "abc.com" از <تاریخ d1> تا <تاریخ d2> برای افرادی که از Chrome OR استفاده می‌کنند (واقع در اروپا و از iPhone استفاده می‌کنند)

به عملگرهای Boolean توجه کنید. آنها در رابط پرس و جو در دسترس مشتریان هستند تا پرس و جوهای پیچیده دلخواه را برای به دست آوردن نمونه انجام دهند.

درخواست کند

مشتری مورد نظر سعی داشت کاری را انجام دهد که به طور شهودی باید سریع کار کند:

نمایش تمام سوابق جلسه برای کاربرانی که از هر صفحه ای با URL حاوی "/jobs" بازدید کرده اند.

این سایت تعداد زیادی ترافیک داشت و ما بیش از یک میلیون URL منحصر به فرد را فقط برای آن ذخیره می کردیم. و آنها می خواستند یک الگوی URL نسبتاً ساده پیدا کنند که با مدل کسب و کار آنها مرتبط باشد.

تحقیقات اولیه

بیایید نگاهی به آنچه در پایگاه داده می گذرد بیاندازیم. در زیر پرس و جوی اصلی SQL کند است:

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 ;

و در اینجا زمان بندی است:

زمان برنامه ریزی شده: 1.480 ms زمان اجرا: 1431924.650 ms

پرس و جو 150 هزار ردیف خزیده است. برنامه ریز پرس و جو چند جزئیات جالب را نشان داد، اما هیچ گلوگاه آشکاری نداشت.

بیایید درخواست را بیشتر مطالعه کنیم. همانطور که می بینید، او این کار را می کند JOIN سه جدول:

جلسات: برای نمایش اطلاعات جلسه: مرورگر، عامل کاربر، کشور و غیره.
ضبط_داده: URL های ثبت شده، صفحات، مدت زمان بازدید
URL ها: برای جلوگیری از تکرار URL های بسیار بزرگ، آنها را در یک جدول جداگانه ذخیره می کنیم.

همچنین توجه داشته باشید که همه جداول ما قبلاً توسط پارتیشن بندی شده اند account_id. به این ترتیب، وضعیتی که در آن یک حساب به خصوص بزرگ برای دیگران مشکل ایجاد کند، حذف می شود.

به دنبال سرنخ

با بررسی دقیق تر، می بینیم که مشکلی در یک درخواست خاص وجود دارد. ارزش نگاهی دقیق تر به این خط را دارد:

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

اولین فکر این بود که شاید به این دلیل ILIKE در همه این URL های طولانی (ما بیش از 1,4 میلیون داریم منحصر به فرد نشانی‌های وب جمع‌آوری‌شده برای این حساب) ممکن است عملکرد ضعیفی داشته باشد.

اما نه، این موضوع نیست!

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

Time: 5231.765 ms

خود درخواست جستجوی قالب فقط 5 ثانیه طول می کشد. جستجوی یک الگو در میلیون ها URL منحصر به فرد به وضوح مشکلی نیست.

مظنون بعدی در لیست چند نفر هستند JOIN. شاید استفاده بیش از حد آنها باعث کاهش سرعت شده است؟ معمولا JOIN'ها واضح ترین نامزدها برای مشکلات عملکرد هستند، اما من باور نداشتم که مورد ما معمولی باشد.

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

و این هم مورد ما نبود. JOINمعلوم شد که بسیار سریع است.

محدود کردن دایره مظنونین

من آماده بودم تا برای دستیابی به هر گونه بهبود عملکرد ممکن، تغییر پرس و جو را شروع کنم. من و تیمم 2 ایده اصلی را توسعه دادیم:

از EXISTS برای URL فرعی استفاده کنید: می‌خواستیم دوباره بررسی کنیم که آیا در جستجوی فرعی URLها مشکلی وجود دارد یا خیر. یکی از راه های رسیدن به این هدف استفاده ساده است EXISTS. EXISTS می تواند عملکرد را بسیار بهبود می بخشد زیرا به محض یافتن تنها رشته ای که با شرایط مطابقت دارد بلافاصله پایان می یابد.

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

خب بله. درخواست فرعی وقتی در آن پیچیده شود EXISTS، همه چیز را فوق العاده سریع می کند. سوال منطقی بعدی این است که چرا درخواست با JOIN-ami و subquery به صورت جداگانه سریع هستند، اما آیا با هم خیلی کند هستند؟

انتقال درخواست فرعی به CTE : اگر پرس و جو به خودی خود سریع است، می توانیم به سادگی ابتدا نتیجه سریع را محاسبه کرده و سپس آن را به درخواست اصلی ارائه دهیم.

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;

اما باز هم خیلی کند بود.

پیدا کردن مقصر

در تمام این مدت یک چیز کوچک از جلوی چشمانم می گذشت که مدام آن را کنار می زدم. اما چون چیز دیگری نمانده بود، تصمیم گرفتم به او هم نگاه کنم. من در مورد صحبت می کنم && اپراتور. خدا حافظ EXISTS فقط عملکرد بهبود یافته است && تنها عامل مشترک باقی مانده در تمام نسخه های پرس و جو کند بود.

نگاه كردن مستندات، ما آن را می بینیم && زمانی استفاده می شود که شما نیاز به یافتن عناصر مشترک بین دو آرایه دارید.

در درخواست اصلی این است:

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

این بدان معناست که ما یک جستجوی الگو در URL های خود انجام می دهیم، سپس محل تلاقی همه URL ها را با پست های رایج پیدا می کنیم. این کمی گیج کننده است زیرا "urls" در اینجا به جدول حاوی همه URL ها اشاره نمی کند، بلکه به ستون "urls" در جدول اشاره می کند. recording_data.

با شک و تردیدهای فزاینده در مورد &&، سعی کردم در طرح پرس و جو ایجاد شده تأییدی برای آنها پیدا کنم EXPLAIN ANALYZE (من قبلاً یک برنامه ذخیره کرده بودم، اما معمولاً در آزمایش SQL راحت تر از تلاش برای درک شفافیت برنامه ریزان پرس و جو هستم).

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

چندین خط فیلتر فقط از وجود داشت &&. به این معنی که این عمل نه تنها گران بود، بلکه چندین بار انجام شد.

من این را با جداسازی شرایط آزمایش کردم

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[]

این پرس و جو کند بود. از آنجا که JOIN-ها سریع هستند و سوالات فرعی سریع هستند، تنها چیزی که باقی ماند این بود && اپراتور.

این فقط یک عملیات کلیدی است. ما همیشه باید کل جدول زیرین URL ها را برای جستجوی یک الگو جستجو کنیم و همیشه باید تقاطع ها را پیدا کنیم. ما نمی‌توانیم مستقیماً بر اساس سوابق URL جستجو کنیم، زیرا اینها فقط شناسه‌هایی هستند که به آنها اشاره می‌کنند urls.

در راه حل

&& کند زیرا هر دو مجموعه بزرگ هستند. اگر تعویض کنم عملیات نسبتا سریع خواهد بود urls بر { "http://google.com/", "http://wingify.com/" }.

من شروع کردم به دنبال راهی برای انجام تقاطع تنظیم در Postgres بدون استفاده &&، اما بدون موفقیت زیاد.

در پایان، ما تصمیم گرفتیم که مشکل را به تنهایی حل کنیم: همه چیز را به من بدهید urls خطوطی که URL با الگوی آنها مطابقت دارد. بدون شرایط اضافی خواهد بود -

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%'

به جای JOIN نحو من فقط از یک سوال فرعی استفاده کردم و گسترش دادم recording_data.urls آرایه ای به طوری که بتوانید مستقیماً شرط را در آن اعمال کنید WHERE.

مهمترین چیز در اینجا این است && برای بررسی اینکه آیا یک ورودی داده شده حاوی URL منطبق است یا خیر استفاده می شود. اگر کمی چشمک بزنید، می‌توانید ببینید که این عملیات در میان عناصر یک آرایه (یا ردیف‌های جدول) حرکت می‌کند و با برقراری یک شرط (تطابق) متوقف می‌شود. شما را به یاد چیزی نمی اندازد؟ آره EXISTS.

زیرا در recording_data.urls می توان از خارج از زمینه فرعی ارجاع داد، وقتی این اتفاق می افتد می توانیم به دوست قدیمی خود بازگردیم EXISTS و فرعی را با آن بپیچید.

با کنار هم گذاشتن همه چیز، پرس و جوی بهینه سازی شده نهایی را دریافت می کنیم:

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%'
    );

و زمان پایان نهایی Time: 1898.717 ms وقت جشن گرفتن؟!؟

نه خیلی سریع! ابتدا باید صحت را بررسی کنید. من به شدت مشکوک بودم EXISTS بهینه سازی زیرا منطق را تغییر می دهد تا زودتر خاتمه یابد. باید مطمئن باشیم که خطای غیر واضحی به درخواست اضافه نکرده‌ایم.

یک آزمایش ساده برای اجرا بود count(*) در پرس و جوهای آهسته و سریع برای تعداد زیادی مجموعه داده مختلف. سپس، برای زیرمجموعه کوچکی از داده ها، من به صورت دستی تأیید کردم که همه نتایج صحیح هستند.

همه آزمایشات به طور مداوم نتایج مثبت دادند. همه چیز را درست کردیم!

درس های آموخته شده

از این داستان می توان درس های زیادی گرفت:

طرح های پرس و جو کل داستان را بیان نمی کنند، اما می توانند سرنخ هایی را ارائه دهند
مظنونان اصلی همیشه مقصر واقعی نیستند
پرس و جوهای کند را می توان برای جداسازی تنگناها تجزیه کرد
همه بهینه‌سازی‌ها ماهیت تقلیلی ندارند
استفاده EXISTدر صورت امکان، می تواند منجر به افزایش چشمگیر بهره وری شود

نتیجه

ما از زمان پرس و جو 24 دقیقه به 2 ثانیه رسیدیم - عملکرد بسیار قابل توجهی افزایش یافت! اگرچه این مقاله بزرگ بود، اما تمام آزمایش‌هایی که ما انجام دادیم در یک روز انجام شد و تخمین زده شد که بین 1,5 تا 2 ساعت برای بهینه‌سازی و آزمایش طول می‌کشد.

SQL یک زبان فوق العاده است اگر از آن نترسید، اما سعی کنید آن را یاد بگیرید و استفاده کنید. با داشتن درک خوب از نحوه اجرای پرس‌و‌جوهای SQL، نحوه ایجاد طرح‌های پرس و جو توسط پایگاه داده، نحوه کار ایندکس‌ها، و به سادگی اندازه داده‌هایی که با آنها سروکار دارید، می‌توانید در بهینه‌سازی پرس‌و‌جوها بسیار موفق باشید. با این حال، به همان اندازه مهم است که روش‌های مختلف را امتحان کنید و به آرامی مشکل را از بین ببرید و گلوگاه‌ها را پیدا کنید.

بهترین بخش در مورد دستیابی به نتایجی مانند این، بهبود قابل توجه و قابل مشاهده سرعت است - جایی که گزارشی که قبلاً حتی بارگیری نمی شد اکنون تقریباً بلافاصله بارگیری می شود.

تشکر ویژه از رفقای من به فرمان آدیتیا میشرا, آدیتیا گورو и وارون مالهوترا برای طوفان فکری و دینکار پاندیر برای پیدا کردن یک خطای مهم در درخواست نهایی ما قبل از اینکه در نهایت با آن خداحافظی کنیم!

منبع: www.habr.com

داستان یک تحقیق SQL