Hızlı zaman aralığı sorguları ile Sqlite ile zaman serileri nasıl kullanılır?

Dec 23 2020

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 PLANverir SCAN TABLE databir 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şekilde INTEGER PRIMARY KEY, bu yüzden tsolacağını rowidkendisi. Ancak bu başarısız olur çünkü tsbenzersiz 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ş rowidzaman damgası olarak kullanabiliriz ts. Misal:

     16087186540001      
     16087186540002
     [--------][--]
         ts     increasing number 
    

    Ardından rowidbenzersizdir ve kesin bir şekilde artar (saniyede 10.000'den az olay olması koşuluyla) ve herhangi bir indeks gerekmez. Bir sorgu WHERE ts BETWEEN a AND bbasitçe olur WHERE rowid BETWEEN a*10000 AND b*10000+9999.

    Ancak Sqlite'ı belirli bir değere eşit veya daha büyük olan INSERTbir öğeye sormanın kolay bir yolu var rowidmı? Geçerli zaman damgası olduğunu 1608718654ve 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

4 Basj Dec 24 2020 at 04:49

İ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 dtbir olarak INTEGER PRIMARY KEYbö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ı rowidsü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 AUTOINCREMENTdahili bir sqlite_sequencetablo 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*10000ve last_added_ID + 1benzersiz 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 ROWIDTablo 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 ROWIDPRIMARY KEY(dt, another_column1, another_column2, id)rowidrowid(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 ----]
2 maytham-ɯɐɥʇʎɐɯ Dec 26 2020 at 16:59

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: