COUNT 쿼리의 실행계획, 인덱싱

1. 들어가며

 

https://rawshrimpsushi.tistory.com/65

COUNT(*) & COUNT(DISTINCT) 튜닝

위 글에서 COUNT 쿼리에 대한 잘못된 인식과 ORM과 함께 사용하였을 때의 COUNT(DISTINCT)의 문제점에 대해 알아보았었습니다. 오늘은 COUNT 쿼리를 사용할 때 주로 쓰는 (*) 키워드와 column을 넣었을 때의 차이점, 그리고 일반적으로 권장되는 방법에 대해 자세히 알아보고자 합니다.

 

2. COUNT(*) vs COUNT(column)

정의

CREATE TABLE counter (
    id int NOT NULL AUTO_INCREMENT,
    ix1 varchar(200) NOT NULL,
    fd1 varchar(200) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx1 (ix1)
);

 

위와 같은 테이블이 있다고 했을 때 다음 쿼리들은 어떤 결과를 만들까요?

SELECT COUNT(*), COUNT(1),
       SUM(1), COUNT(id),
       COUNT(ix1), COUNT(fd1)
FROM counter;

 

정답은 다음과 같습니다.

COUNT(*): 14108
COUNT(1): 14108
SUM(1): 14108
COUNT(id): 14108
COUNT(ix1): 14108
COUNT(fd1): 14107

 

보면 fd1만 값이 다른 것을 알 수 있습니다. 이는 fd1은 nullable한 column이기 때문인데요.

MySQL 공식 문서에서도 COUNT 쿼리에 대해서 다음과 같이 이야기 하고 있습니다.

Returns a cocunt of the number of non-NULL values of expr in the rows retreved by a SELECT statement

즉 count는 레코드 건 수가 아닌 null이 아닌 건 수를 반환한다는 것입니다. 이는 count를 튜닝할 때 다른 방식으로 대체해야될 수 있는데, 어떻게 해야 동일한 값이 나오는지 판단해야할 때 중요한 기준이 됩니다.

 

실행 계획

COUNT 쿼리는 어떠한 실행 계획으로 처리될 지 미리 알 수 있을까요? 일반적으로는 다음과 같은 방식으로 실행됩니다.

• WHERE 조건 가진 COUNT()
  • Covering Index
  • Non-Covering Index
• WHERE 조건 없는 COUNT()
  • ha_records() 스토리지 API 사용
  • ha_index_next() 스토리지 API 사용

조금 생소한 것이 있습니다. WHERE 조건 없은 COUNT()문에서 사용하는 스토리지 API들입니다. 이 API들에 대해서는 조금 더 자세히 알아보도록 하겠습니다.

 

3. WHERE 조건 없는 COUNT()

1) 스토리지 API

저번 글에서 WHERE 조건이 없는 COUNT 쿼리를 지양하고자 했지만, 꼭 써야 되는 상황이 올 수 있습니다. 이 때 ha_records()와 ha_index_next()라는 스토리지 API를 사용한다고 나와 있는데요. 이들은 InnoDB가 제공하는 API들 중 하나로

ha_records의 경우

• 항상 클러스터형 인덱스를 사용하여 행 수를 계산합니다.
• 기본키 값만 읽어오면 되며, 외부 저장소 컬럼(row_prebuild_t::read_just_key)을 읽을 필요가 없습니다. 행 레코드가 크면 메모리 복사 등 객관적인 연산 오버헤드를 줄일 수 있습니다.
• 엔진 계층에 대한 호출이 하나만 있기 때문에 서버 계층과 InnoDB 간의 상호 작용이 줄어들고 불필요한 메모리 작업이나 형식 변환이 방지됩니다.

라는 장점을 가진 COUNT를 위한 스토리지 API입니다. 다만 클러스터형 인덱스는 항상 강제로 사용해야 하기 때문에 단점은 명백합니다. 보조 인덱스의 크기가 클러스터형 인덱스보다 훨씬 작고 데이터가 메모리에 없는 경우 보조 인덱스를 사용하는 것이 확실히 더 빠르므로 파일 IO가 적습니다. 

 

그렇다면 어떤 조건에서 ha_records를 사용하고 어떤 조건에서 ha_index_next()를 사용할까요?

 

CREATE TABLE counter ( /* INSERT counter VALUES (1,1); 1,000,000 rows */
    fd1 INT NOT NULL,
    fd2 INT
);

 

위와 같은 테이블이 있을 때 실행 계획은 다음과 같이 모두 FULL SCAN으로 동일합니다.

 

하지만 실행 시간은 큰 차이가 있는데요.

• Case-1) SELECT COUNT(fd1) FROM counter; => 0.04 sec
• Case-2) SELECT COUNT(*) FROM counter; => 0.04 sec
• Case-3) SELECT COUNT(fd2) FROM counter; => 4.26 sec

조금 더 자세히 보기 위해 내부적으로 handler activity metric 변화를 살펴보겠습니다.

handler activity metric은 https://dev.mysql.com/doc/mysql-em-plugin/en/myoem-metric-mysqlserver-handleractivity-category.html 문서를 참조해주세요

 

 

• fd2는 read_next와 rnd_next 차이가 엄청 큰 걸 알 수 있습니다.
• count(*)는 어떻게 handling metric이 모두 0일수 있을까요?
• innodb storage는 ha_records를 호출했기 때문에 사용자 눈으로 확인이 안 된 것입니다. 호출하는 storage api에 따라 성능 차이가 발생할 수 있다는 것을 알 수 있습니다.

2) innodb_parallel_read_threads 설정

 

MySQL 8.0 버전부터는 조건 없는 COUNT() 쿼리에 대해 병렬 처리를 지원합니다.

병렬 처리를 비활성화하고 위의 쿼리 실행 시간을 다시 비교해볼까요? 참고로 병렬 처리의 기본값 option은 4입니다.

SET innodb_parallel_read_threads = 1;

• Case-1) SELECT COUNT(fd1) FROM counter; => 4.31 sec
• Case-2) SELECT COUNT(*) FROM counter; => 0.04 sec
• Case-3) SELECT COUNT(fd2) FROM counter; => 4.26 sec

• count(fd1)이 훨씬 느려진 것을 알 수 있습니다. 스레드 하나 썼더니 사실상 count(fd2)와 유사합니다
• 이는 Innodb엔진이 레코드를 읽어서 column을 추출해야 되는가 아닌가에 따라 성능 차이가 매우 커진다는 것을 보여줍니다.
• 병렬 처리 option이 2 이상이면 not null col은 컬럼을 추출하지 않습니다.
• 요약하면 count(*)를 사용하는 것이 항상 좋다는 것을 알 수 있습니다.

 

그럼 이번엔 실행 계획에서 index를 사용하는 조건 없는 COUNT(*)를 살펴보겠습니다.

EXPLAIN SELECT COUNT(*) FROM counter;

• mysql의 특성: 다른 dbms와 다르게 index가 Null인 컬럼도 모두 포함하고 있습니다. 즉 어떤 index를 읽어도 정확한 레코드 건수를 가져올 수 있습니다.
• mysql optimizer가 pk index를 읽지 않고 idx1를 가져온 이유: pk index는 매우 크고, idx1는 column 하나로 크기가 매우 작기 때문입니다. 그래서 idx1을 사용하는 것이 효율적입니다.
• 주의: 항상 작은 것만 가져오는 것은 아니고 mysql 8.0에서는 innodb에 캐시된 버퍼풀을 통해 더 최적의 index를 사용하기도 합니다.
• mysql에서 인덱스에서 해당 위치를 읽고 실제 테이블의 row를 찾게 되면 매우 많은 random access가 생깁니다. → 쿼리가 느려질 수 있습니다.
• 그래서 최대한 두 가지에 집중하는 것이 좋습니다.
  • 쿼리가 인덱스를 사용하고
  • 최소의 레코드만 테이블 데이터를 가져오도록 튜닝하는 편이 좋습니다.

그런데 재밌는 점은 실행 계획에서 분명 idx1를 사용한다고 했는데 실제 버퍼풀을 조회해보면 PRIMARY KEY를 사용합니다. 

https://manshei.tistory.com/147

MySQL에서 count(*)의 처리방식 테스트

위에서 언급한 것처럼 스토리지 API의 ha_records가 5.7 버전 기준으로 클러스터링 인덱스만 사용하도록 되어 있는데 연관이 있는 것인지, (위 글은 mysql 8 버전 이상) 아무튼 의도된 동작은 아닌 것으로 보입니다.

 

 

4. WHERE 조건을 가진 COUNT()

where 조건을 가진 COUNT의 경우 처음 언급했던 것처럼 covering index와 non-covering index로 나뉩니다.

SELECT COUNT(1)   FROM counter WHERE ix1='comment';
SELECT COUNT(*)   FROM counter WHERE ix1='comment';
SELECT COUNT(ix1) FROM counter WHERE ix1='comment';

 

SELECT COUNT(fd1)
FROM counter
WHERE ix1 = 'comment';

 

• extra에 using index인 경우가 커버링 인덱스입니다.
• 위 두 예시의 차이가 무엇이기에 하나는 covering, 하나는 non-covering이었을까요?
• fd1는 nullable한 column이므로 테이블 레코드까지 가서 값을 확인해야 하고, 그래서 속도가 확 느려집니다.
• covering index의 실행 계획이 10배 가량 빠릅니다.

혹시 커버링 인덱스와 논 커버링 인덱스의 차이에 대해 잊으신 분을 위해 잠시 짚고 넘어가겠습니다.

 

• Non Clustered Key에는 데이터 블록의 위치가 없습니다.
• 즉, 인덱스 조건에 부합한 where 조건이 있더라도 select에 인덱스에 포함된 컬럼 외에 다른 컬럼값이 필요할때는 Non Clustered Key에 있는 Clustered Key 값으로 데이터 블록을 찾는 과정이 필요합니다. 커버링 인덱스는 여기서 "2. 실제 데이터 접근" 의 행위 없이 인덱스에 있는 컬럼값들로만 쿼리를 완성하는 것을 이야기 합니다.