代码之家 › 专栏 › 技术社区 › Saif Bechan

有效地存储每个项目的最后X条记录

performance mysql

Saif Bechan · 技术社区 · 16 年前

我想以一种有效的方式将最后X条记录存储在MySQL数据库中。因此,当存储第4条记录时,应删除第一条记录。

我这样做的方法不是首先运行一个获取项目的查询。检查我应该做什么然后插入/删除。

必须有更好的方法来做到这一点。有什么建议吗?

编辑

我想我应该补充一下,存储的记录没有唯一的编号。他们的标准杆是混合的。例如,文章\u id和用户\u id。

然后我想为用户创建一个包含最后x个项目的表。

只从按用户分组和按时间排序的表中选择文章对我来说不是一个选项。在我进行分类和分组的表中,有数百万条记录,而且毫无理由地受到了很大的影响。因此,在最后一个X记录之间创建一个表是非常有效的。

我没有在文章和用户中使用这个。

3 回复 | 直到 16 年前

Hannes de Jager Ruben 16 年前

在存储过程中实现它(表名为ibt,它代表表之间):

delimiter ;
DROP TABLE IF EXISTS `ibt`;
CREATE TABLE `ibt` (
  `seqid` int(10) unsigned NOT NULL auto_increment,
  `article_id` varchar(10) NOT NULL default '',
  `user_id` varchar(10) NOT NULL default '',  
   anotherVar VARCHAR(10),
  PRIMARY KEY  (`article_id`,`user_id`),
  KEY `seqid` (`seqid`)
) ENGINE=MEMORY AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;


drop procedure if exists addEntry;
delimiter $$
create procedure addEntry(_article_id INT, _user_id INT, _anotherVar VARCHAR(10))
begin
  DECLARE done INT DEFAULT 0;
  declare seq INT;    
  declare seqNew INT DEFAULT 1;  
  declare Cnt INT DEFAULT 0;  

  declare cur CURSOR for
      SELECT seqid
      from ibt 
      where user_id=_user_id   
      order by seqid desc;  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  START TRANSACTION;

  open cur;  
  REPEAT  
    FETCH cur INTO seq;    
    IF NOT done THEN        
      SET Cnt = Cnt+1;       
      IF Cnt = 3 THEN            
         DELETE FROM `ibt` where seqid = seq;
      END IF;      
      IF Cnt = 1 THEN            
         SET seqNew = seq + 1;
      END IF;      
    END IF;
  UNTIL done END REPEAT;

  INSERT into `ibt` 
  SET article_id=_article_id, 
        user_id=_user_id,  
      seqid=seqNew,      
      anotherVar=_anotherVar;

  close cur;  

  COMMIT;
end $$

delimiter ;

call addEntry(1, 1, 'a');
call addEntry(2, 1, 'b');
call addEntry(3, 1, 'c');
call addEntry(4, 1, 'd');

可以将上述SQL作为要测试的单元运行。我用过Heidisql。

一旦数据库中有了存储过程,就可以从PHP代码中执行“call addentry”。

Hannes de Jager Ruben 16 年前

将前三项的主键保存在文件或其他永久性存储中,然后执行UPDATE语句而不是删除/插入?

如果你想把所有的都放在数据库中,那么我会给中间表中的所有记录添加一个唯一的序列号(我们称之为seqid),然后仍然执行一个查询,但不获取整行,只获取序列号,例如。

SELECT seqid from inbetweentable where article_id=? and user_id=?

在您的编程语言(比如php)中排序,然后进行更新

UPDATE inbetweentable SET seqid=BIGGESTID+1, ... WHERE seqid=SMALLESTID

(biggested和smallestid来自您的PHP代码)

编辑: 要仅从SQL语句返回一个值(逗号分隔的字符串),请执行以下操作:

 SELECT GROUP_CONCAT(seqid) as idsCsv from inbetweentable where article_id=? and user_id=? ORDER BY seqid

并用PHP解析。这将节省mysql和php端的循环代码,而且应该更快。例如

<?php
// Get single row
... 
$seqIds = explode(',', $row['idsCsv']);

newtover 16 年前

我认为您应该在交易中执行以下操作:

插入新记录
获取多余记录的ID
从步骤2中删除具有ID的记录(如果有)

如果您可以在一个查询中合并步骤2和3,那就太好了,但这似乎是不可能的,因为您需要对同一个不允许的表进行排序和删除。

以下是一些提示:

CREATE TEMPORARY TABLE items (
  item_id int unsigned not null auto_increment,
  data_ varchar(5) not null,
  primary key(item_id)
);

INSERT INTO items (data_)
  VALUES ('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6');

# select ids of the excess items
SELECT item_id
  FROM items, (select @cnt:=0) as cnt
  WHERE IF((@cnt:=@cnt+1)<=3, 0, 1)
  ORDER BY item_id DESC;

最后一个查询将返回:

+-------+
|item_id|
+-------+
|   3   |
|   2   |
|   1   |
+-------+