Index 를 시작하기 전에, 먼저, Random I/O 와 Sequential I/O 에 대해 알아보자.
Random I/O 와 Sequential I/O 둘 다, 하드 디스크 드라이브의 플래터(원판)을 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더(disk arm)를 이동시킨 다음 데이터를 읽는 것을 의미한다.
여러 개의 데이터를 입력할 때, 순차 I/O는 디스크 헤드를 한 번만 움직이지만, 랜덤 I/O는 디스크 헤더를 데이터의 개수만큼 움직여야 한다.
디스크 헤더를 움직이는 시간은 디스크에 데이터를 쓰고 읽는 데 걸리는 시간에서 가장 많이 차지하는 시간으로 병목이 되는 부분이다. 그래서 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 크다.
물론, 최근 DBMS 에서 사용하는 SSD 를 사용하고 있다.
SSD 는 HDD 와 달리, 디스크 헤더를 움직이지 않고, 전기 신호를 이용하여 데이터를 읽고 쓴다.
그럼에도 불구하고, 랜덤 I/O 작업은 순차 I/O 작업보다 훨씬 더 많은 시간이 걸린다.
랜덤 I/O 작업, 순차 I/O 작업 모두 파일에 쓰기를 실행하면, 반드시 동기화 작업이 필요하다. 그런데, 순차 I/O 작업은 랜덤 I/O 작업보다 훨씬 더 많은 시간이 걸리기 때문에, 동기화 작업이 더 많이 발생한다. 그런데, 순차 I/O 더라도, 파일 동기화 작업이 빈번히 발생한다면, 랜덤 I/O 작업보다 더 많은 시간이 걸릴 수 있다.
일반적으로 쿼리를 튜닝한다 라는 것은 랜덤 I/O 작업을 순차 I/O 작업으로 바꾸는 것을 의미한다. 즉, 쿼리를 처리하는 데에 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.
Index 는, 도서 색인이나 사전과 같이, 데이터를 빠르게 조회하기 위해 사용하는 자료구조이다.
SQL 서버의 인덱스는 B-Tree 를 기반으로 만들어져 있다.
루트 노드(root node), 내부 노드(internal node), 리프 노드(leaf node) 로 구성된 트리 자료 구조로, 리프 노느가 모두 같은 레벨에 존재하는 균형 이진 트리이다.
클러스터 인덱스 : 연속된 키값의 레코드를 묶어서, 같은 블록에 저장하는 방법으로, 리프 노드에 RID 대신 테이블의 열 자체가 저장된다.
비클러스터 인덱스 : 리프 노드에 실제 데이터 값이 아닌, 테이블에 해당되는 행이 위치한 RID 를 저장한다.
커버링 인덱스 : 인덱스에 포함된 열만으로 쿼리를 처리할 수 있는 인덱스이다.
인덱스는 검색 속도를 빠르게 하지만, 인덱싱을 수행하는데 일정한 비용이 발생하기 때문에 인덱스를 지나치게 많이 생성하면 오히려 성능 저하가 발생할 수 있다.
따라서 인덱스를 생성할 때에는 검색 빈도가 높은 컬럼에 대해서만 생성하거나, 인덱스를 생성할 때 컬럼의 카디널리티(cardinality)를 고려하여 최적의 인덱스 디자인을 수행하는 것이 좋다.
또한, 인덱스를 생성하면 데이터의 수정, 추가, 삭제 등의 작업도 느려질 수 있기 때문에 인덱스는 신중하게 사용해야 한다.
1
EXPAIN SELECT * FROM table_name;