PostgreSQL Antipatterns፡ የፍለጋ ተደጋጋሚ ማሻሻያ ታሪክ፣ ወይም "ወደ ኋላ እና ወደ ፊት ማመቻቸት"

በመላው አገሪቱ የሚገኙ በሺዎች የሚቆጠሩ የሽያጭ ቢሮዎች አስተዳዳሪዎች ተመዝግበዋል የእኛ 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;

[ማብራሪያውን ይመልከቱ.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]

እዚህ የጥያቄ አፈፃፀም ትይዩነት ትንሽ ረድቶናል፣ ሰዓቱን በግማሽ ቆርጦ 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.05ms እና ከ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 እና ጂን በጠረጴዛው ላይ አሉን ፣ ግን በስታቲስቲክስ መሰረት ያ ተለወጠ። ከ 10% ያነሱ ጥያቄዎች የሁለተኛው እገዳ አፈፃፀም ላይ ደርሰዋል. ማለትም፣ ለሥራው አስቀድሞ በሚታወቁ እንደዚህ ያሉ ዓይነተኛ ገደቦች፣ አጠቃላይ የአገልጋይ ሀብቶችን ፍጆታ ወደ አንድ ሺህ ጊዜ ያህል መቀነስ ችለናል!

1.5*: ያለ ፋይል ማድረግ እንችላለን

ከፍ ያለ LIKE የተሳሳተ መደርደር እንዳንጠቀም ተከልክለናል። ነገር ግን የ USING ኦፕሬተርን በመጥቀስ "በትክክለኛው መንገድ ላይ" ሊደረግ ይችላል.

በነባሪነት ይገመታል ASC. በተጨማሪም፣ በአንድ አንቀፅ ውስጥ የአንድ የተወሰነ አይነት ኦፕሬተር ስም መግለጽ ይችላሉ። USING. የዓይነት ኦፕሬተር ከአንዳንድ የቢ-ዛፍ ኦፕሬተሮች ቤተሰብ ያነሰ ወይም የበለጠ አባል መሆን አለበት። 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፡ ልዩነት ትርጉም የለሽ እና ምሕረት የለሽ ነው።

ከ 2 ኛ ንዑስ መጠይቅ በእንደዚህ ዓይነት የዝግመተ ለውጥ ሂደት ውስጥ የሆነ ቦታ ጠፋ 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 ለተወሰኑት ሳይሆን ለሁሉም መስኮች በአንድ ጊዜ መዝገቦች ፣ ከዚያ የንዑስ_መጠይቁ መስክ - የንዑስ መጠይቁ ውጤት - ወዲያውኑ እዚያ ውስጥ ተካቷል። አሁን፣ ለማስፈጸም DISTINCT, የውሂብ ጎታው ቀድሞውኑ መፈጸም ነበረበት 10 ንዑስ መጠይቆች አይደሉም፣ ግን ሁሉም <2 * N> + 10!

2.4፡ ትብብር ከሁሉም በላይ!

ስለዚህ ፣ ገንቢዎቹ ኖረዋል - አልተጨነቁም ፣ ምክንያቱም ተጠቃሚው በግልጽ መዝገቡን ወደ ጉልህ N እሴቶች “ለማስተካከል” በቂ ትዕግስት ስላልነበረው እያንዳንዱን ቀጣይ “ገጽ” በመቀበል ሥር የሰደደ መዘግየት።

ከሌላ ክፍል የመጡ ገንቢዎች ወደ እነርሱ እስኪመጡ እና እንደዚህ አይነት ምቹ ዘዴን ለመጠቀም እስኪፈልጉ ድረስ ለተደጋጋሚ ፍለጋ - ማለትም ከአንዳንድ ናሙናዎች አንድ ቁራጭ እንወስዳለን, ተጨማሪ ሁኔታዎችን በማጣራት, ውጤቱን ይሳሉ, ከዚያም የሚቀጥለውን ቁራጭ (በእኛ ሁኔታ N በመጨመር ነው), እና ማያ ገጹን እስክንሞላ ድረስ.

በአጠቃላይ, በተያዘው ናሙና ውስጥ N ወደ 17 ሺህ የሚጠጉ እሴቶች ላይ ደርሷል፣ እና በአንድ ቀን ውስጥ ቢያንስ 4 ኪ.ሜ እንደዚህ ያሉ ጥያቄዎች "በሰንሰለቱ" ተፈፅመዋል። የመጨረሻዎቹ በድፍረት ተቃኙ በአንድ ድግግሞሽ 1 ጂቢ ማህደረ ትውስታ...

ԸՆԴՀԱՆՈՒՐ ԳԻՆ

ምንጭ: hab.com