MySQL 퍼포먼스 향상 (3) 인덱스

3. 인덱스

B-tree 인덱스

  - 인덱스는 데이터 사이즈에 비례한다.

  - 인덱스는 스토리지 계층에서 사용되며 엔진마다 조금씩 동작이 다르다.

  - MySQL 의 거의 모든 스토리지 엔진은 B-Tree 인덱스이다.

  - B-Tree 인덱스는 키, 키 범위, 키 프리픽스에 의한 검색에 적절하다.

  - MyISAM 은 인덱스가 압축됨. InnoDB 는 인덱스를 압축하지 않음.

  - MyISAM 은 물리적 위치로 인덱스를 참조하지만, InnoDB 는 주키로 참조한다.

  - LIKE 검색에서는 그 열의 인덱스는 사용불가

해시 인덱스

  - 검색 값을 주면 해시 함수를 통해 결과값을 가지고 찾는다. 해시함수 결과에 대한 값을 저장하기 때문에 컬럼길이가 아무리 길어도 저장되는 양은 현저히 줄어든다는 장점이 있다.

  - 실제 저장위치를 빠르게 알 수 있다.

  - 함수의 결과 범위가 너무 넓으면 그 만큼 많은 버킷이 필요해서 공간의 낭비가 발생한다.

  - 범위 검색이나 원본값 기준으로 소트가 불가능하다.

  - IN, IS NULL, IS NOT NULL, ==, <=  같은 경우에는 B-tree 보다 빠르다.

인덱스 전략

  - BLOB, TEXT, 큰 VARCHAR  에는 인덱스를 붙이지 않는다. 필요할때는 prefix 를 사용한다.

  - prefix 를 사용하면, ORDER BY, GROUP BY 사용이 불가능하다.

  - 클러스터화 인덱스는 관련데이터를 가까운 위치에 배치하므로 엑세스가 빠르다. 

  - InnoDB 는 클러스터화 인덱스로 주키를 사용한다.

  - 여기저기 삽입할땐 풀스캔이 되어버려서 오히려 퍼포먼스가 좋지 않다.

  - InnoDB 에서 주키의 순서대로 데이터를 삽입해야만 한다.

  - 인덱스는 LIKE 연산을 하지 못한다. 단순히 비교 연산만을 할뿐이다.

  - MySQL 은 기본적으로 파일소트방식이다.

  - 중복 인덱스는 작성하지 않도록 주의를 한다.

  - 새로 인덱스를 추가하는 것은 INSERT, UPDATE, DELETE 퍼포먼스에 영향을 준다.

실제사용

  - 식별가능한 값과 WHERE 에서 매우 자주 사용되는 열을 찾아 인덱스를 부여하는 것을 고려해본다.

  - 인덱스가 너무 많은 것은 좋지 않다. WHERE IN 구절이 심플할때 사용을 고려해본다.

  - BETWEEN 같은 2 개 이상의 범위조건 지정이 있는 경우에는 인덱스를 사용하지 말자.

  - MyISAM 은 인덱스를 소트순서로 작성하는 알고리즘으로 인덱스를 재구축하므로 단편화 제거가 가능하다.

  - InnoDB 는 인덱스를 삭제해서 재구축해도 데이터에 따라 단편화가 되버린다.