ProHoster > Blog > Идораи > Antipatterns PostgreSQL: Паймоиш дар Феҳрист

Antipatterns PostgreSQL: Паймоиш дар Феҳрист

Имрӯз дар SQL ҳолатҳои мураккаб ва алгоритмҳои мураккаб вуҷуд нахоҳад дошт. Ҳама чиз хеле содда хоҳад буд, дар сатҳи капитан Обвиус - биёед ин корро кунем дидани феҳристи рӯйдодҳо аз рӯи вақт ҷудо карда шудааст.

Ин аст, ки дар базаи маълумот нишона мавҷуд аст events, ва вай майдон дорад ts - маҳз вақте, ки мо мехоҳем ин сабтҳоро бо тартиб нишон диҳем:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Равшан аст, ки мо дар он ҷо даҳҳо сабт нахоҳем дошт, аз ин рӯ ба мо ягон шакл лозим аст паймоиши саҳифа.

#0. "Ман погромисти модарам ҳастам"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Ин қариб як шӯхӣ нест - он кам аст, аммо дар ваҳшӣ пайдо мешавад. Баъзан, пас аз кор бо ORM, гузаштан ба кори "мустақим" бо SQL душвор буда метавонад.

Аммо биёед ба мушкилоти бештар маъмул ва камтар маълум гузарем.

#1. ОФСЕТ

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

Рақами 26 аз куҷо пайдо шуд? Ин шумораи тахминии сабтҳо барои пур кардани як экран аст. Аниқтараш, 25 сабти намоишшуда ва иловаи 1 нишон медиҳад, ки ҳадди аққал чизи дигаре дар намуна вуҷуд дорад ва ҳаракат кардан маъно дорад.

Албатта, ин арзишро дар бадани дархост "дӯхта" кардан мумкин нест, балки тавассути параметр гузаронида мешавад. Аммо дар ин ҳолат, нақшаи PostgreSQL наметавонад ба донише такя кунад, ки сабтҳо бояд нисбатан кам бошанд - ва ба осонӣ нақшаи бесамарро интихоб мекунад.

Ва дар ҳоле, ки дар интерфейси барнома, дидани реестр ҳамчун гузариш байни "саҳифаҳои" визуалӣ амалӣ карда мешавад, ҳеҷ кас дар муддати тӯлонӣ ягон чизи шубҳанокро пайхас намекунад. Маҳз то лаҳзае, ки дар мубориза барои роҳат, UI/UX қарор мекунад, ки интерфейсро ба "ҳаракати беохир" дубора созад - яъне ҳама сабтҳои сабти ном дар як рӯйхате тартиб дода мешаванд, ки корбар метавонад ба боло ва поён ҳаракат кунад.

Ҳамин тавр, ҳангоми санҷиши навбатӣ, шумо дастгир мешавед такрори сабтҳо дар реестр. Чаро, зеро ҷадвал индекси муқаррарӣ дорад (ts), дархости шумо ба кадоме такя мекунад?

Маҳз аз он сабаб, ки шумо инро ба назар нагирифтед ts калиди беназир нест дар ин ҷадвал. Дар асл, ва арзишҳои он беназир нестанд, мисли ҳама гуна "вақт" дар шароити воқеӣ - аз ин рӯ, як сабт дар ду дархости ҳамсоя ба осонӣ аз саҳифа ба саҳифа бо сабаби тартиби дигари ниҳоӣ дар доираи мураттабсозии як арзиши калидӣ "ҷаҳида" мешавад.

Дарвоқеъ, дар ин ҷо як мушкили дуюм низ пинҳон аст, ки мушоҳида кардан душвортар аст - баъзе сабтҳо намоиш дода намешаванд умуман! Охир, сабтхои «дубликат» чои каси дигарро гирифтанд. Шарҳи муфассалро бо тасвирҳои зебо пайдо кардан мумкин аст ин ҷо бихонед.

Васеъ кардани индекс

Таҳиягари маккор дарк мекунад, ки калиди индекс бояд беназир бошад ва роҳи осонтарини васеъ кардани он бо майдони баръало беназир аст, ки PK барои он комил аст:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

Ва дархост мутатсия мешавад:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. Гузариш ба "курсорҳо"

Пас аз чанде, як DBA ба назди шумо меояд ва аз дархостҳои шумо "қаноатманд" аст онҳо серверро мисли ҷаҳаннам бо қоидаҳои OFFSET худ бор мекунанд, ва умуман, вақти гузаштан ба он расидааст паймоиш аз арзиши охирин нишон дода шудааст. Дархости шумо боз мураттаб мешавад:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Як нафаси сабук кашидӣ, то омад...

#3. Индексҳои тозакунӣ

Зеро рӯзе DBA шумо хондааст мақола дар бораи дарёфти нишондиҳандаҳои бесамар ва дарк намуд Тамғаи вақт "на охирин" хуб нест. Ва ман боз назди шумо омадам - ​​ҳоло бо он фикр, ки ин нишондиҳанда бояд ба ҳар ҳол баргардад (ts DESC).

Аммо бо мушкилоти ибтидоии "ҷаҳидан" сабтҳо дар байни саҳифаҳо чӣ кор кардан лозим аст?.. Ва ҳама чиз оддӣ аст - шумо бояд блокҳоро бо шумораи номуайяни сабтҳо интихоб кунед!

Умуман, кӣ моро манъ мекунад, ки на «дуруст 26», балки «на камтар аз 26»-ро бихонем? Масалан, ба тавре ки дар блоки оянда вуҷуд доранд сабтҳо бо маъноҳои баръало гуногун ts - пас дар "ҷаҳидан" сабтҳо дар байни блокҳо мушкилот вуҷуд надорад!

Ин аст, ки чӣ тавр ба ин ноил шудан мумкин аст:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Дар ин ҷо чӣ гап?

  1. Мо 25 сабтро "поён" қадам мезанем ва арзиши "сарҳад" -ро мегирем ts.
  2. Агар дар он ҷо ҳеҷ чиз мавҷуд набошад, пас арзиши NULL -ро бо иваз кунед -infinity.
  3. Мо тамоми сегменти арзишҳоро байни арзиши гирифташуда хориҷ мекунем ts ва параметри $1 аз интерфейс гузашт (қимати қаблии "охирин" додашуда).
  4. Агар блок бо камтар аз 26 сабт баргардонида шавад, он охирин аст.

Ё ҳамон сурат:
Antipatterns PostgreSQL: Паймоиш дар Феҳрист

Зеро акнун мо дорем намуна ягон "оғоз"-и мушаххас надорад, пас ҳеҷ чиз ба мо халал намерасонад, ки ин дархостро ба самти муқобил "васеъ кунем" ва боркунии динамикии блокҳои додаҳоро аз "нуқтаи истинод" дар ҳар ду самт - ҳам поён ва ҳам боло амалӣ кунем.

Эзоҳ:

  1. Бале, дар ин ҳолат мо ду маротиба ба индекс дастрасӣ пайдо мекунем, аммо ҳама чиз «соф аз рӯи индекс» аст. Аз ин рӯ, зерпурсиш танҳо натиҷа медиҳад ба як скан танҳо Индекси иловагӣ.
  2. Комилан равшан аст, ки ин техникаро танҳо дар сурати доштани арзишҳо истифода бурдан мумкин аст ts метавонад танҳо тасодуфан убур, ва бисьёр нестанд. Агар ҳолати маъмулии шумо "як миллион сабт дар 00:00:00.000" бошад, шумо набояд ин корро кунед. Ман дар назар дорам, ки шумо набояд ба чунин ҳолат иҷозат диҳед. Аммо агар ин рӯй диҳад, интихобро бо индекси васеъ истифода баред.

Манбаъ: will.com

Илова Эзоҳ