MySQL :: MySQL 8.4 参考手册 :: 10.2.4 优化性能模式查询

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.2 优化 SQL 语句 / 10.2.4 优化性能模式查询

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

10.2.4 优化 Performance Schema 查询10.2.4 Optimizing Performance Schema Queries

监控数据库的应用程序可能会频繁使用 Performance Schema 表。为了最有效地编写这些表的查询，请利用它们的索引。例如，包含一个 WHERE 子句，根据索引列中的特定值限制检索的行。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 a WHERE clause that restricts retrieved rows based on comparison to specific values in an indexed column.

大多数 Performance Schema 表都有索引。不带索引的表通常是那些行数很少或不太可能被频繁查询的表。Performance Schema 索引使优化器能够访问除全表扫描之外的执行计划。这些索引还提高了相关对象的性能，例如使用这些表的 sys schema 视图。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 as sys schema views that use those tables.

要查看给定的 Performance Schema 表是否有索引以及它们是什么，请使用 SHOW INDEX 或 SHOW CREATE TABLE：To see whether a given Performance Schema table has indexes and what they are, use SHOW INDEX or SHOW 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: YES

mysql> SHOW CREATE TABLE performance_schema.rwlock_instances\G
*************************** 1. row ***************************
       Table: rwlock_instances
Create 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

要查看 Performance Schema 查询的执行计划以及它是否使用任何索引，请使用 EXPLAIN：To see the execution plan for a Performance Schema query and whether it uses any indexes, use EXPLAIN:

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: const
possible_keys: ACCOUNT
          key: ACCOUNT
      key_len: 278
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

EXPLAIN 输出显示优化器使用了 accounts 表的 ACCOUNT 索引，该索引包含 USER 和 HOST 列。The EXPLAIN output indicates that the optimizer uses the accounts table ACCOUNT index that comprises the USER and HOST columns.

性能模式索引是虚拟的：它们是性能模式存储引擎的构造，不使用内存或磁盘存储。 Performance Schema 向优化器报告索引信息，以便优化器构建高效的执行计划。性能模式反过来使用有关要查找的内容（例如，特定键值）的优化器信息，以便它可以执行有效的查找，而无需构建实际的索引结构。此实现提供了两个重要的好处：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:

它完全避免了频繁更新的表通常产生的维护成本。It entirely avoids the maintenance cost normally incurred for tables that undergo frequent updates.
它在查询执行的早期阶段就减少了检索的数据量。对于已建立索引的列上的条件，Performance Schema 会高效地只返回满足查询条件的表行。如果没有索引，Performance Schema 将返回表中的所有行，需要优化器随后对每一行评估条件以生成最终结果。It reduces at an early stage of query execution the amount of data retrieved. For conditions on the indexed columns, the Performance Schema efficiently returns only table rows that satisfy the query conditions. Without an index, the Performance Schema would return all rows in the table, requiring that the optimizer later evaluate the conditions against each row to produce the final result.

Performance Schema 的索引是预定义的，不能删除、添加或修改。Performance Schema indexes are predefined and cannot be dropped, added, or altered.

Performance Schema 的索引类似于哈希索引。例如：Performance Schema indexes are similar to hash indexes. For example:

它们仅用于使用 = 或 <=> 操作符的等值比较。They are used only for equality comparisons that use the = or <=> operators.
它们是无序的。如果查询结果必须具有特定的行排序特性，请包含 ORDER BY 子句。They are unordered. If a query result must have specific row ordering characteristics, include an ORDER BY clause.

有关哈希索引的更多信息，请参见第 10.3.9 节，“B 树索引与哈希索引的比较”。For additional information about hash indexes, see Section 10.3.9, “Comparison of B-Tree and Hash Indexes”.

首页上一级下一节