如何在PostgreSQL中识别未被应用使用与更新的表和列？

一、用数据库内置监控功能直接查（最靠谱） #

不同数据库的统计视图逻辑不同，直接用官方提供的工具比自定义查询精准得多：

MySQL/MariaDB #

先开启性能监控（生产环境注意控制日志存储量），再筛选未被访问的表：

-- 启用表访问监控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'wait/lock/table/sql/handler';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements_%';

-- 筛选非系统库中未被访问的表
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
AND TABLE_NAME NOT IN (
    SELECT DISTINCT OBJECT_NAME
    FROM performance_schema.events_statements_history_long
    WHERE OBJECT_TYPE = 'TABLE'
);

也可开启userstat参数后，通过INFORMATION_SCHEMA.TABLE_STATISTICS查看表的读写次数。

PostgreSQL #

利用pg_stat_user_tables和pg_stat_user_columns视图，直接筛选长期无访问的对象：

-- 查90天内无任何读写扫描的表
SELECT schemaname, relname
FROM pg_stat_user_tables
WHERE (last_scan IS NULL OR last_scan < NOW() - INTERVAL '90 days')
  AND (last_idx_scan IS NULL OR last_idx_scan < NOW() - INTERVAL '90 days')
  AND (last_insert IS NULL OR last_insert < NOW() - INTERVAL '90 days')
  AND (last_update IS NULL OR last_update < NOW() - INTERVAL '90 days')
  AND (last_delete IS NULL OR last_delete < NOW() - INTERVAL '90 days');

-- 查长期未被读取且全为NULL的列（大概率未被使用）
SELECT schemaname, relname, attname
FROM pg_stat_user_columns
WHERE null_frac = 1
  AND (last_read IS NULL OR last_read < NOW() - INTERVAL '90 days');

SQL Server #

注意：sys.dm_db_index_usage_stats在服务重启后会清空统计数据，建议结合长期监控结果：

-- 查未被任何索引访问的用户表
SELECT t.name AS TableName
FROM sys.tables t
LEFT JOIN sys.dm_db_index_usage_stats s ON t.object_id = s.object_id
WHERE s.object_id IS NULL
  AND t.type_desc = 'USER_TABLE';

-- 查未被读写的列
SELECT c.name AS ColumnName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
LEFT JOIN sys.dm_db_column_store_row_groups rg ON c.object_id = rg.object_id
WHERE rg.object_id IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM sys.dm_db_index_usage_stats s
      WHERE s.object_id = t.object_id
      AND s.index_id IN (0,1)
  );

二、从应用层入手排查 #

数据库统计可能漏过间接访问（比如视图、存储过程），结合应用代码和日志能减少误判：

• 代码扫描：用IDE全局搜索、grep或代码分析工具，提取所有SQL语句中的表和列，对比数据库结构找出未被引用的对象。
• 日志分析：收集1-3个月的应用SQL日志，用Python/Shell脚本提取所有访问过的表和列，反向筛选未出现的对象。

三、临时标记验证（避免误操作） #

如果拿不准对象是否被使用，给疑似未使用的表加触发器记录访问，观察一段时间：

-- MySQL示例：创建访问日志表和触发器
CREATE TABLE table_access_log (
    table_name VARCHAR(100),
    access_type VARCHAR(20),
    access_time DATETIME DEFAULT NOW(),
    accessed_by VARCHAR(100)
);

DELIMITER //
CREATE TRIGGER trg_suspect_table_read BEFORE SELECT ON your_suspect_table
FOR EACH ROW
BEGIN
    INSERT INTO table_access_log (table_name, access_type, accessed_by)
    VALUES ('your_suspect_table', 'READ', CURRENT_USER());
END //
DELIMITER ;

观察1-2个月，若日志无记录基本可确定该表未被使用。

四、之前查询没结果的常见原因 #

• 统计数据被重置：比如SQL Server的监控视图在服务重启后清空，MySQL的Performance Schema默认未开启。
• 忽略间接访问：通过视图、存储过程、触发器访问的表，直接查表统计会漏掉，需要关联这些对象的定义。
• 时间范围太短：只查几天数据可能错过低频访问的表，建议至少覆盖1-3个月的统计周期。

内容的提问来源于stack exchange，提问作者Jayant Gupta