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

Вовед

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 >= CURDATE()
  AND f.departure < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
  AND (SELECT COUNT(*)
       FROM booking bx
       WHERE bx.flight_id = f.flight_id) > 0
ORDER BY load_factor DESC
LIMIT 100;

Од следните анализи може да се дојде до заклучок дека овој прашалник не е најоптимален поради тоа што correlated subqueries создаваат row-by-row извршување (nested loops) и повторено читање на истата табела, што е скапо кај големи datasets.

Ова прави повторување на COUNT/AVG над booking за секој flight ред.

• booking е огромна табела => ова може да стане N пати скенирање/индекс-скенирање.
• Двапати пресметуваме COUNT(*) (и уште еднаш во WHERE), што е уште полошо.