쿼리 작성 및 최적화

요새 스프링 프로젝트, DB, 자소서 수정, 토비 스프링, 코테 준비 등등을 하고 있다. 하루가 너무 짧기도 하고 잠을 적게 자도 괜찮은 체질이 되고 싶은 요즘이다.
DB공부는 Real Mysql을 읽고 있다. 읽기 너무 어려운 책인데..  DB 질문도 신입 개발자 면접에서 디테일한 질문까지 필수라고 하기 때문에 열심히 정리하면서 읽고 있다. 
쿼리 작성 및 최적화 부분을 공부중인데 이 부분에서 헷갈리는 개념을 정리해볼 생각이다. 

Join 기법

Nested Loop Join

중첩된 반복문과 유사한 방식으로 조인을 수행

반복문 외부의 테이블을 선행 테이블 & 외부 테이블이라 칭함

반복문 내부의 테이블은 후행 테이블 & 내부 테이블이라고 칭한다. 

 

1. 조건을 만족하는 선행 테이블 행을 추출 후 후행 테이블을 읽으며 조인
2. 조건을 만족하는 선행테이블의 모든 행의 수만큼 반복 수행
3. 만족하는 행의 수가 많으면 후행 테이블의 조인 작업은 반복 수행됨

=> 결과 행의 수가 적은 테이블을 선행 테이블로 선택하는 것이 유리함

 

드라이빙 테이블 (Driving Table)

조인 시 먼저 액세스 되는 테이블

후에 액세스 되는 테이블을 Driven 테이블

=> 인덱스 존재 및 우선순위와 From절에서 Table 지정 순서에 영향을 받음

=> 결국 드라이빙 테이블을 정하는 것은 매우 중요하다

 

 

옵티마이저에 의해 결정되는 조인 시 쿼리 수행 규칙

1. 두 칼럼 모두 인덱스가 존재할 경우

옵티마이저가 테이블의 레코드 건수에 따라 드라이빙, 드리븐 테이블 결정

2. 한쪽의 테이블만 인덱스가 존재할 경우

인덱스가 있는 테이블을 드리븐 테이블로 결정

-> 드리븐 테이블에 인덱스가 없다면 드라이빙 테이블의 레코드 하나당 드리븐 테이블을 테이블 풀 스캔해야 된다!

 

3. 두 컬럼 모두 인덱스가 존재하지 않을 경우

어느 테이블을 선택해도 테이블 풀 스캔이므로 옵티마이저는 적절히 레코드 수를 선택해 드라이빙 테이블을 선택함.

 

GROUP BY문에서 불필요한 정렬을 수행하지 않으려면?

GROUP BY 절 뒤에 ORDER BY NULL을 붙이면 됨

=> Mysql에서만 특이 적용

=> Mysql은 GROUP BY 대상 칼럼을 기준으로 GROUP BY 실행 후 정렬 작업까지 같이 수행하도록 구현됨

 

 

서브 쿼리를 사용하는 이유와 장점

서브 쿼리를 사용하면 단위 처리별로 쿼리를 독립 가능하다.

쿼리의 가독성도 높아지고, 복잡한 쿼리도 손쉽게 작성 가능함. 

외부 컬럼 참조에 따라 나눠지는 상관 서브 쿼리, 독립 서브 쿼리가 존재함.

 

서브 쿼리를 사용하는 게 좋을까?

mysql은 서브 쿼리를 최적화하는 능력이 부족하다.

왜냐하면 From 절에 사용할 괄호 수만큼 임시 테이블을 만들 정도이다.

임시 테이블은 메모리를 사용하므로 가급적 지양하는 편이 좋다.

불필요한 서브 쿼리를 작성하기보다는 Join을 사용하는 것이 좋은데 이는 최적화가 상당히 높은 수준이므로 여러모로 좋기 때문이다.

 

REPLACE문의 ON DUPLICATE KEY 차이

Replace는 기본 키가 중복될 경우 해당 로우를 삭제하고 재추가

보통 Insert에서 사용하는 On Duplicate Key update는 기대하는 대로 중복 키 발생 시 지정 칼럼을 update 한다.

http://daplus.net/mysql-mysql%EC%97%90%EC%84%9Creplace%EC%99%80insert-on-duplicate-key-update%EC%9D%98-%EC%8B%A4%EC%A7%88%EC%A0%81%EC%9D%B8-%EC%B0%A8%EC%9D%B4%EC%A0%90%EC%9D%80-%EB%AC%B4%EC%97%87/

[mysql] MySQL에서`REPLACE`와`INSERT… ON DUPLICATE KEY UPDATE`의 실질적인 차이점은 무엇입니까? - 리뷰나라