后端 - MySQL查询当前数据的上一条，下一条？

Question

获取当前数据的上一条，下一条的 MySQL 代码:

WITH RankedRecords AS (    
    SELECT *,    
           LEAD(id) OVER (ORDER BY time ASC) AS next_id,    
           LAG(id) OVER (ORDER BY time ASC) AS prev_id    
    FROM records    
)    
SELECT r.id, r.time,  -- 其他字段
       CASE   
           WHEN r.id = @CurrentID THEN 'Current'  -- @CurrentID 是当前id
           WHEN r.prev_id = @CurrentID THEN 'Previous'  
           WHEN r.next_id = @CurrentID THEN 'Next'  
       END AS record_type  
FROM RankedRecords r  
WHERE r.id = @CurrentID OR r.prev_id = @CurrentID OR r.next_id = @CurrentID  
ORDER BY CASE WHEN record_type = 'Previous' THEN 1 ELSE 2 END, r.time ASC;

代码是一言生成的，测试达到了效果，想问问数据库大佬，这段代码如果对大量数据的查询会影响性能吗，像这一行：

   SELECT *, 
           LEAD(id) OVER (ORDER BY time ASC) AS next_id,    
           LAG(id) OVER (ORDER BY time ASC) AS prev_id    
   FROM records

数据库结构：

id	time	···一些其他列
2	12345660	···
3	12345640	···
5	12345670	···

查询id=2，以time排序：

id	time	···一些其他列	record_type
3	12345640	···	Previuous
5	12345670	···	Next
2	12345660	···	Current

查询id=3：

id	time	···一些其他列	record_type
2	12345660	···	Next
3	12345640	···	Current

Answer 1

WITH RankedRecords AS (    
    SELECT id, time,  -- 只选择需要的列
           LEAD(id) OVER (ORDER BY time ASC) AS next_id,    
           LAG(id) OVER (ORDER BY time ASC) AS prev_id    
    FROM records    
)    
SELECT r.id, r.time,  -- 其他字段
       CASE   
           WHEN r.id = @CurrentID THEN 'Current'  -- @CurrentID 是当前id
           WHEN r.prev_id = @CurrentID THEN 'Previous'  
           WHEN r.next_id = @CurrentID THEN 'Next'  
       END AS record_type  
FROM RankedRecords r  
WHERE r.id = @CurrentID OR r.prev_id = @CurrentID OR r.next_id = @CurrentID  
ORDER BY CASE WHEN record_type = 'Previous' THEN 1 ELSE 2 END, r.time ASC;

Answer 2

回答

该段代码使用了 MySQL 的窗口函数（LEAD 和 LAG）以及公用表表达式（CTE，即 WITH 子句），来查询当前数据行的前一条和后一条记录。这种查询方式在中等大小的数据集上通常是高效的，但在处理大量数据时，性能可能会受到影响，特别是如果 records 表非常大，或者没有适当的索引来支持 ORDER BY time ASC 排序。

对于性能优化，你可以考虑以下几点：

1. 索引：确保 time 列有索引，这样 ORDER BY 操作可以更快地进行。
2. 限制返回的数据量：如果你只对当前行、前一行和后一行感兴趣，那么可以在 WHERE 子句中添加额外的条件来限制返回的数据量，但这已经在你提供的查询中做到了。
3. 查询计划：使用 EXPLAIN 关键字来查看查询的执行计划，检查是否使用了索引以及是否有任何潜在的性能瓶颈。
4. 硬件和配置：确保数据库服务器有足够的 RAM 来缓存常用数据和索引，以及适当的 CPU 和磁盘 I/O 性能。
5. 分区：如果 records 表非常大，并且数据可以按某种方式分区（例如按日期），那么可以考虑使用 MySQL 的分区功能来提高性能。
6. 其他考虑：如果你经常需要执行此类查询，并且发现性能成为问题，那么可能需要考虑在数据插入或更新时预先计算并存储前一条和后一条记录的 ID，或者使用其他数据结构（如缓存）来存储这些信息。

关于你提到的这一行：

SELECT *, 
       LEAD(id) OVER (ORDER BY time ASC) AS next_id,    
       LAG(id) OVER (ORDER BY time ASC) AS prev_id    
FROM records

这一行本身并不会对性能产生特别大的影响，只要 time 列有索引并且表的大小不是极端的大。然而，它确实需要对整个 records 表进行排序操作，这是查询中最耗时的部分之一。

总的来说，对于中等大小的数据集，这个查询应该是足够高效的。但对于大数据集，你可能需要采取一些额外的措施来优化性能。