mysql - 我怎样才能优化这个mysql操作？

Question

我有这张表-“产品使用情况”。该表中的列是 id、rank、product_id、checked_at(时间戳)。我正在尝试将另一列(时间戳)添加到名为“last_inactive”的表中，并追溯填充该列。这样做的逻辑是1)查找同一product_id的下一个条目的created_at(按排名排序)2) 获取该时间戳并减去 1 秒，然后使用该时间戳填充当前列并继续3) 如果下一行不存在，则使用no​​w()作为last_inactive的值

我尝试了以下方法来获得想要的结果

SELECT *,
        coalesce(
date_sub(
(SELECT checked_at
              FROM product_usage p2
              WHERE p2.rank > p.rank
              and p.product_id=p2.product_id
              group by id, product_id, created_at
              ORDER BY product_id, rank ASC
              LIMIT 1 OFFSET 0),
INTERVAL 1 second),
now()
)
as valid_until
FROM product_usage p
where p.product_id = '1111111';

此查询适用于这个特定的product_id，但每个product_id大约需要2-3秒，并且我希望它适用于大约20k个不同的product_id。我能做些什么来加快速度吗？

Answer 1

您不需要在子查询中进行聚合。所以试试这个:

SELECT pu.*,
       coalesce(date_sub( (SELECT pu2.checked_at
                           FROM product_usage pu2
                           WHERE pu2.rank > pu.rank AND
                                 pu2.product_id = pu.product_id
                           ORDER BY pu2.rank ASC
                           LIMIT 1 OFFSET 0
                          ), INTERVAL 1 second
                        ), now() ) as valid_until
FROM product_usage pu
WHERE pu.product_id = 1111111;  -- looks like a number so removed the single quotes

为此，您需要在product_usage(product_id,rank,created_at)上建立索引。

当然，在 MySQL 8+ 中，您只需使用 lead()。