MySQL 入门教程 · 第三课

48次阅读
没有评论

共计 7017 个字符，预计需要花费 18 分钟才能阅读完成。

目录

  1. 范式设计与约束体系（从“对”到“稳”）
  2. 慢查询排查与性能画像（定位 → 解释 → 优化）
  3. 分区表与数据归档（冷热分层）
  4. 备份与恢复（逻辑 / 物理，PITR）
  5. 复制与读写分离（GTID 架构）
  6. 最小可用 my.cnf 模板
  7. 运维排障清单
  8. 实战练习（含参考答案）

环境假设：MySQL 8.0，InnoDB；承接第一课的 shop 库。

1) 范式设计与约束体系

1.1 范式速记

  • 1NF：列不可再分；
  • 2NF：消除对 部分主键 的依赖（复合主键场景）；
  • 3NF：消除传递依赖（列只依赖主键，不依赖非主键）。

经验：OLTP 先到 3NF；为读性能可 适度反规范化（见 §3.3）。

1.2 约束（Constraints）与示例

-- 用户表：域约束 + 唯一 + 生成列 + 检查
CREATE TABLE users (
  user_id     BIGINT PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) UNIQUE,
  email_l     VARCHAR(255) GENERATED ALWAYS AS (LOWER(email)) STORED,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (is_active IN (0,1))
) ENGINE=InnoDB;

-- 订单：外键动作与金额检查
CREATE TABLE orders (
  order_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id     BIGINT NOT NULL,
  status      ENUM('paid','canceled','refunded') NOT NULL DEFAULT 'paid',
  amount      DECIMAL(10,2) NOT NULL CHECK (amount >= 0),
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB;

外键动作：RESTRICT（阻止删除）、CASCADE（级联）、SET NULL、NO ACTION（同 RESTRICT）。

建议：删除用户这类操作多用 RESTRICT + 逻辑删 is_active=0，避免“多米诺”。

1.3 索引与约束的协同

  • 外键列、连接键、常用过滤列建索引；
  • 联合索引按 选择性高 与查询谓词顺序 靠左；
  • 检查函数列检索可用 函数索引 或生成列 + 索引。

1.4 反规范化的三把刀

  1. 冗余列：如在订单冗余 amount（由明细汇总）以便快速查询；
  2. 物化汇总表：metrics_daily(user_id, d, gmv)；
  3. 维度扁平化：小量维度（如渠道名）直接冗余在事实表，减少 JOIN。

前置条件：明确来源与刷新策略（触发器 / 定时任务 / 应用层写入）。

2) 慢查询排查与性能画像

2.1 打开慢查询日志（生产建议）

# my.cnf

[mysqld]

slow_query_log = ON long_query_time = 0.5 # 超过 0.5s 记录 log_queries_not_using_indexes = ON log_output = FILE

2.2 定位热点语句（performance_schema / sys）

-- 开启必要消费者（一次性配置）UPDATE performance_schema.setup_consumers SET ENABLED='YES';

-- 最近热点语句（sys 库）SELECT * FROM sys.statement_analysis
ORDER BY avg_latency DESC LIMIT 20;

-- 表 / 索引使用画像
SELECT * FROM sys.schema_table_statistics ORDER BY rows_read DESC LIMIT 20;
SELECT * FROM sys.schema_index_statistics ORDER BY rows_read DESC LIMIT 20;

2.3 解释执行计划（EXPLAIN/ANALYZE）

EXPLAIN ANALYZE
SELECT o.order_id
FROM orders o
WHERE o.user_id = 1 AND o.created_at >= NOW() - INTERVAL 30 DAY
ORDER BY o.created_at DESC
LIMIT 50;

关注：type（ALL/INDEX/RANGE/REF/CONST）、rows 预估、key 命中、Extra 的 Using filesort/Using temporary。

2.4 典型可优化模式

  • LIKE '%keyword%' → 倒排 / 前缀索引 / 全文索引（FULLTEXT，InnoDB 支持）；
  • WHERE func(col)=... → 函数索引或生成列；
  • 隐式类型转换（字符串列与数字比较）导致不走索引；
  • OR 过多 → UNION ALL 分段并各走索引；
  • 大 OFFSET 分页 → Keyset 游标；
  • 选择性差的列放在联合索引后部；
  • SELECT * 回表多 → 精选列 + 覆盖索引。

2.5 排查流程（SOP）

  1. 复现场景与样本参数（绑定变量）；
  2. EXPLAIN/ANALYZE 查看实际耗时与扫描行；
  3. 检查是否 可索引化（SARGable）；
  4. 重写：改 JOIN 顺序 / 子查询 → CTE/ 窗口；
  5. 建 / 调索引并回归测试；
  6. 上线后观测慢日志与延迟。

3) 分区表与数据归档

3.1 何时使用分区

  • 单表数亿行、按时间范围查询居多；
  • 需要 快速归档 / 清理 旧数据（ALTER TABLE ... DROP PARTITION）；
  • 按分区并行维护 / 备份。

注意：所有 唯一键（含主键）必须包含分区键。

3.2 RANGE 分区（按月）

CREATE TABLE orders_p (
  order_id   BIGINT NOT NULL,
  user_id    BIGINT NOT NULL,
  amount     DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY(order_id, created_at)  -- 主键含分区列
)
PARTITION BY RANGE (TO_DAYS(created_at)) (PARTITION p2025m07 VALUES LESS THAN (TO_DAYS('2025-08-01')),
  PARTITION p2025m08 VALUES LESS THAN (TO_DAYS('2025-09-01')),
  PARTITION p2025m09 VALUES LESS THAN (TO_DAYS('2025-10-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

分区修剪：

SELECT SUM(amount)
FROM orders_p
WHERE created_at >= '2025-09-01' AND created_at < '2025-10-01';
-- 仅扫描 p2025m09（观察 EXPLAIN PARTITIONS）

3.3 分区维护与归档

-- 滚动新增下月分区
ALTER TABLE orders_p
ADD PARTITION (PARTITION p2025m10 VALUES LESS THAN (TO_DAYS('2025-11-01'))
);

-- 归档并删除 2025-07 分区
CREATE TABLE orders_2025m07 LIKE orders_p;
ALTER TABLE orders_p EXCHANGE PARTITION p2025m07 WITH TABLE orders_2025m07;
-- 导出 / 下沉后
ALTER TABLE orders_p DROP PARTITION p2025m07;

EXCHANGE PARTITION 零拷贝切换冷热数据，适合月度归档。

3.4 HASH/KEY 分区（均衡热点）

CREATE TABLE events_p (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  ts DATETIME NOT NULL
)
PARTITION BY HASH(user_id) PARTITIONS 16;

4) 备份与恢复

4.1 逻辑备份（mysqldump/mysqlpump）

# 全库（含建表与数据）mysqldump -h127.0.0.1 -uroot -p --databases shop \
  --single-transaction --routines --triggers | gzip > shop_full.sql.gz

# 仅结构
mysqldump -uroot -p shop --no-data > schema.sql

# 单表（带条件）mysqldump -uroot -p shop orders --where="created_at>='2025-09-01'" > orders_202509.sql

--single-transaction 在 InnoDB 下实现一致性快照（不锁表）。

4.2 物理备份（克隆 / 热备）

  • Clone Plugin（8.0+）：快速从源实例克隆到目标（适合初始化副本）。
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
-- 在目标实例执行：CLONE INSTANCE FROM 'user'@'source_host':3306 IDENTIFIED BY 'pass';
  • 专业热备：Percona XtraBackup（第三方，思路：物理页拷贝 + 崩溃恢复）。

4.3 基于 binlog 的时间点恢复（PITR）

前提：开启二进制日志（ROW 格式）。

# my.cnf
server_id = 1001
log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800   # 7 天

流程：1) 恢复最近的全量备份 → 2) 用 mysqlbinlog 回放从备份时间到事故前的 binlog。

# 导出窗口内的变更（含 GTID）mysqlbinlog --read-from-remote-server -uroot -p \
  --start-datetime="2025-10-10 10:00:00" \
  --stop-datetime="2025-10-10 12:00:00" \
  --result-file=redo.sql  127.0.0.1:3306

mysql -uroot -p < redo.sql

建议：生产采用 周全量 + 日增量 (binlog) 的组合；定期做 演练恢复 验证可用。

5) 复制与读写分离（GTID）

5.1 基本配置

# Source（主库）server_id = 1
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW

# Replica（从库）server_id = 2
read_only = ON
super_read_only = ON

5.2 建立复制（GTID 自动定位）

-- 在 Replica 上
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source_host', SOURCE_PORT=3306,
  SOURCE_USER='repl', SOURCE_PASSWORD='repl_pass',
  SOURCE_AUTO_POSITION=1;
START REPLICA;
SHOW REPLICA STATUS\G

读流量指向 Replica；写入只在 Source。应用层做 读写分离 并容错切换。

5.3 复制增强与常见模式

  • 半同步复制：等待至少一个副本确认（降低数据丢失概率）；
  • 延迟复制：Replica 延迟回放（如 1 小时）防误删；
  • 多源复制：汇聚多实例到一台只读库做报表；
  • Clone + 复制：先克隆后接上复制以缩短初始化。

反模式：过滤器丢表、在副本跑写操作、忽略错误持续复制。上线前务必写 演练手册。

6) 最小可用 my.cnf 模板

[mysqld]
# 基础
server_id = 1001
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# InnoDB
innodb_buffer_pool_size = 4G        # 物理内存 50%~70%
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1  # 最稳妥

# 日志
log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
slow_query_log = ON
long_query_time = 0.5
log_error_verbosity = 2

# 性能视图
performance_schema = ON

# 连接
max_connections = 500
wait_timeout = 600

以上为“起步线”，需按硬件与负载调整。

7) 运维排障清单（Cheat Sheet）

  • 连接暴增：看 SHOW PROCESSLIST、information_schema.processlist、连接池设置；
  • 慢查询：sys.statement_analysis 定位 → 建索引 / 重写；
  • 复制延迟：Seconds_Behind_Source、磁盘 IO/ 单大事务；
  • 锁等待 / 死锁：SHOW ENGINE INNODB STATUS、performance_schema.events_transactions_*；
  • 空间暴涨：二进制日志、临时表、历史分区未清；
  • 突发 95 分位延迟：检查 文件系统缓存抖动、自适应哈希关闭与否、磁盘队列深度。

8) 实战练习（含参考答案）

练习 1： 为 orders(created_at) 做月分区，并将 2025-07 的数据归档到独立表。

-- 建分区表见 §3.2；以下演示归档
CREATE TABLE orders_2025m07 LIKE orders_p;
ALTER TABLE orders_p EXCHANGE PARTITION p2025m07 WITH TABLE orders_2025m07;
-- 导出或移动后
ALTER TABLE orders_p DROP PARTITION p2025m07;

练习 2： 打开慢日志，找出平均延迟 Top 5 的语句。

-- 配置见 §2.1；查询：SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 5;

练习 3： 将以下查询优化为可走覆盖索引并避免大 OFFSET。

-- 原始：SELECT * FROM orders WHERE status='paid' ORDER BY created_at DESC LIMIT 100000, 50;
-- 优化：CREATE INDEX idx_orders_status_created ON orders(status, created_at, order_id);
-- 首次：SELECT order_id, created_at FROM orders WHERE status='paid' ORDER BY created_at DESC, order_id DESC LIMIT 50;
-- 下一页：SELECT order_id, created_at FROM orders
WHERE status='paid' AND (created_at, order_id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, order_id DESC LIMIT 50;
-- 回表取详情：SELECT * FROM orders WHERE order_id IN (...);

练习 4： 设计备份 +PITR 流程并演练：一周全量、binlog 保留 7 天，误删后回放到事故前 1 分钟。

# 1) 周日 02:00 全量备份（示例）mysqldump -uroot -p --databases shop --single-transaction | gzip > /backups/shop_$(date +%F).sql.gz
# 2) 开启 binlog，保留 7 天（见 §4.3）# 3) 恢复：mysql < /backups/shop_2025-10-05.sql
mysqlbinlog --start-datetime="2025-10-10 00:00:00" --stop-datetime="2025-10-10 11:59:00" \
  --read-from-remote-server -uroot -p 127.0.0.1:3306 | mysql

练习 5： 初始化一个只读副本并开启延迟复制 1 小时，用于“后悔药”。

-- 复制建链见 §5.2；延迟复制（示例参数名以 8.0 新术语为准）CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;  -- 延迟 3600 秒
START REPLICA;

小结

  • 以 约束 和口径 固化数据正确性；
  • 以 慢日志 → sys 库 → EXPLAIN/ANALYZE 建模性能画像；
  • 用 分区与归档 实现冷热分层与“零拷贝”月度切换；
  • 以 全量 + binlog + 演练恢复 保证可回溯；
  • 以 GTID 复制 + 读写分离 获得伸缩与高可用。

打赏 微海报 分享
正文完
mysql
发表至： 数据科学
2025-10-03
 0
一诺
版权声明：本站原创文章，由 一诺 于2025-10-03发表，共计7017字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
YOLOv5从入门到部署之：模型部署
MySQL 入门教程 · 第四课
YOLOv5从入门到部署之：初识YOLOv5
大表性能优化：从原理到入土笔记
评论（没有评论）
验证码