빠른 시간 범위 쿼리와 함께 Sqlite에서 시계열을 사용하는 방법은 무엇입니까?

Dec 23 2020

Unix 타임 스탬프 열이있는 Sqlite 데이터베이스에 이벤트를 기록한다고 가정 해 보겠습니다 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).

그러면 문제가 해결되지만 O (log n)에서 직접 B- 트리 검색을 사용할 수있는 이미 증가하는 시퀀스 / 이미 정렬 된 열에 대한 인덱스를 유지해야하는 것은 오히려 좋지 않은 솔루션 입니다. 내부적으로 이것은 인덱스가됩니다.ts

ts           rowid
1608000001   1
1608000002   2
1608000012   3
1608000077   4

이는 DB 공간 (및 쿼리가 인덱스를 먼저 살펴 봐야하는 경우 CPU)의 낭비입니다.

이를 방지하려면 :

(1) 우리가 사용할 수있는 ts등 INTEGER PRIMARY KEY, 그래서 ts것 rowid자체. 그러나 이것은 ts고유 하지 않기 때문에 실패합니다 . 2 개의 이벤트가 동일한 초 (또는 동일한 밀리 초)에 발생할 수 있습니다.

예를 들어 SQLite Autoincrement에 제공된 정보를 참조하십시오 .
(2) 증가하는 숫자와 연결된 rowid타임 스탬프로 사용할 ts수 있습니다. 예:
```
 16087186540001      
 16087186540002
 [--------][--]
     ts     increasing number 
```
그런 다음 rowid고유하고 엄격하게 증가하며 (초당 이벤트 수가 10,000 개 미만인 경우) 인덱스가 필요하지 않습니다. 쿼리 WHERE ts BETWEEN a AND b는 단순히 WHERE rowid BETWEEN a*10000 AND b*10000+9999.

그러나 주어진 값보다 크거나 같은 INSERT항목에 Sqlite를 요청하는 쉬운 방법이 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
```

더 일반적으로 빠른 쿼리를 위해 Sqlite로 시계열을 최적으로 만드는 방법은 WHERE timestamp BETWEEN a AND b무엇입니까?

답변

4 Basj Dec 24 2020 at 04:49

첫 번째 솔루션

질문에 설명 된 방법 (2)이 잘 작동하는 것 같습니다. 벤치 마크에서 다음을 얻었습니다.

순진한 방법, 인덱스 없음 : 18MB 데이터베이스, 86ms 쿼리 시간
순진한 방법, 인덱스 포함 : 32MB 데이터베이스, 12ms 쿼리 시간
방법 (2) : 18MB 데이터베이스, 12ms 쿼리 시간

요점은으로 사용 dt하기위한 INTEGER PRIMARY KEY것이므로 B- 트리를 사용하는 행 ID 자체 ( SQLite의 기본 키에 인덱스가 필요합니까? 참조 )가 되고 또 다른 숨겨진 열 이 없습니다rowid . 따라서 우리는 correspondance에 만들 것 별도의 인덱스를 피하기 dt => rowid: 여기 dt 입니다 행 번호입니다.

또한 마지막으로 추가 된 ID를 추적 AUTOINCREMENT하는 sqlite_sequence테이블 을 내부적으로 생성하는 방법 도 사용 합니다. 이것은 삽입 할 때 유용합니다. 두 이벤트가 초 단위로 동일한 타임 스탬프를 가질 수 있기 때문에 (밀리 초 또는 마이크로 초 타임 스탬프에서도 가능할 수 있으므로 OS가 정밀도를자를 수 있음) 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`테이블 사용

다음은 8ms의 쿼리 시간 WITHOUT ROWID을 제공하는 또 다른 방법입니다 . 를 사용할 때 AUTOINCREMENT를 사용할 수 없기 때문에 자동 증가 ID를 직접 구현해야합니다 . a를 사용하고 추가 열 을 사용 하지 않으려 고 할 때 유용합니다 . 대신 하나의 B-트리있는의 와 하나의 B-트리를 , 우리는 단지 하나해야합니다.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)

대략적으로 분류 된 UUID

보다 일반적으로 문제는 날짜 시간별로 "대략 정렬 된"ID와 관련이 있습니다. 이에 대한 추가 정보 :

ULID (Universally Unique Lexicographically Sortable Identifier)
눈송이
MongoDB ObjectId

이 모든 방법은 다음과 같은 ID를 사용합니다.

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

2 maytham-ɯɐɥʇʎɐɯ Dec 26 2020 at 16:59

저는 SqlLite 전문가는 아니지만 데이터베이스 및 시계열 작업을 해왔습니다. 이전에 비슷한 상황이 있었으며 개념적 솔루션을 공유 할 것입니다.

질문에 대한 답변의 일부가 있지만 방법은 없습니다.

내가 한 방식으로 2 개의 테이블을 생성하면 하나의 테이블 (main_logs)은 기본 키로 정수로 날짜로 초 단위로 시간을 기록하고 다른 테이블 로그에는 귀하의 경우에 할 수있는 특정 시간에 만든 모든 로그 (main_sub_logs)가 포함됩니다. 초당 최대 10000 개의 로그가 있어야합니다. main_sub_logs는 main_logs에 대한 참조를 가지고 있으며 각 로그에 대해 포함하며 X 개의 로그는 자신의 카운터 ID와 함께 해당 초에 속하며 다시 시작됩니다.

이런 식으로 시계열 조회를 한 곳에서 모든 로그가 아닌 몇 초의 이벤트 창으로 제한 할 수 있습니다.

이렇게하면 두 테이블을 조인 할 수 있으며 두 특정 시간 사이에 첫 번째 테이블에서 조회 할 때 그 사이에 모든 로그를 얻을 수 있습니다.

빠른 시간 범위 쿼리와 함께 Sqlite에서 시계열을 사용하는 방법은 무엇입니까?

답변

첫 번째 솔루션

WITHOUT ROWID테이블 사용

대략적으로 분류 된 UUID

`WITHOUT ROWID`테이블 사용