🎫 🙌🏾 🧕🏾 تاريخ تحقيق SQL واحد ✌🏽 🔤 🚵🏽

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

قبل التاريخ

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

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

باستخدام النظام الأساسي ، يمكنك تقديم طلب تعسفي على مجموعة كبيرة من البيانات. هنا مثال بسيط:

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

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

طلب بطيء

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

  عرض كل ملاحظات الجلسة
 للمستخدمين الذين يزورون أي صفحة
 مع رابط حيث يوجد "/ وظائف"

كان هناك الكثير من الزيارات على هذا الموقع ، وقمنا بتخزين أكثر من مليون عنوان 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 :

الجلسات : لعرض معلومات الجلسة: المستعرض ، وكيل المستخدم ، البلد ، وما إلى ذلك.
registration_data : عناوين url المسجلة والصفحات ومدة الزيارات
عناوين url : لتجنب تكرار عناوين url الكبيرة جدًا ، نقوم بتخزينها في جدول منفصل.

لاحظ أيضًا أن جميع account_id مقسومة بالفعل على 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 تحولت 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 ، يجعل كل شيء بسرعة فائقة. والسؤال المنطقي التالي هو لماذا يكون الاستعلام مع JOINs والاستعلام الفرعي نفسه سريعًا بشكل فردي ، لكن بطيئًا بشكل رهيب؟

ننقل الاستعلام الفرعي إلى 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 سريعة 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 ، قمت باستخدام استعلام فرعي وقمت بتوسيع صفيف registration_data.urls بحيث يمكن تطبيق الشرط مباشرةً على WHERE .

الشيء الأكثر أهمية هنا هو أنه && استخدام && للتحقق مما إذا كان الإدخال المحدد يحتوي على عنوان URL مناسب. عند التحديق قليلاً ، يمكنك أن ترى في هذه العملية تتحرك من خلال عناصر المصفوفة (أو صفوف الجدول) وتتوقف عند استيفاء الشرط (المطابقة). لا يشبه أي شيء؟ نعم ، EXISTS .

بما أنه يمكن الرجوع إلى registration_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 التشغيل النهائي Time: 1898.717 ms حان وقت الاحتفال؟!؟

ليس بهذه السرعة! تحتاج أولا إلى التحقق من صحة. كنت متشككا للغاية من تحسين EXISTS ، لأنه يغير المنطق إلى نهاية سابقة. يجب أن نتأكد من أننا لم نقم بإضافة خطأ غير واضح للطلب.

كان الاختيار البسيط هو إجراء count(*) لكل من الاستعلامات البطيئة والسريعة لعدد كبير من مجموعات البيانات المختلفة. بعد ذلك ، بالنسبة لمجموعة فرعية صغيرة من البيانات ، راجعت صحة جميع النتائج يدويًا.

أعطت جميع الشيكات نتائج إيجابية باستمرار. نحن إصلاحه!

الدروس المستفادة

هناك العديد من الدروس التي يجب تعلمها من هذه القصة:

خطط الاستعلام لا تروي القصة بأكملها ، ولكنها قد تعطي أدلة
المشتبه بهم الرئيسيون ليسوا دائماً الجناة الحقيقيين
يمكن تقسيم الاستعلامات البطيئة لعزل الاختناقات
ليست كل التحسينات مختصرة بطبيعتها
يمكن أن يؤدي استخدام EXIST ، حيثما أمكن ، إلى زيادة حادة في الإنتاجية.

استنتاج

لقد ذهبنا من وقت الطلب من ~ 24 دقيقة إلى 2 ثانية - زيادة خطيرة للغاية في الأداء! على الرغم من أن هذه المقالة كانت كبيرة ، إلا أن جميع التجارب التي أجريناها حدثت في نفس اليوم ، ووفقًا للتقديرات ، استغرق الأمر من 1.5 إلى ساعتين للتحسينات والاختبارات.

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

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

شكر خاص لزملائي في الفريق Aditya Misra و Aditya Gauru و Varun Malhotra على العصف الذهني و Dinkar Pandir لإيجاد خطأ مهم في طلبنا النهائي قبل أن نودعه أخيرًا!

تاريخ تحقيق SQL واحد