PRIMARY KEY와 Rowid

모든 테이블엔 rowid가 있어 (거의)

기본적으로 SQLite 테이블에는 눈에 안 보이는 rowid가 하나씩 붙어 있어. 엔진이 row마다 매기는 64비트 정수인데, 인덱스와 join이 실제로 row를 찾아가는 내부 통로가 이거야. 직접 꺼내 볼 수도 있어.

SELECT rowid, * FROM users;

컬럼을 INTEGER PRIMARY KEY로 선언하면 — 정확히 그 표기여야 해, INT PRIMARY KEY는 안 돼 — SQLite가 그 컬럼을 rowid의 다른 이름으로 삼아. 컬럼이 rowid 가 되는 거지. 저장 공간이 더 들지도 않고 인덱스가 따로 생기지도 않아. SQLite가 어차피 관리하던 정수에 이름 하나 더 붙였을 뿐이야.

PK 모양은 이것 말고도 있어.

Composite PK — 컬럼 여러 개를 묶어서 PRIMARY KEY (a, b)로 잡는 거야.
WITHOUT ROWID 테이블 — UUID처럼 정수가 아닌 키를 쓸 때 유용해. 네가 선언한 컬럼이 실제 storage key가 돼.

Warning: INT PRIMARY KEY와 INTEGER PRIMARY KEY는 다른 물건이야. 앞의 것은 인덱스가 따로 붙는 평범한 컬럼이고, rowid alias 최적화는 INTEGER라고 정확히 써야만 켜져. 실수해도 insert는 멀쩡히 되니까 그대로 ship되기 쉬운데, 테이블이 커지면 성능이 조용히 주저앉아.

Code

Rowid alias — 같은 컬럼, 두 이름·sql

CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users(name) VALUES ('Alice'), ('Bob');

SELECT rowid, id, name FROM users;
-- 1 | 1 | Alice
-- 2 | 2 | Bob

-- 말 그대로 같은 컬럼
SELECT rowid = id FROM users;
-- 1
-- 1

정수 아닌 PK용 WITHOUT ROWID·sql

CREATE TABLE sessions (
  session_id TEXT PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) WITHOUT ROWID;

-- session_id 가 이제 실제 storage key.
-- UUID 류 테이블에선 B-tree level 한 층 절약.

Exercise

테이블을 세 개 만들어봐. INTEGER PRIMARY KEY를 쓴 것, INT PRIMARY KEY를 쓴 것, TEXT 키 위에 WITHOUT ROWID를 얹은 것. 각각에 row를 몇 개 넣고 SELECT rowid, *로 셋을 나란히 봐. 어디가 다른지 눈으로 확인하고, pragma_index_list(table)로 SQLite가 내부적으로 만든 인덱스가 몇 개인지도 비교해.

모든 테이블엔 rowid가 있어 (거의)

Code

External links

Exercise

Progress

댓글 0