실행 가능한 SQLite 문서: 인덱스

Q: SQLite에서 인덱스는 어떻게 만드나요?

기본 문법은 CREATE INDEX indexname ON tablename(columnname); 입니다. 유일성 제약을 같이 걸고 싶다면 CREATE UNIQUE INDEX를 쓰면 되고요. 한 가지 주의할 점은 인덱스 이름이 테이블 단위가 아니라 데이터베이스 전체에서 유일해야 한다는 겁니다. 삭제할 때는 DROP INDEX indexname;.

Q: 인덱스는 언제 추가하는 게 좋나요?

WHERE, JOIN, ORDER BY에 자주 쓰이는 컬럼, 특히 테이블이 크고 그중 일부 행만 추려내는 쿼리에 인덱스가 잘 먹습니다. 다만 모든 컬럼에 인덱스를 거는 건 피해야 해요. 인덱스 하나하나가 INSERT, UPDATE, DELETE 속도를 느리게 만들고 디스크 공간도 잡아먹습니다. 만든 다음에는 꼭 EXPLAIN QUERY PLAN으로 플래너가 실제로 그 인덱스를 쓰는지 확인하세요.

Q: 인덱스를 만들었는데 SQLite가 왜 안 쓰나요?

흔한 원인 몇 가지가 있습니다. 테이블이 작아서 풀 스캔이 더 빠른 경우, 컬럼을 함수로 감싼 경우(WHERE lower(email) = ...는 email 인덱스를 못 탑니다), 인덱스가 없는 컬럼과 OR로 묶인 조건, 또는 통계가 오래된 경우입니다. ANALYZE로 통계를 갱신하고 EXPLAIN QUERY PLAN으로 플래너가 어떤 경로를 골랐는지 직접 확인해보세요.

Q: 특정 테이블의 인덱스 목록은 어떻게 보나요?

특정 테이블이라면 PRAGMA indexlist('tablename');이 가장 간단합니다. 전체를 보고 싶다면 sqlitemaster를 직접 조회하면 되고요: SELECT name, sql FROM sqlitemaster WHERE type = 'index';. 결과에 보이는 sqliteautoindex*는 PRIMARY KEY나 UNIQUE 제약 때문에 SQLite가 자동으로 만든 인덱스입니다.

인덱스가 정확히 무엇인가

인덱스(index)는 별도로 존재하는 자료구조입니다. 정렬된 B-tree 형태로 만들어져 있어서, 특정 컬럼 값으로 행을 찾을 때 SQLite가 테이블 전체를 훑지 않고도 바로 찾아갈 수 있게 해주죠. 인덱스가 없다면 WHERE email = 'rosa@example.com' 같은 쿼리는 모든 행을 하나씩 읽으면서 비교해야 합니다. 반면 email에 인덱스를 걸어두면 SQLite는 트리를 따라 약 log(n) 단계만 거쳐 곧장 일치하는 행으로 점프합니다.

물론 이 속도 향상이 공짜는 아닙니다. 인덱스는 인덱싱된 컬럼의 값과 원본 행을 가리키는 포인터를 함께 담은 일종의 사본이거든요. 그래서 INSERT, 인덱스 컬럼에 대한 UPDATE, DELETE가 일어날 때마다 인덱스도 같이 갱신해야 합니다. 디스크 사용량은 늘고, 쓰기 처리량은 약간 떨어지죠. 결국 거래는 단순합니다. 쓰기에서 조금 손해 보는 대신 읽기에서 훨씬 큰 이득을 챙기는 겁니다.

sqlite 인덱스 생성: CREATE INDEX 사용법

기본 문법은 다음과 같습니다.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT,
    created_at TEXT
);

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

네이밍 컨벤션은 보통 idx_<테이블>_<컬럼> 형태를 많이 씁니다. 이렇게 해두면 어떤 인덱스인지 한눈에 보이거든요. 참고로 인덱스 이름은 테이블 단위가 아니라 데이터베이스 전체에서 유일해야 하기 때문에, 테이블명을 같이 넣는 거예요.

삭제할 때는 이렇게 합니다:

DROP INDEX idx_users_email;

인덱스는 순전히 성능을 위한 보조 장치라고 보면 됩니다. 인덱스를 삭제해도 데이터 자체에는 전혀 영향이 없고, 단지 쿼리 속도만 달라질 뿐이죠.

sqlite UNIQUE 인덱스

UNIQUE 인덱스는 두 가지 역할을 한 번에 합니다. 조회 속도를 빠르게 해주는 동시에, 인덱스가 걸린 값에 중복이 들어오지 못하도록 막아줍니다.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

세 번째 INSERT는 UNIQUE constraint failed: accounts.username 에러로 실패합니다. SQLite는 PRIMARY KEY와 UNIQUE 컬럼에 대해 유니크 인덱스를 자동으로 만들어 주는데, 이때 이름은 sqlite_autoindex_<table>_<n> 형태로 붙습니다. 따라서 CREATE UNIQUE INDEX를 직접 작성해야 하는 경우는 테이블 정의에 제약을 걸어 두지 않았을 때뿐입니다.

쿼리 플래너는 실제로 무슨 일을 하는가

인덱스를 만들었다고 해서 SQLite가 무조건 그걸 쓰는 건 아닙니다. 쿼리 플래너는 쿼리마다 실행 전략을 새로 고르며, 어떤 전략이 선택됐는지는 EXPLAIN QUERY PLAN으로 확인할 수 있습니다:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL,
    status TEXT
);

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

출력 결과에서 SEARCH ... USING INDEX idx_orders_customer라는 문구를 찾아보세요. 이게 보이면 인덱스를 제대로 타고 있다는 뜻입니다. 반대로 SCAN orders가 나온다면, 옵티마이저가 풀 테이블 스캔이 더 싸다고 판단했거나(데이터가 적을 땐 실제로 그게 맞을 때가 많습니다) 쿼리 작성 방식 때문에 인덱스를 못 쓰는 상황입니다. 실행 계획을 읽는 방법은 이어지는 문서에서 자세히 다룹니다.

sqlite 인덱스 안 탈 때 — 흔한 함정들

인덱스에는 잘 알려진 사각지대가 몇 가지 있습니다. 아래 패턴들은 모두 email 컬럼의 인덱스를 무력화시킵니다.

-- 関数がカラムをラップしている
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- LIKE の先頭にワイルドカード
SELECT * FROM users WHERE email LIKE '%@example.com';

-- 型の不一致により変換が強制される
SELECT * FROM users WHERE email = 12345;

B-tree는 가공되지 않은 email 값을 기준으로 정렬되어 있기 때문에, 쿼리 시점에 컬럼을 변형하는 순간 풀스캔으로 빠집니다. 해결 방법은 몇 가지가 있어요. 아예 정규화된 형태로 저장해 두거나(email_lower 컬럼 추가), 표현식 인덱스(CREATE INDEX idx ON users(lower(email)))를 만들거나, 부분 문자열 검색이 필요하다면 SQLite의 전문 검색(FTS)을 쓰면 됩니다.

sqlite 커버링 인덱스

쿼리에 필요한 모든 컬럼이 인덱스 안에 들어 있으면, SQLite는 테이블을 아예 건드리지 않고도 결과를 돌려줄 수 있습니다. 이걸 커버링 인덱스(covering index) 라고 불러요. 핵심은 인덱스 정의에 필요한 컬럼들을 함께 넣어두는 겁니다.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    type TEXT,
    created_at TEXT
);

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

쿼리가 요청하는 두 컬럼이 모두 인덱스 안에 들어 있기 때문에, SQLite는 USING COVERING INDEX라고 알려줍니다. 실제 행을 읽으러 갈 필요가 없다는 뜻이죠. 자주 호출되는 읽기 경로에서 sqlite 커버링 인덱스는 가장 효과가 큰 최적화 중 하나입니다. 대신 인덱스 크기가 커진다는 트레이드오프가 있죠. 다중 컬럼 인덱스는 별도의 주제라, 다음 문서에서 제대로 다루겠습니다.

sqlite 인덱스 목록 조회 및 확인하기

지금 어떤 인덱스가 있는지 확인하는 방법은 두 가지입니다:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT UNIQUE,
    name TEXT
);

CREATE INDEX idx_products_name ON products(name);

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

이렇게 하면 데이터베이스에 있는 모든 인덱스를 CREATE 문과 함께 확인할 수 있습니다. 특정 테이블만 보고 싶다면 PRAGMA index_list('products');로 해당 테이블의 인덱스만 조회할 수 있고, PRAGMA index_info('idx_products_name');로 각 인덱스가 어떤 컬럼을 대상으로 하는지 확인할 수 있습니다. 이름이 sqlite_autoindex_로 시작하는 건 PRIMARY KEY나 UNIQUE 제약 조건 때문에 자동으로 생성된 것이라 삭제할 수 없습니다.

인덱스를 만들면 안 되는 경우

오히려 인덱스를 추가했을 때 상황이 나빠지는 경우도 꽤 있습니다.

데이터가 적은 테이블. 수백 행 정도는 마이크로초 단위로 풀스캔이 끝납니다. 옵티마이저도 어차피 인덱스를 무시할 가능성이 높고, 결국 쓰기 비용만 늘어나는 셈이죠.
자주 조회하지 않는데 쓰기가 많은 컬럼. 모든 쓰기 작업은 모든 인덱스를 같이 갱신합니다. 거의 필터로 쓰지도 않는 컬럼에 인덱스를 걸면 비용만 발생합니다.
카디널리티가 낮은 단일 컬럼. 값이 세 종류뿐인 status 컬럼에 인덱스를 걸어봐야 후보를 거의 좁히지 못합니다. 복합 인덱스의 두 번째 컬럼으로 쓰거나 부분 인덱스(partial index) 형태라면 도움이 되지만, 단독으로는 보통 손해입니다.
이미 다른 인덱스에 포함된 경우. (a, b) 인덱스가 있다면 (a) 인덱스를 따로 만들 필요가 없습니다. SQLite는 a만 필터링하는 쿼리에서도 복합 인덱스의 선행 컬럼을 활용합니다.

"이 인덱스, 만들어야 할까?"에 대한 솔직한 답은 거의 항상 같습니다. 일단 만들어 보고, EXPLAIN QUERY PLAN으로 확인하고, 실제 데이터 규모로 측정한 뒤에 판단하세요.

다음 글: 복합 인덱스

단일 컬럼 인덱스만으로도 꽤 많은 케이스를 커버할 수 있지만, 실무 쿼리는 여러 컬럼을 동시에 필터링하고 정렬하는 경우가 많습니다. (a, b, c) 같은 복합 인덱스가 이런 상황을 처리해 주는데, 여기서 컬럼 순서는 생각보다 훨씬 중요합니다. 이 내용은 다음 페이지에서 이어가겠습니다.

자주 묻는 질문

SQLite에서 인덱스는 어떻게 만드나요?

기본 문법은 CREATE INDEX index_name ON table_name(column_name); 입니다. 유일성 제약을 같이 걸고 싶다면 CREATE UNIQUE INDEX를 쓰면 되고요. 한 가지 주의할 점은 인덱스 이름이 테이블 단위가 아니라 데이터베이스 전체에서 유일해야 한다는 겁니다. 삭제할 때는 DROP INDEX index_name;.

인덱스는 언제 추가하는 게 좋나요?

WHERE, JOIN, ORDER BY에 자주 쓰이는 컬럼, 특히 테이블이 크고 그중 일부 행만 추려내는 쿼리에 인덱스가 잘 먹습니다. 다만 모든 컬럼에 인덱스를 거는 건 피해야 해요. 인덱스 하나하나가 INSERT, UPDATE, DELETE 속도를 느리게 만들고 디스크 공간도 잡아먹습니다. 만든 다음에는 꼭 EXPLAIN QUERY PLAN으로 플래너가 실제로 그 인덱스를 쓰는지 확인하세요.

인덱스를 만들었는데 SQLite가 왜 안 쓰나요?

흔한 원인 몇 가지가 있습니다. 테이블이 작아서 풀 스캔이 더 빠른 경우, 컬럼을 함수로 감싼 경우(WHERE lower(email) = ...는 email 인덱스를 못 탑니다), 인덱스가 없는 컬럼과 OR로 묶인 조건, 또는 통계가 오래된 경우입니다. ANALYZE로 통계를 갱신하고 EXPLAIN QUERY PLAN으로 플래너가 어떤 경로를 골랐는지 직접 확인해보세요.

특정 테이블의 인덱스 목록은 어떻게 보나요?

특정 테이블이라면 PRAGMA index_list('table_name');이 가장 간단합니다. 전체를 보고 싶다면 sqlite_master를 직접 조회하면 되고요: SELECT name, sql FROM sqlite_master WHERE type = 'index';. 결과에 보이는 sqlite_autoindex_*는 PRIMARY KEY나 UNIQUE 제약 때문에 SQLite가 자동으로 만든 인덱스입니다.

SQLite 인덱스: CREATE INDEX 사용법과 성능 가이드