CREATE INDEX로 인덱스 만들기
CREATE INDEX로 인덱스 만들기
SQLite에서 인덱스는 검색 속도를 높이는 핵심 수단이에요. CREATE INDEX 명령은 키워드 "CREATE INDEX", 새 인덱스 이름, "ON", 인덱스를 걸 테이블 이름, 그리고 인덱스 키로 쓸 컬럼 이름·표현식의 괄호 목록으로 구성돼요. 선택적으로 WHERE 절을 넣으면 "부분 인덱스(partial index)"가 돼요.
기본 문법
CREATE INDEX는 "CREATE INDEX" 키워드 다음에 새 인덱스 이름이 오고, "ON" 뒤에 기존 테이블 이름, 그리고 괄호 안에 인덱스 키로 사용할 컬럼 이름이나 표현식 목록이 와요. 선택적인 IF NOT EXISTS 절이 있고 같은 이름의 인덱스가 이미 있으면 이 명령은 아무것도 하지 않아요. 하나의 테이블에 붙일 수 있는 인덱스 수에는 임의의 제한이 없고, 인덱스의 컬럼 수는 sqlite3_limit(SQLITE_LIMIT_COLUMN,...)이 정한 값으로 제한돼요. 인덱스 제거는 DROP INDEX 명령으로 해요.
고유 인덱스 (Unique Index)
CREATE와 INDEX 사이에 UNIQUE 키워드가 있으면 중복 인덱스 항목이 허용되지 않아요. 중복 항목을 삽입하려 하면 오류가 나요. 고유 인덱스의 관점에서 모든 NULL 값은 다른 NULL 값과 다른 것으로 간주되어 서로 고유해요. 이것은 SQL-92 표준의 두 가지 해석 중 하나인데, SQLite가 택한 방식이에요. PostgreSQL·MySQL·Firebird·Oracle도 같은 해석을 따르고, Informix와 Microsoft SQL Server는 반대 해석(모든 NULL이 서로 같음)을 따라요.
표현식 인덱스
인덱스의 표현식은 다른 테이블을 참조할 수 없고, 서브쿼리나 결과가 바뀔 수 있는 함수(예: random(), sqlite_version())를 쓸 수 없어요. 표현식은 인덱스를 거는 테이블의 컬럼만 참조할 수 있어요. 표현식 인덱스는 3.9.0(2015-10-14) 이전 버전에서는 동작하지 않아요.
내림차순 인덱스
각 컬럼 이름이나 표현식 뒤에 ASC 또는 DESC 키워드로 정렬 순서를 붙일 수 있어요. 그런데 정렬 순서가 실제로 적용되는지는 데이터베이스 파일 형식, 특히 스키마 형식 번호에 따라 다를 수 있어요. "legacy" 스키마 형식(1)은 인덱스 정렬 순서를 무시하고, 내림차순 인덱스 스키마 형식(4)은 인덱스 정렬 순서를 반영해요. 3.3.0(2006-01-11) 이후 버전만 내림차순 인덱스 형식을 이해할 수 있어요. 3.7.10(2012-01-16) 이후 버전에서는 기본으로 새로운 형식을 쓰고, legacy_file_format PRAGMA로 특정 동작을 바꿀 수 있어요.
NULLS FIRST와 NULLS LAST
NULLS FIRST와 NULLS LAST 조건자는 인덱스에서 지원되지 않아요. 정렬 목적상 SQLite는 NULL을 다른 모든 값보다 작다고 봐요. 그래서 NULL은 항상 ASC 인덱스의 시작 부분에, DESC 인덱스의 끝 부분에 나타나요.
정렬 순서(Collation)
각 컬럼 이름이나 표현식 뒤에 선택적으로 붙는 COLLATE 절은 그 컬럼의 텍스트 항목에 쓸 정렬 순서를 정의해요. 기본 정렬 순서는 CREATE TABLE 문에서 그 컬럼에 정의된 정렬 순서고, 그게 없다면 내장 BINARY 정렬 순서를 사용해요.