# 操作前备份命令
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --all-databases | gzip > /backup/his_backup_$(date +%Y%m%d_%H%M%S).sql.gz

-- 查看当前正在执行的慢查询（MySQL）
SHOW PROCESSLIST;

-- 查看超过5秒的查询
SELECT * FROM information_schema.PROCESSLIST 
WHERE TIME > 5 
ORDER BY TIME DESC;

-- 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时开启（重启后失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 3;  -- 超过3秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 永久配置写入 my.cnf

# /etc/mysql/my.cnf 或 /etc/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 3
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10

# CPU使用情况
top -p $(pgrep mysqld)
htop

# 内存使用
free -h
vmstat -s

# 磁盘IO（关键！HIS系统IO密集）
iostat -x 1 10
iotop -o

# 磁盘使用率
df -h
du -sh /var/lib/mysql/

# 网络延迟（排查网络问题）
ping 数据库服务器IP
netstat -an | grep 3306 | wc -l  # 连接数

-- 查看缓冲池大小（最重要！）
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看查询缓存
SHOW VARIABLES LIKE 'query_cache%';

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 查看锁等待情况
SHOW STATUS LIKE 'innodb_row_lock%';
SELECT * FROM information_schema.INNODB_TRX;  -- 当前事务
SELECT * FROM information_schema.INNODB_LOCKS; -- 锁信息
SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 锁等待

-- 查看表碎片情况
SELECT 
    table_name,
    engine,
    ROUND(data_length/1024/1024, 2) AS '数据大小(MB)',
    ROUND(index_length/1024/1024, 2) AS '索引大小(MB)',
    ROUND(data_free/1024/1024, 2) AS '碎片大小(MB)',
    ROUND(data_free/(data_length+index_length)*100, 2) AS '碎片率%'
FROM information_schema.tables 
WHERE table_schema = 'your_his_database'
ORDER BY data_free DESC;

-- 使用EXPLAIN分析执行计划
EXPLAIN SELECT * FROM patient_records 
WHERE register_date BETWEEN '2024-01-01' AND '2024-12-31';

-- 更详细的分析
EXPLAIN FORMAT=JSON SELECT ...;

-- 实际执行分析
EXPLAIN ANALYZE SELECT ...;  -- MySQL 8.0+

type字段（性能从好到差）:
system > const > eq_ref > ref > range > index > ALL

⚠️  出现 ALL 表示全表扫描，必须优化！
⚠️  rows 值很大说明扫描行数多
⚠️  Extra 出现 Using filesort/Using temporary 需要优化

-- 查看现有索引
SHOW INDEX FROM patient_records;

-- HIS系统常用查询场景建索引

-- 1. 患者信息查询
ALTER TABLE patient_info 
ADD INDEX idx_patient_name_idcard (patient_name, id_card);

ALTER TABLE patient_info 
ADD INDEX idx_register_date (register_date);

-- 2. 门诊记录查询
ALTER TABLE outpatient_records 
ADD INDEX idx_visit_date_dept (visit_date, dept_id, doctor_id);

-- 3. 住院记录查询
ALTER TABLE inpatient_records 
ADD INDEX idx_admission_date (admission_date, discharge_date, dept_id);

-- 4. 费用明细查询（常见慢查询）
ALTER TABLE charge_details 
ADD INDEX idx_patient_date (patient_id, charge_date);

-- 5. 药品医嘱查询
ALTER TABLE medical_orders 
ADD INDEX idx_patient_order (patient_id, order_date, order_status);

-- 检查索引使用情况（找出未使用的索引）
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema = 'your_his_database';

-- 找出重复索引
SELECT * FROM sys.schema_redundant_indexes 
WHERE table_schema = 'your_his_database';

# /etc/my.cnf 核心参数优化

[mysqld]
# ============ 内存配置 ============
# InnoDB缓冲池（设置为物理内存的60-70%）
# 服务器8GB内存 → 设置5-6GB
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4  # 每个实例1GB

# 查询缓存（MySQL 8.0已移除，5.7设置）
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

# 排序和连接缓存
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M

# ============ 连接配置 ============
max_connections = 500
max_connect_errors = 10000
wait_timeout = 600
interactive_timeout = 600
connect_timeout = 10

# ============ InnoDB配置 ============
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2  # 性能优先（1为安全优先）
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000  # SSD可设4000-8000

# ============ 临时表配置 ============
tmp_table_size = 256M
max_heap_table_size = 256M

# ============ 线程配置 ============
thread_cache_size = 64
table_open_cache = 4000
table_definition_cache = 2000

-- ❌ 常见问题SQL示例及优化

-- 问题1：SELECT * 全字段查询
-- 错误写法
SELECT * FROM patient_records WHERE dept_id = 1;
-- 正确写法
SELECT patient_id, patient_name, visit_date, diagnosis 
FROM patient_records WHERE dept_id = 1;

-- 问题2：在索引字段上使用函数
-- 错误写法（无法使用索引）
SELECT * FROM records WHERE YEAR(visit_date) = 2024;
-- 正确写法
SELECT * FROM records 
WHERE visit_date >= '2024-01-01' AND visit_date < '2025-01-01';

-- 问题3：隐式类型转换
-- 错误写法（id_card是varchar，传入数字）
SELECT * FROM patient_info WHERE id_card = 123456789;
-- 正确写法
SELECT * FROM patient_info WHERE id_card = '123456789';

-- 问题4：LIKE前缀模糊查询
-- 错误写法（不走索引）
SELECT * FROM patient_info WHERE patient_name LIKE '%张%';
-- 优化方案：使用全文索引
ALTER TABLE patient_info ADD FULLTEXT INDEX ft_name(patient_name);
SELECT * FROM patient_info WHERE MATCH(patient_name) AGAINST('张');

-- 问题5：大分页查询
-- 错误写法（offset很大时极慢）
SELECT * FROM charge_details LIMIT 100000, 20;
-- 正确写法（游标分页）
SELECT * FROM charge_details 
WHERE id > 100000  -- 上次查询最后一条ID
ORDER BY id LIMIT 20;

-- 问题6：不必要的子查询
-- 错误写法
SELECT * FROM patient_info 
WHERE patient_id IN (SELECT patient_id FROM inpatient_records WHERE dept_id=1);
-- 正确写法
SELECT DISTINCT p.* FROM patient_info p
INNER JOIN inpatient_records i ON p.patient_id = i.patient_id 
WHERE i.dept_id = 1;

-- HIS系统数据量大是慢查询主因之一
-- 建议将3年以上历史数据归档

-- 创建归档库
CREATE DATABASE his_archive;

-- 创建归档表（与原表结构相同）
CREATE TABLE his_archive.patient_records_2021 
LIKE his_main.patient_records;

-- 迁移历史数据（分批执行，避免锁表）
INSERT INTO his_archive.patient_records_2021
SELECT * FROM his_main.patient_records 
WHERE visit_date < '2022-01-01'
LIMIT 10000;  -- 分批迁移

-- 验证数据完整性后删除主库历史数据
DELETE FROM his_main.patient_records 
WHERE visit_date < '2022-01-01' 
LIMIT 5000;  -- 分批删除，减少锁定时间

-- 对大表按时间分区（如charge_details费用表）
ALTER TABLE charge_details 
PARTITION BY RANGE (YEAR(charge_date)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询时自动使用分区裁剪
SELECT * FROM charge_details 
WHERE charge_date BETWEEN '2024-01-01' AND '2024-12-31';

         ┌─────────────────┐
         │   HIS应用服务器  │
         └────────┬────────┘
                  │
         ┌────────▼────────┐
         │  数据库中间件    │
         │ (MyCat/ProxySQL)│
         └──┬──────────┬───┘
            │          │
     ┌──────▼──┐  ┌────▼──────┐
     │  主库    │  │  从库      │
     │ (写操作) │  │ (读操作)   │
     └──────────┘  └───────────┘

# ProxySQL 配置示例
# 写操作走主库，读操作走从库
mysql_query_rules:
- rule_id: 1
  active: 1
  match_pattern: "^SELECT"
  destination_hostgroup: 10  # 读组
  apply: 1
- rule_id: 2  
  active: 1
  match_pattern: ".*"
  destination_hostgroup: 20  # 写组
  apply: 1

<!-- HIS系统数据库连接池配置（以Druid为例）-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <!-- 初始连接数 -->
    <property name="initialSize" value="10"/>
    <!-- 最小空闲连接 -->
    <property name="minIdle" value="10"/>
    <!-- 最大连接数 -->
    <property name="maxActive" value="100"/>
    <!-- 获取连接等待超时 -->
    <property name="maxWait" value="30000"/>
    <!-- 连接有效性检测 -->
    <property name="testWhileIdle" value="true"/>
    <property name="validationQuery" value="SELECT 1"/>
    <!-- 连接泄漏检测 -->
    <property name="removeAbandoned" value="true"/>
    <property name="removeAbandonedTimeout" value="180"/>
</bean>

#!/bin/bash
# his_db_monitor.sh - HIS数据库监控脚本

DB_HOST="localhost"
DB_USER="monitor"
DB_PASS="password"
ALERT_EMAIL="admin@hospital.com"
LOG_FILE="/var/log/his_db_monitor.log"

check_slow_queries() {
    SLOW_COUNT=$(mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -e \
        "SHOW GLOBAL STATUS LIKE 'Slow_queries';" 2>/dev/null | \
        awk 'NR==2{print $2}')
    echo "$(date): 慢查询数量: $SLOW_COUNT" >> $LOG_FILE
    
    if [ "$SLOW_COUNT" -gt 100 ]; then
        echo "警告：慢查询数量过多 $SLOW_COUNT" | \
        mail -s "HIS数据库慢查询告警" $ALERT_EMAIL
    fi
}

check_connections() {
    CONN=$(mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -e \
        "SHOW STATUS LIKE 'Threads_connected';" 2>/dev/null | \
        awk 'NR==2{print $2}')
    echo "$(date): 当前连接数: $CONN" >> $LOG_FILE
}

check_lock_waits() {
    LOCKS=$(mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -e \
        "SELECT COUNT(*) FROM information_schema.INNODB_LOCK_WAITS;" \
        2>/dev/null | awk 'NR==2{print $1}')
    
    if [ "$LOCKS" -gt 5 ]; then
        echo "$(date): 警告！存在 $LOCKS 个锁等待！" >> $LOG_FILE
        # 记录详细锁信息
        mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -e \
            "SELECT * FROM information_schema.INNODB_LOCK_WAITS;" \
            >> $LOG_FILE 2>/dev/null
    fi
}

# 每分钟执行检查
while true; do
    check_slow_queries
    check_connections  
    check_lock_waits
    sleep 60
done

监控工具推荐：
├── Prometheus + Grafana（推荐）
│   ├── mysqld_exporter 采集MySQL指标
│   └── 配置告警规则
├── Zabbix
│   └── MySQL监控模板
├── MySQL Enterprise Monitor（官方）
└── Percona Monitoring（免费）

-- Step 1: 查找并终止长时间运行的查询
SELECT id, user, host, db, command, time, state, info 
FROM information_schema.PROCESSLIST 
WHERE time > 10 
ORDER BY time DESC;

-- 终止特定查询（替换 [process_id]）
KILL QUERY [process_id];
-- 或终止连接
KILL CONNECTION [process_id];

-- Step 2: 查看并解决锁等待
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS 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;

-- 终止阻塞的事务
KILL [blocking_thread_id];

-- Step 3: 刷新查询缓存
FLUSH QUERY CACHE;
RESET QUERY CACHE;

-- Step 4: 碎片整理（选择业务低峰期）
OPTIMIZE TABLE patient_records;
OPTIMIZE TABLE charge_details;
OPTIMIZE TABLE medical_orders;

阶段一（立即，当天）：
  ✅ 开启慢查询日志
  ✅ 终止当前长查询/死锁
  ✅ 检查服务器资源（CPU/内存/IO）
  ✅ 查看并调整 innodb_buffer_pool_size

阶段二（本周）：
  ✅ 分析慢查询日志，找出Top10慢SQL
  ✅ 针对性添加索引
  ✅ 优化问题SQL语句
  ✅ 调整数据库核心配置参数

阶段三（本月）：
  ✅ 历史数据归档（3年以上）
  ✅ 大表分区改造
  ✅ 连接池优化
  ✅ 建立监控告警体系

阶段四（季度）：
  ✅ 评估读写分离方案
  ✅ 考虑服务器硬件升级（SSD/内存）
  ✅ 定期维护计划（索引重建、碎片整理）