Amazon Redshift提供了强大的系统表和视图来监控和分析SQL查询性能。本文将详细介绍如何查看Redshift的历史SQL运行记录，特别是通过STL_QUERY表及其相关查询。

通过以下方法，您可以有效地监控和分析Redshift集群中的SQL查询历史，识别性能问题，优化查询性能，并了解用户行为模式。

1. STL_QUERY表概述

STL_QUERY是Redshift中最基础的系统表之一，记录了集群中执行的所有SQL查询的详细信息。该表包含查询的元数据、执行统计信息和状态信息。

主要字段包括：

• query - 查询的SQL文本（可能被截断）
• querytxt - 完整的查询文本（在STL_QUERYTEXT中）
• starttime - 查询开始时间
• endtime - 查询结束时间
• userid - 执行查询的用户ID
• pid - 进程ID
• aborted - 查询是否被中止
• label - 查询标签（如果有）

2. 直接查看STL_QUERY记录

最基本的查询方式是直接查询STL_QUERY表并按开始时间倒序排列：

SELECT 
    query,
    TRIM(querytxt) as query_text,
    starttime,
    endtime,
    userid,
    pid,
    aborted
FROM 
    stl_query
WHERE 
    starttime >= DATEADD(day, -1, GETDATE())  -- 最近一天的数据
ORDER BY 
    starttime DESC
LIMIT 100;

注意事项：

• STL_QUERY表只保留2-5天的数据，具体取决于集群的日志级别设置
• query字段中的SQL文本可能被截断，完整内容需要查询STL_QUERYTEXT表
• 建议使用时间范围过滤以避免返回过多数据

3. 按用户名筛选查询记录

为了将用户ID转换为可读的用户名，需要连接pg_user系统目录表：

SELECT 
    u.usename as username,
    q.query,
    TRIM(q.querytxt) as query_text,
    q.starttime,
    q.endtime,
    (q.endtime - q.starttime) as duration,
    q.pid,
    q.aborted
FROM 
    stl_query q
JOIN 
    pg_user u ON q.userid = u.usesysid
WHERE 
    q.starttime >= DATEADD(day, -1, GETDATE())
    AND u.usename = 'target_username'  -- 替换为目标用户名
ORDER BY 
    q.starttime DESC
LIMIT 100;

4. 获取完整查询文本

由于STL_QUERY表中的query字段可能被截断，要获取完整的SQL查询文本，需要使用STL_QUERYTEXT表：

SELECT 
    u.usename as username,
    q.query,
    LISTAGG(CASE WHEN LENGTH(RTRIM(t.text)) > 0 THEN RTRIM(t.text) ELSE '' END, '') 
        WITHIN GROUP (ORDER BY t.sequence) as full_query_text,
    q.starttime,
    q.endtime,
    (q.endtime - q.starttime) as duration
FROM 
    stl_query q
JOIN 
    pg_user u ON q.userid = u.usesysid
JOIN 
    stl_querytext t ON q.query = t.query
WHERE 
    q.starttime >= DATEADD(hour, -6, GETDATE())
    AND u.usename = 'target_username'
GROUP BY 
    u.usename, q.query, q.starttime, q.endtime
ORDER BY 
    q.starttime DESC
LIMIT 50;

5. 高级查询示例

5.1 查询执行时间最长的SQL

SELECT 
    u.usename,
    q.query,
    LISTAGG(CASE WHEN LENGTH(RTRIM(t.text)) > 0 THEN RTRIM(t.text) ELSE '' END, '') 
        WITHIN GROUP (ORDER BY t.sequence) as full_query,
    q.starttime,
    q.endtime,
    DATEDIFF(seconds, q.starttime, q.endtime) as execution_seconds
FROM 
    stl_query q
JOIN 
    pg_user u ON q.userid = u.usesysid
JOIN 
    stl_querytext t ON q.query = t.query
WHERE 
    q.starttime >= DATEADD(day, -1, GETDATE())
    AND q.aborted = 0
GROUP BY 
    u.usename, q.query, q.starttime, q.endtime
ORDER BY 
    execution_seconds DESC
LIMIT 20;

5.2 查询被中止的SQL

SELECT 
    u.usename,
    q.query,
    LISTAGG(CASE WHEN LENGTH(RTRIM(t.text)) > 0 THEN RTRIM(t.text) ELSE '' END, '') 
        WITHIN GROUP (ORDER BY t.sequence) as full_query,
    q.starttime,
    q.endtime,
    q.aborted
FROM 
    stl_query q
JOIN 
    pg_user u ON q.userid = u.usesysid
JOIN 
    stl_querytext t ON q.query = t.query
WHERE 
    q.starttime >= DATEADD(day, -1, GETDATE())
    AND q.aborted = 1
GROUP BY 
    u.usename, q.query, q.starttime, q.endtime, q.aborted
ORDER BY 
    q.starttime DESC;

6. 权限和限制

• 要查询STL系统表，用户需要具有超级用户权限或对相关表具有SELECT权限
• STL表数据在集群重启后仍然保留，但会定期清理旧数据
• 对于长期存储和分析，建议将日志数据导出到其他存储系统

7. 性能考虑

• 在查询STL表时，始终使用时间范围过滤以减少扫描的数据量
• 考虑在非高峰时段执行这些分析查询
• 对于生产环境，建议创建物化视图或定期将数据导出到专门的监控表