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

Question

performance_schema 似乎有一些 varying数量performance impact ，我很好奇我们有什么工具可以减轻影响？

我在 mysql 8.x 中看到开发人员正在调查 setting up indexing ，这可能是我们可以自己手动添加到数据库中的东西吗？如果是这样，是否有我们可以使用的资源来添加 PFS 可能使用的索引？

Answer 1

不，这不受支持。您根本不能对性能模式表ALTER TABLE。

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        |
+---------------------------------------------------------------------+

即使你能做到，在 MySQL 8.0 之前向 performance_schema 添加索引也不会提高性能，它会使开销变得更糟。

您链接到的文章说:

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.

为 MySQL 8.0 开发的模拟索引的功能只是假装存储持久索引。正如文章所说，他们只报告索引以指导优化器选择最佳访问模式。实际上没有任何索引。