MySQL(17)
주차 학습내용 (9.3.2 조인 최적화 알고리즘 ~ 9장 마무리 )
9.3.2 조인 최적화 알고리즘
MySQL엔 조인 쿼리 실행계획 최적화를 위한 알고리즘이 2개 있다. 5.0부터 있었다.
MySQL에서의 조인최적화가 개선되었다고는 하나, 테이블 개수가 많아지면 최적화 하기 어렵고, 하나의 쿼리에서 조인되는 테이블의 개수가 많아지면, 실행 계획을 수립하는데에만 몇 분이 걸릴 수 있다. 특정 한계를 넘으면 실행 계획 시간만 몇시간, 며칠로 늘어나기도 한다. 왜 그런지 살펴보자. 어떻게 그런 현상을 피할 수 있는지도 보자.
적절한 알고리즘 한글명칭이 없어서 영어로 표시하고, 예제는 다음 SQL로 가보자.
SELECT *
FROM t1, t2, t3,t4
WHERE...
9.3.2.1 Exhaustive 검색 알고리즘
5.0과 그 이전버전의 조인 최적화 기법.
FROM절에 명시된 모든 테이블 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법.
그림은 최적의 조인 순서를 찾는 방법을 표현한 것.
테이블이 20개라면, 20!개가 된다. 이전버전에서의 exhaustive는 테이블 10개만 되도 실행계획 수립이 몇 분이 걸린다.
9.3.2.2 Greedy 검색 알고리즘
5.0부터 도입된 조인 최적화 기법이다. 그림은 optimizer_search_depth(OSD) 변수 값이 2일때 최적의 조인순서를 탐색하는 그림이다.
- 전체 N개의 테이블 중에서 optimizer_search_depth 개수의 테이블로 가능한 조인 조합을 생성
- 1번중 최소 비용 하나를 선정(t3-t2)
- 2번에서 선정된 조합의 첫 테이블을 (그림의 t3) 부분 실행 계획의 첫번째 테이블로 선정.
- 전체 N-1개의 테이블중(3번에서 선택된 테이블 제외)에서 Optimizer_search_depth 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
- 4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 “부분 실행 계획”에 대입해 실행 비용을 계산.
- 5번의 비용 계산 결과, 최적 실행계획에서 두번째 테이블을 3번에서 생성된 부분 실행계획의 두 번째 테이블로 선정 (3차)
- 남은 테이블이 모두 없어질때까지 4~6까지의 과정을 반복 실행하면서 “부분 실행 계획”에 테이블의 조인 순서를 기록
- 최종적으로 “부분 실행 계획”이 테이블의 조인 순서로 결정됨.
- OSD는 기본 62다. 0~62까지의 값을 설정 가능하고, 0이면 옵티마이저가 자동으로 결정한다. 조인에 사용된 테이블 개수 > OSD라면, OSD만큼의 테이블은 Exhaustive, 나머지는 Greedy검색이 사용된다. 조인에 사용된 테이블 개수 < OSD라면, Exhaustive만 사용된다. OSD 기본값이 62인데, 많은 테이블이 조인되는 쿼리에서 상당한 부담이다. 따라서 OSD를 4~5정도로 써라.
-Optimizer_prune_level이라는것도 있고,책에 설명하는 부분이 있다.
9.4 쿼리 힌트
버전이 업그레이드 되고, 통계 정보나 옵티마이저 최적화 방법들이 다양해지면서, 쿼리의 실행 계획 최적화가 많이 성숙하고 있다. 그러나 우리가 서비스하는 비즈니스를 100% 이해하지 못하므로, 서비스 개발자나 DBA보다 부족한 실행 계획을 수립하는 경우가 있다. 우리가 힌트를 써서 알려줘야 한다.
크게 2가지 방법
- 인덱스 힌트(9.4.1), 예전부터 사용되어오던 USE INDEX 같은 힌트,
- 옵티마이저 힌트(9.4.2) 5.6버전부터 추가되기 시작한 힌트들
여기에 포함되지 않은 STRAIGHT_JOIN같은 힌트도 있다. 여기서는 옵티마이저 힌트가 아닌 것들은 모두 모아서 인덱스 힌트절로 분류했다.
9.4.1 인덱스 힌트 질문 1
STRAIGHT_JOIN과 USE_INDEX 등을 포함한 인덱스 힌트들은 모두 MySQL 서버에 옵티마이저 힌트가 도입되기 전에 사용되던 기능들이다. 이들은 모두 SQL의 문법에 맞게 사용해야 하기 때문에 사용하게 되면 ANSI-SQL 표준 문법을 준수하지 못하게 되는 단점이 있다.
추가 설명, USE INDEX 같은게 MySQL 문법이기때문에, ANSI-SQL에는 이런게 없다.
5.6부터 추가되기 시작한 옵티마이저 힌트 들은 모두 MySQL서버를 제외한 다른 RDBMS에서는 주석으로 해석하기 때문에 ANSI-SQL 표준을 준수한다고 볼 수 있다. 그래서 가능하다면 인덱스 힌트보다는 옵티마이저 힌트를 사용할 것을 추천한다. 또한 인덱스 힌트는 SELECT 와 UPDATE 에서만 사용할 수 있다.
9.4.1.1 STRAIGHT_JOIN
STRAIGHT_JOIN은 옵티마이저 힌트인 동시에, 조인 키워드 이기도 하다(11.6.2절 JOIN UPDATE)
SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다.
다음 쿼리는 3개의 테이블을 조인하지만 어느 테이블이 드라이빙이 되고, 드리븐이 될지 알 수 없다. 옵티마이저가 그때그때 각 테이블의 통계정보와 쿼리 조건을 기반으로 가장 최적이라고 판단되는 순서로 조인한다.
SELECT *
FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no
실행계획을 보면, departments를 드라이빙 테이블로 선택했고, 두번째로 dept_emp테이블을 읽은 뒤에 employees 테이블을 읽었다. 일반적으로 조인을 하기 위한 칼럼들의 인덱스 여부로 조인의 순서가 결정되며, 조인 칼럼의 인덱스에 아무런 문제가 없는 경우에는(WHERE 조건이 있는 경우, WHERE조건을 만족하는) 레코드가 적은 테이블을 드라이빙으로 선택한다.
하지만 이 쿼리의 조인순서를 변경하려는 경우에는 STRAIGHT_JOIN 힌트를 사용할 수 있다. 다음 두 쿼리는 표기법만 다르고 동일한 쿼리다.
SELECT STRAIGHT_JOIN
e.first_name, e.last_name, d.dept_name
FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no
AND d.dept_no=de.dept_no
SELECT /*! STRAIGHT_JOIN */
e.first_name, e.last_name, d.dept_name
FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no
AND d.dept_no=de.dept_no
STRAIGHT_JOIN 힌트는 옵티마이저가 FROM절에 명시된 테이블의 순서대로 조인을 수행하도록 유도한다.
주로 다음 기준에 맞게 조인 순서가 결정되지 않는 경우에만 조정하는게 좋다.
-
임시 테이블(인라인 뷰 또는 파생된 테이블)과 일반 테이블의 조인 : 이 경우엔 거의 일반적으로 임시테이블을 드라이빙 테이블로 선정하는게 좋다. 일반 테이블의 조인 칼럼에 인덱스가 없으면, 레코드 건수가 작은 쪽을 먼저 읽도록 드라이빙으로 선택하는 것이 좋다. 대부분 옵티마이저가 적절히 해서 쿼리 작성부터 힌트 쓸 필요는 없다. 심각한 성능저하 시 고려하자.
-
임시 테이블끼리 조인: 임시테이블(서브쿼리로 파생된 테이블)은 항상 인덱스가 없기때문에, 크기가 작은 테이블을 드라이빙으로 선택해라.
여기서 언급한 레코드 건수는 인덱스를 사용할 수 있는 WHERE 조건까지 포함해서 그 조건을만족하는 레코드 건수를 의미한다.
STRAIGHT_JOIN과 유사한 옵티마이저 힌트는 다음이 있다.
- JOIN_FIXED_ORDER
- JOIN_ORDER
- JOIN_PREFIX
- JOIN_SUFFIX
9.4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX 질문 2
조인의 순서를 변경하는 것 다음으로 자주 사용되는 것이 인덱스 힌트, STRAIGHT_JOIN과는 달리 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다. 3~4개의 칼럼을 포함하는 비슷한 인덱스가 여러개 존재하는 경우에는 가끔 옵티마이저가 실수를 한다. 이때 강제로 힌트를 준다.
인덱스 힌트는 크게 3종류가 있다. 이들 모두 키워드 뒤에 사용할 인덱스의 이름을 괄호로 묶어서 사용한다. 별도로 사용자가 부여한 이름이 없는 PK는 “PRIMARY”라고 명시하면 된다.
- USE INDEX: 가장 자주 쓰이는 인덱스힌트, 권장하는 힌트, 옵티마이저가 항상 이걸 반영하는것은 아니다.
-
FORCE INDEX : USE INDEX보다 더 강제성이 강함. 그러나 USE INDEX만으로 충분해서 저자의 경험상 USE INDEX를 해도 그걸 안쓰는 경우라면 FORCE를 해도 안썼다.
- IGNORE INDEX: 반대로 못쓰게 하는것. 때로는 옵티마이저가 테이블 풀 스캔을 사용하도록 유도하기 위해 이걸 쓰기도 한다.
위 3가지에 모두 용도를 명시할 수 있다. 선택사항인데, 명시되지 않으면, 사용가능한 경우 3가지 용도로 사용한다.
- USE INDEX FOR JOIN : 여기서 JOIN은 테이블 간의 조인 뿐만 아니라 레코드를 검색하기 위한 용도까지 포함하는 용어다. MySQL서버에서는 하나의 테이블로부터 데이터를 검색하는 작업도 JOIN이라고 표현하기 때문에 FOR JOIN 이라는 이름이 붙었다.
- USE INDEX FOR ORDER BY
- USE INDEX FOR GROUP BY
책에는 예제 ….
왠만하면 옵티마이저가 잘 하니까 안건드리는게 좋다. 그리고 최적의 실행계획이란 시간에 따라서도 계속 변해서 지금 좋다고 그거만 계속 쓰면 안된다.
9.4.1.3 SQL_CALC_FOUND_ROWS
LIMIT를 사용한다면, 명시된 수 만큼 레코드를 찾으면 검색 작업을 멈춘다. 하지만 이 힌트가 있는 쿼리는 끝까지 간다. 이 힌트가 사용된 쿼리의 경우, FOUND_ROWS 함수를 이용해 LIMIT을 제외한 조건을 만족하는 레코드가 전체 몇 건이었는지 알 수 있다.
아마도, 이 기능을 웹페이지의 페이징 기능에 적용하기 위해서 검토했거나, 사용했을지도 모른다. 하지만 이 힌트의 장점이 아니라 사용하면 안되는 경우를 보자.