10.3.14 Indexed Lookups from TIMESTAMP Columns

10.3.14 TIMESTAMP 컬럼에서의 인덱스 기반 Lookup

Temporal 값은 TIMESTAMP 컬럼에 UTC 값으로 저장되며, TIMESTAMP 컬럼에 삽입되거나 그로부터 조회되는 값은 session time zone과 UTC 사이에서 변환됩니다. (이는 CONVERT_TZ() 함수가 수행하는 것과 같은 유형의 변환입니다. session time zone이 UTC이면 사실상 time zone 변환은 발생하지 않습니다.)

일광 절약 시간제(Daylight Saving Time, DST)와 같은 local time zone 변경 규칙 때문에, UTC와 non-UTC time zone 사이의 변환은 양방향에서 1:1이 아닙니다. UTC 값들은 서로 구분되지만 다른 time zone에서는 구분되지 않을 수 있습니다. 다음 예시는 distinct한 UTC 값들이 non-UTC time zone에서 동일해지는 경우를 보여줍니다:

1mysql> CREATE TABLE tstable (ts TIMESTAMP);
2mysql> SET time_zone = 'UTC'; -- insert UTC values
3mysql> INSERT INTO tstable VALUES
4       ('2018-10-28 00:30:00'),
5       ('2018-10-28 01:30:00');
6mysql> SELECT ts FROM tstable;
7+---------------------+
8| ts                  |
9+---------------------+
10| 2018-10-28 00:30:00 |
11| 2018-10-28 01:30:00 |
12+---------------------+
13mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
14mysql> SELECT ts FROM tstable;
15+---------------------+
16| ts                  |
17+---------------------+
18| 2018-10-28 02:30:00 |
19| 2018-10-28 02:30:00 |
20+---------------------+

참고

'MET' 또는 'Europe/Amsterdam' 같은 이름 있는 time zone을 사용하려면 time zone 테이블이 올바르게 설정되어 있어야 합니다. 설정 방법은 Section 7.1.15, “MySQL Server Time Zone Support”를 참조하십시오.

두 개의 distinct한 UTC 값이 'MET' time zone으로 변환되면 동일해지는 것을 확인할 수 있습니다. 이 현상은 optimizer가 쿼리를 실행하기 위해 인덱스를 사용하는지 여부에 따라 동일한 TIMESTAMP 컬럼 쿼리가 서로 다른 결과를 내게 만들 수 있습니다.

앞에서 제시한 테이블에서 WHERE 절을 사용하여 ts 컬럼을 단일 특정 값(예를 들어 사용자가 제공한 timestamp 리터럴)으로 검색하는 쿼리를 가정해 보겠습니다:

1SELECT ts FROM tstable
2WHERE ts = 'literal';

또한 이 쿼리가 다음과 같은 조건에서 실행된다고 가정합니다:

• session time zone이 UTC가 아니며 DST shift가 있는 경우. 예를 들면:

1SET time_zone = 'MET';

• TIMESTAMP 컬럼에 저장된 유니크한 UTC 값이 DST shift 때문에 session time zone에서는 유니크하지 않은 경우. (앞에서 제시한 예시는 이것이 어떻게 발생할 수 있는지를 보여줍니다.)

• 쿼리가 session time zone에서 DST에 진입하는 그 한 시간 안에 해당하는 검색 값을 지정하는 경우.

이러한 조건에서, WHERE 절의 비교는 nonindexed lookup과 indexed lookup에서 서로 다른 방식으로 수행되어 서로 다른 결과를 초래합니다:

• 인덱스가 없거나 optimizer가 인덱스를 사용할 수 없는 경우, 비교는 session time zone에서 수행됩니다. optimizer는 테이블 스캔을 수행하면서 각 ts 컬럼 값을 가져와 UTC에서 session time zone으로 변환하고, 검색 값(역시 session time zone에서 해석됨)과 비교합니다:

1mysql> SELECT ts FROM tstable
2         WHERE ts = '2018-10-28 02:30:00';
3+---------------------+
4| ts                  |
5+---------------------+
6| 2018-10-28 02:30:00 |
7| 2018-10-28 02:30:00 |
8+---------------------+

저장된 ts 값이 session time zone으로 변환되므로, 이 쿼리는 UTC 값으로는 서로 다르지만 session time zone에서는 같은 두 timestamp 값을 반환할 수 있습니다: DST shift가 발생하여 시계가 변경되기 전의 값 하나와, DST shift가 발생한 후의 값 하나입니다.

• 사용 가능한 인덱스가 있는 경우, 비교는 UTC에서 수행됩니다. optimizer는 인덱스 스캔을 수행하면서 먼저 검색 값을 session time zone에서 UTC로 변환한 다음, 그 결과를 UTC 인덱스 항목과 비교합니다:

1mysql> ALTER TABLE tstable ADD INDEX (ts);
2mysql> SELECT ts FROM tstable
3         WHERE ts = '2018-10-28 02:30:00';
4+---------------------+
5| ts                  |
6+---------------------+
7| 2018-10-28 02:30:00 |
8+---------------------+

이 경우 (변환된) 검색 값은 인덱스 항목에만 매칭되고, 저장된 distinct UTC 값에 대한 인덱스 항목이 또한 distinct하기 때문에, 검색 값은 그중 하나에만 매칭될 수 있습니다.

nonindexed lookup과 indexed lookup에서 optimizer 동작이 서로 다르기 때문에, 이 쿼리는 각 경우에 다른 결과를 냅니다. nonindexed lookup의 결과는 session time zone에서 일치하는 모든 값을 반환합니다. indexed lookup은 그렇게 할 수 없습니다:

• 이는 스토리지 엔진 내에서 수행되며, 스토리지 엔진은 UTC 값만 알고 있기 때문입니다.

• session time zone의 두 개의 distinct한 값이 동일한 UTC 값에 매핑되는 경우, indexed lookup은 해당 UTC 인덱스 항목과만 매칭되며 단일 row만 반환합니다.

앞선 설명에서, tstable에 저장된 데이터 세트는 우연히도 distinct한 UTC 값들로 구성되어 있습니다. 이러한 경우, 제시한 형태의 인덱스-사용 쿼리는 최대 하나의 인덱스 항목과만 매칭됩니다.

인덱스가 UNIQUE가 아니면, 테이블(및 인덱스)에 주어진 UTC 값의 여러 인스턴스를 저장할 수 있습니다. 예를 들어, ts 컬럼이 UTC 값 '2018-10-28 00:30:00'의 여러 인스턴스를 포함할 수 있습니다. 이 경우, 인덱스-사용 쿼리는 각 인스턴스를 모두 반환하며 (결과 집합에서는 MET 값 '2018-10-28 02:30:00'로 변환됨), 여전히 인덱스-사용 쿼리는 변환된 검색 값을 session time zone에서의 검색 값으로 변환되는 여러 UTC 값에 매칭시키는 것이 아니라, UTC 인덱스 항목 내의 단일 값에 매칭시킨다는 점은 변함이 없습니다.

session time zone에서 일치하는 모든 ts 값을 반환하는 것이 중요하다면, workaround는 IGNORE INDEX 힌트를 사용하여 인덱스 사용을 억제하는 것입니다:

1mysql> SELECT ts FROM tstable
2       IGNORE INDEX (ts)
3       WHERE ts = '2018-10-28 02:30:00';
4+---------------------+
5| ts                  |
6+---------------------+
7| 2018-10-28 02:30:00 |
8| 2018-10-28 02:30:00 |
9+---------------------+

양방향 time zone 변환에서의 이러한 1:1 매핑의 부재는 FROM_UNIXTIME() 및 UNIX_TIMESTAMP() 함수로 수행되는 변환과 같은 다른 컨텍스트에서도 발생합니다. 자세한 내용은 Section 14.7, “Date and Time Functions”을 참조하십시오.