限制Sqlite DB中的记录数 [英] Limiting the number of records in a Sqlite DB
问题描述
我要在此处实现的条件是，sqlite数据库仅保存最近的1000条记录。每个记录都有时间戳记。
立即生效的低效逻辑之一是检查记录总数。如果它们超过1000，则只需删除掉到外围的那些。
但是，我将必须对每个INSERT进行此检查，这会使事情效率极低。
有什么更好的逻辑？我们可以用触发器做些什么吗？
一些与我想到的逻辑相同的相关问题都张贴在SO上：-
您可以使用隐式 rowid 列。
假设您不t以不同的方式手动删除行：
从您的表中删除rowid< （last_row_id-1000）
您可以使用 API函数或作为 max（rowid）
如果您不需要完全 1000条记录（例如，只想清理旧记录），则不必在每次插入时都执行。在程序中添加一些计数器并执行清理f.i.每100次插入一次。
更新：
无论如何，您需要为每次插入或每次选择支付性能。因此，选择取决于您所拥有的更多内容：INSERT或SELECT。
如果您没有太多插入来关心性能，则可以使用以下触发器来保留不超过1000条记录： （从表名ORDER BY时间戳DESC LIMIT 1000中选择MIN（时间戳））;
END
在timestamp列上创建唯一索引也是一个好主意（以防万一还不是PK）。还要注意，SQLITE仅支持 FOR EACH ROW 触发器，因此，当您批量插入许多记录时，值得暂时禁用该触发器。
如果INSERT太多，则在数据库方面您无能为力。通过添加触发条件，例如在NEW.rowid％100 = 0 时进行插入，可以实现频率较低的触发调用。选择仅使用LIMIT 1000（或创建适当的视图）。
我无法预测速度会快多少。最好的方法是衡量您在特定情况下将获得多少性能。
What I'm trying to implement here is a condition wherein a sqlite database holds only the most recent 1000 records. I have timestamps with each record. One of the inefficient logic which strikes right away is to check the total number of records. If they exceed 1000, then simply delete the ones which fall out of the periphery.
However, I would have to do this check with each INSERT which makes things highly inefficient.
What could be a better logic? Can we do something with triggers?
Some related questions which follow the same logic I thought of are posted on SO:-
Delete oldest records from database
SQL Query to delete records older than two years
You can use an implicit "rowid" column for that.
Assuming you don't delete rows manually in different ways:
DELETE FROM yourtable WHERE rowid < (last_row_id - 1000)
You can obtain last rowid using API function or as max(rowid)
If you don't need to have exactly 1000 records (e.g. just want to cleanup old records), it is not necessary to do it on each insert. Add some counter in your program and execute cleanup f.i. once every 100 inserts.
UPDATE:
Anyway, you pay performance either on each insert or on each select. So the choice depends on what you have more: INSERTs or SELECTs.
In case you don't have that much inserts to care about performance, you can use following trigger to keep not more than 1000 records:
CREATE TRIGGER triggername AFTER INSERT ON tablename BEGIN
DELETE FROM tablename WHERE timestamp < (SELECT MIN(timestamp) FROM tablename ORDER BY timestamp DESC LIMIT 1000);
END
Creating unique index on timestamp column should be a good idea too (in case it isn't PK already). Also note, that SQLITE supports only FOR EACH ROW triggers, so when you bulk-insert many records it is worth to temporary disable the trigger.
If there are too many INSERTs, there isn't much you can do on database side. You can achieve less frequent trigger calls by adding trigger condition like AFTER INSERT WHEN NEW.rowid % 100 = 0. And with selects just use LIMIT 1000 (or create appropriate view).
I can't predict how much faster that would be. The best way would be just measure how much performance you will gain in your particular case.
这篇关于限制Sqlite DB中的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
