Бир нече ай мурун - коомдук 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: индекс кесилиши (BimapAnd)
Качан пайда болот
"НАО 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сунуштар
Использовать СОЮЗ [БАРДЫК] шарттардын ЖЕ-блокторунун ар бири үчүн субсуроолорду айкалыштыруу.
мисалы:
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
Качан пайда болот
Сураныч боюнча "майлуу" КТЭ деген упай алган ар кандай столдордон, анан алардын ортосунда эмне кылууну чечишти 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: "бир нерсе туура эмес болуп кетти"
Качан пайда болот
Атаандаш өтүнүч тарабынан коюлган кулпуну күтүү болду же CPU/гипервизордун аппараттык ресурстары жетишсиз.
Кантип аныктоого болот
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
сунуштар
Тышкы колдонуу мониторинг системасы бөгөттөө же анормалдуу ресурстарды керектөө үчүн сервер. Биз буга чейин жүздөгөн серверлер үчүн бул процессти уюштуруунун версиясы жөнүндө айтканбыз и .
Source: www.habr.com