MySQL 쿼리 및 인덱스의 이해
페이지 정보
본문
인덱스 사용전략
- 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
- 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
댓글목록
등록된 댓글이 없습니다.