쿼리 작성 및 최적화 (3)

GROUP BY 사용 시 주의사항

쿼리에 GROUP BY가 사용되면 그룹 키(GROUP BY 절에 명시된 칼럼)가 아닌 칼럼은 일반적으로 집합 함수를 감싸서 사용해야 한다.

SELECT first_name FROM employees GROUP BY gender;

가져온 first_name 이 제일 큰 값인지, 제일 작은 값인지 아니면 중간의 값을 가져온 것인지 알 수 없다.

그렇기 때문에 가능하다면 GROUP BY 절에 명시되지 않은 칼럼은 반드시 집합 함수로 감싸서 사용하길 권장

GROUP BY .. ORDER BY NULL

MySQL의 GROUP BY은 그룹핑과 정렬 작업을 동시에 수행한다. 실제 정렬 작업이 필요하지 않을 때는 정렬은 하지 않도록 쿼리를 작성할 수 있다. ⇒ order by null

GROUP BY 결과 건수가 많아지면 많아질수록 정렬 작업으로 인한 성능 저하가 커진다.

EXPLAIN
SELECT from_date
FROM salaries
GROUP BY from_date;

Using Filesort 까지 표시된 것으로 보아 별도의 정렬 작업이 수행됬음을 알 수 있다.

EXPLAIN
SELECT from_date
FROM salaries
GROUP BY from_date ORDER BY NULL;
이렇게 작성

GROUP BY col1 ASC col2 DESC

주의해야 할 점은 GROUP BY 절의 칼럼에 명시하는 정렬 순서가 혼용되면 인덱스를 사용할 수 없게된다.

GROUP BY .. WITH ROLLUP

WITH ROLLUP 과 함께 사용된 GROUP BY 쿼리 결과는 그룹별 소계를 출력하는 레코드가 추가되어 표시된다. 소계 레코드의 칼럼값은 항상 NULL로 표시된다는 점에 주의해야 한다.

GROUP BY .. ROLLUP 기능은 ORDER BY 와 함께 사용할 수 없다. ROLLUP 기능이 LIMIT 와 함께 사용되는 경우 결과가 혼란스러울 수 있다. GROUP BY .. WITH ROLLUP 이 처리된 이후 LIMIT 가 수행되므로 LIMIT 페이징 처리를 하는 것은 주의해야 한다.

ORDER BY

• 인덱스를 사용한 SELECT의 경우에는 인덱스의 정렬된 순서대로 레코드를 가져온다.
• 인덱스를 사용하지 못하고 풀 테이블 스캔을 실행하는 SELECT의 경우, MyISAM은 테이블 저장된 순서대로 가져옴
• InnoDB의 경우 항상 프라이머리 키로 클러스터링 돼어 있기 때문에 풀 테이블 스캔의 경우 기본적으로 프라이머리 키 순서대로 레코드를 가져온다
• Filesort라는 단어에 포함된 File은 디스크의 파일을 이용해 정렬을 수행한다는 의미가 아니라 쿼리를 수행하는 도중에 MySQL 서버가 퀵 소트 정렬 알고리즘을 수행했다는 의미 정도로 이해하면 된다.

함수나 표현식을 이용한 정렬

• 하나 또는 여러 칼럼의 연산 결과를 이용해 정렬하는 것도 가능하다. 연산 결과에 의한 정렬은 인덱스를 사용할 수 없기 때문에 가능하다면 피하는 것이 좋다.

서브 쿼리

• MySQL 서버는 서브 쿼리를 최적으로 실행하지 못할 때가 많다. 가장 대표적으로 FROM 절에 사용되는 서브 쿼리나 WHERE 절의 IN (subquery) 구문은 가장 최신 버전 에서도 그다지 효율적이지 않다.

서브 쿼리의 제약 사항

• LIMIT 절과 LOAD DATA INFILE의 파일명에는 사용할 수 없다.
• 서브 쿼리를 IN 연산자와 함께 사용할 때에는 효율적으로 처리되지 못한다.
• IN 연산자 안에서 사용하는 서브 쿼리에는 ORDER BY 와 LIMIT를 동시에 사용할 수 없다.
• FROM 절에 사용하는 서브 쿼리는 상관 서브 쿼리 형태로 사용할 수 없다. 이런 형태의 쿼리는 '칼럼을 인식할 수 없다'라는 오류 메시지를 발생시킨다.
• (상관 서브쿼리, 독립 서브쿼리)

SELECT 절에 사용된 서브 쿼리

• SELECT 절에 사용된 서브 쿼리는 내부적으로 임시 테이블을 만들거나 쿼리를 비효율적으로 실행하도록 만들지 않기 때문에 서브 쿼리가 적절히 인덱스를 사용할 수 있다면 크게 주의할 사항은 없다.
• SELECT 절에 서브 쿼리를 사용하면 그 서브 쿼리는 항상 칼럼과 레코드가 하나인 결과를 반환해야 한다.

WHERE 절에 단순 비교를 위해 사용된 서브 쿼리

• 서비쿼리가 WHERE 절에서 사용될 때 서브 쿼리는 범위 제한 조건으로 사용되지 못한다

WHERE 절에 IN과 함께 사용된 서브 쿼리 - IN (subquery)

• 쿼리의 WHERE 절에 IN 연산자를 상수와 함께 사용할 때는 동등 비교와 똑같이 처리되기 때문에 상당히 최적화돼서 실행된다.

FROM 절에 사용된 서브 쿼리

쿼리를 튜닝할 때는 가장 먼저 FROM 절의 서브 쿼리를 조인 쿼리로 바꾼다. FROM 절에 사용된 서브 쿼리는 항상 임시 테이블을 사용하므로 제대로 최적화되지 못하고 비효율적일 때가 많다.

*** 이걸 진짜 많이 들은것 같다. 그리고 querydsl 에서 이거를 지원하지 않는 이유도 비슷하지 않을까??