아래의 내용들은 제가 주로 사용하는 MySQL중에서도 InnoDB 스토리지 엔진과의 연관성이 있습니다.

예전의 나의 생각.

select 시 모든 레코드 컬럼에 대해서 조회하는 애스터리스크 (*)를 사용하지 말고, 필요한 컬럼만 조회하는 것이 어떨까요?

몇 년 전, 꼬꼬마 개발자 시절에 위와 같은 피드백을 받은 적이 있었다.

아래의 예시처럼 말이다.

-- Before 
select * from table_1;

-- After
select column_1, column_2 from table_1;

그때의 생각으로는 문제될 것이 단순 네트워크 통신에 드는 Cost(비용)만 생각을 했었기 때문에 나는 딱히 공감이 되지 않았다.

나는 예전에 네트워크를 공부했었기에 네트워크에 대해 잘 알고 있었고, MySQL을 data warehouse급으로 사용하지 않는 한 크게 비용이 달라질 것이라고 생각하지 않았기 때문이다.

게다가 소프트웨어 레이어에서 생각해본다면 영속화된 테이블의 레코드를 추상화한 인터페이스에 대해 반드시 필요한 데이터가 할당이 되어있어야 할 수도 있을 것이다. (이 경우 필요하지 않은 컬럼까지도 스토리지로부터 가져와야 된다.)

그러면, 필요한 데이터만 제공하도록 인터페이스를 각각 분리해내면 되지 않을까?

또 다른 컬럼 데이터가 필요하다면 그에 맞는 소프트웨어 인터페이스를 추가적으로 만들어야 하고, 또 추가적인 쿼리를 만들어야 할 것이다.

‘소프트웨어 관점에서 유지보수 포인트가 굉장히 늘어날 수도 있다’라고 생각했다.

그래서 딱히 크게 공감이 되지는 않았었다.

정말 애스터리스크를 사용 하면 안 되는 이유들은 무엇이 있을까?

현재의 내가 생각하는 애스터리스크를 사용했을 때의 문제점.

1. 통신 비용 증가

예를 들어, 인덱스 스캔만으로 데이터를 가져올 수 있는 경우임에도 불구하고 (Covering Index 등) 모든 컬럼을 조회하기 위해 불필요한 Disk I/O가 발생할 수 있다.

대부분의 경우 index size <= table size 이기 때문에, table scan의 cost가 훨씬 높다.

또한 레코드의 모든 컬럼들을 클라이언트에게 반환하기 위해 네트워크 통신 비용이 증가할 수 있다.

만약, 데이터 웨어하우스 급으로 사용되는 InnoDB 스토리지 엔진이 있다면 이러한 통신 비용을 무시하지 못할 것이다.

2. 만약 정렬이 필요할 시 Sort Buffer로 인한 비용 증가

우리는 서비스를 개발할 때 많은 상황에서의 정렬 쿼리를 사용하게 된다. 정렬을 수행할 때 애스터리스크로 인해 쿼리 수행에 대한 비용이 증가될 수도 있다.

이 내용을 이해하기 위해서는 먼저 MySQL 엔진 아키텍처의 Sort Buffer를 이해할 필요가 있을 것 같아, 잠깐 소트 버퍼에 대한 내용을 간단하게 적어보겠다.

소트 버퍼, Sort Buffer란?

MySQL 엔진은 스토리지로부터 가져온 데이터들을 정렬하기 위해 별도의 메모리 공간을 할당한다. 이 메모리 공간을 소트 버퍼(Sort Buffer)라고 한다.

sort buffer 사이즈는 아래 사진과 같이 확인할 수 있다.

show global variables like 'sort_buffer_size';

여기서 한 가지 생각해 봐야 할 문제가 있다.

만약 스토리지로부터 가져온 데이터가 sort buffer의 크기보다 커지게 될 경우 어떻게 될까?

Multi-Merge

만약, data size > sort buffer size인 경우엔 처리해야 할 모든 레코드들을 일정 크기로 나누어 소트 버퍼 내에서 처리를 수행하고, 그 결과를 디스크에 임시적으로 기록한다.

그리고, 최종적으로 각 버퍼 크기만큼 정렬된 레코드를 병합하는 과정을 거친다.

이 과정을 멀티 머지(Multi-Merge)라고 한다.

멀티 머지 과정을 그림으로 표현하면 다음과 같다.

그럼 어떤 문제가 있을까?

위에서 언급한 멀티 머지 과정을 보자.

가져온 데이터가 소트 버퍼의 사이즈보다 큰 경우 메모리에서 정렬하고 임시 파일에 쓰고, 머지 하는 과정을 거친다.

물론 애스터리스크를 사용하지 않는 상황에서도 해당 과정은 필요하다.

다만, 애스터리스크로 인해 Multi-Merge로 발생되는 Cost가 훨씬 늘어날 수도 있다라는 것이다.

2-1. Optimize Sort Buffer

누군가 다음과 같이 생각할 수도 있을 것 같다.

음… Multi-Merge 과정으로 인해 발생하는 Cost를 줄이기 위해 Sort Buffer Size를 크게 할당하면 되지 않을까?

매우 잘못된 생각이다.

Sort Buffer는 클라이언트 세션별로 고유하게 할당되는 영역이기에, 각 세션별로 공유가 되지 않는다.

일단, 글로벌로 설정하지 않고 특정한 쿼리에 대한 코스트를 줄이기 위해 해당 세션에 대해서만 Sort Buffer Size를 늘린다고 할지라도 해당 세션이 얼마나 많이 수행될지 예측할 수 없는 상황에서

갑작스럽게 많은 요청이 발생할 경우 Sort Buffer로 인해 Out Of Memory 상황까지 도달할 수 있다.

그리고, 직접 테스트는 하지 않았지만 이러한 상황들을 고려하여 적절한 수치로 Sort Buffer의 크기를 늘려볼지라도 성능은 크게 증가하지 않는다.

위에서 언급한 정렬과 관련된 문제점을 회피(?)할 수 있는 방법도 존재한다.

바로 Sorting Algorithm을 이용하여 약간의 회피를 할 수도 있다.

MySQL에서는 크게 아래의 두 가지 방식이 존재한다.

(1) 쿼리 결과를 생성하기 위해 필요한 모든 컬럼을 스토리지로부터 가져와서 메모리에서 정렬하는 방식

(2) 쿼리에 필요한 레코드의 Primary Key와 정렬에 필요한 컬럼만 스토리지로부터 가져와서 정렬하는 방식

• 이 경우 정렬 연산이 끝나고, 다시 한 번 스토리지로부터 데이터를 가져와서 반환하게 된다.

만약, 많은 레코드의 조회가 필요한 경우에는 (2)번을 이용하여 Sort Buffer에서 발생하는 멀티머지로 발생되는 cost를 줄이고 최종적으로 스토리지로부터 다시 조회하는 것이 성능개선에 도움이 될 수도 있다.

다만 이러한 SortBuffer, Multi-Merge, 정렬 알고리즘 등을 이해하지 못 한 채 MySQL Optimizer가 판단하는 내용을 곧이 곧대로 믿고 애스터리스크 쿼리를 수행한다면 큰일날 수도 있다.

가령, 전체 컬럼에 대한 조회가 불필요함에도 불구하고 애스터리스크를 사용했고 옵티마이저의 판단하에 (1)번의 정렬 알고리즘을 사용하게 되는 경우를 생각해보자. 끔찍하다.

특정 쿼리에 대해 옵티마이저가 어떤 정렬을 사용한지는 옵티마이저 트레이스 테이블의 정보를 활용하여 확인할 수 있다.

• MySQL 8.0 Optimizer Trace

3. 임시 테이블 (Internal Temporary Table)에서 발생하는 비용

이 부분은 테스트를 해보고 추가적으로 업데이트를 해야겠다.

정리하며.

또 추가적으로 생각나는 것이 있다면 이 포스팅에 업데이트를 해야겠다.

나중에 누군가 애스터리스크를 무작정 사용하는 쿼리를 보았을 때, 이 포스팅의 내용이 적절히 도움이 될 수 있길 바란다.

읽어주셔서 감사합니다.

참고 내용

• MySQL 8.0 Reference Manual
• Real MySQL 8.0 vol.1