원티드 프리온보딩 챌린지 (3/4)

solarrrrr·2023년 10월 13일

mysql 기본기 다지기 원티드 프리온보딩

Today I Learned

목록 보기

65/74

3회차는 중요한 약속이 있어 참석하지 못했다.
자료만 받아서 중요 키워드 중심으로 알아보았다.

실행계획

쿼리 순서

JOIN

Subquery

실행계획이란?

SQL 쿼리를 실행할 때 DBMS는 데이터를 어떻게 조회할지 결정하는데 그것을 실행계획이라고 한다.
테이블 스캔 방식, 인덱스 사용 여부, 조인 순서 등을 포함해 가장 효율적인 방법을 선택한다.

쿼리문을 받으면 옵티마이저가 비용 기반으로 최적화된
실행계획을 세우게 되는데,
만족스럽지 못할 경우엔 쿼리를 개선해야 한다.

실행계획 관련해서 강사님의 의견으로는 최초 DB 설계 시 실행계획까지 고려해서 설계하기보다는 일단 기능 구현을 하고 추후 이슈가 발생하는 부분을 실행계획을 통해 분석해 개선해 나가는 형태로 개발하는 게 좋다고 했다.

나 역시 회사 업무 때 JOIN이 여러 개 걸리는 부분의 조회 성능이 낮아서 MYSQL 워크밴치를 통해 이슈를 해결한 경험이 있었다.
full scan이 일어나는 곳이 있었고 적절한 인덱싱을 통해 개선을 했었다.

실행계획을 디테일하게 잘 아는 건 아니지만 워크벤치를 사용하면
시각적으로 정보가 제공되므로 활용할 만했던 기억이 있어 추천하고 싶다.

실행계획 항목 살펴보기

MySQL에서 EXPLAIN 명령을 사용하면 쿼리가 어떻게 실행될지
정보를 얻을 수 있다.
쿼리문 가장 앞에 EXPLAIN을 붙여주면 된다.

EXPLAIN을 했을 때 아래와 같은 표를 얻을 수 있다.

select_type
쿼리의 유형을 나타낸다.

ㅇ SIMPLE
가장 단순한 쿼리 형태를 나타낸다.
보통 단일 테이블에서 데이터를 검색한다.

ㅇ PRIMARY
주 테이블에서 처음 시작한다.

ㅇ SUBQUERY
서브쿼리를 사용한다.

ㅇ DELIVED
서브쿼리의 결과로 파생된 테이블을 사용한다.

ㅇ UNION
여러 SELECT문을 UNION 연산자를 통해 결합한다.

ㅇ UNION RESULT
UNION 연산의 결과를 표시한다.

ㅇ DEPENDENT(DEPENDENT SUBQUERY)
서브쿼리가 외부 쿼리의 데이터에 의존한다.

ㅇ DEPENDENT UNION
UNION 결과가 외부 쿼리에 의존한다.

ㅇ MATERIALIZED
서브쿼리의 결과를 메모리 테이블에 저장해 사용한다.
type
MySQL이 테이블의 레코드들을 어떤 방식으로 스캔했는지 나타낸다.
All의 경우는 full table scan으로 가장 비효율적인 방법이다.
인덱스가 없거나 인덱스를 사용할 수 없는 경우이다.

All 말고도 아래처럼 여러 값이 올 수 있다.

index - 인덱스 스캔을 통해 데이터를 가져온다.
보통 조회가 빈번한 컬럼에 걸어주는 게 성능 향상을 기대할 수 있고,
수정이나 삭제가 많은 컬럼에는 재정렬 비용이 들기 때문에 문제될 수 있다.

range - 인덱스 범위 스캔을 사용해서 조건에 맞는 레코드를 가져온다.
WHERE절에 BETWEEN이나 <, > 같은 비교연산자를 쓸 때 뜬다.

ref(참조) - 조인 작업할 때 주요 테이블과 참조되는 인덱스 사이의 조건으로 검색 시 ref로 표시된다.

eq_ref - 유니크나 PK 같은 유일 인덱스로 조인 작업을 수행할 때 표시된다.

const, system - 상수값을 기반으로 한 단일 레코드 조회 시 이 값들로 표시된다. (pk 검색 등)

NULL - 결과 없음을 의미한다.

index_merge - 여러 인덱스를 결합해서 결과를 가져올 때 표시된다.

unique_subquery, index_subquery - 서브쿼리에서 생성된 결과와 메인 쿼리 간에 연관된 유니크한 값을 비교해서 필터링한다.
1. 서브쿼리 결과 생성
2. 위의 결과와 메인쿼리 간에 연관된 유니크한 값을 비교
possible_keys
WHERE절 조건과 관련된 인덱스들이 나열된다.
옵티마이저가 인덱스를 선택할 때 고려하는 후보들이 표시되는 항목이다.
key
실제 옵티마이저가 선택한 인덱스이다.
key_len
선택한 인덱스 중 실제로 사용된 길이를 바이트 단위로 보여준다.
다중 컬럼 인덱스의 경우 몇 개의 컬럼까지 사용되었는지 알 수 있다.
ref
type 항목에 표시되는 ref와 다름에 유의하자.
ref 항목은 조인 및 비교 조건에서 어떤 인덱스가 사용되었는지를 나타낸다.
별로 중요하진 않다고 한다.
rows
쿼리 실행 중 스캔할 행(row)의 개수를 나타낸다.
즉 추정치를 보여준다. 이 정도 조회할 거 같다 정도의 제시이다.
이 값이 낮을수록 더 좋은 성능이라고 판단할 수 있지만
추정치이기 때문에 참조 정도로 보면 좋을 것 같다.
filtered
row의 필터링 정도를 백분율로 나타낸 항목이다.
테이블 또는 인덱스를 통해 검색된 row가
실제 결과 집합에 얼마나 포함돼 있는지를 나타내는데,
100%에 가까울수록 효율적인 쿼리 필터링이었다고 판단할 수 있다.

참고로 WHERE절의 조건이 바뀌면 옵티마이저의 판단이 달라지므로
filtered 항목의 결과값이 변경될 수 있다.
extra
실행계획의 기타 정보, 추가 정보가 표시된다.
예를 들어 정렬 작업, 파일 소트, 임시 테이블 사용 등과 같은 정보가
이곳에 표시된다.
extra에 2개의 옵션이 표시되는 경우가 있는데
(using temporary; using filesort)
순차적으로 사용된다고 보면 된다.

이 extra 항목은 기타 정보라고 하지만 생각보다 중요한 항목이다.

ㅇ using index
쿼리에서 인덱스를 사용하면 표시된다.

ㅇ using where
where절을 사용했다는 의미이다.

ㅇ using temporary
임시 테이블이 사용됐을 때 표시된다.
FROM절의 서브쿼리는 무조건 이 값으로 표시된다.
COUNT(DISTINCT ~)를 사용할 때도 인덱스를 사용할 수 없다면
임시테이블이 사용되어 이 값으로 표시된다.

ㅇ using filesort
ORDER BY, GROUP BY가 포함된 경우 나타난다.
정렬을 해야 하는데 인덱스를 사용할 수 없는 경우
soft buffer에 저장했다가 리턴하게 된다.

ㅇ Range checked for each record
범위 검색을 수행하면서 각 레코드에 대한 추가적인 검증이 필요한 경우를 나타낸다.
예를 들면 BETWEEN 연산을 하면 이 항목이 표시된다.

ㅇ full table scan
테이블의 모든 레코드를 스캔했을 때 나타난다.

쿼리 순서

SQL의 주요 구성요소들은 (WHERE, GROUP BY, HAVING 등)
특정 순서로 처리되는데, 이 순서는 DBMS에 따라 약간 다를 수 있지만
일반적으로 아래와 같은 순서로 처리된다.

FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> SELECT

대상 테이블에서 데이터를 가져올 건데,
어떤 조건이 걸렸나 확인하고,
특정 컬럼의 그루핑을 한 후,
그루핑에 대한 조건이 있나 확인하고 있다면 적용해서,
오름차순, 내림차순 등으로 정렬해서,
최종적으로 위의 결과셋을 가져온다.

대략 이런 플로우다.

실제 쿼리를 예로 살펴보자.

SELECT 
	seller_id, 
    SUM(sale_amount) AS total_sales, 
    AVG(sale_amount) AS avg_sales
FROM sales
JOIN sellers 
ON sales.seller_id=sellers.id
WHERE sale_date BETWEEN'2022-01-01' AND '2022-12-31'
GROUP BY seller_id
HAVING total_sales > 10000
ORDER BY total_sales DESC
LIMIT 10 OFFSET 5;

이런 쿼리가 있을 때 순서는 아래와 같다

FROM -> JOIN -> WHERE -> WHERE -> SELECT 내의 집계함수
-> HAVING -> ORDER BY, LIMIT, OFFSET -> SELECT

sales 테이블에 sellers 테이블을 ON 조건에 맞게 JOIN 하고
WHERE절의 조건으로, sale_date 컬럼에서 주어진 기간 사이의 레코드를 가져온다.
이때 seller_id 컬럼으로 그룹화하고 (중복 제거)
SELECT절 내의 집계함수 SUM, AVG를 처리한다.
그리고 HAVING절의 필터 조건을 적용해서 나온 결과를
ORDER BY 조건에 맞게 내림차순으로 정렬한다.
마지막으로 LIMIT과 OFFSET에 따라 페이지네이션을 하게 된다.
10개의 데이터를 보여주되 5개의 행을 건너뛴다.
즉 6번째부터 15번째 데이터를 보여줄 것이다.
마지막으로 SELECT절을 통해 결과셋을 반환하게 된다.

구문별 추가 정보(WHERE)

컬럼은 가급적 조작하지 않는 게 좋음
쿼리에서 컬럼을 조작하면 해당 컬럼에 대해 정의된 인덱스를
사용할 수 없게 된다.
```
WHERE DATE(created_at) = '2023-01-01'
```
이 쿼리는 created_at 열에 대한 인덱스를 사용할 수가 없게 된다.
왜냐하면 DATE() 함수로 인해 원래의 컬럼 값이 변경되었기 때문이다.
성능 최적화를 위해서는 조작 없이 원래의 컬럼 값을 사용하는 게 좋다.

Mysql에서의 NULL
NULL은 값이 없음을 나타낸다. 일반 값과는 다르게 동작하는데
NULL을 검사할 땐 특별한 연산자가 필요하다.

<=> (NULL-safe equal operator):
이 연산자는 양쪽 모두 NULL일 경우 True를 반환한다.

IS NULL:
이 구문은 해당 열의 값이 NULL인지 확인한다.

VARCHAR / INT
VARCHAR는 문자열 타입이고 INT는 정수형 타입인데
당연히 서로 호환되지 않으므로 함께 사용하지 않아야 한다.

SQL에서는 문자열과 숫자가 함께 있으면
기본적으로 문자열을 숫자로 변환하려 시도한다.
```
WHERE '123' = 123
```
이런 구문이 있을 경우 문자열 '123'을 숫자 123으로 변환 후 비교하게 된다.
날짜 비교
데이터베이스 시스템은 날짜 및 시간 값을 DATE나 DATETIME 형식으로 저장한다.
그렇기 때문에 날짜 및 시간 값을 저장할 때 해당 컬럼 값을 문자열로 저장하지 않고 형식에 맞게 저장하는 게 좋다.
다만 데이터베이스 시스템은 필요할 경우 문자열을 날짜 형식으로 자동 형 변환을 해 주는 기능이 있다.

예를 들자면,
```
WHERE PostDate < '2023-10-14'
```
이렇게 날짜를 비교할 때 PostDate는 DATE 타입이고
입력된 날짜는 문자열이기 때문에 원래는 비교할 수가 없지만
형 변환을 통해 비교가 가능해지는 것이다.
DATE vs DATETIME
DATE 타입은 날짜만, DATETIME은 날짜와 시간 정보를 담고 있다.
서로 다른 형태이지만 MYSQL에서는 DATE를 DATETIME으로 변환하기 때문에 00:00:00이라는 시간 값이 붙어서 비교가 가능해지긴 한다.
다만 시간 값이 다르기 때문에 원하는 결과를 얻어내지 못할 수도 있다.
그래서 정확한 비교를 위해서는 두 타입 간 비교 시
DATE(DATETIME())과 같은 형식으로 비교해 주는 게 좋다.
```
WHERE PostDate < DATE(NOW())
```
DATETIME vs TIMESTAMP
DATETIME은 날짜와 시간 정보를 나타내지만 시간대 정보(timezone)를 포함하지는 않는다.
그래서 시스템의 로컬 시간대를 사용한다.

TIMESTAMP은 UNIX epoch(1970년 1월 1일 00:00:00 UTC)부터
현재까지의 초 단위 값을 사용한다.
timezone에 따라 값이 다르게 보일 수 있다.

일반적으로 UNIX_TIMESTAMP() 함수는 초 단위 값으로 변환하는 작업을 하는데 반환값이 숫자이다.
그래서 'YYYY-MM-DD HH:MM:SS'의 문자열 형식을 갖는 DATETIME과 직접 비교할 수 없다.
다만 TIMESTAMP의 경우엔 MYSQL에서는 UNIX_TIMESTAMP() 결과값과 비교가 가능하다.
똑같이 초 단위 숫자를 갖기 때문이다.
```
CommentDate < '2012-12-14 13:13:15'
CommentDate < UNIX_TIMESTAMP('2012-12-14 13:13:15')
```
두 조건을 예로 볼 때,
CommentDate가 DATETIME 형식이거나 TIMESTAMP 형식이라면
첫 번째 조건은 성립한다.
위에서 말했듯 MySQL이 내부적으로 문자열을 DATETIME 형태로
형 변환을 해서 조건이 성립하게 된다.
또 TIMESTAMP일 경우에도 조건이 성립하게 되는데,
MySQL이 TIMESTAMP를 DATETIME으로 형 변환해 비교하기 때문이다.
```
'2012-12-14 13:13:15' -> DATETIME
TIMESTAMP -> DATETIME
```
하지만 두 번째 조건은 CommentDate가 DATETIME이라면 성립하지 않는다.
CommentDate가 TIMESTAMP일 경우에만 성립하게 된다.

구문별 추가 정보(GROUP BY)

집계 및 요약 작업을 수행할 때 사용된다.
특정 열을 기준으로 그루핑하고
각 그룹에 대한 집계함수(SUM, COUNT, AVG)를 사용할 수 있다.

WITH ROLLUP이라는 옵션이 있는데 이 옵션을 사용하면,
각 그룹에 대한 집계값 + 모든 그룹에 대한 집계값도 뽑을 수가 있게 된다.

참고로 GROUP BY를 사용할 때 ORDER BY를 사용하지 않으면
결과가 정렬되지 않는다.

구문별 추가 정보(DISTINCT)

중복을 제거하고 유니크한 값만 가져올 때 사용한다.
GROUP BY와 같이 중복된 값을 제거할 수 있지만
집계함수와 같이 사용할 순 없다.
단순히 중복을 제거하고자 할 때 아래와 같이 사용한다면
DISTINCT와 GROUP BY는 같은 결과를 보여주게 된다.

1. SELECT DISTINCT emp_no FROM salaries;
2. SELECT emp_no FROM salaries GROUP BY emp_no;

또 헷갈릴 수 있는 게 아래와 같은 경우인데,

1. SELECT DISTINCT(first_name), last_name FROM employees;
2. SELECT DISTINCT first_name, last_name FROM employees;

괄호는 불필요하다. 괄호를 넣었다고 first_name 컬럼만 중복 제거하는 게 아니라 last_name 컬럼까지 두 컬럼 모두 일치하는 데이터가 있을 경우 중복을 제거하고 표시하게 된다.

DISTINCT는 모든 컬럼에 대해 중복 제거가 이루어지므로
여러 컬럼 중 특정 컬럼에 대한 중복 제거가 필요하다면
위처럼 괄호로 묶을 게 아니라 GROUP BY를 사용해야 한다.

JOIN

조인은 두 개 이상의 테이블이 있을 때 관련된 데이터를 결합하는 연산을 말한다.

JOIN
JOIN = INNER JOIN이다.
교집합을 생각하면 된다.
두 테이블의 공통된 부분을 가져온다.
LEFT JOIN
LEFT JOIN = LEFT OUTER JOIN이다.
두 테이블의 공통된 데이터와 왼쪽 테이블의 모든 데이터는 다 가져오고
우측 테이블에 일치하지 않는 부분은 NULL로 채워서 가져오는 방식이다.

RIGHT JOIN(=RIGHT OUTER JOIN) 역시 방향만 다를 뿐
같은 방식을 취한다.
FULL OUTER JOIN
합집합을 생각하면 된다.
다만 집합에서의 합집합처럼 두 원소 모두를 다 들고 오는 건 아니며,
모든 데이터를 가져오되 일치하지 않는 부분은 NULL로 채워서 가져온다.
CROSS JOIN
가능한 모든 조합을 생성해서 '카디션 곱'을 반환한다.
이 연산은 WHERE절을 사용하지 않으면
왼쪽과 오른쪽의 모든 가능한 조합을 가져오게 된다.

참고로 '카디션 곱'이란 두 집합의 모든 조합 연산을 말한다.
A가 {1, 2}이고 B가 {a, b}라는 원소를 갖고 있다면
A와 B의 카디션 곱은 {(1, a), (1, b), (2, a), (2, b)}가 된다.

테이블을 예로 말한다면,
A 테이블의 각 행이 B 테이블의 각 행과 모두 조합되는 걸 말한다.

이렇게 조합된 모든 결과가 유의미한 것은 아니므로
적절한 WHERE절의 사용으로 필요한 데이터만 뽑아내야 한다.

개인적으로는 실무에서는 아직 경험해 보지 못한 연산의 형태이다.
SELF-JOIN
이름 그대로 나 자신과 조인하는 형태를 말한다.
1개의 테이블을 독립적인 2개의 테이블의 조합인 것처럼 활용한다.