запрос очень медленный после перехода на MySQL 5.7

У меня есть база данных MySQL с таблицами InnoDB, суммирующими более 10 десять ГБ данных, которые я хочу перенести из MySQL 5.5 в MySQL 5.7. И у меня есть запрос, который выглядит примерно так:

SELECT dates.date, count(mySub2.myColumn1), sum(mySub2.myColumn2)
FROM (
    SELECT date
    FROM dates -- just a table containing all possible dates next 5 years
    WHERE date BETWEEN '2016-06-01' AND '2016-09-03'
) AS dates
LEFT JOIN (
    SELECT o.id, time_start, time_end
    FROM order AS o
    INNER JOIN order_items AS oi on oi.order_id = o.id
    WHERE time_start BETWEEN '2016-06-01' AND '2016-09-03'
) AS mySub1 ON dates.date >= mySub1.time_start AND dates.date < mySub1.time_end
LEFT JOIN (
    SELECT o.id, time_start, time_end
    FROM order AS o
    INNER JOIN order_items AS oi on oi.order_id = o.id
    WHERE o.shop_id = 50 AND time_start BETWEEN '2016-06-01' AND '2016-09-03'
) AS mySub2 ON dates.date >= mySub2.time_start AND dates.date < mySub2.time_end
GROUP BY dates.date;

Моя проблема в том, что этот запрос выполняется быстро в MySQL 5.5, но чрезвычайно медленно в MySQL 5.7.

В MySQL 5.5 сначала требуется более 1 секунды и <0,001 секунды при каждом повторяющемся выполнении без перезапуска MySQL.
В MySQL 5.7 сначала требуется более 11,5 секунд и 1,4 секунды при каждом повторяющемся выполнении без перезапуска MySQL.
И чем больше левых соединений я добавляю к запросу, тем медленнее становится запрос в MySQL 5.7.

Оба экземпляра теперь работают на одной машине, на одном жестком диске и с одинаковыми настройками my.ini. Так что это не аппаратное обеспечение.
Планы выполнения действительно отличаются, хотя, и я не знаю, что из этого сделать.

Это EXPLAIN EXTENDED на MySQL 5.5:

| id | select_type | table      | type  | possible_keys | key         | key_len | ref       | rows  | filtered | extra                           |
|----|-------------|------------|-------|---------------|-------------|---------|-----------|-------|----------|---------------------------------|
| 1  | PRIMARY     | dates      | ALL   |               |             |         |           | 95    | 100.00   | Using temporary; Using filesort |
| 1  | PRIMARY     | <derived2> | ALL   |               |             |         |           | 281   | 100.00   | ''                              |
| 1  | PRIMARY     | <derived3> | ALL   |               |             |         |           | 100   | 100.00   | ''                              |
| 3  | DERIVED     | o          | ref   | xxxxxx        | shop_id_fk  | 4       | ''        | 1736  | 100.00   | ''                              |
| 3  | DERIVED     | oc         | ref   | xxxxx         | order_id_fk | 4       | myDb.o.id | 1     | 100.00   | Using index                     |
| 2  | DERIVED     | o          | range | xxxx          | date_start  | 3       |           | 17938 | 100.00   | Using where                     |
| 2  | DERIVED     | oc         | ref   | xxx           | order_id_fk | 4       | myDb.o.id | 1     | 100.00   | Using where                     |

Это EXPLAIN EXTENDED на MySQL 5.7:

| id | select_type | table | type   | possible_keys | key         | key_len | ref              | rows | filtered | extra          |
|----|-------------|-------|--------|---------------|-------------|---------|------------------|------|----------|----------------|
| 1  | SIMPLE      | dates | ALL    |               |             |         |                  | 95   | 100.00   | Using filesort |
| 1  | SIMPLE      | oi    | ref    | xxxxxx        | order_id_fk | 4       | const            | 228  | 100.00   |                |
| 1  | SIMPLE      | o     | eq_ref | xxxxx         | PRIMARY     | 4       | myDb.oi.order_id | 1    | 100.00   | Using where    |
| 1  | SIMPLE      | o     | ref    | xxxx          | shop_id_fk  | 4       | const            | 65   | 100.00   | Using where    |
| 1  | SIMPLE      | oi    | ref    | xxx           | order_id_fk | 4       | myDb.o.id        | 1    | 100.00   | Using where    |

Я хочу понять, почему MySQL обрабатывает один и тот же запрос так сильно по-разному, и как я могу настроить MySQL 5.7 для ускорения?
Я не ищу помощи в переписывании запроса, чтобы он был быстрее, потому что это то, что я уже делаю самостоятельно.

 wchiquito09 июн. 2016 г., 22:33
Поведение оптимизатора может варьироваться в зависимости от версии, проверьте переменнуюoptimizer_switch на каждом сервере5,5 а также5,7.
 Solarflare10 июн. 2016 г., 00:36
В вашем первом подзапросеmySub1, попробуй использовать...FROM order AS o FORCE INDEX (date_start) INNER JOIN ... или же...FROM order AS o FORCE INDEX (date_start) INNER JOIN order_items AS oi on time_start BETWEEN '2016-06-01' AND '2016-09-03' and oi.order_id = o.id ..., Если это не поможет, не могли бы вы добавить объяснение для второго?
 wchiquito10 июн. 2016 г., 10:38
Не волнуйтесь, действительно важно, чтобы их цель была достигнута. Благодарю.
 oysteing27 июн. 2016 г., 09:25
Для меня, основываясь на числах, приведенных EXPLAIN, кажется, что план 5.7 должен быть лучше, чем план 5.5. Было бы интересно сравнить переменные состояния обработчика для двух версий. Если вы выполните FLUSH STATUS перед выполнением запроса и SHOW STATUS LIKE 'handler_read%' после запроса, вы увидите фактическое количество строк, к которым обратились два запроса.
 Uueerdo09 июн. 2016 г., 21:14
Я бы посмотрел, для чего эти строки.
 Uueerdo09 июн. 2016 г., 21:01
Тогда я понятия не имею. Я предполагаю, что будут сделаны оптимизации для более распространенных типов запросов, отрицательно влияющих на этот конкретный (и особенный) запрос.
 nl-x09 июн. 2016 г., 21:04
Могут ли планы запросов быть не связанными, и MySQL 5.7 просто отключает некоторые функции по умолчанию, такие как некоторая буферизация ключа?
 Rick James24 июн. 2016 г., 16:43
Еслиtime_start вtime_end может охватывать несколько дней, затемcount(mySub2.myColumn1) будет считать каждый «заказ» на каждый день. Ты этого хотел? Или вы хотели посчитать каждый «заказ» только один раз?
 Rick James24 июн. 2016 г., 16:45
< 0.001 seconds подразумевает, что кэш запросов действовал.
 nl-x09 июн. 2016 г., 20:58
@ Ууэрдо да, именно так. Сначала это было на разных машинах. Но когда я столкнулся с этим, я установил MySQL 5.5 и MySQL 5.7 на свой ноутбук и дважды импортировал один и тот же дамп. И я взял тот же my.ini и внес в него минимум изменений, чтобы я мог запустить два экземпляра одновременно. Так что все одинаково. И только тогда я запустил один и тот же запрос к ним.
 Uueerdo09 июн. 2016 г., 20:57
Просто чтобы убедиться ... (1) запросы точно такие же? (2) таблицы, включая индексы, абсолютно одинаковы?
 nl-x10 июн. 2016 г., 09:38
@wchiquito Пожалуйста, оставьте свой комментарий в качестве ответа, чтобы я мог принять его.set optimizer_switch='derived_merge=off'; исправляет мою проблему Это новый флаг, которого не было в MySQL 5.5.
 Uueerdo09 июн. 2016 г., 21:08
Вы скопировали INI-файл старого сервера или прошли через настройки соответствия? Возможно, более новая версия предлагает дополнительные опции, которые могут по умолчанию вести к нежелательному поведению, если не указывать, когда INI-файл перезаписывается; или могут иметь новые параметры, которые должны быть отключены, чтобы вести себя как предыдущие версии. Если бы 5.7 «отключал некоторые функции», я бы ожидал, что эти настройки появятся в INI-файле.
 nl-x09 июн. 2016 г., 21:12
@Uueerdo Я на самом деле взял ini с нового сервера и скопировал его на старый, вычеркнув строки, несовместимые со старым. Так что этого не должно быть.

Ответы на вопрос(2)

Решение Вопроса

optimizer_switch, Здесь я обнаружил, что переключательderived_merge можно отключить, чтобы исправить это новое и в данном конкретном случае нежелательное поведение.

set session optimizer_switch='derived_merge=off'; устраняет проблему
(Это также можно сделать сset global ... или быть помещенным в my.cnf / my.ini)

 nl-x05 апр. 2019 г., 10:45
@Ingus Вы пытались настроить другие переключатели оптимизатора?
 wchiquito10 июн. 2016 г., 10:46
Вы можете прочитать немного больше в следующей статье:Производные таблицы в MySQL 5.7 а также9.2.1.18 Оптимизация подзапроса.
 Rodion V10 сент. 2016 г., 22:08
Большое спасибо. У меня такая же ситуация и ваше решение сработало. В моем случае mysql-5.5 возвращает результат через 0.5 с, а mysql-5.7 - через 1.2 с. После отключения оптимизатора производного_мерги и переписывания запроса из объединений в подзапросы mysql-5.7 возвращает результат через 0.01 с
 Ingus05 апр. 2019 г., 10:49
@ Нл-х еще нет! Мы только что обнаружили, что MySQL использует 100% CPU. Есть мысли по этому поводу?
 Santosh Pillai05 апр. 2019 г., 13:19
@Ingus Я добавил это в my.cnf и перезапустил mysql. Изменения --- [mysqld] sql_mode = '' optimizer_switch = 'производное_merge = выкл, duplicateweedout = выкл'
 Ingus05 апр. 2019 г., 14:37
@SantoshPillai Что вы имеете в виду под mysql_upgrade? Обновление версии? Если это так, я обновился до 5.7.25 и результат такой же
 KrishCdbry07 янв. 2019 г., 10:51
Perfecto! Отличный
 Santosh Pillai02 апр. 2019 г., 11:45
Хороший, была такая же проблема после обновления до 5.7. если бы они были отключены по умолчанию на 5,7, это позволило бы избежать всех стрессов
 Santosh Pillai05 апр. 2019 г., 13:24
@Ingus Вы также выполнили mysql_upgrade для обновления таблиц?
 Santosh Pillai05 апр. 2019 г., 18:36
@lingus запустить команду mysql_upgradedev.mysql.com/doc/refman/5.6/en/mysql-upgrade.html
 Ingus05 апр. 2019 г., 09:58
@ SantoshPillai была ли необходимость перезапуска MySQL? У меня есть эта проблема прямо сейчас, и я не могу это исправить. , На тесте MariaDB 10.1.37 работает быстрее, но на MySQL 5.7.23 работает очень медленно
 Johnny05 янв. 2018 г., 09:36
Спасибо @ нл-х. Потратил целый день, пытаясь выяснить настройки, почему запрос, который занимал 0,9 секунды на старом сервере баз данных MySQL, занимал 4,5 минуты на сервере MariaDB. Отключение флажка производного_мерджа уменьшило время запроса MariaDB до 0,8 секунды. Какая разница! Я не понимаю, почему этот флаг по умолчанию включен, если производительность настолько велика. Или, по крайней мере, MariaDB должна лучше документировать это для пользователей, переходящих с MySQL.

ее, чем даже 1 секунда.

Такая таблица, вероятно, будет включатьshop_id, dateи некоторые считают.

Подробнее о сводных таблицах.

Ваш ответ на вопрос