如何优化 SQL 查询以高效地从大型数据库中的每个类别中检索最新项目？

提升海量数据集的 SQL 查询性能

高效的 SQL 查询对于维护数据库性能至关重要，尤其是在处理大型数据库和复杂的数据检索时。当使用多个互连表时，这一点尤其重要。

挑战：检索每个类别的最新项目

想象一个数据库存储按 ID 分类的项目。目标是显示每个类别及其最近添加的四个项目。 每个类别使用多个查询的传统方法会导致数据库负载过重，尤其是在类别数量较多的情况下。

优化查询：利用分组和连接

以下优化查询使用外连接和分组在单个数据库传递中检索所需的数据：

SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4;

此查询将同一类别中的每个项目 (i1) 与较新的项目 (i2) 进行比较。 如果存在的新项目少于四个，则 i1 包含在结果中。

替代方法

其他方法，例如用于每个类别内行编号的 MySQL 用户变量，提供替代解决方案：

SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 4;

对于 MySQL 8.0.3 及更高版本，SQL 窗口函数提供了标准化方法：

WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id DESC) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;

性能优势

随着类别数量的增加，这些优化的查询显着提高了性能。通过最大限度地减少数据库负载和优化资源使用，应用程序即使在处理大量数据集的情况下也能确保高效的数据检索。

以上是如何优化 SQL 查询以高效地从大型数据库中的每个类别中检索最新项目？的详细内容。更多信息请关注PHP中文网其他相关文章！