您现在访问的是微软AZURE全球版技术文档网站，若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站，请访问 https://docs.azure.cn.
Azure Database for MySQL 灵活服务器中的慢查询日志(预览版)Slow query logs in Azure Database for MySQL Flexible Server (Preview)
9/21/2020
本文内容
重要
Azure Database for MySQL 灵活服务器当前以公共预览版提供。Azure Database for MySQL - Flexible Server is currently in public preview.
在 Azure Database for MySQL 灵活服务器中，用户可配置和访问慢查询日志。In Azure Database for MySQL Flexible Server, the slow query log is available to users to configure and access. 慢查询日志默认禁用，你可启用它来在故障排除期间帮助识别性能性能瓶颈。Slow query logs are disabled by default and can be enabled to assist with identifying performance bottlenecks during troubleshooting.
有关 MySQL 慢查询日志的详细信息，请参阅 MySQL 引擎文档中的“慢查询日志”部分。For more information about the MySQL slow query log, see the slow query log section in the MySQL engine documentation.
配置慢查询日志记录Configure slow query logging
慢查询日志默认禁用。By default, the slow query log is disabled. 若要启用这些日志，请将 slow_query_log 服务器参数设置为 ON。To enable logs, set the slow_query_log server parameter to ON. 可使用 Azure 门户或 Azure CLI 配置此功能This can be configured using the Azure portal or Azure CLI ..
可调整来控制慢查询日志记录行为的其他参数包括：Other parameters you can adjust to control slow query logging behavior include:
long_query_time：如果查询超过 long_query_time(秒)才完成，则记录该查询。long_query_time: log a query if it takes longer than long_query_time (in seconds) to complete. 默认为 10 秒。The default is 10 seconds.
log_slow_admin_statements：确定是否记录管理语句(例如log_slow_admin_statements: determines if administrative statements (ex. ALTER_TABLE、ANALYZE_TABLE)。ALTER_TABLE, ANALYZE_TABLE) are logged.
log_queries_not_using_indexes：确定是否记录没有使用索引的查询。log_queries_not_using_indexes: determines if queries that do not use indexes are logged.
log_throttle_queries_not_using_indexes：限制可写入慢查询日志的非索引查询的数目。log_throttle_queries_not_using_indexes: limits the number of non-indexed queries that can be written to the slow query log. 当 log_queries_not_using_indexes 设置为 ON 时，此参数生效This parameter takes effect when log_queries_not_using_indexes is set to ON
重要
如果表未编制索引，则将 log_queries_not_using_indexes 和 log_throttle_queries_not_using_indexes 参数设置为 ON 可能会影响 MySQL 性能，因为对这些非索引表运行的所有查询都将写入到慢查询日志。If your tables are not indexed, setting the log_queries_not_using_indexes and log_throttle_queries_not_using_indexes parameters to ON may affect MySQL performance since all queries running against these non-indexed tables will be written to the slow query log.
有关慢查询日志参数的完整说明，请参阅 MySQL 慢查询日志文档。See the MySQL slow query log documentation for full descriptions of the slow query log parameters.
访问慢查询日志Access slow query logs
慢查询日志与 Azure Monitor 诊断设置相集成。Slow query logs are integrated with Azure Monitor diagnostic settings. 在 MySQL 灵活服务器上启用慢查询日志后，可将它们发送到 Azure Monitor 日志、事件中心或 Azure 存储。Once you've enabled slow query logs on your MySQL flexible server, you can emit them to Azure Monitor logs, Event Hubs, or Azure Storage. 若要详细了解诊断设置，请参阅诊断日志文档。To learn more about diagnostic settings, see the diagnostic logs documentation. 若要详细了解如何在 Azure 门户启用诊断设置，请参阅慢查询日志门户文章。To learn more about how to enable diagnostic settings in the Azure portal, see the slow query log portal article.
下表描述了慢查询日志的输出。The following table describes the output of the slow query log. 根据输出方法，包含的字段以及这些字段出现的顺序可能会有所不同。Depending on the output method, the fields included and the order in which they appear may vary.
属性Property
说明Description
TenantId
租户 IDYour tenant ID
SourceSystem
Azure
TimeGenerated [UTC]TimeGenerated [UTC]
记录日志时的时间戳 (UTC)Time stamp when the log was recorded in UTC
Type
日志的类型。Type of the log. 始终是 AzureDiagnosticsAlways AzureDiagnostics
SubscriptionId
服务器所属的订阅的 GUIDGUID for the subscription that the server belongs to
ResourceGroup
服务器所属的资源组的名称Name of the resource group the server belongs to
ResourceProvider
资源提供程序的名称。Name of the resource provider. 始终是 MICROSOFT.DBFORMYSQLAlways MICROSOFT.DBFORMYSQL
ResourceType
Servers
ResourceId
资源 URIResource URI
Resource
服务器的名称Name of the server
Category
MySqlSlowLogs
OperationName
LogEvent
Logical_server_name_s
服务器的名称Name of the server
start_time_t [UTC]start_time_t [UTC]
查询开始时间Time the query began
query_time_s
执行查询所需的总时间(秒)Total time in seconds the query took to execute
lock_time_s
锁定查询的总时间(秒)Total time in seconds the query was locked
user_host_s
用户名Username
rows_sent_s
发送的行数Number of rows sent
rows_examined_s
检查的行数Number of rows examined
insert_id_s
插入 IDInsert ID
sql_text_s
完整查询Full query
server_id_s
服务器 IDThe server's ID
thread_id_s
线程 IDThread ID
\_ResourceId
资源 URIResource URI
备注
对于 sql_text_s，如果日志超过 2048 个字符，则会截断日志。For sql_text_s, log will be truncated if it exceeds 2048 characters.
分析 Azure Monitor 日志中的日志Analyze logs in Azure Monitor Logs
通过诊断日志将慢速查询日志传输到 Azure Monitor 日志后，可以进一步分析慢速查询。Once your slow query logs are piped to Azure Monitor Logs through Diagnostic Logs, you can perform further analysis of your slow queries. 下面是一些可帮助你入门的示例查询。Below are some sample queries to help you get started. 请确保使用你的服务器名称更新下面的内容。Make sure to update the below with your server name.
特定服务器上超过 10 秒的查询Queries longer than 10 seconds on a particular server
AzureDiagnostics
| where LogicalServerName_s == ''
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10
列出特定服务器上最长的 5 个查询List top 5 longest queries on a particular server
AzureDiagnostics
| where LogicalServerName_s == ''
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s
| order by query_time_d desc
| take 5
按最小、最大、平均和标准偏差查询时间汇总特定服务器上的慢速查询Summarize slow queries by minimum, maximum, average, and standard deviation query time on a particular server
AzureDiagnostics
| where LogicalServerName_s == ''
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s
| summarize count(), min(query_time_d), max(query_time_d), avg(query_time_d), stdev(query_time_d), percentile(query_time_d, 95) by LogicalServerName_s
绘制特定服务器上的慢速查询的分布图Graph the slow query distribution on a particular server
AzureDiagnostics
| where LogicalServerName_s == ''
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s
| summarize count() by LogicalServerName_s, bin(TimeGenerated, 5m)
| render timechart
显示启用了诊断日志的所有 MySQL 服务器上超过 10 秒的查询Display queries longer than 10 seconds across all MySQL servers with Diagnostic Logs enabled
AzureDiagnostics
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10
后续步骤Next steps
详细了解审核日志Learn more about audit logs
通过 Azure 门户配置慢查询日志Configure slow query logs from the Azure portal
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
