Вовед

Општа Цел

Оваа фаза има цел да имплементира партиционирање на клучни табели во AirportDB базата на податоци за да се подобрат перформансите на системот, олесни одржувањето на податоците и овозможи поефикасно процесирање на големи волумени на податоци кои се типични за авиоиндустрија.

Зошто Партиционирање?

Специфични предизвици на AirportDB:

• Континуиран раст на податоци за летови (секојдневно се додаваат нови летови)
• Историски податоци што се акумулираат со години (weatherdata)
• Честа потреба за пристап до актуелни податоци, додека стари податоци се ретко користат
• Сезонски query-ја (пребарување на летови по датум, сезона, временски услови)

Очекувани придобивки:

• Подобрени Перформанси
• Полесно Одржување
• Скалабилност

Анализа на AirpotdDB табели

Анализа:

• Табелата airline не треба да биде партиционирана затоа што содржи мал број податоци и нема потреба од подобрување на перформанси преку партиционирање.
• Табелата airplane_type не треба да се партиционира затоа што содржи мал број статични записи и нема практична добивка од партиционирање.
• Табелата airplane не треба да биде партиционирана затоа што содржи ограничен број редови, нема колона погодна за партиционирање и не би се добила никаква подобрување во перформансите.
• Табелата airport_geo е lookup табела и треба да остане непартиционирана.
• Табелата airport_reachable има специфична, ограничена намена и мала големина. Партиционирањето би додало административен товар без вистинска корист за перформансите.
• Табелата airport не треба да биде партиционирана затоа што содржи ограничен број статични записи и нема колона погодна за поделба на податоците по партиции.
• Табелата booking е добар кандидат за партиционирање бидејќи може да содржи огромен број на редови со тек на време. Партиционирањето би помогнало за подобри перформанси и полесно управување со историски податоци — особено ако се воведе колона со датум за да се партиционира по време. Но не може да биде партиционирана во MySQL затоа што содржи foreign keys(flight_id, passenger_id). Иако има голем обем на податоци и би имало смисла логички, ограничувањата на MySQL не дозволуваат партиционирање на такви табели.
• Табелата employee не треба да биде партиционирана затоа што содржи релативно мал број записи, нема природен критериум за поделба на партиции и партиционирањето не би донело никакво подобрување на перформансите.
• Табелата flight_log има голем обем на податоци и би била идеална за партиционирање по log_date. Меѓутоа, бидејќи содржи foreign key (flight_id), MySQL не дозволува партиционирање, така што во пракса оваа табела не може да се партиционира додека постои foreign кey.
• Табелата flight не треба да се партиционира бидејќи има foreign keys кон airline или airplane, бидејќи MySQL не дозволува партиционирање со foreign keys. Ако ги нема или се отстранат, партиционирањето по departure би имало смисла за големи обеми на летови.
• Табелата flightschedule не треба да се партиционира бидејќи има мал обем на податоци, нема соодветна временска колона за логично партиционирање, и партиционирањето не би донело значителна предност при перформансите.
• Табелата passenger можеби треба да се партиционира бидејќи со зголемување на бројот на патници, партиционирањето би ја подобрило брзината на пребарување и управувањето со податоците.
• Табелата passengerdetails не треба да се партиционира бидејќи бројот на редови е мал, нема соодветна колона за ефективно партиционирање и партиционирањето би ја зголемило сложеноста без реална корист.
• Табелата weatherdata е совршен кандидат за партиционирање - има голем раст, временска природа, чести временски пребарувања и нема foreign key ограничувања.

Визуелен приказ:

• Со црвена боја се означени табелите каде што нема потреба од партиција.
• Со жолта боја се означени табелите каде што треба да има партиција но поради рестрикции од MYSQL неможе да се оствари.
• Со зелена боја се означени табелите каде што треба да има партиција.
• Со плава боја се означени табелите каде што треба да има партиција доколку бројот на записи надмине >1 милион.

Имплементација

weatherdata

Оваа табела е партиционирана по колоната log_date користејќи го range типот на партиционирање. Пример за партиционирање на оваа табела е поделбата по месеци (јануари 2024, февруари 2024, март 2024, итн.). На овој начин може лесно и брзо да се пребаруваат и анализираат временските податоци за одреден месец, без потреба од пребарување низ целата табела. Дополнително, ваквиот пристап овозможува едноставно архивирање или бришење на податоците по месеци, што ја олеснува одржливоста и ја подобрува ефикасноста на базата на податоци.

• Креирање на партициите:

ALTER TABLE `weatherdata`
PARTITION BY RANGE (TO_DAYS(log_date)) (
  PARTITION p_2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p_2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  PARTITION p_2024_03 VALUES LESS THAN (TO_DAYS('2024-04-01')),
  PARTITION p_2024_04 VALUES LESS THAN (TO_DAYS('2024-05-01')),
  PARTITION p_2024_05 VALUES LESS THAN (TO_DAYS('2024-06-01')),
  PARTITION p_2024_06 VALUES LESS THAN (TO_DAYS('2024-07-01')),
  PARTITION p_2024_07 VALUES LESS THAN (TO_DAYS('2024-08-01')),
  PARTITION p_2024_08 VALUES LESS THAN (TO_DAYS('2024-09-01')),
  PARTITION p_2024_09 VALUES LESS THAN (TO_DAYS('2024-10-01')),
  PARTITION p_2024_10 VALUES LESS THAN (TO_DAYS('2024-11-01')),
  PARTITION p_2024_11 VALUES LESS THAN (TO_DAYS('2024-12-01')),
  PARTITION p_2024_12 VALUES LESS THAN (TO_DAYS('2025-01-01')),
  PARTITION p_2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p_2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')),
  PARTITION p_2025_03 VALUES LESS THAN (TO_DAYS('2025-04-01')),
  PARTITION p_2025_04 VALUES LESS THAN (TO_DAYS('2025-05-01')),
  PARTITION p_2025_05 VALUES LESS THAN (TO_DAYS('2025-06-01')),
  PARTITION p_2025_06 VALUES LESS THAN (TO_DAYS('2025-07-01')),
  PARTITION p_2025_07 VALUES LESS THAN (TO_DAYS('2025-08-01')),
  PARTITION p_2025_08 VALUES LESS THAN (TO_DAYS('2025-09-01')),
  PARTITION p_2025_09 VALUES LESS THAN (TO_DAYS('2025-10-01')),
  PARTITION p_2025_10 VALUES LESS THAN (TO_DAYS('2025-11-01')),
  PARTITION p_2025_11 VALUES LESS THAN (TO_DAYS('2025-12-01')),
  PARTITION p_2025_12 VALUES LESS THAN (TO_DAYS('2026-01-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

• Додавање на нови месеци во иднината:

ALTER TABLE `weatherdata` 
REORGANIZE PARTITION p_future INTO (
  PARTITION p_2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p_2026_02 VALUES LESS THAN (TO_DAYS('2026-02-02')),
  PARTITION p_2026_03 VALUES LESS THAN (TO_DAYS('2026-02-03')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

• Архивирање на стари податоци (Опција: Креирање на Архивска табела):

CREATE TABLE weatherdata_archive_2024_01 LIKE weatherdata;
ALTER TABLE weatherdata_archive_2024_01 REMOVE PARTITIONING;

-- Move data from partition to archive
INSERT INTO weatherdata_archive_2024_01
SELECT * FROM weatherdata PARTITION (p_2024_01);

-- Drop partition from weatherdata
ALTER TABLE weatherdata DROP PARTITION p_2024_01;

passenger

• Партиционирана по колона passenger_id користејќи HASH.
  • Број на партиции:
    • За <1M патници: 4 партиции
    • За 1M-10M патници: 8 партиции
    • За >10M патници: 16 партиции

ALTER TABLE `passenger` DROP INDEX pass_unq;
ALTER TABLE `passenger`
PARTITION BY HASH(passenger_id)
PARTITIONS 4;

• Партиционирана по колона passenger_id користејќи RANGE.

ALTER TABLE `passenger` DROP INDEX pass_unq;
ALTER TABLE `passenger`
PARTITION BY RANGE(passenger_id) (
  PARTITION p0 VALUES LESS THAN (1000000),
  PARTITION p1 VALUES LESS THAN (2000000),
  PARTITION p2 VALUES LESS THAN (3000000),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

• За табелата passenger каде што нема временска компонента, HASH е подобар избор бидејќи ги дистрибуира патниците рамномерно низ партициите. Уникатниот индекс треба да се брише само ако MySQL го спречува партиционирањето (како во случајот со HASH или RANGE по колона која не е дел од уникатниот индекс). Ако уникатноста е критична, подобро е да се најде начин да се вклучи во партиционирањето или да се задржи контролата на апликациски слој.

• Dropping Unique Index (pass_unq)
  • MySQL бара сите колони од UNIQUE индекс да бидат вклучени во партициониот клуч
  • Ризици:
    • Можност за внесување на дупликат записи ако апликацијата не прави проверка
    • Влијание врз постоечки кодот кој се потпира на оваа constraint

Тестирање и Перформанси

Објаснување:

• Овој ред од EXPLAIN покажува дека MySQL ќе чита редови од партицијата p_2005_01, ќе користи RANGE достап преку PRIMARY KEY, и потоа ќе примени WHERE филтер за да го заврши SELECT прашањето.

Објаснување:

• Време на извршување: 0.018 секунди
• Овој тест се однесува само на еден месец (јуни 2005) и еден station.
• Partition pruning се користи: MySQL ја користи само партицијата p_2005_06 наместо целата табела.
• Резултатот е многу брз затоа што се читаат малку редови од специфична партиција.

Објаснување:

• Време на извршување: 0.218 секунди
• Овој тест покрива цела година (јануари – декември 2005).
• MySQL мора да чита од сите 12 партиции за 2005, така што partition pruning се користи, но не само една партиција, туку 12.
• Поголем број редови значи поголемо време за извршување.

Предности и Недостатоци

Предности

• Подобрени Перформанси
• Полесно Одржување
• Подобрено Управување со Податоци
• Ефикасност на Диск

Недостатоци

• Комплексност на Управување
• Ограничувања на Primary Key
• Ограничен Број на Партиции (MySQL има лимит од 8192 партиции по табела)
• Ограничувања со Foreign Keys

Заклучок и Препораки

Кога да се користи партиционирање?

Идеални Сценарија:

• Табели со >5-10 милиони реда
• Time-series податоци (логови, сензори, метрика)
• Редовно бришење/архивирање на стари податоци
• Query-ја што секогаш филтрираат по датум

Не Се Препорачува:

• Мали табели (<1 милион реда)
• OLTP системи со многу INSERT/UPDATE операции
• Кога немате ресурси за одржување

Заклучок

• Партиционирањето на табели е моќна техника за оптимизација на перформанси кај големи time-series бази, но доаѓа со значајни ограничувања особено во MySQL забраната за foreign keys на партиционирани табели. За airportdb базата, единствено weatherdata табелата е идеален кандидат за месечно RANGE партиционирање бидејќи содржи time-series податоци, нема foreign key зависности, и PRIMARY KEY веќе вклучува log_date, и passenger табелата за HASH партиционирање. Останатите табели како flight, flight_log и booking не се погодни поради критичните referential integrity constraints кои се неопходни за одржување на интегритетот на податоците во авионската резервациска система.