문제 발생

제가 작성한 이전 게시물에서 잘못된 생각으로 글을 작성한 것을 깨달았습니다. 깨닫게 되고 데이터를 조회하는 쿼리를 진짜진짜 간단히 쿼리 개선 해보게 되었습니다.

우선 전체 상품 데이터를 조회하는 get_product_all_list 함수를 봐보겠습니다.

def get_product_all_list(page):
    products = []
    pagination = Products.query.paginate(page=page, per_page=80, error_out=False)
    for p in pagination.items:
        product = {'id': p.id,
                   'class_name': p.class_name,
                   'price': p.price,
                   'img_url': p.img_url}
        products.append(product)
    meta = get_page_list(pagination)
    return products, meta

이 함수에서 Products.query.paginate(page=page, per_page=80, error_out=False)
이 코드는 Flask 내부의 Pagination을 적용한 것으로, 데이터베이스에는 어떠한 WHERE 조건을 사용하여 쿼리를 날리지 않습니다. paginate 메소드는 페이지네이션을 수행하며, 데이터베이스에서 현재 페이지의 데이터만을 추출합니다. 즉, 데이터베이스는 페이지별로 필요한 데이터만을 가져옵니다. 저는 이때 Products 테이블에는 primary_key로 id가 있기 때문에 mysql에서는 id컬럼에 인덱스가 생성되어 있어 다음과 같은 코드를 실행하면 당연히 id 컬럼의 인덱스로 조회를 할 줄 알았습니다.

그러나 실제 데이터베이스에서 생성된 조회 쿼리는 SELECT * FROM products LIMIT 80 OFFSET 0 이렇게 됩니다. 이 쿼리를 explain으로 실행계획을 확인해 보았습니다.
다음과 같이 type이 ALL 이었습니다. 이는 테이블을 처음부터 끝까지 검색하는 경우를 말합니다. (FULL SCAN) 결국 id 컬럼의 인덱스를 통한 검색을 하지 않는 것이었습니다.

개선 시작

type: index

pagination = Products.query.order_by(Products.id.asc())
					 .paginate(page=1, per_page=80, error_out=False)

인덱스를 이용하여 데이터를 조회하려면 SELECT * FROM products ORDER BY id ASC LIMIT 80 OFFSET 0 이렇게 쿼리를 작성하면 됩니다. 다음은 쿼리에 맞게 코드를 수정했습니다. 이 쿼리는 type이 index인 것을 확인할 수 있습니다.

제가 원하는 id 컬럼의 인덱스를 사용하여 조회를 하게 되었습니다.
그러나 type이 index이면 인덱스를 처음부터 끝까지 찾아서 검색하는 경우를 말합니다.(INDEX FULL SCAN)

type: range

    start_id = (page - 1) * 80 + 1
    end_id = page * 80
    pagination = Products.query.filter(Products.id.between(start_id, end_id))
    						   .order_by(Products.id.asc())
    						   .paginate(page=page, per_page=80, error_out=False)

인덱스를 풀 스캔으로 사용하지 않고 특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우에는 어떻게 하면 될까요?
SELECT * FROM products WHERE id BETWEEN 1 AND 80 ORDER BY id ASC LIMIT 80;
이 쿼리문을 보네면 범위를 지정해 주었기에 type이 range가 됩니다. Extra도 Using where로 바뀌게 됩니다. 다음은 쿼리에 맞게 코드를 수정했습니다.

결론

저는 Flask 내부의 pagination을 적용하면 type이 range인 쿼리문이 실행될 것 이라고 생각했습니다. 그러나 실제로는 type이 ALL이었고 성능이 제일 좋지 않은 쿼리문이었다는 것을 깨달았습니다. 각 쿼리문은 파이참에서 console창으로 실행을 해보니 5s -> 4s -> 3s 였습니다. 아주 정말정말 간단히 쿼리 튜닝을 하게 되어 재밌었습니다. 잘못된 정보는 알려주시면 감사하겠습니다!