[MySQL] SELECT * 가 좋지 않은 이유

acho·2024년 7월 3일

배경

TypeORM 문법에 익숙지 않은 상태로 빠르게 구현하다보니, SELECT 옵션을 적용하지 않은 사실을 뒤늦게 알게 되었습니다.
그 결과 모든 쿼리가 SELECT *로 실행되는 걸 확인해 개선 작업을 진행했습니다.
그렇다면 SELECT *는 왜 좋지 않은 쿼리일까요?

행 기반 vs 열 기반 데이터베이스

데이터베이스가 데이터를 저장하는 방식에는 두 가지 종류가 있습니다.

행 기반 데이터베이스
일반적으로 사용되는 방식입니다. 행을 단위로 해서 데이터가 저장됩니다.
즉, 한 행을 이루는 모든 컬럼이 힙 안에 연속적으로 저장됩니다.
이 때문에 하나의 컬럼의 위치를 찾으면, 같은 행에 속한 다른 컬럼에 쉽게 접근할 수 있습니다.
열 기반 데이터베이스
위와 반대로, 열 기반 데이터베이스는 열을 단위로 해서 데이터가 저장됩니다.
예를 들어 이름과 나이라는 컬럼 두 개를 갖는 간단한 테이블이 있고, 총 행의 갯수가 다섯개라고 하면, 디스크에 다음과 같이 저장됩니다 :

이름1 이름2 이름3 이름4 이름5 나이1 나이2 나이3 나이4 나이5

하나의 컬럼만 가져오는 경우라면 열 기반 데이터베이스가 매우 빠를 수 있습니다. 특히 sum 등의 집계 함수를 실행할 때 특장점을 갖습니다.
하지만, 여러 컬럼을 가져오는 경우에는 매우 비효율적이 됩니다. 인덱스가 없다고 가정했을 때, 모든 컬럼을 가져오는 SELECT *와 같은 쿼리를 열 기반 데이터베이스에서 실행하면 엄청난 오버헤드가 발생할 수도 있습니다.

물론 행 기반 데이터베이스에서도 컬럼을 명시하는 것이 좋습니다.
디스크에 데이터가 Page 단위로 저장되기 때문입니다.
만약 컬럼의 길이가 아주 긴 경우라면 하나의 페이지에 모든 행이 담기지 않을 수도 있습니다.
또는 운 나쁘게 하나의 행이 페이지와 페이지 사이에 걸쳐서 저장되어 있을 수도 있습니다. 이 경우 두 개의 페이지를 가져오게 되어 좀 더 많은 리소스가 들어갑니다.
그렇기 때문에 행 기반 데이터베이스에서도 가져오고자 하는 데이터의 양을 적게 유지하는 것이 좀 더 유리한 전략입니다.

커버링 인덱스

컬럼을 명시하는 것과 연관되는 또 다른 개념이 있습니다. 바로 인덱스인데요
필요한 모든 컬럼이 인덱스에 위치한 경우를 커버링 인덱스라고 합니다.
즉 인덱스 탐색 이후 실제 데이터를 가져오기 위해 디스크로 이동할 필요가 없는 경우입니다.

실제로 이를 적용한 예시입니다:

개선 전 - select *

 const ownJournals = await this.journalsRepository.find({ where: { userId: userId } });

-> Limit: 200 row(s)  (cost=15 rows=30) (actual time=1.1..1.27 rows=30 loops=1)
    -> Index lookup on Journals using FK_dcd8f26897887ea1ca19e9b910a (user_id=1)  (cost=15 rows=30) (actual time=1.1..1.26 rows=30 loops=1)

개선 후 - id 컬럼만 select

const ownJournals = await this.journalsRepository.find({ where: { userId }, select: ['id'] });

-> Limit: 200 row(s)  (cost=4.02 rows=30) (actual time=0.33..0.353 rows=30 loops=1)
    -> Covering index lookup on Journals using FK_dcd8f26897887ea1ca19e9b910a (user_id=1)  (cost=4.02 rows=30) (actual time=0.328..0.347 rows=30 loops=1)

MySQL에서는 PK를 자동으로 인덱싱합니다. PK로 등록되어있는 id만 가져오도록 바꾸었더니 Index lookcup -> Covering index lookup으로 바뀌고, cost가 15 -> 4.02로 줄어든 것을 확인할 수 있습니다.

PK가 아니어도 자주 조회하게 되는 컬럼이라면 인덱싱하는 것이 좋은 선택일 수 있습니다.

Stream 처리

이 케이스는 실제 개선 작업을 진행하다가 우연히 발견한 경우입니다.

개선 전

const result = await this.entityManager
            .createQueryBuilder(Journals, 'journals')
            .orderBy('journals_id', 'ASC')
            .innerJoin(JournalsDogs, 'journals_dogs', 'journals.id = journals_dogs.journal_id')
            .where('journals_dogs.dog_id = :dogId', { dogId })
            .andWhere('journals.started_at >= :startDate', { startDate: startEndDate.startDate })
            .andWhere('journals.started_at < :endDate', { endDate: startEndDate.endDate })
            .getRawMany();

개선 후

const result = await this.entityManager
            .createQueryBuilder(Journals, 'journals')
            .select('journals.id') //select 추가
            .orderBy('journals.id', 'ASC')
            .innerJoin(JournalsDogs, 'journals_dogs', 'journals.id = journals_dogs.journal_id')
            .where('journals_dogs.dog_id = :dogId', { dogId })
            .andWhere('journals.started_at >= :startDate', { startDate: startEndDate.startDate })
            .andWhere('journals.started_at < :endDate', { endDate: startEndDate.endDate })
            .getRawMany();

바뀐 부분

--- 전
    -> Table scan on <temporary>  (cost=14.9..16.6 rows=3.33) (actual time=3.05..3.05 rows=1 loops=1)
        -> Temporary table  (cost=14.1..14.1 rows=3.33) (actual time=2.86..2.86 rows=1 loops=1)
--- 후
    -> Stream results  (cost=13.8 rows=3.33) (actual time=7.15..7.16 rows=1 loops=1)

Explain Analyze 결과가 매우 길어 실제로 변화한 부분만 가져왔습니다

Join이 들어가는 상대적으로 복잡한 쿼리인데요. SELECT로 가져올 컬럼을 명시했더니 임시 테이블을 만드는 방식 대신 스트림 방식을 사용하는 변화가 생겼습니다.
(참고로, SELECT의 대상이 되는 journals_dogs 테이블은 PK와 FK로만 이루어진 테이블이고 FK 또한 자동으로 인덱싱되어 이전 쿼리 또한 Covering Index Lookup이 사용되었습니다.)

임시테이블을 만들 때는 테이블을 만든 뒤에 -> 해당 테이블에 대한 스캔 작업을 거쳐야 하고
16.1의 cost가 필요했습니다.
Stream 방식은 13.8의 cost를 가져 약 17% 비용 절감 효과가 있었습니다.

MySQL 옵티마이저의 내부 알고리즘이 임시 테이블을 사용할지, stream을 사용할 지 결정할 때 컬럼의 갯수가 영향을 미치는 것으로 보입니다.