Mysql Query Plan - Intellij 를 활용하여 Plan query 를 해보자

안녕하세요 ㅎㅎ 

오늘은 이제 Database 항목으로 다시 찾아오게 되었습니다.!!

실제 업무를 진행하다보면, 비즈니스 사항에 맞는 쿼리를 작성하는 경우가 정말 많은데요.

 

내가 작성한 쿼리가 올바른가?

이대로 production 코드에 반영해도 괜찮은가?

 

에 대한 의문을 해결해줄 수 있는 Query Plan 과 어떻게 Intellij 를 통해서 활용할 수 있는지 알아보겠습니다.

Mysql 이 띄워진 Container를 활용하자

설명에 사용된 기술: Mysql, Mysql Container, Spring Data JPA

Query Plan 이란?

QueryPlan 의 정의는 Wiki 페이지에서 찾아볼 수 있는데요.

 

SQL 관계형 데이터베이스 관리 시스템의 데이터 접근에 사용되는 순서에 대한 순서 집합

즉 우리가 만든 쿼리를 제출했을 때 어떤식으로 데이터 접근을 하게 되는지 알려주게 됩니다.

select * from employee_table;

Table 에 대한 검색 조건 쿼리를 실행할 때, 어떤식으로 검색할 것인지 결과를 알려주게 되는 것이죠.

Query Plan 을 해보자!!

의외로 사용하는 방법은 매우 간단한데요.

EXPLAIN SELECT * FROM employee_table;

바로 EXPLAIN 키워드를 활용해주시면 됩니다.

EXPLAIN query 시 동작 결과

무언가가 엄청 많죠.? 

하나씩 항목에 대해 살펴보려고 합니다.!

항목	설명
id	쿼리 안에 있는 select 문에 대한 순차적인 식별자
select_type	select 문의 유형  ( SIMPLE, PRIMARY, SUBQUERY, UNION )
table	참조되는 table
type	어떤식으로 table 이 join 되는지 알려주게 된다. system: 1개 이하의 row 를 가진 table const: 테이블에 조건을 만족하는 row 가 1개일 때 eq_ref: primary key 나 unique key 로 검색하는 경우 ref: 인덱스로 지정된 컬럼끼리의 '=' '<=' 와 같은 조건문 검색일 때 unique_subquery: 오직 하나의 결과만 반환하는 'IN' 이 포함된 sub query의 경우 index_subquery: unique subquery 와 성격은 동일하지만 여러 개의 결과를 반환하는 경우 range: 특정한 범위의 키를 매칭할 때 사용하는 경우. BETWEEN IN '>=' ... all: 모든 row 를 스캔하는 경우
possible_keys	테이블에서 row 를 매핑시키기 위한 key 목록을 나열
key	실제적으로 쿼리 실행에 사용된 key 의 목록
ref	key column 에 지정된 인덱스와 비교되는 column 또는 constant
rows	결과 산출에 있어서 접근되는 record 의 수
extra	실행계획에 있어서 표현되는 부가적인 정보

참 항목이 많죠.?

걱정하지 마세요~!

제가 나열한 모든 항목들을 볼 필요는 없습니다.

우리가 봐야되는 항목은 다행히도 빨간색 항목입니다.

 

Query 의 유형 type

Query 에 사용된 key

Query 가 스캔된 row 의 개수

 

위 항목들만 파악해도 이미 내가 작성한 Query 에 어떤 이상이 있는지 알게되는 것이니까요.!

자 그러면 실제 Intellij 예시 코드와 함께 살펴보러 들어가보겠습니다.

Intellij 에서 활용하기

우선 기본적으로 local 에서 Mysql container 가 띄워져 있다고 가정할 것인데요.

 

아 어떻게 띄워야 되는거지? 라고 생각되시면 아래 링크를 참조하세요.!!ㅎㅎ

https://huisam.tistory.com/entry/mysql-replication

MySql - Master Slave Replication 구조 만들어보기

자 그러면 container 준비는 끝났으니 한번 직접 Table 을 만들어보겠습니다.

 

이번 예시는 Cafe 에서 제공하는 메뉴들을 한번 도메인 Table 로 지정해보겠습니다.

Cafe Menu 에 대한 테이블 생성

Cafe Menu 에 대한 type 에 대한 검색을 쉽게 하기 위해서,

CafeType 에 대해 인덱스를 지정했다고 가정해봅시다.

물론 예제이니, 실제 운영에서는 신중히 반영하세요!

@Entity
@Table(
    name = "cafe_table",
    indexes = [
        Index(name = "cafe_table_type_index", columnList = "type", unique = false)
    ]
)
@EntityListeners(AuditingEntityListener::class)
class CafeTable(
    @Id
    @GeneratedValue
    private val id: Long? = null,

    @Column(name = "name")
    private val name: String,

    @Column(name = "type", length = 20)
    @Enumerated(EnumType.STRING)
    private val type: CafeType,
) {
    @CreatedDate
    @Column(name = "created_at", nullable = false)
    var createdAt: LocalDateTime = LocalDateTime.now()

    @LastModifiedDate
    @Column(name = "modified_at")
    var modifiedAt: LocalDateTime? = null


    override fun equals(other: Any?): Boolean {
        if (this === other) return true
        if (other !is CafeTable) return false

        if (id != other.id) return false

        return true
    }

    override fun hashCode(): Int {
        return id.hashCode()
    }
}

enum class CafeType {
    COFFEE, BLENDED, JUICE, ICE_CREAM
}

테이블을 만들었으니, 테스트 데이터들을 조금 더 쉽게 만들어볼 것인데요.

일일히 sql insert 문을 다 만들기에는 너무나 귀찮으므로 임시 코드로 한번 만들어 볼 예정입니다.

interface CafeRepository : JpaRepository<CafeTable, Long>

@Component
class CafeDataInitializer(
    private val cafeRepository: CafeRepository,
) {
    @PostConstruct
    fun init() {
        cafeRepository.saveAll(
            (0..1000).map {
                val type = CafeType.values()[it % 4]
                CafeTable(name = "$type name $it", type = type)
            }
        )
    }
}

간단하게 Repository 를 만들고, 

Bean 등록 후속 처리 이벤트를 활용하여 저장해볼 생각입니다.

 

그러면 Application 을 실행해보면 ..?

무수히 쏟아지는 Sequence 와 insert 항목들

자 그러면 준비는 끝났습니다.

먼저 Intellij 에서 Database 를 연결해줍시다

Intellij DataSource 설정

User 와 Password , Port 를 확인해서 데이터베이스에 연결해줍니다.

그러면 아래와 같이 Console 창을 띄울 수 있게 되는데요.

Query Console 실행하기

그러면 Intellij 상에서 Query 를 준비할 준비(?) 는 모두 끝났습니다.

SELECT * FROM cafe_table;

를 실행하면?

Query result

정상적으로 데이터가 모두 표기되는 것을 확인할 수 있습니다.!

그러면 위의 쿼리에 대해서 한번 Plan 을 확인해볼까요.?

EXPLAIN Query 요청해보기

간단합니다. 위에서 설명드린 것처럼 EXPLAIN 키워드만 붙이면 됩니다.

해당 Query 를 실행하게 되면 아래와 같이 결과가 나오게 됩니다.

Query EXPLAIN 결과

type 은 Table Full scan 에 해당하는 ALL type 과 영향받은 row 는 1001 개로 확인되었네요.

헉 너무나 안좋은 쿼리네요

Index Query 해보기

그러면 위의 예시에서 Index 를 태울 수 있는 Query 를 한번 만들어볼까요?

Cafe 에서 Coffee 가 들어간 메뉴만 한번 검색해보도록 하겠습니다.

SELECT * FROM cafe_table
WHERE type = 'COFFEE';

짜잔 Coffee 인 것만 나오게 하면?

Index Query 결과

앗 우리가 원하는 type 만 검색되었네요 ㅎㅎ

그러면 이 Query 에 대해서 어떻게 Plan 이 되었는지 확인해보러 갈까요?

EXPLAIN SELECT * FROM cafe_table
WHERE type = 'COFFEE';

두근두근...

Index Query 에 대한 결과

type 은 인덱스가 지정된 검색이라는 유형의 ref

영향받은 row 의 개수는 230개 라고 나오게 되었네요 ㅎㅎ

 

우리는 테스트 데이터로 1000개 밖에 안넣어놓았지만,

실제 데이터라면 이보다 더 훨씬 데이터양을 가지고 있으니 더 많은 영향을 끼치게 되겠죠?

 

실제로 현업에서 커스텀 검색 Query 를 비즈니스로 제공할 때에는

위와 같은 Query Planing 을 통해서 DB 에 어떠한 영향을 미칠지 간략하게 파악할 수 있게 됩니다.

 

개인적으로 제가 현업에서 검색 기능 제공에 대한 일을 해보니까

검색에 대한 기능 을 제공할 때는 코드 리뷰 보다는 Query Plan 에 대한 검토가 더 중요한 것 같습니다.

한번 현업에서 활용해보는 것은 어떨까요?

정리

Database 에서 검색 Query 에 대해 Planning 하는 법을 알게 되었습니다.

• EXPLAIN 키워드를 통해서 내가 작성한 Query 를 plan 해보자!
• 결과를 분석하여 더 효율적인 Query 를 작성할 수 있게 된다!

참고

Mysql Explain 실행 계획 보는 법