پوسٹگری ایس کیو ایل اینٹی پیٹرنز: نام کے ذریعہ تلاش کی تکراری اصلاح کی کہانی، یا "آگے پیچھے کی اصلاح"

ملک بھر میں سیلز دفاتر کے ہزاروں مینیجرز کا ریکارڈ ہمارا CRM سسٹم روزانہ دسیوں ہزار رابطے - ممکنہ یا موجودہ گاہکوں کے ساتھ مواصلت کے حقائق۔ اور اس کے لئے، آپ کو سب سے پہلے ایک کلائنٹ تلاش کرنا ضروری ہے، اور ترجیحا بہت جلد. اور یہ اکثر نام سے ہوتا ہے۔

لہذا، یہ حیرت کی بات نہیں ہے کہ، ایک بار پھر سب سے زیادہ بھرے ہوئے ڈیٹا بیس میں سے ایک پر "بھاری" سوالات کا تجزیہ کرنا - ہمارے اپنے VLSI کارپوریٹ اکاؤنٹمجھے "اوپر میں" ملا نام سے "فوری" تلاش کی درخواست کریں۔ تنظیمی کارڈز کے لیے۔

مزید یہ کہ مزید تفتیش سے ایک دلچسپ مثال سامنے آئی پہلے اصلاح اور پھر کارکردگی میں کمی متعدد ٹیموں کے ذریعہ اس کی ترتیب وار تطہیر کے ساتھ درخواست، جن میں سے ہر ایک نے مکمل طور پر بہترین ارادوں کے ساتھ کام کیا۔

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;

[explain.tensor.ru پر دیکھیں]

ٹھیک ہے، یہ... 26ms، 31MB ڈیٹا پڑھیں اور 1.7K سے زیادہ فلٹر شدہ ریکارڈز - 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;

[explain.tensor.ru پر دیکھیں]

یہاں استفسار پر عمل درآمد کے متوازی ہونے سے ہمیں تھوڑی مدد ملی، وقت کو آدھے میں کاٹ کر 11ms. اور ہمیں 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;

[explain.tensor.ru پر دیکھیں]

بہترین کارکردگی - کل 0.05ms اور 100KB سے تھوڑا زیادہ پڑھیں! بس ہم بھول گئے۔ نام سے ترتیب دیںتاکہ صارف نتائج میں گم نہ ہو:

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

[explain.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;

[explain.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 ہمیں غلط چھانٹی کے استعمال سے روکا گیا تھا۔ لیکن اسے استعمال کرنے والے آپریٹر کی وضاحت کر کے "صحیح راستے پر سیٹ" کیا جا سکتا ہے:

پہلے سے طے شدہ طور پر یہ فرض کیا جاتا ہے۔ ASC. مزید برآں، آپ ایک شق میں مخصوص ترتیب والے آپریٹر کا نام بتا سکتے ہیں۔ USING. ترتیب دینے والا آپریٹر بی ٹری آپریٹرز کے کچھ خاندان سے کم یا زیادہ کا رکن ہونا چاہیے۔ ASC عام طور پر برابر USING < и DESC عام طور پر برابر USING >.

ہمارے معاملے میں، "کم" ہے۔ ~<~:

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

[explain.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: DISTINCT بے حس اور بے رحم ہے۔

کہیں 2nd subquery سے اس طرح کے ارتقاء کے عمل میں کھو گیا NOT LIKE حالت. واضح رہے کہ اس کے بعد UNION ALL لوٹنا شروع کر دیا کچھ اندراجات دو بار - سب سے پہلے لائن کے شروع میں ملا، اور پھر دوبارہ - اس لائن کے پہلے لفظ کے شروع میں۔ حد میں، 2nd subquery کے تمام ریکارڈ پہلے کے ریکارڈ سے مماثل ہو سکتے ہیں۔

ایک ڈویلپر وجہ تلاش کرنے کے بجائے کیا کرتا ہے؟... کوئی سوال نہیں!

• سائز دوگنا اصل نمونے
• 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>;

یعنی، یہ واضح ہے کہ نتیجہ، آخر میں، بالکل وہی ہے، لیکن 2nd CTE ذیلی سوال میں "اڑنے" کا امکان بہت زیادہ ہو گیا ہے، اور اس کے بغیر بھی، واضح طور پر زیادہ پڑھنے کے قابل.

لیکن یہ سب سے افسوسناک چیز نہیں ہے۔ چونکہ ڈویلپر نے منتخب کرنے کو کہا DISTINCT مخصوص لوگوں کے لیے نہیں، بلکہ ایک ساتھ تمام شعبوں کے لیے ریکارڈز، پھر sub_query فیلڈ — subquery کا نتیجہ — خود بخود وہاں شامل ہو گیا تھا۔ اب، عملدرآمد کرنے کے لئے DISTINCT، ڈیٹا بیس کو پہلے ہی عمل میں لانا تھا۔ 10 ذیلی سوالات نہیں، بلکہ تمام <2 * N> + 10!

2.4: تعاون سب سے بڑھ کر!

لہذا، ڈویلپرز زندہ رہے - انہوں نے پریشان نہیں کیا، کیونکہ صارف کے پاس واضح طور پر اتنا صبر نہیں تھا کہ وہ رجسٹری کو اہم N اقدار کے ساتھ "ایڈجسٹ" کرسکے جس کے بعد ہر ایک "صفحہ" کو حاصل کرنے میں ایک دائمی سست روی تھی۔

جب تک کہ دوسرے محکمے کے ڈویلپرز ان کے پاس آئے اور اس طرح کا آسان طریقہ استعمال کرنا چاہتے تھے۔ تکراری تلاش کے لیے - یعنی، ہم کچھ نمونے سے ایک ٹکڑا لیتے ہیں، اسے اضافی شرائط کے مطابق فلٹر کرتے ہیں، نتیجہ نکالتے ہیں، پھر اگلا ٹکڑا (جو ہمارے معاملے میں N کو بڑھا کر حاصل کیا جاتا ہے)، اور اسی طرح جب تک ہم اسکرین کو بھر نہیں دیتے۔

عام طور پر، پکڑے گئے نمونے میں N تقریباً 17K کی قدروں تک پہنچ گیا۔، اور صرف ایک دن میں اس طرح کی کم از کم 4K درخواستوں کو "زنجیر کے ساتھ" انجام دیا گیا۔ ان میں سے آخری کو ڈھٹائی سے اسکین کیا گیا۔ 1GB میموری فی تکرار...

مجموعی طور پر

ماخذ: www.habr.com