PostgreSQL Antipatterns: نالي جي ذريعي ڳولا جي ٻيهر سڌاري جي هڪ ڪهاڻي، يا "اڳتي ۽ اڳتي وڌڻ جي اصلاح"

سڄي ملڪ ۾ سيلز آفيسن مان هزارين مينيجر رڪارڊ اسان جي CRM سسٽم روزانو هزارين رابطا - امڪاني يا موجوده گراهڪن سان رابطي جي حقيقت. ۽ انهي لاء، توهان کي پهريان هڪ گراهڪ ڳولڻ گهرجي، ۽ ترجيح طور تي تمام جلدي. ۽ اهو اڪثر ڪري نالي سان ٿئي ٿو.

تنهن ڪري، اها تعجب جي ڳالهه ناهي ته، هڪ ڀيرو ٻيهر هڪ تمام گهڻي لوڊ ٿيل ڊيٽابيس تي "ڀاري" سوالن جو تجزيو ڪرڻ - اسان جي پنهنجي VLSI ڪارپوريٽ اڪائونٽ، مون کي "مٿي ۾" مليو نالي سان "جلدي" ڳولا جي درخواست تنظيمي ڪارڊ لاء.

ان کان علاوه، وڌيڪ تحقيق هڪ دلچسپ مثال ظاهر ڪيو پهرين اصلاح ۽ پوءِ ڪارڪردگيءَ ۾ گهٽتائي ڪيترن ئي ٽيمن پاران ان جي ترتيب وار سڌارڻ جي درخواست، جن مان هر هڪ بهترين ارادن سان ڪم ڪيو.

0: صارف ڇا ٿو چاهي؟

[KDPV هتي کان]

هڪ صارف عام طور تي ڇا مطلب آهي جڏهن اهي ڳالهائيندا آهن "جلدي" ڳولا جي نالي سان؟ اهو لڳ ڀڳ ڪڏهن به نه نڪرندو آهي “ايماندار” ڳولا لاءِ هڪ سبسٽرنگ وانگر ... LIKE '%роза%' - ڇاڪاڻ ته پوء نتيجو شامل نه رڳو 'Розалия' и 'Магазин Роза'پر 'Гроза' ۽ اڃا به 'Дом Деда Мороза'.

صارف روزمره جي سطح تي فرض ڪري ٿو ته توھان کيس مهيا ڪندا لفظ جي شروعات سان ڳولا ڪريو عنوان ۾ ۽ ان کي وڌيڪ لاڳاپيل ٺاهيو تي شروع ٿئي ٿو داخل ٿيو. ۽ تون ائين ڪندين تقريبن فوري طور تي - interlinear ان پٽ لاء.

1: ڪم کي محدود ڪريو

۽ اڃا به وڌيڪ، هڪ شخص خاص طور تي داخل نه ٿيندو 'роз магаз'، ته جيئن توهان کي هر لفظ جي ڳولا ڪرڻي پوندي اڳياڙي سان. نه، اهو تمام آسان آهي هڪ صارف لاءِ آخري لفظ لاءِ تڪڙي اشاري جو جواب ڏيڻ بجاءِ اڳئين لفظ کي مقصد سان ”اڻ بيان“ ڪرڻ جي - ڏسو ته ڪيئن ڪو سرچ انجڻ هن کي سنڀالي ٿو.

عام صحيح مسئلي جي ضرورتن کي ترتيب ڏيڻ اڌ کان وڌيڪ حل آهي. ڪڏهن ڪڏهن محتاط استعمال ڪيس تجزيو نتيجن کي خاص طور تي متاثر ڪري سگھي ٿو.

هڪ خلاصو ڊولپر ڇا ڪندو آهي؟

1.0: خارجي سرچ انجڻ

اوه، ڳولها مشڪل آهي، مان ڪجهه به ڪرڻ نه ٿو چاهيان - اچو ته ان کي ڏيون! انهن کي ڊيٽابيس جي ٻاهران سرچ انجڻ لڳايو: Sphinx، ElasticSearch،...

هڪ ڪم ڪندڙ اختيار، جيتوڻيڪ محنت جي لحاظ کان هم وقت سازي ۽ تبديلين جي رفتار جي لحاظ کان. پر اسان جي صورت ۾ نه، ڇو ته ڳولا هر ڪلائنٽ لاء صرف هن جي اڪائونٽ ڊيٽا جي فريم ورڪ ۾ ڪئي وئي آهي. ۽ ڊيٽا کي ڪافي اعلي variability آهي - ۽ جيڪڏهن مئنيجر هاڻي ڪارڊ داخل ڪيو آهي 'Магазин Роза'، پوءِ 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 رڪارڊس لاءِ interlinear ڳولا لاءِ:

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

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

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

[explanation.tensor.ru تي ڏسو]

شاندار ڪارڪردگي - مجموعي 0.05ms ۽ 100KB کان ٿورو وڌيڪ پڙهو! صرف اسان وساري ڇڏيو نالي سان ترتيب ڏيوانهي ڪري ته صارف نتيجن ۾ گم نه ٿئي:

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

[explanation.tensor.ru تي ڏسو]

ها، ڪا شيءِ هاڻي ايتري خوبصورت نه رهي آهي- لڳي ٿو ته ڪا انڊيڪس موجود آهي، پر ان جي ترتيب سان گذري وڃي ٿي... اهو، يقيناً، اڳئين آپشن کان ڪيترائي ڀيرا وڌيڪ اثرائتو آهي، پر...

1.4: "فائل سان ختم ڪريو"

پر اتي ھڪڙو انڊيڪس آھي جيڪو توھان کي رينج ذريعي ڳولڻ جي اجازت ڏئي ٿو ۽ اڃا تائين عام طور تي ترتيب ڏيڻ استعمال ڪريو. باقاعده بيٽري!

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;

[explanation.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 لائنن جو تعداد. مان هن طريقي جي باري ۾ ڳالهائي رهيو آهيان سوال جي اصلاح جي اڳ ۾ ئي لکيو آهي.

سو ها، اسان وٽ هاڻي ميز تي بيٽري ۽ جين ٻئي آهن، پر شمارياتي طور تي اهو ظاهر ٿئي ٿو ته 10 سيڪڙو کان به گھٽ درخواستون ٻئي بلاڪ جي عمل تي پهچن ٿيون. اهو آهي، اهڙين مخصوص حدن سان جيڪي اڳ ۾ ڄاڻايل ڪم لاء، اسان سرور جي وسيلن جي ڪل استعمال کي تقريبا هڪ هزار ڀيرا گھٽائڻ جي قابل هئا!

1.5*: اسان بغير فائل ڪري سگھون ٿا

وڏيون LIKE اسان کي غلط ترتيب ڏيڻ کان روڪيو ويو. پر ان کي استعمال ڪندڙ آپريٽر جي وضاحت ڪندي ”صحيح رستي تي مقرر“ ڪري سگھجي ٿو:

ڊفالٽ طور اهو فرض ڪيو ويو آهي ASC. اضافي طور تي، توهان هڪ خاص ترتيب آپريٽر جو نالو هڪ شق ۾ بيان ڪري سگهو ٿا USING. ترتيب ڏيڻ وارو آپريٽر لازمي طور تي B-tree آپريٽرز جي ڪجهه خاندانن کان گهٽ يا وڌيڪ جو ميمبر هجڻ گهرجي. ASC عام طور تي برابر USING < и DESC عام طور تي برابر USING >.

اسان جي حالت ۾، "گهٽ" آهي ~<~:

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

[explanation.tensor.ru تي ڏسو]

2: درخواستون ڪيئن خراب ٿيون

ھاڻي اسان پنھنجي گذارش کي ڇھن مھينن يا ھڪ سال لاءِ ”اُڻڻ“ لاءِ ڇڏي ڏيون ٿا، ۽ اسان کي حيرت ٿي آھي ته ان کي وري ”مٿين“ تي ڳولھيو آھي، جنھن جي ڪل روزاني ”پمپنگ“ جي ياداشت جي اشارن سان (buffers شيئر ڪيو ويو) ۾ 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 فيلڊ - ذيلي پڇا ڳاڇا جو نتيجو - خودڪار طور تي اتي شامل ڪيو ويو. هاڻي، عمل ڪرڻ لاء DISTINCT، ڊيٽابيس کي اڳ ۾ ئي عمل ڪرڻو پوندو هو 10 ذيلي سوال نه، پر سڀ <2 * N> + 10!

2.4: تعاون سڀني کان مٿي!

تنهن ڪري، ڊولپرز رهندا هئا - انهن کي پريشان نه ڪيو، ڇاڪاڻ ته صارف واضح طور تي هر ايندڙ "صفحو" حاصل ڪرڻ ۾ هڪ دائمي سستي سان گڏ اهم N قدرن کي رجسٽري کي "ايڊٽ" ڪرڻ لاء ڪافي صبر نه ڪيو.

جيستائين ڪنهن ٻئي کاتي مان ڊولپرز وٽن آيا ۽ اهڙي آسان طريقو استعمال ڪرڻ چاهيندا هئا ٻيهر ڳولڻ لاء - اهو آهي، اسان ڪجهه نموني مان هڪ ٽڪرو وٺون ٿا، ان کي اضافي حالتن سان فلٽر ڪريو، نتيجو ڪڍو، پوء ايندڙ ٽڪرو (جيڪو اسان جي صورت ۾ N وڌائڻ سان حاصل ٿئي ٿو)، ۽ ائين ئي جيستائين اسان اسڪرين کي ڀريو.

عام طور تي، پڪڙيل نموني ۾ N تقريبن 17K جي قيمتن تي پهچي ويو، ۽ صرف هڪ ڏينهن ۾ گهٽ ۾ گهٽ 4K اهڙين درخواستن تي عمل ڪيو ويو ”زنجيرن سان گڏ“. انهن مان آخري کي جرئت سان اسڪين ڪيو ويو 1GB ميموري في ورجائي...

ڪل

جو ذريعو: www.habr.com