《高性能mysql》读书笔记

文章目录

第三章监控
- 关于存储过程的监控：
第七章高性能索引
第10章备份与恢复

第三章监控

关于存储过程的监控：

示例存储过程：

DELIMITER $$

CREATE PROCEDURE SimpleSelectOne()
BEGIN
    SELECT 1 AS result;
    END$$

DELIMITER ;

只能看到某个存储过程中执行的sql查询，但是看不到是哪个存储过程：

select * from performance_schema.events_statements_history where EVENT_NAME like 'statement/sp%' \G;

在这里插入图片描述

查看存储过程：

SELECT 
    EVENT_ID AS call_event_id,
    OBJECT_SCHEMA AS proc_schema,
    OBJECT_NAME AS proc_name,
    SQL_TEXT
FROM performance_schema.events_statements_history
WHERE SQL_TEXT LIKE 'CALL%';

在这里插入图片描述
注意这里call_event_id和上面的nesting_event_id 可以串联/结合起来查看。

第七章高性能索引

关于前缀索引和基数

1、选择性=基数/总记录数，基数（表列不同值的个数）越接近表总数选择性越高，索引越快。
2、可以通过列前缀索引在索引大小和查询速度上进行折中。选择有一定区分度的列前缀即可。

select count(distinct left(city, 3)) /  count(*) as sel3,
count(distinct left(city, 4)) /  count(*) as sel4,
count(distinct left(city, 5)) /  count(*) as sel5
from city

观察不同长度的前缀的选择性，选择合适的。建索引。

alter table city add key (city(7))

explain的输出

type:

ref：用了索引或者索引的前缀部分列
all 全表扫描
index 全索引扫描

Extra:

Using where：额外过滤
Using index：用了覆盖索引，要查询的列都在索引里面
Using filesort
Use temporary

对于where的实现：

性能从高到底：

在索引中使用where条件过滤记录【存储层完成】
覆盖索引中（Extra列显示using index），读取索引记录后在服务器端中过滤【服务器层完成】
从表中返回记录（Extra列显示using where），在服务器层过滤。最慢的.

索引不被使用的情况：

1、索引未被使用：

检查 name 条件是否使用了函数或类型转换，如 WHERE LOWER(name) = 'xxx' 或 WHERE name = 123（当 name 是字符串类型时）
检查是否使用了 !=、NOT IN 等无法使用索引的操作符

2、索引选择性太低：

如果 name 的值非常集中（如90%的行都有相同的 name 值），优化器可能认为全表扫描比索引扫描更高效

3、统计信息不准确：

MySQL 的优化器依赖统计信息做决策，如果统计信息过时，可能导致错误选择执行计划

4、索引字段类型不一致，比如一个是int 但是查询时候用的string

5、查询覆盖了太多数据：

如果满足 name = xxx 条件的行数超过表的约30%，优化器可能选择全表扫描

第10章备份与恢复

常见和推荐的工具：

基于物理文件的备份和恢复：xtrackbackup
基于逻辑的备份和恢复：mydumper

在生产环境中，安全、可靠、可验证的备份恢复方案是数据库运维的生命线。mydumper（逻辑备份）和 xtrabackup（物理备份）是 MySQL 生态中最主流的两种工具，各有适用场景：

mydumper：适合中小库、跨版本迁移、部分表恢复
xtrabackup：适合大库、秒级恢复、PITR（时间点恢复）

下面分别给出 生产级安全备份与恢复示例，包含权限控制、加密、校验、监控等关键要素。

🔒 一、`mydumper` —— 安全逻辑备份（适用于 ≤ 500GB 库）

✅ 备份策略

每日全量 + 增量 binlog
压缩 + 加密
保留 7 天
专用备份账号（最小权限）

🛠 1. 创建备份专用账号（主库执行）

-- 最小权限原则
CREATE USER 'backup'@'%' IDENTIFIED BY 'StrongPass!2026';
GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, PROCESS ON *.* TO 'backup'@'%';
FLUSH PRIVILEGES;

⚠️ 禁止授予 SUPER 权限！

📦 2. 安全备份脚本（`/opt/scripts/mydumper_backup.sh`）

#!/bin/bash
# 安全 mydumper 备份脚本 - 生产环境

set -euo pipefail

BACKUP_DIR="/backup/mysql/mydumper"
DATE=$(date +%Y%m%d_%H%M)
LOG_FILE="/var/log/mydumper_backup.log"
MYSQL_HOST="127.0.0.1"
MYSQL_USER="backup"
MYSQL_PASS="StrongPass!2026"
ENCRYPTION_KEY="/etc/mysql/backup.key"  # AES-256 密钥文件

# 创建目录
mkdir -p ${BACKUP_DIR}/${DATE}

# 记录开始时间
echo "[$(date)] Starting mydumper backup..." >> $LOG_FILE

# 执行备份（压缩 + 加密 + 并行）
mydumper \
  --host=${MYSQL_HOST} \
  --user=${MYSQL_USER} \
  --password=${MYSQL_PASS} \
  --outputdir=${BACKUP_DIR}/${DATE} \
  --compress=gzip \          # 压缩节省空间
  --encrypt=AES256 \         # 加密备份文件
  --encrypt-key-file=${ENCRYPTION_KEY} \
  --threads=8 \              # 根据 CPU 调整
  --trx-consistency-only \   # 仅保证事务一致性（不锁表）
  --verbose=3 \
  >> $LOG_FILE 2>&1

# 验证备份完整性（检查 metadata 文件）
if [ ! -f "${BACKUP_DIR}/${DATE}/metadata" ]; then
  echo "[$(date)] ERROR: Backup failed - metadata missing!" >> $LOG_FILE
  exit 1
fi

# 清理 7 天前备份
find ${BACKUP_DIR} -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \;

echo "[$(date)] Backup completed successfully." >> $LOG_FILE

🔑 加密密钥管理：

# 生成 256 位 AES 密钥（仅 root 可读）
openssl rand -base64 32 > /etc/mysql/backup.key
chmod 600 /etc/mysql/backup.key
chown root:root /etc/mysql/backup.key

🔁 3. 安全恢复示例（到新实例）

# 解密并恢复
myloader \
  --host=127.0.0.1 \
  --user=restore_user \
  --password='RestorePass!2026' \
  --directory=/backup/mysql/mydumper/20260122_1400 \
  --decrypt=AES256 \
  --decrypt-key-file=/etc/mysql/backup.key \
  --threads=8 \
  --overwrite-tables \
  --verbose=3

✅ 恢复前必做：

在隔离环境测试恢复

检查 SHOW TABLES; 和 SELECT COUNT(*) 验证数据量

不要直接恢复到生产主库！

🔒 二、`xtrabackup` —— 安全物理备份（适用于 ≥ 100GB 库）

✅ 备份策略

每周日全量 + 每日增量
流式压缩 + 加密
保留 4 周
支持 PITR（基于 binlog）

🛠 1. 创建备份账号（主库执行）

CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'XbkPass!2026';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'xtrabackup'@'localhost';
FLUSH PRIVILEGES;

📦 2. 安全全量备份脚本（`/opt/scripts/xtrabackup_full.sh`）

#!/bin/bash
# xtrabackup 全量备份 - 生产安全版

set -euo pipefail

BACKUP_BASE="/backup/mysql/xtrabackup"
DATE=$(date +%Y%m%d)
FULL_BACKUP_DIR="${BACKUP_BASE}/full_${DATE}"
LOG_FILE="/var/log/xtrabackup_full.log"
ENCRYPTION_KEY="/etc/mysql/xbk.key"

mkdir -p $FULL_BACKUP_DIR

# 流式备份到 xbstream + 压缩 + 加密
xtrabackup \
  --user=xtrabackup \
  --password=XbkPass!2026 \
  --backup \
  --target-dir=$FULL_BACKUP_DIR \
  --stream=xbstream \
  --compress=zstd \          # zstd 比 gzip 更快
  --compress-threads=4 \
  --encrypt=AES256 \
  --encrypt-key-file=$ENCRYPTION_KEY \
  --encrypt-threads=4 \
  | ssh backup-server "cat > ${FULL_BACKUP_DIR}/full.xbstream.zst.enc"

# 记录 binlog 位置（用于 PITR）
ssh backup-server "xtrabackup --decrypt=AES256 --encrypt-key-file=$ENCRYPTION_KEY --target-dir=$FULL_BACKUP_DIR && xtrabackup --decompress --target-dir=$FULL_BACKUP_DIR && xtrabackup --prepare --target-dir=$FULL_BACKUP_DIR"

echo "Full backup completed: $DATE" >> $LOG_FILE

💡 为什么用 --stream？
避免本地磁盘写满，直接流到备份服务器。

➕ 3. 增量备份脚本（每日）

# 基于上周日全量做增量
xtrabackup \
  --user=xtrabackup \
  --password=XbkPass!2026 \
  --backup \
  --target-dir=/tmp/inc_$(date +%Y%m%d) \
  --incremental-basedir=/backup/mysql/xtrabackup/full_20260119 \
  --stream=xbstream \
  --compress=zstd \
  --encrypt=AES256 \
  --encrypt-key-file=/etc/mysql/xbk.key \
  | ssh backup-server "cat > /backup/mysql/xtrabackup/inc_$(date +%Y%m%d).xbstream.zst.enc"

🔁 4. 安全恢复流程（到新服务器）

步骤 1：传输并解密全量

scp backup-server:/backup/mysql/xtrabackup/full_20260119.xbstream.zst.enc /restore/
xtrabackup --decrypt=AES256 --encrypt-key-file=/etc/mysql/xbk.key --target-dir=/restore/full
xtrabackup --decompress --target-dir=/restore/full

步骤 2：应用增量（如有）

xtrabackup --decrypt=... --decompress=... --target-dir=/restore/inc_20260120
xtrabackup --prepare --apply-log-only --target-dir=/restore/full
xtrabackup --prepare --target-dir=/restore/full --incremental-dir=/restore/inc_20260120

步骤 3：最终 prepare + 启动

xtrabackup --prepare --target-dir=/restore/full
rsync -avrP /restore/full/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

步骤 4：PITR（如果需要）

-- 查看备份的 binlog 位置
cat /restore/full/xtrabackup_binlog_info

-- 用 mysqlbinlog 恢复到指定时间点
mysqlbinlog --start-position=12345 --stop-datetime="2026-01-22 14:00:00" binlog.000001 | mysql -u root -p

🛡 三、生产环境安全加固清单

项目	mydumper	xtrabackup
最小权限账号	✅	✅
传输加密	SSH / TLS	SSH / TLS
存储加密	AES256	AES256
完整性校验	`metadata` 文件	`xtrabackup_checkpoints`
恢复演练	每月一次	每季度一次
监控告警	备份大小突降、失败日志	同左 + prepare 失败
保留策略	7天全量	4周（全量+增量）

📊 四、如何选择？

场景	推荐工具
库 500GB，要求 RTO < 30min	✅ xtrabackup
需恢复单表	✅ mydumper（`.sql` 文件可编辑）
需 PITR（时间点恢复）	✅ xtrabackup + binlog
云环境（RDS）	❌ 两者均不可用 → 用云厂商快照

✅ 总结

mydumper：逻辑备份，灵活但慢，适合中小库
xtrabackup：物理备份，极速恢复，适合大库
共同原则：
🔐 加密（传输+存储）
👮 最小权限
✅ 定期恢复演练
📉 监控备份大小/耗时异常

💡 终极建议：
同时使用两者——

xtrabackup 做主力（快速恢复）

mydumper 做辅助（单表恢复、跨环境迁移）

文章目录

第三章 监控

关于存储过程的监控：

第七章 高性能索引

关于前缀索引和基数

explain的输出

type:

Extra:

对于where的实现：

索引不被使用的情况：

第10章备份与恢复

🔒 一、mydumper —— 安全逻辑备份（适用于 ≤ 500GB 库）

✅ 备份策略

🛠 1. 创建备份专用账号（主库执行）

📦 2. 安全备份脚本（/opt/scripts/mydumper_backup.sh）

🔁 3. 安全恢复示例（到新实例）

🔒 二、xtrabackup —— 安全物理备份（适用于 ≥ 100GB 库）

✅ 备份策略

🛠 1. 创建备份账号（主库执行）

📦 2. 安全全量备份脚本（/opt/scripts/xtrabackup_full.sh）

➕ 3. 增量备份脚本（每日）

🔁 4. 安全恢复流程（到新服务器）

步骤 1：传输并解密全量

步骤 2：应用增量（如有）

步骤 3：最终 prepare + 启动

步骤 4：PITR（如果需要）

🛡 三、生产环境安全加固清单

📊 四、如何选择？

✅ 总结

第三章监控

第七章高性能索引

🔒 一、`mydumper` —— 安全逻辑备份（适用于 ≤ 500GB 库）

📦 2. 安全备份脚本（`/opt/scripts/mydumper_backup.sh`）

🔒 二、`xtrabackup` —— 安全物理备份（适用于 ≥ 100GB 库）

📦 2. 安全全量备份脚本（`/opt/scripts/xtrabackup_full.sh`）