AUTOINCREMENT vs 암묵적 Rowid

거의 항상: AUTOINCREMENT 쓰지 마

SQLite에 AUTOINCREMENT 키워드가 있긴 한데, 사람들이 기대하는 것보다 semantic이 미묘하고 기본 동작보다 느려.

그냥 INTEGER PRIMARY KEY — SQLite가 쓸 수 있는 다음 rowid를 골라. row를 지우면 엔진이 그 id를 다시 쓸 수도 있어.
INTEGER PRIMARY KEY AUTOINCREMENT — 여기에 보장이 하나 더 붙어. id가 항상 커지기만 하고, 지운 뒤에도 재사용하지 않아. 내부의 sqlite_sequence 테이블이 지금까지 나온 최대 rowid를 기억해.

대가가 있어. insert할 때마다 sqlite_sequence를 건드리니까 page write가 하나씩 더 붙어. 그런데 얻는 건 대개 필요가 없어. 앱 코드가 'DB가 id를 재사용하지 않는다'에 기대는 경우는 드물거든. 전역에서 겹치지 않는 id가 정말 필요하면 TEXT 컬럼에 UUID를 담고 WITHOUT ROWID를 쓰는 게 나아.

Warning: 테이블에 AUTOINCREMENT를 한 번 박으면 CTAS dance 없이는 못 빼. 재사용 금지 보장이 정말 필요한지 미리 결정해.

Code

Default vs AUTOINCREMENT — 차이 관찰·sql

-- Default: 삭제 후 id 재사용 가능
CREATE TABLE a(id INTEGER PRIMARY KEY, n TEXT);
INSERT INTO a(n) VALUES ('one'), ('two'), ('three');
DELETE FROM a WHERE id = 3;
INSERT INTO a(n) VALUES ('four');
SELECT * FROM a;
-- 1 | one
-- 2 | two
-- 3 | four    <- 재사용!

-- AUTOINCREMENT: 재사용 안 함
CREATE TABLE b(id INTEGER PRIMARY KEY AUTOINCREMENT, n TEXT);
INSERT INTO b(n) VALUES ('one'), ('two'), ('three');
DELETE FROM b WHERE id = 3;
INSERT INTO b(n) VALUES ('four');
SELECT * FROM b;
-- 1 | one
-- 2 | two
-- 4 | four    <- 3 건너뜀

Exercise

위 demo를 네 머신에서 그대로 돌려봐. 그다음 한 페이지짜리 노트를 써. 재사용 금지 보장이 정말 중요한 앱 시나리오는 뭐고, 어디서는 그냥 짐일 뿐인지. 각각 실제 예를 붙여. 보너스로, WITHOUT ROWID를 써서 같은 효과를 내는 UUID 키 테이블을 디자인해봐.

거의 항상: AUTOINCREMENT 쓰지 마

Code

External links

Exercise

Progress

댓글 0