mysql, mariadb 조인 쿼리 최적화 알고리즘

요즘 통계쪽 쿼리를 짜게되면서 DB를 공부하고있다.

보통 쿼리를 짜게되면 실행계획을 확인 한 후 쿼리를 튜닝하여 효율성 높은 쿼리를 짜게되는데 이번에 포스팅할 내용은 그 실행계획을 빠르게 수집하는 법이다.

쿼리에 2개 이상 테이블의 join이 들어갈 경우 어떤 테이블을 기준으로 조인을 거는지에 따라 수행시간이 엄청나게 차이가 날 수 있는데 이때 최적화된 조인을 위한 알고리즘은 2가지가 존재한다.

1. Exhaustive Search(철저한 탐색)

모든 경우의 수에 해당하는 비용을 계산한 후 최적의 조합을 찾는다. 확실하게 최적의 조합을 찾을 순 있겠으나 모든 경우의 수를 구하는 만큼 효율적이라고 보기 힘들다.

2. Greedy Search(탐욕스러운 탐색)

optimizer_search_depth 변수에 설정된 값 이상의 테이블 조인이 시도될 경우, 해당 변수에 설정된 값 만큼의 테이블로 조인 실행 계획을 산출한다. 즉 optimizer_search_depth 의 값이 3인데 조인 쿼리의 테이블개수가 6개라면 6개 모두의 조인 실행계획을 산출하는게 아니라 3개만 산출하는 것이다. 전형적인 Divide and Qonquer(분할 정복) 방식이며 각각 부분 결과들을 조합한 결과를 사용한다. 각 부분에서 최적을 찾는다고 그 조합이 무조건 전체의 최적이라고는 할 수 없다.

#optimizer_search_depth

0~63 까지의 정수 값을 설정할 수 있다. 1~62까지는 위에 설명한 설정을 사용하기 위한 값이지만 0으로 설정할 경우는 Greedy를 쓰지않겠다 라는 의미는 아니고, 옵티마이저가 개수를 정하도록 위임한다는 의미이다. 63으로 설정할 경우가 Greedy를 사용하지 않겠다 는 의미인데 이 경우엔 Exhaustive 방식을 사용하게 된다.

기본값은 62인데 사실 62개가 넘는 조인쿼리는 쓸일이 거의 없으므로 기본은 Exhaustive라고 봐도 무방하다. Greedy Search를 사용하기 위해서는 해당 값을 확 줄여야한다(4 정도로 줄여야 그래도 종종 쓸일이 있지않을까...).

해당 변수 값 확인

SHOW GLOBAL variables WHERE variable_name = 'optimizer_search_depth';