[MySQL] 5. 집계와 서브쿼리

20. 집계함수

• COUNT() : 행 개수를 반환 NULL 값이 있는경우 : COUNT(*)는 NULL 포함, 칼럼 지정할 경우 포함하지 않는다.
• AVG() : 해당 행 평균
• MIN() : 해당 열의 최소값
• MAX() : 해당 열의 최대값 GROUP BY가 MIN/MAX의 경우 EXPAIN을 실행해보면, Using index for group-by를 확인할 수 있다.

22. 그룹화

GROUP BY 구에 열을 지정하여 그룹화하면 지정된 열의 값이 같은 행이 하나의 그룹으로 묶인다. GROUP BY 구로 그룹화된 각각의 그룹이 하나의 집합으로서 집계함수의 인수로 넘겨진다.

xxxxxxxxxx
SELECT ...
FROM 테이블명
GROUP BY 열1, 열2, ....

GROUP BY에서 조건을 주고 싶을 경우 WHERE를 사용할 수 없다. 대신 GROUP BY에 대응되는 조건이라는 개념으로 HAVING 명령어를 사용해야 한다. WHERE 절이 GROUP BY 보다 먼저 실행되기 때문이다.

내부 처리 순서

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

HAVING 은 GROUP BY 뒤에 작성하며, WHERE와 동일한 형식으로 조건식을 작성할 수 있다.

GROUP BY 성능 최적화를 위해 인덱스를 설계해 봤다.

참고

테스트 테이블

xxxxxxxxxx
CREATE TABLE temp
(
    job INT,
    lv INT,
    amount INT
);

테스트 쿼리

xxxxxxxxxx
EXPLAIN
SELECT job, MAX(amount) AS amount_max
FROM temp
GROUP BY job
;

인덱스 없이 EXPLAIN으로 확인해 보면 다음과 같다.

• Using filesort를 확인할 수 있다.
• 20만개 데이터를 담은 테이블인데, 실행시 평균 0.187sec가 걸린다.

인덱스 생성

xxxxxxxxxx
CREATE INDEX idx_job_amount_lv ON temp(job, amount, lv);

 

xxxxxxxxxx
ALTER TABLE tbl ADD INDEX (lv, job);

EXPLAIN을 다시 실행해보면 다음과 같이 나온다.

• 실행 시간이 0.000sec로 인덱스를 잘 활용하고 있다.
• MIN()/MAX() 이외의 집계함수를 사용할 경우 Using index for group-by가 아니라 Using index만 표시된다. 실행시간은 평균 0.094sec이 걸렸다.

 

GROUP BY 구를 수행하는 가장 일반적인 방법은 전체 테이블을 스캔하고, 각 그룹의 모든 행이 연속 된 새 임시 테이블을 만든 다음이 임시 테이블을 사용하여 그룹을 검색하고 집계 함수를 적용하는 것 이다. 경우에 따라 MySQL은 인덱스를 사용하여 임시 테이블을 만들지 않고 훨씬 더 잘 수행 할 수 있다.

GROUP BY를 인덱스를 사용하는 가장 중요한 조건은 모든 GROUP BY컬럼이 동일한 인덱스의 속성을 참조하고 인덱스가 해당 키를 순서대로 저장하는 것이다.

MySQL에서 GROUP BY는 정렬을 위해 사용되는데, ORDER BY를 최적화하느데 적용될 수도 있다.

• Loose Index Scan
• Tight Index Scan

GROUP BY를 처리하는 가장 효과적인 방법은 인덱스를 통해 그룹화된 열을 직접 검색하는 것이다.

참고

 

DISTINCT vs GROUP BY

MySQL - What is the difference between GROUP BY and DISTINCT?

• GROUP BY는 집계 함수를 쓸 때 사용한다.

실행 계획을 살펴봤다.

xxxxxxxxxx
USE employees;
​
EXPLAIN
SELECT distinct first_name
FROM employees;
​
EXPLAIN
SELECT first_name
FROM employees
GROUP BY first_name

distint

group by

GROUP BY의 실행 계획을 확인했을 때 Using filesort를 볼 수 있다.

• DBMS 내부적으로 sort를 한다.(MySQL의 경우 퀵소트 알고리즘을 사용) 소트를 처리하기 위해서 메모리공간을 사용하며, 메모리가 부족할 경우 임시파일을 사용한다. (임시파일은 HDD등의 저장장치에 생성되며 PC에서 가장 느린 성능을 보여주는 부분이다. -> 결과적으로 EXPLAIN시 Extra필드에 Using filesort가 표시된다면 이는 파일을 사용할 가능성이 크다는 것이며, 가장 느린성능을 보여줄것이다.)

index가 지정된 열이면 DISTINT, GROUP BY 둘다 인덱스를 탔다.

 

서브쿼리

서브쿼리는 SELECT 명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의를 의미한다.

서브쿼리를 사용할 떄는 그 SELECT 명령이 어떤 값을 반환하는지 주의할 필요가 있다.

• 열 하나, 행 하나 : 스칼라 값
• 열 하나, 여러 행
• 행 하나, 여러 열
• 여러 열, 여러 행

 

DELETE 서브쿼리

xxxxxxxxxx
mysql> SELECT * FROM sample54;
+------+------+
| no   | a    |
+------+------+
|    1 |  100 |
|    2 |  900 |
|    3 |   20 |
|    4 |  800 |
+------+------+
​
​
DELETE FROM sample54
WHERE
a = (
    SELECT a FROM(
        SELECT MIN(s.a) AS a FROM sample54
     )tmp
);

• Worckbench Safe Mode 해제

 

WHERE구에서 스칼라값을 반환ㄴ하는 서브쿼리는 = 연산자로비교할 수 있다.

xxxxxxxxxx
DELETE FROM sample54 WHERE a=(SELECTMIN(a) FROM sample43)

xxxxxxxxxx
# SELECT 구에 서브쿼리
SEELCT
    (SELECT COUNT(*) FROM sample51) AS sq1,
    (SELECT COUNT(*) FROM sample52) AS sq2;
​
# Set구에 서브쿼리
UPDATE sample54 SET a = (SELECT MAX(a) FROM sample54);
​
# FROM 구에 서브쿼리
SELECT * FROM (SELECT * FROM sample54) sq;
​
# INSERT 서브쿼리
INSERT INTO sample541
VALUES(
(SELECT * COUNT(*) FROM sample51),
(SELECT * COUNT(*) FROM sample54)
);
INSERT INTO sampe541
SELECT * FROM smpale542;

 

 

24. 상관 서브쿼리

서브쿼리를 사용해 DELETE 명령과 SELECT 명령을 결합할 수 있었다. EXISTS 술어를 사용하면 서브쿼리가 바환하는 결괏값이 있는지를 조사할 수 있다. 특히 EXISTS 를 사용하는 경우에는 서브쿼리가 반드시 스칼라 값을 반환할 필요가 없다. EXIST는 단지 반환된 행이 있는지를 확인해보고 값이 있으면 참, 없으면 거짓을 반환한다.

1) 상관 서브쿼리

xxxxxxxxxx
mysql> SELECT * FROM sample551;
+------+------+
| no   | a    |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
|    4 | NULL |
|    5 | NULL |
+------+------+
​
mysql> SELECT * FROM sample552;
+------+
| no   |
+------+
|    3 |
|    5 |
+------+
​
​
​
# sample551에서 sample552에 존재하는 열과 같은 값이 있는 행을 'O'으로 바꾼다.
​
UPDATE sample551 AS s551 SET a = 'O'
WHERE
    EXISTS ( SELECT * FROM sample552 AS s552 WHERE s551.no = s552.no );
​
​
mysql> SELECT * FROM sample551;
+------+------+
| no   | a    |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | O    |
|    4 | NULL |
|    5 | O    |
+------+------+
​
​
​
# sample551에서 sample552에 존재하는 열과 같은 값이 있는 행이 없으면 'X'로 바꾼다.
UPDATE sample551 AS s551 SET a = 'X'
WHERE
    NOT EXISTS ( SELECT * FROM sample552 AS s552 WHERE  s552.no = s551.no );
​
mysql> SELECT * FROM sample551;
+------+------+
| no   | a    |
+------+------+
|    1 | X    |
|    2 | X    |
|    3 | O    |
|    4 | X    |
|    5 | O    |
+------+------+

부모 명령과 연관되어 처리되기 때문에SELECT * FROM sample552 AS s552 WHERE s552.no = s551.no 를 단독으로 실행할 수 없다.

4) IN

IN을 사용하면 집합 안의 값이 존재하는지를 확인할 수 있어서 서브쿼리를 사용할 때 IN을 통해 비교하는 경우도 많다.

xxxxxxxxxx
# IN을 사용한 조건식
​
SELECT *
FROM sample551
WHERE no IN (3, 5);
+------+------+
| no   | a    |
+------+------+
|    3 | O    |
|    5 | O    |
+------+------+
​
​
​
# IN에 서브쿼리 지정
​
SELECT *
FROM sample551
WHERE no IN (
        SELECT no
        FROM sample552
    );
+------+------+
| no   | a    |
+------+------+
|    3 | O    |
|    5 | O    |
+------+------+
​

NOT IN의 집합에 NULL이 존재하면 결과 값이 0이 나온다.

xxxxxxxxxx
no IN (3, 5)
=> no = 3 OR no = 5
​
no열의 값 중 IN()집합과 조건이 TRUE인 것의 목록
​
​
no IN (3, 5, NULL)
=> no = 3           # T / F
    OR no = 5       # T / F
    OR no = NULL    # NULL : NULL과의 비교 결과는 항상 NULL이 나온다.
 : 3, 5면 TRUE를, 아니면 NULL을 반환한다
    TRUE OR NULL = TRUE이기 때문에 no가 3, 5면 TRUE를 반환한다.
​
​
​
# NOT IN의 의미는 다음과 같다
no NOT IN (3, 5)
=> no != 3 AND no != 5
​
no NOT IN (3, 5, NULL)
=> no != 3          # T / F
    AND no != 5     # T / F
    AND no != NULL  # NULL : NULL과의 비교 결과는 항상 NULL이 나온다.
 : 3, 5면 FALSE를, 아니면 NULL을 반환한다. 결국 결과값은 0이 된다.
    1, 2, 4의 경우 no != 3, no != 5가 TRUE이고 no != NULL은 NULL이다
    TRUE AND NULL = NULL이므로 3, 5, NULL에 포함되지 않지만 결과를 가져올 수 없다.

xxxxxxxxxx
mysql> SELECT TRUE AND NULL;
+---------------+
| TRUE AND NULL |
+---------------+
|          NULL |
+---------------+
1 row in set (0.00 sec)
​
mysql> SELECT FALSE AND NULL;
+----------------+
| FALSE AND NULL |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)
​
mysql> SELECT NULL OR TRUE;
+--------------+
| NULL OR TRUE |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
​
mysql> SELECT NULL OR FALSE;
+---------------+
| NULL OR FALSE |
+---------------+
|          NULL |
+---------------+
1 row in set (0.00 sec)

참고 포스트

 

IN에서의 서브쿼리 사용

IN을 통한 서브쿼리를 사용할 경우 쿼리 실행시 풀 스캔을 해버리기 떄문에 성능에 매우 좋지 않은 영향을 끼친다. 5.5 이후의 버전에서는 최적화를 하기 때문에 상관 없다.

참고