我有一个具有1000万行和3列的MySQL表,格式如下:
id time num
ca65e871-d758-437e-b76f-175234760e7b 2020-11-14 23:08:05.553770 11112222222
...
为了运行下面的第一个查询,我在表上建立了索引,(num, time)
并且工作非常快(在1000万行的表上<5毫秒):
SELECT COUNT(*)
FROM TABLE_NAME
WHERE time >= '2020-11-14 23:08:05.553752' AND num = 11112222222
但是,我还需要count(distinct)
在带有between
子句的同一张表上执行以下操作:
SELECT COUNT(DISTINCT num)
FROM TABLE_NAME
WHERE time >= '2020-11-14 23:08:05.553752'
AND num BETWEEN (11112222222 - 30)
AND (11112222222 + 30)
事实证明,这要慢得多,大约200毫秒。有没有一种方法可以加快在同一表上第二个查询的执行时间?
如果你的MySQl为8+,请尝试:
WITH RECURSIVE
cte AS ( SELECT 11112222222 - 30 num
UNION ALL
SELECT num + 1 FROM cte WHERE num < 11112222222 + 30 )
SELECT COUNT(*)
FROM cte
WHERE EXISTS ( SELECT NULL
FROM TABLE_NAME
WHERE TABLE_NAME.num = cte.num
AND time >= '2020-11-14 23:08:05.553752' )
如果你经常执行这样的查询,那么我建议创建数字从-30到30的服务表,并使用它代替递归CTE。
非常精彩!这使查询运行少于5毫秒。非常感谢你!您能简要介绍一下您的所作所为吗?
@Makaroni我
num
在范围内生成s列表,然后简单地测试num
在日期范围内是否存在与此相关的行。WISTE EXISTS检查是否存在-即,它对每一行执行一次,但对于每一行都会num
在找到一个值后中止(而您的查询需要查找所有匹配的行)。极好的!为了真正弄清楚,我将多次阅读该解释。:)) 再次感谢!
顺便说一句,您能帮助我解决类似的使用问题
count(distinct)
吗?我可以recursive cte
在这种情况下申请吗?stackoverflow.com/questions/65005378/…