4. SELECT
오늘은 가장 많이 사용하는 SELECT 절에 대해 살펴보려고 합니다.
SELECT는 여러 개의 테이블에서 데이터를 조합하여 가져오기 때문에 테이블을 어떻게 읽을 것인가에 주의해서 살펴봅시다.
4.1) SELECT 절의 처리 순서
SELECT 문장에는 SELECT 절을 포함한 여러 절이 포함되어 있다.
이때, 어느 절이 먼저 실행되는지를 모르면 처리 내용이나 처리 결과를 예측할 수 없다.
-
실행 순서를 벗어난 쿼리가 필요하다면 서브쿼리로 작성된 인라인 뷰를 사용해야 한다.
➔ 하지만, 인라인 뷰가 사용되면 임시 테이블이 사용되기 때문에 주의해야 한다. -
MySQL 8.0에 새로 도입된
WITH절(CTE)은 항상 제일 먼저 실행되어 임시 테이블로 저장된다.CTE란?
CTE(공통 테이블 식)는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 하나의 실행 범위 내에서 정의되는 임시 결과 집합이라고 볼 수 있습니다. CTE는 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷합니다. 그러나 CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있습니다. -
MySQL 8.0에 새로 추가된 윈도우 함수에서도 쿼리의 각 절이 실행되는 순서가 중요하다.
- 윈도우 함수를 사용하는 쿼리의 결과에 보여지는 레코드는
FROM,WHERE,GROUP BY,HAVING절에 의해 결정되고, 그 이후 윈도우 함수가 실행된다. - 윈도우 함수가 실행되고 나서 마지막으로
SELECT,ORDER BY,LIMIT절이 실행되어 최종 결과가 반환된다.
- 윈도우 함수를 사용하는 쿼리의 결과에 보여지는 레코드는
4.2) WHERE 절과 GROUP BY 절, ORDER BY 절의 인덱스 사용
WHERE 절의 조건뿐만 아니라 GROUP BY나 ORDER BY 절도 인덱스를 이용해 빠르게 처리할 수 있다.
(1) 인덱스를 사용하기 위한 기본 규칙
- 기본적으로 인덱스된 칼럼의 값 자체를 변환하지 않고 그대로 사용해야 한다.
-- 인덱스를 변환해서 사용하면 안됨
SELECT * FROM salaries WHERE salary * 10 > 150000;
-- 인덱스를 변환하지 않는 방식으로 수정
SELECT * FROM salaries WHERE salary > 150000 / 10;WHERE절에 사용되는 비교 조건에서 연산자 양쪽의 두 비교 대상 값은 데이터 타입이 일치해야 한다.
-- 테스트용 테이블 생성
CREATE TABLE tb_test (age VARCHAR(10), INDEX ix_age(age));
INSERT INTO tb_test VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7');
-- 타입이 일치하지 않게 사용하면 타입 변환이 발생하여 성능 저하
SELECT * FROM tb_test WHERE age = 2;
-- 타입이 일치하게 사용해야 함
SELECT * FROM tb_test WHERE age = '2';(2) WHERE 절의 인덱스 사용
WHERE조건이 인덱스를 사용하는 방법은 크게 두 가지가 있다.
① 작업 범위 결정 조건
② 체크 조건
➔ 작업 범위 결정 조건으로 사용된 다음 칼럼부터는 인덱스를 사용하지 못한다.WHERE조건절에 나열된 순서가 인덱스와 다르더라도 옵티마이저는 인덱스를 사용할 수 있는 조건을 뽑아서 최적화를 수행한다.OR연산자를 사용하면 읽어야할 레코드가 늘어나게 되어 풀 테이블 스캔을 하게 될 수 있으므로 주의해야 한다.
(3) GROUP BY 절의 인덱스 사용
GROUP BY절이 인덱스를 사용할 수 있는 경우GROUP BY절에 명시된 인덱스 칼럼의 순서와 위치가 같아야 한다.- 인덱스를 구성하는 칼럼 중에서 뒤쪽에 있는 칼럼은
GROUP BY절에 명시되지 않아도 인덱스를 사용할 수 있지만, 인덱스의 앞쪽에 있는 칼럼이GROUP BY절에 명시되지 않으면 인덱스를 사용할 수 없다. GROUP BY절에 명시된 칼럼이 하나라도 인덱스에 없으면 인덱스를 사용할 수 없다.
GROUP BY에서 인덱스를 사용할 수 없는 경우
-- 인덱스 칼럼의 순서
(COL_1, COL_2, COL_3, COL_4)
... GROUP BY COL_2, COL_1
... GROUP BY COL_1, COL_3, COL_2
... GROUP BY COL_1, COL_3
... GROUP BY COL_1, COL_2, COL_3, COL_4, COL_5GROUP BY에서 인덱스를 사용할 수 있는 경우
-- 인덱스 칼럼의 순서
(COL_1, COL_2, COL_3, COL_4)
... GROUP BY COL_1
... GROUP BY COL_1, COL_2
... GROUP BY COL_1, COL_2, COL_3
... GROUP BY COL_1, COL_2, COL_3, COL_4(4) ORDER BY 절의 인덱스 사용
ORDER BY절의 인덱스 사용은GROUP BY절과 거의 흡사하다.- 단, 정렬되는 각 칼럼의 오름차순 및 내림차순 옵션이 인덱스와 같거나 정반대인 경우에만 사용할 수 있다.
(5) WHERE 조건 + (ORDER BY or GROUP BY) 절의 인덱스 사용
WHERE절과ORDER BY절이 같이 사용된 하나의 쿼리 문장은 다음 3가지 중 한 가지 방법으로만 인덱스를 이용한다. (GROUP BY도 마찬가지이다.)WHERE절과ORDER BY절이 동시에 같은 인덱스를 사용WHERE절과ORDER BY절의 대상 칼럼이 모두 하나의 인덱스에 연속해서 포함되어 있는 경우에 사용 가능
➔ 가장 성능이 좋으므로, 가능하다면 이 방식으로 처리할 수 있게 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋음
WHERE절만 인덱스를 이용- 인덱스를 통해 검색된 레코드를 별도의 정렬 처리 과정(Using Filesort)을 거쳐 정렬을 수행
➔WHERE절에 일치하는 레코드의 건수가 많지 않을 때 효율적인 방식
- 인덱스를 통해 검색된 레코드를 별도의 정렬 처리 과정(Using Filesort)을 거쳐 정렬을 수행
ORDER BY절만 인덱스를 이용ORDER BY절의 순서대로 인덱스를 읽으면서WHERE절의 조건에 일치하는지 비교하고, 일치하지 않을 때는 버리는 형태로 처리한다.
➔ 아주 많은 레코드를 조회해서 정렬해야 할 때는 이런 형태로 튜닝하기도 함
(6) GROUP BY + ORDER BY 절의 인덱스 사용
GROUP BY와ORDER BY절이 동시에 사용된 쿼리에서 하나의 인덱스를 사용하려면 칼럼의 순서와 내용이 모두 같아야 한다.GROUP BY는 인덱스를 이용할 수 있지만ORDER BY가 인덱스를 이용할 수 없을 때는 모두 인덱스를 이용하지 못한다.- 인덱스를 사용하지 못하는 예
-- 인덱스 칼럼의 순서
(COL_1, COL_2, COL_3, COL_4)
...GROUP BY COL_1, COL_2 ORDER BY COL_2
...GROUP BY COL_1, COL_2 ORDER BY COL_1, COL_3(7) WHERE 조건 + (ORDER BY + GROUP BY) 절의 인덱스 사용
WHERE,GROUP BY,ORDER BY절에서 인덱스를 사용하는지 판단하는 방법
①WHERE절이 인덱스를 사용할 수 있는가?
②GROUP BY절이 인덱스를 사용할 수 있는가?
③GROUP BY절과ORDER BY절이 동시에 인덱스를 사용할 수 있는가?
4.3) WHERE 절의 비교 조건 사용 시 주의사항
(1) NULL 비교
- 다른 DBMS와는 다르게 MySQL에서는
NULL값이 포함된 레코드도 인덱스로 관리된다. - SQL 표준에서는
NULL값을 동등 비교하는 것이 불가능하다. 한쪽이라도NULL이면 그 결과도NULL이다. NULL인지 비교하려면IS NULL또는<=>연산자를 사용해야 한다.
-- 결과: NULL
SELECT NULL = NULL;
-- 결과: 1
SELECT NULL <=> NULL;ISNULL()이라는 함수를 사용할 수도 있지만, 이 함수를 사용할 때는 인덱스를 사용하지 못할 수도 있다.
-- 인덱스 레인지 스캔
SELECT * FROM titles WHERE to_date IS NULL;
SELECT * FROM titles WHERE ISNULL(to_date);
-- 인덱스 풀스캔 또는 테이블 풀스캔
SELECT * FROM titles WHERE ISNULL(to_date) = 1;
SELECT * FROM titles WHERE ISNULL(to_date) = true;(2) 문자열, 숫자 비교
- 문자열 칼럼이나 숫자 칼럼을 비교할 때는 반드시 그 타입에 맞는 값을 사용하는 것을 권장한다.
- 문자열 칼럼을 숫자 타입의 상수로 비교하면, 모든 문자열 칼럼을 숫자로 변환해서 비교를 수행하기 때문에 인덱스를 사용하지 못한다.
(3) 날짜 비교
DATESTR_TO_DATE()함수를 사용하지 않아도 문자열을DATE타입으로 내부적으로 변환해준다.DATE()함수는DATETIME값에서 시간 부분은 버리고 날짜 부분만 반환한다.
DATETIMEDATE와 마찬가지로 문자열을 내부적으로DATETIME으로 변환해준다.DATETIME과DATE간의 타입 변환은 인덱스의 사용 여부에 영향을 미치지 않는다.
➔ 두 쿼리 모두 인덱스 사용 가능(DATETIME도 마찬가지)
-- STR_TO_DATE() 함수를 통해 명시적으로 변환
SELECT COUNT(*) FROM employees
WHERE hire_date > STR_TO_DATE('2011-07-23', '%Y-%m-%d');
-- 기본 날짜 형식으로 입력하면 자동 변환하여 비교
SELECT COUNT(*) FROM employees
WHERE hire_date > '2011-07-23';TIMESTAMPDATE나DATETIME타입을TIMESTAMP타입과 비교할 때는 별도의 변환 작업이 없으면 인덱스를 제대로 사용하지 못한다.- 칼럼이
DATETIME인 경우FROM_UNIXTIME()함수를 이용해TIMESTAMP→DATETIME로 변환해서 비교해야 한다.
- 칼럼이
TIMESTAMP인 경우UNIX_TIMESTAMP()함수를 이용해DATETIME→TIMESTAMP로 변환해서 비교해야 한다.
(4) Short-Circuit Evaluation
- 여러 개의 표현식이
AND또는OR연산자로 연결된 경우 선행 표현식의 결과에 따라 후행 표현식을 평가할지 말지 결정하는 최적화를 "Short-Circuit Evaluation"라고 한다.
-- 1번 조건(2,442,943 건)
SELECT COUNT(*) FROM salaries
WHERE CONVERT_TZ(from_date, '+00:00', '+09:00') > '1991-01-01';
-- 2번 조건(0 건)
SELECT COUNT(*) FROM salaries
WHERE to_date < '1985-01-01';
-- 1번 조건 체크 후 2번 조건 확인 (5.89 sec 소요)
SELECT * FROM salaries
WHERE CONVERT_TZ(from_date, '+00:00', '+09:00') > '1991-01-01' /* 1번 조건 */
AND to_date < '1985-01-01'; /* 2번 조건 */
-- 2번 조건 체크 후 1번 조건 확이 (4.50 sec 소요)
SELECT * FROM salaries
WHERE to_date < '1985-01-01' /* 2번 조건 */
AND CONVERT_TZ(from_date, '+00:00', '+09:00') > '1991-01-01'; /* 1번 조건 */➔ 복잡한 연산 또는 다른 테이블의 레코드를 읽어야 하는 서브쿼리 조건 등은 WHERE 절의 뒤쪽으로 배치하는 것이 성능상 좋다.
단, WHERE 절의 조건 중에서 인덱스를 사용할 수 있는 조건은 나열 순서와 관계없이 가장 먼저 평가되기 때문에 고려하지 않아도 된다.
4.4) DISTINCT
DISTINCT를 남용하는 것은 성능적인 문제도 있지만, 쿼리의 결과도 의도한 바와 달라질 수 있다.- 특히, 조인 쿼리에서 레코드의 중복을 막기 위해
DISTINCT를 남용하는 경향이 있다. - 테이블 간의 조인이
1:1조인인지,1:M인인지 업무적인 특성을 잘 이해하는 것이 중요하다.
- 특히, 조인 쿼리에서 레코드의 중복을 막기 위해
4.5) LIMIT n
LIMIT의 중요한 특성은 필요한 레코드 건수만 준비되면 즉시 쿼리를 종료한다는 것이다.ORDER BY,GROUP BY,DISTINCT가 인덱스를 이용해 처리될 수 있다면 LIMIT 절은 꼭 필요한 만큼의 레코드만 읽도록 하여 작업량을 상당히 줄여준다.- 하지만
LIMIT에n과m이 주어질 때, n의 수치가 커지는 경우 주의해야 한다. (주로 페이징 처리)LIMIT 2000000, 10은2000010건을 읽은 후2000000건은 버리고 마지막10건만 반환한다.LIMIT조건의 페이징이 늘어날 경우,WHERE조건절로 읽어야 할 위치를 찾고, 그 위치에서 10개만 읽는 형태의 쿼리를 사용하는 것이 좋다.
-- 이런 형태는 성능 저하
SELECT * FROM salaries ORDER BY salary LIMIT 2000000, 10;
-- 10 rows in set (9.05 sec)
-- WHERE 조건절로 처음 위치를 찾음
SELECT * FROM salaries
WHERE salary >= 154888 AND NOT (salary = 154888 AND emp_no <= 109334)
ORDER BY salary LIMIT 0, 10;
-- 7 rows in set (0.04 sec)4.6) COUNT()
COUNT()함수는 칼럼이나 표현식을 인자로 받으며,"*"를 사용할 수도 있다."*"는SELECT절에 사용될 때처럼 모든 칼럼을 가져오라는 의미가 아니라 그냥 레코드 자체를 의미한다.COUNT(프라이머리 키 칼럼)와COUNT(*)은 동일한 처리 성능을 보인다.
WHERE조건이 없는COUNT(*)쿼리는 실제 레코드 건수를 세어 보지 않아도 바로 결과를 반환할 수 있기 때문에 빠르게 처리된다.WHERE조건이 있는COUNT(*)쿼리는 일반적인 방식으로 처리되며, 인덱스를 제대로 사용하지 못하면COUNT(*)쿼리도 많은 부하를 일으키기 때문에 주의해야 한다.- 또한,
COUNT()함수에 칼럼명이나 표현식이 인자로 사용되면 그 칼럼이나 표현식의 결과가NULL이 아닌 레코드 건수만 반환한다.
4.7) JOIN
(1) JOIN의 순서와 인덱스
- 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다.
(2) JOIN 칼럼의 데이터 타입
- 조인 칼럼 간의 비교에서 각 칼럼의 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 이용할 수 없다.
- 인덱스 사용에 영향을 미치지 않는 경우
CHAR타입과VARCHAR타입INT타입과BIGINT타입DATE타입과DATETIME타입
- 인덱스 사용에 영향을 미치는 경우
CHAR타입과INT타입의 비교와 같이 데이터 타입의 종류가 완전히 다른 경우- 같은
CHAR타입이더라도 문자 집합이나 콜레이션이 다른 경우 (COLLATE utf8mb4_general_ci≠COLLATE latin1_general_ci) - 같은
INT타입이더라도 부호(Sign)의 존재 여부가 다른 경우
- 인덱스 사용에 영향을 미치지 않는 경우
(3) OUTER JOIN의 성능과 주의사항
- 이너 조인은 조인 대상 테이블에 모두 존재하는 레코드만 결과 집합으로 반환다. 테이블의 데이터가 일관되지 않은 경우에는 아우터 조인이 필요하다.
- MySQL 옵티마이저는 절대 아우터로 조인되는 테이블을 드라이빙 테이블로 선택하지 못한다.
➔ 이너 조인으로 사용해도 되는 쿼리를 아우터 조인으로 작성하지 않도록 주의하자. - 아우터로 조인되는 테이블에 대한 조건을 WHERE 절에 명시하면 옵티마이저가
INNER JOIN으로 변환해서 실행한다.- 예외적으로, 안티 조인 효과를 기대하는 경우에는 아우터 조인으로 연결되는 테이블의 칼럼에 대한 조건을
WHERE절에 사용한다. - 그 외의 경우 MySQL 서버는 LEFT JOIN을 INNER JOIN으로 자동 변환한다.
- 예외적으로, 안티 조인 효과를 기대하는 경우에는 아우터 조인으로 연결되는 테이블의 칼럼에 대한 조건을
-- 잘못된 아우터 조인 방법
SELECT * FROM employees e
LEFT JOIN dept_manager mgr ON mgr.emp_no = e.emp_no
WHERE mgr.dept_no = 'd001';
-- 옵티마이저가 이너 조인으로 변환
SELECT * FROM employees e
INNER JOIN dept_manager mgr ON mgr.emp_no = e.emp_no
WHERE mgr.dept_no = 'd001';
-- 정상적인 아우터 조인이 되는 경우
SELECT * FROM employees e
LEFT JOIN dept_manager mgr ON mgr.emp_no = e.emp_no AND mgr.dept_no = 'd001';
-- 안티 조인의 경우 아우터 조인에 WHERE 절 사용 가능
SELECT * FROM employees e
LEFT JOIN dept_manager mgr ON mgr.emp_no = e.emp_no
WHERE mgr.emp_no IS NULL
LIMIT 10;(4) JOIN과 외래키
- 외래키를 생성하는 주목적은 데이터의 무결성을 보장하기 위함이며, 조인과 아무런 연관이 없다.
- 테이블 간의 조인을 수행하는 것은 전혀 무관한 칼럼을 조인 조건으로 사용해도 문법적으로는 문제가 되지 않는다.
(5) 지연된 조인
- 지연된 조인이란 조인이 실행되기 이전에
GROUP BY나ORDER BY를 처리하는 방식을 의미한다. GROUP BY나ORDER BY를 사용하는 쿼리에서 지연된 조인을 사용하면 조인 횟수가 줄어들어 성능이 향샹된다.- 잘 튜닝된 지연된 쿼리는 원래 쿼리보다 몇십 배, 몇백 배 더 나은 성능을 보일 수도 있다.
- 하지만, 지연된 쿼리의 원리를 정확히 이해하지 못한 상태로 지연된 쿼리를 작성하면 오히려 역효과가 날 수도 있다.
(6) 래터럴 조인
FROM절에 사용된 서브쿼리가 외부 쿼리의 칼럼을 참조하기 위해서는LETERAL키워드가 명시되어야 한다.
(7) 실행 계획으로 인한 흐트러짐
- 네스티드-루프 조인은 알고리즘 특성상 드라이빙 테이블에서 읽은 레코드의 순서가 그대로 유지된다.
- 해시 조인은 레코드 정렬 순서가 달라진다.
- 실행 계획은 MySQL 옵티마이저에 의해 그때그때 상황에 따라 달라질 수 있다.
➔ 정렬된 결과가 필요한 경우라면 드라이빙 테이블의 순서에 의존하지 말고,ORDER BY절을 명시적으로 사용하는 것이 좋다.
4.8) GROUP BY
(1) WITH ROLLUP
GROUP BY와 함께WITH ROLLUP을 사용할 수 있다.
-- GROUP BY의 전체 합계 조회
SELECT dept_no, COUNT(*)
FROM dept_emp
GROUP BY dept_no WITH ROLLUP;
+---------+----------+
| dept_no | COUNT(*) |
+---------+----------+
| d001 | 20211 |
| d002 | 17346 |
| d003 | 17786 |
| d004 | 73485 |
| d005 | 85707 |
| d006 | 20117 |
| d007 | 52245 |
| d008 | 21126 |
| d009 | 23580 |
| NULL | 331603 |
+---------+----------+
-- GROUP BY의 전체 합계에 NULL이 아닌 명시적인 값 부여
SELECT IF(GROUPING(dept_no), 'Total', dept_no) AS dept_no,
COUNT(*)
FROM dept_emp
GROUP BY dept_no WITH ROLLUP;
+---------+----------+
| dept_no | COUNT(*) |
+---------+----------+
| d001 | 20211 |
| d002 | 17346 |
| d003 | 17786 |
| d004 | 73485 |
| d005 | 85707 |
| d006 | 20117 |
| d007 | 52245 |
| d008 | 21126 |
| d009 | 23580 |
| Total | 331603 |
+---------+----------+(2) 레코드를 칼럼으로 변환해서 조회
- (레코드 → 칼럼 변환) 또는 (하나의 칼럼 → 여러 칼럼) 분리하는 등의 작업이 필요한 경우에는
집합 함수또는CASE...WHEN구문을 통해 SQL을 작성하면 된다.
-- 레코드를 칼럼으로 변환
SELECT
SUM(CASE WHEN dept_no='d001' THEN emp_count ELSE 0 END) AS count_d001,
SUM(CASE WHEN dept_no='d002' THEN emp_count ELSE 0 END) AS count_d002,
SUM(CASE WHEN dept_no='d003' THEN emp_count ELSE 0 END) AS count_d003,
SUM(CASE WHEN dept_no='d004' THEN emp_count ELSE 0 END) AS count_d004,
SUM(CASE WHEN dept_no='d005' THEN emp_count ELSE 0 END) AS count_d005,
SUM(CASE WHEN dept_no='d006' THEN emp_count ELSE 0 END) AS count_d006,
SUM(CASE WHEN dept_no='d007' THEN emp_count ELSE 0 END) AS count_d007,
SUM(CASE WHEN dept_no='d008' THEN emp_count ELSE 0 END) AS count_d008,
SUM(CASE WHEN dept_no='d009' THEN emp_count ELSE 0 END) AS count_d009,
SUM(emp_count) as count_total
FROM (
SELECT dept_no, COUNT(*) as emp_count from dept_emp group by dept_no
) tb_derived;
+------------+------------+------------+------------+------------+------------+------------+------------+------------+-------------+
| count_d001 | count_d002 | count_d003 | count_d004 | count_d005 | count_d006 | count_d007 | count_d008 | count_d009 | count_total |
+------------+------------+------------+------------+------------+------------+------------+------------+------------+-------------+
| 20211 | 17346 | 17786 | 73485 | 85707 | 20117 | 52245 | 21126 | 23580 | 331603 |
+------------+------------+------------+------------+------------+------------+------------+------------+------------+-------------+-- 하나의 칼럼을 여러 칼럼으로 분리
SELECT de.dept_no,
SUM(CASE WHEN e.hire_date BETWEEN '1980-01-01' AND '1989-12-31' THEN 1 ELSE 0 END) AS cnt_1980,
SUM(CASE WHEN e.hire_date BETWEEN '1990-01-01' AND '1999-12-31' THEN 1 ELSE 0 END) AS cnt_1990,
SUM(CASE WHEN e.hire_date BETWEEN '2000-01-01' AND '2009-12-31' THEN 1 ELSE 0 END) AS cnt_2000,
COUNT(*) AS cnt_total
FROM dept_emp de, employees e
WHERE e.emp_no = de.emp_no
GROUP BY de.dept_no;
+---------+----------+----------+----------+-----------+
| dept_no | cnt_1980 | cnt_1990 | cnt_2000 | cnt_total |
+---------+----------+----------+----------+-----------+
| d001 | 11038 | 9171 | 2 | 20211 |
| d002 | 9580 | 7765 | 1 | 17346 |
| d003 | 9714 | 8068 | 4 | 17786 |
| d004 | 40418 | 33065 | 2 | 73485 |
| d005 | 47007 | 38697 | 3 | 85707 |
| d006 | 11057 | 9059 | 1 | 20117 |
| d007 | 28673 | 23571 | 1 | 52245 |
| d008 | 11602 | 9524 | 0 | 21126 |
| d009 | 12979 | 10600 | 1 | 23580 |
+---------+----------+----------+----------+-----------+4.9) ORDER BY
- 기본적으로
SELECT쿼리는 순서를 보장하지 않는다.
➔ 정렬이 필요하다면ORDER BY를 사용해야 한다. - 정렬이 이루어진 쿼리는 Extra 칼럼에
"Using Filesort"가 표시된다. ORDER BY절에 문자 리터럴은 무시된다.- MySQL 8.0 부터 정렬 순서를 혼용해서 인덱스를 생성할 수 있다.
- 인덱스를
ASC방향으로 생성하면 ASC(정순 스캔)든 DESC(역순 스캔)든 비용 차이가 거의 없다. - 인덱스를
DESC방향으로 생성하면 DESC(정순 스캔)가 ASC(역순 스캔)보다 더 빠르다.
- 인덱스를
- MySQL 8.0 버전부터는 함수 기반의 인덱스를 지원하기 때문에, 연산의 결과값을 기준으로 정렬하는 작업에도 인덱스를 사용하도록 할 수 있다.
SELECT * FROM salaries ORDER BY COS(salary);4.10) 서브쿼리
MySQL 5.6버전까지는 서브쿼리를 최적으로 실행하지 못한다.MySQL 8.0버전부터는 서브쿼리가 많이 최적화 되었다.- 서브쿼리가 사용되는 위치(
SELECT,FROM,WHERE)에 따라 최적화 방법이 달라진다.
(1) SELECT 절에 사용된 서브쿼리
SELECT절에 서브쿼리를 사용하면 그 서브쿼리는 항상 칼럼과 레코드가 하나인 결과를 반환해야 한다.
-- 10건이 조회되는 서브쿼리 (로우 서브쿼리)
SELECT emp_no, (SELECT dept_name FROM departments)
FROM dept_emp LIMIT 10;
-- ERROR 1242 (21000): Subquery returns more than 1 row- 하지만 서브쿼리로 실행될 때보다 조인으로 처리할 때가 더 빠르므로 가능하면 조인을 사용하는 것이 좋다.
- 동일한 서브쿼리가 여러번 사용되는 경우에는 래터럴 조인을 사용하는 것이 좋다. (MySQL 8.0 부터 가능)
(2) FROM 절에 사용된 서브쿼리
MySQL 5.7버전부터FROM절의 서브쿼리를 외부 쿼리로 병합하는 최적화를 수행한다.- 하지만
FROM절의 모든 서브쿼리를 외부 쿼리로 병합할 수 있는 것은 아니다.FROM절의 서브쿼리를 최적화 할 수 없는 경우- 집합 함수 사용 (
SUM(),MIM(),MAX(),COUNT()) DISTINCTGROUP BY또는HAVINGLIMITUNION(UNION DISTINCT)또는UNION ALLSELECT절에 서브쿼리가 사용된 경우- 사용자 변수 사용(사용자 변수에 값이 할당되는 경우)
- 집합 함수 사용 (
(3) WHERE 절에 사용된 서브쿼리
WHERE절의 서브쿼리는 연산자 형태로 사용될 수 있다.- 동등 비교 or 범위 비교
= (subquery) - IN 비교
IN (subquery) - NOT IN 비교
NOT IN (subquery)
- 동등 비교 or 범위 비교
WHERE절에 사용된IN (subquery)형태의 조건을 세미 조인이라고 한다.
4.11) CTE (Common Table Expression)
- CTE는 임시테이블로서, SQL 문장 내에서 한 번 이상 사용될 수 있으며, SQL 문장이 종료되면 자동으로 CTE 테이블은 삭제된다.
- 재귀적 반복 실행 여부를 기준으로
비 재귀적 CTE와재귀적 CTE로 나눌 수 있다. - CTE의 장점
① CTE는 재사용이 가능하기 때문에FROM절의 서브쿼리보다 효율적이다.
② CTE로 선언된 임시 테이블을 다른 CTE 쿼리에서 참조할 수 있다.
③ CTE는 임시 테이블의 생성 부분과 사용 부분의 코드를 분리할 수 있으므로 가독성이 높다.
(1) 비 재귀적 CTE
WITH절을 이용해 CTE를 정의한다.
-- CTE를 사용한 쿼리
WITH cte_d AS (SELECT * FROM departments)
SELECT * FROM cte_d;
-- 위의 쿼리와 같은 서브 쿼리
SELECT *
FROM (SELECT * FROM departments) cte_de;- CTE로 생성된 임시 테이블은 다른 CTE 쿼리에서 참조할 수 있다.
-- cte1 임시 테이블을 cte2에서 사용 가능
WITH
cte1 AS (SELECT emp_no, MIN(from_date) AS salary_from_date
FROM salaries
WHERE salary BETWEEN 50000 AND 51000
GROUP BY emp_no
),
cte2 AS (SELECT de.emp_no, min(from_date) as dept_from_date
FROM cte1
INNER JOIN dept_emp de on de.emp_no = cte1.emp_no
GROUP BY emp_no
)
SELECT * FROM employees e
INNER JOIN cte1 t1 ON t1.emp_no = e.emp_no
INNER JOIN cte2 t2 ON t2.emp_no = e.emp_no;(2) 재귀적 CTE
비 재귀적 쿼리+(UNION | UNION DISTINCT | UNION ALL)+재귀적 쿼리로 구성된다.- 재귀적 CTE가 종료 조건을 만족하지 못해서 무한 반복하지 않기 위해
cte_max_recursion_depth변수를 낮추는 것을 권장한다.
-- cte_max_recursion_depth 값을 제한
SET cte_max_recursion_depth = 10;
-- SET_VAR 힌트를 사용하여 cte_max_recursion_depth 값을 크게 설정
WITH RECURSIVE cte (no) AS (
SELECT 1 AS no
UNION ALL
SELECT (no + 1) FROM cte WHERE no < 1000)
SELECT /*+ SET_VAR(cte_max_recursion_depth=10000) */ * FROM cte;(3) 재귀적 CTE 활용
- 실제 응용 프로그램에서 사용되는 쿼리 예제
-- 재귀적 CTE 테스트용 데이터베이스 생성
CREATE DATABASE tb_cte;
USE tb_cte
-- 테스트용 테이블 생성
CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX(manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
-- 데이터 추가
INSERT INTO employees
VALUES (333, "Yasmina", null),
(198, "John", 333),
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);
-- Adil(id=123)의 상위 조직장을 찾는 CTE 쿼리
WITH RECURSIVE managers AS (
SELECT *, 1 AS lv
FROM employees
WHERE id = 123
UNION ALL
SELECT e.*, (lv + 1)
FROM managers m
INNER JOIN employees e
ON e.id = m.manager_id
AND m.manager_id IS NOT NULL
)
SELECT * FROM managers ORDER BY lv DESC;
+------+---------+------------+------+
| id | name | manager_id | lv |
+------+---------+------------+------+
| 333 | Yasmina | NULL | 3 |
| 692 | Tarek | 333 | 2 |
| 123 | Adil | 692 | 1 |
+------+---------+------------+------+-- 재귀적으로 상위 조직장의 순서를 나열
WITH RECURSIVE managers AS (
SELECT *,
CAST(id AS CHAR(100)) AS manager_path,
1 AS lv
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.*,
CONCAT(e.id, ' -> ', m.manager_path) AS manager_path,
lv + 1
FROM managers m
INNER JOIN employees e ON e.manager_id = m.id
)
SELECT * FROM managers ORDER BY lv ASC;
+------+---------+------------+--------------------------+------+
| id | name | manager_id | manager_path | lv |
+------+---------+------------+--------------------------+------+
| 333 | Yasmina | NULL | 333 | 1 |
| 198 | John | 333 | 198 -> 333 | 2 |
| 692 | Tarek | 333 | 692 -> 333 | 2 |
| 29 | Pedro | 198 | 29 -> 198 -> 333 | 3 |
| 123 | Adil | 692 | 123 -> 692 -> 333 | 3 |
| 72 | Pierre | 29 | 72 -> 29 -> 198 -> 333 | 4 |
| 4610 | Sarah | 29 | 4610 -> 29 -> 198 -> 333 | 4 |
+------+---------+------------+--------------------------+------+4.12) 윈도우 함수
- 집계함수는 그룹별로 하나의 레코드를 묶어서 출력하기 때문에 결과 집합의 모양이 변하지만, 윈도우 함수는 그대로 유지한다.
- 윈도우 함수의 실행 순서
WHERE→FROM→GROUP BY→ORDER BY→윈도우 함수→SELECT→ORDER BY→LIMIT
- 윈도우 함수 기본 사용법
AGGREGATE_FUNC() OVER(<partition> <order>) AS window_func_column
- 윈도우 함수의 각 파티션 안에서 프레임이라고 하는 연산을 수행하는 소그룹이 사용된다. (프레임을 지정하지 않아도 MySQL에서 상황에 맞게 프레임을 선택한다.)
- 윈도우 함수에는 집계 함수와 비 집계 함수를 모두 사용할 수 있다.
- 비 집계 함수는 반드시
OVER()절을 가지고 있어야 하며, 윈도우 함수로만 사용될 수 있다.
- 비 집계 함수는 반드시
4.13) 잠금을 사용하는 SELECT
- 기본적으로 InnoDB는
SELECT할 때 아무런 잠금도 걸지 않는다. SELECT절에서FOR SHARE,FOR UPDATE를 사용하면 잠금을 걸 수 있다.FOR SHARE: 읽기 잠금FOR UPDATE: 쓰기 잠금
OF 테이블옵션을 주면 명시적으로 테이블을 지정해서 잠금을 걸 수 있다.NO WAIT는 레코드가 잠긴 상태라도 무시하고 즉시 에러를 반환한다.SKIP LOCKED는 잠금이 걸리지 않은 레코드만 가져온다.
Reference
조금씩, 천천히, 꾸준히