Optimizer

1. Invisible index 지원

• 옵티마이저에서 사용하지 않는 인덱스

• 인덱스가 쿼리 성능에 미치는 영향을 테스트 가능

• 인덱스를 삭제하고 다시 추가하는 작업 보다 빠름

세부 정보

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;

CREATE INDEX j_idx ON t1 (j) INVISIBLE;

ALTER TABLE t1 ALTER INDEX j_idx (j) VISIBLE;

2. Index DESC 지원

• 가장 효율적인 스캔 순서가 일부 컬럼에 대한 오름차순과 다른 컬럼에 대한 내림차순을 혼합할 때 옵티마이저가 다중 컬럼 인덱스를 사용 가능

세부 정보

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

ORDER BY c1 ASC, c2 ASC    -- idx1 사용 가능
ORDER BY c1 DESC, c2 DESC  -- idx4 사용 가능
ORDER BY c1 ASC, c2 DESC   -- idx2 사용 가능
ORDER BY c1 DESC, c2 ASC   -- idx3 사용 가능

3. 표현식 값 인덱스 생성 지원

• JSON 값과 같이 다른 방법으로는 인덱싱할 수 없는 값의 인덱싱 가능

세부 정보

CREATE TABLE t1 (
  col1 INT, 
  col2 INT,
  col3 JSON,
  INDEX func_index ((ABS(col1))),
  INDEX json_index ((CAST(col3->>'$.name' AS CHAR(30))))
);

CREATE INDEX idx1 ON t1 ((col1 + col2));

CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);

ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

4. 상수 리터럴 표현식에서 발생하는 사소한 WHERE 조건이 최적화(optimization) 단계가 아닌 준비(preparation) 단계 중 제거

• 사소한 조건을 갖는 아우터 조인이 있는 쿼리의 경우 프로세스 초기에 조건을 제거하여 조인을 단순화

세부 정보

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1

# 0 = 1이 항상 거짓이기에 옵티마이저는 불필요한 조건 제거
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2

# 옵티마이저는 다음과 같이 쿼리를 INNER 조인으로 다시 작성
SELECT * FROM t1 JOIN t2 WHERE condition_1 AND condition_2

5. 최적화 시 상수가 범위를 벗어나거나 컬럼 타입과 관련하여 범위 경계에 있는 상수 값 사이의 비교를 처리하기 위해 실행 시 각 행에 대해 수행하지 않고 처리 가능

세부 정보

CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);

# UNSIGNED TINYINT 범위 0 ~ 255
SELECT * FROM t WHERE c < 256;

# WHERE 1로 재작성
SELECT * FROM t WHERE 1;

# 컬럼이 null을 허용하는 경우
SELECT * FROM t WHERE c IS NOT NULL;

6. IN 서브쿼리와 함께 사용되는 세미조인 최적화를 EXISTS 서브쿼리에도 적용 가능

• 행과 일치하는 인스턴스 하나만 반환

세부 정보

# 실제로 등록된 학생이 있는 수업 나열
# 일치 항목이 있는 횟수만큼 한 테이블에서 행을 반환
SELECT class.class_num, class.class_name
FROM `class`
INNER JOIN roster
WHERE class.class_num = roster.class_num;

/*
+-----------+------------+
| class_num | class_name |
+-----------+------------+
|         1 | class1     |
|         1 | class1     |
|         2 | class2     |
|         3 | class3     |
|         4 | class4     |
+-----------+------------+
*/

# 중복 없는 결과 반환을 위해 IN 절 사용
# 옵티마이저는 IN절 서브쿼리가 테이블에서 각 class_num의 인스턴스 하나만 반환하도록 요구한다는 것을 인식하여 세미조인을 사용하여 최적화

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

/*
+-----------+------------+
| class_num | class_name |
+-----------+------------+
|         1 | class1     |
|         2 | class2     |
|         3 | class3     |
|         4 | class4     |
+-----------+------------+
*/

/*
show warnings Message
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `dataops`.`class`.`class_num` AS `class_num`,`dataops`.`class`.`class_name` AS `class_name` from `dataops`.`class` semi join (`dataops`.`roster`) where (`dataops`.`class`.`class_num` = `dataops`.`roster`.`class_num`)

*/


# EXISTS도 동일
SELECT class_num, class_name
FROM class
WHERE EXISTS
(SELECT * FROM roster WHERE class.class_num = roster.class_num);

7. 불완전한 SQL 조건자(즉, 값이 컬럼 이름 또는 상수 표현식이고 비교 연산자가 사용되지 않는 WHERE 값 형식을 갖는 조건자)를 컨텍스트화(contextualization) 단계에서 내부적으로 WHERE 값 <> 0으로 재작성

세부 정보

SELECT id FROM t1 WHERE id;

/*
EXPLAIN format=tree
-> Filter: (0 <> t1.id)  (cost=0.45 rows=1)
    -> Table scan on t1  (cost=0.45 rows=2)
*/

• 부울 값의 경우 EXPLAIN 출력이 이제 1과 0이 아닌 참(TRUE)과 거짓(FALSE)을 표시

• SQL 부울 컨텍스트에서 JSON 정수 0에 대한 묵시적 비교를 수행

세부 정보

CREATE TABLE t1 (id INT, col JSON);

INSERT INTO t1 VALUES (1, '{"val":true}'), (2, '{"val":false}');

# 8.0.17 이상에서 추출된 값을 JSON 정수 0과 묵시적으로 비교하면 다른 결과 출력
SELECT id, col, col->"$.val" FROM t1 WHERE col->"$.val" IS TRUE;

/*
+------+----------------+--------------+
| id   | col            | col->"$.val" |
+------+----------------+--------------+
|    1 | {"val": true}  | true         |
|    2 | {"val": false} | false        |
+------+----------------+--------------+
*/

# 8.0.16 이하
SELECT id, col, col->"$.val" FROM t1 WHERE col->"$.val" IS TRUE;

/*
+------+---------------+--------------+
| id   | col           | col->"$.val" |
+------+---------------+--------------+
|    1 | {"val": true} | true         |
+------+---------------+--------------+
*/

8. NOT IN(서브쿼리) 또는 NOT EXISTS(서브쿼리)가 있는 WHERE 조건은 내부적으로 안티조인으로 변환되어 서브쿼리 제거

세부 정보

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

/*
EXPLAIN
-> Nested loop antijoin  (cost=2.63 rows=6)
    -> Table scan on class  (cost=0.75 rows=5)
        -> Covering index lookup on roster using idx_class_num (class_num=class.class_num)  (cost=0.34 rows=1)
*/

9. 단일 테이블 UPDATE 또는 DELETE 문에서 대부분의 경우에 세미조인 변환 또는 서브쿼리 구체화를 사용

• optimizer trace에서 확인 가능

• UPDATE 또는 DELETE 문에서 [NOT] IN 또는 [NOT] EXISTS 가 있는 서브쿼리를 사용하는 경우

• ORDER BY와 LIMIT 절이 없는 경우

• 서브쿼리에 포함된 힌트와 optimizer_switch의 값에 따라

세부 정보

DELETE FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.col1 = t2.col1);

/*
EXPLAIN
<not executable="" by="" iterator="" executor="">
*/


/*
OPTIMIZER TRACE
{
"steps": [
    {
        "delete_preparation": {
            "select#": 1,
            "steps": [
                {
                    "join_preparation": {
                        "select#": 2,
                        "steps": [
                            {
                                "expanded_query": "/* select#2 */ select 1 from `t2` where (`t1`.`col1` = `t2`.`col1`)"
                            }
                        ]
                    }
                },
                "delete from `t1` where exists(/* select#2 */ select 1 from `t2` where (`t1`.`col1` = `t2`.`col1`))",
                {
                    "transformation": {
                        "select#": 2,
                        "from": "IN (SELECT)",
                        "to": "semijoin",
                        "chosen": true,
                        "transformation_to_semi_join": {
                            "subquery_predicate": "exists(/* select#2 */ select 1 from `t2` where (`t1`.`col1` = `t2`.`col1`))",
                            "embedded in": "WHERE",
                            "semi-join condition": "(`t1`.`col1` = `t2`.`col1`)",
                            "decorrelated_predicates": [
                                {
                                    "outer": "`t1`.`col1`",
                                    "inner": "`t2`.`col1`"
                                }
                            ]
                        }
                    }
                },
                {
                    "transformations_to_nested_joins": {
                        "transformations": [
                            "semijoin"
                        ],
                        "expanded_query": "delete from `t1` where ((`t1`.`col1` = `t2`.`col1`))"
                    }
                }
            ]
        }
    },

-- 이하 생략 --
*/

* 또한 REPEATABLE READ보다 낮은 트랜잭션 격리 수준에 대해 InnoDB 테이블을 사용하는 다중 테이블 UPDATE 문에서 semi-consistent read 지원

* 잠겨 있지 않은 행에 대해서는 여러 세션에서 서로 다른 부분을 수정할 수 있도록 허용

10. 해시 조인에 사용되는 해시 테이블을 다시 구현하여 해시 조인 성능이 여러 가지 향상

• 조인 버퍼에 할당된 메모리의 약 2/3만 실제로 해시 조인에서 사용할 수 있는 문제 수정

• 새 해시 테이블은 일반적으로 이전보다 빠르며 정렬, 키/값 및 동일한 키가 많은 시나리오에서 더 적은 메모리를 사용

• 해시 테이블의 크기가 증가할 때 이전 메모리를 해제 가능