Бірнеше ай бұрын - қоғамдық PostgreSQL үшін.
Сіз оны 6000-нан астам рет пайдаландыңыз, бірақ байқамай қалуы мүмкін бір ыңғайлы мүмкіндік: құрылымдық белгілер, ол келесідей көрінеді:
Оларды тыңдаңыз, сонда сіздің сұрауларыңыз «тегіс және жібектей» болады. 🙂
Бірақ байыпты, сұрауды баяу және ресурсты қажет ететін көптеген жағдайлар типтік болып табылады және жоспардың құрылымы мен деректері арқылы танылуы мүмкін.
Бұл жағдайда әрбір жеке әзірлеуші өз тәжірибесіне сүйене отырып, оңтайландыру опциясын өздігінен іздеудің қажеті жоқ - біз оған мұнда не болып жатқанын, себебі не болуы мүмкін екенін айта аламыз және шешуге қалай жақындауға болады. Біз осылай істедік.
Бұл жағдайларды толығырақ қарастырайық - олар қалай анықталады және олар қандай ұсыныстарға әкеледі.
Тақырыпқа жақсырақ ену үшін алдымен тиісті блокты тыңдауға болады , содан кейін ғана әрбір мысалдың егжей-тегжейлі талдауына көшіңіз:
№1: индексі «төмен сұрыптау»
Қашан болады
«Колокольчик» ЖШС клиенті үшін соңғы шот-фактураны көрсетіңіз.
Қалай анықтауға болады
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
ұсынымдар
Қолданылатын индекс сұрыптау өрістерімен кеңейтіңіз.
Мысал:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Көрсеткіштен 100-ден астам жазбалар алынып тасталғанын бірден байқай аласыз, олар кейін барлық сұрыпталған, содан кейін жалғыз қалды.
Түзету:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Тіпті мұндай қарабайыр үлгіде - 8.5 есе жылдам және 33 есе аз оқу. Әрбір мән үшін неғұрлым көп «фактілер» болса, әсер соғұрлым айқын болады fk.
Мен мұндай индекс «префикс» индексі ретінде басқа сұраулар үшін бұрынғыдан нашар жұмыс істейтінін ескертемін. fk, қай жерде сұрыптау pk болған жоқ және жоқ (бұл туралы толығырақ оқуға болады ). Соның ішінде қалыпты қамтамасыз етеді айқын шетелдік кілт қолдауы осы өрісте.
№2: индекс қиылысы (NetmapAnd)
Қашан болады
«NAO Buttercup» атынан жасалған «Колокольчик» ЖШС клиенті үшін барлық келісімдерді көрсетіңіз.
Қалай анықтауға болады
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanұсынымдар
жасау құрама индекс екі түпнұсқаның өрістері бойынша немесе бар біреуін екіншісінің өрістерімен кеңейтіңіз.
Мысал:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Түзету:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Мұндағы пайда азырақ, өйткені Bitmap Heap Scan өздігінен тиімді. Бірақ бәрібір 7 есе жылдам және 2.5 есе аз оқу.
№3: индекстерді біріктіру (BimapOr)
Қашан болады
Алғашқы 20 ең ескі "бізді" немесе өңдеуге тағайындалмаған сұрауларды көрсетіңіз.
Қалай анықтауға болады
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanұсынымдар
Пайдаланыңыз ОДАУ [БАРЛЫҒЫ] OR-шарттардың блоктарының әрқайсысы үшін ішкі сұрауларды біріктіру.
Мысал:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Түзету:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Біз барлық 20 талап етілетін жазбаның бірінші блокта бірден алынғанын пайдаландық, сондықтан екіншісі «қымбат» Bitmap Heap Scan арқылы тіпті орындалмады - соңында 22 есе жылдам, 44 есе аз оқу!
Бұл оңтайландыру әдісі туралы толығырақ әңгіме нақты мысалдарды қолдану мақалалардан оқуға болады и .
Жалпыланған нұсқа бірнеше кілттерге негізделген реттелген таңдау (және const/NULL жұбы ғана емес) мақалада талқыланады .
№4: Біз қажетсіз нәрселерді көп оқимыз
Қашан болады
Әдетте, ол бұрыннан бар сұрауға «басқа сүзгіні тіркегіңіз» келгенде пайда болады.
«Ал сізде бірдей жоқ, бірақ інжу түймелері бар? «Алмас қол» фильмі
Мысалы, жоғарыдағы тапсырманы өзгерте отырып, олардың мақсатына қарамастан өңдеуге арналған алғашқы 20 ең ескі «сыни» сұрауларды көрсетіңіз.
Қалай анықтауға болады
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
ұсынымдар
Мамандандырылған [толығырақ] жасаңыз WHERE шарты бар индекс немесе индекске қосымша өрістерді қосыңыз.
Егер сүзгі күйі сіздің мақсаттарыңыз үшін «статикалық» болса - бұл кеңейтуді білдірмейді болашақта мәндер тізімі - WHERE индексін қолданған дұрыс. Әртүрлі логикалық/енум күйлері осы санатқа жақсы сәйкес келеді.
Егер сүзу шарты болса әртүрлі мағынаға ие болуы мүмкін, содан кейін BitmapЖәне жоғарыдағы жағдайдағыдай индексті осы өрістермен кеңейткен дұрыс.
Мысал:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Түзету:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Көріп отырғаныңыздай, сүзгілеу жоспардан толығымен жойылып, сұраныс болды 5 есе жылдам.
№5: сирек үстел
Қашан болады
Кестедегі жазбаларды жаңарту/жоюдың көп саны «өлі» жазбалар санының жағдайына әкелетін кезде, өзіңіздің тапсырманы өңдеу кезегін құрудың әртүрлі әрекеттері.
Қалай анықтауға болады
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
ұсынымдар
Қолмен жүйелі түрде орындаңыз ВАКУУМ [ТОЛЫҚ] немесе жеткілікті жиі жаттығуларға қол жеткізіңіз оның ішінде оның параметрлерін дәл баптау арқылы .
Көп жағдайда мұндай мәселелер талқыланғандар сияқты іскерлік логикадан қоңырау шалу кезінде сұраныс құрамының нашарлығынан туындайды .
Бірақ сіз тіпті VACUUM FULL әрқашан көмектеспейтінін түсінуіңіз керек. Мұндай жағдайлар үшін мақаладағы алгоритммен танысқан жөн .
№6: Көрсеткіштің «ортасынан» оқу
Қашан болады
Біз аздап оқыдық, бәрі индекстелді және ешкімді артық сүзгіден өткізбедік - бірақ бәрібір біз қалағанымыздан әлдеқайда көп беттерді оқыдық.
Қалай анықтауға болады
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
ұсынымдар
Пайдаланылған индекс құрылымын және сұрауда көрсетілген негізгі өрістерді мұқият қараңыз - ең алдымен индекстің бір бөлігі орнатылмаған. Сізге ұқсас индексті жасау керек болуы мүмкін, бірақ префикс өрістері жоқ немесе .
Мысал:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Тіпті индекс бойынша бәрі жақсы сияқты, бірақ бұл қандай да бір күдікті - оқылған 20 жазбаның әрқайсысы үшін біз 4 беттік деректерді, әр жазбаға 32 КБ алып тастауға тура келді - бұл батыл емес пе? Және индекс атауы tbl_fk_org_fk_cli_idx ойландыратын.
Түзету:
CREATE INDEX ON tbl(fk_cli);
Кенеттен - 10 есе жылдам, ал оқу 4 есе аз!
Көрсеткіштерді тиімсіз пайдалану жағдайларының басқа мысалдарын мақаладан көруге болады .
№7: CTE × CTE
Қашан болады
Сұраныс бойынша CTE «майлы» балл алды әртүрлі кестелерден, содан кейін олардың арасында жасауға шешім қабылдады JOIN.
Іс v12-ден төмен нұсқаларға немесе сұрауларға қатысты WITH MATERIALIZED.
Қалай анықтауға болады
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
ұсынымдар
Сұранысты мұқият талдаңыз - және ? Егер иә болса, онда hstore/json ішінде «сөздікті» қолданыңыз сипатталған үлгіге сәйкес .
№8: дискіге ауыстыру (температура жазылған)
Қашан болады
Көптеген жазбаларды бір реттік өңдеу (сұрыптау немесе бірегейлендіру) бұл үшін бөлінген жадқа сәйкес келмейді.
Қалай анықтауға болады
-> *
&& temp written > 0ұсынымдар
Егер операция қолданатын жад көлемі параметрдің көрсетілген мәнінен айтарлықтай аспаса , оны түзетуге тұрарлық. Сіз бірден барлығына арналған конфигурацияға кіре аласыз немесе өте аласыз SET [LOCAL] нақты сұрау/транзакция үшін.
Мысал:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Түзету:
SET work_mem = '128MB'; -- перед выполнением запроса
Белгілі себептерге байланысты, егер диск емес, тек жад пайдаланылса, сұрау әлдеқайда жылдам орындалады. Сонымен қатар, HDD-ден жүктеменің бір бөлігі де жойылады.
Бірақ сіз әрқашан көп және көп жадты бөле алмайтыныңызды түсінуіңіз керек - бұл барлығына жеткіліксіз болады.
№9: маңызды емес статистика
Қашан болады
Олар бірден дерекқорға көп нәрсені құйды, бірақ оны қуып шығуға уақыт болмады ANALYZE.
Қалай анықтауға болады
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10ұсынымдар
Оны орындаңыз ANALYZE.
Бұл жағдай толығырақ мақалада сипатталған .
№10: «бірдеңе дұрыс болмады»
Қашан болады
Бәсекелес сұрау арқылы құлыптауды күту болды немесе процессор/гипервизордың аппараттық ресурстары жеткіліксіз болды.
Қалай анықтауға болады
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
ұсынымдар
Сыртқы пайдаланыңыз мониторинг жүйесі блоктау немесе қалыпты емес ресурстарды тұтыну үшін сервер. Біз бұл процесті жүздеген серверлер үшін ұйымдастыру нұсқамыз туралы айттық и .
Ақпарат көзі: www.habr.com