DB Inspection

-- psql CLI
\l

-- SQL 查詢
SELECT datname FROM pg_database;

SHOW DATABASES;

# SQLite CLI
.databases

-- psql CLI
\dt

-- 僅列出 public schema
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public';

-- 含 schema 資訊
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');

SHOW TABLES;

-- 含型別（BASE TABLE / VIEW）
SHOW FULL TABLES;

-- 跨資料庫查詢
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_db';

# SQLite CLI
.tables

# SQL 查詢
SELECT name FROM sqlite_master WHERE type = 'table';

-- psql CLI（含欄位、型別、constraint）
\d tablename

-- 詳細資訊
\d+ tablename

-- SQL 查詢
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'your_table'
AND table_schema = 'public'
ORDER BY ordinal_position;

DESCRIBE tablename;

-- 或
SHOW COLUMNS FROM tablename;

-- 含 comment
SHOW FULL COLUMNS FROM tablename;

# SQLite CLI
.schema tablename

-- SQL 查詢（回傳欄位名、型別、nullable、預設值）
PRAGMA table_info(tablename);

-- psql CLI
\di

-- 指定 table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table';

-- 含 index 大小
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'your_table';

SHOW INDEX FROM tablename;

-- information_schema 查詢
SELECT index_name, column_name, non_unique
FROM information_schema.statistics
WHERE table_schema = 'your_db'
AND table_name = 'your_table';

-- 列出 table 的所有 index
PRAGMA index_list(tablename);

-- 查看 index 欄位
PRAGMA index_info(index_name);

SELECT
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table,
ccu.column_name AS foreign_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_name = 'your_table';

-- 查看建表語句（含 FK 定義）
SHOW CREATE TABLE tablename;

-- information_schema 查詢
SELECT
constraint_name,
column_name,
referenced_table_name,
referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'your_db'
AND table_name = 'your_table'
AND referenced_table_name IS NOT NULL;

PRAGMA foreign_key_list(tablename);

-- psql CLI
\dv

-- SQL 查詢
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'public';

SHOW FULL TABLES WHERE table_type = 'VIEW';

-- 含 view 定義
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'your_db';

SELECT name, sql
FROM sqlite_master
WHERE type = 'view';

-- psql CLI
\du

-- SQL 查詢
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole
FROM pg_roles;

-- 含連線資訊
SELECT usename, usesuper, usecreatedb
FROM pg_user;

SELECT user, host, plugin
FROM mysql.user;

-- 當前使用者
SELECT current_user();

-- 單一 DB
SELECT pg_size_pretty(pg_database_size('your_db'));

-- 列出所有 DB 大小
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

SELECT
table_schema AS db_name,
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;

-- 頁數 × 頁大小 = 總大小（bytes）
SELECT page_count * page_size AS total_bytes
FROM pragma_page_count(), pragma_page_size();

-- 或分開查詢
PRAGMA page_count;
PRAGMA page_size;

-- 含 index
SELECT pg_size_pretty(pg_total_relation_size('your_table'));

-- 拆分 data / index
SELECT
pg_size_pretty(pg_relation_size('your_table')) AS data_size,
pg_size_pretty(pg_indexes_size('your_table')) AS index_size,
pg_size_pretty(pg_total_relation_size('your_table')) AS total_size;

-- 列出所有 table 排序
SELECT
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

SELECT
table_name,
ROUND(data_length / 1024 / 1024, 2) AS data_mb,
ROUND(index_length / 1024 / 1024, 2) AS index_mb,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY (data_length + index_length) DESC;

-- SQLite 無原生 table size 函數，用行數替代
SELECT count(*) FROM your_table;

-- 精確（慢，全表掃描）
SELECT count(*) FROM your_table;

-- 快速估算（從統計資訊）
SELECT reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relname = 'your_table';

-- 精確
SELECT count(*) FROM your_table;

-- 快速估算（InnoDB 為近似值）
SELECT table_rows AS estimated_rows
FROM information_schema.tables
WHERE table_schema = 'your_db'
AND table_name = 'your_table';

-- SQLite 只有精確計數
SELECT count(*) FROM your_table;

-- 查詢計畫（不執行）
EXPLAIN SELECT * FROM your_table WHERE id = 1;

-- 實際執行 + 計時（推薦）
EXPLAIN ANALYZE SELECT * FROM your_table WHERE id = 1;

-- 含 buffer 使用量
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table WHERE id = 1;

-- JSON 格式（適合工具解析）
EXPLAIN (FORMAT JSON) SELECT * FROM your_table WHERE id = 1;

EXPLAIN SELECT * FROM your_table WHERE id = 1;

-- 含實際執行統計（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM your_table WHERE id = 1;

-- 顯示優化器追蹤
SET optimizer_trace = 'enabled=on';
SELECT * FROM your_table WHERE id = 1;
SELECT * FROM information_schema.optimizer_trace;

EXPLAIN QUERY PLAN SELECT * FROM your_table WHERE id = 1;

-- 需啟用 pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 列出最耗時查詢
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 重置統計
SELECT pg_stat_reset();

-- 查看慢查詢日誌設定
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 啟用（需 SUPER 權限）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超過 1 秒記錄

-- MySQL 8.0+ Performance Schema
SELECT
digest_text,
count_star,
avg_timer_wait / 1e12 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 20;

-- 所有連線狀態
SELECT pid, usename, application_name, client_addr, state, query, query_start
FROM pg_stat_activity
ORDER BY query_start;

-- 僅 active 查詢
SELECT pid, usename, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active';

-- 執行超過 N 秒的查詢
SELECT pid, usename, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 seconds';

SHOW PROCESSLIST;

-- 含完整 SQL（不截斷）
SHOW FULL PROCESSLIST;

-- information_schema 版本
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- 查看所有 lock
SELECT
pid,
relation::regclass AS table_name,
mode,
granted
FROM pg_locks
WHERE relation IS NOT NULL;

-- 找出 blocking / blocked 關係
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocker.pid AS blocker_pid,
blocker.query AS blocker_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocker
ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- InnoDB 引擎狀態（含 lock 資訊）
SHOW ENGINE INNODB STATUS;

-- MySQL 8.0+ Performance Schema
SELECT
r.trx_id AS waiting_trx,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits AS w
JOIN information_schema.innodb_trx AS r ON r.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.innodb_trx AS b ON b.trx_id = w.blocking_engine_transaction_id;

-- 連線數總計
SELECT count(*) FROM pg_stat_activity;

-- 依狀態分組
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- 最大連線數設定
SHOW max_connections;

-- 當前連線數
SHOW STATUS LIKE 'Threads_connected';

-- 歷史最大連線數
SHOW STATUS LIKE 'Max_used_connections';

-- 最大連線數設定
SHOW VARIABLES LIKE 'max_connections';

-- 取消查詢（不斷線，較溫和）
SELECT pg_cancel_backend(pid);

-- 強制中斷連線
SELECT pg_terminate_backend(pid);

-- 批次終止閒置連線
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND query_start < now() - interval '10 minutes';

-- 取消查詢（保留連線）
KILL QUERY thread_id;

-- 中斷連線
KILL CONNECTION thread_id;

-- thread_id 從 SHOW PROCESSLIST 取得

SELECT version();

-- 當前連線 DB
SELECT current_database();

-- 當前使用者
SELECT current_user;

SELECT VERSION();

-- 當前連線 DB
SELECT DATABASE();

-- 當前使用者
SELECT CURRENT_USER();

SELECT sqlite_version();

-- 查看所有設定
SHOW ALL;

-- 查看特定設定
SHOW work_mem;
SHOW shared_buffers;

-- 完整設定含來源
SELECT name, setting, unit, source
FROM pg_settings
WHERE name LIKE '%memory%';

-- 所有系統變數
SHOW VARIABLES;

-- 模糊搜尋
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE '%timeout%';

-- 全域 vs Session 值
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW SESSION VARIABLES LIKE 'sql_mode';

-- 編譯選項
PRAGMA compile_options;

-- 常用 PRAGMA 查詢
PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA cache_size;
PRAGMA foreign_keys;

-- 回收死 tuple 空間 + 更新統計資訊
VACUUM ANALYZE your_table;

-- 完整回收（鎖表，慎用）
VACUUM FULL your_table;

-- 僅更新統計資訊（供 query planner 使用）
ANALYZE your_table;

-- 重建 index
REINDEX TABLE your_table;
REINDEX INDEX your_index;

-- 重建 table（InnoDB defragment）
OPTIMIZE TABLE your_table;

-- 更新統計資訊
ANALYZE TABLE your_table;

-- 檢查 table 完整性
CHECK TABLE your_table;

-- 修復（MyISAM）
REPAIR TABLE your_table;

-- 回收刪除後的空間（重建 DB 檔案）
VACUUM;

-- 更新統計資訊
ANALYZE;

-- 完整性檢查
PRAGMA integrity_check;

-- 快速完整性檢查
PRAGMA quick_check;