인덱스의 기타 주의사항
스토리지 엔진별 지원 인덱스 목록
인덱스는 MySQL 엔진 레벨이 아니라 스토리지 엔진 레벨이 포함되는 영역이므로 스토리지 엔진의 종류별로 사용 가능한 인덱스의 종류가 다르다. 간단히 스토리지 엔진별로 지원 가능한 인덱스 알고리즘은 아래와 같다.
| 스토리지 엔진 | 인덱스 알고리즘(종류) |
| MyISAM | B-Tree, R-Tree(Spatial-index), Fulltext-index |
| InnoDB | B-Tree |
| Memory | B-Tree, Hash |
| TokuDB | Fractal-Tree |
| NDB(MySQL Cluster | Hash, B-Tree |
analyze와 optimize의 필요성
MyISAM이나 InnoDB 테이블의 경우, 인덱스에 대한 통계 정보를 관리하고 각 통계 정보를 기반으로 쿼리의 실행 계획을 수립한다. 인덱스에 대한 통계 정보는 아래와 같이 확인할 수 있다.
root@localhost:test > SHOW INDEX FROM tb_test;이 정보에서 가장 중요한 칼럼은 Cardinality 항목이다. InnoDB와 MyISAM 모두 거의 칼럼의 Cardinality에 의존해서 실행 계획을 수립한다.
MySQL의 인덱스 통계 정보에서 기억해야 할 점은 사용자나 DB 관리자도 모르는 사이에 통계 정보가 상당히 자주 업데이트된다는 것이다. 그래서 쿼리의 실행 계획을 최적화하거나 동일하게 유지하기 위해 별도로 통계 정보를 백업했다가 복구하는 작업은 할 수도 없을 뿐더러, 한다 해도 별로 의미가 없다. MySQL 서버가 테이블을 처음으로 열거나 대량의 데이터 변경 또는 테이블의 구조 변경(DDL)이 실행되면 통계 정보를 자동으로 갱신한다.
가끔은 쿼리의 실행 계획이 의도했던 것과는 너무 다르게 만들어질 때가 있다. 이런 경우는 인덱스의 통계 정보가 실제와는 너무 다르게 수집되어 MySQL이 실행 계획을 너무 엉뚱하게 만들어 버리게 되는 것이다. 이렇게 통계 정보가 크게 잘못되는 경우는 다음과 같을 때 자주 발생하는데, 이런 경우에는 ANALYZE 명령으로 통계 정보를 다시 수집해 보는 것이 좋다.
- 테이블의 데이터가 별로 없는 경우(주로 개발용 데이터베이스)
- 단시간에 대량의 데이터가 늘어나거나 줄어든 경우
참고
- Real MySQL
