Changes between Version 20 and Version 21 of Profiling
- Timestamp:
- 09/10/26 19:23:27 (9 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Profiling
v20 v21 7 7 * да ги анализираме со execution plan 8 8 * да примениме оптимизации (индекси/рефакторинг на SQL) 9 * и да измериме пред и потоа.9 * да ги споредиме перформансите пред и по оптимизацијата. 10 10 11 11 === Поставување на бизнис барање: … … 41 41 FROM flight f 42 42 JOIN airline al ON al.airline_id = f.airline_id 43 WHERE f.departure >= CURDATE()44 AND f.departure < DATE_ADD(CURDATE(), INTERVAL 1DAY)43 WHERE f.departure >= DATE_ADD(CURDATE(), INTERVAL 1 DAY) 44 AND f.departure < DATE_ADD(CURDATE(), INTERVAL 2 DAY) 45 45 AND (SELECT COUNT(*) 46 46 FROM booking bx … … 51 51 52 52 [[Image(F4 IMG 1.png)]] 53 [[Image(F4 IMG 2.png)]]54 53 55 54 {{{ … … 59 58 }}} 60 59 61 Од следните анализи може да се дојде до заклучок дека овој прашалник не е најоптимален поради тоа што correlated subqueries создаваат row-by-row извршување (nested loops) и повторено читање на истата табела, што е скапо кај големи datasets.60 Од execution планот се гледа дека прашалникот извршува повеќе корелирани под-прашалници за секој лет. Поради тоа, исти пресметки над booking се повторуваат повеќепати, што создава дополнително оптоварување над најголемата табела во базата. 62 61 63 Ова прави повторување на COUNT/AVG над booking за секој flight ред.64 * booking е огромна табела => ова може да стане N пати скенирање/индекс-скенирање.65 * Двапати пресметуваме COUNT(*) (и уште еднаш во WHERE), што е уште полошо.62 * COUNT(*) се пресметува повеќепати за истиот flight_id. 63 * AVG(price) се пресметува преку посебен под-прашалник. 64 * Иако дел од пристапите користат индекси, повтореното извршување за секој лет и понатаму создава непотребна работа. 66 65 67 66 === Оптимизација 1 … … 73 72 SELECT flight_id, airline_id, airplane_id, flightno, `from`, `to`, departure, arrival 74 73 FROM flight 75 WHERE departure >= CURDATE()76 AND departure < DATE_ADD(CURDATE(), INTERVAL 1DAY)74 WHERE departure >= DATE_ADD(CURDATE(), INTERVAL 1 DAY) 75 AND departure < DATE_ADD(CURDATE(), INTERVAL 2 DAY) 77 76 ), 78 77 booking_x AS ( … … 108 107 }}} 109 108 110 Зошто е многупобрзо?109 Зошто е побрзо? 111 110 112 * Избегнува повторувачки subquery-ја 113 * Користи CTE и JOIN наместо subqueries - го пресметува booked_seats и avg_price само еднаш преку GROUP BY, наместо за секој ред посебно. 114 * Филтрира порано - CTE tomorrow_flights ги филтрира летовите на почеток, па работи само со релевантни податоци. 115 * Помалку scan-ови на табелите - ги скенира booking & airplane само по еднаш. 116 * MySQL ги материјализира CTE-ата по default што значи дека резултатите се чуваат привремено и не се пресметуваат повторно. 111 * Се избегнуваат повеќекратните корелирани под-прашалници. 112 * Со CTE најпрво се издвојуваат релевантните летови. 113 * COUNT(*) и AVG(price) се пресметуваат еднаш по flight_id преку GROUP BY. 114 * Добиените агрегирани вредности потоа се поврзуваат со останатите податоци преку JOIN. 117 115 118 === Оптимизација 2 (Уште пооптимизирано)116 === Оптимизација 2 119 117 120 118 Идеја: еднаш да агрегираме booking по flight_id (COUNT и AVG), па потоа само join-ираме. … … 144 142 GROUP BY b.flight_id 145 143 ) bx ON bx.flight_id = f.flight_id 146 WHERE f.departure >= CURDATE()147 AND f.departure < DATE_ADD(CURDATE(), INTERVAL 1DAY)144 WHERE f.departure >= DATE_ADD(CURDATE(), INTERVAL 1 DAY) 145 AND f.departure < DATE_ADD(CURDATE(), INTERVAL 2 DAY) 148 146 AND bx.booked_seats > 0 149 147 ORDER BY load_factor DESC … … 159 157 }}} 160 158 161 Зошто е многу побрзо? 162 * booking се чита/групира еднаш, не повеќе пати по ред. 163 * Планот најчесто станува: 164 * booking -> aggregate by flight_id → мал резултат → join со flight, airplane, airline. 165 * Се намалува I/O и број на обработени редови. 159 Во оваа варијанта booking најпрво се агрегира по flight_id, а добиениот резултат потоа се поврзува со flight, airplane и airline. 166 160 161 Во измерениот тест оваа варијанта имаше најдобро време од трите пристапи. COUNT(*) и AVG(price) се пресметуваат во една агрегирана подтабела, наместо повеќепати преку корелирани под-прашалници. 162 163 164 === Споредба на резултатите 165 166 || Прашалник || Пристап || Измерено време || 167 || Q1 || Корелирани под-прашалници || 68.039 s || 168 || Q2 || CTE + GROUP BY + JOIN || 42.155 s || 169 || Q3 || Агрегирање по flight_id + JOIN || 20.398 s || 170 171 Дополнително, при валидација на Q1 и Q2 со фиксен датум од податочното множество, двата прашалници вратија по 100 редови, без недостасувачки редови и без разлики во добиените вредности. 167 172 168 173 === Индекси 169 174 170 Индекси што исто така помагаат: 175 Покрај рефакторирањето на SQL, важна улога имаат и индексите што се користат при филтрирање и агрегирање. 171 176 172 {{{ 173 ALTER TABLE flight ADD INDEX ix_flight_departure (departure); - за да се филтрираат утрешните летови брзо 177 Релевантни индекси за овој прашалник се: 178 * flight(departure) - за побрзо филтрирање на летовите според датум. 179 * booking(flight_id) - за поврзување и агрегирање на резервациите по лет. 180 * booking(flight_id, price) - корисен кога покрај flight_id често е потребна и пресметка врз price, како AVG(price). 174 181 175 ALTER TABLE booking ADD INDEX ix_booking_flight (flight_id); - за агрегирање по flight_id во booking 182 Индексите помагаат при пристапот до податоците, но не можат целосно да надоместат лошо структуриран SQL. Во овој пример значително подобрување се добива со отстранување на повторените корелирани под-прашалници. 176 183 177 ALTER TABLE booking ADD INDEX ix_booking_flight_price (flight_id, price); - ако бизнис барањата често налагаат пресметување на цената во просек или некои операции. 184 === Заклучок 178 185 179 }}} 186 Со анализа на execution plan-от се утврди дека почетниот прашалник извршува повеќе исти пресметки над booking. Со преработка на SQL и групирање на податоците по flight_id се намалува повторената работа, а измереното време се намалува од 68.039 s кај почетниот прашалник, на 42.155 s кај првата и 20.398 s кај втората оптимизација.
