日誌分析 - - 阿里雲

PolarDB-X支援SQL審計與分析功能，依託Log Service產品，提供強大的日誌分析能力。本文將介紹常見情境的SQL日誌分析語句及樣本。

前提條件

已開啟SQL審計與分析功能。

注意事項

相同地區下的所有PolarDB-X資料庫的審計日誌都會寫入同一個Log Service的Logstore中，因此PolarDB-X的SQL審計與分析搜尋方塊內會預設為您帶上__topic__的過濾條件，保證您搜尋到的SQL日誌都屬於同一地區下的PolarDB-X資料庫。本文提供的所有查詢語句，都需要在已有的__topic__過濾條件後追加使用。
您可以單擊原始日誌各個欄位後的詳細內容，自動產生包含對應欄位查詢語句。
例如您可以單擊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模板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