Mysql WHERE ... ORDER BY 在大型数据库中花费太长时间
我有一个很大的数据库。 目前有2200万条线，但未来将有数十亿条线。 我附上了示例行，其中时间戳以毫秒为单位。 我想做的是每秒获取最后一行。
SELECT *
FROM btcusdt
WHERE 1502942432000 <= timestamp
AND timestamp < 1502942433000
ORDER BY tradeid DESC
LIMIT 1
我上面写的查询工作正常，但是 WHERE 条件花费的时间太长，因为它扫描了表中的所有行，但实际上 它不需要扫描所有行，因为时间已经是连续的。一旦不符合where条件，它就应该完成扫描。 关于如何加快速度有什么建议吗？
I have a large database.
There are 22 million lines for now, but there will be billions of lines in the future.
I have attached example lines where timestamp is in milliseconds.
What I want to do is get the last line every second.
SELECT *
FROM btcusdt
WHERE 1502942432000 <= timestamp
AND timestamp < 1502942433000
ORDER BY tradeid DESC
LIMIT 1
The query I wrote above works fine, but the WHERE condition takes too long because it scans all the rows in the table, but actually
It doesn't need to scan all the lines because the time is already sequential. As soon as it doesn’t fit the where condition, it should finish scanning.
Any suggestions on how I can speed this up?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论