[SQLite3] 인덱스 활용하기

SQLite3란?

지난 번에 기록했던 Cloudflare의 D1은 SQLite 베이스의 서비스이다. 그러므로 SQLite에 대해 알아보도록하자. SQLite는 파일 시스템에 직접 파일을 저장한다. 다른 데이터베이스들도 그렇지만 Sqlite는 특히 그 간단한 사용방법으로 선호받고 있다. 하지만 성능도 요즘에 들어선 그렇게 나쁘지 않다. 대규모 프로젝트를 위해선 좀 다르겠지만 소규모 프로젝트에는 차고 넘치는 성능이다.

서버리스와 로컬 데이터베이스에 적합한 구조

별도의 서버 프로세스가 필요하지 않다는 것이 큰 장점이다. 별도의 서버 프로세스가 필요하지 않으므로 다른 서버를 사용하기 위해 해야했던 설치, 세팅 과정이 생략된다. 뭐 하나만 개발하려고 해도 클라이언트 받고 서버 파일 받아서 설치하느라 시간이 많이 갔던 개발자들에겐 더할나위 없이 좋다.

그러므로 앱 개발을 한다던지 할때 자체적으로 서버를 구성해서 데이터를 저장할 경우나, 요즘같은 경우엔 웬만한 규모의 서비스에도 편리하게 사용되고 있다. 실제로 사용해보면 사용하다가 데이터베이스를 갈아끼운다던지 하는 게 굉장히 편리하다. 로컬에서 웹서버를 구성해서 테스트할때도 새로 테이블을 드랍하고 만들고 그런 일 없이 바로 파일을 만들면된다.

자주 쓰이는 곳

주로 장고같은 웹 프레임워크에서 쓰이며 IOS, Android에서 아주 쉽게 활용할 수 있다.

또한 웹브라우저 내에서도 편리하게 사용된다

실제로 사용해보기

SQLite를 실제로 사용해보자 파이썬을 이용해서 구현할텐데 실제로 아주 간단하게 구현할 수 있다.

import sqlite3

# 파일이 없는 경우 새로 생성한다.

conn = sqlite3.connect('tutorial.db')

# 이제 커서를 생성해서 여기서 작업을 하면 된다. 터미널에서의 커서라고 생각해도 된다.

cursor = conn.cursor()

# users 테이블 생성

cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
''')

# 데이터를 넣자

cursor.execute('''
INSERT INTO users (name, email) VALUES (?, ?)
''', ('밥말리', 'dont@worry.com'))
cursor.execute('''
INSERT INTO users (name, email) VALUES (?, ?)
''', ('밥말리2', 'be@happy.com'))

# 저장

conn.commit()

# 데이터를 불러오자

cursor.execute('SELECT \* FROM users')
rows = cursor.fetchall()

for row in rows:
print(row)

# 연결을 닫아준다

conn.close()

위 코드를 하나하나 살펴보면 이렇다

import sqlite3

우선 sqlite3를 임포트해야한다 없다면 pip를 통해 설치하도록하자.

conn = sqlite3.connect('tutorial.db')

위에서 말한 것처럼 sqlite의 기반이 되는 파일을 생성하는 것이다. 없다면 생성하고 있다면 연결한다.

cursor = conn.cursor()

이건 커서를 연결한다고 하는데 데이터베이스에서 실제로 작업할 위치를 잡는 것이다. 터미널에서의 커서라고 이해해도 된다.

cursor.execute('''

CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
''')

이 부분을 통해 users라는 테이블을 생성할건데 이미 있다면 생성하지 않는다. AUTOINCREMENT는 자동으로 증가하는 숫자 필드로 index역할을 한다. 나머지는 name과 email을 설정했다. 혹시 SQL문법이 어렵다면 여기를 참고하자. SQL문법보기

cursor.execute('''

INSERT INTO users (name, email) VALUES (?, ?)
''', ('밥말리', 'dont@worry.com'))
cursor.execute('''
INSERT INTO users (name, email) VALUES (?, ?)
''', ('밥말리2', 'be@happy.com'))

이제 데이터를 넣어준다. 위에서 만들었던 컬럼대로 name과 email을 넣는다. id를 넣지 않는건 자동으로 생성해주기 떄문이다.

conn.commit()

그후엔 커밋을 해준다.

cursor.execute('SELECT * FROM users')

rows = cursor.fetchall()

for row in rows:
print(row)

이제 데이터를 불러올건데 SQL문법이다. users 테이블에서 모든 행을 가져온다는 뜻이다. 그 다음에 하나하나 for문을 통해 프린트한다. conn.close()

이렇게하면 연결이 종료된다.

생각보다 간단하다. 설치가 이렇게 몇줄로 끝나기 때문에 빠른 테스트 빠른 삭제가 가능하다. 로컬 개발에서도 편하고 그렇다하여 성능도 그렇게 나쁘지 않아 몇십만개의 데이터정도는 가뿐하게 처리할 수 있다. 개인용 소규모 프로젝트에는 이만한 것이 없는 것 같다.

MySQL, PostgreSQL과의 비교

매우 인기있는 두 데이터베이스와 비교하면 SQLite를 이해하기 더 쉬울 것이다.

저장방법

MySQL은 아까 말했듯이 클라이언트, 서버가 있는 관계형 데이터비에스이다. 그러나 SQLite는 파일 기반이므로 그 자체로 서버리스라고 할 수 있다.

레이스컨디션 문제

MySQL은 레이스컨디션을 다루기가 상대적으로 쉽다. 여러 사용자가 동시에 데이터베이스에 접근할 때 그걸 처리하기가 쉽다. 그러나 SQLite는 그렇지 않다. 쓰기 작업시 발생하는 락을 잘 처리해야 하는 문제가 있다. 가벼운만큼 그런 문제를 처리해야한다.

대용량 처리시 문제

MySQL은 대용량 처리에도 상대적으로 좋다. 매우 큰 서비스들도 사용할만큼 안정성이 높다. 그러나 SQLite는 MySQL과 비교하면 상대적으로 부족한 성능을 보인다. 그러므로 초반엔 SQLite를 쓰더라도 어느정도 서비스가 커지면 갈아탈 각오를 하자. 그게 아니라면 소규모 프로젝트에 사용하자.

Nested 쿼리

SQLite는 다음과 같은 요청을 무조건 Nested 쿼리로 실행한다. 그렇기때문에 제약이 있다. 예를들면,

select *

from edge AS edge,
node AS node
where node.name = '애냐'
and edge.orig = node.id

이런 SQL문이 있다면 이거를 파이썬으로 좀 이해하기 쉽게 변형하면

nodes = [
{"id": 1, "name": "애냐"},
{"id": 2, "name": "밥"},
]

edges = [
{"orig": 1, "dest": 3},
{"orig": 2, "dest": 4},
]

joined_data = []

# 노드 중에서 이름이 '애냐'인 노드를 찾는 루프
for node in nodes:
if node["name"] == "애냐":
  # '애냐'인 노드에 대해서 orig가 해당 노드의 id와 일치하는 엣지를 찾는다
  for edge in edges:
      if edge["orig"] == node["id"]:
          # 찾아낸 노드와 엣지를 리스트에 추가하자
          joined_data.append({"node": node, "edge": edge})

for data in joined_data:
print(data)

이렇다. 이렇게 코드가 무조건 nested loop로 돌기 때문에 이걸 유의해야 한다.

파일변경 참조

SQLite는 내부적으로 데이터를 변경할때 원본 데이터를 다른 파일에 저장한 후, 데이터를 변경하고, 백업 데이터를 제거하는 순으로 진행된다. 파일 수정이 실제로는 갈아치기와 같은 것이다.