[SQL 첫걸음] 6장.데이터베이스 객체 작성과 삭제 - 29.인덱스 작성과 삭제

저자 : 아사이 아츠시

출판 : 한빛미디어 

발매 : 2015.11.01

1. 인덱스 작성

CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ...)

ex) CREATE INDEX isample65  ON sample62(no); -> sample62테이블의 no열에 isample65 라는 인덱스를 지정. 

- 인덱스는 이름을 붙여 관리하는데, 데이터베이스 객체가 될지 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다름.

- Oracle이나 DB2 등에서 인덱스는 스키마 객체. 따라서 스키마 내에 이름이 중복하지 않도록 지정.  /  SQL Server나 MySQL에서 인덱스는 테이블 내의 객체. 따라서 테이블 내에 이름이 중복하지 않도록 지정. => 인덱스의 네임스페이스가 디비제품마다 다름. 

- 인덱스를 작성할 때는 해당 인덱스가 어느 테이블의 어느 열에 관한 것인지 지정해야 함. 열은 복수로도 지정 가능. 

- 인덱스를 작성할 때는 저장장치에 색인용 데이터가 만들어짐. 행이 많을 수록 시간도 많이 걸리고 저장공간도 많이 소비. 

 

2. 인덱스 삭제 

- 스키마 객체의 경우

DROP INDEX 인덱스명

- 테이블 내 객체의 경우 

DROP INDEX 인덱스명 ON 테이블명 

ex) DROP INDEX isample65  ON sample62;

- DROP 할때는 다른 객체와 동일하게 인덱스 이름만 지정. 다만 테이블 내 객체로서 작성하는 경우에는 테이블명도 지정.(이때 인덱스를 구성하는 열은 지정 X) 

- DROP TABLE로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동으로 삭제됨.

-인덱스를 작성해두면 검색이 빨라짐.  작성한 인덱스의 열을 WHERE구의 조건식에 지정하여 SELECT명령으로 검색하면 처리속도가 향상됨. 한편, INSERT명령의 경우에는 인덱스를 최신 상태로 갱신하는 처리가 늘어나므로 처리속도가 조금 떨어짐.

ex)  CREATE INDEX isample65 ON sample62(a); 명령으로 인덱스를 작성했다고 가정하면,

SELECT * FROM sample62 WHERE a = 'a' 이 와 같이 WHERE구에 a열에 대한 조건식을 지정한 경우 SELECT명령은 인덱스를 사용해 빠르게 검색할 수 있음. 그러나 WHERE구의 조건식에 a열이 전혀 사용되지 않으면 SELECT명령은 isample65라는 인덱스를 사용할 수 없음. 

3. EXPLAIN 

EXPLAIN SQL명령 

- 실제로 인덱스를 사용해 검색하는지를 확인하려면 EXPLAIN 명령을 사용.

- EXPLAIN에 뒤이에 확인하고 싶은 SELECT명령 등의 SQL명령을 지정. 어떤 상태로 실행되는지를 데이터베이스가 설명해줌. 

- 표준SQL에는 존재하지않는, 데이터베이스 제품 의존형 명령이지만 어떤 데이터베이스 제품이라도 이와 비슷한 명령을 지원 

EXPLAIN으로 인덱스 사용 확인(MySQL)

- sample62의 a열에는 isample65라는 인덱스가 작성되어 있음. EXPLAIN의 뒤를 잇는 SELECT명령은 a열 값을 참조해 검색하므로 isample65을 사용해 검색. 

( possible_keys 라는 곳에 사용될 수 있는 인덱스가 표시되며, key는 사용된 인덱스가 표시됨. ) 

- WHRER구에  a열을 사용하지 않도록 조건을 변경하면 인덱스를 사용할 수 없음. 

( possible_keys 와 key가 NULL이 됨. ) 

4. 최적화

- SELECT명령을 실행할 때 인덱스의 사용 여부를 선택함. 이는 데이터베이스 내부의 최적화에 의해 처리되는 부분. 

내부 처리에서는 SELECT명령을 실행하기 앞서 실행 계획을 세움. 실행 계획에선 '인덱스가 지정된 열이 WHERE 조건으로 지정되있으니 인덱스를 사용하자'와 같은 처리가 이뤄짐.  EXPLAIN명령은 이 실행계획을 확인하는 명령.

- 실행계획에서는 인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단. 

이때 판단 기준으로 인덱스의 품질도 고려. 예를 들어 '예'또는 '아니오'라는 2가지 값만 가지는 열이 있다면, 해당 열에 인덱스를 지정해도 이진탐색에 의한 효율화를 기대할 수 없음.  ==>  데이터의 종류가 적으면 적을 수록 인덱스의 효율도 떨어짐. 

반대로, 서로 다른 값으로 여러 종류의 데이터가 존재하면 그만큼 효율은 좋아짐.  이렇게 인덱스의 품질을 고려해 실행계획이 세워짐.