পোস্টগ্রেএসকিউএল অ্যান্টিপ্যাটার্নস: নামের দ্বারা অনুসন্ধানের পুনরাবৃত্তিমূলক পরিমার্জনার গল্প, বা "অপ্টিমাইজ করা এবং সামনের দিকে"

সারা দেশে সেলস অফিস থেকে হাজার হাজার ম্যানেজার ঠিক করে আমাদের সিআরএম সিস্টেম প্রতিদিন হাজার হাজার পরিচিতি - সম্ভাব্য বা ইতিমধ্যে আমাদের ক্লায়েন্টদের সাথে কাজ করার সাথে যোগাযোগের তথ্য। এবং এই ক্লায়েন্টের জন্য, আপনাকে অবশ্যই প্রথমে খুঁজে বের করতে হবে, এবং খুব দ্রুত। এবং এটি প্রায়শই নামে ঘটে।

অতএব, এটি আশ্চর্যজনক নয় যে, আবারও সবচেয়ে লোড করা ডেটাবেসগুলির একটিতে "ভারী" প্রশ্নগুলি বিশ্লেষণ করা - আমাদের নিজস্ব VLIS কর্পোরেট অ্যাকাউন্ট, আমি "শীর্ষে" খুঁজে পেয়েছি নাম দ্বারা "দ্রুত" অনুসন্ধানের জন্য ক্যোয়ারী ব্যবসায়িক কার্ডের জন্য।

তদুপরি, আরও তদন্তে একটি আকর্ষণীয় উদাহরণ প্রকাশিত হয়েছে প্রথমে অপ্টিমাইজেশান, তারপর কর্মক্ষমতা অবনতি বেশ কয়েকটি দল দ্বারা এর ধারাবাহিক সমাপ্তির সাথে অনুরোধ, যার প্রত্যেকটি শুধুমাত্র সর্বোত্তম উদ্দেশ্য থেকে কাজ করেছে।

0: ব্যবহারকারী কি চেয়েছিলেন

[কেডিপিভি এখানে থেকে]

ব্যবহারকারী যখন নাম দ্বারা একটি "দ্রুত" অনুসন্ধান সম্পর্কে কথা বলে তখন তাকে সাধারণত কী বোঝায়? এটি প্রায় কখনই একটি "ন্যায্য" সাবস্ট্রিং অনুসন্ধান হিসাবে পরিণত হয় না ... LIKE '%роза%' - সব পরে, তারপর ফলাফল না শুধুমাত্র 'Розалия' и 'Магазин Роза'কিন্তু 'Гроза' এমনকি এমনকি 'Дом Деда Мороза'.

ব্যবহারকারী মানে পারিবারিক পর্যায়ে আপনি তাকে প্রদান করবেন একটি শব্দের শুরুতে অনুসন্ধান করুন শিরোনামে এবং আরো প্রাসঙ্গিক কি দেখান শুরু হয় প্রবেশ এবং এটি তৈরি করুন প্রায় সঙ্গে সঙ্গে - সাবস্ক্রিপ্ট ইনপুট সহ।

1: টাস্ক সীমিত করুন

এবং আরও বেশি, একজন ব্যক্তি বিশেষভাবে পরিচয় করিয়ে দেবেন না 'роз магаз'যাতে আপনাকে প্রতিটি শব্দের জন্য উপসর্গ অনুসন্ধান করতে হবে। না, একজন ব্যবহারকারীর জন্য পূর্ববর্তী শব্দগুলি ইচ্ছাকৃতভাবে "আন্ডার-এন্টার" করার চেয়ে শেষ শব্দের জন্য একটি দ্রুত ইঙ্গিতের প্রতিক্রিয়া দেওয়া অনেক সহজ - দেখুন যে কোনও সার্চ ইঞ্জিন এটি কীভাবে কাজ করে৷

সাধারণত, সঠিকভাবে সমস্যার জন্য প্রয়োজনীয়তা প্রণয়ন করা অর্ধেকেরও বেশি সমাধান। কখনও কখনও সাবধানে ব্যবহার কেস বিশ্লেষণ উল্লেখযোগ্যভাবে ফলাফল প্রভাবিত করতে পারে।.

একটি বিমূর্ত বিকাশকারী কি করে?

1.0: বাহ্যিক সার্চ ইঞ্জিন

ওহ, অনুসন্ধান করা কঠিন, আপনি মোটেও কিছু করতে চান না - আসুন এটি ডেভপসকে দেওয়া যাক! তাদের আমাদের জন্য ডাটাবেসের বাইরের সার্চ ইঞ্জিন স্থাপন করতে দিন: স্ফিঙ্কস, ইলাস্টিক সার্চ, ...

সিঙ্ক্রোনাইজেশন এবং পরিবর্তনের দক্ষতার পরিপ্রেক্ষিতে একটি কার্যকরী, সময় সাপেক্ষ বিকল্প। কিন্তু আমাদের ক্ষেত্রে নয়, যেহেতু অনুসন্ধান প্রতিটি ক্লায়েন্টের জন্য শুধুমাত্র তার অ্যাকাউন্ট ডেটার কাঠামোর মধ্যেই করা হয়। এবং ডেটাতে মোটামুটি উচ্চ পরিবর্তনশীলতা রয়েছে - এবং যদি এখন ম্যানেজার একটি কার্ড প্রবেশ করেছে 'Магазин Роза', তারপর 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: আলাদা অনুভূতিহীন এবং নির্দয়

কোথাও ২য় সাবকুয়েরি থেকে এরকম বিবর্তনের প্রক্রিয়ায় নিখোঁজ NOT LIKE শর্ত. এর পরেই বোঝা যায় UNION ALL ফিরতে শুরু করে কিছু এন্ট্রি দুইবার - প্রথমে লাইনের শুরুতে পাওয়া যায়, এবং তারপর আবার - এই লাইনের প্রথম শব্দের শুরুতে। সীমার মধ্যে, 2য় সাবকোয়েরির সমস্ত রেকর্ড প্রথমটির রেকর্ডের সাথে মেলে।

কারণ খোঁজার পরিবর্তে একজন ডেভেলপার কী করেন?... প্রশ্ন নয়!

• আকার দ্বিগুণ প্রাথমিক নমুনা
• 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>;

অর্থাৎ, এটি স্পষ্ট যে ফলাফলটি, শেষ পর্যন্ত, ঠিক একই, তবে 2য় CTE সাবকোয়েরিতে "উড়তে" যাওয়ার সুযোগ অনেক বেশি হয়ে গেছে, এবং এমনকি এটি ছাড়াই, স্পষ্টভাবে আরো পড়ুন.

তবে এটি সবচেয়ে দুঃখজনক বিষয় নয়। যেহেতু ডেভেলপার নির্বাচন করতে বলেছেন DISTINCT নির্দিষ্ট জন্য নয়, কিন্তু একবারে সব ক্ষেত্রের জন্য রেকর্ড, তারপর sub_query ক্ষেত্র স্বয়ংক্রিয়ভাবে অন্তর্ভুক্ত করা হয় - সাবকোয়েরির ফলাফল। এখন, চালানো DISTINCT, ডাটাবেস ইতিমধ্যেই চালানো ছিল 10টি সাবকোয়ারি নয়, কিন্তু সমস্ত <2 * N> + 10!

2.4: সর্বোপরি সহযোগিতা!

সুতরাং, বিকাশকারীরা বেঁচে ছিলেন - তারা শোক করেনি, কারণ রেজিস্ট্রিতে উল্লেখযোগ্য N মানগুলিতে "স্ক্রু আপ" প্রতিটি পরবর্তী "পৃষ্ঠা" পাওয়ার ক্ষেত্রে দীর্ঘস্থায়ী মন্থরতার সাথে, ব্যবহারকারীর স্পষ্টত ধৈর্য ছিল না।

যতক্ষণ না অন্য বিভাগের বিকাশকারীরা তাদের কাছে আসেন, এবং এমন সুবিধাজনক পদ্ধতি ব্যবহার করতে চান না পুনরাবৃত্তিমূলক অনুসন্ধানের জন্য - অর্থাৎ, আমরা কিছু নমুনা থেকে একটি টুকরো নিই, অতিরিক্ত শর্ত দ্বারা ফিল্টার করি, ফলাফলটি আঁকি, তারপরে পরবর্তী অংশটি (যা আমাদের ক্ষেত্রে N বৃদ্ধি করে অর্জন করা হয়), এবং আমরা স্ক্রিনটি পূরণ না করা পর্যন্ত।

সাধারণভাবে, একটি ধরা নমুনা মধ্যে N প্রায় 17K পৌঁছেছে, এবং মাত্র এক দিনে, অন্তত 4K এই ধরনের অনুরোধগুলি "শৃঙ্খল বরাবর" কার্যকর করা হয়েছিল। তাদের শেষ সাহসীভাবে ইতিমধ্যে দ্বারা স্ক্যান প্রতি পুনরাবৃত্তি প্রতি 1GB মেমরি...

মোটে

উত্স: www.habr.com