10.3.11 Optimizer Use of Generated Column Indexes

10.3.11 Optimizer에서 Generated Column Index 사용

MySQL은 generated column에 대한 인덱스를 지원합니다. 예를 들면 다음과 같습니다:

1CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

generated column gc는 식 f1 + 1로 정의됩니다. 이 컬럼은 인덱스가 생성되어 있으며, 옵티마이저는 실행 계획을 구성하는 동안 그 인덱스를 고려할 수 있습니다. 다음 쿼리에서 WHERE 절은 gc를 참조하고, 옵티마이저는 해당 컬럼의 인덱스가 더 효율적인 계획을 제공하는지 여부를 고려합니다:

1SELECT * FROM t1 WHERE gc > 9;

옵티마이저는 generated column에 대한 인덱스를, 쿼리에서 그 컬럼 이름을 직접 참조하지 않는 경우에도 실행 계획을 생성하는 데 사용할 수 있습니다. 이는 WHERE, ORDER BY, 또는 GROUP BY 절이 어떤 인덱스가 생성된 generated column의 정의와 일치하는 식을 참조하는 경우에 발생합니다. 다음 쿼리는 gc를 직접 참조하지 않지만, gc의 정의와 일치하는 식을 사용합니다:

1SELECT * FROM t1 WHERE f1 + 1 > 9;

옵티마이저는 식 f1 + 1이 gc의 정의와 일치하며 gc에 인덱스가 있다는 것을 인식하므로, 실행 계획을 구성하는 동안 그 인덱스를 고려합니다. 이는 EXPLAIN을 사용하여 확인할 수 있습니다:

1mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G
2*************************** 1. row ***************************
3           id: 1
4  select_type: SIMPLE
5        table: t1
6   partitions: NULL
7         type: range
8possible_keys: gc
9          key: gc
10      key_len: 5
11          ref: NULL
12         rows: 1
13     filtered: 100.00
14        Extra: Using index condition

사실상 옵티마이저는 식 f1 + 1을, 그 식과 일치하는 generated column의 이름으로 대체한 것입니다. 이는 또한 확장된 EXPLAIN 정보에 포함된 재작성된 쿼리에서도, SHOW WARNINGS에 의해 표시되는 내용을 통해 분명히 드러납니다:

1mysql> SHOW WARNINGS\G
2*************************** 1. row ***************************
3  Level: Note
4   Code: 1003
5Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
6         AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)

옵티마이저가 generated column 인덱스를 사용하는 데에는 다음과 같은 제한과 조건이 적용됩니다:

• 쿼리 식이 generated column 정의와 일치하려면, 그 식은 동일해야 하고 동일한 결과 타입을 가져야 합니다. 예를 들어, generated column 식이 f1 + 1인 경우, 쿼리가 1 + f1을 사용하거나, f1 + 1(정수 식)을 문자열과 비교하는 경우 옵티마이저는 일치를 인식하지 않습니다.

• 최적화는 다음 연산자에 적용됩니다: =, <, <=, >, >=, BETWEEN, 그리고 IN().

BETWEEN 및 IN()이 아닌 연산자의 경우, 어느 한 피연산자든 일치하는 generated column으로 대체될 수 있습니다. BETWEEN 및 IN()의 경우에는 첫 번째 인수만 일치하는 generated column으로 대체될 수 있고, 다른 인수들은 동일한 결과 타입을 가져야 합니다. BETWEEN 및 IN()은 아직 JSON 값을 포함하는 비교에 대해서는 지원되지 않습니다.

• generated column은 앞에서 언급한 연산자들 중 하나 또는 함수 호출을 적어도 하나 포함하는 식으로 정의되어야 합니다. 그 식은 단순히 다른 컬럼에 대한 참조로만 구성될 수 없습니다. 예를 들어, gc INT AS (f1) STORED는 컬럼 참조만으로 구성되므로, gc에 대한 인덱스는 고려되지 않습니다.

• JSON 함수를 사용하여 따옴표가 있는 문자열을 반환하는 값에서 계산되는 인덱스가 생성된 generated column과 문자열을 비교하는 경우, 컬럼 정의에서 함수 값의 추가 따옴표를 제거하기 위해 JSON_UNQUOTE()가 필요합니다. (문자열과 함수 결과를 직접 비교하는 경우에는 JSON 비교기가 따옴표 제거를 처리하지만, 인덱스 조회에서는 이 작업이 수행되지 않습니다.) 예를 들어 다음과 같은 컬럼 정의를 작성하는 대신:

1doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED

다음과 같이 작성하십시오:

1doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED

후자의 정의를 사용하면, 옵티마이저는 다음 두 비교 모두에 대해 일치를 감지할 수 있습니다:

1... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ...
2... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...

컬럼 정의에 JSON_UNQUOTE()가 없으면, 옵티마이저는 위 두 비교 중 첫 번째에 대해서만 일치를 감지합니다.

• 옵티마이저가 잘못된 인덱스를 선택한 경우, 인덱스 힌트를 사용하여 해당 인덱스를 비활성화하고 옵티마이저가 다른 선택을 하도록 강제할 수 있습니다.