Post

(DB) 다 같은 Count가 아니에요..!

Posted Updated
By
13 min read
(DB) 다 같은 Count가 아니에요..!

(DB)아 Count 운동 많이된다..

Database 대해서 공부한 내용을 정리한 글입니다.
Count의 동작 방식에 대한 내용입니다.

Count 쿼리

현재 테이블에 존재하는 행의 개수를 세고 싶을때 count함수를 사용합니다.

  • NULL을 제외하고 셉니다.

사용법

Count 함수를 사용해서 셀 수 있는 것들입니다.

  1. Column

    1

    SELECT COUNT(age) FROM user;
    • age가 NULL이 아닌 행만 셉니다.

  2. * 아스타 링크

1

SELECT COUNT(*) FROM table;
  • 행 개수 자체를 셉니다 (NULL 상관없음)
  • MySQL에서 가장 많이 쓰고, 최적화도 잘 됩니다.

  1. 식(expression)

    1
2

    SELECT COUNT(age + 1) FROM user;
SELECT COUNT(DISTINCT email) FROM user;
    • 내부적으로는 계산 결과가 NULL이냐 아니냐만 봅니다.

  2. 함수

    1
2

    SELECT COUNT(LENGTH(name)) FROM user;
SELECT COUNT(IF(age > 20, 1, NULL)) FROM user;
    • 함수 결과가 NULL이 아니면 카운트됨

모두 같은 count 함수인데 실행계확이 다른 이유는 MySQL(InnoDB)는 row를 세지 않습니다.

그 이유는 InnoDB는 MVCC(Multi-Version Concurrency Control) 기반 스토리지 엔진이기 때문입니다.

InnoDB는 정확한 row count보다 트랜잭션 일관성(MVCC)과 동시성을 우선했기 때문에 row count를 저장하지 않는다.

만약 row count를 저장하면 생기는 지옥

row count를 저장하지 않는다는 것은 알겠는데 어떤 이유로 row를 세지 않을까요?

간단하게 삽입삭제가 일어날때 수만 증감연산을 수행하면 훨씬 간단한 count가 가능할거라는 생각은 아직 지나지 않습니다.

만약 row count를 센다고 가정해보겠습니다.

아래와 같은 메타데이터로 table의 row count를 수행한다고 생각해봅시다.

1

table_meta.row_count = 1,000,000

동시에 발생하는 상황

  • 동시에 INSERT 100개 / DELETE 50개 / ROLLBACK 20개 / 다른 트랜잭션은 아직 커밋 안 됨

    이런 상황이 지속적으로 발생한다면,

  • 언제 증가?
  • 언제 감소?
  • 롤백 시 어떻게 복구?
  • 트랜잭션별 count는?

이렇게 되면 모든 DML마다 전역 락 필요하게 되고 DB의 병목이 증가할 것입니다.

Count의 유형별 동작 및 실습

몇가지 예제를 통해 count가 동작하는 과정을 따라가보겠습니다.

목데이터 삽입

  1. 테이블 생성

실습을 위해 다음과 같은 Table을 생성하고 목데이터 100만개를 만드는 procedure를 실행했습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13

-- auto-generated definition
create table count_test
(
    id    int auto_increment
        primary key,
    age   int         null,
    email varchar(50) null,
    name  varchar(50) null
);

CREATE INDEX idx_age ON count_test(age);
CREATE INDEX idx_email ON count_test(email);
CREATE INDEX idx_name ON count_test(name);

인덱싱을 한 이유

인덱스를 추가한 이유는 성능을 높이기 위함이 아니라, COUNT 연산이 어떤 스캔 경로를 선택하는지 확인해보고자 추가했습니다. 인덱스가 있을 때와 없을 때의 실행계획 차이는 COUNT 성능의 본질이 ‘연산’이 아니라 ‘접근 경로’임을 보여줍니다.

  1. 프로시저 실행
  • age - NULL을 33%로 생성
  • email - 모두 다르도록 unique하게 생성
  • name - 100개씩 중복되도록 생성
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

DELIMITER $$

CREATE PROCEDURE insert_count_test(IN max_rows INT)
BEGIN
    DECLARE i INT DEFAULT 1;

    START TRANSACTION;

    WHILE i <= max_rows DO
        INSERT INTO count_test (age, email, name)
        VALUES (
            IF(i % 3 = 0, NULL, i % 80),           -- age: NULL 33%
            CONCAT('user', i, '@test.com'),        -- email: 거의 unique
            CONCAT('name', i % 100)                -- name: 중복 100개
        );

        SET i = i + 1;

    END WHILE;

    COMMIT;
END$$

DELIMITER ;

1. COUNT(*)

1
2
3
4
5
6
7
8

mysql> EXPLAIN ANALYZE
    -> SELECT COUNT(*) FROM count_test;
+-----------------------------------------------------------------------+
| EXPLAIN                                                               |
+-----------------------------------------------------------------------+
| -> Count rows in count_test  (actual time=96.9..96.9 rows=1 loops=1)  |
+-----------------------------------------------------------------------+

위에서도 말했듯이 *를 활용한 count는 Aggregate / Scan 단계가 없는것을 볼 수 있습니다.

때문에 Count rows int count_test 단계에서 해당 pk 인덱스(클러스터드 인덱스)에만 접근하여 row가 있는지만 체크하고 결과를 1행 반환했습니다.

  • actual 부분에서 rows=1이 반환 된것은 풀 스캔을 했지만 결국 반환한 row는 1개라는 뜻입니다.

스캔 방식 실행 계확 확인

1
2
3
4
5
6
7

mysql> EXPLAIN
    ->     SELECT COUNT(*) FROM count_test;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | count_test | NULL       | index | NULL          | idx_age | 5       | NULL | 997170 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

2. COUNT(age)

1
2
3
4
5
6
7
8
9

mysql> EXPLAIN ANALYZE
    -> SELECT COUNT(age) FROM count_test;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(count_test.age)  (cost=200307 rows=1) (actual time=143..143 rows=1 loops=1)
    -> Covering index scan on count_test using idx_age  (cost=100590 rows=997170) (actual time=5.62..110 rows=1e+6 loops=1)  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.14 sec)

count(*)과는 다르게 탐색 과정에서 Aggrecate 과정이 추가된 것을 알 수 있습니다.

Aggregation 과정을 위해 index 스캔 후 rows를 대부분 반환한 것을 확인할 수 있습니다.

2-1. 🤔Hmm.. I don’t think so

아니 결국 ***풀스캔***하는데 NULL 체크랑 rows가 존재하는지랑 어차피 if문 1개차이일거 같은데 무슨 상관이지..? 왜 느린지 모르겠다

라는 의구심이 들었고 실제 어떤 동작의 차이가 있는지 확인해보았습니다.

MySQL 공식 내용 (WL#6742)

  • 요약하면 엔진 내부의 row_search_for_mysql()라는 함수를 사용해서 개수를 세는 과정을 DB 서버로 올리지 않고 엔진내에서 해결한다는 내용입니다.

  • [동작 상세]

    1. MySQL 옵티마이저는 COUNT(*)를 처리할 때 handler::records() 를 호출한다.

    2. InnoDB 엔진은 저장 엔진 내부에서 인덱스를 순회하면서 row 존재 여부를 확인하며 카운트한다.

    3. 이 과정은 서버 레벨로 row 전체를 반환하지 않고 단일 숫자 결과만 리턴한다.

3. COUNT(email)

그렇다면 NULL 없으면 빠를까요?

1
2
3
4
5
6
7
8

mysql> EXPLAIN ANALYZE
    -> SELECT COUNT(email) FROM count_test;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(count_test.email)  (cost=200307 rows=1) (actual time=213..213 rows=1 loops=1)
    -> Covering index scan on count_test using idx_email  (cost=100590 rows=997170) (actual time=0.149..176 rows=1e+6 loops=1)						 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  • 마찬가지로 column을 기반으로 스캔하기 때문에 Aggregate 과정을 거칩니다.
  • NULL이 없어도 이 값에 NULL이 없는게 맞는지 확인하는 과정을 거치기 때문에 동이하게 모든 rows를 스캔하는 모습을 볼 수 있습니다.
1
2
3
4
5
6
7
8

mysql> EXPLAIN ANALYZE
    ->     SELECT COUNT(email) FROM count_test;
+---------------------------------------------------------------------+
| EXPLAIN                                                             |
+---------------------------------------------------------------------+
| -> Count rows in count_test  (actual time=129..129 rows=1 loops=1)  |
+---------------------------------------------------------------------+
1 row in set (0.13 sec)

3-1. NOT NULL 옵션을 줘보자

1
2
3
4
5
6
7
8
9
10
11
12

mysql> alter table count_test modify column email VARCHAR(50)  NOT NULL;
Query OK, 0 rows affected (4.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN ANALYZE
    ->     SELECT COUNT(email) FROM count_test;
+---------------------------------------------------------------------+
| EXPLAIN                                                             |
+---------------------------------------------------------------------+
| -> Count rows in count_test  (actual time=129..129 rows=1 loops=1)  |
+---------------------------------------------------------------------+
1 row in set (0.13 sec)

4. COUNT(DISTINCT name)

Index 된 distinct

1
2
3
4
5
6
7
8
9

mysql> EXPLAIN ANALYZE
    -> SELECT COUNT(DISTINCT name) FROM count_test;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(distinct count_test.`name`)  (cost=152 rows=1) (actual time=12.3..12.3 rows=1 loops=1)
    -> Covering index skip scan for deduplication on count_test using idx_name  (cost=141 rows=101) (actual time=0.357..12.3 rows=105 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

인덱스가 있을 때는 Covering index skip scan for deduplication 이 보입니다.
즉, 인덱스만 타면서 중복을 건너뛰는 방식으로 distinct를 세는거라서 table row 전체를 읽을 필요가 없습니다.

그리고 rows=105 정도가 나온 이유는 name을 100개로 반복 생성했기 때문입니다.
이렇게 카디널리티가 작고 인덱스로 바로 dedup이 가능하면 distinct는 굉장히 빠릅니다.

Index 없는 distinct

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mysql> DROP INDEX idx_name ON count_test;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> EXPLAIN ANALYZE
    -> SELECT COUNT(DISTINCT name) FROM count_test;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(distinct count_test.`name`)  (cost=200636 rows=1) (actual time=401..401 rows=1 loops=1)
    -> Table scan on count_test  (cost=100919 rows=997170) (actual time=2.15..127 rows=1e+6 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.40 sec)

인덱스가 없으면 결국 테이블 풀 스캔 후 서버 레벨에서 distinct 집계를 해야 합니다.
이때는 다음 과정이 들어갑니다.

  1. 모든 row를 읽는다.
  2. 임시 테이블(혹은 메모리 구조)에 distinct 값을 저장한다.
  3. 마지막에 개수를 반환한다.

그래서 실제 시간 차이가 엄청 크게 나는 걸 확인할 수 있습니다.

정리

정리하면 다음과 같습니다.

  • COUNT(*)
    • 가장 빠름.
    • InnoDB 엔진 내부에서 handler::records() 방식으로 처리됨.
  • COUNT(col)
    • NULL 체크 때문에 Aggregate 과정이 필요함.
    • 인덱스가 있으면 커버링 스캔 되지만 그래도 서버 레벨 집계가 들어감.
  • COUNT(DISTINCT col)
    • 인덱스 있으면 dedup skip scan 가능 → 매우 빠름.
    • 인덱스 없으면 풀 스캔 + distinct 집계 → 매우 느림.

결론

COUNT는 같은 함수라도 옵티마이저가 선택하는 실행계획이 완전히 달라집니다.
그리고 속도의 핵심은 “count 연산”이 아니라 “어떤 경로로 스캔하고 어떤 레벨에서 집계하느냐” 입니다.

그래서 실제 서비스에서도

  • 단순 개수는 COUNT(*)
  • distinct가 필요한 컬럼은 가능하면 인덱싱
Database
RDB MySQL count
This post is licensed under CC BY 4.0 by the author.