MySql 쿼리 성능 분석 및 튜닝

프로젝트 'Helparty'를 진행하면서 여러방식의 성능 튜닝 방법들이 있다는 걸 알게되었습니다. 

 

그리고 이번에 진행해볼 방법은 데이터베이스와 소통하는 언어를 튜닝해서 MySql이 일하는 방식을 효율적으로 안내하여 성능을 향상 시키는 '쿼리 성능 튜닝'을 진행하겠습니다. 

 

저의 웹 서비스 'Helparty'에서 사용하는 쿼리 중에 3개의 테이블을 조인 하는 쿼리의 수행시간을 확인하고 실행계획을 토대로 튜닝을 해보겠습니다. 

 

튜닝할 코드

SELECT gb.id id,
    gb.title title,
    gb.content content,
    gb.created_at create_at,
    gb.modified_at modified_at,
    g.gym_name gym_name,
    g.phone_number phone_number,
    g.address_detail address_detail,
    g.address_code address_code,
    p.title product_title,
    p.content product_content,
    p.price price,
    p.scope scope
    FROM helpartydb.gymboard gb LEFT OUTER JOIN helpartydb.product p ON (gb.id = p.gym_board_id)
    LEFT OUTER JOIN helpartydb.gym g ON (gb.gym_id = g.id)
    ORDER BY gb.id DESC LIMIT 0, 10;

 

제가 사용할 코드입니다. helpartydb에 있는 gymboard 테이블과 gym 테이블 그리고 product 테이블 입니다.

운동시설을 홍보하는 게시물에 올라갈 정보를 조회하는 쿼리입니다.

 

UML Diagram으로 표현하면 이렇게 생겼습니다. 

운동시설 홍보 게시물(gymboard)이 있고 그 게시물을 올리는 주체가 되는 운동시설(gym) 그리고 홍보 게시물(gymboard)에서 판매하는 상품(product)가 있습니다. 

 

이제 이렇게 조인으로 만들어진 쿼리에 대한 튜닝을 계획해보겠습니다. 

 

튜닝 계획 세우기

 

먼저, show profile 쿼리로 쿼리의 성능을 보겠습니다. 

 

약 0.011초가 걸립니다. 

쿼리의 성능 튜닝을 해서 저 시간을 줄여보겠습니다. 

 

쿼리 튜닝을 하기에 앞서 실행계획을 뽑아보겠습니다. 

 

이 실행계획을 통해 알 수 있는 정보를 제 나름대로 정리해보겠습니다. 

 

1. gymboard 테이블이 먼저 탐색 됩니다. 그리고 product 테이블이 탐색 되고 마지막으로 gym 테이블이 탐색됩니다. 
2. 3개의 테이블이 모두 type이 ALL인 것을 보니 인덱스를 사용하지 않고 풀 테이블 스캔을 했습니다. 그리고 이건 사용할 수 있는 인덱스가 있다면 매우 비효율적인 방법입니다. 탐색할 기준이 있는데도 불구하고 기준을 무시하고 처음부터 끝까지 다 훑어보기 때문입니다. 
3. Extra를 보면 Using temporary를 사용하여 연산 중에 중간에 저장할 테이블을 만듭니다. 그리고 ORDER BY를 사용해서 Using filesort로 인덱스를 사용하지 않고 퀵소트로 정렬시킵니다. 마지막으로 Using join buffer를 사용하여 드리븐 테이블에서 검색 기준으로 사용할 인덱스를 buffer에 저장합니다.

 

튜닝할 요소

 

1. Join 순서

gymboard 테이블과 gym 테이블의 관계는 1 : 1입니다. 그리고 gymboard 테이블과 product 테이블의 관계는 1 : N 입니다. 그래서 하나의 gymboard 레코드에는 하나 이상의 product 레코드가 매칭되어 있습니다. 

 

그리고 Join은 되도록이면 적은 레코드를 탐색해야하는 테이블이 먼저 Join 되도록 하는게 성능에 효율적입니다. 

이 부분을 제가 이해한 대로 설명 드리면 2 * 5 인 상황에서는 2개의 드라이빙 레코드에서 각 레코드와 매칭된 드리븐 레코드를 2번에 걸쳐 조인할 수 있지만 5 * 2 인 경우에는 5번에 걸쳐 조인할 수 있기 때문입니다.

 

따라서 레코드의 숫자는 대략 gymboard = gym < product이므로 원래 순서가 gymboard, product, gym 이었다면 gymboard, gym, product로 바꾸겠습니다. 

 

2. Index 설정

인덱스의 설정을 추가해서 성능을 개선할 수 있습니다. 

지금은 모든 테이블이 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하여 탐색하고 있습니다. 

이것은 모든 레코드를 탐색하는 것이기에 인덱스로 사용할 칼럼이 있다면 비효율적인 방법입니다.

 

제 쿼리를 살펴보면 gymboard의 id는 product가 gym_board_id의 칼럽으로 가지고 있습니다. 그리고 Join의 조건이 gymboard.id = product.gym_board_id 이므로 product 테이블은 gym_board_id 칼럼을 인덱스로 설정하면 테이블 전체를 살펴보지 않고 해당 인덱스만을 검색하게 됩니다. 

 

튜닝 결과

조인의 순서를 바꾸고 Index 설정을 추가해준 결과 

 

이러한 실행 계획이 나왔습니다.

실행계획을 보면 Product 테이블의 탐색 방법이 ALL에서 ref로 바꼈습니다.

인덱스를 사용해 참조 검색을 한 것입니다. 

 

 

실행 속도는 4배 정도 빨라졌습니다. 

 

추가

서브쿼리를 사용시 주의점

SELECT  gb.id id,
    gb.title title,
    gb.content content,
    gb.created_at create_at,
    gb.modified_at modified_at,
    g.gym_name gym_name,
    g.phone_number phone_number,
    g.address_detail address_detail,
    g.address_code address_code,
    p.title product_title,
    p.content product_content,
    p.price price,
    p.scope scope
    FROM (select id, title, content, created_at, modified_at, gym_id from helpartydb.gymboard order by id desc) gb, helpartydb.product p, helpartydb.gym g
    where gb.id = p.gym_board_id and gb.gym_id = g.id
    LIMIT 0, 10;

이렇게 Join을 사용하지 않고 서브쿼리를 사용하여 작업을 처리하여도 결과는 같게 나옵니다. 

심지어 Join을 사용했을 때보다 더 쿼리를 이해하기 쉬워보이기도 합니다. 

 

하지만 이러한 서브쿼리는 결국 옵티마이저에 의해 다시 Join으로 해석되어 수행됩니다. 그래서 결국 제가 Join을 쓰나 옵티마이저가 Join을 쓰나 작업 내용은 같습니다. 제가 애초에 Join으로 연산을 요청하면 옵티마이저가 Join으로 해석하는 시간은 절약할 수 있을거 같긴 합니다.