数据库性能监控与诊断

[mysqld]
performance_schema = ON
performance-schema-instrument='wait/lock/%=ON'
performance-schema-instrument='stage/%=ON'
performance-schema-instrument='statement/%=ON'

-- 查看最消耗资源的 SQL（按总执行时间排序）
SELECT 
    DIGEST_TEXT as query,
    COUNT_STAR as exec_count,
    SUM_TIMER_WAIT/1e12 as total_latency_sec,
    AVG_TIMER_WAIT/1e12 as avg_latency_sec,
    MAX_TIMER_WAIT/1e12 as max_latency_sec,
    SUM_LOCK_TIME/1e12 as total_lock_time_sec,
    SUM_ROWS_SENT,
    SUM_ROWS_EXAMINED,
    FIRST_SEEN, LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看表级别的 IO 统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ, SUM_NUMBER_OF_BYTES_READ,
    COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE,
    COUNT_FETCH, SUM_TIMER_FETCH/1e12
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC;

-- 查看从未使用过的索引
SELECT 
    OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_STAR = 0;

-- 查看全表扫描的 SQL
SELECT * FROM sys.statements_with_full_table_scans 
ORDER BY rows_examined DESC;

-- 查看最慢的 SQL（75 percentile）
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits\G

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 查看表统计
SELECT * FROM sys.schema_table_statistics 
WHERE table_schema = 'mydb' ORDER BY total_latency DESC;

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1              # 超过1秒记录
log_queries_not_using_indexes = 1  # 记录未走索引的查询
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10  # 每分钟最多记录10条无索引查询

# 基础分析
pt-query-digest /var/lib/mysql/slow.log > slow_report.txt

# 过滤特定数据库
pt-query-digest --filter '$event->{db} && $event->{db} eq "mydb"' slow.log

# 按时间段分析
pt-query-digest --since '2024-01-01 00:00:00' --until '2024-01-02 00:00:00' slow.log

# 输出到库里（方便查询历史）
pt-query-digest --review h=localhost,D=percona,t=query_review \
  --history h=localhost,D=percona,t=query_history slow.log

-- InnoDB 当前事务与锁
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 查看锁明细
SELECT * FROM performance_schema.data_locks 
WHERE OBJECT_NAME = 'my_table';

-- 查看最近死锁日志
SHOW ENGINE INNODB STATUS\G

-- ------------------------
-- LATEST DETECTED DEADLOCK
-- ------------------------
-- *** (1) TRANSACTION:
-- TRANSACTION 12345, ACTIVE 10 sec starting index read
-- mysql tables in use 1, locked 1
-- LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
-- MySQL thread id 100, OS thread handle 12345, query id 500 localhost 127.0.0.1 app
-- SELECT * FROM accounts WHERE id = 1 FOR UPDATE
-- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table `db`.`accounts`
-- *** (2) TRANSACTION:
-- ...

# InnoDB 完整状态
SHOW ENGINE INNODB STATUS\G

# 关键段分析

# Micrometer 暴露连接池指标
management:
  metrics:
    enable:
      hikari: true  # 自动暴露 HikariCP 指标到 Prometheus

EXPLAIN ANALYZE
SELECT o.order_id, u.name, o.amount
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
  AND o.create_time > '2024-01-01'
ORDER BY o.amount DESC
LIMIT 100;

-- 查看索引选择性（cardinality / rows，越接近 1 越好）
SELECT TABLE_NAME, INDEX_NAME, CARDINALITY,
       CARDINALITY / (SELECT TABLE_ROWS 
                      FROM information_schema.TABLES 
                      WHERE TABLE_NAME = t.TABLE_NAME) as selectivity
FROM information_schema.STATISTICS t
WHERE TABLE_SCHEMA = 'mydb';

-- 查看冗余索引（Sys Schema）
SELECT * FROM sys.schema_redundant_indexes;

-- 分片键路由命中率（中间件层统计）
-- 理想情况 > 90% 的查询能直接路由到单分片
SELECT 
    total_queries,
    routed_to_single_shard,
    broadcast_queries,
    routed_to_single_shard / total_queries as hit_rate
FROM sharding_proxy.metrics;

# Docker 部署
docker pull percona/pmm-server:2

docker run -d \
  -p 443:443 \
  -v /srv/pmm-data:/srv \
  --name pmm-server \
  --restart always \
  percona/pmm-server:2

# 客户端注册
pmm-admin config --server-insecure-tls --server-url=https://admin:admin@pmm-server:443
pmm-admin add mysql --username=pmm --password=pmm --query-source=perfschema

# mysqld_exporter 配置
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql-exporter:9104']
    params:
      collect[]:
        - global_status
        - perf_schema.tablelocks
        - perf_schema.file_events
        - binlog
        - info_schema.innodb_metrics

# Prometheus alert rules
groups:
  - name: mysql-alerts
    rules:
      - alert: MySQLHighQPS
        expr: rate(mysql_global_status_queries[1m]) > 10000
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL QPS 过高"
          
      - alert: MySQLSlowQueries
        expr: rate(mysql_global_status_slow_queries[5m]) > 1
        for: 5m
        labels:
          severity: warning
          
      - alert: MySQLConnectionsHigh
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 5m
        labels:
          severity: critical
          
      - alert: MySQLReplicationLag
        expr: mysql_slave_lag_seconds > 60
        for: 5m
        labels:
          severity: critical
          
      - alert: MySQLDeadlock
        expr: increase(mysql_global_status_innodb_deadlocks[5m]) > 0
        labels:
          severity: warning