时间戳

首页 > 解决方案 > 尽可能快地获取我的 MySQL 的第一行（也在大表上）

mysql - 尽可能快地获取我的 MySQL 的第一行（也在大表上）

问题描述

我有一个用于工业机器的简单软件，它使用 MySQL 作为数据存储。任何时候数据库上只有一个连接和一个用户。

我的表很简单：

id   data            fetched 
int  varchar         boolean

1     KDINNALSKDGJ     0
2     F34LNALNLIJA     0

等等 id 总是顺序的，并且有一个索引。

我需要的是始终获取第一个“数据”（具有最低 id），其中 fetched 为 0。然后将“fetched”更新为“1”，因为我收到了数据。

我使用类似的东西

SELECT id, data FROM mytable WHERE fetched=0 LIMIT 0,1

这可行，但每次调用都会按顺序变慢。这是我真正的问题。在前 100 秒左右，我可以忍受估计的 0,005 秒，但在 50.000+ 时，我会遇到 0,3 秒。

我认为这是因为数据库每次从顶部搜索首先找到匹配的。

按数字约束索引要快得多：

SELECT id, data FROM mytable WHERE id> :myLastID  and fetched=0 LIMIT 0,1

.. 但这在 40.000 左右之后也会减慢，在 80.000 处我大约 20 毫秒（首先大约 6 毫秒）

我的最终数据库可能在数百万范围内，但通常可能在 2-500.000 左右

有什么办法可以让 MySQL 更快地返回“下一条记录”？使用 MySQL 中的 CURSOR 吗？

我将使用 Delphi 来连接 MySQL。我尝试了存储过程并使用 2 个查询来选择 /update。结果几乎一样。

标签： mysqlsqldelphisql-order-byquery-optimization

解决方案

首先：您的查询需要一个order by子句，否则实际上未定义将首先返回哪一行（不能保证它将是最小的行id）。

因此，您应该将其表述为：

select id, data 
from mytable 
where fetched = 0 
order by id
limit 1

然后为了性能，我建议添加以下索引：

create index myindex on mytable(fetched, id, data)

逻辑是：

  • 索引的第一列fetched, 匹配where子句中的谓词

  • 第二列是排序列 ( id)

  • 第三列是select子句 ( data)中的剩余列

这为您提供了一个覆盖索引：MySQL 应该能够通过仅查看索引来执行整个查询（即不查看数据本身）。

推荐阅读