तीव्र समय-श्रेणी के प्रश्नों के साथ स्केलाइट के साथ समय-श्रृंखला का उपयोग कैसे करें?

Question

मान लें कि हम यूनिक्स टाइमस्टैम्प कॉलम के साथ सकलाइट डेटाबेस में ईवेंट लॉग करते हैं ts:

CREATE TABLE data(ts INTEGER, text TEXT);   -- more columns in reality

और हम उदाहरण के लिए, डेटाइम रेंज के लिए फास्ट लुकअप चाहते हैं:

SELECT text FROM data WHERE ts BETWEEN 1608710000 and 1608718654;

इस तरह, EXPLAIN QUERY PLANवह देता है SCAN TABLE dataजो खराब है, इसलिए एक स्पष्ट समाधान के साथ एक सूचकांक बनाना हैCREATE INDEX dt_idx ON data(ts) ।

तब समस्या हल हो जाती है, लेकिन यह पहले से ही बढ़ते अनुक्रम / पहले से ही सॉर्ट किए गए कॉलम के लिए एक सूचकांक बनाए रखने के लिए एक खराब समाधान है tsजिसके लिए हम सीधे ओ (लॉग एन) में बी-ट्री खोज का उपयोग कर सकते हैं । आंतरिक रूप से यह सूचकांक होगा:

ts           rowid
1608000001   1
1608000002   2
1608000012   3
1608000077   4

जो DB स्पेस की बर्बादी है (और सीपीयू जब किसी क्वेरी को पहले इंडेक्स में देखना होता है)।

इससे बचने के लिए:

• (1) हम इस्तेमाल कर सकते हैं tsके रूप में INTEGER PRIMARY KEYहै, तो tsहो सकता है rowidअपने आप में। लेकिन यह विफल रहता है क्योंकि tsअद्वितीय नहीं है: 2 घटनाएं एक ही सेकंड (या एक ही मिलीसेकंड पर भी) हो सकती हैं।

  उदाहरण के लिए SQLite Autoincrement में दी गई जानकारी देखें ।

• (2) हम एक बढ़ती संख्या के साथ समतल rowidरूप में उपयोग कर सकते हैं ts। उदाहरण:

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

  फिर rowidअद्वितीय है और सख्ती से बढ़ रहा है (बशर्ते कि प्रति सेकंड 10k से कम घटनाएं हों), और किसी भी सूचकांक की आवश्यकता नहीं होगी। एक प्रश्न WHERE ts BETWEEN a AND bबस बन जाएगा WHERE rowid BETWEEN a*10000 AND b*10000+9999।

  लेकिन क्या दिए गए मूल्य से अधिक या उसके बराबर वाले INSERTआइटम पर सकलाइट से पूछने का एक आसान तरीका है rowid? मान लें कि वर्तमान टाइमस्टैम्प है 1608718654और दो घटनाएं दिखाई देती हैं:

    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
  

आम तौर पर, तेजी से प्रश्न करने के लिए, स्क्लाइट के साथ समय-श्रृंखला कैसे बनाएं WHERE timestamp BETWEEN a AND b?

Answer 1

पहला उपाय

प्रश्न में विस्तृत विधि (2) अच्छी तरह से काम करती है। एक बेंचमार्क में, मैंने प्राप्त किया:

• इंडेक्स के बिना भोली विधि: 18 एमबी डेटाबेस, 86 एमएस क्वेरी समय
• सूचकांक के साथ भोली विधि: 32 एमबी डेटाबेस, 12 एमएस क्वेरी समय
• विधि (2): 18 एमबी डेटाबेस, 12 एमएस क्वेरी समय

मुख्य बिंदु dtएक के रूप में उपयोग करने के लिए यहां है INTEGER PRIMARY KEY, इसलिए यह स्वयं ही पंक्ति आईडी होगा (यह भी देखें कि क्या SQLite में एक प्राथमिक के लिए एक सूचकांक आवश्यक है? ), एक बी-ट्री का उपयोग करके, और दूसरा छिपा हुआ कॉलम नहीं होगा rowid। इस प्रकार हम एक अतिरिक्त सूचकांक जो एक correspondance होगा बचने dt => rowid: यहां dt है पंक्ति आईडी।

हम AUTOINCREMENTआंतरिक रूप से एक sqlite_sequenceतालिका बनाने वाले का भी उपयोग करते हैं , जो अंतिम जोड़े गए आईडी का ट्रैक रखता है। सम्मिलित करते समय यह उपयोगी है: क्योंकि यह संभव है कि दो घटनाओं में सेकंड में समान टाइमस्टैम्प हो (यह मिलीसेकंड या माइक्रोसेकंड टाइमस्टैम्प के साथ भी संभव होगा, ओएस सटीक रूप से छोटा हो सकता है), हम अधिकतम उपयोग करते हैं timestamp*10000और last_added_ID + 1यह सुनिश्चित करने के लिए कि यह अद्वितीय है :

 MAX(?, (SELECT seq FROM sqlite_sequence) + 1)

कोड:

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तालिका का उपयोग करना

यहाँ एक और विधि है WITHOUT ROWIDजिसके साथ एक 8 एमएस क्वेरी समय देता है । हमें अपने आप ही एक ऑटो-इन्क्रिमेंटिंग आईडी लागू करना होगा, क्योंकि उपयोग करते समय AUTOINCREMENT उपलब्ध नहीं है 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)

---

मोटे तौर पर क्रमबद्ध UUID

आम तौर पर, समस्या आईडी से जुड़ी होती है जो डेटाइम द्वारा "मोटे तौर पर क्रमबद्ध" होती है। इसके बारे में अधिक जानकारी:

• ULID (यूनिवर्सली यूनिक लेक्सिकोग्राफ़िक रूप से छांटने योग्य पहचानकर्ता)
• हिमपात का एक खंड
• MongoDB ऑब्जेक्टआईड

ये सभी विधियाँ एक ID का उपयोग करती हैं जो है:

[---- timestamp ----][---- random and/or incremental ----]

Answer 2

मैं SqlLite में विशेषज्ञ नहीं हूं, लेकिन मैंने डेटाबेस और समय श्रृंखला के साथ काम किया है। मेरे पास पहले जैसी ही स्थिति थी, और मैं अपने वैचारिक समाधान को साझा करता।

आपके पास अपने प्रश्न में उत्तर का कुछ हिस्सा है, लेकिन इसे करने का तरीका नहीं है।

जिस तरह से मैंने इसे किया, 2 टेबल, एक टेबल (main_logs) बनाकर सेकंड इन्क्रीमेंट में समय लॉग होगा जैसे कि प्राथमिक कुंजी के रूप में पूर्णांक और दूसरे टेबल लॉग में सभी लॉग्स (main_sub_logs) होते हैं जो उस विशेष स्थिति में बने होते हैं जो आपके मामले में हो सकते हैं इसमें प्रति सेकंड 10000 लॉग तक हो सकते हैं। Main_sub_logs में main_logs का संदर्भ होता है और इसमें प्रत्येक लॉग सेकंड के लिए होता है और X नंबर की लॉग्स स्वयं काउंटर आईडी के साथ उस सेकंड से संबंधित होती है, जो फिर से शुरू होती है।

इस तरह से आप अपनी समय श्रृंखला को एक ही स्थान पर सभी लॉग के बजाय इवेंट विंडो के सेकंड तक सीमित कर सकते हैं।

इस तरह से आप उन दो तालिकाओं में शामिल हो सकते हैं और जब आप पहली तालिका में 2 विशिष्ट समय के बीच से देखते हैं तो आपको बीच में सभी लॉग मिलते हैं।

तो यहाँ पर मैंने अपनी 2 टेबल कैसे बनाई:

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)
)

मैंने कुछ डमी डेटा डाला है:

अब 1608718655 और 1608718656 के बीच सभी लॉग को क्वेरी करने देता है

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

यह परिणाम मिलेगा: