常见场景的SQL日志分析语句及示例_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

PolarDB-X支持SQL审计与分析功能，依托日志服务产品，提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。

前提条件

已开启SQL审计与分析功能。

注意事项

相同地域下的所有PolarDB-X数据库的审计日志都会写入同一个日志服务的Logstore中，因此PolarDB-X的SQL审计与分析搜索框内会默认为您带上__topic__的过滤条件，保证您搜索到的SQL日志都属于同一地域下的PolarDB-X数据库。本文提供的所有查询语句，都需要在已有的__topic__过滤条件后追加使用。
例如图中1部分的语句为默认过滤条件，序号2部分的语句为追加的过滤条件。
您可以单击原始日志各个字段后的详细内容，自动生成包含对应字段查询语句。
例如您可以单击sql_type后的Delete，查看所有包含Delete字段的SQL语句。

快速定位SQL

您可以使用以下命令快速定位问题SQL。

模糊搜索
例如，您可以使用如下命令查询包含关键字为200003的SQL语句：
```
and sql: 200003
```
字段搜索
依赖预置的索引字段，PolarDB-X SQL审计还支持根据字段搜索。例如您可以使用如下命令查询DROP类型的SQL：
```
and sql_type:Drop
```
多条件搜索
您可以通过and或or等关键字实现多条件的搜索。例如您可以使用如下命令查询针对id=200003行进行的所有DELETE语句：
```
and sql: 200003 and sql_type: Delete
```
数值比较搜索
索引字段中的affect_rows和response_time是数值类型，支持比较操作符。例如您可以使用如下命令查询response_time大于5秒的DROP语句：
```
and response_time > 5 and sql_type: Drop
```
或者使用如下命令查询删除100行以上数据的SQL语句：
```
and affect_rows  > 100 and sql_type: Delete
```

SQL执行状况分析

您可以使用以下命令查看SQL执行状况。

SQL执行失败率
您可以使用如下命令查询SQL执行的失败率：
```
| SELECT sum(case when fail = 1 then 1 else 0 end) * 1.0 / count(1) as fail_ratio
```
查询结果如下图所示：
说明
若您的业务对SQL错误率敏感，您可以在此查询结果的基础上，单击页面右上角的另存为告警并根据业务需要自定义告警信息。
SQL累计查询行数
您可以使用如下命令查询SELECT语句累计查询的行数：
```
and sql_type: Select | SELECT sum(affect_rows)
```
SQL类型分布
您可以使用如下命令查询SQL类型的分布情况：
```
| SELECT  sql_type, count(sql) as times GROUP BY sql_type
```
SQL独立用户IP分布
您可以使用如下命令查询SQL独立用户的IP地址分布：
```
| SELECT  user, client_ip, count(sql) as times GROUP BY user, client_ip
```

SQL性能分析

您可以使用以下命令查看SQL性能分析详情。

SELECT平均耗时
您可以使用如下命令查询SELECT语句的平均耗时：
```
and sql_type: Select | SELECT avg(response_time)
```

SQL执行耗时分布

您可以使用如下命令查询SQL执行耗时分布：

and response_time > 0 | select   case  when response_time <= 10 then '<=10毫秒'  when response_time > 10 and response_time <= 100 then '10~100毫秒'  when response_time > 100 and response_time <= 1000 then '100毫秒~1秒'  when response_time > 1000 and response_time <= 10000  then '1秒~10秒'  when response_time > 10000 and response_time <= 60000  then '10秒~1分钟'  else '>1分钟' end as latency_type,  count(1) as cnt group by latency_type order by latency_type DESC

说明

上述查询将response_time按照小于等于10毫秒、大于10毫秒小于等于100毫秒、大于100毫秒小于等于1秒和大于1秒小于等于10秒这4个时间段对SQL执行耗时进行分布，您也可以自定义response_time的时间段，获取更加精细的结果。

慢SQL Top 50
您可以使用如下命令查询系统排名前50的慢SQL：
```
| SELECT date_format(from_unixtime(__time__), '%m/%d %H:%i:%s') as time, user, client_ip, client_port, sql_type, affect_rows, response_time, sql ORDER BY  response_time desc LIMIT 50
```
查询结果如下图所示，结果中包含SQL执行时间、执行的用户名、IP地址、端口号、SQL类型、影响行数、执行时间以及具体的SQL语句等信息。

高代价SQL模板Top 10

在大多数应用中，SQL通常基于若干模板动态生成的，只是参数不同。您可以使用如下命令通过模板ID找到应用中高代价的SQL模板：

| SELECT sql_code as "SQL模板ID", round(total_time * 1.0 /sum(total_time) over() * 100, 2) as "总体耗时比例(%)" ,execute_times as "执行次数", round(avg_time) as "平均执行时间",round(avg_rows) as "平均影响行数", CASE WHEN length(sql) > 200 THEN  concat(substr(sql, 1, 200), '......') ELSE trim(lpad(sql, 200, ' ')) end as "样例SQL" FROM  (SELECT sql_code, count(1) as execute_times, sum(response_time) as total_time, avg(response_time) as avg_time, avg(affect_rows) as avg_rows, arbitrary(sql) as sql FROM log GROUP BY sql_code) ORDER BY "总体耗时比例(%)" desc limit 10

统计结果中包括SQL模板ID，该模板SQL占总体SQL的耗时比例、执行次数、平均执行时间、平均影响行数以及样例SQL等信息。

说明

上述查询是按照总体耗时比例%排序，您也可以根据平均执行时间或执行次数进行排序帮助排查问题。

事务平均执行时长
对于相同事务内的SQL，预置的trace_id字段前缀相同，后缀为'-' + 序号；非事务的SQL的trace_id中则不包含'-'。因此，您可以使用如下命令对事务SQL的性能进行相关分析。
说明
由于事务分析涉及前缀匹配操作，查询效率会低于其它类型的查询操作。
- 查询事务的平均执行耗时
  您可以使用如下语句查询事务的平均执行耗时：
```
 | SELECT  sum(response_time) / COUNT(DISTINCT substr(trace_id, 1, strpos(trace_id, '-') - 1)) where strpos(trace_id, '-') > 0
```
- 慢事务Top 10
  您可以按照事务的执行时间排序查询慢事务的列表：
```
| SELECT substr(trace_id, 1, strpos(trace_id, '-') - 1) as "事务ID" , sum(response_time) as "事务耗时" where strpos(trace_id, '-') > 0 GROUP BY substr(trace_id, 1, strpos(trace_id, '-') - 1) ORDER BY "事务耗时" DESC LIMIT 10
```
  在此基础上，您可以使用如下命令，根据查到的慢事务ID搜索该事务下的所有SQL用于分析执行慢的具体原因：
```
 and trace_id: db3226a20402000*
```
- 大批量操作事务Top 10
  您可以使用如下命令按照事务内SQL影响的行数排序，查询大批量操作的事务列表：
```
| SELECT substr(trace_id, 1, strpos(trace_id, '-') - 1) as  "事务ID" , sum(affect_rows) as "影响行数" where strpos(trace_id, '-') > 0 GROUP BY substr(trace_id, 1, strpos(trace_id, '-') - 1) ORDER BY "影响行数" DESC LIMIT 10
```

SQL安全性分析

您可以使用以下命令查看SQL安全性分析情况。

错误SQL类型分布
您可以使用如下命令查看错误SQL类型分布：
```
and fail > 0 | select sql_type, count(1) as "错误次数" group by sql_type
```
高危SQL列表
PolarDB-X中的高危SQL是指DROP或TRUNCATE类型的SQL（您也可以根据业务需求增加更多条件自定义高危SQL)。
您可以使用如下命令查询包含DROP或TRUNCATE类型的SQL列表：
```
and sql_type: Drop OR sql_type: Truncate
```

大批量删除SQL列表

您可以使用如下命令大批量删除SQL列表：

and affect_rows > 100 and sql_type: Delete | SELECT date_format(from_unixtime(__time__), '%m/%d %H:%i:%s') as time, user, client_ip, client_port, affect_rows, sql ORDER BY  affect_rows desc LIMIT 50