ProHoster > Блог > басқарма > PostgreSQL антипаттерндері: тізілімде шарлау

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 жазбадан аз болса, ол соңғы болып табылады.

Немесе сол сурет:
PostgreSQL антипаттерндері: тізілімде шарлау

Өйткені қазір бізде үлгіде нақты «басталуы» жоқ, содан кейін бізге бұл сұранысты қарама-қарсы бағытта «кеңейтуге» және «анықтамалық нүктеден» екі бағытта да - төмен және жоғары деректер блоктарын динамикалық жүктеуді жүзеге асыруға ештеңе кедергі болмайды.

Ескерту:

  1. Иә, бұл жағдайда біз индекске екі рет қол жеткіземіз, бірақ бәрі «таза индекс бойынша». Сондықтан ішкі сұрау тек нәтиже береді тек бір қосымша индексті сканерлеуге.
  2. Бұл әдісті сізде құндылықтар болған кезде ғана қолдануға болатыны анық ts тек кездейсоқ кесіп өтуге болады, және олардың саны көп емес. Егер сіздің әдеттегі жағдайыңыз «00:00:00.000-дағы миллион жазба» болса, мұны істемеуіңіз керек. Айтайын дегенім, мұндай жағдайға жол бермеу керек. Бірақ бұл орын алса, кеңейтілген индексі бар опцияны пайдаланыңыз.

Ақпарат көзі: www.habr.com

пікір қалдыру