PostgreSQL యాంటీప్యాటర్న్స్: ఎ టేల్ ఆఫ్ ఇటరేటివ్ రిఫైన్మెంట్ ఆఫ్ సెర్చ్ ఆఫ్ నేమ్, లేదా "ఆప్టిమైజింగ్ బ్యాక్ అండ్ ఫార్త్"

దేశవ్యాప్తంగా ఉన్న విక్రయాల కార్యాలయాల నుండి వేలాది మంది నిర్వాహకులు రికార్డు సృష్టించారు మా CRM వ్యవస్థ రోజూ పదివేల పరిచయాలు - సంభావ్య లేదా ఇప్పటికే ఉన్న ఖాతాదారులతో కమ్యూనికేషన్ యొక్క వాస్తవాలు. మరియు దీని కోసం, మీరు మొదట క్లయింట్‌ను కనుగొనాలి మరియు ప్రాధాన్యంగా చాలా త్వరగా ఉండాలి. మరియు ఇది చాలా తరచుగా పేరుతో జరుగుతుంది.

అందువల్ల, అత్యంత లోడ్ చేయబడిన డేటాబేస్‌లలో ఒకటైన “భారీ” ప్రశ్నలను మరోసారి విశ్లేషించడంలో ఆశ్చర్యం లేదు - మన స్వంతం VLSI కార్పొరేట్ ఖాతా, నేను "పైభాగంలో" కనుగొన్నాను పేరు ద్వారా "శీఘ్ర" శోధన కోసం అభ్యర్థన సంస్థ కార్డుల కోసం.

అంతేకాకుండా, తదుపరి విచారణ ఒక ఆసక్తికరమైన ఉదాహరణను వెల్లడించింది మొదటి ఆప్టిమైజేషన్ మరియు తరువాత పనితీరు క్షీణత అనేక బృందాలు దాని వరుస శుద్ధీకరణతో అభ్యర్థించండి, వీటిలో ప్రతి ఒక్కటి ఉత్తమ ఉద్దేశ్యంతో మాత్రమే పని చేస్తాయి.

0: వినియోగదారు ఏమి కోరుకుంటున్నారు?

[KDPV ఇక్కడ నుండి]

వినియోగదారు పేరు ద్వారా "శీఘ్ర" శోధన గురించి మాట్లాడినప్పుడు సాధారణంగా అర్థం ఏమిటి? ఇది దాదాపు ఎన్నడూ వంటి సబ్‌స్ట్రింగ్ కోసం "నిజాయితీ" శోధనగా మారదు ... LIKE '%роза%' - ఎందుకంటే అప్పుడు ఫలితం మాత్రమే కాదు 'Розалия' и 'Магазин Роза'కానీ 'Гроза' మరియు కూడా 'Дом Деда Мороза'.

మీరు అతనికి అందిస్తారని వినియోగదారు రోజువారీ స్థాయిలో ఊహిస్తారు పదం ప్రారంభం ద్వారా శోధించండి శీర్షికలో మరియు దానిని మరింత సందర్భోచితంగా చేయండి తో మొదలవుతుంది ప్రవేశించింది. మరియు మీరు దీన్ని చేస్తారు దాదాపు తక్షణమే - ఇంటర్‌లీనియర్ ఇన్‌పుట్ కోసం.

1: పనిని పరిమితం చేయండి

మరియు ఇంకా ఎక్కువగా, ఒక వ్యక్తి ప్రత్యేకంగా ప్రవేశించడు 'роз магаз', కాబట్టి మీరు ప్రతి పదాన్ని ఉపసర్గ ద్వారా శోధించాలి. లేదు, మునుపటి పదాలను ఉద్దేశపూర్వకంగా "తక్కువగా పేర్కొనడం" కంటే చివరి పదం కోసం శీఘ్ర సూచనకు ప్రతిస్పందించడం వినియోగదారుకు చాలా సులభం - ఏదైనా శోధన ఇంజిన్ దీన్ని ఎలా నిర్వహిస్తుందో చూడండి.

సాధారణంగా, సరిగ్గా సమస్యకు అవసరాలను రూపొందించడం సగం కంటే ఎక్కువ పరిష్కారం. కొన్నిసార్లు జాగ్రత్తగా ఉపయోగం కేస్ విశ్లేషణ ఫలితాన్ని గణనీయంగా ప్రభావితం చేయవచ్చు.

వియుక్త డెవలపర్ ఏమి చేస్తాడు?

1.0: బాహ్య శోధన ఇంజిన్

ఓహ్, శోధన కష్టం, నేను ఏమీ చేయకూడదనుకుంటున్నాను - దానిని devopsకి ఇద్దాం! డేటాబేస్ వెలుపల శోధన ఇంజిన్‌ని అమలు చేయడానికి వారిని అనుమతించండి: సింహిక, సాగే శోధన,...

సింక్రొనైజేషన్ మరియు మార్పుల వేగం పరంగా శ్రమతో కూడుకున్నది అయినప్పటికీ పని ఎంపిక. కానీ మా విషయంలో కాదు, ప్రతి క్లయింట్ కోసం అతని ఖాతా డేటా ఫ్రేమ్‌వర్క్‌లో మాత్రమే శోధన జరుగుతుంది. మరియు డేటా చాలా ఎక్కువ వైవిధ్యాన్ని కలిగి ఉంది - మరియు మేనేజర్ ఇప్పుడు కార్డ్‌లోకి ప్రవేశించినట్లయితే 'Магазин Роза', ఆపై 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 చూడండి]

ఇక్కడ ప్రశ్న అమలు యొక్క సమాంతరీకరణ మాకు కొద్దిగా సహాయపడింది, సమయాన్ని సగానికి తగ్గించింది 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;

[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 పంక్తుల సంఖ్య. నేను ఈ ప్రశ్న ఆప్టిమైజేషన్ పద్ధతి గురించి మాట్లాడుతున్నాను ఇంతకు ముందు వ్రాసారు.

కాబట్టి అవును, మేము ఇప్పుడు టేబుల్‌పై బిట్రీ మరియు జిన్ రెండింటినీ కలిగి ఉన్నాము, కానీ గణాంకపరంగా అది తేలింది 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;

[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: వైవిధ్యం తెలివిలేనిది మరియు కనికరం లేనిది

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 నిర్దిష్ట వాటి కోసం కాదు, అన్ని ఫీల్డ్‌ల కోసం ఒకేసారి రికార్డులు, ఆపై sub_query ఫీల్డ్ — subquery యొక్క ఫలితం — స్వయంచాలకంగా అక్కడ చేర్చబడుతుంది. ఇప్పుడు, అమలు చేయడానికి DISTINCT, డేటాబేస్ ఇప్పటికే అమలు చేయాలి 10 సబ్‌క్వెరీలు కాదు, అన్నీ + 2!

2.4: అన్నింటికంటే సహకారం!

కాబట్టి, డెవలపర్లు ఇబ్బంది పడకుండా జీవించారు, ఎందుకంటే ప్రతి తదుపరి “పేజీ”ని స్వీకరించడంలో దీర్ఘకాలిక మందగమనంతో రిజిస్ట్రీని ముఖ్యమైన N విలువలకు “సర్దుబాటు” చేయడానికి వినియోగదారుకు తగినంత ఓపిక స్పష్టంగా లేదు.

మరొక విభాగానికి చెందిన డెవలపర్లు వారి వద్దకు వచ్చి, అటువంటి అనుకూలమైన పద్ధతిని ఉపయోగించాలని కోరుకునే వరకు పునరావృత శోధన కోసం - అంటే, మేము కొన్ని నమూనా నుండి ఒక భాగాన్ని తీసుకుంటాము, అదనపు షరతుల ద్వారా దాన్ని ఫిల్టర్ చేస్తాము, ఫలితాన్ని గీయండి, ఆపై తదుపరి భాగాన్ని (మా విషయంలో N పెంచడం ద్వారా సాధించబడుతుంది), మరియు మేము స్క్రీన్‌ను పూరించే వరకు.

సాధారణంగా, పట్టుకున్న నమూనాలో N దాదాపు 17K విలువలను చేరుకుంది, మరియు కేవలం ఒక రోజులో కనీసం 4K అటువంటి అభ్యర్థనలు "చైన్ వెంట" అమలు చేయబడ్డాయి. వాటిలో చివరిది ధైర్యంగా స్కాన్ చేయబడింది ప్రతి పునరావృతానికి 1GB మెమరీ...

మొత్తం

మూలం: www.habr.com