MySQL Query Plan

 

오늘은 제가 프로젝트중 작성했던 쿼리들을 실행 계획을 통해 index를 타고 있는지 확인해 보겠습니다. 

 

Docker로 MySQL을 띄우고

procedure를 만들어서 1만건 씩 dummy 데이터를 적재하고 조회하겠습니다. 

(데이터베이스 툴로는 DBeaver를 사용하고 있습니다.)

 

먼저 코스와 연관된 정보를 함께 조인해서 출력하는 쿼리를 보겠습니다. 

 

 

type column을 보면 locatoin 테이블과 course 좋아요에 대한 쿼리는 인덱스를 타고 있지만 course에 경우 type column이 all로 index를 타지 못하고 있습니다. 

 

풀 테이블 스캔으로 1만건을 다 스캔하고 있는데요 사실 이것은 쿼리도 문제지만 전체 데이터를 다 끌고 오는것도 문제라 생각됩니다. 

 

예시로 1만건으로 들었지만 실제 production에서 100만건,1000만건이 적재된 테이블의 경우 해당 레코드를 전부 끌고 와서 서버 메모리에 올린다고 생각해보면,, 흠,,

 

실제 쿼리는 저렇게 작성하지 않고 페이징을 적용해서 풀 테이블 스캔을 인덱스 레인지 스캔으로 40건씩만 가져올 수 있도록 해보겠습니다. 

 

MySQL에서 정렬 쿼리를 실행할 때 Sort Buffer를 사용하는데 이 Buffer의 size를 넘어가는 크기로 SELECT를 시도하면 일부를 정렬하고 그 결과를 임시로 디스크에 저장하며 여러번 반복 후 병합하면서 정렬하는 과정을 수행하는데 디스크 작업이 늘어나는 만큼 성능적인 저하로 직결될 수 밖에 없겠죠?

때문에 페이징으로 필요한 만큼 가져다 쓰는것이 좋습니다.

 

쿼리의 정렬 처리 방식은 3가지가 있는데 쿼리에 ORDER BY가 사용되면

• 인덱스 사용한 정렬
• 드라이빙 테이블만 정렬(조인이 없는 경우 포함)
• 조인 결과를 임시 테이블로 저장한 후, 임시 테이블에서 정렬

이렇게 3가지 경우가 존재합니다.

 

옵티마이저가 검토시에 정렬 처리에 인덱스를 사용 가능한지 판단하고 이용 가능하면 별도의 Filesort 과정 없이 인덱스 순서대로 읽어서 반환하고, 인덱스를 사용 못 하는 경우엔 where 조건에 일치하는 레코드를 검색하고 정렬 버퍼에 저장하면서 정렬 처리를 하는데 정렬 레코드를 최소화 하기 위해 앞서 말한 방식 중 2,3에서 선택하게 됩니다.