학습 주제
쿼리를 효율적으로 작성하는 방법을 배웠습니다. 또한 가독성을 높여야하는 이유와 방법을 배웠습니다.
주요 메모 사항 소개
테이블을 집합으로 생각하기
현업에서 큰 덩치의 테이블을 다뤄야하는데 기본적인 원칙들을 지키지 않으면 여러가지 부작용이 발생한다. (한 사람의 쿼리가 DB에 과부하를 주어 사람들이 한동안 쿼리를 사용하지 못하거나 비효율적인 쿼리로 인해 비용이 증가할 수 있다.)
-
테이블은 일반적으로 집합 개념과 대응이 된다. (집합 내에 다양한 원소들이 존재하고 각 원소들의 특징은 컬럼 값으로 구분할 수 있다.)
-
테이블을 집합 개념으로 인식하고 쿼리를 작성하면 도움이 된다. (WHERE절을 통한 필터링)
-
최대한 먼저 필터링해서 집합 크기를 줄여준 뒤에 다른 테이블과 조인해주는 것이 좋다. (효율성)
쿼리 프로파일링: 쿼리 실행이 얼마나 걸리는지, CPU는 얼마나 사용하는지 등 쿼리 실행에 대한 세부내용을 확인하는 것
*, % 사용 지양하기
-
새로운 테이블을 접했을 때 해당 테이블에 대한 문서가 상세하게 정리되어 있지 않다면 데이터 일부를 조회하면서 이해도를 높이는데 이때 LIMIT은 필수다. (테이블의 내용을 확인하기 위해 쿼리를 실행할 때 LIMIT을 사용하는 것이 안전하다.)
-
파티션으로 나뉘어 있는 테이블인 경우 WHERE절로 파티션을 지정해주는 것이 필수다. (유저 행동 로그 같은 경우 데이터가 쌓이는 양이 매우 많기 때문에 파티션을 나누어 저장한다.)
-
컬럼을 제한적으로 SELECT해야한다. (상품 메타 데이터는 상품에 대한 정보가 수십 개 이상의 컬럼으로 달려있을 수 있기 때문에 알고싶은 컬럼만 지정해서 조회해야한다.)
-
LIKE에서 %를 사용하면 연산량이 매우 커진다. 따라서 규칙을 최대한 좁혀서 사용하는 것이 필요하다. (특정 문자열로 시작하는 것이 확실하다면 뒤에만 %기호를 사용하고, 뒤에 몇글자인지 명확하게 알고 있다면 _기호를 사용한다.)
데이터 타입 잘 확인하기
DB설정에 따라 다르지만 일반적으로 SQL에서 묵시적 형변환을 지원한다.
묵시적 형변환: 비교하고자 하는 두 값이 다른 타입일 때 타입을 알아서 DB가 맞춘 뒤에 비교하는 것
묵시적 형변환의 문제: 형변환하는데 시간이 걸려 쿼리가 비효율적이다. 또한 정확한 타입을 확인하지 않은 채 쿼리가 복잡해지면 쿼리의 결과가 원하는대로 나오지 않았을 때 문제의 원인을 찾아내는데 시간이 오래 걸린다. (비교 연산자를 사용할 때 타입을 먼저 확인하고 동일한 타입의 값으로 비교해야한다.)
WHERE절에서 왼쪽 컬럼에 함수 적용 지양하기
- WHERE절에서 컬럼에 함수를 사용하면 인덱스를 사용할 수 없다.
인덱스: 데이터를 읽기 속도를 높이는 자료구조 (유무에 따라 읽기속도가 크게 차이난다.)
- 꼭 함수를 써야한다면 상대적으로 작은 테이블의 컬럼에 함수를 적용하는 것이 좋다. (날짜 포멧이 다르거나 컬럼에 함수를 사용해야한다면 WHERE절에서 왼쪽에 있는 컬럼에는 최대한 쓰지않고 다른 방식으로 함수를 적용하는 것이 좋다.)
JOIN 시 유의할 점
JOIN 대상 테이블을 최대한 줄여놓고 시작하는 것이 좋다.
JOIN하는 테이블 간의 관계를 고려해야한다.
1:1 관계
왼쪽 테이블에 행 하나와 오른쪽 테이블에 행 하나가 대응되는 경우
직원 인사 정보 관리 테이블, 직원 팀별 소속 관리 테이블이 있을때, 한 명의 직원이 하나의 팀에만 속할 수 있다면 한 명의 직원을 기준으로 인사 정보 테이블과 팀별 소속 테이블은 1:1 관계가 된다.
두 테이블을 INNER JOIN으로 결합하면 두 테이블 중 더 작은 테이블의 행 수 기준으로 테이블이 생성된다.
1:n 관계
왼쪽 테이블에 행 하나와 오른쪽 테이블에 행 여러개가 대응되는 관계
고객 정보와 클릭 이력 또는 고객 정보와 구매 이력 간의 관계 (고객 한명이 여러 상품을 클릭하거나 구매할 수 있다.)
고객 정보 테이블과 클릭 이력 테이블을 고객 정보 테이블을 기준으로 LEFT JOIN을 하면 고객 정보 테이블의 행 수보다 더 많은 테이블이 생성된다. (고객 한명의 클릭 이력이 LEFT JOIN으로 붙기 때문이다)
반대로 클릭 이력 테이블을 기준으로 고객 정보 테이블을 LEFT JOIN하면 클릭 이력 테이블의 행 수와 동일하다.
n:n 관계
왼쪽 테이블에 행 하나와 오른쪽 테이블에 행 여러 개가 대응되고 오른쪽 테이블에 행 하나도 왼쪽 테이블에 행 여러 개와 대응되는 경우 (대학생과 동아리의 관계 - 학생 한 명은 여러 개의 동아리에 가입할 수 있고 동아리는 학생 여러 명으로 이루어질 수 있기 때문이다. )
결과 테이블의 행 수가 두 테이블의 행 수 조합에 의해 늘어난다.
DB 테이블을 설계할 때 ER모델이란 개념이 있는데 테이블을 엔티티로 두고 테이블간의 관계를 릴레이션십으로 표현한 것
JOIN 대상이 되는 테이블에 중복이 있는지 항상 확인해야 한다.
클릭 로그와 구매 로그가 있을 때 상품별로 구매한 사람의 명수를 구하고 싶을 때 구매 로그에 같은 상품을 여러 개 구매한 경우가 존재할 수 있으므로 이러한 경우는 중복은 생략해야 한다. 따라서 원하는 데이터가 무엇인지에 따라 중복을 생략여부를 확인해야 한다.
여러가지 쿼리 방식을 고려하자
-
같은 결과라도 쿼리는 다양할 수 있고 상황에 따라 가장 효율적인 방식을 사용하는 것이 좋다. (만약 효율적인 방법이 여러 개이면 가독성이 좋은 쿼리를 사용한다.)
-
JOIN은 연산량이 크기 때문에 최대한 효율적으로 작성하는 것이 좋다.
-
만약 여러 쿼리 방식 중 어떤 방식이 가장 나은지 잘 모르겠다면 데이터 일부를 샘플링해서 각 방식의 쿼리 프로파일을 확인해보는 것이 좋다.
-
데이터 별로 쿼리 방식에 따라 효율이 극단적으로 차이가 날 수 있기 때문에 큰 테이블 간의 JOIN을 수행해야 할 때는 여러가지 방식을 비교해 효율적인 방식을 찾아야한다.
가독성 높이기
가독성이 중요한 이유
-
복잡한 쿼리들은 이해하는데 시간이 오래 걸리지만 가독성이 높게 쿼리를 짜두면 이해하기가 훨씬 수월하다. 또한 완벽하게 이해하지않고도 해당 쿼리에서 필요한 부분만 수정해서 사용할 수 있다.
-
가독성을 높이려면 WITH 구문을 사용해야한다. (블럭으로 구분이 되기 때문에 각 블럭 안의 내용을 숙지한 뒤 블럭 밖에서 어떻게 결합이 이루어지는지 순차적으로 이해할 수 있기 때문이다.)
공부하며 어려웠던 내용
쿼리를 작성할 때 효율적으로 작성하는 것이 중요하지만 상황에 맞는 쿼리를 작성하는 것이 어려워보입니다. 따라서 다양한 쿼리를 작성하고 프로파일링을 통해 효율적인 쿼리 작성 방법을 학습해야겠다는 생각이 들었습니다.
