[SQL] INDEX와 INDEX의 종류 그리고 주의 사항

• 인덱스(Index)
  • 데이터(레코드)를 빠르게 접근하기 위해 <키, 주소> 쌍으로 구성된 데이터
  • Index가 없으면 테이블의 특정값을 찾기 위해 모든 데이터를 서치하는 TABLE SCAN 발생
  • 테이블에 기본키(PK)가 있으면 기본키(PK)에 대한 기본 인덱스가 자동 생성된다.
  • 인덱스의 종류
    • M-one 검색 Tree
    • B-Tree
    • B*-Tree
    • B+-Tree
    • 인덱스를 잘 사용하면 성능이 향상될 수 있지만, 잘못 설정하면 역효과가 발생한다. 
  • ROWID 
    • 테이블에서 행의 위치를 지정하는 논리적인 주소값
    • 기본적으로 INSERT 되는 순서대로 입력 되지만, 정렬 없이 저장된다.
      • SELECT name, ROWID FROM test;
    • INDEX는 WHERE 절에 오는 조건컬럼이나, JOIN 조건절에 오는 조건컬럼에 만들어 두는게 원칙이다.

• B-Tree 인덱스의 종류
  1. UNIQUE INDEX
  2. Non UNIQUE INDEX
  3. Function Based INDEX(함수 기반 인덱스)
  4. Descending INDEX
  5. Composite INDEX(결합 인덱스)
  • UNIQUE INDEX
    • KEY 값이 중복되는 데이터가 없다.
    • 인덱스 성능이 매우 좋아지지만, 중복되는 값이 들어오는 경우 성능이 엄청 안좋아질 수 있다.
    • UNIQUE INDEX가 지정되면 데이터 값이 중복으로 들어갈 수 없다.
      • CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명1 ASC | DESC, 컬럼명2 ...);
  • Non UNIQUE INDEX
    • 중복된 데이터가 들어와야 하는 경우 사용
      • CREATE INDEX 인덱스명 ON 테이블명(컬럼명1 ASC | DESC, 컬럼명2 ...);
  • Function Based INDEX(함수 기반 인덱스)
    • WHERE 조건절에 컬럼을 가공한 INDEX를 생성할 수 있다.
    • 조건이 변경되면 다시 만들어야 하고 FBI는 기존 인덱스를 활용할 수 없는 단점도 있다.
      • CREATE INDEX 인덱스명 ON 테이블명(컬럼명1 + 100);
  • Descending INDEX
    • INDEX는 생성될 때 기본적으로 오름차순으로 생성되나, 내림차순 INDEX를 만들 때 사용한다.
      • CREATE INDEX 인덱스명 ON 테이블명(컬럼명 DESC);
  • Composite INDEX
    • 두개 이상의 컬럼을 합쳐서 만드는 INDEX
    • WHERE 조건절에 컬럼이 2개 이상의 AND로 연결된 경우
      • CREATE INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);
• INDEX 사용시 주의 사항
  • INDEX가 항상 SQL 성능을 빠르게 하는것은 아니다.
  • SQL 성능이 느려졌다고 빨라질 때까지 INDEX를 만들면 매우 위험하다.
  • INDEX의 취약점
    • INSERT : INDEX Split 발생 가능
      • INDEX 블럭 용량 초과, 이 과정은 시간이 오래 걸린다.
    • DELETE
      • DELETE를 해도 데이터는 지워지지만 INDEX는 지워지지 않는다.
    • UPDATE
      • 데이터를 UPDATE해도 INDEX는 UPDATE 되지 않는다.
      • UPDATE 시 기존의 INDEX가 삭제되고 새로운 데이터의 INDEX가 생성된다. 즉, UPDATE 작업은 두가지 작업이 동시 발생하며 부하를 더 준다.