概述

DRDS SQL 审计与分析，依托日志服务产品，提供强大的日志分析能力。本文档提供常见场景的 SQL 日志分析语句及示例。

开启 DRDS SQL 审计与分析功能之后，您可以在当前页面通过日志服务的查询分析语法进行 SQL 审计与分析。结合日志服务的查询分析语法，在日志分析页面，您可以快速定位问题 SQL，并针对 DRDS 数据库的 SQL 执行状况、性能指标，安全问题进行分析。日志服务的查询分析语法请参考：

• 查询语法
• 实时分析简介

注意事项

相同 Region 下，DRDS 数据库的审计日志都是写入同一个日志服务的 Logstore 里，所以 DRDS SQL 审计与分析的搜索页面会默认为您带上按照 __topic__ 的过滤条件，保证您搜索到的 SQL 日志是 DRDS 数据库的。因此本文提供的所有的查询语句，都需要在已有的过滤条件后追加使用。

例如下图中：

• 序号1部分的语句为默认过滤条件。
• 序号2部分的语句为追加的过滤条件。

快速定位问题 SQL

模糊搜索

例如，查询包含“34”关键字的 SQL 语句，请在查询框中输入：

 and sql: 34

查询结果如下图所示：

字段搜索

依赖预置的索引字段，DRDS 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  > 1507 and sql_type: Insert

例如，查询删除100行以上数据的 SQL：

 and affect_rows  > 100 and sql_type: Delete

SQL 执行状况分析

本节主要介绍 DRDS 数据执行状况相关的查询语句。

SQL 执行失败率

通过以下语句查询 SQL 执行的失败率：

| SELECT sum(case when fail = 1 then 1 else 0 end) * 1.0 / count(1) as fail_ratio

查询结果如下图所示。

如果您的业务对 SQL 错误率敏感，可以在此查询结果的基础上，定制报警信息，单击下图中1所示的 另存为告警。以下报警设置表示每隔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

通过以下语句查询系统慢 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 中则不包含'-'。基于此，我们可以对事务的性能进行相关分析。

说明 事务分析由于涉及前缀匹配操作，查询效率会低于其它类型的查询操作。

例如，通过以下语句查询事务的平均执行耗时：

 | 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 类型分布

 and fail > 0 | select sql_type, count(1) as "错误次数" group by sql_type

高危 SQL 列表

高危 SQL 是 Drop 或者 Truncate 类型的 SQL，您也可以根据需求增加更多条件。

 and sql_type: Drop OR  sql_type: Truncate

大批量删除 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