Movatterモバイル変換

Applications that monitor databases may make frequent use of Performance Schema tables. To write queries for these tables most efficiently, take advantage of their indexes. For example, include aWHERE clause that restricts retrieved rows based on comparison to specific values in an indexed column.

Most Performance Schema tables have indexes. Tables that do not are those that normally contain few rows or are unlikely to be queried frequently. Performance Schema indexes give the optimizer access to execution plans other than full table scans. These indexes also improve performance for related objects, such assys schema views that use those tables.

To see whether a given Performance Schema table has indexes and what they are, useSHOW INDEX orSHOW CREATE TABLE:

mysql> SHOW INDEX FROM performance_schema.accounts\G*************************** 1. row ***************************        Table: accounts   Non_unique: 0     Key_name: ACCOUNT Seq_in_index: 1  Column_name: USER    Collation: NULL  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: HASH      Comment:Index_comment:      Visible: YES*************************** 2. row ***************************        Table: accounts   Non_unique: 0     Key_name: ACCOUNT Seq_in_index: 2  Column_name: HOST    Collation: NULL  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: HASH      Comment:Index_comment:      Visible: YESmysql> SHOW CREATE TABLE performance_schema.rwlock_instances\G*************************** 1. row ***************************       Table: rwlock_instancesCreate Table: CREATE TABLE `rwlock_instances` (  `NAME` varchar(128) NOT NULL,  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,  `WRITE_LOCKED_BY_THREAD_ID` bigint(20) unsigned DEFAULT NULL,  `READ_LOCKED_BY_COUNT` int(10) unsigned NOT NULL,  PRIMARY KEY (`OBJECT_INSTANCE_BEGIN`),  KEY `NAME` (`NAME`),  KEY `WRITE_LOCKED_BY_THREAD_ID` (`WRITE_LOCKED_BY_THREAD_ID`)) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

To see the execution plan for a Performance Schema query and whether it uses any indexes, useEXPLAIN:

mysql> EXPLAIN SELECT * FROM performance_schema.accounts       WHERE (USER,HOST) = ('root','localhost')\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: accounts   partitions: NULL         type: constpossible_keys: ACCOUNT          key: ACCOUNT      key_len: 278          ref: const,const         rows: 1     filtered: 100.00        Extra: NULL

TheEXPLAIN output indicates that the optimizer uses theaccounts tableACCOUNT index that comprises theUSER andHOST columns.

Performance Schema indexes are virtual: They are a construct of the Performance Schema storage engine and use no memory or disk storage. The Performance Schema reports index information to the optimizer so that it can construct efficient execution plans. The Performance Schema in turn uses optimizer information about what to look for (for example, a particular key value), so that it can perform efficient lookups without building actual index structures. This implementation provides two important benefits:

Performance Schema indexes are predefined and cannot be dropped, added, or altered.

Performance Schema indexes are similar to hash indexes. For example:

For additional information about hash indexes, seeSection 10.3.9, “Comparison of B-Tree and Hash Indexes”.