쿼리 및 인덱스의 이해 > 기술자료

• 목록

인덱스 사용전략
 - Mysql> SHOW INDEX FROM index_name \G
 
 - 필드 해석
   Table : 테이블명
   Non_unique : 중복이면 1 아니면 0
   Key_name : 인덱스에 할당된 키 이름
   Seq_in_index : 멀티컬럼인덱스일 경우 순서
   Column_name : 컬럼이름
   Collation : 인덱스의 정렬방식 A(Asc), D(Desc-지원안함) 이나
   Cardinality : 인덱스에서 유니크한 값의 개수. 값이 낮다는 것이 중복이 많다는 것(예:성별은 남,여로 2)
   Sub_part : 컬럼이 부분적으로 인덱싱 되었을때 길이
   Packed : 인덱스의 압축여부. 압축하지 않았을때 Null
   Null : 컬럼이 Null값을 가질수 있으면 Yes, 아니면 NO
   Index_type : 인덱스의 타임(RTREE,FULL TEXT,HASH,BTREE)


인덱스와 관련된 로그
  - 로그를 통해서 추가적인 정보획득 가능
  - "--log_queries-not-using-indexes" 슬로우 쿼리 로그에 인덱스를 사용하지 않은 쿼리 로그를 남김
  - 슬로우 로그사용시 "-log-slow_queries"옵션을 통해서 활성화 해야함


MyISAM의 인덱스 구조
 - PrimaryKey : Leaf 노드에는 ROW Number가 들어 있다
 - Secondary Index : Leaf 노드에는 ROW Number가 들어 있다.
 - Key Cache :
    인덱스를 메모리에 저장.
    인덱스를 디스크에서 읽을때보다 10배빠름.
    없을경우 시스템 캐쉬사용.
    전체물리메모리의 512M-1G정도 지정.


InnoDB의 인덱스 구조
 - 내부적으로 Clustered인덱스라는 구조를 통해 저장. 인덱스의 순서에 따라 물리적 데이터 저장.
 - 프라이머리키가 존재하면 : Clustered인덱스
 - 프라이머리키가 없으면 : 유니크 인덱스를 자동으로 지정
 - 프라이머리키, 유니크 없으면 : 자체적으로 rowID라는 6바이트 유니크 컬럼을 생성해서 입력순서에 따라 생성. show index로 보이지 않음
 - Clustered 인덱스 : Leaf노드에 데이터가 저장됨
 - 다른 모든 인덱스는 Leaf노드에 Clustered인덱스 주소를 가짐
 - 어떠한 컬럼이 Clustered 인덱스가 되느냐가 중요 (크기가 크지 않으며 자주사용되는 컬럼을 이용)


인덱스 선정 절차
 - 해당 테이블 엑세스 유형 조사
 - 대상 컬럼의 선정 및 분포도 분석
 - 반복 수행되는 엑세스 경로의 해결
 - 인덱스 컬럼의 조합 및 순서 결정
 - 시험생성 및 테스트
 - 수정한 필요한 애플리케이션 조사 및 조사
 - 일괄 적용


인덱스의 선정기준
 - 분포도가 좋은 컬럼은 단독적으로 생성
 - 자주 조합되어 사용되는 경우 결합인덱스
 - 각종 엑세스 경우의 수를 만족할 수 있도록 인덱스간의 역활분담
 - 가능한 수정이 빈번하지 않는 컬럼
 - 기본키 및 외부키(조인의 연결고리 컬럼)
 - 결합 인덱스의 경우 컬럼 순서 주의
 - 반복수행 되는 조건은 가장 빠른 수행속도를 내게 할 것


인덱스의 활용시 고려사항
 - 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음
 - 지나치게 많은 인덱스는 오버헤드 발생
 - 넓은 범위를 인덱스 처리시 많은 오버헤드발생
 - 옵티마이저를 위한 통계데이터를 주기적으로 갱신
 - 인덱스의 개수는 적절히 생성
 - 분포가 양호한 컬럼도 처리범위에 따라 분포도가 다를수 있음
 - 인덱스 사용원칙을 준수해야 인덱스가 사용되어진다
 - 조인시 인덱스 사용여부에 주의


Index Cardinality
 - SHOW INDEX에서 확인 가능
 - 가능하면 높은 값을 유지하도록 하는 것이 좋음
 - 높은 컬럼에 인덱스가 필요한 경우 복합 인덱스를 고려


문자형 인덱스 VS 숫자형 인덱스
 - 문자형 인덱스 성능 < 숫자형 인덱스 성능
 - 숫자형 컬럼이 작은 싸이즈

최적의 컬럼 타입 선택을 위한 툴 - Procedure Analyse
 - 사용법
   SELECT * FROM 데이블명 PROCEDURE Analyse(처리할컬럼수)


다중컬럼사용시 빈번한 실수
 - Index(columnA, columnB) 일 경우 WHERE columnB='ABCD' 일경우 인덱스 사용안함
 - WHERE columnA LIKE '%rane' 일 경우 와일드카드가 앞에 있으므로 인덱스 사용안함
 - ORDER BY columnB, columnA 일 경우 인덱스 사용안함


참고자료
http://javapia.blogspot.kr/2010/07/mysql-쿼리-및-인덱스의-이해인덱스-사용-전략.html
http://egloos.zum.com/hanaduri/v/19112