InnoDB에서 clustred-index 기반 COUNT 쿼리의 Disk I/O 줄이기

주의! 이 글은 정답이 아닌, 제가 이해한 내용을 바탕으로 글을 작성했습니다. 틀린 내용이 있을 확률이 높으며, 문제가 되는 내용이 있으면 얼마든지 지적해 주시면 감사하겠습니다!

 

현재 풀고 있는 과제에서 COUNT 쿼리를 최적화할 필요가 있어 조사를 하다가 이런 글을 발견했다.

 

https://m.blog.naver.com/birdparang/221574304831

 

이 글에서는 다음과 같은 Stack Overflow의 질문에 대한 풀이를 해주는데, 일단 질문을 보자

 

https://stackoverflow.com/questions/511820/select-count-is-slow-even-with-where-clause

질문의 요는 pk를 활용해 (cluster) index를 태워 count 쿼리를 날리는데, 해당 테이블의 총 로우 수는 600만 건이고 목적 로우는 대충 400만 건 정도 되는데 쿼리 시간이 1분이 넘어간다는 것이다.

 

특이사항으로는 `xml_diff`라는 text 필드가 존재한다는 점 정도로 보인다.

 

그리고 이 질문에 대한 대답이다. 여기서 2를 보면, pk를 non-cluster index로 만들어서 해결하라는 제안을 준다.

 

처음 생각했을 때는 이게 무슨 소리지 하고 생각을 많이 하다가, cluster index와 non-cluster index의 페이지 구조를 떠올려 보니 이해가 갔다.

 

https://www.tutorialsteacher.com/sqlserver/nonclustered-index#google_vignette

위는 non-cluster index일 때의 리프 노드이다. 보다시피 데이터를 저장하는 것이 아닌, "row에 대한 참조"를 저장하고 있다.

 

즉, 일반적인 쿼리라면 non-cluster index보다 cluster index가 훨씬 빠른 것이 맞다. 데이터가 들어있는 위치에 대한 disk I/O를 수행하지 않아도 되기 때문이다.

 

하지만 count 쿼리에서 필요한 것은 "레코드의 수"이다. 데이터가 필요한 것이 아니기 때문이다. 따라서 참조를 가지는 것 만으로도 커버링 인덱스와 같은 효과를 볼 수 있다.

 

거기다가 더 좋은 점은, 레코드에 대한 참조만 가지기 때문에 "한 페이지에 들어갈 수 있는 레코드 수가 많아진다는 것"이다.

 

즉, 읽어야 하는 페이지 수를 줄임으로써 그만큼 Disk I/O를 최소화할 수 있는 방법이라 할 수 있다.

 

더욱이 현재 질문자가 올린 스키마에는 TEXT 필드가 존재한다. TEXT 필드는 글자 수 제한이라는 문제를 현재 페이지가 아니라 다른 페이지에 저장해서 해결토록 한 방법이기 때문에 Disk I/O에 대한 성능을 뚝뚝 떨어뜨리는 주 원인이다.

 

(자세한 내용은 Real MySQL의 저자이신 성욱님의 글을 읽으면 더 도움이 된다 : https://medium.com/daangn/varchar-vs-text-230a718a22a1)

 

따라서 non-cluster index를 활용했을 때의 효과가 배가 될 것으로 보인다.

 

하지만 물론 이러한 기능은 옵티마이저가 COUNT 쿼리를 날릴 때 cluster index까지 조회하지 않는다는 가정 하에 만족할 것이다.

 

실제로 그렇게 동작하는지 MySQL 공식 홈페이지를 방문해서 찾아보았으나, 어떻게 동작하는지는 자세히 나와있지 않고, 다음과 같은 글만 찾을 수 있었다.

 

InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.

 

Processing SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, use SHOW TABLE STATUS.

 

요약해 보자면, InnoDB는 보조 인덱스가 없으면cluster index를 모두 읽는데, 버퍼 풀에 인덱스 레코드가 없으면 느릴 수 있다는 것이다.

 

여기서 "데이터 레코드"가 아닌 "인덱스 레코드"를 언급한다는 점에서 내 생각과 꽤 비슷하게 동작하지 않을까 단순 추측하고 있다.

 

여기서 이런 생각을 해볼 수도 있을 것이다. 아니 row 수를 metadata 영역에 캐시하면 직접 데이터를 읽어서 해결할 수 있지 않나? 왜 굳이 데이터를 직접 읽어야 할까?

 

다음 글을 읽어보면 실제로도 MyISAM은 그러한 방법으로 동작하는 것 같다. 따라서 InnoDB보다 COUNT 쿼리의 성능이 훨씬 빠르다.

 

https://www.percona.com/blog/count-vs-countcol-in-mysql/

 

그 이유는 InnoDB는 트랜잭션을 지원하고, MyISAM은 트랜잭션을 지원하지 않기 때문이다. 트랜잭션을 지원하는 경우 RDBMS는 현재 트랜잭션에 해당하는 정보만을 제공해야 할 의무가 있다.

 

자세한 내용은 MySQL 공식 홈페이지의 내용인 다음 글의 COUNT 부분을 살펴보면 좋다.

 

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

 

그러나, 메타데이터 영역의 경우 각 트랜잭션에 알맞은 메타데이터를 모두 mvcc에 넣어 버저닝하기 어려움에 이러한 방법을 채택한 것으로 보인다.

 

또한 메타데이터에 저장한 count는 전체 레코드 수만 저장하기 때문에 range에 대한 row 수를 알 수 없다. 따라서 위 글에서도 range에 대한 count는 오히려 MyISAM이 더 느린 것을 볼 수 있다.

 

참조한 글

- https://www.percona.com/blog/count-for-innodb-tables/

- https://www.percona.com/blog/count-vs-countcol-in-mysql/

- https://aaronfrancis.com/2022/mysql-count-star-slow

- https://m.blog.naver.com/birdparang/221574304831

- https://stackoverflow.com/questions/511820/select-count-is-slow-even-with-where-clause

- https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html