Azure Database for MySQL 中的 Query Performance InsightQuery Performance Insight in Azure Database for MySQL

适用于: Azure Database for MySQL 5.7、8.0Applies to: Azure Database for MySQL 5.7, 8.0

Query Performance Insight 可帮助你快速确定运行时间最长的查询、它们如何随时间的推移而变化,以及哪些等待因素会影响它们。Query Performance Insight helps you to quickly identify what your longest running queries are, how they change over time, and what waits are affecting them.

常见方案Common scenarios

长时间运行的查询Long running queries

  • 标识过去 X 小时内运行时间最长的查询Identifying longest running queries in the past X hours
  • 标识正在等待资源的前 N 个查询Identifying top N queries that are waiting on resources

等待统计信息Wait statistics

  • 了解查询的等待性质Understanding wait nature for a query
  • 了解资源等待趋势以及发生资源争用的位置Understanding trends for resource waits and where resource contention exists

权限Permissions

查看 Query Performance Insight 中查询文本所需的“所有者”或“参与者”权限。Owner or Contributor permissions required to view the text of the queries in Query Performance Insight. 读者可以查看图表和表格,但不能查看查询文本。Reader can view charts and tables but not query text.

先决条件Prerequisites

若要运行 Query Performance Insight,数据必须存在于查询存储中。For Query Performance Insight to function, data must exist in the Query Store.

查看性能见解Viewing performance insights

Azure 门户中的 Query Performance Insight 视图将显示来自查询存储的关键信息的可视化效果。The Query Performance Insight view in the Azure portal will surface visualizations on key information from Query Store.

在 Azure Database for MySQL 服务器的门户页中,选择菜单栏的“智能性能”部分下的“Query Performance Insight”。In the portal page of your Azure Database for MySQL server, select Query Performance Insight under the Intelligent Performance section of the menu bar.

长时间运行的查询Long running queries

“长时间运行的查询”选项卡按每次执行平均持续时间显示前 5 个查询,每隔 15 分钟聚合一次。The Long running queries tab shows the top 5 queries by average duration per execution, aggregated in 15-minute intervals. 可以通过从“查询数量”下拉列表中进行选择来查看更多查询。You can view more queries by selecting from the Number of Queries drop down. 执行此操作时,特定查询 ID 的图表颜色可能会更改。The chart colors may change for a specific Query ID when you do this.

可以在图表中单击并拖动以缩小到特定的时间窗口。You can click and drag in the chart to narrow down to a specific time window. 或者,使用放大和缩小图标分别查看更短或更长的时间段。Alternatively, use the zoom in and out icons to view a smaller or larger time period respectively.

Query Performance Insight 长时间运行的查询

等待统计信息Wait statistics

备注

等待统计信息用于排查查询性能问题。Wait statistics are meant for troubleshooting query performance issues. 建议仅出于故障排除目的启用此功能。It is recommended to be turned on only for troubleshooting purposes.
如果在 Azure 门户中查看等待统计信息时收到错误消息“遇到了针对 'Microsoft.DBforMySQL' 的问题; 无法完成请求。如果此问题仍然存在或出乎意料,请联系支持人员并提供此信息。”If you receive the error message in the Azure portal "The issue encountered for 'Microsoft.DBforMySQL'; cannot fulfill the request. If this issue continues or is unexpected, please contact support with this information." ,请使用更短的时间段。while viewing wait statistics, use a smaller time period.

等待统计信息提供执行特定查询期间发生的等待事件的视图。Wait statistics provides a view of the wait events that occur during the execution of a specific query. MySQL 引擎文档中了解有关等待事件类型的详细信息。Learn more about the wait event types in the MySQL engine documentation.

选择“等待统计信息”选项卡以查看服务器中等待次数的相应可视化效果。Select the Wait Statistics tab to view the corresponding visualizations on waits in the server.

等待统计信息视图中显示的查询按指定时间间隔内显示最大等待的查询进行分组。Queries displayed in the wait statistics view are grouped by the queries that exhibit the largest waits during the specified time interval.

Query Performance Insight 等待统计信息

后续步骤Next steps