我可以向mysql中的performance_schema添加索引吗

The performance_schema seems to have some varying amounts of performance impact, and I'm curious what tools we have to mitigate the impact?

performance_schema似乎具有不同程度的性能影响，我很好奇我们有什么工具可以减轻这种影响?

I see in mysql 8.x that the devs are looking into setting up indexing, is that potentially something we can manually add into the db ourselves? If so, is there a resource we can use around adding indexes the PFS is likely to use?

mysql 8。开发人员正在考虑建立索引，我们可以手动添加到数据库中吗?如果是这样，我们是否可以使用一个资源来添加PFS可能使用的索引?

1 个解决方案

#1

No, this is not supported. You can't ALTER TABLE on the performance schema tables at all.

不，这是不支持的。您根本不能修改性能模式表上的表。

master [localhost] {root} (performance_schema) > select version();
+------------+
| version()  |
+------------+
| 5.7.20-log |
+------------+

master [localhost] {root} (performance_schema) > alter table events_statements_summary_by_thread_by_event_name add primary key (thread_id, event_name);
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'performance_schema'

master [localhost] {root} (performance_schema) > show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

Even if you could do it, adding indexes to the performance_schema prior to MySQL 8.0 wouldn't improve performance, it would make the overhead worse.

即使您可以这样做，在MySQL 8.0之前向performance_schema添加索引也不会提高性能，反而会使开销更大。

The article you linked to said:

你链接的文章说:

Some readers might also have noted that the performance_schema tables are highly volatile, with data written to it continuously, while read only a few times, typically once in a while by a monitoring application. A table used in a workload which is write intensive with only a few reads is a red flag when it comes to add an index, as the cost of maintaining the index is order of magnitudes higher that the benefits.

有些读者可能还注意到performance_schema表具有高度的不稳定性，数据不断地写到表中，同时只读取几次，通常是由监视应用程序偶尔读取一次。在只进行少量读操作的工作负载中使用的表在添加索引时是危险的，因为维护索引的成本要比好处高很多。

The feature developed for MySQL 8.0 to simulate indexes just pretends to store persistent indexes. As the article says, they only report indexes to guide the optimizer on the best access pattern. There aren't really any indexes.

为MySQL 8.0开发的用于模拟索引的特性只是假装存储持久索引。正如文章所述，他们只报告索引，以指导优化器在最佳访问模式。没有任何索引。

#1