如何加快在Mysql5上使用~5s但在Mysql8上超过10分钟的查询速度

Question

我的公司最近进行了一次数据库升级，从Mysql5升级到Mysql8，我们的一些查询已经大大放缓。(从几秒钟到30分钟后超时)。

我唯一的想法是，数据库设置一定有问题，但是如果一些熟悉的人能够查看这个查询，看看是否可以改进它，那就太好了。

select sd.*,
        sd.transactionDate as transactionDate,
        r.id as resellerId,
        max(pa.id) as platformAccountId,
        op.paymentTypeId as paymentTypeId,
        u.shopuserId as shopuserId,
        sd.currencyId as currencyId /* EUR */,
        GROUP_CONCAT(distinct concat('D', op.id) SEPARATOR ',') as orderPositionIds
        from doppaymentdata sd
        join doppaymentaccount sd1
        on sd1.id = sd.dopPaymentAccountId
        join orders o
        on o.platformOrderId = sd.orderId
        join orderposition op
        on o.id = op.ordersId and op.platformOrderPositionId = sd.orderPositionId
        join platformaccount pa
        on pa.id = o.platformAccountId
        join reseller r
        on r.id = pa.resellerId
        join sapshopuser u
        on u.shopuserId = o.shopuserId
        left join sapsettlement setl
        on setl.settlementitemId = sd.id and setl.settlementsource = 'DOP'
        where (sd.transactionType in ('PAYMENT_SHIPPED_ORDER'))
        and sd.amount > 0
        and u.uuid = 'SUCCESS'
        and setl.id is null
        and r.id in (1,2,3,6)
        and sd.transactionDate > '2014-06-30 23:59:59'
        group by sd.id

值得注意的是，orders表有26,000,000行，orderposition有55,000,000行，sapshopuser有17,000,000行，doppaymentdata有1,500,000行。其他表中有可忽略的数据。

这些表是彼此完全重复的，唯一的区别是，一批在Mysql5数据库中，另一批在Mysql8数据库中。

运行EXPLAIN会提供以下输出，如您所见，这两个输出完全不同：

(第一张图片是Mysql5，第二张图片是Mysql8 -点击放大)

我能做些什么来加速Mysql8查询？！

Answer 1

你需要sd1吗？还是op？它没有提供任何数据；只有一个JOIN：

join  doppaymentaccount sd1  ON sd1.id = sd.dopPaymentAccountId

join  orderposition op  ON o.id = op.ordersId
      and  op.platformOrderPositionId = sd.orderPositionId

JOIN将被“执行”以验证是否存在匹配行。如果您期望有这样的一行，那么查找就是浪费时间。(我不知道这10分钟中有多少是由于这个原因造成的。)即使更改为LEFT JOIN，其中它将是真正无用的，也不能阻止MySQL的工作。(当没有使用任何列时，MariaDB似乎足够聪明地抛出左联接。)

GROUP BY sd.id "smells" like a query that could benefit from "turning inside out". It seems to be doing an "explode-implode" -- that is 'exploding' the number of rows with all those加入, then 'imploding' down to one row per id`。

解决这个问题的方法是(对不起，我不知道你的案子的细节)：

SELECT ...
    FROM ( SELECT sd.id FROM sd ...
            ((minimal number of JOINs and WHEREs, etc to find the ids)) ) AS x
    JOIN ((the other tables, plus possibly back to `sd` to get `sd.*`,
               plus the other tables as needed. ))
    -- and hopefully no GROUP BY

如果在这些“修复”之后仍然有问题，我将检查索引。(现在大约有14条建议；如果您要更改查询，我将无法处理太多的建议。)请为所有8个表提供SHOW CREATE TABLE，以便我可以看到您所拥有的索引。