Mysql

MySQL — 关系型数据库管理系统

MySQL 是最流行的开源关系型数据库管理系统（RDBMS），以 InnoDB 存储引擎为核心，支持 ACID 事务、外键约束和多种复制架构。

Workflow — 使用流程

遇到 MySQL 需求时，按以下顺序决策:

1. 明确场景
├── 建库建表 / 设计 schema?    → DDL 与数据类型参考 (references/05)
├── 复杂查询 / 报表分析?        → DML + 聚合/窗口函数 (references/03)
├── 性能慢 / 优化 SQL?          → 索引与执行计划 (references/06)
├── 备份 / 恢复?                → 备份与恢复 (references/08)
├── 主从 / 高可用?              → 复制与高可用 (references/07)
├── 存储过程 / 分区 / 事务?     → 高级特性 (references/09)
├── 字符串/日期/JSON 函数?       → 函数参考 (references/01-04)
└── 实战配置 / 搭建?             → 示例 (examples/)

2. 引擎选择: InnoDB (99% 场景) → MyISAM (只读归档) → MEMORY (临时表)
3. 索引设计: 主键先 → 查询/排序/JOIN 列建索引 → 检查最左前缀
4. 生产措施: 开启慢查询 → 配置主从复制 → 制定备份策略

When to Use / When NOT to

Boundary — 能力边界

SQL 语法速查

函数速查

高级特性索引

引擎对比

Gotchas — 常见陷阱

FAQ

Keywords

MySQL, Database, RDBMS, SQL, DDL, DML, DQL, DCL, InnoDB, MyISAM, MEMORY, ACID, transaction, index, B-Tree, EXPLAIN, query optimization, replication, master-slave, binlog, backup, restore, XtraBackup, mysqldump, PITR, partition, view, stored procedure, trigger, CTE, window function, JSON, utf8mb4, performance_schema, slow query, connection pool, sharding, high availability, HA

References

官方文档

• MySQL 8.0 Reference Manual
• MySQL 8.0 Release Notes

工具

• Percona XtraBackup
• Percona Toolkit
• Orchestrator — MySQL 高可用管理
• gh-ost — 在线表结构变更

本 skill 深度参考

• references/01-functions-string.md — 字符串函数大全
• references/02-functions-date.md — 日期时间函数大全
• references/03-functions-aggregate-window.md — 聚合与窗口函数
• references/04-functions-json.md — JSON 函数
• references/05-sql-ddl-types.md — DDL 与数据类型详解
• references/06-index-optimization.md — 索引与执行计划
• references/07-replication-ha.md — 主从复制与高可用
• references/08-backup-restore.md — 备份与恢复
• references/09-advanced-features.md — 高级特性（视图/CTE/存储过程/触发器/事务/分区）

实战示例

• examples/01-connection-pool.md — 连接池配置
• examples/02-slow-query-optimization.md — 慢查询优化
• examples/03-master-slave-setup.md — 主从复制搭建
• examples/04-backup-strategy.md — 备份策略方案

使用流程

Step 1: 环境准备

确保开发环境已安装必要的依赖和工具。

Step 2: 配置初始化

根据项目需求进行基础配置。

Step 3: 核心功能使用

按照示例代码实现核心功能。

Step 4: 测试验证

运行测试确保功能正常。

Step 5: 部署上线

完成开发后进行部署和监控。

示例: 连接池配置 (Java HikariCP)

场景

生产环境高并发 Web 应用中，合理配置数据库连接池是保证性能的关键。本例展示 HikariCP（Spring Boot 默认连接池）的最佳实践配置。

问题

• 连接数太少 → 请求排队等待，响应变慢
• 连接数太多 → MySQL 连接数耗尽（max_connections），系统崩溃
• 连接泄漏 → 连接未正确归还，池逐渐耗尽

解决方案

application.yml 配置

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/shop?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8mb4
    username: root
    password: your_password
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      # 核心配置
      maximum-pool-size: 20           # 最大连接数（核心参数）
      minimum-idle: 5                 # 最小空闲连接数
      connection-timeout: 30000       # 等待连接超时（毫秒）
      idle-timeout: 600000            # 空闲连接最大存活（毫秒，10min）
      max-lifetime: 1800000           # 连接最大寿命（毫秒，30min）
      
      # MySQL 专用优化
      auto-commit: true
      connection-test-query: SELECT 1
      pool-name: ShopHikariPool
      
      # 性能监控
      register-mbeans: true           # 开启 JMX 监控

常用计算公式

最大连接数 = ((核心数 * 2) + 有效磁盘数)

示例:
- 4 核 CPU + 1 SSD → (4 * 2) + 1 = 9
- 8 核 CPU + 1 SSD → (8 * 2) + 1 = 17

通用建议:
- 微服务低并发场景: 5-10
- Web 应用中并发场景: 15-30
- 高并发场景: 分库后每个库 20-50

注意: 不是越大越好。连接池大小 × 并发请求数 = MySQL 实际并发连接。
举例: 10 个实例 × 每个 20 连接 = 200 个 MySQL 连接。

验证配置

-- 查看实际连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 查看连接来源
SELECT * FROM information_schema.processlist;

关键要点

1. 连接池大小不是越大越好：过多的连接会导致 MySQL 上下文切换开销和锁争用 2. max-lifetime 应小于 MySQL 的 wait_timeout（通常 28800s），避免连接被 MySQL 断开后还留在池中 3. connection-test-query 用于心跳检测，SELECT 1 性能最好 4. 建议配合 spring.datasource.hikari.leak-detection-threshold 检测连接泄漏

示例: 慢查询优化实战

场景

电商系统中查询"最近一个月下单超过 5 次的 VIP 用户及其总消费金额"的报表越来越慢。

原始查询

SELECT 
  u.id, u.name, u.email,
  COUNT(o.id) AS order_count,
  SUM(o.amount) AS total_amount
FROM user u
LEFT JOIN `order` o ON u.id = o.user_id
WHERE u.level = 'vip' 
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY u.id, u.name, u.email
HAVING order_count > 5
ORDER BY total_amount DESC
LIMIT 100;

执行时间：12.3s（EXPLAIN 显示全表扫 user 和 order）

分析过程

Step 1: EXPLAIN 分析

EXPLAIN FORMAT=JSON
SELECT 
  u.id, u.name, u.email,
  COUNT(o.id) AS order_count,
  SUM(o.amount) AS total_amount
FROM user u
LEFT JOIN `order` o ON u.id = o.user_id
WHERE u.level = 'vip' 
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY u.id, u.name, u.email
HAVING order_count > 5
ORDER BY total_amount DESC
LIMIT 100;

发现的问题： 1. u.level 没有索引 → type: ALL，全表扫描 50 万用户 2. o.created_at 没有索引 → type: ALL，全表扫描 500 万订单 3. GROUP BY 和 ORDER BY 没有索引覆盖 → Using temporary; Using filesort

Step 2: 添加索引

-- 1. user 表的 level 查询索引
ALTER TABLE user ADD INDEX idx_level (level);

-- 2. order 表的复合索引（user_id 用于 JOIN，created_at 用于时间过滤）
ALTER TABLE `order` ADD INDEX idx_user_created (user_id, created_at);

-- 3. 覆盖索引（减少回表）
ALTER TABLE `order` ADD INDEX idx_user_created_amount (user_id, created_at, amount);

Step 3: 优化后 EXPLAIN

• user 表：type: ref（idx_level），rows: 5000（从 50 万降到 5000）
• order 表：type: ref（idx_user_created），rows: 每用户约 10 行
• Extra: 不再有 Using temporary; Using filesort

优化后结果

-- 优化后查询
SELECT 
  u.id, u.name, u.email,
  COUNT(o.id) AS order_count,
  SUM(o.amount) AS total_amount
FROM user u
INNER JOIN `order` o ON u.id = o.user_id
WHERE u.level = 'vip' 
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY u.id
HAVING order_count > 5
ORDER BY total_amount DESC
LIMIT 100;

执行时间：12.3s → 0.08s（提升约 150 倍）

优化要点总结

示例: 主从复制搭建

场景

为电商平台搭建一主一从架构，实现读写分离和基本高可用。主库处理 DML（写），从库处理 SELECT（读）。

环境

• Master: 192.168.1.100:3306
• Slave: 192.168.1.101:3306
• MySQL 8.0.x

步骤

Step 1: Master 配置

编辑 /etc/my.cnf：

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

重启 MySQL：systemctl restart mysqld

Step 2: Master 创建复制用户

CREATE USER 'replicator'@'192.168.1.101' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.1.101';
FLUSH PRIVILEGES;

Step 3: 记录 Master 二进制日志位置

FLUSH TABLES WITH READ LOCK;  -- 锁住所有表
SHOW MASTER STATUS;
-- 输出:
-- File: mysql-bin.000042
-- Position: 841236

注意：新开一个终端会话执行 SHOW MASTER STATUS，不要在锁会话中执行，否则锁会一直持有。

Step 4: 初始数据同步

# 在 Master 上导出数据
mysqldump -u root -p --all-databases --single-transaction --master-data=2 > /tmp/mysql_full.sql

# 复制到 Slave
scp /tmp/mysql_full.sql root@192.168.1.101:/tmp/

# 解锁 Master
UNLOCK TABLES;

Step 5: Slave 配置

编辑 /etc/my.cnf：

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
read_only = 1
log_slave_updates = 0          # 可选：记录从库更新到 binlog
skip_slave_start = 1           # 防止自动启动复制

重启 MySQL：systemctl restart mysqld

Step 6: Slave 恢复初始数据

mysql -u root -p < /tmp/mysql_full.sql

Step 7: 配置复制

CHANGE MASTER TO
  MASTER_HOST = '192.168.1.100',
  MASTER_PORT = 3306,
  MASTER_USER = 'replicator',
  MASTER_PASSWORD = 'StrongPassword123!',
  MASTER_LOG_FILE = 'mysql-bin.000042',
  MASTER_LOG_POS = 841236;

START SLAVE;

Step 8: 验证复制

SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

Step 9: 测试

-- Master 上插入测试数据
INSERT INTO test.replication_test VALUES (1, 'hello');

-- Slave 上验证
SELECT * FROM test.replication_test;  -- 应该看到数据

验证脚本

#!/bin/bash
# check_replication.sh

IO_STATUS=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running" | awk '{print $2}')
SQL_STATUS=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running" | awk '{print $2}')
LAG=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')

if [ "$IO_STATUS" = "Yes" ] && [ "$SQL_STATUS" = "Yes" ]; then
    echo "Replication OK. Lag: ${LAG}s"
    exit 0
else
    echo "Replication ERROR!"
    exit 1
fi

常见问题排查

示例: 生产环境备份策略

场景

日活 10 万用户的电商平台，MySQL 总数据量约 200GB，需要 24×7 运行，无法接受超过 30 分钟的数据丢失。

备份策略

全量备份:    每天 02:00 (XtraBackup 物理备份)
增量备份:    每 6 小时 (XtraBackup 增量)
二进制日志:  实时归档 (自动备份到 S3)
保留周期:    7 天全量 + 30 天增量 + 90 天 Binlog
异地备份:    同步到阿里云 OSS (跨区域复制)
恢复演练:    每月一次

备份脚本

全量备份脚本

#!/bin/bash
# /usr/local/bin/backup_full.sh

BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
FULL_DIR="${BACKUP_DIR}/full/${DATE}"
MYSQL_USER="backup_user"
MYSQL_PASS="$(cat /etc/mysql/backup_pass)"

# 创建备份目录
mkdir -p ${FULL_DIR}

# 执行 XtraBackup 全量备份
xtrabackup --backup \
  --user=${MYSQL_USER} \
  --password=${MYSQL_PASS} \
  --target-dir=${FULL_DIR} \
  --compress \
  --compress-threads=4 \
  --parallel=4 2>>/var/log/xtrabackup.log

if [ $? -eq 0 ]; then
  echo "[$(date)] Full backup completed: ${FULL_DIR}" >> /var/log/backup.log
  
  # 同步到 OSS
  ossutil sync ${FULL_DIR} oss://myapp-backup/mysql/full/${DATE}/ \
    --delete --force 2>>/var/log/oss_backup.log
    
  # 清理 7 天前的全量备份
  find ${BACKUP_DIR}/full/ -type d -mtime +7 -exec rm -rf {} \;
  echo "[$(date)] Full backup synced to OSS" >> /var/log/backup.log
else
  echo "[$(date)] Full backup FAILED!" >> /var/log/backup.log
  curl -X POST -H "Content-Type: application/json" \
    -d '{"msg":"MySQL 全量备份失败"}' \
    https://alert.example.com/notify
fi

增量备份脚本

#!/bin/bash
# /usr/local/bin/backup_inc.sh

BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
INC_DIR="${BACKUP_DIR}/inc/${DATE}"
MYSQL_USER="backup_user"
MYSQL_PASS="$(cat /etc/mysql/backup_pass)"
LATEST_FULL=$(ls -td ${BACKUP_DIR}/full/*/ | head -1)

# 找最近的备份作为增量基准备份
if [ -z "$(ls -A ${BACKUP_DIR}/inc/ 2>/dev/null)" ]; then
  BASEDIR="${LATEST_FULL}"
else
  BASEDIR=$(ls -td ${BACKUP_DIR}/inc/*/ | head -1)
fi

mkdir -p ${INC_DIR}

xtrabackup --backup \
  --user=${MYSQL_USER} \
  --password=${MYSQL_PASS} \
  --target-dir=${INC_DIR} \
  --incremental-basedir=${BASEDIR} \
  --compress \
  --compress-threads=4 \
  --parallel=4 2>>/var/log/xtrabackup.log

if [ $? -eq 0 ]; then
  echo "[$(date)] Incremental backup completed" >> /var/log/backup.log
  ossutil sync ${INC_DIR} oss://myapp-backup/mysql/inc/${DATE}/ \
    --delete --force 2>>/var/log/oss_backup.log
  find ${BACKUP_DIR}/inc/ -type d -mtime +30 -exec rm -rf {} \;
else
  echo "[$(date)] Incremental backup FAILED!" >> /var/log/backup.log
  curl -X POST -H "Content-Type: application/json" \
    -d '{"msg":"MySQL 增量备份失败"}' \
    https://alert.example.com/notify
fi

Binlog 实时归档

#!/bin/bash
# /usr/local/bin/archive_binlog.sh

BINLOG_DIR="/var/log/mysql"
ARCHIVE_DIR="/data/backup/binlog"
FILES=($(ls -1t ${BINLOG_DIR}/mysql-bin.* 2>/dev/null))

# 排除当前正在使用的 binlog
CURRENT=$(mysql -e "SHOW MASTER STATUS\G" | grep File | awk '{print $2}')
for FILE in "${FILES[@]}"; do
  BASENAME=$(basename $FILE)
  if [ "$BASENAME" != "$CURRENT" ] && [ ! -f "${ARCHIVE_DIR}/${BASENAME}.gz" ]; then
    gzip -c $FILE > ${ARCHIVE_DIR}/${BASENAME}.gz
    ossutil cp ${ARCHIVE_DIR}/${BASENAME}.gz oss://myapp-backup/binlog/
    echo "[$(date)] Archived: ${BASENAME}" >> /var/log/binlog_archive.log
    
    # 删除本地归档后的 binlog 释放空间
    mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"
  fi
done

恢复流程

完整恢复步骤

#!/bin/bash
# /usr/local/bin/restore_mysql.sh

RESTORE_DATE=$1  # 格式: 2024-03-15 10:30:00

# 1. 从 OSS 下载最近的全量备份
ossutil cp -r oss://myapp-backup/mysql/full/latest/ /tmp/restore/full/
echo "Step 1: Full backup downloaded"

# 2. 准备全量备份
xtrabackup --prepare --target-dir=/tmp/restore/full/ --apply-log-only
echo "Step 2: Full backup prepared"

# 3. 按需合并增量备份
for inc in $(ossutil ls oss://myapp-backup/mysql/inc/ | sort); do
  ossutil cp -r $inc /tmp/restore/inc/
  xtrabackup --prepare --target-dir=/tmp/restore/full/ \
    --incremental-dir=/tmp/restore/inc/ --apply-log-only
  echo "Step 3: Incremental ${inc} merged"
done

# 4. 最终准备（非 apply-log-only，回滚未提交事务）
xtrabackup --prepare --target-dir=/tmp/restore/full/
echo "Step 4: Final prepare done"

# 5. 停止 MySQL，替换数据目录
systemctl stop mysqld
mv /var/lib/mysql /var/lib/mysql_bak
xtrabackup --copy-back --target-dir=/tmp/restore/full/
chown -R mysql:mysql /var/lib/mysql
echo "Step 5: Data restored"

# 6. 启动 MySQL
systemctl start mysqld
echo "Step 6: MySQL started"

# 7. 回放 Binlog 到指定时间点（PITR）
mysqlbinlog --stop-datetime="${RESTORE_DATE}" \
  /data/backup/binlog/mysql-bin.* | mysql -u root -p
echo "Step 7: PITR applied to ${RESTORE_DATE}"

定时作业配置

# crontab -e

# 每天 02:00 全量备份
0 2 * * * /usr/local/bin/backup_full.sh

# 每 6 小时增量备份
0 */6 * * * /usr/local/bin/backup_inc.sh

# 每小时检查并归档 binlog
0 * * * * /usr/local/bin/archive_binlog.sh

# 每天 06:00 检查备份完整性
0 6 * * * /usr/local/bin/check_backup.sh

恢复演练计划

每月第一周周日凌晨 2:00 执行：

1. 在测试环境恢复最近的全量备份
2. 应用增量备份
3. 执行 PITR 到指定时间点
4. 验证数据完整性：
   - 检查关键表行数
   - 验证最近订单数据
   - 运行业务自检脚本
5. 记录恢复耗时，持续优化

目标 RTO: < 2 小时
目标 RPO: < 30 分钟

字符串函数 (String Functions)

简介

MySQL 提供丰富的字符串处理函数，用于字符串拼接、截取、替换、格式化等操作。这些函数在数据清洗、脱敏、报表生成中广泛使用。

常用函数速查

拼接与格式化

截取与定位

替换与转换

长度与校验

业务场景

场景 1: 手机号脱敏

SELECT 
  REPLACE(phone, SUBSTRING(phone, 4, 4), '****') AS masked_phone 
FROM user;
-- 138****0000

-- 更推荐的做法（INSERT 函数）
SELECT INSERT(phone, 4, 4, '****') AS masked_phone FROM user;

场景 2: 商品编号补零

SELECT CONCAT('PRD', LPAD(id, 5, '0')) AS product_no FROM product;
-- PRD00001, PRD00002, ...

场景 3: 统计每个用户的所有订单号

SELECT user_id, 
  GROUP_CONCAT(order_no ORDER BY created_at SEPARATOR ', ') AS order_list
FROM `order`
GROUP BY user_id;

场景 4: 检查邮箱格式

SELECT * FROM user WHERE LOCATE('@', email) = 0;

场景 5: JSON 字符串提取（旧版本兼容）

-- 在 MySQL 5.7 之前，JSON 字段用字符串存储时的提取方式
SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(attrs, '"color":"', -1), '"', 2) AS color
FROM product;

注意事项

• LENGTH() 返回字节数而非字符数，对于多字节字符集（utf8mb4）一个中文字符占 3-4 字节
• CHAR_LENGTH() 返回字符数，处理中文时使用此函数
• GROUP_CONCAT 的结果长度受 group_concat_max_len 限制（默认 1024）
• MySQL 字符串索引默认从 1 开始（非 0）

日期时间函数 (Date & Time Functions)

简介

MySQL 的日期时间函数用于获取当前时间、提取日期组件、格式化和计算日期差。在报表统计、时间范围查询、过期计算等场景中高频使用。

常用函数速查

获取当前日期时间

提取日期/时间组件

日期运算

支持的时间单位：MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

格式化与转换

DATE_FORMAT 常用格式符：

星期与周

业务场景

场景 1: 本月、本周、本日统计

-- 本月注册用户
SELECT COUNT(*) FROM user
WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01');

-- 本周注册用户（周一为一周开始）
SELECT COUNT(*) FROM user
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY);

-- 今日统计
SELECT COUNT(*) FROM `order`
WHERE DATE(created_at) = CURDATE();

场景 2: 按年月聚合

SELECT 
  DATE_FORMAT(created_at, '%Y-%m') AS month, 
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue
FROM `order`
GROUP BY month
ORDER BY month;

场景 3: 计算用户注册天数

SELECT id, name, 
  DATEDIFF(NOW(), created_at) AS days_since_reg 
FROM user;

场景 4: 计算任务耗时

SELECT task_id, 
  TIMESTAMPDIFF(SECOND, start_time, end_time) AS duration_seconds
FROM task;

场景 5: 上月同期对比

SELECT 
  DATE_FORMAT(created_at, '%Y-%m-%d') AS day,
  COUNT(*) AS orders_today
FROM `order`
WHERE created_at >= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), INTERVAL WEEKDAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) DAY)
  AND created_at < CURDATE();

注意事项

• DATETIME vs TIMESTAMP：TIMESTAMP 会自动时区转换，范围仅到 2038 年
• NOW() 和 SYSDATE() 的区别：NOW() 返回语句开始的时刻，SYSDATE() 返回函数执行时的时刻
• MySQL 5.6.4+ 支持毫秒精度：NOW(3), CURTIME(6)
• 日期函数中使用 DATE() 包裹列会导致索引失效（应改用范围查询）

聚合函数与窗口函数 (Aggregate & Window Functions)

聚合函数 (Aggregate Functions)

简介

聚合函数对一组行进行计算并返回单个值，常与 GROUP BY 子句配合使用，用于统计汇总和报表生成。

常用聚合函数

业务场景

订单日报统计

SELECT
  COUNT(*) AS total_orders,
  COUNT(DISTINCT user_id) AS unique_users,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_amount,
  MAX(amount) AS max_order,
  MIN(amount) AS min_order
FROM `order`
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY);

GROUP_CONCAT 行转列

SELECT 
  u.name,
  GROUP_CONCAT(o.order_no ORDER BY o.created_at SEPARATOR ', ') AS orders
FROM user u
LEFT JOIN `order` o ON u.id = o.user_id
GROUP BY u.id;

CASE WHEN 条件聚合 (Pivot)

SELECT
  SUM(CASE WHEN amount < 100 THEN 1 ELSE 0 END) AS low_orders,
  SUM(CASE WHEN amount BETWEEN 100 AND 1000 THEN 1 ELSE 0 END) AS mid_orders,
  SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS high_orders
FROM `order`;

窗口函数 (Window Functions, MySQL 8.0+)

简介

窗口函数在不折叠行的情况下对结果集进行聚合和排名计算。与 GROUP BY 不同，窗口函数保留所有原始行，为每行添加计算结果。

语法：

function_name() OVER (
  [PARTITION BY col1, col2, ...]  -- 分组（可选）
  [ORDER BY col ASC|DESC]          -- 排序（可选）
  [frame_clause]                   -- 窗口帧（可选）
)

排名函数

示例：每部门薪资 TOP 3

SELECT dept_id, name, salary
FROM (
  SELECT dept_id, name, salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employee
) t
WHERE rn <= 3;

示例：考试成绩排名

SELECT name, score,
  RANK() OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM exam_score;

偏移函数

示例：日环比增长

SELECT created_at, amount,
  LAG(amount, 1, 0) OVER (ORDER BY created_at) AS prev_amount,
  amount - LAG(amount, 1, 0) OVER (ORDER BY created_at) AS diff,
  ROUND((amount - LAG(amount, 1, 0) OVER (ORDER BY created_at)) / 
        LAG(amount, 1, 0) OVER (ORDER BY created_at) * 100, 2) AS growth_pct
FROM daily_sales;

聚合窗口函数（累计计算）

聚合函数（SUM、AVG、COUNT 等）加上 OVER() 子句后可作为窗口函数使用。

示例：月度累计销售额

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS cumulative_sum,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

示例：部门内薪资对比

SELECT dept_id, name, salary,
  AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary,
  salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg,
  ROUND(salary / AVG(salary) OVER (PARTITION BY dept_id) * 100, 2) AS pct_of_avg
FROM employee;

窗口帧 (Frame Clause)

帧定义了窗口函数的计算范围：

注意事项

• COUNT(*) 和 COUNT(col) 不同：前者包含 NULL 行，后者排除
• GROUP_CONCAT 结果受 group_concat_max_len 限制（默认 1024），可 SET SESSION group_concat_max_len = 10000;
• 窗口函数只能在 SELECT 和 ORDER BY 中使用，不能在 WHERE、GROUP BY、HAVING 中使用
• LAST_VALUE 的默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，需要在 ORDER BY 后显式指定帧才能得到正确结果

JSON 函数 (JSON Functions, MySQL 5.7+)

简介

MySQL 5.7+ 原生支持 JSON 数据类型和一系列 JSON 操作函数。JSON 类型比字符串存储更高效（自动校验格式、内部二进制存储），支持通过虚拟列建立索引。

函数速查

查询与提取

JSON 路径语法

构造与修改

聚合函数

业务场景

场景 1: 从商品 JSON 字段提取属性

SELECT id, name, 
  attrs->>'$.color' AS color,
  attrs->'$.specs' AS specs
FROM product;

场景 2: 更新嵌套 JSON 字段

UPDATE product 
SET attrs = JSON_SET(attrs, '$.specs.storage', '256GB', '$.price', 5999) 
WHERE id = 1;

场景 3: 记录操作日志（JSON 动态字段）

INSERT INTO audit_log (action, detail) VALUES ('update_product',
  JSON_OBJECT(
    'product_id', 1, 
    'old_price', 99, 
    'new_price', 129, 
    'operator', 'admin',
    'timestamp', NOW()
  ));

场景 4: JSON_TABLE 展开为关系表 (MySQL 8.0+)

SELECT jt.*
FROM product,
JSON_TABLE(attrs, '$' COLUMNS (
  color VARCHAR(20) PATH '$.color',
  ram VARCHAR(10) PATH '$.specs.ram',
  storage VARCHAR(10) PATH '$.specs.storage'
)) AS jt
WHERE color = 'black';

场景 5: 通过虚拟列建立 JSON 索引

MySQL 不支持直接对 JSON 列建索引。通过虚拟列 + 普通索引实现：

-- 添加虚拟列
ALTER TABLE product ADD COLUMN color_virtual VARCHAR(20) 
  GENERATED ALWAYS AS (attrs->>'$.color');

-- 为虚拟列建索引
CREATE INDEX idx_product_color ON product(color_virtual);

-- 查询自动使用索引
SELECT * FROM product WHERE color_virtual = 'red';

注意事项

• JSON 列不存储重复键（保留最后一个值）
• JSON 列自动格式校验，非法格式会报错
• JSON 的二进制格式（BSON）允许快速键值查找，无需解析全文
• JSON 列不能有 DEFAULT 值（MySQL 限制）
• JSON 列不能直接索引，必须通过虚拟列间接索引
• 在 WHERE 中直接使用 attrs->>'$.key' 不会使用索引，需走虚拟列
• MySQL 8.0.13+ 支持 JSON_TYPE() 等更多 JSON 工具函数

DDL 与数据类型详解

简介

DDL（Data Definition Language）用于定义和管理数据库对象（库、表、索引、约束等）。正确选择数据类型和约束对性能和数据完整性至关重要。

数据库操作

-- 创建数据库
CREATE DATABASE IF NOT EXISTS shop
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

-- 修改数据库
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 删除数据库
DROP DATABASE IF EXISTS shop;

-- 查看数据库列表
SHOW DATABASES;

-- 切换数据库
USE shop;

-- 查看当前数据库
SELECT DATABASE();

字符集选择建议

原则：生产环境统一使用 utf8mb4 + utf8mb4_unicode_ci，避免字符集混用导致乱码和索引失效。

数据类型

整数类型

主键建议：预期行数超过 40 亿时用 BIGINT，否则用 INT。MySQL 8.0.17+ 不推荐 UNSIGNED，建议用 CHECK 约束代替。

-- ❌ 旧做法
age TINYINT UNSIGNED NOT NULL

-- ✅ MySQL 8.0.17+ 推荐
age TINYINT NOT NULL CHECK (age >= 0)

浮点数与定点数

-- 金额字段
price DECIMAL(10, 2) NOT NULL DEFAULT 0.00  -- 最大 99999999.99
rate  DECIMAL(5, 4)                          -- 利率 0.0001 ~ 9.9999

字符串类型

VARCHAR 长度选择：

• VARCHAR(255) 和 VARCHAR(50) 在行内存储消耗相同（只看实际长度）
• 但临时表排序时按定义长度分配内存，不宜无意义设大
• 合理范围：50-200

TEXT 注意事项：

• TEXT 列不能有 DEFAULT 值
• TEXT 列不能用于内存临时表（ORDER BY 含 TEXT 会使用磁盘临时表）
• TEXT 列的索引必须指定前缀长度：CREATE INDEX idx_content ON article (content(100));

枚举类型

ENUM 注意事项：

• 修改 ENUM 定义需全表重建（ALTER TABLE MODIFY）
• 内部按索引排序（定义顺序），非字母序
• 推荐用 TINYINT + 代码映射，更灵活且迁移友好

-- 推荐做法
status TINYINT NOT NULL DEFAULT 0 COMMENT '0:pending 1:paid 2:shipped'

-- 不推荐
status ENUM('pending', 'paid', 'shipped') NOT NULL DEFAULT 'pending'

JSON 类型 (MySQL 5.7+)

-- 建表使用 JSON
attrs JSON DEFAULT NULL COMMENT '商品扩展属性'

-- 插入 JSON 数据
INSERT INTO product VALUES (1, '手机', '{"color": "black", "specs": {"ram": "8GB"}}');

-- 查询 JSON 值
SELECT attrs->>'$.color' AS color FROM product;

详情参见 references/04-functions-json.md。

空间数据类型

-- 创建空间表
CREATE TABLE location (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  coord POINT NOT NULL SRID 4326  -- WGS84 坐标系
);

-- 插入空间数据
INSERT INTO location VALUES (1, '北京天安门', ST_GeomFromText('POINT(116.397 39.908)', 4326));

-- 查询距离（米）
SELECT name, ST_Distance_Sphere(coord, ST_GeomFromText('POINT(116.4 39.9)', 4326)) AS distance_m
FROM location;

-- 空间索引
ALTER TABLE location ADD SPATIAL INDEX idx_coord (coord);

约束 (Constraints)

完整建表示例

CREATE TABLE IF NOT EXISTS `order` (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  order_no    VARCHAR(32) NOT NULL COMMENT '订单号',
  user_id     INT UNSIGNED NOT NULL COMMENT '用户ID',
  amount      DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '金额',
  status      TINYINT NOT NULL DEFAULT 0 COMMENT '状态:0待支付1已支付2已取消',
  quantity    INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '数量',
  email       VARCHAR(100) DEFAULT NULL COMMENT '通知邮箱',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  deleted_at  DATETIME DEFAULT NULL COMMENT '软删除时间',
  
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_id (user_id),
  KEY idx_status_created (status, created_at),
  KEY idx_deleted_at (deleted_at),
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
  CONSTRAINT chk_amount CHECK (amount >= 0),
  CONSTRAINT chk_status CHECK (status IN (0, 1, 2))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

ALTER TABLE — 表结构变更

-- 添加列
ALTER TABLE user ADD COLUMN avatar VARCHAR(500) DEFAULT NULL AFTER nickname;

-- 修改列类型
ALTER TABLE user MODIFY COLUMN email VARCHAR(200) NOT NULL;

-- 重命名列
ALTER TABLE user CHANGE COLUMN email new_email VARCHAR(200) NOT NULL;

-- 重命名表
RENAME TABLE old_name TO new_name;

-- 添加索引
ALTER TABLE user ADD INDEX idx_email (email);
ALTER TABLE user ADD UNIQUE KEY uk_email (email);

-- 删除索引
ALTER TABLE user DROP INDEX idx_email;

-- 在线 DDL (MySQL 5.6+)
ALTER TABLE user ADD COLUMN age INT, ALGORITHM=INPLACE, LOCK=NONE;

生产环境大表改结构使用 pt-online-schema-change(Percona Toolkit) 或 gh-ost，避免锁表。

DROP / TRUNCATE / DELETE 对比

索引与执行计划 (Index & Query Optimization)

简介

索引是 MySQL 性能优化的核心手段。正确的索引能大幅减少扫描行数，错误的索引设计则会导致全表扫描和性能灾难。

索引类型

B-Tree 索引创建

-- 普通索引
CREATE INDEX idx_name ON user (name);

-- 唯一索引
CREATE UNIQUE INDEX uk_email ON user (email);

-- 复合索引（最左前缀原则）
CREATE INDEX idx_city_age ON user (city, age);

-- 前缀索引（字符串前 N 字符）
CREATE INDEX idx_email_prefix ON user (email(10));

-- 全文索引
CREATE FULLTEXT INDEX ftx_content ON article (title, content);

-- 降序索引 (MySQL 8.0+)
CREATE INDEX idx_created_desc ON `order` (created_at DESC);

-- 不可见索引（测试用）
CREATE INDEX idx_test ON user (name) INVISIBLE;
ALTER TABLE user ALTER INDEX idx_test VISIBLE;

-- 函数索引 (MySQL 8.0.13+)
CREATE INDEX idx_phone_last4 ON user ((RIGHT(phone, 4)));

复合索引最左前缀原则

复合索引 idx_a_b_c (a, b, c)：

索引设计原则

1. 区分度高的列放前面：选择性 = COUNT(DISTINCT col) / COUNT(*) 2. 等值条件列放前面：= 比范围查询列放前面 3. 覆盖索引：查询列全在索引中（Extra 显示 Using index），避免回表 4. 索引下推 (ICP)：MySQL 5.6+，引擎层用 WHERE 条件过滤索引记录后再回表

EXPLAIN 执行计划分析

使用 EXPLAIN

EXPLAIN SELECT u.name, o.order_no
FROM user u
JOIN `order` o ON u.id = o.user_id
WHERE u.id = 100;

EXPLAIN 输出列

type 访问类型（从优到劣）

Extra 关键信息

EXPLAIN ANALYZE (MySQL 8.0.18+)

-- 实际执行并显示各步骤耗时和行数
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM user u
LEFT JOIN `order` o ON u.id = o.user_id
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

SQL 优化原则

★ 核心原则：减少扫描行数，减少回表，减少排序

1. WHERE 条件列建索引（符合最左前缀）
2. 避免 SELECT *，仅取需要的列（利用覆盖索引）
3. 用 EXISTS 代替 IN（大数据量下）
4. 用 UNION ALL 代替 UNION（不需要去重时）
5. 用 LIMIT 限制结果集大小
6. 大表分页用游标（WHERE id > last_id）而非 OFFSET
7. JOIN 的关联列必须有索引
8. GROUP BY / ORDER BY 的列尽量利用索引
9. 避免在 WHERE 条件列上使用函数或计算
10. 拆分大查询为多次小查询（减少锁范围）

常见优化案例

隐式类型转换（索引失效）

-- ❌ phone 是 VARCHAR，传 INT 导致全表扫描
SELECT * FROM user WHERE phone = 13800138000;

-- ✅ 字符类型就传字符串
SELECT * FROM user WHERE phone = '13800138000';

WHERE 条件函数操作（索引失效）

-- ❌ DATE() 函数使索引失效
SELECT * FROM `order` WHERE DATE(created_at) = '2024-01-01';

-- ✅ 范围查询可用索引
SELECT * FROM `order` WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

大分页优化

-- ❌ OFFSET 越大越慢（先扫描再丢弃）
SELECT * FROM `order` ORDER BY id LIMIT 100000, 20;

-- ✅ 游标分页
SELECT * FROM `order` WHERE id > 100000 ORDER BY id LIMIT 20;

-- ✅ 子查询 + JOIN 方式
SELECT * FROM `order`
JOIN (SELECT id FROM `order` ORDER BY id LIMIT 100000, 20) AS tmp
ON `order`.id = tmp.id;

OR 改为 UNION

-- ❌ OR 可能用不到复合索引
SELECT * FROM user WHERE name = '张三' OR email = 'zhangsan@example.com';

-- ✅ UNION 分别用各自索引
SELECT * FROM user WHERE name = '张三'
UNION
SELECT * FROM user WHERE email = 'zhangsan@example.com';

慢查询配置

# my.cnf
slow_query_log = ON                          # 开启慢查询
slow_query_log_file = /var/log/mysql/slow.log # 慢查询日志文件
long_query_time = 1                          # 超过 1 秒的查询记录
log_queries_not_using_indexes = ON           # 记录未使用索引的查询
min_examined_row_limit = 100                 # 扫描行数超过此值才记录

分析慢查询

# mysqldumpslow 排序取前10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Percona Toolkit 分析
pt-query-digest /var/log/mysql/slow.log

索引设计规范

1. 每个表必须有主键（推荐 BIGINT AUTO_INCREMENT 或有序 UUID） 2. 主键不宜过长（B+ 树二级索引过大） 3. 每个表索引数不超过 5-8 个（过多影响写入性能） 4. 大表（> 1000 万行）索引必须业务验证后创建 5. 字符串区分度低或长度大时用前缀索引 6. ORDER BY / GROUP BY / JOIN 的列考虑建索引 7. 不建索引的列：频繁更新、区分度极低（如性别） 8. 用 INVISIBLE 索引测试后再删除 9. 避免冗余索引（如 idx_a_b 和 idx_a 重复）

主从复制与高可用 (Replication & High Availability)

简介

MySQL 主从复制是生产环境中最常用的高可用和读写分离方案。主库（Master）记录二进制日志（Binlog），从库（Slave）通过网络读取并回放日志，实现数据同步。

复制原理

┌─────────────┐          ┌─────────────┐
│   Master    │          │   Slave     │
├─────────────┤          ├─────────────┤
│   Binlog    │─────→    │ Relay Log   │
│  (二进制日志) │  IO线程  │  (中继日志)  │
└─────────────┘          └──────┬──────┘
                               │ SQL线程
                               ▼
                           ┌─────────────┐
                           │  Slave Data  │
                           └─────────────┘

复制流程： 1. Master 提交事务时写入 Binlog 2. Slave 的 IO 线程读取 Master 的 Binlog，写入 Relay Log 3. Slave 的 SQL 线程回放 Relay Log，应用到自身数据

主从同步配置

Master 配置

# my.cnf
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW             # ROW 格式最安全（推荐）
binlog_expire_logs_seconds = 604800  # 保留 7 天
sync_binlog = 1                 # 每次事务提交同步（最安全）

-- 创建复制用户
CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

-- 查看 Master 状态
SHOW MASTER STATUS;
-- File: mysql-bin.000001, Position: 1234

Slave 配置

# my.cnf
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
read_only = 1                   # 只读（防止误写）

-- 设置主节点
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.100',
  MASTER_PORT = 3306,
  MASTER_USER = 'replicator',
  MASTER_PASSWORD = 'password',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 1234;

-- 启动复制
START SLAVE;

-- 查看复制状态（关键字段）
SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes      -- IO 线程正常
-- Slave_SQL_Running: Yes     -- SQL 线程正常
-- Seconds_Behind_Master: 0   -- 延迟秒数（0 最佳）

-- 停止复制
STOP SLAVE;

-- 重置复制
RESET SLAVE ALL;

复制状态监控关键字段

复制模式对比

半同步复制配置

-- Master 和 Slave 都安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- Master 启用
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 1s 超时降级为异步

-- Slave 启用
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

复制延迟处理

常见延迟原因

1. Slave 硬件弱于 Master 2. 大事务（一次 DELETE/UPDATE 百万行） 3. Slave 上存在慢查询锁竞争 4. 单线程 SQL 回放（MySQL 5.6+ 可开启并行复制）

并行复制配置 (MySQL 5.7+)

# my.cnf
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

应用层处理延迟

-- 关键读走主库（如支付成功后的订单查询）
-- 普通读走从库（容忍秒级延迟）

-- 判断延迟：如果从库读不到数据，降级读主库

高可用方案

Orchestrator 工作流程

1. 检测 Master 故障（心跳超时）
2. 选择最优 Slave（延迟最小、数据最新）
3. 自动提升为新 Master
4. 重新配置其他 Slave 指向新 Master
5. 通知应用层新 Master 地址（通过 API / Consul）

读写分离架构

应用层实现

// 伪代码
if (sql.startsWith("SELECT")) {
    connection = slavePool.getConnection();
} else {
    connection = masterPool.getConnection();
}

ProxySQL 实现

-- ProxySQL 配置读写分离组
INSERT INTO mysql_replication_hostgroups 
(writer_hostgroup, reader_hostgroup, comment) 
VALUES (10, 20, '读写分离');

-- SELECT 自动路由到从节点（reader_hostgroup=20）
-- DML 自动路由到主节点（writer_hostgroup=10）

分库分表 (Sharding)

何时需要分库分表

单表 > 5000 万行或单实例 > 2TB 且预期继续增长。

方案选择

分库分表注意事项

1. 分片键选择：user_id % shard_count 或 order_id % shard_count 2. 跨分片查询：全局表、广播表、ER 分片 3. 分布式 ID：雪花算法、Leaf、Segment 4. 分布式事务：XA / TCC / Saga / Seata

备份与恢复 (Backup & Restore)

简介

MySQL 备份分为逻辑备份（mysqldump）和物理备份（XtraBackup）。逻辑备份导出 SQL 语句，适合小规模和迁移场景；物理备份直接复制数据文件，适合大数据库的快速恢复。

mysqldump — 逻辑备份

基本用法

# 备份单个数据库（推荐使用 --single-transaction 避免锁表）
mysqldump -u root -p --single-transaction --routines --triggers --events shop > shop_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases --single-transaction > all_db_backup.sql

# 只备份表结构
mysqldump -u root -p --no-data shop > shop_schema.sql

# 只备份数据
mysqldump -u root -p --no-create-info shop > shop_data.sql

# 压缩备份
mysqldump -u root -p shop | gzip > shop_backup.sql.gz

# 备份特定表
mysqldump -u root -p shop user order product > critical_tables.sql

# 备份到远程服务器
mysqldump -u root -p shop | ssh user@backup-server "cat > /backups/shop.sql"

关键参数说明

恢复

# 基本恢复
mysql -u root -p shop < shop_backup.sql

# 恢复压缩备份
gunzip < shop_backup.sql.gz | mysql -u root -p shop

# 恢复多个数据库
mysql -u root -p < all_db_backup.sql

XtraBackup — 物理备份

Percona XtraBackup 是 MySQL 物理备份的事实标准，支持热备份 InnoDB 表而不影响读写。

安装

# macOS
brew install percona-xtrabackup

# Ubuntu
apt install percona-xtrabackup-80

# CentOS
yum install percona-xtrabackup-80

全量备份与恢复

# 全量备份
xtrabackup --backup --target-dir=/data/backup/full/ --user=root --password=xxx

# 准备恢复（应用 redo log，使数据一致）
xtrabackup --prepare --target-dir=/data/backup/full/

# 恢复到 MySQL 数据目录
xtrabackup --copy-back --target-dir=/data/backup/full/
# 或手动复制
rsync -avrP /data/backup/full/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/

增量备份与恢复

# 全量备份（基础）
xtrabackup --backup --target-dir=/data/backup/full/

# 增量备份（基于全量）
xtrabackup --backup --target-dir=/data/backup/inc1/ \
  --incremental-basedir=/data/backup/full/

# 第二个增量备份（基于前一个增量）
xtrabackup --backup --target-dir=/data/backup/inc2/ \
  --incremental-basedir=/data/backup/inc1/

# 增量恢复流程
# 1. 准备全量（应用 log 但不回滚未提交事务）
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full/

# 2. 合并增量 1
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full/ \
  --incremental-dir=/data/backup/inc1/

# 3. 合并增量 2（最后一次不用 --apply-log-only）
xtrabackup --prepare --target-dir=/data/backup/full/ \
  --incremental-dir=/data/backup/inc2/

# 4. copy-back 恢复
xtrabackup --copy-back --target-dir=/data/backup/full/

二进制日志与 PITR（时间点恢复）

PITR（Point-In-Time Recovery）允许恢复到某个精确的时间点，是应对误操作（DROP TABLE、DELETE 全表）的核心手段。

启用二进制日志

# my.cnf
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW         # ROW 格式最安全
binlog_expire_logs_seconds = 604800  # 保留 7 天

查看二进制日志

-- 查看 Binlog 是否开启
SHOW VARIABLES LIKE 'log_bin';

-- 列出所有 Binlog 文件
SHOW BINARY LOGS;

-- 查看 Binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;

-- 查看当前 Binlog 位置
SHOW MASTER STATUS;

PITR 恢复流程

# Step 1: 恢复最近的完整备份
mysql -u root -p shop < shop_backup.sql

# Step 2: 回放二进制日志到指定时间点
mysqlbinlog --stop-datetime="2024-01-15 10:00:00" \
  /var/log/mysql/mysql-bin.* | mysql -u root -p

# 指定位置恢复
mysqlbinlog --stop-position=12345 \
  /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# 指定开始和结束范围
mysqlbinlog \
  --start-datetime="2024-01-15 09:00:00" \
  --stop-datetime="2024-01-15 10:00:00" \
  /var/log/mysql/mysql-bin.000001 \
  /var/log/mysql/mysql-bin.000002 \
  | mysql -u root -p shop

备份策略推荐

生产环境备份策略

┌──────────────────────────────────────────────┐
│ 生产环境备份策略：                              │
│                                               │
│ 每日凌晨 2:00: 全量备份 (XtraBackup 物理备份)    │
│ 每 6 小时:      增量备份 (XtraBackup 增量)       │
│ 实时:           二进制日志持续归档 (BINLOG)       │
│ 保留周期:       最近 7 天全量 + 30 天增量         │
│ 异地备份:       备份文件同步到对象存储 (OSS/S3)    │
│ 定期演练:       每月一次恢复测试                   │
└──────────────────────────────────────────────┘

备份检查清单

• [ ] 全量备份是否成功（检查 xtrabackup 退出码）
• [ ] 备份文件大小是否合理（过大/过小要排查）
• [ ] 异地备份是否同步完成
• [ ] 二进制日志是否连续不中断
• [ ] 每月恢复演练验证备份可用性
• [ ] 备份保留策略是否符合合规要求

注意事项

• 不要只依赖一种备份方式：逻辑备份 + 物理备份 + Binlog 三者配合
• 测试恢复：定期在测试环境演练恢复流程，确保备份可用
• 监控备份状态：通过脚本监控备份成功率，发送告警
• 备份加密：敏感数据的备份文件应加密存储
• 备份压缩：物理备份建议用 --compress 参数，可节省 3-5 倍存储空间
• mysqldump 对超大表（> 50GB）不适用：导出和导入都极慢，建议用 XtraBackup

高级特性 (Advanced Features)

简介

MySQL 提供视图、CTE、存储过程/函数、触发器、事务与锁、分区表等高级特性，用于满足复杂业务需求和性能优化。

视图 (View)

视图是存储的查询定义，不存储数据，使用时会展开为底层查询执行。

创建与使用

-- 创建视图
CREATE VIEW user_order_summary AS
SELECT u.id, u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount
FROM user u
LEFT JOIN `order` o ON u.id = o.user_id
GROUP BY u.id;

-- 使用视图（像普通表一样查询）
SELECT * FROM user_order_summary WHERE order_count > 5;

-- 可更新视图（需满足条件）
UPDATE active_user SET email = 'new@example.com' WHERE id = 1;

-- 查看视图定义
SHOW CREATE VIEW user_order_summary;

-- 删除视图
DROP VIEW IF EXISTS user_order_summary;

视图限制

业务场景：封装复杂查询逻辑、简化报表查询、提供权限控制层。

CTE (Common Table Expression, MySQL 8.0+)

CTE 提供临时结果集的命名引用，可读性更高，支持递归。

基础 CTE

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  GROUP BY dept_id
)
SELECT e.name, e.salary, da.avg_salary
FROM employee e
JOIN dept_avg da ON e.dept_id = da.dept_id
WHERE e.salary > da.avg_salary;

递归 CTE — 树形结构查询

WITH RECURSIVE sub_depts AS (
  -- 基础节点（根部门）
  SELECT id, name, parent_id, 1 AS level
  FROM department
  WHERE parent_id IS NULL

  UNION ALL

  -- 递归子节点
  SELECT d.id, d.name, d.parent_id, sd.level + 1
  FROM department d
  JOIN sub_depts sd ON d.parent_id = sd.id
)
SELECT * FROM sub_depts ORDER BY level, id;

业务场景：

• 递归 CTE 查询组织树、分类树、评论树
• CTE 替代派生表，提高可读性并支持多次引用

存储过程与存储函数

存储过程

封装多条 SQL，支持事务控制、IN/OUT 参数、错误处理。

DELIMITER //
CREATE PROCEDURE transfer_funds(
  IN from_account INT,
  IN to_account INT,
  IN amount DECIMAL(10, 2),
  OUT result_code INT,
  OUT result_msg VARCHAR(200)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET result_code = -1;
    SET result_msg = '转账失败，事务回滚';
  END;

  START TRANSACTION;
    UPDATE account SET balance = balance - amount 
    WHERE id = from_account AND balance >= amount;
    
    IF ROW_COUNT() = 0 THEN
      SET result_code = -2;
      SET result_msg = '余额不足';
      ROLLBACK;
    ELSE
      UPDATE account SET balance = balance + amount WHERE id = to_account;
      SET result_code = 0;
      SET result_msg = '转账成功';
      COMMIT;
    END IF;
END //
DELIMITER ;

-- 调用
CALL transfer_funds(1, 2, 100.00, @code, @msg);
SELECT @code, @msg;

存储函数

返回单值的函数，可在 SQL 中直接使用。

DELIMITER //
CREATE FUNCTION get_order_count(user_id INT) RETURNS INT
READS SQL DATA
BEGIN
  DECLARE cnt INT;
  SELECT COUNT(*) INTO cnt FROM `order` WHERE user_id = user_id;
  RETURN cnt;
END //
DELIMITER ;

-- 使用
SELECT name, get_order_count(id) AS order_count FROM user;

存储过程 vs 函数

业务场景：存储过程适合转账、库存核减等事务性操作。但现代应用主要在应用层（如 Spring/Go）实现业务逻辑。

触发器 (Trigger)

基本语法

-- CREATE TRIGGER trigger_name
-- {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
-- ON table_name FOR EACH ROW
-- trigger_body

常见用例

-- 1. 自动更新时间
CREATE TRIGGER before_user_update
BEFORE UPDATE ON user
FOR EACH ROW
SET NEW.updated_at = NOW();

-- 2. 审计日志
CREATE TRIGGER after_order_update
AFTER UPDATE ON `order`
FOR EACH ROW
INSERT INTO audit_log (table_name, action, old_data, new_data)
VALUES ('order', 'UPDATE',
  JSON_OBJECT('status', OLD.status, 'amount', OLD.amount),
  JSON_OBJECT('status', NEW.status, 'amount', NEW.amount));

-- 3. 防止重复签到
CREATE TRIGGER before_signin_insert
BEFORE INSERT ON signin
FOR EACH ROW
BEGIN
  DECLARE cnt INT;
  SELECT COUNT(*) INTO cnt FROM signin
  WHERE user_id = NEW.user_id AND DATE(created_at) = CURDATE();
  IF cnt > 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '今日已签到';
  END IF;
END;

触发器注意事项

优先在应用层实现业务逻辑，触发器仅用于审计、自动更新时间等必要场景。

事务与锁

ACID 特性

事务隔离级别

事务使用

START TRANSACTION;
  UPDATE account SET balance = balance - 100 WHERE id = 1;
  UPDATE account SET balance = balance + 100 WHERE id = 2;
  -- 成功
  COMMIT;
  -- 或失败
  ROLLBACK;

-- 保存点
START TRANSACTION;
  INSERT INTO log VALUES ('step1');
  SAVEPOINT sp1;
  INSERT INTO log VALUES ('step2');  -- 出错
  ROLLBACK TO SAVEPOINT sp1;        -- 回退到 sp1
  INSERT INTO log VALUES ('step3');
COMMIT;

锁类型

死锁避免

1. 固定访问顺序：所有事务按相同顺序访问表 2. 缩短事务时间：不要在一个事务内执行大量无关操作 3. 降低隔离级别：SERIALIZABLE → REPEATABLE READ → READ COMMITTED 4. 查看死锁：SHOW ENGINE INNODB STATUS;

分区表 (Partitioning)

分区类型

RANGE 分区

CREATE TABLE orders_partitioned (
  id BIGINT NOT NULL,
  user_id INT NOT NULL,
  amount DECIMAL(10, 2),
  created_at DATETIME NOT NULL
) PARTITION BY RANGE (YEAR(created_at)) (
  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
);

-- 添加分区
ALTER TABLE orders_partitioned ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

-- 删除分区（极快）
ALTER TABLE orders_partitioned DROP PARTITION p2022;

LIST / HASH / KEY 分区

-- LIST 分区（按区域）
CREATE TABLE user_region (
  id INT NOT NULL, name VARCHAR(50), region_id INT NOT NULL
) PARTITION BY LIST (region_id) (
  PARTITION p_north VALUES IN (1, 2, 3),
  PARTITION p_south VALUES IN (4, 5, 6)
);

-- HASH 分区（按哈希）
CREATE TABLE logs (
  id INT NOT NULL, log_data TEXT, created_at DATETIME
) PARTITION BY HASH (id) PARTITIONS 8;

-- KEY 分区
CREATE TABLE sessions (
  id INT NOT NULL, session_data TEXT
) PARTITION BY KEY (id) PARTITIONS 4;

分区修剪 (Partition Pruning)

查询自动只扫描相关分区：

EXPLAIN SELECT * FROM orders_partitioned WHERE created_at >= '2024-01-01';
-- 只扫描 p2024, p_future

ALTER TABLE orders_partitioned TRUNCATE PARTITION p2022;  -- 快速清理历史数据

分区注意事项