PolarDB-X 1.0支持SQL审计与分析功能,依托日志服务产品,提供强大的日志分析能力。本文档将介绍常见场景的SQL日志分析语句及示例。
背景信息
开启PolarDB-X 1.0 SQL审计与分析功能之后,您可以在当前页面通过日志服务的查询分析语法进行SQL审计与分析。结合日志服务的查询分析语法,在日志分析页面,您可以快速定位问题SQL,并针对PolarDB-X 1.0数据库的SQL执行状况、性能指标、安全问题进行分析。关于日志服务的查询分析语法,详情请参见查询语法。
注意事项
相同Region下,PolarDB-X 1.0数据库的审计日志都是写入同一个日志服务的Logstore里,所以PolarDB-X 1.0 SQL审计与分析的搜索页面会默认为您带上__topic__
过滤条件,保证您搜索到的SQL日志是PolarDB-X 1.0数据库的。因此本文提供的所有的查询语句,都需要在已有的过滤条件后追加使用。
例如下图中序号1部分的语句为默认过滤条件,序号2部分的语句为追加的过滤条件。
快速定位问题SQL
您可以使用以下命令快速定位问题SQL。
- 模糊搜索
例如,您可以使用如下命令查询包含关键字为
34
的SQL语句:and sql: 34
查询结果如下图所示:
- 字段搜索
依赖预置的索引字段,PolarDB-X 1.0 SQL审计还支持根据字段搜索。例如您可以使用如下命令查询
Drop
类型的SQL:and sql_type:Drop
查询结果如下图所示:
日志服务支持鼠标点击自动生成查询语句,如下图所示。
- 多条件搜索
您可以通过
and
或or
等关键字实现多条件的搜索。例如您可以使用如下命令查询对id=34
行的删除操作:and sql:34 and sql_type: Delete
- 数值比较搜索
索引字段中的
affect_rows
和response_time
是数值类型,支持比较操作符。例如您可以使用如下命令查询response_time
大于1s的Insert SQL:and response_time > 1 and sql_type: Insert
或者使用如下命令查询删除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错误率敏感,可以在此查询结果的基础上,定制报警信息。以下报警设置表示每隔15分钟,检查15分钟内SQL执行的错误率大于0.01的日志数量。您也可以根据业务需要定制告警。
- 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
说明 上述查询给出了按照给定时间段的SQL执行时间分布,您也可以调整时间段的范围,获取更加精细的结果。 - 慢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(为了显示效果,该列按照200的长度截断)。
说明 上述查询是按照总体耗时比例排序,当然您也可以根据平均执行时间,执行次数进行排序排查问题。 - 事务平均执行时长
对于相同事务内的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列表
高危SQL是指DROP或RUNCATE类型的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