mysql在特定阈值下获取最大值的最快方法

Question

我在 mysql 中有两个表和一个慢速查询。

表格：

1. 包含通道、开始时间、结束时间

2. 字段的表格剪辑

   包含通道、开始时间、结束时间字段的表格显示

   包含通道、开始时间、结束时间

两个表都有字段 start_time 的索引。

我正在尝试查找许多剪辑中在剪辑之前开始的节目。

到目前为止，我得到了这个查询：

SELECT (
   SELECT shows.id 
   FROM shows
   WHERE shows.starttime<=clips.starttime AND shows.channel=clips.channel
   ORDER BY shows.starttime DESC
   LIMIT 1) as show_id,
   clips.*
FROM clips

对于少量的剪辑，这效果很好，但对于大量的剪辑，它变得太慢。

我的理解是，依赖子查询应该特别快，因为 start_time 上有一个索引，并且需要做的只是索引查找。尽管如此，它还是很慢，并且解释查询状态是“使用where”而不是“使用索引”。

这是解释的输出

--+------------------+-----+-----+------------+---------+------+----+------+-----------------------+
id| select_type      |table|type |possibleKeys| key     |keylen|ref |rows  | Extra                 |
--+------------------+-----+-----+------------+---------+------+----+------+-----------------------+
 1|PRIMARY           |clips|range| startDate  |startDate| 8    |NULL| 9095 |Using where;Using index|
 2|DEPENDENT SUBQUERY|shows|index| startDate  |startDate| 8    |NULL|287896|Using where;Using index|
--+------------------+-----+-----+------------+---------+------+----+------+-----------------------+

任何有关如何提高此任务性能的建议将不胜感激。

Answer 1

尝试将查询重写为

SELECT max(shows.starttime) as show_start, shows.id as show_id, clips.*
FROM shows
INNER JOIN clips ON (clips.channel = shows.channel AND shows.starttime<=clips.starttime)
GROUP BY clips.id

因为剪辑是节目的一部分，所以您希望它们靠近在一起，您可以通过执行以下操作进一步限制命中数：

SELECT max(shows.starttime) as show_start, shows.id as show_id, clips.*
FROM shows
INNER JOIN clips ON (clips.channel = shows.channel 
  AND clips.starttime BETWEEN shows.starttime AND DATE_ADD(shows.starttime, INTERVAL 1 DAY) )
GROUP BY clips.id

这将阻止 MySQL 运行对每一行进行排序的完整子查询的剪辑。

Answer 2

我认为添加同时使用 start_time 和 channel 列的索引可能会将查询性能提高到可接受的值。

约翰的回答很好，但考虑到你的过滤器，我认为索引在任何情况下都可以提高性能。