Профилирање и оптимизација на извршувањето на прашалниците

Вовед

Airportdb има голем волумен на оперативни податоци (booking е најголема табела). Во production, кога паралелно се извршуваат OLTP операции (резервации/промени) и тешки аналитички прашалници, перформансите на базата стануваат тесно грло и влијаат врз корисничко искуство и стабилност. Целта на оваа фаза е да:

• идентификуваме slow queries што најчесто се извршуваат
• да ги анализираме со execution plan
• да примениме оптимизации (индекси/рефакторинг на SQL)
• да ги споредиме перформансите пред и по оптимизацијата.

Поставување на бизнис барање:

“За утрешниот ден, за секој лет да се прикаже: авиокомпанија, рута, време, капацитет на авион, број резервирани места, load factor (% пополнетост), и просечна цена по резервација. Да се филтрираат само летови што веќе имаат барем 1 резервација и сортирај по највисок load factor.”

Прашалник 1

SELECT
  f.flight_id,
  al.airlinename,
  f.flightno,
  f.`from`,
  f.`to`,
  f.departure,
  f.arrival,
  (SELECT a.capacity
   FROM airplane a
   WHERE a.airplane_id = f.airplane_id) AS capacity,
  (SELECT COUNT(*)
   FROM booking b
   WHERE b.flight_id = f.flight_id) AS booked_seats,
  (SELECT AVG(b2.price)
   FROM booking b2
   WHERE b2.flight_id = f.flight_id) AS avg_price,
  (SELECT COUNT(*)
   FROM booking b3
   WHERE b3.flight_id = f.flight_id) /
  (SELECT a2.capacity
   FROM airplane a2
   WHERE a2.airplane_id = f.airplane_id) AS load_factor
FROM flight f
JOIN airline al ON al.airline_id = f.airline_id
WHERE f.departure >= DATE_ADD(CURDATE(), INTERVAL 1 DAY)
  AND f.departure < DATE_ADD(CURDATE(), INTERVAL 2 DAY)
  AND (SELECT COUNT(*)
       FROM booking bx
       WHERE bx.flight_id = f.flight_id) > 0
ORDER BY load_factor DESC
LIMIT 100;

-> Limit: 100 row(s)  (actual time=68039..68039 rows=100 loops=1)
     -> Sort: load_factor DESC, limit input to 100 row(s) per chunk  (actual time=68039..68039 rows=100 loops=1)
         -> Stream results  (cost=127262 rows=230643) (actual time=1.01..6782...

Од execution планот се гледа дека прашалникот извршува повеќе корелирани под-прашалници за секој лет. Поради тоа, исти пресметки над booking се повторуваат повеќепати, што создава дополнително оптоварување над најголемата табела во базата.

• COUNT(*) се пресметува повеќепати за истиот flight_id.
• AVG(price) се пресметува преку посебен под-прашалник.
• Иако дел од пристапите користат индекси, повтореното извршување за секој лет и понатаму создава непотребна работа.

Оптимизација 1

Прво издвојуваме утрешни flight_id со CTE, па агрегираме само за нив.

WITH tomorrow_flights AS (
  SELECT flight_id, airline_id, airplane_id, flightno, `from`, `to`, departure, arrival
  FROM flight
  WHERE departure >= DATE_ADD(CURDATE(), INTERVAL 1 DAY)
  AND departure < DATE_ADD(CURDATE(), INTERVAL 2 DAY)
),
booking_x AS (
  SELECT b.flight_id, COUNT(*) AS booked_seats, AVG(b.price) AS avg_price
  FROM booking b
  JOIN tomorrow_flights tf ON tf.flight_id = b.flight_id
  GROUP BY b.flight_id
)
SELECT
  tf.flight_id,
  al.airlinename,
  tf.flightno,
  tf.`from`,
  tf.`to`,
  tf.departure,
  tf.arrival,
  a.capacity,
  bx.booked_seats,
  bx.avg_price,
  bx.booked_seats / a.capacity AS load_factor
FROM tomorrow_flights tf
JOIN booking_x bx ON bx.flight_id = tf.flight_id
JOIN airline al ON al.airline_id = tf.airline_id
JOIN airplane a ON a.airplane_id = tf.airplane_id
ORDER BY load_factor DESC
LIMIT 100;

-> Limit: 100 row(s)  (actual time=42155..42155 rows=100 loops=1)
     -> Sort: load_factor DESC, limit input to 100 row(s) per chunk  (actual time=42155..42155 rows=100 loops=1)
         -> Stream results  (cost=3.98e+6 rows=0) (actual time=39609..41987 r...

Зошто е побрзо?

• Се избегнуваат повеќекратните корелирани под-прашалници.
• Со CTE најпрво се издвојуваат релевантните летови.
• COUNT(*) и AVG(price) се пресметуваат еднаш по flight_id преку GROUP BY.
• Добиените агрегирани вредности потоа се поврзуваат со останатите податоци преку JOIN.

Оптимизација 2

Идеја: еднаш да агрегираме booking по flight_id (COUNT и AVG), па потоа само join-ираме.

SELECT
  f.flight_id,
  al.airlinename,
  f.flightno,
  f.`from`,
  f.`to`,
  f.departure,
  f.arrival,
  a.capacity,
  bx.booked_seats,
  bx.avg_price,
  bx.booked_seats / a.capacity AS load_factor
FROM flight f
JOIN airline al ON al.airline_id = f.airline_id
JOIN airplane a ON a.airplane_id = f.airplane_id
JOIN (
  SELECT
    b.flight_id,
    COUNT(*) AS booked_seats,
    AVG(b.price) AS avg_price
  FROM booking b
  GROUP BY b.flight_id
) bx ON bx.flight_id = f.flight_id
WHERE f.departure >= DATE_ADD(CURDATE(), INTERVAL 1 DAY)
  AND f.departure < DATE_ADD(CURDATE(), INTERVAL 2 DAY)
  AND bx.booked_seats > 0
ORDER BY load_factor DESC
LIMIT 100;

-> Limit: 100 row(s)  (actual time=20398..20398 rows=100 loops=1)
     -> Sort: load_factor DESC, limit input to 100 row(s) per chunk  (actual time=20398..20398 rows=100 loops=1)
         -> Stream results  (cost=10.3e+9 rows=103e+9) (actual time=18155..20...

Во оваа варијанта booking најпрво се агрегира по flight_id, а добиениот резултат потоа се поврзува со flight, airplane и airline.

Во измерениот тест оваа варијанта имаше најдобро време од трите пристапи. COUNT(*) и AVG(price) се пресметуваат во една агрегирана подтабела, наместо повеќепати преку корелирани под-прашалници.

Споредба на резултатите

Прашалник	Пристап	Измерено време
Q1	Корелирани под-прашалници	68.039 s
Q2	CTE + GROUP BY + JOIN	42.155 s
Q3	Агрегирање по flight_id + JOIN	20.398 s

Дополнително, при валидација на Q1 и Q2 со фиксен датум од податочното множество, двата прашалници вратија по 100 редови, без недостасувачки редови и без разлики во добиените вредности.

Индекси

Покрај рефакторирањето на SQL, важна улога имаат и индексите што се користат при филтрирање и агрегирање.

Релевантни индекси за овој прашалник се:

• flight(departure) - за побрзо филтрирање на летовите според датум.
• booking(flight_id) - за поврзување и агрегирање на резервациите по лет.
• booking(flight_id, price) - корисен кога покрај flight_id често е потребна и пресметка врз price, како AVG(price).

Индексите помагаат при пристапот до податоците, но не можат целосно да надоместат лошо структуриран SQL. Во овој пример значително подобрување се добива со отстранување на повторените корелирани под-прашалници.

Заклучок

Со анализа на execution plan-от се утврди дека почетниот прашалник извршува повеќе исти пресметки над booking. Со преработка на SQL и групирање на податоците по flight_id се намалува повторената работа, а измереното време се намалува од 68.039 s кај почетниот прашалник, на 42.155 s кај првата и 20.398 s кај втората оптимизација.