-- 最大连接数
SHOW max_connections;

-- 当前总连接数
SELECT count(*) FROM pg_stat_activity;

-- 按数据库统计连接
SELECT datname,count(*) FROM pg_stat_activity GROUP BY datname;

-- 按用户统计连接
SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;

-- 区分 idle / active / waiting
SELECT datname,usename,state,waiting,client_addr,query_start,query
FROM pg_stat_activity ORDER BY state;

-- 查看单库连接上限
SELECT datname,datconnlimit FROM pg_database;

-- 杀掉会话（pid来自pg_stat_activity.pid）
SELECT pg_terminate_backend(12345);

-- 查看表空间列表
SELECT spcname,pg_size_pretty(pg_tablespace_size(oid)) AS size FROM pg_tablespace;

-- 所有数据库大小
SELECT datname,pg_size_pretty(pg_database_size(oid)) AS size FROM pg_database;

-- 当前库：schema、表、索引大小（不含TOAST）
SELECT nspname,relname,pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE relkind IN ('r')
ORDER BY pg_total_relation_size(c.oid) DESC;

-- 表膨胀（简易版）
SELECT schemaname,relname,n_live_tup,n_dead_tup,
round(n_dead_tup::numeric/(n_live_tup+n_dead_tup+1)*100,2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC;

-- 所有锁清单
SELECT pid,relation::regclass,locktype,mode,granted,query
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON l.pid=a.pid
WHERE relation IS NOT NULL;

-- 阻塞关系：等待者 ← 持有者（最常用）
SELECT
  w.pid wait_pid, w.usename wait_user, w.query wait_sql,
  l.pid lock_pid, l.usename lock_user, l.query lock_sql,
  t.schemaname || '.' || t.relname table_name
FROM pg_stat_activity w
JOIN pg_locks l1 ON w.pid=l1.pid AND NOT l1.granted
JOIN pg_locks l2 ON l1.relation=l2.relation AND l2.granted
JOIN pg_stat_activity l ON l2.pid=l.pid
JOIN pg_stat_user_tables t ON l1.relation=t.relid;

-- 查看正在等待锁的会话
SELECT datname,usename,pid,state,query FROM pg_stat_activity WHERE waiting=true;

-- 当前运行SQL，按执行时长倒序（重点）
SELECT current_timestamp - query_start AS run_time, datname, usename, client_addr, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY run_time DESC;

-- 超过30s的活跃SQL
SELECT current_timestamp - query_start AS run_time, datname, usename, query
FROM pg_stat_activity
WHERE state <> 'idle' AND current_timestamp - query_start > interval '30 seconds';

-- 历史慢SQL（需要开启：pg_stat_statements插件，必须预先shared_preload_libraries加入）
SELECT queryid,query,total_time,calls,mean_time FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 20;

-- 最大连接
SHOW VARIABLES LIKE 'max_connections';

-- 当前连接总数
SHOW STATUS LIKE 'Threads_connected';

-- 会话详情（基础）
SHOW PROCESSLIST;
-- 完整版，不锁表（sys库）
SELECT * FROM sys.processlist;

-- 按用户、host统计连接
SELECT user,host,count(*) FROM information_schema.processlist GROUP BY user,host;

-- 按状态统计
SELECT state,count(*) FROM information_schema.processlist GROUP BY state;

-- 杀会话：ID来自processlist.ID
KILL 1234;
KILL QUERY 1234; -- 只杀当前SQL，不断连接

-- 所有库占用
SELECT TABLE_SCHEMA,
ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) AS size_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA ORDER BY size_mb DESC;

-- 单库下表、索引大小
SELECT TABLE_NAME,ENGINE,
ROUND(DATA_LENGTH/1024/1024,2) data_mb,
ROUND(INDEX_LENGTH/1024/1024,2) idx_mb,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='your_db'
ORDER BY total_mb DESC;

-- 查看表空间文件（独立表空间开启时）
SELECT TABLE_NAME,SPACE,FILE_NAME FROM information_schema.INNODB_TABLESPACES;

-- InnoDB 行锁信息
SELECT * FROM performance_schema.data_locks;

-- 锁等待阻塞关系（核心！谁在等谁）
SELECT * FROM performance_schema.data_lock_waits;

-- sys封装好的InnoDB锁等待视图（推荐）
SELECT * FROM sys.innodb_lock_waits\G

-- MDL元数据锁（alter table 卡住常见）
SELECT * FROM sys.schema_table_lock_waits\G

-- 锁状态指标
SHOW STATUS LIKE 'Innodb_row_lock%';

-- 查看慢查询配置开关
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time'; -- 慢阈值（秒）

-- sys：历史Top慢SQL（需要开启performance_schema）
SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 20;

-- 全表扫描SQL
SELECT * FROM sys.schema_tables_with_full_table_scans;

-- 排序/临时表多的SQL
SELECT * FROM sys.statements_with_sorting;
SELECT * FROM sys.statements_with_temp_tables;