물고기 개발자의 블로그

출처 :  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 가 더 좋다

 - 꼭필요한 경우가 아니라면 서브쿼리보다는 조인을 사용

간단한 쇼핑몰 모델링으로 보는  ER 구조 파악

1. 구성

개념적으로는 회원 - 상품(M:M)  구조이지만 실제 개발에서는 구매 테이블을 넣고 회원 - 구매 - 상품 (1:M, M:1) 관계로 테이블이 생성된다

2. 점선과 실선

관계선 표기법의 종류중 Richard Barker 관계선 방식이다

Mandatory(필수/실선) 와 Optional(선택/점선) 관계로 확인할 수있으며

회원 - 구매 (1:M) 관계를 예시로 보자면 

   (1) 회원은 구매내역이 필수가 아니다 (Optional)

   (2) 구매내역에는 회원정보가 필수이다 (Mandatory)

Information Engineering 방식은 아래의 그림처럼 표현 된다

3. 쿼리에서의 사용

필수 는 inner join  선택은 left join 관계로 표현하면 된다

회원의 구매내역은 구매내역을 left join 으로

구매내역 목록은 회원을 inner join 으로 표현하라는 관계도로 파악하면 된다

4. 양쪽이 선택(Optional)인 경우

   (1) 이벤트만 생성해놓고 상품은 추가되지 않는 경우 (Optional)

   (2) 상품에 이벤트가 적용되지 않는 경우 (Optional)

5. 식별 비식별 관계

식별 관계는 각각의 관계 테이블의 FK를 PK로 지정하는 경우이고

비식별 관계는 FK로 지정해 조인을 하는 방식이다

식별관계에서는 FK의 변경에 따른 PK 업데이트가 번거로워질 수 있고,

비식별 관계에서는 기본적으로 데이터를 특정하기 위해선 join을 여러번 사용해야한다는 단점이있다

6. 정규화 처리

1:M 으로 한테이블 안에서 중복이 발생할 경우에는 테이블을 나눠야한다