MySQL 8.0 Optimization 변경사항

 

 

Optimizing SELECT Statement

WHERE 조건절

• WHERE 절 처리에 대한 최적화 기술 
• SELECT 문을 예시로 들었지만 UPDATE,DELETE 동일하게 적용

불필요한 괄호 제거 

((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d)

불필요한 조건 제거 

(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6) -> b=5 OR b=6

Data type의 범위를 벗어난 조건 제거 (tinyint unsigned 의 최대값은 255)

# CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);   SELECT * FROM t WHERE c ≪ 256; -≫ SELECT * FROM t WHERE 1;

 

Range Scan

Range Access Method for Single-Part Indexes

MySQL 8.0에서 Range Scan을 구성하는대 사용할 수 없는 조건은 삭제되고 범위가 겹치면 결합된다.

아래의 SELECT문에서 key1은 indexed , nonkey는 not indexed 컬럼임.

SELECT * FROM t1 WHERE   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR   (key1 < 'bar' AND nonkey = 4) OR   (key1 < 'uux' AND key1 > 'z');

key1의 추출 프로세는 아래와 같다.

1. original WHERE

2. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z')

3. nonkey=4 그리고 key1 LIKE '%b'는 RANGE SCAN에 사용 될 수 없으므로 제거 된다. 다만 RANGE SCAN 할 때 일치 하는 행을 놓치지 않도록 제거 된 조건은 TRUE로 치환 된다.

4. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR (key1 < 'bar' AND TRUE) OR (key1 < 'uux' AND key1 > 'z')

5. 항상 참인 조건과 거짓인 조건으로 치환.
   a. (key1 LIKE 'abcde%' OR TURE) 는 항상 참
   b. (key1 < 'uux' AND key1 > 'z')는 항상 거짓

6. (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

7. 불필요한 TRUE/FALSE 상수 제거 

8. (key1 < 'abc') OR (key1 < 'bar')

9. 겹치는 RANGE를 하나로 결합하면 아래와 같은 최종 조건이 생성

10. (key1 < 'bar')

Range Access Method for Multiple-Part Indexes

다중 인덱스 RANGE SCAN은 단일 인덱스 RANGE SCAN의 확장임.

key1 이라는 인덱스에 아래와 같이(key_part1,key_part2,key_part3)의 컬럼이 존재 할 때 

key_part1  key_part2  key_part3   NULL       1          'abc'   NULL       1          'xyz'   NULL       2          'foo'    1         1          'abc'    1         1          'xyz'    1         2          'abc'    2         1          'aaa'

key_part1=1 조건은 아래와 같이 정의 될 수 있다.

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

위의 조건에 해당하는 튜플은 4번째,5번째,6번째 튜플이 해당 된다.

반대로 key_part3='abc' 조건은 RANGE SCAN에 사용 될 수 없다. 

Equality Range Optimization of Many-Valued Comparisons

아래와 같이 인덱스 걸린 col_name 컬럼이 존재 할 때 

col_name IN(val1, ..., valN) col_name = val1 OR ... OR col_name = valN

col_name이 여러값 중 하나와 같으면 각 표현식은 참임. 이러한 비교를 동등비교라고 함.

옵티마이저는 Equal RANGE SCAN 비용을 아래와 같이 추정.

• 최대 하나의 행이 주어진 값을 가질 수 있으므로 각 추정치는 1.
• col_name의 컬럼을 포함하는 인덱스가 고유하지 않으면 옵티마이저는 인덱스 통계로 dive해서 각 범위의 row수를 추정.

인덱스 dive를 사용하면 옵티마이저는 범위의 처음과 마지막의 row수를 추정치로 사용.

예를 들어 col_name IN (10,20,30)의 조건에는 3개의 equal range가 존재하며 옵티마이저는 2번의 dive를 통해 row 추정치를 생성.

인덱스 dive는 정확한 row 추정치를 제공하지만 비교값 수가 증가하면 옵티마이저가 추정치 생성하는데 오래 걸림.

인덱스 통계를 사용할 경우 인덱스 dive 보다는 덜 정확하지만 비교값 수가 많으면 더 빠른 row 추정치를 생성

최상의 추정치를 위해 인덱스 통계 업데이트를 ANALYZE TABLE 명령어를 통해 수행 가능.

Skip Scan Range Access Method

 

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2)); INSERT INTO t1 VALUES   (1,1), (1,2), (1,3), (1,4), (1,5),   (2,1), (2,2), (2,3), (2,4), (2,5); INSERT INTO t1 SELECT f1, f2 + 5 FROM t1; INSERT INTO t1 SELECT f1, f2 + 10 FROM t1; INSERT INTO t1 SELECT f1, f2 + 20 FROM t1; INSERT INTO t1 SELECT f1, f2 + 40 FROM t1; ANALYZE TABLE t1;   EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

• 위의 시나리오에서 MySQL 8.0 이전 버전에서는 모든 행을 가져오는 INDEX SCAN을 한 다음 WHERE 조건 f>40을 만족하는 RESULT SET이 생성 되었음.
  • RANGE SCAN이 효율적이지만 f1 컬럼이 사용할 인덱스가 없기 때문에 INDEX SCAN을 했었음.
  • MySQL 8.0.13 버전 부터는 LOOSE INDEX SCAN 과 비슷한 방식 SKIP SCAN을 사용해서 f1의 각 값에 대해 MULTIPLE RANGE SCAN을 수행.

1. 1.  인덱스의 구성 컬럼 중 f1의 고유값을 건너 뜀.
   2. 나머지 인덱스 f2 에서 f>40을 만족하는 값에 대해 하위 RANGE SCAN 수행

Range Optimization of Row Constructor Expressions

옵티마이저는 아래의 쿼리에도 RANGE SCAN ACCESS 방법을 선택할 수 있음.

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

MySQL 8.0 이전에는 RANGE SCAN ACCESS 방법을 사용하려면 아래와 같이 쿼리를 작성해야 했었음.

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' ) OR ( col_1 = 'c' AND col_2 = 'd' );

 

Index Merge

• 인덱스 머지는 multiple range scan 행을 검색하고 결과를 하나로 병합한다. 
• 단일 테이블의 인덱스 스캔만 병합.
• EXPLAIN output 의 extra 항목 에서 index merge 사용 되는 것 확인 가능.

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;   SELECT * FROM tbl_name   WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;   SELECT * FROM t1, t2   WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')   AND t2.key1 = t1.some_col;   SELECT * FROM t1, t2   WHERE t1.key1 = 1   AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

Index Merge Intersection Access Algorithm

해당 알고리즘은 WHERE 절이 AND 조건과 같이 결합될 때 적용된다. 

SELECT * FROM innodb_table   WHERE primary_key < 10 AND key_col1 = 20;   SELECT * FROM tbl_name   WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

Index Merge Union Access Algorithm

해당 알고리즘은 Index Merge Intersection Access 알고리즘과 비슷하며 WHERE 절이 OR 조건과 같이 결합 될 때 적용된다. 

 

SELECT * FROM t1   WHERE key1 = 1 OR key2 = 2 OR key3 = 3;   SELECT * FROM innodb_table   WHERE (key1 = 1 AND key2 = 2)      OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

Index Merge Sort-Union Access Algorithm

해당 알고리즘은 WHERE 절이 OR 조건과 같이 결합 될 때 사용되지만 Index Merge 알고리즘은 적용되지 않음.

sort-union 알고리즘과 union 알고리즘의 차이는 sort-union 알고리즘의 경우 모든 행의 id를 먼저 페치하고 행을 리턴하기전에 정렬함.

SELECT * FROM tbl_name   WHERE key_col1 < 10 OR key_col2 < 20;   SELECT * FROM tbl_name   WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

Index Condition Pushdown

• ICP는 MySQL이 인덱스를 사용한 테이블 row 검색에 대한 최적화. 
• ICP가 없는 경우 스토리지 엔진은 인덱스를 이용해서 테이블 행을 찾은 다음 WHERE 조건에 맞는 결과를 MySQL 서버로 반환함.
• ICP가 활성화 되어 있고 WHERE조건 일부를 사용할 수 있는 경우 MySQL 서버는 WHERE 조건을 스토리지 엔진으로 푸시함. 
• 이후 스토리지 엔진은 푸시된 인덱스 조건을 평가해서 만족하는 경우만 테이블에서 row 를 읽어옴.
• ICP는 스토리지 엔진이 테이블에 액세스 해야 하는 횟수와 MySQL 서버가 스토리지 엔진에 액세스해야 하는 횟수를 줄일 수 있게 함.

 

아래의 조건을 만족하면 ICP 기능 사용 가능.

• TABLE Full Scan을 해야 하는경우 range, ref, eq_ref,ref_or_null 액세스 방식에 사용.
• Partitioning Table로 구성된 InnoDB , MyISAM 스토리지 엔진에 사용 가능.
• InnoDB 스토리지 엔진의 경우 보조 인덱스에만 사용.
  • ICP의 목표는 읽어 들이는 row를 줄여 I/O를 낮추는 것임.
  • InnoDB의 경우 클러스터드 인덱스로 구성되어 전체 레코드가 이미 InnoDB 버퍼로 읽혀지기 때문에 ICP로 인한 I/O 감소 효과가 없음.
• 서브쿼리를 참조하는 조건은 ICP를 사용할 수 없음.
• Stored Function을 참조하는 조건은 ICP를 사용할 수 없음.
• 트리거를 사용하는 조건은 ICP를 사용 할 수 없음.

ICP 동작 방식

• 인덱스의 다음 row 를 가져옴 (전체 테이블 row는 아님)
• WHERE 조건에 만족하는지 테스트. 조건에 충족 되지 않으면 인덱스의 다음 row 를 가져옴.
• 조건에 만족하면 인덱스를 이용하여 테이블 전체 row를 읽음.

아래와 같이 TABLE에 사람과 주소에 대한 정보가 포함되어 있고 ( zipcode,lastname,firstname) 으로 인덱스가 있다고 가정했을 때 

SELECT * FROM people   WHERE zipcode='95054'   AND lastname LIKE '%etrunia%'   AND address LIKE '%Main Street%';

MySQL은 index를 사용해서 zipcode=95054인 사람은 검색할 수 있으나, 두번째 부분은 인덱스를 이용할 수 없으므로 zipcode=95054를 가진 모든 사람을 검색해야 함.

반면 ICP를 이용하면 MySQL은 전체 row를 읽기 전 lastname LIKE '%etrunia%' 부분을 확인하고 전체 row를 읽는 것을 방지 

Nested-Loop Join Algorithms

MySQL은 Nested-Loop 알고리즘을 이용해서 테이블간 Join을 수행.

Nested-Loop Join Algorithm

nested-loop join(NLJ) 알고리즘은 루프의 첫 번째 테이블에서 한번에 하나씩 row를 읽고 각 row를 조인의 다음테이블에 nested loop로 전달하는 방식으로 동작

Table   Join Type t1      range t2      ref t3      ALL     for each row in t1 matching range {   for each row in t2 matching reference key {     for each row in t3 {       if row satisfies join conditions, send to client     }   } }

 

Outer Join Simplification

쿼리의 FROM 절 뒤의 TABLE 표현식은 대부분 단순화 가능.

파싱 단계에서 RIGHT OUTER JOIN은 동등 쿼리의 LEFT OUTER JOIN으로 변환 됨.

(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

위의 RIGHT JOIN은 아래와 같이 LEFT JOIN으로 변경.

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)

 

T1 INNER JOIN T2 ON P (T1,T2) 형식의 모든 INNER JOIN은 WHERE 조건에 대한 결합으로 조인되는 목록 T1,T2,P(T1,T2)로 대체 됨.

옵티마이저는 OUTER JOIN에 대한 PLAN을 세울 때 각 작업에 대해 내부 테이블보다 외부 테이블을 먼저 액세스 하려는 계획을 세우고, Nested-Loop 알고리즘으로 외부 조인을 실행하려 하기 때문에 최적화에 제한이 생김.

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)   WHERE P(T1,T2) AND R(T2)

위와 같은 쿼리가 있을 때  작성된대로 실행되면 옵티마이저는 T1에 액세스하는 실행 계획을 생성하고 이는 비효율적임.

MySQL은 WHERE 조건이 NULL-rejected 인 경우 OUTER JOIN의 쿼리를 변경함.(즉 OUTER JOIN을 INNER JOIN으로 변경함)

NULL-rejected 조건은 OUTER JOIN으로 생성된 null행에 대해 FALSE 또는 UNKNOWN으로 평가되는 경우를 뜻함.

따라서 위의 OUTER JOIN 은 아래와 같이 된다. 

T1 LEFT JOIN T2 ON T1.A=T2.A

 

아래와 같은 조건식은 NULL-rejected임. NULL-complemented row에 대해 참이 아니기 때문. (T2 컬럼이 NULL이다)

T2.B IS NOT NULL T2.B > 3 T2.C <= T1.C T2.B < 2 OR T2.C > 1

아래와 같은 조건식은 NULL-rejected가 아니다. NULL-complemented row가 참일 수 있기 때문.

T2.B IS NULL T1.B < 3 OR T2.B IS NOT NULL T1.B < 3 OR T2.B > 3

 

OUTER JOIN 단순화 예시

아래의 쿼리에서 WHERE 조건에 첫번째 OUTER JOIN은 NULL-rejected가 아니다. 두번째 OUTER JOIN은 NULL-rejected이다. 

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                  LEFT JOIN T3 ON T3.B=T1.B   WHERE T3.C > 0

이 때 OUTER JOIN에서 NULL-rejected 이면 INNER JOIN으로 쿼리가 변경 된다. 

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A                  INNER JOIN T3 ON T3.B=T1.B   WHERE T3.C > 0

원래의 쿼리에서는 OUTER JOIN이므로 JOIN PATH가 고정되어 T1,T2,T3의 실행계획만 세워지지만 변경된 쿼리에서는 T3,T1,T2순서의 액세스 방향도 고려 됨.

위의 쿼리는 다시 한번 아래와 같이 변경 가능.

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3   WHERE T3.C > 0 AND T3.B=T2.B

T3.B=T2.B 조건이 NULL-rejected 이므로 나머지 OUTER JOIN도 INNER JOIN으로 치환 가능. 따라서 모든 OUTER JOIN이 INNER JOIN으로 치환 되었음.

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3   WHERE T3.C > 0 AND T3.B=T2.B

 

Condition Filtering

• 조인 처리에서 prefix row는 조인의 한 테이블에서 다음 테이블로 전달 되는 row임.
• 일반적으로 옵티마이저는 row 조합수가 급격히 증가하지 않도록 조인 실행 계획을 세울 때 prefix row가 적은 테이블을 선택하려고 함.
• condition filtering이 없으면 옵티마이저가 선택한 액세스 방법에 따라 WHERE절에서 선택한 예상 행 수를 기반으로 함.
• condition filtering을 사용하면 옵티마이저가 액세스 방법에서 고려하지 않은 WHERE절의 관련 조건을 사용할 수 있으므로 prefix row 추정치를 개선 할 수 있음.
  • 예를 들어  조인에서 현재 테이블의 행을 선택하는데 사용할 수 있는 인덱스 기반 액세스 방법이 있더라도 WHERE 절에서는 다음 테이블로 전달되는 row에 대한 추정치를 필터링 할 수 있음.
• EXPLAIN output에서 추정치가 표현되고 백분율로 표현된다. 최대값은 100이고 row filtering이 발생하지 않았음을 의미.
• prefix row의 수는  row x filtering 이다. 예를 들어 row의 수는 1000개 이고 filtering이 20%인 경우 prefix row count는 1000 x 0.2 = 200임.

 

아래와 같은 쿼리의 경우 

SELECT *   FROM employee JOIN department ON employee.dept_no = department.dept_no   WHERE employee.first_name = 'John'   AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01';

• employee 테이블 1024 rows.
•  department 테이블 12 rows.
• 두개의 테이블 모두 dept_no에 인덱스 있음.
• employee 테이블 first_name에 인덱스 있음.
• employee.first_name에 8 row 만족함.

• employee.first_name = 'John'

• employee.hire_date 150 row 만족함

• employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'

• 위의 2개 조건을 만족하는 row 는 1개임

위의 쿼리에서 condition filtering이 없는 경우 EXPLAIN 결과 

+----+------------+--------+------------------+---------+---------+------+----------+ | id | table      | type   | possible_keys    | key     | ref     | rows | filtered | +----+------------+--------+------------------+---------+---------+------+----------+ | 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 100.00   | | 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   | +----+------------+--------+------------------+---------+---------+------+----------+

필터링이 수행되지 않으므로 prefix row count 는 8 x 100% = 8 임.

 

condition  filtering이 동작 하는 경우 employee.hire_date의 BETWEEN 조건에 대해 16.31%의 필터링 효과를 추정함.

+----+------------+--------+------------------+---------+---------+------+----------+ | id | table      | type   | possible_keys    | key     | ref     | rows | filtered | +----+------------+--------+------------------+---------+---------+------+----------+ | 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 16.31    | | 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   | +----+------------+--------+------------------+---------+---------+------+----------+

이 경우 prefix row count 는 8 x 16.31%= 1.3 임.

ORDER BY 

MySQL이 ORDERB BY 절을 충족하기 위해 인덱스를 사용할 수 있는 경우와 , 그렇지 않을 때 filesort에 대한 내용 확인.

LIMIT 여부에 따라 ORDER BY 정렬 순서가 달라질 수 있다.

Use of Indexes to Satisfy ORDER BY 

MySQL은 인덱스를 사용해서 ORDER BY절을 수행해서 filesort 작업을 피할 수 있음.

WHERE 절의 조건이 INDEX와 정확히 일치하지 않아도 ORDER BY 절은 INDEX 사용 가능.

(key_part1,key_part2)에 인덱스가 있을 경우 아래의 쿼리는 인덱스를 사용하여 ORDER BY 가능.

SELECT * FROM t1   ORDER BY key_part1, key_part2;

다만 SELECT * 을 사용하므로 key_part1,key_part2 보다 많은 row를 선택.

위의 쿼리의 경우 전체 인덱스를 스캔하고 인덱스에 없는 열을 찾기위해 테이블 스캔하게 되고 테이블 스캔후 결과를 정렬하는 것 보다 비용이 더 발생하게 됨.

이 경우 옵티마이저는 인덱스를 선택하지 않게 될 가능성이 있다. 

아래의 쿼리처럼 인덱스에 포함된 열만 SELECT 하면 filesort를 피할 수 있다.

SELECT pk, key_part1, key_part2 FROM t1   ORDER BY key_part1, key_part2;

아래의 두 쿼리도 key_part1은 상수와 비교 되고, WHERE절이 인덱스 range scan이 table scna보다 저렴하다고 판단되면 인덱스 사용함.

SELECT * FROM t1   WHERE key_part1 > constant   ORDER BY key_part1 ASC;   SELECT * FROM t1   WHERE key_part1 < constant   ORDER BY key_part1 DESC;

아래의 쿼리에서 ORDER BY는 key_part1의 이름을 지정하지는 않았지만 선택된 모든 행에 key_part1이 있으므로 인덱스 사용함.

SELECT * FROM t1   WHERE key_part1 = constant1 AND key_part2 > constant2   ORDER BY key_part2;

 

MySQL 8.0 이전 버전에서는 GROUP BY가 특정 조건에서는 암시적으로 ORDER BY 되었으나 MySQL 8.0부터는 발생하지 않음.

다만 쿼리 결과는 이전과 다를 수 있어 정렬이 필요하면 ORDER BY 함께 사용해야 함.

Use of filesort to Satisfy ORDER BY

MySQL은 인덱스를 사용하여 ORDER BY절을 충족 할 수 없는 경우 filesort를 이용하여 ORDER BY 를 수행.

MySQL 8.0.12 이전 버전에서는 filesort를 빠르게 하기 위해 sort_buffer_size 값을 지정하면 버퍼 크기가 미리 할당 되어 메모리를 과도하게 사용하는 문제가 있었음.

MySQL 8.0.12 이후에서는 sort_buffer_size를 점진적으로 사용하기 때문에 메모리 사용에 대한 걱정없이 크게 설정 가능함.

 

filesort작업은 결과 집합이 너무커서 메모리 사이즈를 벗어나는 경우 disk에 temp 파일을 사용. 일부 쿼리는 메모리 내에서 filesort가 이루어짐.

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

Influencing ORDER BY Optimization

ORDER BY 속도를 높이려면 MySQL이 인덱스를 활용하여 ORDER BY 할 수 있게 해야 함.

• sort_buffer_size 늘림.
• sort buffer에 저장된 컬럼의 크기는 max_sort_lenght 변수 값의 영향을 받음. 
  • 예를 들어 튜플이 긴 문자열의 값을 저장하고 max_sort_length 의 값을 늘리면 sor buffer 튜플의 크기도 증가하기 때문에 sort_buffer_size를 늘려야 할 수 있음.
  • merge pass(temporary file)에 대한 모니터링은 sort_merge_passes 상태 값으로 확인 가능.
• 한번에 많은 행을 읽을 수 있게 read_rnd_buffer_size의 값을 늘림.
• tmpdir 변수 설정에서 물리적으로 떨어진 disk에 round-robin 방식으로 tmp fill이 생성 될 수 있게 설정 가능함. 

GROUP BY 

GROUP BY 절의 가장 일반적인 동작 방식은 전체 테이블을 스캔한 뒤 새 임시 테이블을 만들어 그룹을 검색하고 집계함수를 적용하는 방식.

몇몇 CASE에서 MySQL은 인덱스를 사용해서 임시 테이블 생성을 피할 수 있음.

GROUP BY 절에 인덱스를 사용하기 위해 가장 중요한 전제조건은 인덱스에 걸린 키 순서대로 조건이 명시 되어야 함.

임시 테이블 생성 대신 인덱스 사용으로 대체 할 수 있는지 여부는 WHERE 절의 조건 및 집계 함수에 따라 달라짐.

 

인덱스를 통해 GROUP BY 하는 방법에는 아래의 두가지 방법이 있음.

Loose Index Scan

이 액세스 방법은 인덱스에 있는 키의 일부분만 사용하기 때문에 loose index scan이라고 함.

loose index scan이 가능한조건

• 단일 테이블에 대해서
• GROUP BY는 인덱스의 가장 왼쪽 prefix만 지정. 예를들어 테이블 t1에 (c1,c2,c3)에 인덱스가 있는 경우  
  • GROUP BY c1,c2 는 Loose Index Scan 적용 되고  
  • GROUP BY c2,c3 (인덱스 가장 왼쪽 c1 컬럼이 조건에 없음) / GROUP BY c1,c2,c4 (c4는 인덱스에 없는 컬럼) 일 때는 Loose Index Scan 적용 안됨.
• SELECT LIST에서 사용되는 집계함수는 MIN(),MAX()가 유일하며 모두 동일한 컬럼을 참조. 컬럼은 인덱스에 있어야 하며 GROUP BY 바로 뒤에 와야 함.
• MIN(),MAX()를 제외하고 쿼리에서 참조된 GROUP BY 인덱스 외에는 모두 상수여야 함.
• 인덱스에 속한 컬럼의 경우 전체 길이가 모두 인덱싱 되어야 함. 예를들어 c1 VARCHAR(20), INDEX (c1(10)의 경우 인덱스는 Loose Index Scan을 사용할 수 없음.

Loose Index Scan을 사용할 수 있는 경우 EXPLAIN의 extra 부분에 Using index for group-by 가 표시 됨.

테이블 t1 (c1,c2,c3,c4) 에 인덱스 (c1,c2,c3)가 있을 때 아래의 쿼리는 Loose Index Scan 사용 가능.

SELECT c1, c2 FROM t1 GROUP BY c1, c2; SELECT DISTINCT c1, c2 FROM t1; SELECT c1, MIN(c2) FROM t1 GROUP BY c1; SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2; SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

 

아래의 쿼리들은 Loose Index Scan이 사용되지 않음.

• MIN(),MAX() 이외의 집계 함수 

• SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

• 인덱스의 제일 왼쪽 컬럼이 조건에 없음

• SELECT c1, c2 FROM t1 GROUP BY c2, c3;

• GROUP BY 뒤에 오는 키 부분은 참고 했지만 상수비교가 없음.위의 쿼리가 Loose Index Scan이 되기 위해선 WHERE c= const 조건이 들어가야 함.

• SELECT c1, c3 FROM t1 GROUP BY c1, c2;

Tight Index Scan

Tight Index Scan은 쿼리의 조건에 따라 full index scan 또는 range index scan임.

Loose Index Scan 조건이 충족 되지 않아도 여전히 임시 테이블 생성 회피 가능.

WHERE절에 range 조건이 있는 경우 조건을 충족하는 key만 읽음. WHERE 절로 정의 된 각 RANGE의 모든 key를 읽거나 RANGE 조건이 없으면 전체 인덱스를 스캔하기 때문에  Tight Index Scan이라고 부름.

Tight Index Scan을 사용하면 RANGE 조건을 만족하는 모든 키를 찾은 후 그룹화 함.

 

아래의 쿼리에서 테이블 T1 (c1,c2,c3,c4)에 인덱스 idx(c1,c2,c3)가 있다고 가정할 때 Loose Index Scan은 동작하지 않지만 Tight Index Scan은 동작함.

• c2 = 'a' 조건이 있음.

• SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

• 상수 조건이 있음.

• SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

DISTINCT

많은 CASE에서 ORDER BY 와 DISTINCT가 같이 사용되면 임시테이블이 필요함.

대부분의 경우 DISTINCT 절은 GROUP BY의 특수한 경우로 간주 됨.

예를들어 아래의 쿼리는 같다.

SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;   SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

 이러한 동등성으로 DISTINCT 쿼리의 최적화 방법은 GROUP BY 최적화와 거의 일치한다.

LIMIT

MySQL은 LIMIT row_count 절이 있고 HAVING 절이 없는 경우 최적화 가능.

• LIMIT으로 몇개의 행만 선택하는 경우 MySQL은 인덱스를 이용한 full table scan함.
• LIMIT row_count를 ORDER BY 와 결합해서 사용하면 전체 결과를 정렬하는 대신 정렬 된 결과의 첫번째 row_count 행을 발견하면 정렬을 중지함.
• LIMIT row_count와 DISTINCT를 결합하면 row_count의 고유행을 찾는 즉시 중지

ORDER BY 절 뒤에 LIMIT 유무에 따라 정렬 결과가 다름.

mysql> SELECT * FROM ratings ORDER BY category; +----+----------+--------+ | id | category | rating | +----+----------+--------+ |  1 |        1 |    4.5 | |  5 |        1 |    3.2 | |  3 |        2 |    3.7 | |  4 |        2 |    3.5 | |  6 |        2 |    3.5 | |  2 |        3 |    5.0 | |  7 |        3 |    2.7 | +----+----------+--------+

ORDER BY 뒤에 LIMIT 이 오는 경우

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5; +----+----------+--------+ | id | category | rating | +----+----------+--------+ |  1 |        1 |    4.5 | |  5 |        1 |    3.2 | |  4 |        2 |    3.5 | |  3 |        2 |    3.7 | |  6 |        2 |    3.5 | +----+----------+--------+

일반적으로 ORDER BY 기준 컬럼으로 정렬되고 이는 SQL 표준임.

만약 결과에 대한 순서가 중요한 경우 ORDER BY 에 정렬이 필요한 컬럼을 추가하면 됨.

예를 들어 id값이 고유해야 하는 경우 아래와 같이 쿼리를 변경.

mysql> SELECT * FROM ratings ORDER BY category, id; +----+----------+--------+ | id | category | rating | +----+----------+--------+ |  1 |        1 |    4.5 | |  5 |        1 |    3.2 | |  3 |        2 |    3.7 | |  4 |        2 |    3.5 | |  6 |        2 |    3.5 | |  2 |        3 |    5.0 | |  7 |        3 |    2.7 | +----+----------+--------+   mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5; +----+----------+--------+ | id | category | rating | +----+----------+--------+ |  1 |        1 |    4.5 | |  5 |        1 |    3.2 | |  3 |        2 |    3.7 | |  4 |        2 |    3.5 | |  6 |        2 |    3.5 | +----+----------+--------+

 

 

Optimizing Subqueries,Derived Tables, View References, and CTE

Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations

옵티마이저는 semijoin전략을 사용하여 Sub-Query 실행을 최적화 함.

두 테이블간의 Inner Join에서 1:M 관계의 조인은 M개의 데이터 셋이 만들어짐. 이 중에서 중요한 정보는 일치 항목 수가 아니라 일치 항목이 있는지 여부임.

클래스와 클래스 명단 (등록된 학생)을 나열하는 class 및 roster라는 테이블이 있다고 가정하면, 실제로 등록한 학생이 있는 수업을 나열하려면 아래와 같은 쿼리가 만들어 짐.

SELECT class.class_num, class.class_name     FROM class     INNER JOIN roster     WHERE class.class_num = roster.class_num;

위의 쿼리 결과는 각 학생에 대해 클래스가 한번씩 나열되고 이는 불필요한 중복이 발생함.

class_num이 클래스 테이블의 Primary Key라고 가정하면 SELECT DISTINCT를 사용하여 중복 제거 가능하지만, 나중에 중복 제거를 위해 일치하는 모든 row를 먼저 생성하는 것은 비효율적임.

아래의 sub-query로 중복 제거된 결과를 얻을 수 있음.

SELECT class_num, class_name     FROM class     WHERE class_num IN         (SELECT class_num FROM roster);

여기서 옵티마이저는 IN 절이 roster 테이블에서 각 Class 번호의 인스턴스를 하나만 반환하는 하위 쿼리로 인식함.

이 경우 쿼리는 semijoin을 사용할 수 있다. 즉 명단의 행과 일치하는 class의 각 행을 하나씩만 반환하는 것.

IN 절의 경우 EXISTS로 같은 결과를 얻을 수 있음. (보통의 경우 EXISTS는  조건에 만족하는 row 의 true/false만 비교하기 때문에 IN 보다 연산 속도가 빠르다)

SELECT class_num, class_name     FROM class     WHERE EXISTS         (SELECT * FROM roster WHERE class.class_num = roster.class_num);

MySQL 8.0.16 이상에서 EXISTS 와 IN절은 동일하게 semijoin 으로 적용.

MySQL 8.0.17 부터는 아래의 쿼리가 anti join으로 변환 됨.

• NOT IN (SELECT ... FROM ...)
• NOT EXISTS (SELECT ... FROM ...)
• IN (SELECT ... FROM ...) IS NOT TRUE
• EXISTS (SELECT ... FROM ...) IS NOT TRUE
• IN (SELECT ... FROM ...) IS FALSE
• EXISTS (SELECT ... FROM ...) IS FALSE

Anti join은 일치하는 항목이 없는 row만 반환하는 조인임.

SELECT class_num, class_name     FROM class     WHERE class_num NOT IN         (SELECT class_num FROM roster);

위의 쿼리는 내부적으로 anti join으로 SELECT class_num,class_name FROM class ANTI JOIN roster ON class_num으로 rewrite 됨.   class 테이블의 각 row에 대해 명단에서 일치하는 항목이 발견되는 즉시 버려짐.

비교되는 조건이 NULLABLE 인 경우는 anti join 변환이 되지 않는다.

MySQL에서 sub query가 semi join으로 처리 되기 위해서 아래의 조건을 충족해야 한다.

• WHERE 절 또는 ON절의 최상위 레벨에서 나타나는 IN, = ANY , EXISTS 여야 함.

• SELECT ...     FROM ot1, ...     WHERE (oe1, ...) IN         (SELECT ie1, ... FROM it1, ... WHERE ...);

• UNION이 없는 단일 SELECT 일 것.
• HAVING 절이 없어야 함.
• 집계 함수가 없어야 함.
• LIMIT 절이 없어야 함.
• outer query에서 STRAIGHT_JOIN이 없어야 함.
• DISTINCT 구문은 허용되나 내부적으로 제거 됨. semi join 적용에 따라 자동으로 중복 제거됨.
• sub query에 GROUP BY 절이 올 경우 허용되나 무시 됨.
• semi join이 실행 될 때 순서는 상관이 없으므로 ORDER BY 절 사용은 허용되나 내부적으로 제거 됨.

sub query가 위의 조건을 충족하면 MySQL은 비용기반으로 semi join을 수행.

• Duplicate Weedout : semi join을 join처럼 실행하고 임시 테이블을 사용하여 중복 레코드 제거
• FirstMatch : sub query쪽 테이블을 먼저 스캔하여 조건을 만족하는 첫번째 row를 선택하여 전체 row에 대한 scan을 하지 않음. 

Optimizing Subqueries with Materialization 

옵티마이저는 Materialization을 사용해서 sub query를 보다 효율적으로 수행함. Materialization은 일반적으로 메모리에 있는 임시테이블을 이용해서 Sub Query 결과를 생성하기 때문에 쿼리 실행 속도가 높아짐.

MySQL은 처음으로 Sub Query의 결과를 필요로 할 때 그 결과를 임시 테이블로 Materialization함. 결과가 필요할 때마다 다시 임시테이블을 참조함. 

Sub Query Materialization은 가능하면 메모리 내 임시테이블을 사용하고 테이블이 너무 커지면 디스크를 사용.

 

Materialization을 사용하지 않으면 옵티마이저는 상관 Sub Query를 비 상관 Sub Query로 Rewrite 시도.

예를들어 아래의 비상관 IN Sub Query가 있을 때

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

옵티마이저는 EXISTS Query로 Rewrite 시도함.

SELECT * FROM t1 WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

임시 테이블을 사용한 Sub Query Materialization은 이러한 Query Rewrite를 방지하고 외부의 쿼리 결과 1개당 한번의 실행이 아닌 한번만 sub query를 실행할 수 있게 함.

아래의 쿼리는 Materialization 대상.

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

위의 쿼리에서 IN 절의 조건이 UNKNOWN 또는 FALSE 인 것은 중요하지 않음. 어느 쪽이든 t1의 행은 쿼리 결과에 포함되지 않음.

 

아래의 쿼리는 Materialization 대상이 아님 (t2.b 컬럼 nullable로 가정)

SELECT * FROM t1 WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2                           WHERE where_condition);

Sub Query Materialization 되기 위한 조건

• Sub Query 안과 밖의 데이터 타입이 같아야 함. 예를들어 하나의 컬럼은 integer 타입 하나의 컬럼은 decimal 타입일 경우 Materialization 이 사용 되지 않음.
• Sub Query 안쪽이 BLOB 타입일 경우 Materialization 사용 되지 않음.

EXPLAIN output으로 Sub Query Materialization 사용 유무 확인 가능.

• Sub Query Materialization이 사용 되면 EXPLAIN select_type이 DEPENDENT SUBQUERY에서 SUBQUERY로 변경 된다. 이는 외부 테이블 하나의 row당 한번씩 반복 실행 되는 것이 아니라 sub query 한번만 실행하면 되는 것. 
• EXPLAIN output에서 show warnings 명령어를 사용하면 materialize and materialized-subquery로 표시된다.

 

Optimizing Subqueries with the EXISTS Strategy 

NULL 값이 나타내는 문제와 관련한 최적화.

아래와 같은 쿼리가 있을 때 

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL은  outer_expr에서 값을 얻어 sub query를 실행하고 값을 비교함.

가장 효율적인 방법은 inner_expr과 outer_expr이 같은 값임을 sub query에 알리는 것임.

위의 쿼리를 아래와 같이 변경.

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

위와 같이 IN → EXISTS 로 변경해서 sub query의 row 수 제한 가능.

 

Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization 

옵티마이저는 아래의 2가지 전략을 이용해서 파생된 테이블의 참조 최적화 가능.

• 파생된 테이블을 outer 쿼리 블럭에 병합
• 파생된 테이블을 내부 임시 테이블로 Materialize

아래의 2가지 예로 설명.

예시 1)

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

파생 테이블 derived_t1 테이블을 병합하면 아래와 비슷해짐.

SELECT * FROM t1;

예시 2)

SELECT *   FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1   WHERE t1.f1 > 0;

파생 테이블 derived_t2를 병합하면 아래와 비슷해짐.

SELECT t1.*, t2.f1   FROM t1 JOIN t2 ON t1.f2=t2.f1   WHERE t1.f1 > 0;

앞서 설명한 Materialized 최적화를 이용하면 derived_t1 과 derived_t2 테이블은 각각 해당 쿼리내에서 별도로 실행 됨.

 

옵티마이저는 다음 조건이 모두 참인 경우 파생된 테이블의 ORDER BY 절을 outer table 쿼리 블럭에 전파.

• 외부쿼리가 GROUP BY, Aggregate 를 사용하지 않음.
• 외부쿼리가 DISTINCT, HAVING, ORDER BY 를 사용하지 않음.

 

Derived Condition Pushdown Optimization 

MySQL 8.0.22 버전부터 적합한 쿼리에 대해 파생 테이블(Derived Table) Condition Pushdown 지원.

예를들어 SELECT * FROM ( SELECT i,j FROM t1) AS dt WHERE i > constant 의 쿼리가 있을 때 대부분의 경우 SELECT * FROM ( SELECT i,j FROM t1 WHERE i > constant) AS dt 와 같이 Pushdown 됨.

앞장에서 확인한 Derived Table에 대한 Merge를 할 수 없는 경우 ( Derived 테이블에서 Aggregate함수를 사용하는 경우) 외부 WHERE절을 Derived Table로 Push하면 처리해야 하는 행 수가 줄어들어 실행 속도가 빨라진다.

 

Outer쪽의 WHERE 조건은 아래의 경우 Materialized 된 파생 테이블로 Pushdown 가능.

• Derived Table이 Aggregate 또는 Window 함수를 사용하지 않는 경우 
  • SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11 →  SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS  dt 로 rewrite 됨.
• Derived Table이 GROUP BY를 사용하고 outer WHERE 조건의 컬럼이 GROUP BY 컬럼을 참조하는 경우 WHERE 조건을 Derived Table로 Pushdown 할 수 있음.
  • SELECT * FROM (SELECT i,j, SUM (k) AS sum FROM t1 GROUP BY i,j ) AS dt WHERE i > 10 → SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt

Derived Condition Pushdown Optimization은 아래의 제약 조건이 있음.

• Derived Table에 UNION이 사용 된 경우 
• Derived Table이 LIMIT을 사용하는 경우
• Sub query가 포함된 조건은 Pushdown 안됨.
• Derived Table이 Outer Join의 내부 테이블인 경우 Pushdown 안됨.
• Materialization 된 Derived Table이 CTE (common table expression) 이면서 여러번 참조가 될 경우 Pushdown되지 않음.