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

问题描述

我有一个 MySQL 数据库，其中 InnoDB 表汇总了超过 10 GB 的数据，我想从 MySQL 5.5 迁移到 MySQL 5.7.我有一个看起来有点像的查询:

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 中，它一开始需要 11.5 秒，每次重复执行需要 1.4 秒，而无需重新启动 MySQL.
我添加到查询中的 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 上的 EXPLAIN EXTENDED:

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 上的 EXPLAIN EXTENDED:

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屋！