通过查询日志，您可以查看查询的执行情况、识别慢查询，有助于您提升系统性能和解决潜在问题。本文介绍如何通过SQL查看ByteHouse企业版集群的查询日志。

确保查询日志已开启。

-- 选择全部节点模式执行
SET log_queries = 1;

• 查询日志可能会包含敏感信息，请妥善管理查询日志信息。
• 定期清理归档查询日志，避免日志文件太大。

以下示例以 192.168.. 节点为基础环境，在实际使用过程中，请根据您的场景修改对应参数。如果您不知道如何获取节点名称，可以通过以下方式获取。

• 通过控制台：您可以在集群管理页面，获取节点名称。
• 通过SQL语句：您可以执行以下语句，获取集群所有节点的名称。

SELECT * FROM system.clusters;

查看报错日志对于提升系统的稳定性和安全性具有重要意义。主要体现在以下几个方面：

• 快速定位问题：通过报错信息可以直接定位到导致问题的具体原因，从而快速修复问题。
• 趋势分析：通过分析报错日志，识别错误发生的时间与模式，可以进行防御性的代码优化或者配置调整。
• 安全：通过分析错误日志，发现系统存在的潜在安全问题，比如SQL注入、非法访问等。

查询模板

SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time,
    exception
FROM clusterAllReplicas('<cluster_name>',system.query_log) ql
WHERE ((event_time >= '<startTime>') 
       AND 
       (event_time <= '<endTime>')) AND (lowerUTF8(query) LIKE '%insert into demo_local%') 
       AND (type != 'QueryStart')
       AND (exception_code != 0)
       [AND host() = '<nodeIP>']
ORDER BY event_time DESC
[LIMIT <x>]

参数说明

示例
查询2025-01-21 22:00:00 至 2025-01-23 23:00:00时间段内，192.18.*.*节点上的执行查询的错误日志信息，返回结果中的前30行数据。

SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time,
    exception
FROM clusterAllReplicas('<cluster_name>',system.query_log) ql
WHERE (lowerUTF8(query) LIKE '%insert into demo_local%') AND 
 ((event_time >= '2025-01-21 22:00:00') 
       AND 
       (event_time <= '2025-01-23 23:00:00')) 
       AND (type != 'QueryStart')
       AND (exception_code != 0)
AND host() = '192.18.*.*'
ORDER BY event_time DESC
LIMIT 30

查询模版

--查看最新写入的SQL，每个batch的行数和bytes大小：
SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time
FROM clusterAllReplicas('<cluster_name>',system.query_log) ql
WHERE ((event_time >= '<startTime>') 
       AND (event_time <= '<endTime>'))
       AND (upperUTF8(query) ILIKE '%INSERT INTO%') AND (type != 'QueryStart')
       [AND host() = '<nodeIP>']
ORDER BY event_time DESC
[LIMIT x]

SELECT   
    event_time,   
    user,   
    query_id AS query,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    exception  
FROM clusterAllReplicas('<cluster_name>', system.query_log)  
WHERE (event_date = today()) AND (event_time >= (now() - <time>)) AND (is_initial_query = 1) AND (upperUTF8(query) NOT ILIKE 'INSERT INTO%'  [AND host() = '<nodeIP>'])  
ORDER BY event_time DESC  
[LIMIT x]

参数说明

示例
查询192.168.*.*节点上近60分钟的非写入SQL，返回查询结果中的前100条数据。

SELECT   
    event_time,   
    user,   
    query_id AS query,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    exception  
FROM clusterAllReplicas('<cluster_name>', system, query_log)  
WHERE (event_date = today()) AND (event_time >= (now() - 60)) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%')  
AND host() = '192.168.*.*'
ORDER BY event_time DESC  
LIMIT 100

查看时间段内执行次数超过n次的非写入语句

查询模板

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('<cluster_name>', system.query_log) ql
   WHERE event_time > toDateTime('<startTime>')
     AND event_time < toDateTime('<endTime>')
     AND upperUTF8(query) not like '%INSERT INTO%'
     AND host() = '<nodeIP>'
   GROUP BY SQL
   ORDER BY avgTime DESC)
WHERE queryNum > <queryNum> 
[LIMIT <x>]

参数说明

示例
查询2024-09-23 12:00:00 至 2024-09-23 17:00:00时间段内，192.168.*.*节点上查询次数超过1000次的非写入的SQL语句。

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('<cluster_name>', system.query_log) ql
   WHERE event_time > toDateTime('2024-09-23 12:00:00')
     AND event_time < toDateTime('2024-09-23 17:00:00')
     AND upperUTF8(query) not like '%INSERT INTO%'
     AND host() = '192.168.*.*'
   GROUP BY SQL
   ORDER BY avgTime DESC)
WHERE queryNum > 1000
LIMIT 50

查询一段时间内每小时或每分钟执行查询的统计

查询模版

--按照每小时聚合
SELECT toHour(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('<cluster_name>', system.query_log) ql
WHERE event_time > toDateTime('<startTime>')
  AND event_time < toDateTime('<endTime>')
  AND upperUTF8(query) not like '%INSERT INTO%'
  AND read_rows != 0
  AND host() = '<nodeIP>'
GROUP BY t
[LIMIT x]

--按照每分钟聚合
SELECT toMinute(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('<cluster_name>', system.query_log) ql
WHERE event_time > toDateTime('<startTime>')
  AND event_time < toDateTime('<endTime>')
  AND upperUTF8(query) not like '%INSERT INTO%'
  AND host() = '<nodeIP>'
  AND read_rows != 0
GROUP BY t
[LIMIT x]

参数说明

查询示例
查询2024-09-23 08:00:00 至 2024-09-23 17:00:00时间段内，192.168.*.*节点上每小时查询数量的统计和查询的平均耗时。

--按照每小时聚合
SELECT toHour(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('<cluster_name>', system.query_log) ql
WHERE event_time > toDateTime('2024-09-23 08:00:00')
  AND event_time < toDateTime('2024-09-23 17:00:00')
  AND upperUTF8(query) not like '%INSERT INTO%'
  AND read_rows != 0
  AND host() = '192.168.*.*'
GROUP BY t
LIMIT 50

查看LEFT JOIN的查询个数

查询一个时间段内LEFT JOIN的查询个数。
查询模板

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('<cluster_name>', system.query_log) ql
   WHERE upperUTF8(query) like '%LEFT JOIN%'
     AND read_rows != 0
     AND event_time > toDateTime('<startTime>')
     AND event_time < toDateTime('<endTime>')
     AND upperUTF8(query) not like '%INSERT INTO%'
     AND host() = '<nodeIP>'
   GROUP BY SQL
   ORDER BY queryNum DESC)

参数说明

查询示例
查询2024-06-25 12:00:00 至 2024-06-25 15:00:00时间段内，192.168.*.*节点上执行LEFT JOIN语句的个数。

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('<cluster_name>', system.query_log) ql
   WHERE upperUTF8(query) like '%LEFT JOIN%'
     AND read_rows != 0
     AND event_time > toDateTime('2024-06-25 12:00:00')
     AND event_time < toDateTime('2024-06-25 15:00:00')
     AND upperUTF8(query) not like '%INSERT INTO%'
     AND host() = '192.168.*.*'
   GROUP BY SQL
   ORDER BY queryNum DESC)

查询用户执行非写入SQL数量排行

查询模板

SELECT   
    user,   
    count(1) AS query_times,   
    sum(read_bytes) AS query_bytes,   
    sum(read_rows) AS query_rows  
FROM clusterAllReplicas('<cluster_name>', system, query_log)  
WHERE (event_date = yesterday()) AND (is_initial_query = 1) AND (upperUTF8(query) NOT LIKE 'INSERT INTO%' AND host() = '<nodeIP>')  
GROUP BY user  
ORDER BY query_times DESC  
[LIMIT x]

参数说明

示例
查询在192.168.*.*节点上执行非写入查询语句的前十名用户。

SELECT   
    user,   
    count(1) AS query_times,   
    sum(read_bytes) AS query_bytes,   
    sum(read_rows) AS query_rows  
FROM clusterAllReplicas('<cluster_name>', system, query_log)  
WHERE (event_date = yesterday()) AND (is_initial_query = 1) AND (upperUTF8(query) NOT LIKE 'INSERT INTO%' AND host() = '192.168.*.*')  
GROUP BY user  
ORDER BY query_times DESC  
LIMIT 10