-- 表命名
-- 1. 使用小写字母和下划线
-- 2. 使用有意义的名称
-- 3. 添加业务前缀

-- 好的命名
user_info
order_detail
product_category

-- 不好的命名
UserInfo       -- 大小写混用
orderdetail    -- 难以阅读
t1, temp       -- 无意义


-- 字段命名
-- 1. 主键统一使用 id
-- 2. 外键使用 表名_id
-- 3. 时间字段使用 _at 后缀

id BIGINT UNSIGNED PRIMARY KEY
user_id BIGINT UNSIGNED
created_at DATETIME
updated_at DATETIME


-- 索引命名
-- 1. 主键: pk_表名
-- 2. 唯一索引: uk_表名_字段
-- 3. 普通索引: idx_表名_字段

PRIMARY KEY pk_user
UNIQUE KEY uk_user_email (email)
INDEX idx_user_status (status)

-- 1. 检查系统整体状态
SELECT
    numbackends AS connections,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    blks_read AS disk_reads,
    blks_hit AS cache_hits,
    round(100.0 * blks_hit / NULLIF(blks_read + blks_hit, 0), 2) AS cache_hit_ratio,
    tup_returned AS rows_returned,
    tup_fetched AS rows_fetched,
    tup_inserted AS rows_inserted,
    tup_updated AS rows_updated,
    tup_deleted AS rows_deleted
FROM pg_stat_database
WHERE datname = current_database();

-- 2. 检查活动连接
SELECT
    state,
    COUNT(*) AS count,
    MAX(NOW() - query_start) AS max_duration
FROM pg_stat_activity
GROUP BY state;

-- 3. 检查慢查询
SELECT
    pid,
    NOW() - query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < NOW() - INTERVAL '5 seconds'
ORDER BY duration DESC;

-- 4. 检查锁等待
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON
    blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;