MySQL中performance_schema索引添加及性能影响缓解方案咨询

Great questions about MySQL's Performance Schema—let's break this down clearly:

Can You Add Indexes to MySQL's Performance Schema? #

Short answer: No, you can't manually add indexes to Performance Schema tables under normal circumstances.

Here's why: Performance Schema tables use the PERFORMANCE_SCHEMA storage engine, a special system engine built for low-overhead data collection. The schema and structure of these tables are fully managed by MySQL itself—users don't have permission to run ALTER TABLE commands to add indexes.

The core design goal of Performance Schema is to minimize runtime overhead, and adding user-defined indexes would actually increase this overhead (since indexes need constant updates as monitoring data is collected), which contradicts its purpose.

Tools/Methods to Mitigate Performance Schema Overhead #

If you're noticing performance impacts from Performance Schema, these strategies can help reduce its footprint:

• Enable only necessary instruments/consumers: Don't leave all monitoring items enabled. Use statements like UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%statement%'; to turn on only the metrics you actually need. Disable unused items by setting ENABLED = 'NO'.
• Tune history size parameters: Limit the amount of historical data stored by adjusting variables like performance_schema_events_statements_history_size or performance_schema_events_wait_history_size. Smaller values mean less memory and disk usage.
• Truncate historical tables regularly: For tables that accumulate data (like events_statements_history), run TRUNCATE TABLE performance_schema.events_statements_history; periodically to clear old data and free up resources.
• Use sampling for high-overhead metrics: For expensive monitoring (e.g., full statement tracking), enable sampling instead of capturing every event. Adjust performance_schema_events_statements_history_long_size and related parameters to control sampling rates.
• Leverage lightweight alternative tools: For specific use cases, tools like pt-query-digest (from Percona Toolkit) or OS-level utilities (e.g., strace, tcpdump) can provide insights without relying heavily on Performance Schema.

Manual Index Addition in MySQL 8.x (Official Research) #

While MySQL's development team has explored adding built-in indexes to Performance Schema tables to improve query performance, there's no supported way to manually add these indexes yourself in current 8.x releases.

Performance Schema tables are tightly integrated with MySQL's internal systems. Manually modifying their structure (even if you could bypass permission checks) would risk metadata corruption, break data collection, or cause unexpected crashes. Any index improvements from the development team will be rolled out as part of official MySQL releases, where they're tested to maintain compatibility and low overhead.

Resources for Working with Performance Schema #

• MySQL Official Documentation: The Performance Schema section of the MySQL 8.x reference manual includes detailed guides on configuration, tuning, and best practices for reducing overhead.
• Percona/MariaDB Blogs: These platforms regularly publish practical articles on optimizing Performance Schema for production environments, including real-world tuning examples.
• MySQL Development Channels: Follow MySQL's bug tracker and developer mailing lists to stay updated on progress around Performance Schema index improvements and other optimizations.

内容的提问来源于stack exchange，提问作者sinneduy