迁移到mysql 5.7后查询速度极慢

前端之家收集整理的这篇文章主要介绍了迁移到mysql 5.7后查询速度极慢前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

我有一个带有InnoDB表的MySQL数据库,总计超过10个GB的数据,我想从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_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.
添加查询中的LEFT JOIN越多,查询MysqL 5.7中就越慢.

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

这是MysqL 5.5上的EXPLAIN EXTENDED:

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

这是MysqL 5.7上的EXPLAIN EXTENDED:

| 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更快?
我不是在寻求更快地重写查询的帮助,因为这是我自己已经在做的事情.

最佳答案
可以在评论中看到,@ wchiquito建议查看optimizer_switch.在这里,我发现可以将开关derived_merge设置为off,以修复这个新的,并且在这种特定情况下是不期望的行为.

set session optimizer_switch =’derived_merge = off’;解决了这个问题.
(这也可以通过set global …或者放在my.cnf / my.ini中完成)

原文链接:https://www.f2er.com/mysql/433287.html

猜你在找的MySQL相关文章