10.9.6 Optimizer Statistics

10.9.6 Optimizer Statistics

column_statistics data dictionary table은 column 값들에 대한 히스토그램 통계를 저장하며, 이는 옵티마이저가 쿼리 실행 계획을 구성하는 데 사용됩니다. 히스토그램 관리는 ANALYZE TABLE 문을 사용하여 수행합니다.

column_statistics table은 다음과 같은 특성을 가집니다:

• table에는 geometry type(공간 데이터) 및 JSON을 제외한 모든 데이터 타입의 column에 대한 통계가 포함됩니다.

• table은 영속적이므로, 서버가 시작될 때마다 column 통계를 다시 생성할 필요가 없습니다.

• table에 대한 업데이트는 서버가 수행하며, 사용자는 수행하지 않습니다.

column_statistics table은 data dictionary의 일부이기 때문에 사용자에 의해 직접 접근할 수 없습니다. 히스토그램 정보는 data dictionary table에 대한 view로 구현된 INFORMATION_SCHEMA.COLUMN_STATISTICS를 사용하여 확인할 수 있습니다.

COLUMN_STATISTICS에는 다음과 같은 column이 있습니다:

• SCHEMA_NAME, TABLE_NAME, COLUMN_NAME: 통계가 적용되는 schema, table, column의 이름입니다.

• HISTOGRAM: column 통계를 히스토그램으로 저장하는 JSON 값입니다.

Column 히스토그램에는 column에 저장된 값의 범위 일부에 대한 버킷들이 포함됩니다. 히스토그램은 column 통계 표현의 유연성을 위해 JSON 객체입니다. 다음은 예시 히스토그램 객체입니다:

1{
2  "buckets": [
3    [
4      1,
5      0.3333333333333333
6    ],
7    [
8      2,
9      0.6666666666666666
10    ],
11    [
12      3,
13      1
14    ]
15  ],
16  "null-values": 0,
17  "last-updated": "2017-03-24 13:32:40.000000",
18  "sampling-rate": 1,
19  "histogram-type": "singleton",
20  "number-of-buckets-specified": 128,
21  "data-type": "int",
22  "collation-id": 8
23}

히스토그램 객체는 다음과 같은 key를 가집니다:

• buckets: 히스토그램 버킷들입니다. 버킷 구조는 히스토그램 타입에 따라 달라집니다.

singleton 히스토그램의 경우, 버킷은 두 개의 값을 포함합니다:

• 값 1: 버킷에 대한 값입니다. 타입은 column 데이터 타입에 따라 달라집니다.

• 값 2: 해당 값에 대한 누적 빈도(cumulative frequency)를 나타내는 double입니다. 예를 들어 .25와 .75는 column 값의 25% 및 75%가 버킷 값보다 작거나 같음을 나타냅니다.

equi-height 히스토그램의 경우, 버킷은 네 개의 값을 포함합니다:

• 값 1, 2: 버킷에 대한 하한 및 상한(포함) 값입니다. 타입은 column 데이터 타입에 따라 달라집니다.

• 값 3: 해당 값에 대한 누적 빈도(cumulative frequency)를 나타내는 double입니다. 예를 들어 .25와 .75는 column 값의 25% 및 75%가 버킷 상한 값보다 작거나 같음을 나타냅니다.

• 값 4: 버킷 하한 값에서 상한 값까지 범위 내의 distinct 값 개수입니다.

• null-values: column 값 중 SQL NULL 값이 차지하는 비율을 나타내는 0.0과 1.0 사이의 수입니다. 값이 0이면 column에 NULL 값이 없음을 의미합니다.

• last-updated: 히스토그램이 생성된 시점으로, YYYY-MM-DD_ _hh:mm:ss.uuuuuu 형식의 UTC 값입니다.

• sampling-rate: 히스토그램 생성을 위해 샘플링된 데이터 비율을 나타내는 0.0과 1.0 사이의 수입니다. 값이 1이면 모든 데이터가 읽혔음을 의미합니다(샘플링 없음).

• histogram-type: 히스토그램 타입입니다:

  • singleton: 하나의 버킷이 column의 하나의 단일 값을 나타냅니다. 이 히스토그램 타입은 column의 distinct 값 개수가 히스토그램을 생성한 ANALYZE TABLE 문에서 지정한 버킷 개수보다 작거나 같은 경우 생성됩니다.

  • equi-height: 하나의 버킷이 값의 범위를 나타냅니다. 이 히스토그램 타입은 column의 distinct 값 개수가 히스토그램을 생성한 ANALYZE TABLE 문에서 지정한 버킷 개수보다 큰 경우 생성됩니다.

• number-of-buckets-specified: 히스토그램을 생성한 ANALYZE TABLE 문에서 지정한 버킷 개수입니다.

• data-type: 이 히스토그램이 포함하는 데이터의 타입입니다. 이는 영속 스토리지에서 히스토그램을 메모리로 읽고 파싱할 때 필요합니다. 값은 int, uint (unsigned integer), double, decimal, datetime, 또는 string (문자 및 바이너리 문자열 포함) 중 하나입니다.

• collation-id: 히스토그램 데이터에 대한 collation ID입니다. 이는 주로 data-type 값이 string인 경우에 의미가 있습니다. 값은 Information Schema COLLATIONS table의 ID column 값에 대응합니다.

히스토그램 객체에서 특정 값을 추출하려면 JSON 연산을 사용할 수 있습니다. 예를 들면 다음과 같습니다:

1mysql> SELECT
2         TABLE_NAME, COLUMN_NAME,
3         HISTOGRAM->>'$."data-type"' AS 'data-type',
4         JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'
5       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
6+-----------------+-------------+-----------+--------------+
7| TABLE_NAME      | COLUMN_NAME | data-type | bucket-count |
8+-----------------+-------------+-----------+--------------+
9| country         | Population  | int       |          226 |
10| city            | Population  | int       |         1024 |
11| countrylanguage | Language    | string    |          457 |
12+-----------------+-------------+-----------+--------------+

옵티마이저는 통계가 수집된 모든 데이터 타입의 column에 대해, 적용 가능한 경우 히스토그램 통계를 사용합니다. 옵티마이저는 column 값과 상수 값 간의 비교에 대한 선택도(selectivity, 필터링 효과)에 기초하여 row 추정치를 결정하기 위해 히스토그램 통계를 적용합니다.

다음 형태의 predicate는 히스토그램 사용 대상이 됩니다:

1col_name = constant
2col_name <> constant
3col_name != constant
4col_name > constant
5col_name < constant
6col_name >= constant
7col_name <= constant
8col_name IS NULL
9col_name IS NOT NULL
10col_name BETWEEN constant AND constant
11col_name NOT BETWEEN constant AND constant
12col_name IN (constant[, constant] ...)
13col_name NOT IN (constant[, constant] ...)

예를 들어, 다음 문에는 히스토그램 사용 대상이 되는 predicate가 포함됩니다:

1SELECT * FROM orders WHERE amount BETWEEN 100.0 AND 300.0;
2SELECT * FROM tbl WHERE col1 = 15 AND col2 > 100;

상수 값에 대한 비교 요구사항에는 ABS() 및 FLOOR()와 같은 상수 함수도 포함됩니다:

1SELECT * FROM tbl WHERE col1 < ABS(-34);

히스토그램 통계는 주로 인덱스가 없는 column에 유용합니다. 히스토그램 통계가 적용 가능한 column에 인덱스를 추가하면 옵티마이저가 row 추정치를 수행하는 데에도 도움이 될 수 있습니다.

Tradeoff는 다음과 같습니다:

• 인덱스는 table 데이터가 변경될 때마다 업데이트되어야 합니다.

• 히스토그램은 필요할 때만 생성 또는 업데이트되므로, table 데이터가 변경될 때 오버헤드를 추가하지 않습니다. 반면에, 히스토그램 통계는 table 변경이 발생하는 동안 다음번 업데이트 시점까지 점점 더 오래된 상태가 됩니다.

옵티마이저는 히스토그램 통계에서 얻은 row 추정치보다 range 옵티마이저의 row 추정치를 선호합니다. 옵티마이저가 range 옵티마이저를 적용할 수 있다고 판단하면 히스토그램 통계를 사용하지 않습니다.

인덱스가 있는 column의 경우, equality 비교에 대한 row 추정치는 인덱스 다이브를 통해 얻을 수 있습니다(자세한 내용은 Section 10.2.1.2, “Range Optimization” 참조). 이 경우, 인덱스 다이브가 더 나은 추정치를 제공할 수 있으므로 히스토그램 통계는 반드시 유용하지는 않습니다.

일부 경우에는 히스토그램 통계 사용이 쿼리 실행을 개선하지 못할 수 있습니다(예: 통계가 오래된 경우). 이러한지 여부를 확인하려면 ANALYZE TABLE을 사용하여 히스토그램 통계를 다시 생성한 다음, 쿼리를 다시 실행하십시오.

또는 히스토그램 통계를 비활성화하려면 ANALYZE TABLE을 사용하여 이를 drop하십시오. 히스토그램 통계를 비활성화하는 또 다른 방법은 optimizer_switch 시스템 변수의 condition_fanout_filter 플래그를 off로 설정하는 것입니다(단, 이 경우 다른 최적화도 비활성화될 수 있습니다):

1SET optimizer_switch='condition_fanout_filter=off';

히스토그램 통계가 사용되는 경우, 그 결과는 EXPLAIN을 사용해 확인할 수 있습니다. 다음 쿼리를 고려해 보십시오. 여기서 column col1에 사용 가능한 인덱스는 없습니다:

1SELECT * FROM t1 WHERE col1 < 24;

히스토그램 통계에서 t1의 row 중 57%가 col1 < 24 predicate를 만족한다고 나타나면, 인덱스가 없는 경우에도 필터링이 가능하며, EXPLAIN의 filtered column에 57.00이 표시됩니다.