: 웹 서비스 같이 일반적인 온라인 트랜잭션 처리 환경의 데이터베이스에서는 INSERT나 UPDATE 같은 작업은 거의 레코드 단위로 발생하므로 성능상 문제가 되는 경우가 별로 없음
: 하지만 SELECT는 여러 개의 테이블로부터 데이터를 조합해서 빠르게 가져와야 하기 때문에 여러 개의 테이블을 어떻게 읽을 것인가에 주의를 기울여야 함
1. SELECT 절의 처리 순서
: SELECT 문장이라고 하면 SQL 전체를 의미
SELECT s.emp_no, COUNT(DISTINCT e.first_name) AS cnt
FROM salaries s
INNER JOIN employees e ON e.emp_no=s.emp_no
WHERE s.emp_no IN (100001, 100002)
GROUP BY s.emp_no
HAVING AVG(s.salary) > 1000
ORDER BY AVG(s.salary)
LIMIT 10;
SQL
복사
•
SELECT 절: SELECT s.emp_no, COUNT(DISTINCT e.first_name) AS cnt
•
FROM 절: FROM salaries s INNER JOIN employees e ON e.emp_no=s.emp_no
•
WHERE 절 : WHERE s.emp_no IN (100001, 100002)
•
GROUP BY 절 : GROUP BY s.emp_no
•
HAVING 절 : HAVING AVG(s.salary) > 1000
•
ORDER BY 절 : ORDER BY AVG(s.salary)
•
LIMIT 절 : LIMIT 10
•
FROM → GROUP BY → DISTINCT → HAVING → ORDER BY → LIMIT
•
WHERE → ORDER BY → JOIN → LIMIT
2. WHERE 절과 GROUP BY 절, ORDER BY 절의 인덱스 사용
: GROUP BY나 ORDER BY도 인덱스를 이용해 빠르게 처리할 수 있다는 점을 언급했으니 어떤 요건을 갖췄을 때 사용할 수 있는지
1.
인덱스를 사용하기 위한 기본 규칙
: WHERE 절이나 ORDER BY 또는 GROUP BY가 인덱스를 사용하려면 기본적으로 인덱스된 칼럼의 값 자체를 변환하지 않고 그대로 사용해야 함
2.
WHERE 절의 인덱스 사용
: 작업 범위 결정 조건과 체크 조건의 두 가지 방식
•
작업 범위 결정 조건
: WHERE 절에서 동등 비교 조건이나 IN으로 구성된 조건에 사용된 칼럼들이 인덱스의 칼럼 구성과 좌측에서부터 비교했을 때 얼마나 일치하는 가에 따라 달라짐
•
체크 조건
: 인덱스의 특정 값이 가리키는 범위로 제한
3.
GROUP BY 절의 인덱스 사용
: GROUP BY가 사용되면 인덱스의 사용 여부는 GROUP BY 절에 명시된 칼럼의 순서가 인덱스를 구성하는 칼럼의 순서와 같으면 이용할 수 있음
4.
ORDER BY 절의 인덱스 사용
: GROUP BY 절과 ORDER BY 절의 인덱스 사용은 매우 유사
5.
WHERE 조건과 ORDER BY(또는 GROUP BY) 절의 인덱스 사용
: 일반적으로 다음 3가지 방법 중 하나로 인덱스를 사용
•
WHERE 절과 ORDER BY 절이 동시에 같은 인덱스를 이용
: WHERE 절의 비교 조건에서 사용하는 칼럼과 ORDER BY 절의 정렬 대상 칼럼이 모두 하나의 인덱스에 연속해서 포함되어있을 때 사용 가능, 다른 2가지 방법보다 빠름
•
WHERE 절만 인덱스를 이용
: ORDER BY 절은 인덱스를 이용한 정렬이 불가능하며 인덱스를 통해 검색된 결과를 별도의 정렬 처리 과정(Using filesort)를 거쳐 정렬을 수행
: 주로 WHERE 절의 조건에 일치하는 레코드가 적을 때 효과적인 방법
•
ORDER BY 절만 인덱스를 이용
: 주로 아주 많은 레코드를 조회해서 정렬해야할 때 효과적인 방법
6.
ORDER BY 절과 GROUP BY 절의 인덱스 사용
: GROUP BY 절에 명시된 칼럼과 ORDER BY 절에 명시된 칼럼의 순서와 내용이 모두 같아야 함
7.
WHERE 조건과 ORDER BY 절, GROUP BY 절의 인덱스 사용
: 다음 3개의 질문을 기본으로 해서 인덱스를 사용하는지 판단할 수 있음
1.
WHERE 절이 인덱스를 사용할 수 있는가?
2.
GROUP BY 절이 인덱스를 사용할 수 있는가?
3.
GROUP BY 절과 ORDER BY 절이 동시에 인덱스를 사용할 수 있는가?
3. WHERE 절의 비교 조건 사용 시 주의사항
: WHERE 절에 사용되는 비교 조건의 표현식은 상당히 중요함, 쿼리가 최적으로 실행되려면 적합한 인덱스와 함께 비교 조건의 표현식을 적절하게 사용해야 함
1.
NULL 비교
: MySQL에서는 다른 DBMS와는 달리 NULL 값이 포함된 레코드도 인덱스로 관리됨, 이렇듯 NULL 값이 존재하는 인덱스를 효과적으로 이용하려면 IS NULL 등을 잘 활용
2.
문자열이나 숫자 비교
: 문자열 칼럼이나 숫자 칼럼을 비교할 때는 반드시 그 타입에 적절한 상숫값을 사용할 것을 권장, 불필요한 형변환을 방지하여 성능을 향상시키고자 함
3.
날짜 비교
: 실수를 많이할 수 있는 부분이므로 적절히 잘 비교하는 것이 중요
1.
DATE 또는 DATETIME과 문자열 비교
: DATE 또는 DATETIME 타입의 값과 문자열을 비교할 때는 문자열 값을 자동으로 DATETIME으로 변환하여 비교를 수행, 가능하면 칼럼의 타입을 바꾸지 않고 상수를
변경하는 형태로 사용하는 것이 좋음
2.
DATE와 DATETIME의 비교
: DATETIME 값에서 시간 부분만 떼어 버리고 비교하려면 DATE로 형변환 해주면 됨, 또한 그냥 비교해도 자동으로 변환해서 비교를 수행
3.
DATETIME과 TIMESTAMP의 비교
: DATE나 DATETIME 타입의 값과 TIMESTAMP의 값을 별도로 타입 변환 없이 비교하면 문제없이 작동하고 실행계획도 문제가 없어뵈나 그렇지 않다.
: UNIX_TIMESTAMP 함수의 결괏값은 MySQL 내부적으로는 단순 숫자 값에 불과하므로 비교를 위한 형변환이 발생함
4.
Short-Circuit Evaluation
: 쿼리의 조건 순서에 따라 데이터의 영향을 받는다는 것..으로 이해했음 일단
4. DISTINCT
: 특정 칼럼의 유니크한 값을 조회할 때 사용, 남용하는 것은 성능적인 문제도 있고 쿼리의 결과가 의도한 바와 달라질 수 있음
5. LIMIT
: 쿼리 결과에서 지정된 순서에 위치한 레코드만 가져오고자 할 때 사용
6. COUNT
: 결과 레코드의 건수를 반환하는 함수
7. JOIN
: JOIN이 어떻게 인덱스를 사용하는지에 대해 쿼리 패턴별로 진행
1.
JOIN의 순서와 인덱스
: 인덱스 레인지 스캔은 인덱스를 탐색하는 단계와 인덱스를 스캔하는 과정으로 구분할 수 있음
: 일반적으로 인덱스를 이용해서 쿼리하는 작업에서는 가져오는 레코드의 건수가 소량이기 때문에 부하가 작지만 특정 인덱스 키를 찾는 인덱스 탐색 작업은 상대적으로 높음
: 조인 작업에서 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 단 한번만 수행하고 그 이후부터는 스캔만 실행, 하지만 드리븐 테이블에서는 인덱스 탐색 작업과
스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복, 드라이빙 테이블과 드리븐 테이블이 1대1 매핑이 되더라도 드리븐 테이블을 읽는 것은 큰 부하를 초래
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no=de.emp_no;
SQL
복사
: 위와 같은 쿼리에서 e, de 각각의 테이블의 emp_no에 인덱스가 걸려있을 때와 걸려있지 않을 때 조인 순서의 차이
•
두 칼럼 모두 각각 인덱스가 있는 경우
: 어느 테이블을 드라이빙으로 선택하든 인덱스를 이용해 드라이븐 테이블의 빠른 처리가 가능, 이때 드라이빙 테이블의 결정은 테이블의 통계정보를 가지고 진행
•
하나에만 있는 경우
: 인덱스가 걸려있지 않는 테이블의 레코드 건수만큼 나머지 테이블을 풀스캔해야하므로 인덱스가 걸려있지 않은 테이블을 드라이빙 테이블로 선택
•
둘 다 없는 경우
: 답도 없는 경우, 뭘 선택해도 풀 스캔이 발생하므로 통계정보를 가지고 처리할듯?
2.
JOIN 칼럼의 데이터 타입
: 조인 칼럼 간의 비교에서 각 칼럼의 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 사용할 수 없다.
: 이 경우, 인덱스를 사용할 수 없으므로 풀 테이블 스캔을 해야함, 이때 옵티마이저가 조금이라도 빠르게 처리하려고 조인 버퍼를 사용해서 처리
3.
OUTER JOIN의 성능과 주의사항
: 이너 조인은 조인 대상 테이블에 모두 존재하는 레코드만 결과 집합으로 반환
: 아우터 조인을 처리할 때, 옵티마이저는 아우터 조인의 대상이 되는 테이블을 드라이빙 테이블로 설정할 수 없음, 때문에 아우터 조인을 사용할 경우, 가장 원본 테이블의
인덱싱 상태가 중요
4.
JOIN과 외래키
: 외래키는 JOIN과 아무 연관성이 없음
5.
지연된 조인(Delayed Join)
: 조인을 수행하기 전에 미리 GROUP BY, ORDER BY를 실행한 임시 테이블을 만들어 조인을 수행하는 것
: LIMIT과 함께 사용할 수록 효과가 좋음, 메모리에 적재해야할 레코드 건수가 줄어드므로
6.
래터럴 조인
: 특정 그룹별로 서브쿼리를 실행해서 그 결과와 조인하는 것이 가능하게 만들어주는 조인
7.
실행 계획으로 인한 정렬 흐트러짐
: 해시 조인이 8.0버전부터 추가, 해시 조인을 사용한 경우
: 네스티드 루프 방식으로 조인이 처리되면 드라이빙 테이블을 읽은 순서대로 결과가 조회되는 것과는 달리 해시 조인을 이용한 경우 정렬되어있지 않고 순환하는 결과
8. GROUP BY
: GROUP BY는 특정 칼럼의 값으로 레코드를 그루핑하고 그룹별로 집계된 결과를 하나의 레코드로 조회할 때 사용
1.
WITH ROLLUP
: GROUP BY가 사용된 쿼리에는 그루핑된 그룹별로 소계를 가져올 수 있는 롭업 기능을 사용할 수 있음
: ROLLUP으로 출력되는 소계는 단순히 최종합만 가져오는 것이 아닌 GROUP BY에 사용된 칼럼의 개수에 따라 소계의 레벨이 달라짐, MySQL의 GROUP BY … ROLLUP 쿼리는
엑셀의 피벗 테이블과 유사한 기능을 제공
SELECT dept_no, COUNT(*)
FROM dept_emp
GROUP BY dept_no WITH ROLLUP
SQL
복사
2.
레코드를 칼럼으로 변환해서 조회
a.
레코드를 칼럼으로 변환
: 특정 부서 아이디를 가지고 해당 부서원의 수를 구하고 싶을 때, COUNT를 사용하면 됨, 하지만 레포팅 도구나 OLAP 도구에선 이런 결과를 반대로 만들어야 할수도 있음
: 이때는 위의 GROUP BY 쿼리 결과를 SUM(CASE WHEN …) 구문을 사용해 한 번 더 변환하면 됨, 이리하면 레코드를 칼럼으로 변경할 수 있음
b.
하나의 칼럼을 여러 칼럼으로 분리
: SUM(CASE WHEN …) 문장으로 특정 조건을 이용해 소그룹으로 나눠서 데이터를 표시할 수도 있음
9. ORDER BY
: ORDER BY 절이 사용되지 않으면 SELECT 쿼리는 어떤 순서로 정렬될까?
•
인덱스를 사용한 SELECT의 경우, 인덱스에 정렬된 순서대로
•
인덱스를 사용하지 못하고 풀 테이블 스캔을 사용하는 경우, 프라이머리 키 순서대로 가져옴
•
SELECT 쿼리가 임시 테이블을 거쳐 처리되면 조회되는 레코드의 순서를 예측하기 어려움
⇒ 확실하진 않ㄷ.. 참고만 할 것!!!
: ORDER BY에서 인덱스를 사용하지 못할 때는 추가 정렬 작업이 수행되며 쿼리 실행 계획에 있는 Extra 칼럼에 Using filesort가 표시
1.
ORDER BY 사용법 및 주의사항
: ORDER BY 절은 1개 또는 그 이상 여러 개의 칼럼으로 정렬을 수행할 수 있으며 정렬 순서는 칼럼별로 다르게 명시할 수 있음
: 일반적으로 정렬 대상은 칼럼명이나 표현식으로 명시하지만 SELECT되는 칼럼의 수번을 명시할 수도 있음, 즉 ORDER BY 2라고 명시하면 SELECT되는 칼럼 중에서 2번째 칼럼으로 정렬하라는 의미,
즉 2번째 칼럼으로 정렬을 수행함, 그러나 문자열 레터럴을 지시한 경우는 무시
2.
여러 방향으로 동시 정렬
: 8.0 이후 버전부터는 여러 개의 칼럼을 조합해서 정렬할 때 각 칼럼의 정렬 순서를 지정하여 인덱스를 생성할 수 있게 되었음
ALTER TABLE salaries ADD INDEX ix_salary_fromdate (salary DESC, from_date ASC);
SQL
복사
3.
함수나 표현식을 이용한 정렬
: 함수 기반의 인덱스를 지원하므로 연산의 결괏값을 기준으로 정렬하는 작업이 인덱스를 사용하도록 튜닝하면 됨
10. 서브쿼리
: 쿼리를 작성할 때 서브쿼리를 이용하면 단위 처리별로 쿼리를 독립적으로 작성할 수 있게 도와줌, 조인과 달리 여러 테이블을 섞어 두는 형태가 아니므로 가독성이 향상됨
: 서브쿼리는 여러 위치에서 사용될 수 있는데 대표적으로 SELECT 절과 FROM, WHERE 절에서 사용될 수 있으며 사용되는 위치에 따라 쿼리의 성능 영향도와 MySQL 서버의
최적화 방법은 완전히 달라짐
1.
SELECT 절에 사용된 서브쿼리
: SELECT 절에 사용된 서브쿼리는 내부적으로 임시 테이블을 만들거나 쿼리를 비효율적으로 실행하게 만들지는 않으므로 서브쿼리가 적절히 인덱스를 사용한다면 상관없음
: 일반적으로 SELECT 절에 서브쿼리를 사용하면 해당 서브쿼리는 항상 칼럼과 레코드가 하나인 결과를 반환해야 함, 그 값이 NULL이든 아니든 관계없이 레코드가 1건이 존재해야한다는 것, 즉 SELECT 절의
서브쿼리는 로우 서브쿼리를 사용할 수 없고 오로지 스칼라 서브쿼리만 사용할 수 있다.
스칼라 서브쿼리는 레코드의 칼럼이 각각 하나인 결과를 만들어내는 서브쿼리이며 로우 서브쿼리는 스칼라 서브쿼리보다 많거나 칼럼 수가 많은 결과를 만들어 냄
2.
FROM 절에 사용된 서브쿼리
: 5.7 이후 버전부터는 옵티마이저가 FROM 절의 서브쿼리를 외부 쿼리로 병합하는 최적화를 수행하도록 개선되었음
: 다만 다음과 같은 기능들이 서브쿼리에 사용되면 외부 쿼리로 병합되지 못함
•
집합 함수
•
DISTINCT
•
GROUP BY 또는 HAVING
•
LIMIT
•
UNION(UNION DISTINCT) 또는 UNION ALL
•
SELECT 절에 서브쿼리가 사용된 경우
•
사용자 변수 사용
3.
WHERE 절에 사용된 서브 쿼리
a.
동등 또는 크다 작다 비교
: 단일 값 비교가 아닌 튜플 비교 방식이 사용되면 서브쿼리가 먼저 처리되어 상수화되며 외부 쿼리는 인덱스를 사용하지 못하고 풀 테이블 스캔을 진행
b.
IN 비교
: 실제 조인은 아니지만 다른 테이블의 레코드를 이용해 표현식과 일치하는 지 체크하는 형태를 세미 조인이라고 함, 즉 WHERE 절에 IN 형태의 조건을 세미 조인으로 봄
c.
NOT IN 비교
: IN과 비슷한 형태지만 안티 세미 조인으로 명명, 안티 세미 조인 쿼리가 사용되면 NOT EXISTS, Materialization으로 최적화를 수행
11. CTE
: 뭔지.. 추후 작성
12. 윈도우 함수
: 윈도우 함수는 조회하는 현재 레코드를 기준으로 연관된 레코드 집합의 연산을 수행
: 집계 함수는 주어진 그룹 별로 하나의 레코드로 묶어서 출력하지만 윈도우 함수는 조건에 일치하는 레코드 건수는 변하지 않고 그대로 유지
: 일반적인 SQL 문장에서 하나의 레코드를 연산할 때 다른 레코드의 값을 참조할 수 없는데 예외적으로 GROUP BY나 집계 함수를 이용하면 다른 레코드의 칼럼값을 참조할 수 있음, 하지만 GROUP BY 또는
집계 함수를 사용하면 결과 집합의 모양이 바뀜, 그에 반해 윈도우 함수는 결과 집합을 그대로 유지하면서 하나의 레코드 연산에 다른 레코드의 칼럼값을 참조할 수 있음 → 은행 입출금 내역같은거!!
1.
쿼리 각 절의 실행 순서
: 윈도우 함수를 사용하는 쿼리의 결과에 보여지는 레코드는 FROM, WHERE, GROUP BY, HAVING 절에 의해 결정되며 이후 윈도우 함수가 실행, 그리고 SELECT, ORDER BY, LIMIT 절이 실행되어 최종 결과가
반환, 즉 윈도우 함수를 GROUP BY 칼럼으로 사용하거나 WHERE 절에 사용이 불가하다는 것
2.
윈도우 함수 기본 사용법
: 윈도우 함수의 기본 사용법은 다음과 같음 AGGREGATE_FUNC() OVER(<partition> <order>) AS window_func_column
: 윈도우 함수는 용도별로 다양한 함수들을 사용할 수 있는데 집계 함수와는 달리 함수 뒤에 OVER 절을 이용해 연산 대상을 파티션하기 위한 옵션을 명시할 수 있음
