SQL LIMIT 获取最新记录

Question

我正在编写一个脚本，其中将列出所有 12 个类别的 25 个项目。数据库结构如下：

tbl_items
---------------------------------------------
item_id | item_name | item_value | timestamp 
---------------------------------------------

tbl_categories
-----------------------------
cat_id | item_id | timestamp
-----------------------------

表tbl_items中有大约600,000行。我正在使用这个 SQL 查询：

SELECT e.item_id, e.item_value
  FROM tbl_items AS e
  JOIN tbl_categories AS cat WHERE e.item_id = cat.item_id AND cat.cat_id = 6001
  LIMIT 25

在从 6000 到 6012 的 cat_id 循环中使用相同的查询。但我想要每个类别的最新记录。如果我使用类似以下内容：

SELECT e.item_id, e.item_value
  FROM tbl_items AS e
  JOIN tbl_categories AS cat WHERE e.item_id = cat.item_id AND cat.cat_id = 6001
  ORDER BY e.timestamp
  LIMIT 25

..查询将计算大约 10 分钟，这是不可接受的。我可以更好地使用 LIMIT 来为每个类别提供最新的 25 条记录吗？

任何人都可以帮助我在没有 ORDER BY 的情况下实现这一目标吗？任何想法或帮助将受到高度赞赏。

编辑

tbl_items

+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| item_id             | int(11)      | NO   | PRI | 0       |       |
| item_name           | longtext     | YES  |     | NULL    |       |
| item_value          | longtext     | YES  |     | NULL    |       |
| timestamp           | datetime     | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+

tbl_categories

+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| cat_id         | int(11)    | NO   | PRI | 0       |       |
| item_id        | int(11)    | NO   | PRI | 0       |       |
| timestamp      | datetime   | YES  |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+

Answer 1

可以添加索引吗？如果您在 timestamp 和其他相应列上添加索引，ORDER BY 将不会花费 10 分钟。

Answer 2

首先：

items和categories之间似乎是N:M关系：一个item可能属于多个categories< /代码>。我这样说是因为 categories 有 item_id 外键。

如果不是 N:M 关系那么你应该考虑改变设计。如果是 1:N 关系，其中一个类别有多个项目，则 item 必须包含 category_id 外键。

使用 N:M：

我重写了您的查询以进行内部联接而不是交叉联接：

  SELECT e.item_id, e.item_value
  FROM 
     tbl_items AS e
  JOIN 
     tbl_categories AS cat 
        on e.item_id = cat.item_id
  WHERE  
     cat.cat_id = 6001
  ORDER BY 
     e.timestamp
  LIMIT 25

要优化性能，所需的索引是：

create index idx_1 on tbl_categories( cat_id, item_id)

项目上的索引不是强制性的，因为主键也已建立索引。
包含时间戳的索引没有多大帮助。可以肯定的是，可以尝试使用 item_id 和 timestamp 对项目建立索引，以避免访问表并从索引中获取值：

create index idx_2 on tbl_items( item_id, timestamp)

要提高性能，您可以通过以下方式更改类别循环单个查询：

  select T.cat_id, T.item_id, T.item_value from 
  (SELECT cat.cat_id, e.item_id, e.item_value
   FROM 
     tbl_items AS e
   JOIN 
     tbl_categories AS cat 
        on e.item_id = cat.item_id
   ORDER BY 
     e.timestamp
   LIMIT 25
  ) T
  WHERE  
     T.cat_id between 6001 and 6012
  ORDER BY
     T.cat_id, T.item_id

请尝试此查询，并在必要时返回您的评论以对其进行完善。

Answer 3

抛开所有其他因素，我可以告诉您，查询如此慢的主要原因是因为结果涉及 longtext 列。

MySQL 中的 BLOB 和 TEXT 字段主要用于存储完整的文件（文本或二进制）。它们与 InnoDB 表的行数据分开存储。每次查询涉及排序（显式排序或group by）时，MySQL 一定会使用磁盘进行排序（因为它无法提前确定任何文件有多大）。

这可能是一个经验法则：如果您需要在查询中返回多于一行的列，则字段的类型几乎永远不应该是 TEXT 或 BLOB< /code>，请改用 VARCHAR 或 VARBINARY。

UPD

如果无法更新表，则使用当前索引和列类型的查询将很难加快。但是，无论如何，这里有一个类似的问题和一个流行的解决方案来解决您的问题： 如何选择每个类别的最新四个项目？