[DataBase] Query 비교 및 개념

SubQuery 와 Join

SubQuery 가 일으킬 수 있는 문제들

• SubQuery는 테이블과 같은 형태의 결과값을 만들어내지만, 실제 데이터는 저장하지않기때문에 성능저하를 야기할 수 있습니다. → SubQuery 를 접근할 때마다 Select 가 실행
• SubQuery로 만들어지는 데이터는 테이블과 다르게 데이터를 설명하는 메타 정보를 가지고 있지않습니다. → 옵티마이저가 최적화를 하지 못함

SubQuery 를 꼭 사용해야한다면?

MySQL 5.6 이상의 버전은 서브 쿼리가 최적화를 진행하는 조건이 생겼습니다.

그럼에도 위와같은 문제 때문에 비교적 Join 을 사용하는것이 더 좋긴합니다.

하지만 SubQuery 를 꼭 사용해야하는 상황이 있을 수 있기에 최적화가 되는 조건에 대해 알아보겠습니다.

• IN (SubQuery) 또는 ANY (SubQuery) 형태
• UNION 이 없는 단일 SELECT
• 집계함수와 HAVING 절이 없는 쿼리
• 미리 수립된 실행계획을 사용하지 않는 경우

이외에도 다양한 조건들이 있습니다.

참조문서

MySQL where in (서브쿼리) vs 조인 조회 성능 비교 (5.5 vs 5.6)

 

 

서브쿼리 써, 말아?

 

---

필요한 컬럼만 조회

DB에서 Select 조회 시 와일드카드 (*) 를 통해 모든 데이터를 가져오는 것과 필요한 컬럼만을 가져오는 것의 차이를 작성해보겠습니다.

커버링 인덱스 (Using Index)

먼저 눈여겨 볼 것은 커버링 인덱스입니다. 커버링 인덱스란 조회하고자하는 모든 컬럼이 인덱스로 설정되어 있어 데이터파일을 전혀 읽지않고, 인덱스만 읽어서 쿼리를 처리할 수 있는 것입니다.

** “인덱스만 읽는다, 데이터 파일을 읽지않아도된다” 의 의미는 디스크를 거치지 않고 메모리에 있는 데이터만을 읽어와도 된다는 의미에서 속도가 빠른것입니다.

따라서 조회 컬럼이 줄어들어 조회하고자 하는 컬럼이 모두 인덱스로 설정되어있다면 커버링인덱스를 사용할 수 있어 성능상의 이점을 얻을 수 있습니다.

FileSort

정렬 기준이 포함된 쿼리를 수행할 때 MySql은 별도의 메모리 공간인 Sort Buffer를 할당받아 정렬을 수행합니다.

여기서 정렬 방식은 Single pass algorithm , Two pass algorithm이 존재하는데

Single pass algorithm은 정렬에 필요하지 않은 모든 컬럼을 Sort Buffer에 담아 정렬을 수행하기에 Sort Buffer에 더 많은 공간을 필요하게 합니다.

Two pass algorithm은 정렬 대상과 PK 값만을 Sort Buffer에 담아서 정렬을 수행하지만 다시 PK 로 테이블에서 레코드를 읽어야해서 효율적으로 좋지않습니다.

그외 이유

• 어플리케이션 메모리 낭비
• 네트워크 트래픽 증가

---

DB 엔진구조

Select 시 DB 엔진 구조에서 일어나는 일은 다음과 같습니다.

파서

요청된 SQL 문을 쪼개 최소단위로 분리 후 트리를 만듭니다.

전처리기

파서에서 생성한 트리를 토대로 SQL 문에 구조적 문제가 없는지 파악합니다.

또한 권한이 있는 요청인지 파악합니다.

옵티마이저

파서 트리를 토대로 실행계획을 수립합니다.

어떤 순서로 테이블을 접근할지, 어떤 인덱스를 사용할지 계획을 수립합니다.

단, 모든 실행계획을 세우는것은 리소스 낭비로 이어질 수 있으므로 일정 실행계획만을 세웁니다.

따라서 옵티마이저의 실행계획이 최적의 성능이라곤 100% 장담하지 못하고, Hint와 같은 기능을 통해 옵티마이저를 도와줘야합니다.

엔진 실행기

실제로 Data File에서 데이터를 가져오는 작업입니다. MySQL 에서는 읽어 온 데이터를 조인하거나 필터링하는 작업을 수행합니다. 따라서 MySQL 엔진의 부하를 줄이려면 스토리지 엔진에서 가져오는 데이터를 줄여야합니다.

 

 

---

Group By 와 DISTINCT

Group By 와 Distinct 는 서로 중복을 제거할 때 많이 사용됩니다.

두 쿼리의 차이는 뭐가 있을까요

중복제거

1. Distinct 로 중복을 제거

다음은 쿼리와 Explain을 찍었을 때의 실행계획입니다.

 

2. Group By 로 중복을 제거

다음은 쿼리와 Explain을 찍었을 때의 실행계획입니다.

 

위의 Explain 의 결과에서 못봤던 Using filesort 가 들어가 있는것을 볼 수 있습니다.

Group By 는 내부적으로 Distinct 와 Order By 를 사용하기 때문에 Order By 에 의한 filesort가 일어난것입니다.

 

그럼 DISTINCT 가 좋은거야?

단지 위의 실험으로 판단을 하면 안될것 같습니다. Group By는 중복을 제거하는 시점이 DB 에서 가져올 때 제거를 하게됩니다. 반대로 Select 에 달린 Distinct는 다 가져온 후에 중복을 제거하게 됩니다.

그러면 데이터가 많아질수록 또는 Join이 많이 걸릴수록 Group By를 통해 미리 중복을 제거하여 가져오는 것이 성능에 더욱 좋은영향을 끼칠 수 있습니다.

또, 데이터가 많을 때 정렬도 함께해서 가져와야한다면 GROUP BY 가 성능적으로 좋을 수 있습니다.

Group By 는 특정 컬럼을 기준으로 그룹화하는 쿼리입니다. 따라서 집계함수를 사용할 수 있다는 장점 또한 가지고있습니다.