迁移到mysql 5.7后查询非常慢 [英] query extremely slow after migration to mysql 5.7

问题描述

我有一个带有InnoDB表的MySQL数据库，该表汇总了我想从MySQL 5.5迁移到MySQL 5.7的10十GB以上的数据.我有一个查询，看起来像:

I have a MySQL database with InnoDB tables summing up over 10 ten GB of data that I want to migrate from MySQL 5.5 to MySQL 5.7. And I have a query that looks a bit like:

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中却非常慢.

My problem is that this query is performing fast in MySQL 5.5 but extremely slow in MySQL 5.7.

在MySQL 5.5中，起初需要1秒钟，而<每重复执行一次0.001秒，而无需重新启动MySQL.
在MySQL 5.7中，在不重新启动MySQL的情况下，它在开始时需要花费11.5秒以上的时间，而每次重复执行则需要花费1.4秒的时间.
而且我向查询添加的LEFT JOIN越多，在MySQL 5.7中查询就变得越慢.

In MySQL 5.5 it is taking over 1 second at first and < 0.001 seconds every recurring execution without restarting MySQL.
In MySQL 5.7 it is taking over 11.5 seconds at first and 1.4 seconds every recurring execution without restarting MySQL.
And the more LEFT JOINs I add to the query, the slower the query becomes in MySQL 5.7.

这两个实例现在都在同一台计算机，同一硬盘驱动器上并以相同的my.ini设置运行.所以它不是硬件.
虽然执行计划确实有所不同，但我不知道该怎么做.

Both instances now run on the same machine, on the same hard drive and with the same my.ini settings. So it isn't hardware.
The execution plans do differ, though and I don't know what to make from it.

这是MySQL 5.5上的扩展说明:

This is the EXPLAIN EXTENDED on 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                     |

这是MySQL 5.7上的扩展说明:

This is the EXPLAIN EXTENDED on 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使其更快?
我不是在寻求更快地重写查询方面寻求帮助，因为那是我已经在做的事情.

I want to understand why the MySQLs treat the same query that much different, and how I can tweak MySQL 5.7 to be faster?
I'm not looking for help on rewriting the query to be faster, as that is something I am already doing on my own.

推荐答案

在注释中可以看到，@ wchiquito建议查看optimizer_switch.在这里，我发现可以将开关derived_merge设置为关闭，以修复此新问题，在这种情况下，这是不希望的行为.

As can be read in the comments, @wchiquito has suggested to look at the optimizer_switch. In here I found that the switch derived_merge could be set to off, to fix this new, and in this specific case undesired, behaviour.

set session optimizer_switch='derived_merge=off';解决了该问题.
(也可以使用set global ...完成此操作，也可以将其放在my.cnf/my.ini中)

set session optimizer_switch='derived_merge=off'; fixes the problem.
(This can also be done with set global ... or be put in the my.cnf / my.ini)

这篇关于迁移到mysql 5.7后查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！