-- card_id: SQL-MY-06
-- title: MySQL 8.x 连接容量快照（只读、聚合、脱敏）
-- engine: MySQL
-- versions: 8.0, 8.4
-- risk: R2
-- purpose: 在连接失败或连接池排队时，形成单行实例级容量快照，供负责人继续核对应用连接池、内存、线程和时间线。
-- input: 无；只读取当前 performance_schema 全局状态与只读全局变量。
-- output: 最大连接、当前连接、运行连接、历史峰值、近似余量、利用率区间、连接上限拒绝计数和中止连接计数。
-- permission: MySQL 8.0/8.4 默认可读取该聚合状态的普通已认证会话；隔离测试不授予额外业务库或管理权限。
-- resource_limit: 单行结果，建议客户端超时 5 秒。
-- validation: 已在 MySQL 8.0/8.4 隔离连接压力验证；5.7、生产及客户环境未验证。
-- failure: performance_schema 未启用时返回明确状态行，状态缺失时返回 status_unavailable；不得为获得更多明细扩大权限。
-- prohibited: 不得输出 SQL、库表、索引、账号、主机、地址或连接串；不得 KILL、SET、提交、回滚、修改参数或扩大权限。

WITH status_snapshot AS (
    SELECT
        MAX(CASE WHEN VARIABLE_NAME = 'Threads_connected'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS threads_connected,
        MAX(CASE WHEN VARIABLE_NAME = 'Threads_running'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS threads_running,
        MAX(CASE WHEN VARIABLE_NAME = 'Max_used_connections'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS max_used_connections,
        MAX(CASE WHEN VARIABLE_NAME = 'Connection_errors_max_connections'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS connection_errors_max_connections,
        MAX(CASE WHEN VARIABLE_NAME = 'Aborted_connects'
            THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS aborted_connects
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME IN (
        'Threads_connected',
        'Threads_running',
        'Max_used_connections',
        'Connection_errors_max_connections',
        'Aborted_connects'
    )
),
capacity AS (
    SELECT
        CAST(@@GLOBAL.max_connections AS UNSIGNED) AS max_connections,
        threads_connected,
        threads_running,
        max_used_connections,
        connection_errors_max_connections,
        aborted_connects
    FROM status_snapshot
)
SELECT
    CASE
        WHEN @@performance_schema = 0 THEN 'performance_schema_disabled'
        WHEN threads_connected IS NULL THEN 'status_unavailable'
        ELSE 'ready'
    END AS observation_status,
    max_connections,
    threads_connected,
    threads_running,
    max_used_connections,
    CASE
        WHEN threads_connected IS NULL THEN NULL
        ELSE GREATEST(max_connections - threads_connected, 0)
    END AS approximate_connection_headroom,
    CASE
        WHEN threads_connected IS NULL OR max_connections = 0 THEN NULL
        ELSE ROUND(threads_connected * 100.0 / max_connections, 2)
    END AS connection_utilization_pct,
    CASE
        WHEN threads_connected IS NULL OR max_connections = 0 THEN 'unknown'
        WHEN threads_connected * 100.0 / max_connections < 50 THEN 'under_50pct'
        WHEN threads_connected * 100.0 / max_connections < 80 THEN '50_to_80pct'
        WHEN threads_connected * 100.0 / max_connections < 95 THEN '80_to_95pct'
        ELSE '95pct_or_more'
    END AS utilization_bucket,
    connection_errors_max_connections,
    aborted_connects
FROM capacity
LIMIT 1;
