10.2.3 Optimizing INFORMATION_SCHEMA Queries

10.2.3 INFORMATION_SCHEMA Query 최적화

데이터베이스를 모니터링하는 애플리케이션은 INFORMATION_SCHEMA 테이블을 자주 사용할 수 있습니다. 이러한 테이블에 대해 가능한 한 효율적으로 쿼리를 작성하려면, 다음과 같은 일반적인 가이드라인을 따르십시오:

• 데이터 딕셔너리 테이블에 대한 뷰인 INFORMATION_SCHEMA 테이블에 대해서만 쿼리하도록 하십시오.

• 정적인 메타데이터에 대해서만 쿼리하도록 하십시오. 정적인 메타데이터와 함께 동적인 메타데이터의 컬럼을 선택하거나 검색 조건으로 사용하는 것은 동적인 메타데이터를 처리하는 오버헤드를 추가합니다.

참고

INFORMATION_SCHEMA 쿼리에서 데이터베이스와 테이블 이름에 대한 비교 동작은 기대와 다를 수 있습니다. 자세한 내용은
Section 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”를 참조하십시오.

다음 INFORMATION_SCHEMA 테이블은 데이터 딕셔너리 테이블에 대한 뷰로 구현되므로, 이들에 대한 쿼리는 데이터 딕셔너리로부터 정보를 가져옵니다:

1CHARACTER_SETS
2CHECK_CONSTRAINTS
3COLLATIONS
4COLLATION_CHARACTER_SET_APPLICABILITY
5COLUMNS
6EVENTS
7FILES
8INNODB_COLUMNS
9INNODB_DATAFILES
10INNODB_FIELDS
11INNODB_FOREIGN
12INNODB_FOREIGN_COLS
13INNODB_INDEXES
14INNODB_TABLES
15INNODB_TABLESPACES
16INNODB_TABLESPACES_BRIEF
17INNODB_TABLESTATS
18KEY_COLUMN_USAGE
19PARAMETERS
20PARTITIONS
21REFERENTIAL_CONSTRAINTS
22RESOURCE_GROUPS
23ROUTINES
24SCHEMATA
25STATISTICS
26TABLES
27TABLE_CONSTRAINTS
28TRIGGERS
29VIEWS
30VIEW_ROUTINE_USAGE
31VIEW_TABLE_USAGE

일부 유형의 값은, 뷰가 아닌 INFORMATION_SCHEMA 테이블에 대해서도 데이터 딕셔너리 조회를 통해 가져옵니다. 여기에는 데이터베이스와 테이블 이름, 테이블 타입, 스토리지 엔진과 같은 값이 포함됩니다.

일부 INFORMATION_SCHEMA 테이블에는 테이블 통계를 제공하는 컬럼이 포함되어 있습니다:

1STATISTICS.CARDINALITY
2TABLES.AUTO_INCREMENT
3TABLES.AVG_ROW_LENGTH
4TABLES.CHECKSUM
5TABLES.CHECK_TIME
6TABLES.CREATE_TIME
7TABLES.DATA_FREE
8TABLES.DATA_LENGTH
9TABLES.INDEX_LENGTH
10TABLES.MAX_DATA_LENGTH
11TABLES.TABLE_ROWS
12TABLES.UPDATE_TIME

이러한 컬럼은 동적인 테이블 메타데이터를 나타냅니다. 즉, 테이블의 내용이 변경됨에 따라 변경되는 정보입니다.

기본적으로, MySQL은 이러한 컬럼이 쿼리될 때 mysql.index_stats 및 mysql.innodb_table_stats 딕셔너리 테이블에서 캐시된 값을 가져옵니다. 이는 스토리지 엔진으로부터 통계를 직접 가져오는 것보다 더 효율적입니다.

캐시된 통계가 존재하지 않거나 만료된 경우, MySQL은 스토리지 엔진으로부터 최신 통계를 가져와 mysql.index_stats 및 mysql.innodb_table_stats 딕셔너리 테이블에 캐시합니다. 이후의 쿼리는 캐시된 통계가 만료될 때까지 캐시된 통계를 가져옵니다. 서버 재시작이나 mysql.index_stats 및 mysql.innodb_table_stats 테이블을 처음 여는 것은 캐시된 통계를 자동으로 갱신하지 않습니다.

information_schema_stats_expiry 세션 변수는 캐시된 통계가 만료되기 전까지의 시간을 정의합니다. 기본값은 86400초(24시간)이며, 이 기간은 최대 1년까지 연장할 수 있습니다.

특정 테이블에 대해 언제든지 캐시된 값을 갱신하려면 ANALYZE TABLE을 사용하십시오.

다음과 같은 상황에서는 통계 컬럼을 쿼리하더라도 mysql.index_stats 및 mysql.innodb_table_stats 딕셔너리 테이블에 통계를 저장하거나 갱신하지 않습니다:

• 캐시된 통계가 만료되지 않았을 때.

• information_schema_stats_expiry
  값이 0으로 설정되어 있을 때.

• 서버가
  read_only,
  super_read_only,
  transaction_read_only, 또는
  innodb_read_only 모드일 때.

• 쿼리가 Performance Schema 데이터를 함께 가져올 때.

information_schema_stats_expiry 는 세션 변수이며, 각 클라이언트 세션은 자체 만료 값을 정의할 수 있습니다. 스토리지 엔진으로부터 가져와 한 세션이 캐시한 통계는 다른 세션에서도 사용할 수 있습니다.

참고

innodb_read_only 시스템 변수가 활성화되어 있으면, ANALYZE TABLE은 데이터 딕셔너리의 통계 테이블을 갱신할 수 없기 때문에 실패할 수 있습니다. 이 통계 테이블은 InnoDB를 사용합니다.

Key 분포를 갱신하는 ANALYZE TABLE 작업의 경우, 작업이 테이블 자체를 갱신하더라도(예: 해당 테이블이 MyISAM 테이블인 경우) 실패가 발생할 수 있습니다. 갱신된 분포 통계를 얻으려면 information_schema_stats_expiry=0으로 설정하십시오.

데이터 딕셔너리 테이블에 대한 뷰로 구현된 INFORMATION_SCHEMA 테이블의 경우, 기본 데이터 딕셔너리 테이블의 인덱스 덕분에 옵티마이저가 효율적인 쿼리 실행 계획을 구성할 수 있습니다. 옵티마이저가 선택한 내용을 확인하려면 EXPLAIN을 사용하십시오.

또한, 서버가 INFORMATION_SCHEMA 쿼리를 실행하기 위해 사용하는 쿼리를 확인하려면 EXPLAIN 직후에 SHOW WARNINGS를 사용하십시오.

다음 스테이트먼트는 utf8mb4 캐릭터 셋에 대한 콜레이션을 식별합니다:

1mysql> SELECT COLLATION_NAME
2       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
3       WHERE CHARACTER_SET_NAME = 'utf8mb4';
4+----------------------------+
5| COLLATION_NAME             |
6+----------------------------+
7| utf8mb4_general_ci         |
8| utf8mb4_bin                |
9| utf8mb4_unicode_ci         |
10| utf8mb4_icelandic_ci       |
11| utf8mb4_latvian_ci         |
12| utf8mb4_romanian_ci        |
13| utf8mb4_slovenian_ci       |
14...

서버는 해당 스테이트먼트를 어떻게 처리할까요? 이를 알아보려면 EXPLAIN을 사용하십시오:

1mysql> EXPLAIN SELECT COLLATION_NAME
2       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
3       WHERE CHARACTER_SET_NAME = 'utf8mb4'\G
4*************************** 1. row ***************************
5           id: 1
6  select_type: SIMPLE
7        table: cs
8   partitions: NULL
9         type: const
10possible_keys: PRIMARY,name
11          key: name
12      key_len: 194
13          ref: const
14         rows: 1
15     filtered: 100.00
16        Extra: Using index
17*************************** 2. row ***************************
18           id: 1
19  select_type: SIMPLE
20        table: col
21   partitions: NULL
22         type: ref
23possible_keys: character_set_id
24          key: character_set_id
25      key_len: 8
26          ref: const
27         rows: 68
28     filtered: 100.00
29        Extra: NULL
302 rows in set, 1 warning (0.01 sec)

해당 스테이트먼트를 충족하기 위해 사용된 쿼리를 보려면 SHOW WARNINGS를 사용하십시오:

1mysql> SHOW WARNINGS\G
2*************************** 1. row ***************************
3  Level: Note
4   Code: 1003
5Message: /* select#1 */ select `mysql`.`col`.`name` AS `COLLATION_NAME`
6         from `mysql`.`character_sets` `cs`
7         join `mysql`.`collations` `col`
8         where ((`mysql`.`col`.`character_set_id` = '45')
9         and ('utf8mb4' = 'utf8mb4'))

SHOW WARNINGS가 나타내듯이, 서버는 COLLATION_CHARACTER_SET_APPLICABILITY 에 대한 쿼리를 mysql 시스템 데이터베이스의 character_sets 및 collations 데이터 딕셔너리 테이블에 대한 쿼리로 처리합니다.