Смяна на плана за изпълнение в MySQL 8.0

Преди около 6 седмици в работата трябваше да напиша нова заявка представляваща доклад, защото страницата на едно приложение се зареждаше бавно (около 25 секунди). Оказа се, че име проблем с приложението, защото то първо изпълняваше сравнително бавна заявка (отнемаща около 6 секунди) и тогава сравнително бърза заявка за всеки ред върнат от първата заявка. Втората заявка отнемаше между 0.3 и 0.5 секунди за изпълнение, но се изпълваше около 60 пъти, така че закъснението се натрупваше и обясни забавянето на страницата.

Реших да предоставя една единствена заявка създаваща целия резултат, така че трябваше да съединя 6 таблица. Една таблица (msg_progs) имаше само 13 реда, други две имаха само 64 реда (bldenv и prjprocstat), а друга само около 100 реда (p) тези четирите нямаха тенденция да растат много повече. Имаше една таблица с няколко десетки хиляди реда (m) и друга с няколко стотин хиляди реда (mtp), които със сигурност щяха да растат всеки ден. За да подобря избирателността във втората по големина таблица (m) създадох съставен индекс по три колони използвани от заявката, но проблема беше, че все още се избираха повечето от данните в таблицата. Така приключих с заявка изпълняваща се за 6-7 секунди, което беше доста по-приемливо от първоначалните 25 секунди. Не мога да разкрия всички подробности, но направих снимка на плана за изпълнение и смятам, че няма проблем да го публикувам.

Забележка: MySQL Workbench показва страхотно планове за изпълнение графично (забележете линиите свърващи различните операции - колкото по-голям е броя на редовете толкова по-дебела е линията). Текстовите планове за изпълнение са вече минало, нали?

Това беше до преди 3 седмици когато надградихме до MySQL 8.0.13 (от 5.7.20). Същата заявка и в същата база започна да се изпълнява по 24 секунди. Когато проверих плана за изпълнение той се беше променил (виж отдолу).

Мисля, че можете лесно да забележите, че реда на съединяване се е променил. Преди втората по големина таблица се взимаше в предвид в първия вложен цикъл, след това най-голямата таблица и накрая по-малките таблици. Сега е обратното, защото оптимизатора първо взема в предвид по-малките таблици и само тогава по-големите. Можех да използвам подсказка, за да оправя реда на съединяване (напр. JOIN_ORDER), но забелязах, че ако пресъздам съставния индекс по само две от колоните (които всъщност дават по-добра избирателност) оптимизатора избира различен план, но заявката все още се изпълвана за около 14 секунди. Накрая реших да премахна една от таблиците в съединението (най-малката msg_progs само с 13 реда), което накара оптимизатора да избере друг план за изпълнение (тук отдолу) и заявката вече се изпълнява за около 4 секунди.

Не съм сигурен от къде идва тази разлика освен от сървъра, но не мога да обвинявам без доказателство (напр. препратка към наръчника, която обяснява разликата), което за съжаление нямах време да търся. Ако знаете причината за смяната на плана за изпълнение в MySQL 8.0 моля уведомете ме.

Ситуацията ми напомни за базата на Oracle където след всяка главна версия имахме заявки с променен план за изпълнение и трябваше да използваме подсказки или други номера, за да ги забързаме отново. Изглежда да няма разлика при MySQL днес.
Обновяване 2018-12-17: Направих прибързано заключение по-горе, защото промяната на плана се случи (или поне беше забелязана) след надграждането на сървъра. Обаче, както се оказа истинската причина беше броя на редовете в таблиците m и mtp заради който сървъра избираше различен план на изпълнение. Открихме това експериментирайки с различни по обем набори с данни.