Для тех, кто решил обновится с 5.6 на 5.7 ждут “сюрпризы” в виде переработанного оптимизатора запросов. Что такое оптимизатор? Это “помощник”, который перед каждым выполнением запросов ищет путь “наилучшего” выполнения запроса. Все есть больше 10-ка различных параметров оптимизатора, которые можно включать/выключать, тем самым меняя логику исполнения запросов. Подробнее о них можно прочесть здесь.
После очередного обновления БД с 5.6 на 5.7 столкнулся с тем, что запросы на 5.7 выполняются в 100 раз дольше. Дальше начинается самое интересное: поиск и решение проблемы. В первую очередь проверили индексы на базах (была реплика базы с 5.6), запустили force upgrade на 5.7 ещё раз, проблем не выявили. Просмотр explain’ов тоже ни к чему не привёл (они разные, но везде используются индексы). Был вариант перестроить заново индексы, вдруг побились после апдейта, но база большая и решили оставить на самый крайний случай.
После очередного запуска запроса, заглянули в processlist и увидели его в статусе optimizing и это натолкнуло нас сравнить флаги оптимизации на 5.6 и 5.7. И Bingo! В 5.7 добавилось часть флагов, в частности для версии 5.7.12 были новыми такие:
duplicateweedout, condition_fanout_filter, derived_merge
Все они были выставлены в on по умолчанию. Методом тыка виновником оказался derived_merge, который включает хитрую оптимизацию для derived таблиц (то есть запросов с подзапросами – select … from … (select … from…)). И действительно, в нашем запросы было много подзапросов. Список появления их от версии к версии.
Оставлю без перевода, что бы не терялся смысл:
The derived_merge flag controls whether the optimizer attempts to merge derived tables and view references into the outer query block, assuming that no other rule prevents merging. By default, the flag is on to enable merging. Setting the flag to off prevents merging and avoids the error just described. (Other workarounds include using SELECT DISTINCT or LIMIT in the subquery, although these are not as explicit in their effect on materialization.) If an ER_UPDATE_TABLE_USED error occurs for a view reference that uses an expression equivalent to the subquery, adding ALGORITHM=TEMPTABLE to the view definition prevents merging and takes precedence over the current derived_merge value.
Так же советую прочесть достаточно полный обзор про derive таблицы и оптимизатор можно прочесть здесь.
А здесь достаточно красиво рассказано об дефолтных отличиях 5.6 и 5.7
