�ݺ�ߣ

MySQL 5.7 optimizer 개선사항
최초 작성일: 2015.11
최종 수정일: 2015.11
소속그룹 : 기술본부
작성자 : 김피터
1

2
목차
1. Query Rewrite Plugin
2. Avoid using temporary table for UNION ALL
3. In expression improvement
4. Merging Derived Tables into Outer Query
5. Explain on a Running Query
6. Improved HINTs
7. Improved Record Estimates for JOIN
8. Next Roadmap

3
목차
6. Improved HINTs
8. Next Roadmap

1. 비용기반 optimizer 에서 plan 의 변경 발생
2. Plan 변경으로 인한 성능 저하 -> 쿼리 수정 필요 ( add hint or rewrite 등 )
3. 바로 수정이 불가한 상황
I. ORM , ONLINE PATCH 불가 환경 등..
4. Query Rewrite Plugin 통해 Application 수정 없이 쿼리를 원하는 형태로 변경 가능
5. 쿼리 자체를 바꿀 수 있기 때문에 PLAN 변경을 위한 Rewrite 뿐 아니라 SELECT 절에 실수로
누락된 컬럼 등을 포함 시키는 것 같은 Rewrite 도 가능 ( Oracle의 stored outline, profiling
등과는 차별화 된 )
4
Query Rewrite Plugin
탄생(?) 배경

- Character by character pattern matching rewrite
- Parse 전에 rewrite
- 5.7 버전에 rewrite_lower 라는 sample plugin 이 존재하여 설치 가능
5
종류 – 2 가지
Pre-parse rewrite plugin API
Post-parse rewrite plugin API
- Parse tree pattern matching rewrite
- digest value 활용
- Parse 후에 rewrite
- 5.7 버전에 포함된 plugin

6
PLUGIN INSTALL

7
Pattern 등록
Query 실행
EXAMPLE

Select 1,2,3; Select ?,?,?;
상수를 ? 로 변경후 pattern matching 진행
1. 쿼리의 digest hash 값 계산, 이 값을 rewrite rule에 등록된 쿼리의 digest hash 값과 비교
2. Match 되는 rule digest hash 값이 있으면 normalized form으로 변경 후 rule 에 등록된 쿼
리의 normalized pattern 과 비교
3. Normalized 된 쿼리가 pattern 과 일치하면 쿼리문의 literal 값들을 비교
8
Rewrite 방식

1. Rewritten at parse time
 Prepare 때 rewrite 가 한 번 일어나고 그 이후 실행시에는 rewrite 된 쿼리로 계속 실행
2. Prepared statement 는 ?를 포함
3. Prepared statement 에 있는 ?를 매치 시키기 위해서는 Rewrite pattern 은 ?를 동일한 위치
에 포함해야 한다.
EX)
Rewrite rule : SELECT ?, 3
Prepared statement 의 pattern 매치는 다음 표와 같이 된다.
9
Rewrite 방식 ( Prepared statement )
Prepared Statement Match 여부
PREPARE s AS ‘SELECT 3, 3’ YES
PREPARE s AS ‘SELECT ?, 3’ YES
PREPARE s AS ‘SELECT 3, ?’ NO
PREPARE s AS ‘SELECT ?, ?’ NO

10
Current Database Issue
원인 : flush_rewrite_rules() 실행시 Query Rewriter가 새로운 session 으로 접속해서
rewrite_rules를 읽어서 flush 되지 않은 rule을 flush 를 시킴. 그래서 나의 session이 어느 스키
마를 선택하고 있는지 Query Rewriter가 알 수가 없음.

11
해결 : query_rewrite.pattern_database를 업데이트
update query_rewrite.rewriter_ruels set pattern_database=‘test’ where … ;

12
Qualifier가 있으면
match 안됨Qualifier가 있는 rule
을 따로 등록해줘야 함

13
Global variable
Global status

14
목차
6. Improved HINTs
8. Next Roadmap

 5.6 :
① UNION ALL 의 결과들을 항상 temporary table 에 저장
② Max_heap_table_size, tmp_table_size 이하에서는 MEMORY 엔진, 그 이상은 MyISAM
③ MEMORY : Fixed length 컬럼 ( 과도한 메모리 사용 발생 가능 )
 5.7
① Sorting 이 일어나지 않는 이상 UNION ALL 결과를 temporary table 에 저장하지 않고
직접 client로 전송.
② Client 는 기존 보다 첫 번째 row를 더 빨리 받을 수 있게 됨.
③ UNION ALL 시에 메모리 사용량과 disk 사용량이 줄어듬.
15
Avoid using temporary table for UNION ALL
개선 사항

16
EXPLAIN UNION ALL 5.6 vs 5.7
5.6 5.7

17
Show profile
First row
First row
query1
query2
query1
query2
query(union result)
query(union result)
query1
query2
query1
query2
Only for
typecasting.
Not for
storing the
result.

18
목차
6. Improved HINTs
8. Next Roadmap

 Query : CREATE TABLE t1( a INT, b INT, c INT, KEY x(a,b));
SELECT a,b FROM t1 WHERE (a,b) IN ((0,0),(1,1));
 5.6 :
① IN절 조건의 컬럼이 인덱스에 다 포함되어 있어도 index scan 이나 range scan 으로 실
행 계획이 세워지지 않는 문제
② Range optimizer가 rows list를 무시함
③ Work around : SELECT a,b FROM t1 WHERE (a=0 AND b=0) OR (a=1 AND b=1);
 5.7
① IN 절에 rows list가 있어도 range로 실행 계획 세워짐.
19
In expression improvement
개선 사항

20
테스트

21
테스트
5.6
5.7

22
목차
6. Improved HINTs
8. Next Roadmap

* Derived table : subquery in FROM clause
CREATE VIEW v1 AS (SELECT * FROM t1);
SELECT * FROM v1 JOIN t2 USING (a);
SELECT * FROM (SELECT * FROM t1) AS dt1 JOIN t2 USING(a);
23
Merging Derived Tables into Outer Query
QUERY
5.6
 View 와 달리 Derived table 은 언제나 temporay table로 materialized 된다.
5.7
 OUTER query 로 merge 되거나 materialized
 Derived table 와 view 를 동일한 방식으로 최적화
동일 쿼리
But, 다른
실행계획

Subquery 가 다음을 포함할 경우
 UNION clause
 GROUP BY
 DISTINCT
 Aggregation
 LIMIT of OFFSET
 An assignment to user variables
24
Merge가 되지 않는 경우

explain select * from (select * from t_test_2) as tt where col1=1;
25
QUERY
5.6
5.7
select * from t_test_2 where col1=1;

 VIEW
 생성 시에 ALGORITHM=MERGE , ALGORITHM=TEMPTABLE 으로 설정 값을 줄수 있다.
 옵션 주지 않으면 MERGE 를 우선적으로 함.
 DERIVED TABLE
 optimizer_switch
 derived_merge=on 을 추가 ( default ON )
 derived_merge=off 일 경우 항상 temp table 에 materialized 함.
 View 의 경우도 ALGORITHM 절을 주지 않고 생성한 경우 derived_merge 설정 값
에 따른다.
26
Merge or Materialize 선택 하는 법

27
목차
6. Improved HINTs
8. Next Roadmap

EXPLAIN [FORMAT=(JSON|TRADITIONAL)] FOR CONNECTION <id>;
28
Explain on a Running Query
SYNTAX
SPEC
 CONNECTION <id> 에서 현재 실행 중인 QUERY 의 PLAN을 보여준다.
 Long Query 의 실행 계획 확인 하는데 유용
 실행 계획이 생성 중일 때는 실행 계획이 확인 되지 않음.
 다음 DML 에 적용 SELECT/INSERT/DELETE/UPDATE

29
Explain on a Running Query
EXAMPLE

30
목차
6. Improved HINTs
8. Next Roadmap

 Optimizer switch 설정 보다 우선 함
 Hint scope ( in a statement ) : global, query block, table, index
 아직은 신규 추가된 hint 에 대해서 적용 가능
31
Improved HINTs
NEW hint syntax /*+ …*/
 BKA, BNL, MRR, ICP, SEMIJOIN, SUBQUERY, MAX_EXECUTION_TIME++
 비활성화 -> 옵티마이져가 해당 힌트를 사용하지 않도록
 활성화 -> 옵티마이져가 선택적으로 해당 힌트를 사용하도록 ( NOT FORCED )
Extended hint support
New hint key point
 Multiple hint 가능
 Query block 은 하나의 comment 만 가질 수 있고 해당 comment 는
SELECT,UPDATE,INSERT,REPLACE 문 바로 다음에 위치해야 함.
 문법이 틀린 hint 는 warning 발생
 힌트 중복시 첫 번째 힌트 사용
 Multilevel hint 가능 ( 특정 테이블에 대해서, 특정 쿼리 블록에 대해서 )

32
Improved HINTs
NEW hints - /*+ */ syntax로 제어 가능
 BKA, NO_BKA – 특정 테이블이나 쿼리 블록에 대해 Batched Key Access 알고리즘 사용 여부
제어
 BNL,NO_BNL – 특정 테이블이나 쿼리 블록에 대해 Block Nested-Loop 알고리즘 사용 여부
제어
 MRR,NO_MRR – 특정 인덱스나 테이블에 대해 multi-range read 사용 여부 제어
 NO_ICP – Index condition pushdown 을 사용하지 않도록
 NO_RANGE_OPTIMIZATION – 특정 테이블이나 인덱스에 range access를 사용하지 않도록
 MAX_EXECUTION_TIME – 쿼리문의 timeout 설정 ( milliseconds )
 QB_NAME – 틀정 쿼리 블록에 이름을 지정
 SEMIJOIN, NO_SEMIJOIN – 세미조인 사용 여부 제어
 SUBQUERY – subquery materialization 또는 IN-to-EXISTS 변환 사용에 대한 제어

33
Improved HINTs
EXAMPLE : MAX_EXECUTION_TIME ( ms )

34
Improved HINTs
EXAMPLE : SEMIJOIN

35
목차
6. Improved HINTs
8. Next Roadmap

 현재 까지의 비용기반 모델의 문제점
• 조인시에 부정확한 레코드 수 추정치
 해결책 : condition filtering
• Hard-coded cost constants
 해결책 : 조작 가능한 cost paramters
• 정밀하지 않은 cardinality/records per key 추정치
 해결책 : Interger value => Floating point
• 세부적인 비용 수치를 획득하기 어려움
 해결책 : JSON 과 MySQL WorkBench 에서의 explain 추가
36
Improved Record Estimates for JOIN
Cost Model : 5.7 주요 사항

Without condition filtering
- t1 JOIN t2
- Total cost = cost ( access method t1 ) + Prefix_rows_t1 * cost (access method t2)
- Prefix_rows_t1 is records read by t1
- Overestimation if where conditions apply! -> Suboptimal join order
37
5.6 : Record Estimates for JOIN
t1 t2Access
method
Prefix_rows_t1
Numbers of records read from t1

Condition filter
- t1 JOIN t2
- Prefix_rows_t1 takes into account the entire query condition
- More accurate record estimate -> improved JOIN order
38
5.7 : Improved Record Estimates for JOIN
t1 t2
Access
method
Number of records read
from t1
Condition filter
Prefix_rows_t1
Records passing the table
conditions on t1

- 10000 rows in the emp table
- 100 rows in the office table
- 14 rows with WHERE emp.first_name LIKE 'first_10' AND hire_date
BETWEEN '2015-01-01' AND '2015-02-01';
39
EXPLAIN 테스트 스키마

40
EXPLAIN result
5.6
5.7

�ݺ�ߣ

MySQL 5.7 NF – Optimizer Improvement

More Related Content

MySQL 5.7 NF – Optimizer Improvement