நாடு முழுவதும் உள்ள விற்பனை அலுவலகங்களில் இருந்து ஆயிரக்கணக்கான மேலாளர்கள் சாதனை படைத்துள்ளனர்
எனவே, மிகவும் ஏற்றப்பட்ட தரவுத்தளங்களில் ஒன்றில் "கனமான" வினவல்களை மீண்டும் பகுப்பாய்வு செய்வதில் ஆச்சரியமில்லை - நம்முடையது
மேலும், மேலதிக விசாரணையில் ஒரு சுவாரஸ்யமான உதாரணம் தெரியவந்தது முதலில் தேர்வுமுறை மற்றும் பின்னர் செயல்திறன் சிதைவு பல குழுக்களால் அதன் தொடர்ச்சியான சுத்திகரிப்பு கோரிக்கை, அவை ஒவ்வொன்றும் சிறந்த நோக்கத்துடன் மட்டுமே செயல்பட்டன.
0: பயனர் என்ன விரும்புகிறார்?
[கேடிபிவி
பெயர் மூலம் "விரைவான" தேடலைப் பற்றிப் பேசும்போது பயனர் பொதுவாக என்ன அர்த்தம்? இது போன்ற துணைச்சரத்திற்கான "நேர்மையான" தேடலாக இது ஒருபோதும் மாறாது ... LIKE '%роза%'
- ஏனெனில் முடிவு மட்டும் அடங்கும் 'Розалия'
и 'Магазин Роза'
ஆனால் 'Гроза'
மற்றும் கூட 'Дом Деда Мороза'
.
நீங்கள் அவருக்கு வழங்குவீர்கள் என்று பயனர் அன்றாட மட்டத்தில் கருதுகிறார் வார்த்தையின் தொடக்கத்தில் தேடுங்கள் தலைப்பில் மற்றும் அதை மிகவும் பொருத்தமானதாக ஆக்குங்கள் தொடங்குகிறது உள்ளிட்ட. நீங்கள் அதை செய்வீர்கள் கிட்டத்தட்ட உடனடியாக - நேரியல் உள்ளீட்டிற்கு.
1: பணியை வரம்பிடவும்
மேலும், ஒரு நபர் குறிப்பாக நுழைய மாட்டார் 'роз магаз'
, எனவே நீங்கள் ஒவ்வொரு வார்த்தையையும் முன்னொட்டு மூலம் தேட வேண்டும். இல்லை, முந்தைய வார்த்தைகளை வேண்டுமென்றே "குறைக்காமல்" விட, கடைசி வார்த்தைக்கான விரைவான குறிப்பிற்கு பயனர் பதிலளிப்பது மிகவும் எளிதானது - எந்த தேடுபொறியும் இதை எவ்வாறு கையாளுகிறது என்பதைப் பாருங்கள்.
பொதுவாக, சரியாக பிரச்சனைக்கான தேவைகளை உருவாக்குவது பாதிக்கும் மேலான தீர்வாகும். சில நேரங்களில் கவனமாக பயன்படுத்த வழக்கு பகுப்பாய்வு
ஒரு சுருக்க டெவலப்பர் என்ன செய்கிறார்?
1.0: வெளிப்புற தேடுபொறி
ஓ, தேடுவது கடினம், நான் எதையும் செய்ய விரும்பவில்லை - அதை டெவொப்ஸுக்குக் கொடுப்போம்! தரவுத்தளத்திற்கு வெளியே ஒரு தேடுபொறியை பயன்படுத்த அவர்களை அனுமதிக்கவும்: Sphinx, ElasticSearch,...
ஒரு வேலை விருப்பம், ஒத்திசைவு மற்றும் மாற்றங்களின் வேகம் ஆகியவற்றின் அடிப்படையில் உழைப்பு மிகுந்ததாக இருந்தாலும். ஆனால் எங்கள் விஷயத்தில் இல்லை, ஏனெனில் ஒவ்வொரு வாடிக்கையாளருக்கும் அவரது கணக்குத் தரவின் கட்டமைப்பிற்குள் மட்டுமே தேடல் மேற்கொள்ளப்படுகிறது. மேலும் தரவு மிகவும் அதிக மாறுபாட்டைக் கொண்டுள்ளது - மேலும் மேலாளர் இப்போது அட்டையில் நுழைந்திருந்தால் 'Магазин Роза'
, 5-10 வினாடிகளுக்குப் பிறகு, அவர் தனது மின்னஞ்சலைக் குறிப்பிட மறந்துவிட்டதையும், அதைக் கண்டுபிடித்து சரிசெய்ய விரும்புவதையும் அவர் ஏற்கனவே நினைவில் வைத்திருக்கலாம்.
எனவே - நாம் "நேரடியாக தரவுத்தளத்தில்" தேடவும். அதிர்ஷ்டவசமாக, PostgreSQL இதை செய்ய அனுமதிக்கிறது, ஒரு விருப்பத்தை மட்டுமல்ல - நாங்கள் அவற்றைப் பார்ப்போம்.
1.1: "நேர்மையான" சப்ஸ்ட்ரிங்
நாம் "சப்ஸ்ட்ரிங்" என்ற வார்த்தையில் ஒட்டிக்கொள்கிறோம். ஆனால் சப்ஸ்ட்ரிங் மூலம் குறியீட்டு தேடலுக்கு (மற்றும் வழக்கமான வெளிப்பாடுகள் மூலம் கூட!) ஒரு சிறந்த உள்ளது
மாதிரியை எளிமைப்படுத்த பின்வரும் தட்டு எடுக்க முயற்சிப்போம்:
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;
சரி, அது... 26எம்எஸ், 31எம்பி தரவு மற்றும் 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;
இங்கே வினவல் செயல்படுத்தலின் இணையாக எங்களுக்கு சிறிது உதவியது, நேரத்தை பாதியாக குறைத்தது 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;
சிறந்த செயல்திறன் - மொத்தம் 0.05ms மற்றும் 100KB க்கும் சற்று அதிகம் படி! நாம் மட்டும் மறந்துவிட்டோம் பெயரால் வரிசைப்படுத்துங்கள்பயனர் முடிவுகளில் தொலைந்து போகாமல் இருக்க:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
ஓ, ஏதோ ஒன்று இனி அவ்வளவு அழகாக இல்லை - ஒரு குறியீட்டு உள்ளது போல் தெரிகிறது, ஆனால் வரிசையாக்கம் அதை கடந்து பறக்கிறது ... இது, நிச்சயமாக, ஏற்கனவே முந்தைய விருப்பத்தை விட பல மடங்கு பயனுள்ளதாக இருக்கும், ஆனால் ...
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;
சிறப்பானது - வரிசையாக்கம் வேலை செய்கிறது, மற்றும் வள நுகர்வு "நுண்ணியமாக" உள்ளது, "தூய" 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;
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 புலம் - துணை வினவலின் முடிவு - தானாகவே அங்கு சேர்க்கப்பட்டது. இப்போது, செயல்படுத்த DISTINCT
, தரவுத்தளம் ஏற்கனவே இயக்க வேண்டும் 10 துணை வினவல்கள் அல்ல, ஆனால் அனைத்தும் <2 * N> + 10!
2.4: எல்லாவற்றிற்கும் மேலாக ஒத்துழைப்பு!
எனவே, டெவலப்பர்கள் வாழ்ந்தார்கள் - அவர்கள் கவலைப்படவில்லை, ஏனென்றால் ஒவ்வொரு அடுத்தடுத்த “பக்கத்தையும்” பெறுவதில் நீண்டகால மந்தநிலையுடன் பதிவேட்டை குறிப்பிடத்தக்க N மதிப்புகளுக்கு “சரிசெய்ய” பயனருக்கு போதுமான பொறுமை இல்லை.
மற்றொரு துறையைச் சேர்ந்த டெவலப்பர்கள் அவர்களிடம் வந்து அத்தகைய வசதியான முறையைப் பயன்படுத்த விரும்பும் வரை மீண்டும் மீண்டும் தேடுவதற்கு - அதாவது, சில மாதிரியிலிருந்து ஒரு பகுதியை எடுத்து, கூடுதல் நிபந்தனைகளால் வடிகட்டுகிறோம், முடிவை வரைகிறோம், பின்னர் அடுத்த துண்டு (எங்கள் விஷயத்தில் N ஐ அதிகரிப்பதன் மூலம் அடையப்படுகிறது), மற்றும் பலவற்றை நாங்கள் திரையை நிரப்புகிறோம்.
பொதுவாக, பிடிபட்ட மாதிரியில் N கிட்டத்தட்ட 17K மதிப்புகளை எட்டியது, மற்றும் ஒரே நாளில் குறைந்தது 4K கோரிக்கைகள் "சங்கிலியில்" செயல்படுத்தப்பட்டன. அவர்களில் கடைசியாக தைரியமாக ஸ்கேன் செய்யப்பட்டது ஒரு மறு செய்கைக்கு 1GB நினைவகம்...
மொத்தம்
ஆதாரம்: www.habr.com