开发者社区> xaubllxwtvaqiu> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析

简介: PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
+关注继续查看

前提条件

开启SQL审计与分析功能。

注意事项

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

例如您可以单击sql_type后的Delete,查看所有包含Delete字段的SQL语句。4..png

快速定位SQL

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

  • 模糊搜索例如,您可以使用如下命令查询包含关键字为200003的SQL语句:
and sql: 200003
  • 字段搜索依赖预置的索引字段,PolarDB-X SQL审计还支持根据字段搜索。例如您可以使用如下命令查询DROP类型的SQL:
and sql_type:Drop
  • 多条件搜索您可以通过andor等关键字实现多条件的搜索。例如您可以使用如下命令查询针对id=200003行进行的所有DELETE语句:
and sql: 200003 and sql_type: Delete
  • 数值比较搜索索引字段中的affect_rowsresponse_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
  • 查询结果如下图所示:

30.png

快速定位SQL

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

  • 模糊搜索例如,您可以使用如下命令查询包含关键字为200003的SQL语句:
and sql: 200003
  • 字段搜索依赖预置的索引字段,PolarDB-X SQL审计还支持根据字段搜索。例如您可以使用如下命令查询DROP类型的SQL:
and sql_type:Drop
  • 多条件搜索您可以通过andor等关键字实现多条件的搜索。例如您可以使用如下命令查询针对id=200003行进行的所有DELETE语句:
and sql: 200003 and sql_type: Delete
  • 数值比较搜索索引字段中的affect_rowsresponse_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
  • 查询结果如下图所示:

1.png

高代价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等信息。2.png


  • 说明
    上述查询是按照总体耗时比例%排序,您也可以根据平均执行时间执行次数进行排序帮助排查问题。
  • 事务平均执行时长对于相同事务内的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 2.0中的高危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,

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
11 0
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
12 0
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
8 0
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
7 0
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
10 0
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
14 0
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
7 0
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
19 0
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
16 0
【笔记】用户指南—诊断与优化—SQL审计与分析—日志分析
PolarDB-X支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文将介绍常见场景的SQL日志分析语句及示例。
18 0
7352
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载