-
[SQLite] 인덱스(INDEX)Software/SQLite 2018. 3. 19. 00:25반응형
인덱스(INDEX)는 우리가 책에서 볼 수 있는 색인을 생각하면 쉽게 이해할 수 있다. 우리는 책의 색인을 통해서 우리가 찾고자 하는 키워드가 어느 페이지에 있는 지 쉽게 찾을 수 있다.
SQLite에서의 인덱스 또한 이 색인과 동일한 기능과 역할을 한다. 인덱스를 정의하면 마치 책의 색인과 같은 자료구조 형태로 해당 열을 정렬해서 관리할 수 있다. 그렇기에 인덱스가 정의된 경우 단순 검색보다 더 빠른 검색 결과를 얻을 수 있다. 다만 인덱스를 쓰고 변경하는 데 추가적인 시간이나 공간이 필요하다는 단점이 존재한다.
- 인덱스 사용의 장/단점
인덱스 사용의 장점
- 검색 - 일치 검색('='), 범위 검색 ('<,>')의 성능 향상
- 필터링/정렬 연산의 성능이 향상
인덱스 사용의 단점
- 레코드 삽입/삭제/변경 시 인덱스를 갱신하기 위한 시간이 추가되어 성능 저하가 발생
- 인덱스 저장을 위한 추가적인 공간 사용
- 인덱스 생성
인덱스 생성은 아래와 같은 구문으로 가능 하다.
CREATE INDEX [인덱스 이름] ON [테이블 이름] ([필드][정렬 방법], ...);
여기서 정렬 방법은 생략 가능하며 만약 인덱스를 설정할 필드가 2개 이상이라면 '," 구분자를 사용해서 정의하면 된다. 또한 생성할 수 있는 인덱스의 개수 제한은 없다.
이제 한번 cars Table의 name 필드에 인덱스를 설정해 보도록 하겠다.
sqlite> CREATE INDEX idxName ON cars(name ASC);
여기서 ASC(Ascent) 오름차순의 경우 정렬 방법 기본값이 오름차순이기 때문에 생략가능 하다. 그러나 만약 내림차순 (DESC)으로 정렬하고 싶다면 DESC를 필드 이름 뒤에 붙여 주어야 한다.
자 이제 인덱스 설정이 잘 되었는 지 한번 확인해 보자. 확인 하는 방법은 EXPLAIN QUERY PLAN을 통해 가능하다.
sqlite> EXPLAIN QUERY PLAN
...> SELECT * FROM cars ORDER BY name;
selectid order from detail
---------- ---------- ---------- -----------------------------------
0 0 0 SCAN TABLE cars USING INDEX idxName
sqlite>
- UNIQUE 인덱스
특정 컬럼에 중복된 데이터가 입력되지 않도록 하기 위한 UNIQUE INDEX를 설정 할 수 있다.
CREATE 와 INDEX 키워드 사이에 UNIQUE 키워드를 추가하면 된다.
sqlite> CREATE UNIQUE INDEX idxName_uq ON cars(name ASC);
sqlite> EXPLAIN QUERY PLAN
...> SELECT * FROM cars ORDER BY name;
selectid order from detail
---------- ---------- ---------- --------------------------------------
0 0 0 SCAN TABLE cars USING INDEX idxName_uq
그런데 만약 이미 테이블에 레코드가 존재하는 경우에 UNIQUE 인덱스 생성을 위해서는 해당 필드에 중복 값이 저장되어 있지 않아야 한다.
- 다중 컬럼 인덱스
조건절이나 조건 정렬이 하나 이상으로 복잡하게 되어 있는 경우 더욱 빠른 검색 성능을 위해 다중 컬럼 인덱스 설정이 가능하다. 2개 이상의 컬럼에 동일 인덱스를 설정하는 것으로 콤마 "," 구분자를 통해 다중으로 설정 가능하다.
sqlite> CREATE INDEX indexNamPrice ON cars (name ASC, prcie DESC);
sqlite> EXPLAIN QUERY PLAN
...> SELECT * FROM cars ORDER BY name;
selectid order from detail
---------- ---------- ---------- -----------------------------------------
0 0 0 SCAN TABLE cars USING INDEX indexNamPrice
- 인덱스 삭제
DROP INDEX 구문을 통해 기존의 정의한 인덱스를 삭제할 수 있다.
DROP INDEX [인덱스 이름];
기존에 만들었던 인덱스 indexNamPrice에 오타가 있어 이를 삭제하고 indexNamePrice로 다시 설정해 보겠다.
sqlite> DROP INDEX indexNamPrice;
sqlite> CREATE INDEX indexNamePrice ON cars (name ASC, price DESC);
sqlite> EXPLAIN QUERY PLAN
...> SELECT * FROM cars ORDER BY name;
selectid order from detail
---------- ---------- ---------- ------------------------------------------
0 0 0 SCAN TABLE cars USING INDEX indexNamePrice
만약 삭제하고자 하는 인덱스가 존재하지 않는 경우 "no such index: " Error를 발생 시키기 때문에 이를 회피하기 위해 IF EXISTS 구문을 사용해야 한다. INDEX 구문 뒤 [인덱스 이름] 앞에 해당 구문을 추가 하면 된다.
DROP INDEX IF EXISTS [인덱스 이름];
반응형'Software > SQLite' 카테고리의 다른 글
[SQLite] 뷰 (VIEW) (0) 2018.03.25 [SQLite] 테이블 제약 조건 (0) 2018.03.19 [SQLite] 외부 조인 (OUTER JOIN) (0) 2018.01.20 [SQLite] 자연 조인(NATURAL JOIN) (0) 2018.01.20 [SQLite] 자체 조인 (SELF JOIN) (0) 2018.01.14 댓글