🥇قصة تحقيق SQL واحد

تلقيت في ديسمبر الماضي تقريرًا مثيرًا للاهتمام عن خطأ من فريق دعم VWO. بدا وقت تحميل أحد تقارير التحليلات لعميل شركة كبيرة أمرًا باهظًا. وبما أن هذا هو نطاق مسؤوليتي، فقد ركزت على الفور على حل المشكلة.

قبل التاريخ

لتوضيح ما أتحدث عنه، سأخبرك قليلاً عن VWO. هذه منصة يمكنك من خلالها إطلاق العديد من الحملات المستهدفة على مواقع الويب الخاصة بك: إجراء تجارب A/B، وتتبع الزائرين والتحويلات، وتحليل مسار المبيعات، وعرض الخرائط الحرارية، وتشغيل تسجيلات الزيارة.

لكن أهم شيء في المنصة هو إعداد التقارير. جميع الوظائف المذكورة أعلاه مترابطة. وبالنسبة للعملاء من الشركات، فإن كمية هائلة من المعلومات ستكون عديمة الفائدة بدون منصة قوية تقدمها في شكل تحليلات.

باستخدام النظام الأساسي، يمكنك إجراء استعلام عشوائي على مجموعة كبيرة من البيانات. إليك مثال بسيط:

عرض جميع النقرات على صفحة "abc.com" من <date d1> إلى <date d2> للأشخاص الذين استخدموا Chrome OR (الموجودين في أوروبا ويستخدمون iPhone)

انتبه إلى العوامل المنطقية. وهي متاحة للعملاء في واجهة الاستعلام لإجراء استعلامات معقدة بشكل تعسفي للحصول على العينات.

طلب بطيء

كان العميل المعني يحاول القيام بشيء يجب أن يعمل بشكل حدسي بسرعة:

إظهار جميع سجلات الجلسة للمستخدمين الذين زاروا أي صفحة بعنوان 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 مللي ثانية وقت التنفيذ: 1431924.650 مللي ثانية

زحف الاستعلام إلى 150 ألف صف. أظهر مخطط الاستعلام بعض التفاصيل المثيرة للاهتمام، ولكن لم يكن هناك أي اختناقات واضحة.

دعونا ندرس الطلب أكثر. كما ترون، فهو يفعل JOIN ثلاث جداول:

دورات: لعرض معلومات الجلسة: المتصفح، وكيل المستخدم، البلد، وما إلى ذلك.
Recording_data: عناوين 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 مليون فريد من نوعه عناوين URL التي تم جمعها لهذا الحساب) قد يتأثر الأداء.

لكن لا، ليس هذا هو الهدف!

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تبين أنه سريع جدًا.

تضييق دائرة المشتبه بهم

لقد كنت على استعداد لبدء تغيير الاستعلام لتحقيق أي تحسينات محتملة في الأداء. لقد طورت أنا وفريقي فكرتين رئيسيتين:

استخدم 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 والاستعلام الفرعي نفسه سريعان بشكل فردي، لكنهما بطيئان للغاية معًا؟

نقل الاستعلام الفرعي إلى 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 ذات المنشورات الشائعة. وهذا أمر مربك بعض الشيء لأن "عناوين url" هنا لا تشير إلى الجدول الذي يحتوي على جميع عناوين URL، ولكن إلى عمود "عناوين url" في الجدول 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-s سريعة والاستعلامات الفرعية سريعة، والشيء الوحيد المتبقي هو && المشغل أو العامل.

هذه مجرد عملية رئيسية. نحتاج دائمًا إلى البحث في الجدول الأساسي لعناوين 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 Array بحيث يمكنك تطبيق الشرط مباشرة فيه 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 ساعة للتحسينات والاختبار.

SQL هي لغة رائعة إذا كنت لا تخاف منها، ولكن حاول تعلمها واستخدامها. من خلال الفهم الجيد لكيفية تنفيذ استعلامات SQL، وكيفية إنشاء قاعدة البيانات لخطط الاستعلام، وكيفية عمل الفهارس، وببساطة حجم البيانات التي تتعامل معها، يمكنك أن تكون ناجحًا جدًا في تحسين الاستعلامات. ومع ذلك، من المهم بنفس القدر الاستمرار في تجربة أساليب مختلفة وحل المشكلة ببطء، وإيجاد الاختناقات.

أفضل جزء في تحقيق مثل هذه النتائج هو التحسن الملحوظ والمرئي في السرعة - حيث يتم الآن تحميل التقرير الذي لم يكن من الممكن تحميله في السابق على الفور تقريبًا.

شكر خاص ل رفاقي بأمر من أديتيا ميشرا, أديتيا جورو и فارون مالهوترا للعصف الذهني و دينكار باندير لإيجاد خطأ مهم في طلبنا الأخير قبل أن نودعه أخيرًا!

المصدر: www.habr.com

قصة تحقيق SQL واحد