PostgreSQL ضد نمونې: د نوم په واسطه د لټون د تکراري اصالحاتو کیسه، یا "شاته او شاته اصلاح کول"

په ټول هیواد کې د پلور دفترونو څخه زرګونه مدیران ثبتوي زموږ د CRM سیسټم هره ورځ لسګونه زره اړیکې - د احتمالي یا موجوده پیرودونکو سره د اړیکو حقایق. او د دې لپاره، تاسو باید لومړی یو پیرودونکي ومومئ، او په غوره توګه ډیر ژر. او دا ډیری وختونه د نوم له مخې پیښیږي.

له همدې امله، دا د حیرانتیا خبره نده چې، یو ځل بیا د ډیری بار شوي ډیټابیسونو څخه د "درنو" پوښتنو تحلیل کول - زموږ خپل د VLSI کارپوریټ حساب، ما "په سر کې" وموندله د نوم په واسطه د "چټک" لټون لپاره غوښتنه وکړئ د سازمان کارتونو لپاره.

برسېره پردې، نور تحقیقات یو په زړه پوری مثال څرګند کړ لومړی اصلاح کول او بیا د فعالیت تخریب د څو ټیمونو لخوا د دې ترتیبي اصالح کولو غوښتنه، چې هر یو یې یوازې د غوره نیت سره عمل کړی.

0: کاروونکي څه غوښتل؟

[KDPV له دې ځایه]

یو کارن معمولا څه معنی لري کله چې دوی د نوم په واسطه د "چټک" لټون په اړه خبرې کوي؟ دا تقریبا هیڅکله د سبسټرینګ په څیر د "صادق" لټون نه وګرځي ... LIKE '%роза%' - ځکه چې بیا پایله کې نه یوازې شامل دي 'Розалия' и 'Магазин Роза'خو 'Гроза' او حتي 'Дом Деда Мороза'.

کارونکي په ورځني کچه فرض کوي چې تاسو به ورته چمتو کړئ د کلمې په پیل کې لټون په سرلیک کې او دا نور اړونده کړئ سره پیل کیږي داخل شو او تاسو به یې وکړئ نږدې سمدستي - د انټرلینر ان پټ لپاره.

۱: کار محدود کړئ

او حتی نور هم، یو سړی به په ځانګړې توګه ننوځي 'роз магаз'، نو تاسو باید د مخکینۍ په واسطه د هرې کلمې لټون وکړئ. نه، دا د یو کاروونکي لپاره خورا اسانه دی چې د وروستي کلمې لپاره ګړندي اشارې ته ځواب ووایی په عمدي توګه د پخوانیو "نیم مشخص کولو" په پرتله - وګورئ چې د لټون کوم انجن دا څنګه اداره کوي.

په عمومي ډول ښی د ستونزې لپاره د اړتیاوو جوړول د حل نیمایي څخه زیات دي. ځینې ​​​​وختونه د قضیې تحلیل په احتیاط سره کارول کیږي کولی شي په پایله کې د پام وړ اغیزه وکړي.

د خلاصون پراختیا کونکی څه کوي؟

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 ته وګورئ]

ښه، دا ... 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;

[ تشریح.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;

[ تشریح.tensor.ru ته وګورئ]

عالي فعالیت - ټول 0.05ms او د 100KB څخه لږ څه ډیر ولولئ! یوازې موږ هیر کړل په نوم ترتیبترڅو کاروونکي په پایلو کې له لاسه ورنکړي:

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

[ تشریح.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;

[ تشریح.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. د ترتیب کوونکی باید د 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: DISTINCT بې حسه او بې رحمه دی

د دوهم فرعي پوښتنې څخه د داسې تکامل پروسې په جریان کې ورک شو 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 د ځانګړو لپاره نه، مګر په یو وخت کې د ټولو ساحو لپاره ریکارډونه، بیا د sub_query ساحه - د فرعي پوښتنې پایله - په اتوماتيک ډول هلته شامله شوه. اوس، د اجرا کولو لپاره DISTINCT، ډیټابیس باید دمخه اجرا کړي نه 10 فرعي پوښتنې، مګر ټولې <2 * N> + 10!

2.4: له هرڅه پورته همکاري!

نو، پراختیا کونکي پرته له ځورونې ژوند کوي، ځکه چې کاروونکي په ښکاره ډول دومره صبر نه درلود چې راجستر د پام وړ N ارزښتونو ته "تنظیم" کړي چې د هرې راتلونکې "پاڼې" ترلاسه کولو کې د اوږدمهاله سستۍ سره.

تر هغه چې د بلې څانګې پراختیا کونکي دوی ته راغلل او غوښتل یې چې دا ډول مناسب میتود وکاروي د تکراري لټون لپاره - دا دی، موږ د یو څه نمونې څخه یوه ټوټه اخلو، د اضافي شرایطو سره یې فلټر کوو، پایله یې راښکته کوو، بیا بله ټوټه (کوم چې زموږ په قضیه کې د N زیاتولو سره ترلاسه کیږي)، او داسې نور تر هغه چې موږ سکرین ډک کړو.

په عموم کې، په نیول شوي نمونه کې N نږدې 17K ارزښتونو ته ورسید، او یوازې په یوه ورځ کې لږترلږه 4K ورته غوښتنې "د سلسلې په اوږدو کې" اجرا شوي. د دوی وروستي په زړورتیا سره سکین شوي په هر تکرار کې 1GB حافظه...

ټول

سرچینه: www.habr.com