출처 : www.youtube.com/watch?v=rmYhahiLHnE&ab_channel=%EC%8B%9C%EB%8B%88%EC%96%B4%EC%BD%94%EB%94%A9indiflex
* 조회에서 특정 인덱스를 스캔하는것과 모든 테이블을 스캔하는 방식이 존재한다
-(pk)인덱스 스캔 vs 테이블 스캔
* Clustered Index
- 물리적인 데이터 파일과 직접적으로 연관이 되는것, pk는 클러스터드 인덱스이다
- pk 데이터 순서대로 물리적인 데이터가 저장이 되기 때문이다
- pk가 없다면 유니크 키가 클러스터드 인덱스가 된다
* 인덱스는 읽기속도만 향상이 된다 쓰기 속도는 오히려 느리다
- 특정 pk목록 사이에 데이터를 추가할때는 인덱스가 나눠져야하기때문에 느리다
* 카디널리티(Cardinality)
- pk외에 다른 컬럼에 인덱스를 걸때 해당 인덱스의 데이터는 세분화가 높을수록 높은 카디널리티로
검색효율이 좋아진다
- 예를들어 남자/여자 같은 두가지 속성의 데이터 컬럼을 인덱스로 지정해 조건을 건다면
카디널리티가 낮은 것이고 검색될 데이터가 많아지기때문에 효율적이지 못하다
* 옵티마이저와 실행계획
- select_type : 조회 쿼리 종류
- type : contst(primary, unique), ALL(테이블 전체 스캔), ref(유니크 하지 않은 인덱스를 탔을때)
range(인덱스의 특정 범위를 읽었을 때) , index, fulltext, etc
- possible_keys : 사용가능한 키들, 옵티마이저가 셀렉트 할 키들
- ref : 인덱스에 지정된 컬럼과 실제로 비교될 컬럼
- rows : 접근된 행 수
- filtered : 조회 된 행 외에 남아있는 값들
* 다중 컬럼 인덱스
- explane select * from Emp where dept(key1) = 5 order by ename(key2);
- ename 이 인덱스가 아닐때 순서에 맞게 where 인덱스, order by 인덱스 순서대로 인덱스 생성을 한다
- 인덱스 새로 구성 명령어 optmize table Emp;
- 다중컬럼 인덱스가 생성된다 ex ) Emp_ibfk_1_idx_Emp_dept_ename
* 인덱스의 크기 확인
- 테이블 별 인덱스의 크기를 확인 ex) show table status where name in ( 'Emp', 'User');
- Rows : 행의 갯수
- Avg_row_length : 한개의 로우가 갖는 평균 데이터 길이(byte) (클러스터드 인덱스 데이터의 크기)
- Index_length : (Non)넌클러스터드 인덱스의 데이터 크기
- Data_length : 기본 테이블 크기 (16k)
- Index_length + Data_length(총 인덱스) 의 크기가
Row * Avg_row_length(데이터 사이즈) 의 10% 를 넘으면 안좋다
* auto increment 가 가장 좋은 인덱스 키의 형태 (중간에 데이터 삽입의 형태가 없기 때문)
* 페이지 분할은 인덱스 사이의 데이터가 삽입되는 과정이며,
보조 인덱스(다중컬럼, Non클러스터드) 사용에 부담이 된다
* 인덱스 쿼리가 아닌것, Sargable
- Where 조건의 컬럼 자리에 함수나 연산이 들어갈 경우 전체 스캔을 하기 때문에 좋지않다
- like 조건에 앞자리 '%' 의 경우에도 간헐적으로 옵티마이저가 인덱스를 잘 못잡는다 ex) ename like '%진'
- between, like, 대소비교(<, > 등)는 범위가 크면 사거블 하지 않다
- or 연산자는 필터링의 반대개념으로 로우를 늘려가기 때문에 사거블 쿼리가 아니다
- limit 에 사용하는 offset 숫자의 범위가 크면 사거블 쿼리가 아니다 (범위 문제)
- 범위(between, <>) 보다는 in 쿼리가 좋고, in 쿼리 보다는 exists 가 더 좋다
- 꼭필요한 경우가 아니라면 서브쿼리보다는 조인을 사용
'IT > Database' 카테고리의 다른 글
| 실무적으로 알아보는 DB 모델링 (0) | 2021.02.10 |
|---|