mysql 从最后 n 行中选择

2021-11-20 00:00:00 select mysql query-optimization

我有一个带有索引(自动增量)和整数值的表.该表有数百万行长.

I have a table with index (autoincrement) and integer value. The table is millions of rows long.

如何最有效地搜索某个数字是否出现在表格的最后 n 行?

How can I search if a certain number appear in the last n rows of the table most efficiently?

推荐答案

从答案 由@chaos 给出，但有一些修改:

Starting from the answer given by @chaos, but with a few modifications:

  • 如果您使用 LIMIT，则应始终使用 ORDER BY.RDBMS 表不保证隐式顺序.您可以通常按照主键的顺序获取行，但您不能依赖于此，也不能移植.

  • You should always use ORDER BY if you use LIMIT. There is no implicit order guaranteed for an RDBMS table. You may usually get rows in the order of the primary key, but you can't rely on this, nor is it portable.

如果按降序排序，则无需事先知道表中的行数.

If you order by in the descending order, you don't need to know the number of rows in the table beforehand.

您必须为派生表提供一个相关名称(也称为表别名).

You must give a correlation name (aka table alias) to a derived table.

这是我的查询版本:

SELECT `id`
FROM (
    SELECT `id`, `val`
    FROM `big_table`
    ORDER BY `id` DESC
    LIMIT $n
) AS t
WHERE t.`val` = $certain_number;

相关文章