第八章 优化(三十)—— 优化 Performance Schema(性能概要)查询
8.2 优化SQL语句
8.2.4 优化 Performance Schema(性能概要)查询
监视数据库的应用程序可能会经常使用Performance Schema表。想要对这些表进行最有效的查询,请在编写语句时利用好它们的索引。例如,在WHERE子句中要包含用于获取行的条件是基于与一个索引列的指定值进行的比较。
大多数Performance Schema表都有索引。没有索引的表都是行数通常很少的表或者不太会频繁被访问的表。Performace Schema表的索引使优化器能够访问除了全表扫描之外的执行计划。这些索引也会提升相关对象的性能,例如使用那些表的系统概要视图(sys schema views)。
想要查看给定的Performance Schema表是否有索引以及它们是什么,请使用SHOW INDEX
或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
:
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
表的由USER
和HOST
联合组成的ACCOUNT
复合索引。
Performance Schema的索引是虚拟的:它们是Performance Schema存储引擎的一个结构,且不使用内存或磁盘存储。Performance Schema向优化器报告索引信息,从而使优化器能够构建高效的执行计划。Performance Schema反过来使用优化器中关于查找内容的优化器信息(例如,一个特定的键值),这样它就可以执行有效的查找,而不必构建实际的索引结构。这种实现提供了两个重要的好处:
-
它完全避免了频繁更新的表通常产生的维护成本。
-
它在查询执行的早期阶段就减少了检索到的数据量。对于建立在索引列的条件,Performance Schema能高效地返回仅满足查询条件的表的行。而如果没有索引,Performance Schema将返回表中的所有行,要求优化器稍后针对每一行进行条件比较以生成最终结果。
Performance Schema的索引是预定义的,且不能删除、增加或修改。
Performance Schema的索引与哈希索引相似。例如:
-
它们仅能用于使用
=
或<=>
操作符的相等的比较。 -
它们是无序的。如果查询结果必须具有特定的行排序特征,请包含
ORDER BY
子句。
有关哈希索引的更多信息,请参阅第8.3.9节“B树和哈希索引的比较”。