10.2.4 Optimizing Performance Schema Queries

10.2.4 Performance Schema 쿼리 최적화

데이터베이스를 모니터링하는 애플리케이션은 Performance Schema 테이블을 자주 사용할 수 있습니다. 이러한 테이블에 대한 쿼리를 최대한 효율적으로 작성하려면 인덱스를 활용하십시오. 예를 들어, 인덱스가 있는 컬럼의 특정 값과 비교하여 조회되는 행을 제한하는 WHERE 절을 포함합니다.

대부분의 Performance Schema 테이블에는 인덱스가 있습니다. 인덱스가 없는 테이블은 보통 행 수가 적거나 자주 쿼리되지 않을 가능성이 있는 테이블입니다. Performance Schema 인덱스는 옵티마이저가 풀 테이블 스캔 이외의 실행 계획을 사용할 수 있게 합니다. 이러한 인덱스는 또한 해당 테이블을 사용하는 sys 스키마 뷰와 같은 관련 객체의 성능도 향상시킵니다.

특정 Performance Schema 테이블에 인덱스가 있는지, 그리고 어떤 인덱스가 있는지 확인하려면 SHOW INDEX 또는 SHOW CREATE TABLE을 사용합니다:

1mysql> SHOW INDEX FROM performance_schema.accounts\G
2*************************** 1. row ***************************
3        Table: accounts
4   Non_unique: 0
5     Key_name: ACCOUNT
6 Seq_in_index: 1
7  Column_name: USER
8    Collation: NULL
9  Cardinality: NULL
10     Sub_part: NULL
11       Packed: NULL
12         Null: YES
13   Index_type: HASH
14      Comment:
15Index_comment:
16      Visible: YES
17*************************** 2. row ***************************
18        Table: accounts
19   Non_unique: 0
20     Key_name: ACCOUNT
21 Seq_in_index: 2
22  Column_name: HOST
23    Collation: NULL
24  Cardinality: NULL
25     Sub_part: NULL
26       Packed: NULL
27         Null: YES
28   Index_type: HASH
29      Comment:
30Index_comment:
31      Visible: YES
32
33mysql> SHOW CREATE TABLE performance_schema.rwlock_instances\G
34*************************** 1. row ***************************
35       Table: rwlock_instances
36Create Table: CREATE TABLE `rwlock_instances` (
37  `NAME` varchar(128) NOT NULL,
38  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
39  `WRITE_LOCKED_BY_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
40  `READ_LOCKED_BY_COUNT` int(10) unsigned NOT NULL,
41  PRIMARY KEY (`OBJECT_INSTANCE_BEGIN`),
42  KEY `NAME` (`NAME`),
43  KEY `WRITE_LOCKED_BY_THREAD_ID` (`WRITE_LOCKED_BY_THREAD_ID`)
44) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Performance Schema 쿼리에 대한 실행 계획과 인덱스 사용 여부를 확인하려면 EXPLAIN을 사용합니다:

1mysql> EXPLAIN SELECT * FROM performance_schema.accounts
2       WHERE (USER,HOST) = ('root','localhost')\G
3*************************** 1. row ***************************
4           id: 1
5  select_type: SIMPLE
6        table: accounts
7   partitions: NULL
8         type: const
9possible_keys: ACCOUNT
10          key: ACCOUNT
11      key_len: 278
12          ref: const,const
13         rows: 1
14     filtered: 100.00
15        Extra: NULL

EXPLAIN 출력은 옵티마이저가 accounts 테이블의 USER와 HOST 컬럼으로 구성된 ACCOUNT 인덱스를 사용함을 나타냅니다.

Performance Schema 인덱스는 가상입니다. 이는 Performance Schema 스토리지 엔진의 구성 요소이며, 메모리나 디스크 스토리지를 사용하지 않습니다. Performance Schema는 인덱스 정보를 옵티마이저에 보고하여 옵티마이저가 효율적인 실행 계획을 구성할 수 있도록 합니다.

반대로 Performance Schema는 옵티마이저로부터 무엇을 찾아야 하는지(예를 들어, 특정 키 값)와 같은 정보를 받아 실제 인덱스 구조를 구축하지 않고도 효율적인 룩업을 수행할 수 있습니다. 이러한 구현은 두 가지 중요한 이점을 제공합니다:

• 자주 업데이트되는 테이블에서 일반적으로 발생하는 유지 관리 비용을 완전히 피할 수 있습니다.

• 쿼리 실행의 초기에 조회되는 데이터 양을 줄입니다. 인덱스가 있는 컬럼에 대한 조건이 있는 경우, Performance Schema는 쿼리 조건을 만족하는 테이블 행만 효율적으로 반환합니다. 인덱스가 없으면, Performance Schema는 테이블의 모든 행을 반환해야 하고, 옵티마이저는 최종 결과를 만들기 위해 나중에 각 행에 대해 조건을 평가해야 합니다.

Performance Schema 인덱스는 미리 정의되어 있으며, drop, add 또는 alter할 수 없습니다.

Performance Schema 인덱스는 해시 인덱스와 유사합니다. 예를 들어:

• = 또는 <=> 연산자를 사용하는 동등 비교에만 사용됩니다.

• 정렬되지 않습니다. 쿼리 결과가 특정 행 정렬 특성을 가져야 하는 경우, ORDER BY 절을 포함하십시오.

해시 인덱스에 대한 추가 정보는 Section 10.3.9, “Comparison of B-Tree and Hash Indexes”를 참조하십시오.