Hızlı zaman aralığı sorguları ile Sqlite ile zaman serileri nasıl kullanılır?
Unix zaman damgası sütunuyla bir Sqlite veritabanındaki olayları günlüğe kaydettiğimizi varsayalım ts
:
CREATE TABLE data(ts INTEGER, text TEXT); -- more columns in reality
ve tarih saat aralıkları için hızlı arama istediğimizi, örneğin:
SELECT text FROM data WHERE ts BETWEEN 1608710000 and 1608718654;
Bunun gibi, EXPLAIN QUERY PLAN
verir SCAN TABLE data
bir bariz çözüm için yani, kötü olan bir dizin yarat ile CREATE INDEX dt_idx ON data(ts)
.
Daha sonra sorun çözülür, ancak doğrudan O (log n) 'de bir B-ağaç araması kullanabileceğimiz, zaten artan sekans / önceden sıralanmış sütun için bir indeks tutmak zorunda kalmak daha ziyade zayıf bir çözümdür . Dahili olarak bu indeks olacaktır:ts
ts rowid
1608000001 1
1608000002 2
1608000012 3
1608000077 4
Bu, DB alanı israfıdır (ve bir sorgunun önce dizine bakması gerektiğinde CPU).
Bundan kaçınmak için:
(1) yarar bir
ts
şekildeINTEGER PRIMARY KEY
, bu yüzdents
olacağınırowid
kendisi. Ancak bu başarısız olur çünküts
benzersiz değildir: Aynı saniyede (veya hatta aynı milisaniyede) 2 olay gerçekleşebilir.Örneğin, SQLite Autoincrement'te verilen bilgilere bakın .
(2) artan bir sayı ile birleştirilmiş
rowid
zaman damgası olarak kullanabilirizts
. Misal:16087186540001 16087186540002 [--------][--] ts increasing number
Ardından
rowid
benzersizdir ve kesin bir şekilde artar (saniyede 10.000'den az olay olması koşuluyla) ve herhangi bir indeks gerekmez. Bir sorguWHERE ts BETWEEN a AND b
basitçe olurWHERE rowid BETWEEN a*10000 AND b*10000+9999
.Ancak Sqlite'ı belirli bir değere eşit veya daha büyük olan
INSERT
bir öğeye sormanın kolay bir yolu varrowid
mı? Geçerli zaman damgası olduğunu1608718654
ve iki etkinlik göründüğünü varsayalım :CREATE TABLE data(ts_and_incr INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT); INSERT INTO data VALUES (NEXT_UNUSED(1608718654), "hello") #16087186540001 INSERT INTO data VALUES (NEXT_UNUSED(1608718654), "hello") #16087186540002
Daha genel olarak, hızlı sorgulara sahip olmak için Sqlite ile en uygun zaman serileri nasıl oluşturulur WHERE timestamp BETWEEN a AND b
?
Yanıtlar
İlk çözüm
Soruda detaylandırılan yöntem (2) iyi çalışıyor gibi görünüyor. Bir karşılaştırmada şunu elde ettim:
- naif yöntem, indekssiz: 18 MB veritabanı, 86 ms sorgu süresi
- saf yöntem, indeksli: 32 MB veritabanı, 12 ms sorgu süresi
- yöntem (2): 18 MB veritabanı, 12 ms sorgu süresi
Kilit noktası kullanmak için burada dt
bir olarak INTEGER PRIMARY KEY
böylece, o satır kimliği kendisi olacaktır (ayrıca bkz SQLite'ta birincil anahtar için gerekli bir dizin var mı? B-ağacı kullanılarak) ve orada olacak değil başka gizli olması rowid
sütunu. Böylece kendine karsilik yapacak ekstra endeksi kaçınmak dt => rowid
: Burada dt
ise satır kimliği.
Ayrıca , son eklenen kimliği izleyen AUTOINCREMENT
dahili bir sqlite_sequence
tablo oluşturan kullanıyoruz . Bu, ekleme sırasında kullanışlıdır: iki olayın saniye cinsinden aynı zaman damgasına sahip olması mümkün olduğundan (milisaniye veya mikrosaniye zaman damgalarında bile mümkün olabilir, işletim sistemi hassasiyeti kesebilir), arasında maksimum olanı kullanırız timestamp*10000
ve last_added_ID + 1
benzersiz olduğundan emin olmak için :
MAX(?, (SELECT seq FROM sqlite_sequence) + 1)
Kod:
import sqlite3, random, time
db = sqlite3.connect('test.db')
db.execute("CREATE TABLE data(dt INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT);")
t = 1600000000
for i in range(1000*1000):
if random.randint(0, 100) == 0: # timestamp increases of 1 second with probability 1%
t += 1
db.execute("INSERT INTO data(dt, label) VALUES (MAX(?, (SELECT seq FROM sqlite_sequence) + 1), 'hello');", (t*10000, ))
db.commit()
# t will range in a ~ 10 000 seconds window
t1, t2 = 1600005000*10000, 1600005100*10000 # time range of width 100 seconds (i.e. 1%)
start = time.time()
for _ in db.execute("SELECT 1 FROM data WHERE dt BETWEEN ? AND ?", (t1, t2)):
pass
print(time.time()-start)
WITHOUT ROWID
Tablo kullanmak
İşte 8 ms'lik bir sorgu süresi WITHOUT ROWIDveren başka bir yöntem . AUTOINCREMENT kullanılırken kullanılamadığından, otomatik artan bir id'i kendimiz uygulamalıyız . a kullanmak istediğimizde ve fazladan bir sütuna sahip olmaktan kaçındığımızda kullanışlıdır . Bir tane B-ağacı ve bir tane B-ağacı olmak yerine , sadece bir tane olacak.WITHOUT ROWID
WITHOUT ROWID
PRIMARY KEY(dt, another_column1, another_column2, id)
rowid
rowid
(dt, another_column1, ...)
db.executescript("""
CREATE TABLE autoinc(num INTEGER); INSERT INTO autoinc(num) VALUES(0);
CREATE TABLE data(dt INTEGER, id INTEGER, label TEXT, PRIMARY KEY(dt, id)) WITHOUT ROWID;
CREATE TRIGGER insert_trigger BEFORE INSERT ON data BEGIN UPDATE autoinc SET num=num+1; END;
""")
t = 1600000000
for i in range(1000*1000):
if random.randint(0, 100) == 0: # timestamp increases of 1 second with probabibly 1%
t += 1
db.execute("INSERT INTO data(dt, id, label) VALUES (?, (SELECT num FROM autoinc), ?);", (t, 'hello'))
db.commit()
# t will range in a ~ 10 000 seconds window
t1, t2 = 1600005000, 1600005100 # time range of width 100 seconds (i.e. 1%)
start = time.time()
for _ in db.execute("SELECT 1 FROM data WHERE dt BETWEEN ? AND ?", (t1, t2)):
pass
print(time.time()-start)
Kabaca sıralanmış UUID
Daha genel olarak sorun, tarih saatine göre "kabaca sıralanmış" kimliklere sahip olmakla bağlantılıdır. Bununla ilgili daha fazlası:
- ULID (Evrensel Olarak Benzersiz Sözlüksel Olarak Sıralanabilir Tanımlayıcı)
- kar tanesi
- MongoDB Nesne Kimliği
Tüm bu yöntemler bir kimlik kullanır:
[---- timestamp ----][---- random and/or incremental ----]
SqlLite konusunda uzman değilim, ancak veritabanları ve zaman serileri ile çalıştım. Daha önce benzer bir durum yaşadım ve kavramsal çözümümü paylaşırdım.
Sorunuzda cevabın nasıl bir parçası var ama bunu yapmanın yolu yok.
Benim yaptığım şekilde, 2 tablo oluşturmak, bir tablo (ana_günlükler), birincil anahtar olarak tamsayı olarak tarih olarak saniye artışıyla süreyi günlüğe kaydedecek ve diğer tablo günlükleri, sizin durumunuzda yapabileceğiniz belirli bir zamanda yapılan tüm günlükleri (ana_ub_loglar) saniyede 10000 günlüğe kadar olabilir. Main_sub_log'ların main_log'lara referansı vardır ve her günlük saniyesi için ve X sayıda log, bu saniyeye ait kendi sayaç kimliği ile yeniden başlar.
Bu şekilde, zaman serilerinizi tek bir yerde tüm günlükler yerine olay pencerelerinin saniyeleriyle sınırlandırırsınız.
Bu şekilde bu iki masaya katılabilir ve ilk tablodan 2 belirli saat arasında baktığınızda aradaki tüm kayıtları alırsınız.
Öyleyse işte 2 masamı nasıl oluşturdum:
CREATE TABLE IF NOT EXISTS main_logs (
id INTEGER PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS main_sub_logs (
id INTEGER,
ref INTEGER,
log_counter INTEGER,
log_text text,
PRIMARY KEY (id),
FOREIGN KEY (ref) REFERENCES main_logs(id)
)
Bazı sahte veriler ekledim:

Şimdi 1608718655 ile 1608718656 arasındaki tüm günlükleri sorgulayalım
SELECT * FROM main_logs AS A
JOIN main_sub_logs AS B ON A.id == B.Ref
WHERE A.id >= 1608718655 AND A.id <= 1608718656
Bu sonucu alacak:
