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만 실제로 해시 조인에서 사용할 수 있는 문제 수정
새 해시 테이블은 일반적으로 이전보다 빠르며 정렬, 키/값 및 동일한 키가 많은 시나리오에서 더 적은 메모리를 사용
