如何检测空表一周内是否有增删操作及无事务活动？

针对清理未使用空表的需求，以下是不同主流数据库下，检测单表近一周是否有INSERT/DELETE操作、以及是否存在未提交事务的实用查询语句：

一、检测单表近7天的INSERT/DELETE操作 #

MySQL #

-- 替换你的数据库名和目标表名
SELECT 
  table_name,
  update_time,
  last_insert,
  last_delete
FROM information_schema.TABLES t
LEFT JOIN performance_schema.table_io_waits_summary_by_table p
  ON t.table_schema = p.object_schema 
  AND t.table_name = p.object_name
WHERE 
  t.table_schema = 'your_db_name'
  AND t.table_name = 'target_table'
  AND t.update_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);

• 若结果为空，说明该表近一周无任何更新（含INSERT/DELETE）操作
• update_time字段记录了表的最后修改时间

PostgreSQL #

-- 替换目标表名
SELECT 
  relname AS table_name,
  n_tup_ins AS insert_count,
  n_tup_del AS delete_count,
  last_autovacuum
FROM pg_stat_user_tables
WHERE 
  relname = 'target_table'
  AND (n_tup_ins > 0 OR n_tup_del > 0)
  AND last_autovacuum >= NOW() - INTERVAL '7 days';

• 返回空则表示近一周无INSERT/DELETE操作
• n_tup_ins和n_tup_del分别记录累计插入、删除行数，可对比历史值确认近期变化

SQL Server #

-- 替换你的数据库名和目标表名
SELECT 
  t.name AS table_name,
  i.last_user_update,
  i.user_seeks
FROM sys.tables t
LEFT JOIN sys.dm_db_index_usage_stats i
  ON t.object_id = i.object_id
  AND i.database_id = DB_ID('your_db_name')
WHERE 
  t.name = 'target_table'
  AND i.last_user_update >= DATEADD(DAY, -7, GETDATE());

• last_user_update为空或早于7天前，说明近一周无写入操作

二、检测单表是否存在未提交事务 #

MySQL #

-- 替换目标表名
SELECT 
  trx_id,
  trx_started,
  trx_state
FROM information_schema.INNODB_TRX
WHERE trx_tables_in_use LIKE '%target_table%';

• 返回结果则表示有未提交的事务在操作该表

PostgreSQL #

-- 替换目标表名
SELECT 
  pid,
  query_start,
  state,
  query
FROM pg_stat_activity
WHERE 
  state = 'idle in transaction'
  AND query LIKE '%target_table%';

• 也可通过锁视图确认表级事务：

SELECT 
  locktype,
  relation::regclass AS table_name,
  mode,
  pid
FROM pg_locks
WHERE 
  relation = 'target_table'::regclass
  AND mode IN ('ACCESS EXCLUSIVE', 'ROW EXCLUSIVE');

SQL Server #

-- 替换目标表名
SELECT 
  t.name AS table_name,
  s.session_id,
  s.transaction_id,
  s.start_time
FROM sys.dm_tran_session_transactions s
JOIN sys.dm_tran_locks l ON s.transaction_id = l.request_owner_id
JOIN sys.tables t ON l.resource_associated_entity_id = t.object_id
WHERE 
  t.name = 'target_table'
  AND s.status = 'active';

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