mysql - MySQL:子查询检查超过 14000 行的子查询优化问题
问题描述
我需要帮助来优化下面的子查询。简而言之,我有以下查询,其中表根据子查询条件在表的AND 最大值上tree
连接branch
表。s_id
timestamp
branch
我对这个查询返回的结果很满意。但是,此查询非常慢。branch2
瓶颈是检查超过 14000 行的依赖子查询( )。如何优化子查询以加快查询速度?
SELECT *
FROM dept.tree tree
LEFT JOIN dept.branch branch ON tree.s_id = branch.s_id
AND branch.timestamp =
(
SELECT MAX(timestamp)
FROM dept.branch branch2
WHERE branch2.s_id = tree.s_id
AND branch2.timestamp <= tree.timestamp
)
WHERE tree.timestamp BETWEEN CONVERT_TZ('2020-05-16 00:00:00', 'America/Toronto', 'UTC')
AND CONVERT_TZ('2020-05-16 23:59:59', 'America/Toronto', 'UTC')
AND tree.s_id IN ('459','460')
ORDER BY tree.timestamp ASC;
表树:
id box_id timestamp
373001645 1 2020-05-07 06:00:20
373001695 1 2020-05-07 06:02:26
373001762 1 2020-05-07 06:05:17
373001794 1 2020-05-07 06:06:38
373001810 2 2020-05-07 06:07:21
表分支:
id box_id timestamp data
373001345 1 2020-05-07 06:00:20 {"R": 0.114, "H": 20.808}
373001395 1 2020-05-07 06:02:26 {"R": 0.12, "H": 15.544}
373001462 1 2020-05-07 06:03:01 {"R": 0.006, "H": 55.469}
373001494 1 2020-05-07 06:04:38 {"R": 0.004, "H": 51.85}
373001496 1 2020-05-07 06:05:18 {"R": 0.02, "H": 5.8965}
373001497 1 2020-05-07 06:06:39 {"R": 0.12, "H": 54.32}
373001510 2 2020-05-07 06:07:09 {"R": 0.34, "H": 1.32}
373001511 2 2020-05-07 06:07:29 {"R": 0.56, "H": 32.7}
分支有 s_id 和时间戳索引
我正在使用 5.7.25-google-log 版本
解释给出以下内容:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY tree range unique_timestamp_s_id,idx_s_id_timestamp,idx_timestamp idx_s_id_timestamp 10 2629 100.00 Using index condition; Using filesort
1 PRIMARY branch ref unique_timestamp_s_id,idx_timestamp unique_timestamp_s_id 5 func 1 100.00 Using where
2 DEPENDENT SUBQUERY branch2 ref unique_timestamp_s_id,idx_s_id_timestamp,idx_timestamp idx_s_id_timestamp 5 tree.s_id 14122 33.33 Using where; Using index
解决方案
请提供SHOW CREATE TABLE
。
branch
需要INDEX(s_id, timestamp)
你需要LEFT
吗?它可能会无缘无故地减慢查询速度。
IN
一列和BETWEEN
另一列的组合可能优化不佳;你在运行什么版本?
请提供EXPLAIN SELECT
,以便我们讨论它是否优化得很好。如果不是,我们可以讨论如何将IN
(a variant on OR
) 变成UNION
.
这实际上可能比我上面考虑的方法更快......
有了上面的索引,然后大大重写查询:
SELECT b.*
FROM ( SELECT s_id,
MAX(timestamp) as timestamp
FROM dept.branch
WHERE timestamp BETWEEN
CONVERT_TZ('2020-05-16 00:00:00', 'America/Toronto', 'UTC')
AND CONVERT_TZ('2020-05-16 23:59:59', 'America/Toronto', 'UTC')
AND s_id IN ('459','460')
) AS x
JOIN dept.branch AS b USING(s_id, timestamp)
首先,看看这是否得到了正确的信息。然后我将解释如何UNION
在子查询中执行(如果您需要帮助)。
推荐阅读
- r - 每第 n 个变量之间的校正
- wpf - 为什么WPF会检测到我项目的另一个同名文件夹
- html - 为什么我放大时我的 div 元素会分散?
- boost - boost mpi 示例编译
- docusignapi - DocuSign 服务集成身份验证 - 在不使用 X-DocuSign-Authentication 的情况下获取用户 ID
- json - AttributeError:“str”对象在读取 JSON 文件时没有“read”属性
- actions-on-google - 助手在等待用户输入时完成通话后麦克风自动拾取
- java - 如何通过 cacheNames 驱逐 spring 缓存?
- c++ - 获取当前按键的简单方法
- javascript - 节点 child_process 和 twitch api