PostgreSQL Antipatterns: قصة التنقيح المتكرر للبحث بالاسم ، أو "التحسين ذهابًا وإيابًا"

الآلاف من المديرين من مكاتب المبيعات في جميع أنحاء البلاد يعملون في نظام CRM الخاص بنا عشرات الآلاف من جهات الاتصال يوميًا - حقائق عن التواصل مع العملاء المحتملين أو الذين يعملون معنا بالفعل. وبالنسبة لهذا العميل ، يجب أن تجده أولاً ، ويفضل أن يكون ذلك سريعًا جدًا. وهذا يحدث غالبًا بالاسم.

لذلك ، ليس من المستغرب أن نحلل مرة أخرى الاستعلامات "الثقيلة" في إحدى قواعد البيانات الأكثر تحميلًا - الخاصة بنا حساب شركة VLIS، وجدت "في الأعلى" الاستعلام عن البحث "السريع" بالاسم لبطاقات العمل.

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

0: ماذا يريد المستخدم

[KDPV من هنا]

ماذا يعني المستخدم عادة عندما يتحدث عن بحث "سريع" بالاسم؟ يكاد لا يتبين أنه بحث "عادل" مثل سلسلة فرعية ... LIKE '%роза%' - بعد كل شيء ، فالنتيجة ليست فقط 'Розалия' и 'Магазин Роза'لكن 'Гроза' وحتى 'Дом Деда Мороза'.

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

1: تحديد المهمة

وحتى أكثر من ذلك ، فإن الشخص لن يقدم على وجه التحديد 'роз магаз'بحيث يتعين عليك البحث عن بادئة لكل كلمة. لا ، من الأسهل بكثير على المستخدم الرد على تلميح سريع للكلمة الأخيرة بدلاً من "إدخال أقل" عمداً في الكلمات السابقة - انظر كيف يعمل أي محرك بحث على ذلك.

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

ماذا يفعل مطور مجرد؟

1.0: محرك بحث خارجي

أوه ، البحث صعب ، فأنت لا تريد أن تفعل شيئًا على الإطلاق - فلنمنحه للمطورين! دعهم ينشرون محرك بحث خارجي لقاعدة البيانات لنا: Sphinx ، ElasticSearch ، ...

خيار عملي ، وإن كان مستهلكًا للوقت من حيث المزامنة وكفاءة التغييرات. لكن ليس في حالتنا ، حيث يتم البحث عن كل عميل فقط في إطار بيانات حسابه. والبيانات لديها تقلبات عالية إلى حد ما - وإذا كان المدير قد أدخل البطاقة الآن 'Магазин Роза'، ثم بعد 5-10 ثوانٍ يمكنه أن يتذكر بالفعل أنه نسي تحديد البريد الإلكتروني هناك ويريد العثور عليه وإصلاحه.

لذلك - دعنا البحث "مباشرة في قاعدة البيانات". لحسن الحظ ، تتيح لنا PostgreSQL القيام بذلك ، وأكثر من خيار واحد - سننظر فيها.

1.1: سلسلة فرعية "صادقة"

نحن نتشبث بكلمة "سلسلة فرعية". ولكن بالضبط بالنسبة لبحث الفهرس عن طريق السلسلة الفرعية (وحتى بالتعبيرات النمطية!) هناك ممتاز وحدة pg_trgm! عندها فقط سيكون من الضروري الفرز بشكل صحيح.

دعنا نحاول أن نأخذ مثل هذه اللوحة من أجل بساطة النموذج:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

نقوم بتحميل 7.8 مليون سجل للمنظمات الحقيقية هناك ونقوم بفهرستها:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

لنبحث عن أول 10 سجلات لبحث السلاسل الفرعية:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[انظر إلىشرح.tensor.ru]

حسنًا ، هذا ... 26 مللي ثانية ، 31 ميجا بايت قراءة البيانات وأكثر من 1.7 ألف سجل تمت تصفيته - لمدة 10 بحث. النفقات العامة مرتفعة للغاية ، فهل من الممكن القيام بشيء أكثر كفاءة؟

1.2: البحث عن النص؟ إنها FTS!

في الواقع ، توفر PostgreSQL نطاقًا قويًا للغاية محرك بحث النص الكامل (البحث عن نص كامل) مع إمكانية البحث عن البادئة. خيار رائع ، لا تحتاج حتى إلى تثبيت ملحقات! لنجرب:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[انظر إلىشرح.tensor.ru]

ساعدنا موازاة تنفيذ الاستعلام قليلاً هنا ، مما أدى إلى تقليل الوقت بمقدار النصف إلى 11 مللي ثانية. نعم ، وكان علينا أن نقرأ 1.5 مرة أقل - في المجموع 20MB. وهنا كلما كان ذلك أقل - كلما كان ذلك أفضل ، لأنه كلما زاد الحجم الذي نطرحه ، زادت فرص الحصول على ذاكرة التخزين المؤقت المفقودة ، وكل صفحة بيانات إضافية تُقرأ من القرص هي "مكبح" محتمل للطلب.

1.3: ما زلت تحب؟

الطلب السابق جيد للجميع ، ولكن فقط إذا سحبه مائة ألف مرة في اليوم ، فسيتم تنفيذه 2TB إقرأ البيانات. في أحسن الأحوال - من الذاكرة ، ولكن إذا لم تكن محظوظًا ، فحينئذٍ من القرص. لذلك دعونا نحاول أن نجعلها أصغر.

تذكر ما يريد المستخدم رؤيته أولاً "التي تبدأ بـ ...". لذلك فهو في أنقى صوره. البحث عن البادئة من خلال text_pattern_ops! وفقط إذا "لم يكن لدينا ما يكفي" حتى 10 سجلات مطلوبة ، فسنضطر إلى قراءتها باستخدام بحث FTS:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[انظر إلىشرح.tensor.ru]

أداء ممتاز - إجمالي 0.05 مللي ثانية وما يزيد قليلاً عن 100 كيلوبايت يقرأ! نحن فقط نسينا الترتيب حسب الاسمحتى لا يضيع المستخدم في النتائج:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[انظر إلىشرح.tensor.ru]

أوه ، شيء ما لم يعد جميلًا بعد الآن - يبدو أن هناك فهرسًا ، لكن الفرز يتخطاه ... بالطبع ، إنه بالفعل أكثر كفاءة بعدة مرات من الإصدار السابق ، ولكن ...

1.4: "إنهاء بملف"

ولكن يوجد فهرس يسمح لك بالبحث حسب النطاق ، ومن الطبيعي استخدام الفرز - btree العادية!

CREATE INDEX ON firms(lower(name));

يجب "تجميع الطلب يدويًا" فقط:

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[انظر إلىشرح.tensor.ru]

ممتاز - وأعمال الفرز ، واستهلاك الموارد يبقى "مجهريا" ، آلاف المرات أكثر فعالية من FTS "الخالصة"! يبقى جمع في طلب واحد:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

لاحظ أنه يتم تنفيذ الاستعلام الفرعي الثاني فقط إذا عاد الأول أقل من المتوقع آخر LIMIT عدد الأسطر. حول هذه الطريقة في تحسين الاستعلامات ، أنا كتب بالفعل من قبل.

لذا نعم ، لدينا الآن btree و gin على الطاولة في نفس الوقت ، لكن من الناحية الإحصائية اتضح ذلك يصل أقل من 10٪ من الطلبات إلى تنفيذ الكتلة الثانية. أي ، مع هذه القيود النموذجية للمهمة المعروفة مسبقًا ، تمكنا من تقليل الاستهلاك الإجمالي لموارد الخادم بما يقرب من ألف مرة!

1.5 *: الاستغناء عن ملف

أعلى LIKE تم منعنا من استخدام الفرز الخاطئ. ولكن يمكن "تعيينه على المسار الصحيح" من خلال تحديد عامل التشغيل USING:

الافتراضي هو ASC. بالإضافة إلى ذلك ، يمكنك تحديد اسم عامل فرز معين في الفقرة USING. يجب أن يكون عامل الفرز عضوًا "أقل من" أو "أكبر من" في بعض عائلة عوامل تشغيل B-tree. ASC عادة ما يعادل USING < и DESC عادة ما يعادل USING >.

في حالتنا ، "أقل" ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[انظر إلىشرح.tensor.ru]

2: كيف تتعكر الطلبات

الآن نترك طلبنا لـ "الشراب" لمدة ستة أشهر أو سنة ، وبدهشة نجده مرة أخرى "في القمة" بمؤشرات إجمالي "الضخ" اليومي للذاكرة (شارك المخازن المؤقتة) في 5.5TB - هذا أكثر مما كان عليه في الأصل.

لا ، بالطبع ، ونمت أعمالنا ، وزاد عبء العمل ، ولكن ليس بنفس القدر! لذا ، هناك شيء ما ليس نظيفًا - فلنكتشف ذلك.

2.1: ولادة الاستدعاء

في مرحلة ما ، أراد فريق تطوير آخر أن يجعل من الممكن "القفز" إلى السجل من بحث سريع باستخدام نفس النتائج ، ولكن النتائج موسعة. وما التسجيل دون ترقيم الصفحات؟ دعونا نثبتها!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

الآن أصبح من الممكن للمطور إظهار سجل نتائج البحث مع تحميل "نوع الصفحة" دون إجهاد.

بالطبع ، في الواقع ، تتم قراءة المزيد والمزيد لكل صفحة بيانات تالية (كل ذلك من الوقت السابق ، والذي سوف نتجاهله ، بالإضافة إلى "الذيل" المطلوب) - أي ، هذا نمط مضاد لا لبس فيه. وسيكون من الأصح بدء البحث في التكرار التالي من المفتاح المخزن في الواجهة ، ولكن المزيد عن ذلك في وقت آخر.

2.2: تريد غريبة

في مرحلة ما ، أراد المطور تنويع العينة الناتجة بالبيانات من جدول آخر ، حيث تم إرسال الاستعلام السابق بالكامل إلى CTE:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

ومع ذلك ، ليس سيئًا ، حيث يتم تقييم الاستعلام الفرعي فقط لـ 10 سجلات تم إرجاعها ، إن لم يكن ...

2.3: تميز بلا معنى ولا رحمة

في مكان ما في عملية هذا التطور من الاستعلام الفرعي الثاني ضائع NOT LIKE حالة. من الواضح أنه بعد هذا UNION ALL بدأت في العودة بعض المداخل مرتين - تم العثور عليها أولاً في بداية السطر ، ثم مرة أخرى - في بداية الكلمة الأولى من هذا السطر. في الحد الأقصى ، يمكن أن تتطابق جميع سجلات الاستعلام الفرعي الثاني مع سجلات الاستعلام الأول.

ماذا يفعل المطور بدلاً من البحث عن سبب؟ .. ليس بسؤال!

• ضعف الحجم عينات أولية
• فرض DISTINCTللحصول على مثيل واحد فقط من كل صف

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

أي أنه من الواضح أن النتيجة ، في النهاية ، هي نفسها تمامًا ، لكن فرصة "الانتقال" إلى الاستعلام الفرعي الثاني لـ CTE أصبحت أعلى بكثير ، وحتى بدونها ، قراءة المزيد بوضوح.

لكن هذا ليس أتعس شيء. منذ طلب المطور للاختيار DISTINCT ليس لغرض محدد ، ولكن لجميع المجالات في وقت واحد السجلات ، ثم يتم تضمين حقل الاستعلام الفرعي تلقائيًا - نتيجة الاستعلام الفرعي. الآن ، للتنفيذ DISTINCT، كان على قاعدة البيانات أن تنفذ بالفعل ليس 10 استعلامات فرعية ، ولكن كل <2 * N> + 10!

2.4: التعاون قبل كل شيء!

لذلك ، عاش المطورون - لم يحزنوا ، لأنه في السجل "يفسد" قيم N المهمة مع تباطؤ مزمن في الحصول على كل "صفحة" تالية ، من الواضح أن المستخدم لم يتحلى بالصبر.

حتى جاءهم مطورو من قسم آخر ، ولم يرغبوا في استخدام مثل هذه الطريقة المريحة للبحث التكراري - أي أننا نأخذ قطعة من عينة ما ، ونرشحها بشروط إضافية ، ثم نرسم النتيجة ، ثم القطعة التالية (التي تتحقق في حالتنا بزيادة N) ، وهكذا دواليك حتى نملأ الشاشة.

بشكل عام ، في عينة تم اصطيادها وصل N إلى 17 ألفًا تقريبًا، وفي يوم واحد فقط ، تم تنفيذ ما لا يقل عن 4K من هذه الطلبات "على طول السلسلة". تم مسح آخر منهم بجرأة بالفعل 1 جيجا بايت من الذاكرة لكل تكرار...

في المجموع

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